if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInsFormaspago]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[paInsFormaspago]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryFinPrestamoDsb]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[paQryFinPrestamoDsb]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryFinPrestResEgr]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[paQryFinPrestResEgr]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryFormaspago]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[paQryFormaspago]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryLineasCredCueLta]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[paQryLineasCredCueLta]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQrytm_BalComBases]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[paQrytm_BalComBases]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paUpFormaspago]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[paUpFormaspago]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [paQryFinPrestamoDsb] @pmTipDoc VARCHAR(3),@pmIdCia CHAR(2)=Null,@pmIdCliente VARCHAR(16)=Null,@pmTipoCredito VARCHAR(10)=Null
,@pmIdLinea VARCHAR(4)=Null
AS 
 SELECT P.IdPrestamo AS NumPrest,P.IdCia AS CdCia,Compania,P.Fecha AS FecRadica,FecPrestamo,FecCorte
,T.RazonSocial AS NomCliente,IdCliente,NomAgencia AS Agencia,P.IdAgencia AS CdAgencia,P.IdConcepto AS CdConcepto,Concepto
,VrPrestamo,VrSeguro,VrIntereses,VrNeto,VrCuota,P.IdLinea AS CdLinea,LinCredito,P.IdTasa AS CdTasa,DescTasa,DTF_EA,Spread_TA,TasaEfectiva
,NPlazos,TipoPlazo,Causacion,TipoCredito,TipoCausac,P.CalcIntMora AS CalcMora,P.IdMora AS CdMora,DescMora,TasaEA,TasaEM,NumCredito,P.IdVend AS CdVend,VN.RazonSocial AS NomAsesor
,CxPagar,NumCuotaIni,AcumCapital,AcumIntereses,AcumIntMora,AcumCuotas,PagosCapital,PagosIntereses,PagosIntMora,CuotasPagadas,CuotasMora
,CuotasConv,TipoAprob,Refinanciado,NumPrestRef,CiaPrestRef,VrRefinanc,DescRefinanc,CauAnticipado,P.IdEstado AS CdEstado,Estado,P.Observacion AS Observ
,Anulado,NumDev,FecDev
,TipEgr,NumEgreso,IdCiaEgr,FecEgreso,VrEgreso
--cliente
,T.TipoId AS TercTipo,T.Dv AS TercDv,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.e_mail AS TercEmail,CLI.IdSzona AS CdSubzona,Subzona,SZ.IdZona AS CdZona,Zona,CLI.IdGrupo AS CdGrupoCli,GrupoClie
,CLI.IdTipoTerc AS CdTipoTerc,TipoTercero,EsExento,FecIngreso,VrSalBasico,DirAgencia,CLI.IdLocalAge AS AgeIdCiudad
,LA.Localidad AS AgeCiudad,LA.IdDep AS AgeCodDep,DA.Departamento AS AgeDpto
 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 EstadoDoc AS ED ON P.IdEstado=ED.IdEstado 
 INNER JOIN TasasInteres AS TI ON P.IdTasa=TI.IdTasa
 INNER JOIN Terceros AS T ON P.IdCliente=T.IdTercero  INNER JOIN Terceros AS VN ON P.IdVend=VN.IdTercero
 INNER JOIN Localidades AS L ON T.IdLocal=L.IdLocal INNER JOIN Departamentos AS D ON L.IdDep=D.IdDep 
 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 Localidades AS LA ON  CLI.IdLocalAge=LA.IdLocal  LEFT JOIN Departamentos AS DA ON LA.IdDep=DA.IdDep 
 LEFT JOIN LineasCred AS LC ON P.IdLinea=LC.IdLinea
 LEFT JOIN Trn_FinPrestEgreso AS EG ON P.TipDoc=EG.TipDoc AND P.IdPrestamo=EG.IdPrestamo AND P.IdCia=EG.IdCia
 LEFT JOIN TasasIntmora AS TIM ON P.IdMora=TIM.IdMora
 WHERE P.TipDoc=@pmTipDoc AND P.IdCia LIKE ISNULL(@pmIdCia,'%%') AND P.IdCliente LIKE ISNULL(@pmIdCliente,'%') 
 AND P.TipoCredito LIKE ISNULL(@pmTipoCredito,'%') AND P.IdLinea LIKE ISNULL(@pmIdLinea,'%')

GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [paQryFinPrestResEgr]  @pmIdCia CHAR(2)=Null,@pmIdLinea VARCHAR(4)=Null,@pmIdCliente VARCHAR(16)=Null
,@pmTipoCredito VARCHAR(10)=Null
 AS 
 SELECT T.RazonSocial AS NomCliente,IdCliente,NomAgencia AS Agencia,P.IdAgencia AS CdAgencia,P.IdPrestamo AS NumPrest,P.IdCia AS CdCia,Compania
,NumCredito,P.Fecha AS FecRadica,FecPrestamo,FecCorte,DiaCausac,DiaCausac2,P.DiasVence AS DiasVce,P.TipoAprob AS TipAprob,NActaJunta,VrPrestamo,VrSeguro,VrIntereses,VrNeto,VrCuota,NPlazos,P.TipoPlazo AS TipPlazo,Causacion
,P.IdLinea AS CdLinea,LinCredito,P.IdTasa AS CdTasa,DescTasa,AcumCapital,AcumIntereses,AcumIntMora,AcumCuotas,PagosCapital,PagosIntereses,PagosIntMora,CuotasPagadas,CuotasMora,NumCuotaIni
,TipoCredito,P.TipoCausac AS Tipo_Causac,P.CalcIntMora AS CalcMora,NitEmpConv,P.CuotasConv AS NCuoConv,P.IdVend AS CdVend,VN.RazonSocial AS NomAsesor,P.NContrato AS NumContrato,CdCiaCon
,P.TipoGarantia AS TipoGarant,P.DescGarantia AS Desc_Garant,P.VrGarantia AS ValGarant,P.Observacion AS Observ,Refinanciado,NumPrestRef,CiaPrestRef,VrRefinanc,DescRefinanc,P.IdConcepto AS CdConcepto,Concepto,P.IdEstado AS CdEstado,Estado
,p.Anulado,TI.TipoInteres AS Tipo_Int,TasaEfectiva,TipoPeriodo,CausaInteres,DTF_EA,Spread_TA,OpcCompra
,FA.Almacen 
--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,CupoCredito,EsExento,FecIngreso,VrSalBasico,DirAgencia,CLI.IdLocalAge AS AgeIdCiudad
,LA.Localidad AS AgeCiudad,LA.IdDep AS AgeCodDep,DA.Departamento AS AgeDpto
,PE.VrEgreso ,PE.FecEgreso As Fechaegreso,CE.NumCheque, CE.FecCheque,CE.FecDev, CE.IdCta,CP.NumeroCta, CP.IdCuenta AS CtaContBanco,CE.EnEfectivo  
 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 EstadoDoc AS ED ON P.IdEstado=ED.IdEstado 
 INNER JOIN TasasInteres AS TI ON P.IdTasa=TI.IdTasa
 INNER JOIN Terceros AS T ON P.IdCliente=T.IdTercero   INNER JOIN Terceros AS VN ON P.IdVend=VN.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 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 INNER JOIN LineasCred AS LC ON P.IdLinea=LC.IdLinea
 LEFT JOIN Localidades AS LA ON  CLI.IdLocalAge=LA.IdLocal  LEFT JOIN Departamentos AS DA ON LA.IdDep=DA.IdDep 
 LEFT JOIN TercClieLineas AS LCR ON P.IdCliente=LCR.IdClie AND P.IdAgencia=LCR.IdAgencia AND P.IdLinea=LCR.IdLinea
 LEFT JOIN FinAlmacenes AS FA ON P.CdAlmacen=FA.IdAlmacen
 LEFT JOIN Trn_FinPrestEgreso PE ON P.TipDoc=PE.TipDoc AND P.IdPrestamo =PE.IdPrestamo AND P.IdCia =PE.IdCia 
 LEFT JOIN Trn_Comprobantes CE ON PE.TipEgr =CE.TipCom AND PE.NumEgreso =CE.Comprobante AND PE.IdCiaEgr=CE.IdCia 
 LEFT JOIN CtasCorrientes CP ON CE.IdCta=CP.IdCta 
  WHERE  P.IdCia LIKE ISNULL(@pmIdCia,'%%')  AND P.IdCliente LIKE ISNULL(@pmIdCliente,'%')
 AND P.IdLinea LIKE ISNULL(@pmIdLinea,'%')  AND P.TipoCredito LIKE ISNULL(@pmTipoCredito,'%')  


GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [paUpFormaspago] @pmIdForma VARCHAR(4),@pmFormaPago VARCHAR(50),@pmConsecutivo INT,@pmCuadreCaja BIT
,@pmRefDcmto BIT,@pmRefNit BIT,@pmRefBco BIT,@pmRefFech BIT,@pmRefAtza BIT,@pmRefOtro BIT,@pmVldRangos BIT,@pmVleConsumo BIT,@pmInactivo BIT
 AS 
 UPDATE Formaspago SET FormaPago=@pmFormaPago,Consecutivo=@pmConsecutivo,CuadreCaja=@pmCuadreCaja,RefDcmto=@pmRefDcmto,RefNit=@pmRefNit
,RefBco=@pmRefBco,RefFech=@pmRefFech,RefAtza=@pmRefAtza,RefOtro=@pmRefOtro,VldRangos=@pmVldRangos,VleConsumo=@pmVleConsumo,Inactivo=@pmInactivo
 WHERE IdForma=@pmIdForma

GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [paQryFormaspago] @pmIdForma VARCHAR(4)
 AS 
 IF @pmIdForma IS NULL
  SELECT IdForma,FormaPago,Consecutivo,CuadreCaja,RefDcmto,RefNit,RefBco,RefFech,RefAtza,RefOtro,VldRangos,VleConsumo
  FROM Formaspago
  WHERE Inactivo=0
  ORDER BY Consecutivo
 ELSE
  SELECT IdForma,FormaPago,Consecutivo,CuadreCaja,RefDcmto,RefNit,RefBco,RefFech,RefAtza,RefOtro,VldRangos,VleConsumo,Inactivo
  FROM Formaspago
  WHERE IdForma=@pmIdForma

GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [paInsFormaspago] @pmIdForma VARCHAR(4),@pmFormaPago VARCHAR(50),@pmConsecutivo INT,@pmCuadreCaja BIT,@pmRefDcmto BIT
,@pmRefNit BIT,@pmRefBco BIT,@pmRefFech BIT,@pmRefAtza BIT,@pmRefOtro BIT,@pmVldRangos BIT,@pmVleConsumo BIT,@pmInactivo BIT
 AS 
 INSERT INTO Formaspago (IdForma,FormaPago,Consecutivo,CuadreCaja,RefDcmto,RefNit,RefBco,RefFech,RefAtza,RefOtro,VldRangos,VleConsumo,Inactivo)
 VALUES (@pmIdForma,@pmFormaPago,@pmConsecutivo,@pmCuadreCaja,@pmRefDcmto,@pmRefNit,@pmRefBco,@pmRefFech,@pmRefAtza
,@pmRefOtro,@pmVldRangos,@pmVleConsumo,@pmInactivo)


GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [paQryLineasCredCueLta] @pmIdLinea VARCHAR(4)=Null
AS 
SELECT LN.IdLinea AS CdLinea,LinCredito,LN.IdTipoTerc AS CdTipoTerc,TipoTercero,Concepto,IdCuentaDb,P.NomCuenta AS NomCueDebito,IdCuentaCr,PC.NomCuenta AS NomCueCredito
FROM LineasCredCue AS LN INNER JOIN LineasCred AS L ON LN.IdLinea=L.IdLinea INNER JOIN TiposTerc AS T ON LN.IdTipoTerc=T.IdTipoTerc
LEFT JOIN Puc AS P ON LN.IdCuentaDb=P.IdCuenta LEFT JOIN Puc AS PC ON LN.IdCuentaCr=PC.IdCuenta
WHERE LN.IdLinea LIKE ISNULL(@pmIdLinea,'%')



GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


CREATE PROCEDURE [paQrytm_BalComBases] @pmtmEst CHAR(2),@pmtmIdCuenta VARCHAR(16)=Null
,@pmtmIdTercero VARCHAR(16)=Null,@pmtmCodAgncia VARCHAR(16)=Null,@pmFechaIni SMALLDATETIME=Null,@pmFechaFin SMALLDATETIME=Null
,@pmIdCia CHAR(2)=Null
 AS 
 SELECT BC.tmIdCuenta AS IdCuent,NomCuenta,tmTipo,tmSaldoAnterior,tmTotalDebitos,tmTotalCreditos
,tmSaldoAnterior+tmTotalDebitos-tmTotalCreditos AS NuevoSaldo,tmVrPresupto
--bases acumuladas del año
,CASE P.BaseGravable WHEN 1 THEN ISNULL(CASE tmTipo WHEN 'N' THEN dbo.FuncComDetalleBasesNit(BC.tmIdCuenta,tmIdTercero,@pmFechaIni,@pmFechaFin,@pmIdCia)
WHEN 'NC' THEN dbo.FuncComDetalleBasesNitCc(BC.tmIdCuenta,tmIdTercero,tmIdCCosto,@pmFechaIni,@pmFechaFin,@pmIdCia)
WHEN 'NCS' THEN dbo.FuncComDetalleBasesNitCcSub(BC.tmIdCuenta,tmIdTercero,tmIdCCosto,tmIdSubCos,@pmFechaIni,@pmFechaFin,@pmIdCia)
WHEN 'CS' THEN dbo.FuncComDetalleBasesCcSub(BC.tmIdCuenta,tmIdCCosto,tmIdSubCos,@pmFechaIni,@pmFechaFin,@pmIdCia)
WHEN 'C' THEN dbo.FuncComDetalleBasesCc(BC.tmIdCuenta,tmIdCCosto,@pmFechaIni,@pmFechaFin,@pmIdCia)
ELSE 
 dbo.FuncComDetalleBasesCue (BC.tmIdCuenta,@pmFechaIni,@pmFechaFin,@pmIdCia)
END,0) ELSE 0 END AS TotalBase,Porcntaje
,tmIdTercero,RazonSocial,tmCodAgncia,A.Agencia AS NomAgencia,tmIdVehiculo,tmIdCCosto,CCosto,tmIdSubCos,SubCosto
--información del tercero
,TipoId,Dv,Direccion,T.IdLocal AS CdCiudad,Localidad,L.IdDep AS CdDepto,Departamento,Telefono,EsAccnista,EsCliente,EsPropietario 
--información de agencia y vehículos
,CodAgencia,Referencia--,NumVeh
,tmItem,tmNivel1,tmNivel2,tmNivel4,tmNivel6,tmNivel9,tmNivel12,Tercero,CentroCosto
 FROM tm_BalCom  AS  BC INNER JOIN Puc AS P ON BC.tmIdCuenta=P.IdCuenta
 INNER JOIN Terceros AS T ON BC.tmIdTercero=T.IdTercero
 INNER JOIN Localidades AS L ON T.IdLocal=L.IdLocal
 INNER JOIN Departamentos AS D ON L.IdDep=D.IdDep
 INNER JOIN CentroCosto AS CC ON BC.tmIdCCosto=CC.IdCCosto
 LEFT JOIN SubCentros AS SC ON BC.tmIdSubCos=SC.IdSubCos
 LEFT JOIN tm_NivCue AS NV ON BC.tmIdCuenta=NV.tmIdCuenta
 LEFT JOIN  Agencias AS A ON BC.tmCodAgncia=A.IdAgencia
-- LEFT JOIN Vehiculos AS V ON BC.tmIdVehiculo=V.IdVehiculo
 WHERE tmEst=@pmtmEst AND BC.tmIdCuenta LIKE ISNULL(@pmtmIdCuenta,'%')
 AND tmIdTercero LIKE ISNULL(@pmtmIdTercero,'%')
 AND tmCodAgncia LIKE ISNULL(@pmtmCodAgncia,'%')
 ORDER BY BC.tmIdCuenta,RazonSocial,tmCodAgncia,tmIdCCosto,tmItem