CREATE TABLE Trn_TasasOro ( 
Fecha SMALLDATETIME NOT NULL,
Quilate INT DEFAULT(0) NOT NULL,
VrGramo DECIMAL(16,6) DEFAULT(0) NOT NULL
 CONSTRAINT PK_Trn_TasasOro PRIMARY KEY CLUSTERED (Fecha,Quilate))
GO
ALTER TABLE tm_PreCuotas ADD tmVrProrroga MONEY DEFAULT(0)
,tmVrCustodia MONEY DEFAULT(0),tmTasaCustod DECIMAL(16,8) DEFAULT(0)
GO
ALTER TABLE Trn_FinAmtza ADD VrProrroga MONEY DEFAULT(0)
,VrCustodia MONEY DEFAULT(0),TasaCustodia DECIMAL(16,8) DEFAULT(0)
GO
ALTER TABLE Trn_FinCausacion ADD TotalCustodia MONEY DEFAULT(0) NOT NULL
GO
INSERT INTO adm_Opciones (IdOpc,Opcion,TipoDato,Valor,NivUp,nModulo)
 VALUES ('PMP','PORCENTAJE MAXIMO DE PRESTAMO SOBRE EL VALOR DE LA PRENDA','DECIMAL','0',5,'PRESTAMOS')
INSERT INTO adm_Opciones (IdOpc,Opcion,TipoDato,Valor,NivUp,nModulo)
 VALUES ('ORO','PRESTAMOS SOBRE PRENDAS (ORO CREDITO)','BOOLEAN','0',5,'PRESTAMOS')
INSERT INTO adm_Opciones (IdOpc,Opcion,TipoDato,Valor,NivUp,nModulo)
 VALUES ('FIT','FLETES INGRESO PARA TERCEROS EN FACTURA DE COMBUSTIBLE','BOOLEAN','0',5,'MAIN')

GO
INSERT INTO Sys_ObjetosApp  (IdObj,IdGrupo,SubModulo,IndObj,Nombre,Formulario,Permisos,NivelMinimo,NomArchivo)
 VALUES ('FRMTASORO','PRECAT','GEN',22,'Precio Diario del Oro','FRMTASORO','S',0,'')
GO
INSERT INTO Sys_Roles (IdObj,IdRole,Funcion)
 VALUES ('FRMPRESIM','CUP','Exceder el limite de préstamo sobre la prenda')
INSERT INTO Sys_Roles (IdObj,IdRole,Funcion)
 VALUES ('FRMDPR1','PRE','Exceder el limite de préstamo sobre la prenda')
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO



ALTER PROCEDURE [dbo].[paQryFinPrestamoFmt] @pmTipDoc VARCHAR(3), @pmIdPrestamoIni INT,@pmIdPrestamoFin INT,@pmIdCia CHAR(2)
 AS 
 SELECT P.IdPrestamo AS NumPtmo,P.IdCia AS CdCia,Compania,P.Fecha AS FechaPtmo,FecPrestamo,FecCorte,P.IdConcepto AS CdConcepto,C.Concepto AS DescConcept,P.IdCliente AS NitCliente
,T.RazonSocial AS NomCliente,P.IdAgencia AS CdAgencia,NomAgencia AS Agencia,VrPrestamo,P.IdTasa AS CdTasa,DescTasa,TI.TipoInteres AS Tipo_Int,TasaEfectiva,NPlazos,TipoPlazo,P.Causacion AS TipoCausac,P.IdMora AS CdMora,DescMora
,TIM.TipoInteres AS Tipo_IntMora,TasaEM,P.NContrato AS NumContrato,CdCiaCon,TipoAprob,NActaJunta,NumCredito
,P.IdVend AS CdVend,VN.RazonSocial AS NomVendedor,pVehiculo,CdConductor,TipoGarantia,VrGarantia,DescGarantia,CxPagar,CdTasa2,VrSeguro,VrIntereses,VrNeto,P.VrCuota AS ValCuota,NumCuotaIni,AcumCapital,AcumIntereses,AcumIntMora,AcumCuotas
,PagosCapital,PagosIntereses,PagosIntMora,CuotasPagadas,CuotasMora,TipCom,TipoCom,Comprobante,IdCiaCom,Anulado,NumDev,FecDev,P.Observacion AS Observ,DiaCausac ,DiaCausac2,DiasVence,P.IdLinea AS CdLinea,LinCredito
,TipoCredito,P.CalcIntMora AS CaclMora,NitEmpConv,CuotasConv,Refinanciado,NumPrestRef,CiaPrestRef,VrRefinanc,DescRefinanc,CdAlmacen,CauAnticipado
,P.IdEstado AS CdEstado,Estado,OrigenAdd,TimeSys,FecUpdate,IdCiaCrea,P.IdUsuario AS Cdusuario,Usuario
--Datos del detalle
,PC.Item,FecCausac,FecVence,NumDias,PC.VrCuota AS TotalCuota,VrCapital,VrInteres,VrSaldo,TasaEfeMes,TasaEfeDia,PC.Causacion AS NumCausac,CdCiaCausac,VrCuoCausac,VrIntCausac,VrSaldoCausac,VrCustodia,VrProrroga,TasaCustodia
--Datos del cliente
,T.TipoId AS TercTipo,T.Dv AS TercDv,T.Codigo AS TercCodigo,T.NomCial AS TercNomCial,T.SiglaRaz AS TercSigal,T.Direccion AS TercDireccion
,T.IdLocal AS TercCdCiudad,L.Localidad AS NomCiudad,L.IdDep AS CdDep,D.Departamento AS TercDpto,T.Telefono AS TercTelefono,T.Fax AS TercFax,T.e_mail AS TercEmail
,T.IdSector AS CdSector,SectorEco,T.IdRegimen AS CdRegimen,Regimen,T.TipEnte AS TercTipEnte
,CLI.IdSzona AS CdSubzona,Subzona,SZ.IdZona AS CdZona,Zona,CLI.IdGrupo AS CdGrupoCli,GrupoClie,CLI.IdTipoTerc AS CdTipoTerc,TipoTercero,EsExento,FecIngreso,VrSalBasico,DirAgencia,TPS.Tarifa AS TarSeg
,CLI.IdLocalAge AS AgeIdCiudad,LA.Localidad AS AgeCiudad,LA.IdDep AS AgeCodDep,DA.Departamento AS AgeDpto,Comentarios,CLI.IncRetFte AS CalcularReteFte 
 FROM Trn_FinPrestamo AS P  INNER JOIN Companias AS CI ON P.IdCia=CI.IdCia
 INNER JOIN Conceptos AS C ON P.IdConcepto=C.IdConcepto  INNER JOIN adm_Usuarios AS U ON P.IdUsuario=U.IdUsuario
 INNER JOIN EstadoDoc AS ED ON P.IdEstado=ED.IdEstado
 INNER JOIN Terceros AS T ON P.IdCliente=T.IdTercero
 INNER JOIN Localidades AS L ON T.IdLocal=L.IdLocal  INNER JOIN Departamentos AS D ON L.IdDep=D.IdDep 
 INNER JOIN  RegimenDian AS RG ON T.IdRegimen=RG.IdRegimen INNER JOIN SectoresEco AS SE ON T.IdSector=SE.IdSector
 INNER JOIN Terceros AS VN ON P.IdVend=VN.IdTercero INNER JOIN TasasInteres AS TI ON P.IdTasa=TI.IdTasa
 INNER JOIN  TercCliePrestamo AS CLI ON P.IdCliente=CLI.IdClie AND P.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 Trn_FinPrestSeguro PS ON P.TipDoc =PS.TipDoc AND P.IdPrestamo=PS.IdPrestamo AND P.IdCia =PS.IdCia 
LEFT JOIN Tablapor TPS ON PS.CdTarSeguro = TPS.IdTarifa 
 LEFT JOIN LineasCred AS LC ON P.IdLinea=LC.IdLinea
 LEFT JOIN TasasIntmora AS TIM ON P.IdMora=TIM.IdMora LEFT JOIN TiposCom AS TC ON P.TipCom=TC.IdCom
 LEFT JOIN Localidades AS LA ON CLI.IdLocalAge=LA.IdLocal  LEFT JOIN Departamentos AS DA ON LA.IdDep=DA.IdDep 
 LEFT JOIN Trn_FinAmtza AS PC ON P.TipDoc=PC.TipDoc AND P.IdPrestamo=PC.IdPrestamo AND P.IdCia=PC.IdCia
 WHERE P.TipDoc=@pmTipDoc  AND P.IdPrestamo BETWEEN @pmIdPrestamoIni AND @pmIdPrestamoFin  AND P.IdCia=@pmIdCia
 ORDER BY  P.IdPrestamo
 


GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


ALTER PROCEDURE  [dbo].[paQrytm_PreCuotasLta] @pmtmNumero VARCHAR(5)
AS
SELECT  tmItem, tmTipDoc, tmPrestamo, tmIdCiaPre, tmItemPre, tmCuota, tmConcepto, tmFecCausa, tmFecVence, tmVrTotal
,tmTipoTasa, tmDTF_EA,tmTasaNom, tmSpread, tmTasaEfe, tmIdCliente,T.RazonSocial AS NomCliente,tmIdAgencia,NomAgencia AS Agencia
,tmIdVend,V.RazonSocial AS NomVendedor,tmEstado, tmDetalle,tmLinCred,tmFactura,tmDiasLiq,tmVrIntCorr, tmTotAbono,tmVrSaldo
,tmUltCausac,tmVrAcumCap,tmNitConv,tmVrSeguro,tmVrProrroga,tmVrCustodia,tmTasaCustod
--datos del prestamo
,FecPrestamo,VrPrestamo,NPlazos,Causacion,P.NContrato AS NumContrato,CdCiaCon,NumCuotaIni,P.IdEstado AS CdEstado
 FROM   tm_PreCuotas AS PC  INNER JOIN Terceros AS T ON PC.tmIdCliente=T.IdTercero
 INNER JOIN Terceros AS V ON PC.tmIdVend=V.IdTercero
 INNER JOIN Trn_FinPrestamo AS P ON PC.tmTipDoc=P.TipDoc AND PC.tmPrestamo=P.IdPrestamo AND PC.tmIdCiaPre=P.IdCia
 LEFT JOIN TercCliePrestamo AS A ON  PC.tmIdCliente=A.IdClie AND PC.tmIdAgencia=A.IdAgencia
 WHERE tmNumero=@pmtmNumero


GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[paInsFinCausacion] @pmTipoProc VARCHAR(3),@pmNumProc INT,@pmIdCia CHAR(2),@pmFecha SMALLDATETIME,@pmTotalCapital MONEY
,@pmTotalInteres MONEY,@pmTotalIntMora MONEY,@pmTipoCred VARCHAR(10),@pmTipCom VARCHAR(3),@pmComprobante INT,@pmAnulado BIT,@pmObservacion VARCHAR(250)
,@pmEdoCausac INT,@pmTotalOtros MONEY,@pmTipoLiquida VARCHAR(10),@pmTotalCustodia MONEY,@pmTimeSys SMALLDATETIME,@pmIdUsuario VARCHAR(11)
 AS 
 INSERT INTO Trn_FinCausacion (TipoProc,NumProc,IdCia,Fecha,TotalCapital,TotalInteres,TotalIntMora,TotalOtros,TipoLiquida,TipoCred,TipCom,Comprobante,Anulado,Observacion,EdoCausac,TimeSys,IdUsuario,TotalCustodia)
 VALUES (@pmTipoProc,@pmNumProc,@pmIdCia,@pmFecha,@pmTotalCapital,@pmTotalInteres,@pmTotalIntMora,@pmTotalOtros,@pmTipoLiquida,@pmTipoCred,@pmTipCom,@pmComprobante,@pmAnulado
,@pmObservacion,@pmEdoCausac,@pmTimeSys,@pmIdUsuario,@pmTotalCustodia)

GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


ALTER PROCEDURE [dbo].[paInsFinAmtza] @pmTipDoc VARCHAR(3),@pmIdPrestamo INT,@pmIdCia CHAR(2),@pmItem INT,@pmFecCausac SMALLDATETIME,@pmFecVence SMALLDATETIME,@pmNumDias INT,@pmVrCuota MONEY,@pmVrCapital MONEY
,@pmVrInteres MONEY,@pmVrSaldo MONEY,@pmTasaEfeMes DECIMAL(16,8),@pmTasaEfeDia DECIMAL(16,8),@pmCausacion INT,@pmCdCiaCausac CHAR(2),@pmVrCuoCausac MONEY,@pmVrIntCausac MONEY,@pmVrSaldoCausac MONEY,@pmVrSeguroCuo MONEY
,@pmVrCustodia MONEY,@pmVrProrroga MONEY,@pmTasaCustodia DECIMAL(16,8)
 AS 
 INSERT INTO Trn_FinAmtza (TipDoc,IdPrestamo,IdCia,Item,FecCausac,FecVence,NumDias,VrCuota,VrCapital,VrInteres,VrSaldo,TasaEfeMes,TasaEfeDia,Causacion,CdCiaCausac,VrCuoCausac,VrIntCausac,VrSaldoCausac,VrSeguroCuo,VrCustodia,VrProrroga,TasaCustodia)
 VALUES (@pmTipDoc,@pmIdPrestamo,@pmIdCia,@pmItem,@pmFecCausac,@pmFecVence,@pmNumDias,@pmVrCuota,@pmVrCapital,@pmVrInteres,@pmVrSaldo,@pmTasaEfeMes,@pmTasaEfeDia,@pmCausacion
,@pmCdCiaCausac,@pmVrCuoCausac,@pmVrIntCausac,@pmVrSaldoCausac,@pmVrSeguroCuo,@pmVrCustodia,@pmVrProrroga,@pmTasaCustodia)


GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


ALTER PROCEDURE  [dbo].[paInstm_PreCuotas_Aza] @pmtmNumero VARCHAR(5),@pmTipDoc VARCHAR(3),@pmIdPrestamo INT,@pmIdCia CHAR(2) 
 AS 
 INSERT INTO tm_PreCuotas (tmNumero,tmItem,tmTipDoc,tmPrestamo,tmIdCiaPre,tmItemPre,tmCuota,tmConcepto,tmFecCausa,tmFecVence,tmVrTotal,tmTipoTasa
,tmDTF_EA,tmTasaNom,tmSpread,tmTasaEfe,tmIdCliente,tmIdAgencia,tmIdVend,tmEstado,tmDetalle,tmTotAbono,tmLinCred,tmFactura
,tmDiasLiq,tmVrIntCorr,tmVrSaldo,tmUltCausac,tmVrAcumCap,tmNitConv,tmConvnio,tmVrSeguro,tmVrProrroga,tmVrCustodia,tmTasaCustod)
SELECT @pmtmNumero,Item,TipDoc,IdPrestamo,IdCia,Item,Item,'CAPITAL',FecCausac,FecVence,VrCuota,'VARIABLE',TasaEfeMes,TasaEfeDia,0,TasaEfeMes
,'0','0','0',0,'',0,'0','',NumDias,VrInteres,VrSaldo,Null,VrCapital,'0',0,VrSeguroCuo,VrProrroga,VrCustodia,TasaCustodia
 FROM Trn_FinAmtza
 WHERE TipDoc=@pmTipDoc AND IdPrestamo=@pmIdPrestamo AND IdCia=@pmIdCia


GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


ALTER PROCEDURE  [dbo].[paInstm_PreCuotas_Sel] @pmtmNumero VARCHAR(5),@pmTipDoc VARCHAR(3),@pmIdPrestamo INT,@pmIdCia CHAR(2) 
 AS 
 INSERT INTO tm_PreCuotas (tmNumero,tmItem,tmTipDoc,tmPrestamo,tmIdCiaPre,tmItemPre,tmCuota,tmConcepto,tmFecCausa,tmFecVence,tmVrTotal,tmTipoTasa
,tmDTF_EA,tmTasaNom,tmSpread,tmTasaEfe,tmIdCliente,tmIdAgencia,tmIdVend,tmEstado,tmDetalle,tmTotAbono,tmLinCred,tmFactura,tmDiasLiq,tmVrIntCorr
,tmVrSaldo,tmUltCausac,tmVrAcumCap,tmNitConv,tmConvnio,tmVrSeguro,tmVrProrroga,tmVrCustodia,tmTasaCustod)
SELECT @pmtmNumero,Item,TipDoc,IdPrestamo,IdCia,Item,NumCuota,Concepto,Fecha,FechaVence,VrTotal,TipoTasa,DTF_EA,TasaNom,Spread_TA,TasaEfe,IdCliente
,IdAgencia,'0',1,Detalle,VrAbonado,CodLinCred,NumFactura,DiasLiquida,VrInteresFact,0,FecUltCausac,VrAcumCapital,NitConvenio,Convenio,0,0,0,0
 FROM Trn_FinCuotas
 WHERE TipDoc=@pmTipDoc AND IdPrestamo=@pmIdPrestamo AND IdCia=@pmIdCia


GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


ALTER PROCEDURE [dbo].[paInstm_PreCuotas] @pmtmNumero VARCHAR(5),@pmtmItem INT,@pmtmTipDoc VARCHAR(3),@pmtmPrestamo INT,@pmtmIdCiaPre CHAR(2),@pmtmItemPre INT,@pmtmCuota INT,@pmtmConcepto VARCHAR(10)
,@pmtmFecCausa SMALLDATETIME,@pmtmFecVence SMALLDATETIME,@pmtmVrTotal MONEY,@pmtmTipoTasa VARCHAR(10),@pmtmDTF_EA DECIMAL(16,8),@pmtmTasaNom DECIMAL(16,8),@pmtmSpread DECIMAL(16,8)
,@pmtmTasaEfe DECIMAL(16,8),@pmtmIdCliente VARCHAR(16),@pmtmIdAgencia VARCHAR(16),@pmtmIdVend VARCHAR(16),@pmtmEstado INT,@pmtmDetalle VARCHAR(250),@pmtmTotAbono MONEY,@pmtmLinCred VARCHAR(4)
,@pmtmFactura VARCHAR(20),@pmtmDiasLiq INT,@pmtmVrIntCorr MONEY,@pmtmVrSaldo MONEY,@pmtmUltCausac SMALLDATETIME,@pmtmVrAcumCap MONEY,@pmtmNitConv VARCHAR(16),@pmtmConvnio INT,@pmtmVrSeguro MONEY
,@pmtmVrProrroga MONEY,@pmtmVrCustodia MONEY,@pmtmTasaCustod DECIMAL(16,8)
 AS 
 INSERT INTO tm_PreCuotas (tmNumero,tmItem,tmTipDoc,tmPrestamo,tmIdCiaPre,tmItemPre,tmCuota,tmConcepto,tmFecCausa,tmFecVence,tmVrTotal,tmTipoTasa,tmDTF_EA,tmTasaNom,tmSpread,tmTasaEfe,tmIdCliente
,tmIdAgencia,tmIdVend,tmEstado,tmDetalle,tmTotAbono,tmLinCred,tmFactura,tmDiasLiq,tmVrIntCorr,tmVrSaldo,tmUltCausac,tmVrAcumCap,tmNitConv,tmConvnio,tmVrSeguro,tmVrProrroga,tmVrCustodia,tmTasaCustod)
 VALUES (@pmtmNumero,@pmtmItem,@pmtmTipDoc,@pmtmPrestamo,@pmtmIdCiaPre,@pmtmItemPre,@pmtmCuota,@pmtmConcepto,@pmtmFecCausa,@pmtmFecVence,@pmtmVrTotal,@pmtmTipoTasa,@pmtmDTF_EA,@pmtmTasaNom
,@pmtmSpread,@pmtmTasaEfe,@pmtmIdCliente,@pmtmIdAgencia,@pmtmIdVend,@pmtmEstado,@pmtmDetalle,@pmtmTotAbono,@pmtmLinCred,@pmtmFactura,@pmtmDiasLiq,@pmtmVrIntCorr,@pmtmVrSaldo,@pmtmUltCausac
,@pmtmVrAcumCap,@pmtmNitConv,@pmtmConvnio,@pmtmVrSeguro,@pmtmVrProrroga,@pmtmVrCustodia,@pmtmTasaCustod)


GO
ALTER TABLE Trn_FinNotas DROP CONSTRAINT [CK_Trn_FinNotasModalidad]
GO
ALTER TABLE Trn_FinNotas ADD CONSTRAINT [CK_Trn_FinNotasModalidad] CHECK  (([Modalidad]='PRORROGA' OR [Modalidad]='NOTA' OR [Modalidad]='MORA' OR [Modalidad]='CHEQUES'))
GO