INSERT INTO  Sys_Roles  (IdObj,IdRole,Funcion)
 VALUES ('FRMDODC','PER','Permitir Exceder Unidades Solicitadas en Requisiciones')
INSERT INTO  Sys_Roles  (IdObj,IdRole,Funcion)
 VALUES ('FRMDPEC','CDE','Permitir Cambiar ciudad destino')
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInstm_PreExtoApf]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paInstm_PreExtoApf]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInstm_PreExtoDpa]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paInstm_PreExtoDpa]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paInstm_PreExtoApf] @pmtmEst CHAR(2),@pmtmTipoRep INT,@pmFechaIni SMALLDATETIME,@pmFechaFin SMALLDATETIME,@pmIdCliente VARCHAR(16)=Null
,@pmIdCia CHAR(2)=Null
 AS 
--diferencia de cuotas no causadas prestamos refinanciados
 INSERT INTO tm_PreExto (tmEst,tmTipoRep,tmTipDoc,tmNumero,tmIdCia,tmItem,tmFecha,tmIdCliente,tmIdAgencia,tmCargos,tmAbonos,tmIdLinea,tmConcepto,tmDetalle,tmReferencia,tmCredito,tmVrPrestamo,tmVrCuota
,tmTotAbonos,tmNCuota,tmIdVend,tmTimeSys,tmFecVence,tmTipoAbono,tmDTF_EA,tmTasaNom,tmTasaEfe,tmTipRef,tmDocRef,tmIdCiaRef,tmTipCom,tmComprobante,tmIdCiaCom,tmAnulado,tmNumDev,tmFecDev
,tmSanClie,tmSanCia,tmSanAgenc,tmSanAgeCia,tmSanLinea,tmSanLineaCia,tmSanConc,tmSanConcCia)
SELECT @pmtmEst,@pmtmTipoRep,P.TipDoc,P.IdPrestamo,P.IdCia,0,P.FecPrestamo,P.IdCliente,P.IdAgencia,0,P.VrPrestamo-ISNULL(AB.VrAbono,0),P.IdLinea,'CAPITAL','PRESTAMO REFINANCIADO-CAPITAL NO CAUSADO',P.NumCredito,P.TipoCredito,P.VrPrestamo,P.VrCuota
,P.PagosCapital,P.AcumCuotas,P.IdVend,P.TimeSys,P.FecCorte,-1,0,0,0,P.TipDoc,P.NumPrestRef,ISNULL(P.CiaPrestRef,''),P.TipCom,P.Comprobante,P.IdCiaCom,P.Anulado,P.NumDev,P.FecDev,0,0,0,0,0,0,0,0
FROM Trn_FinPrestamo AS P LEFT JOIN (SELECT A.TipDoc,A.IdPrestamo,A.IdCiaPre,SUM(A.TotalAbono) AS VrAbono
FROM Trn_FinAbonos AS A INNER JOIN Trn_FinCuotas AS C ON A.TipDoc=C.TipDoc AND A.IdPrestamo=C.IdPrestamo AND A.IdCiaPre=C.IdCia AND A.ItemPre=C.Item
WHERE C.Concepto='CAPITAL' AND A.IdCliente LIKE ISNULL(@pmIdCliente,'%')
GROUP BY A.TipDoc,A.IdPrestamo,A.IdCiaPre) AS AB ON P.TipDoc=AB.TipDoc AND P.IdPrestamo=AB.IdPrestamo AND P.IdCia=AB.IdCiaPre
WHERE P.FecPrestamo BETWEEN @pmFechaIni AND @pmFechaFin AND P.CauAnticipado=0 AND P.Refinanciado=1 AND P.NumPrestRef>0
 AND P.IdCliente LIKE ISNULL(@pmIdCliente,'%') AND P.IdCia LIKE ISNULL(@pmIdCia,'%%')
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paInstm_PreExtoDpa] @pmtmEst CHAR(2),@pmtmTipoRep INT,@pmFechaIni SMALLDATETIME,@pmFechaFin SMALLDATETIME,@pmIdCliente VARCHAR(16)=Null
,@pmIdCia CHAR(2)=Null
 AS 
 INSERT INTO tm_PreExto (tmEst,tmTipoRep,tmTipDoc,tmNumero,tmIdCia,tmItem,tmFecha,tmIdCliente,tmIdAgencia,tmCargos,tmAbonos,tmIdLinea,tmConcepto,tmDetalle,tmReferencia,tmCredito,tmVrPrestamo,tmVrCuota
,tmTotAbonos,tmNCuota,tmIdVend,tmTimeSys,tmFecVence,tmTipoAbono,tmDTF_EA,tmTasaNom,tmTasaEfe,tmTipRef,tmDocRef,tmIdCiaRef,tmTipCom,tmComprobante,tmIdCiaCom,tmAnulado,tmNumDev,tmFecDev
,tmSanClie,tmSanCia,tmSanAgenc,tmSanAgeCia,tmSanLinea,tmSanLineaCia,tmSanConc,tmSanConcCia)
SELECT @pmtmEst,@pmtmTipoRep,A.TipRec,A.Recibo,A.IdCia,A.Item,A.Fecha,A.IdCliente,A.IdAgencia,0,A.TotalAbono,ISNULL(C.CodLinCred,P.IdLinea),C.Concepto,A.Detalle,C.NumFactura,P.TipoCredito,P.VrPrestamo,P.VrCuota
,PagosCapital,AcumCuotas,P.IdVend,A.TimeSys,D.FecDoc,-1,0,0,0,A.TipDoc,A.IdPrestamo,A.IdCiaPre,D.TipCom,D.Comprobante,D.IdCiaCom,0,0,Null
,0,0,0,0,0,0,0,0
 FROM Trn_FinAbonos AS A INNER JOIN Trn_FinCuotas AS C ON A.TipDoc=C.TipDoc AND A.IdPrestamo=C.IdPrestamo AND A.IdCiaPre=C.IdCia AND A.ItemPre=C.Item
 INNER JOIN Trn_FinPrestamo AS P ON A.TipDoc=P.TipDoc AND A.IdPrestamo=P.IdPrestamo AND A.IdCiaPre=P.IdCia
 INNER JOIN Trn_FinDevPre AS D ON A.TipRec=D.TipDev AND A.Recibo=D.Devolucion AND A.IdCia=D.IdCia
 WHERE A.Fecha BETWEEN @pmFechaIni AND @pmFechaFin AND A.TipRec='DP1' AND P.CauAnticipado=1
 AND A.IdCliente LIKE ISNULL(@pmIdCliente,'%') AND A.IdCia LIKE ISNULL(@pmIdCia,'%%')
GO