SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO




CREATE PROCEDURE [dbo].[paQryConciliacion] @pmNumeroIni INT,@pmNumeroFin INT
 AS 
 --movimiento
SELECT 'CONTAB' AS TipoReg,Item,TipCom,TipoCom,Comprobante,M.IdCia AS CdCia,Compania,ItemCom,M.Fecha AS FecMovi,M.IdCuenta AS CdCuenta,NomCuenta,Detalle,VrDebito,VrCredito
,M.IdTercero AS NitTercero,T.RazonSocial AS NomTercero,VrBase,TarifaBase,TipDoc,Documento,IdCiaDoc,NitDoc,ND.TipoId AS Tipo_Id,ND.RazonSocial AS NomTercDoc,ND.Dv AS DvNit
,CodCta,NumCheque,FecCheque,EnEfectivo,Beneficiario,Integrado,EsEgreso,NitOtros,CodCiudad,NomCiudad,Referencia,TipDocRef,DocRef,IdCiaRef,TimeSys,VrTotal,Anulado
,TipEgr,NEgreso,Observacion,M.Estado AS EstadoConc,ItemExto AS ItemConc,ValorExto AS ValorConc
--ENCABEZADO
,C.Numero AS NumConc,C.Fecha AS FechaConc,FecInicial,FecFinal,C.Estado AS EstadoReg,C.IdCta AS CodCta,NumeroCta,IdBanco,Banco,Cuenta,DescCuenta,Criterios,IdUsuario,NomUsuario
FROM Conciliacion AS C INNER JOIN ConcMovimiento AS M ON C.Numero=M.Numero
INNER JOIN Puc AS P ON M.IdCuenta=P.IdCuenta INNER JOIN Companias AS CN ON M.IdCia=CN.IdCia
LEFT JOIN TiposCom AS TC ON M.TipCom=TC.IdCom LEFT JOIN Terceros AS T ON M.IdTercero=T.IdTercero 
LEFT JOIN Terceros AS ND ON M.NitDoc=ND.IdTercero  
LEFT JOIN Cuentas AS CTA ON C.IdCta=CTA.IdCta
WHERE C.Numero BETWEEN @pmNumeroIni AND @pmNumeroFin
UNION
--EXTRACTO
SELECT 'EXTRACTO',Item,'0','',0,'00','',0,E.Fecha,'','',Concepto,CASE WHEN VrImporte>=0 THEN VrImporte ELSE 0 END,CASE WHEN VrImporte<0 THEN VrImporte*-1 ELSE 0 END
,NitTercero,RazonSocial,VrSaldo,0,'',0,'',NitTercero,T.TipoId,T.RazonSocial,T.Dv
,'',NumTrans,E.Fecha,0,'',0,0,'','','',Referencia,'',0,'',E.Fecha,0,0,'',0,'',E.Estado,ItemMovi,ValorMovi
,C.Numero,C.Fecha,FecInicial,FecFinal,C.Estado,C.IdCta,NumeroCta,IdBanco,Banco,Cuenta,DescCuenta,Criterios,IdUsuario,NomUsuario
FROM Conciliacion AS C LEFT JOIN ConcExtracto AS E ON C.Numero=E.Numero
LEFT JOIN Terceros AS T ON E.NitTercero=T.IdTercero LEFT JOIN Cuentas AS CTA ON C.IdCta=CTA.IdCta
WHERE C.Numero BETWEEN @pmNumeroIni AND @pmNumeroFin 


GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paInsConciliacion] @pmNumero INT,@pmFecha SMALLDATETIME,@pmFecInicial SMALLDATETIME,@pmFecFinal SMALLDATETIME,@pmEstado INT,@pmIdCta VARCHAR(4)
,@pmCuenta VARCHAR(50),@pmDescCuenta VARCHAR(50),@pmCriterios VARCHAR(250),@pmIdUsuario VARCHAR(11),@pmNomUsuario VARCHAR(50)
 AS 
 INSERT INTO Conciliacion (Numero,Fecha,FecInicial,FecFinal,Estado,IdCta,Cuenta,DescCuenta,Criterios,IdUsuario,NomUsuario)
 VALUES (@pmNumero,@pmFecha,@pmFecInicial,@pmFecFinal,@pmEstado,@pmIdCta,@pmCuenta,@pmDescCuenta,@pmCriterios,@pmIdUsuario,@pmNomUsuario)

GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paUpConciliacion] @pmNumero INT,@pmFecha SMALLDATETIME,@pmFecInicial SMALLDATETIME,@pmFecFinal SMALLDATETIME,@pmEstado INT,@pmIdCta VARCHAR(4)
,@pmCuenta VARCHAR(50),@pmDescCuenta VARCHAR(50),@pmCriterios VARCHAR(250),@pmIdUsuario VARCHAR(11),@pmNomUsuario VARCHAR(50)
 AS 
 UPDATE Conciliacion SET Fecha=@pmFecha,FecInicial=@pmFecInicial,FecFinal=@pmFecFinal,Estado=@pmEstado,IdCta=@pmIdCta,Cuenta=@pmCuenta,DescCuenta=@pmDescCuenta
,Criterios=@pmCriterios,IdUsuario=@pmIdUsuario,NomUsuario=@pmNomUsuario
 WHERE Numero=@pmNumero

GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


CREATE PROCEDURE [dbo].[paQryConcMovimiento] @pmNumeroIni INT,@pmNumeroFin INT
 AS 
 SELECT C.Numero AS NumConc,C.Fecha AS FechaConc,FecInicial,FecFinal,C.Estado AS EstadoReg,C.IdCta AS CodCta,NumeroCta
,IdBanco,Banco,Cuenta,DescCuenta,Criterios,IdUsuario,NomUsuario
--movimiento
,Item,TipCom,TipoCom,Comprobante,M.IdCia AS CdCia,Compania,ItemCom,M.Fecha AS FecMovi,M.IdCuenta AS CdCuenta,NomCuenta,Detalle,VrDebito,VrCredito,M.IdTercero AS NitTercero,T.RazonSocial AS NomTercero
,VrBase,TarifaBase,TipDoc,Documento,IdCiaDoc,NitDoc,ND.TipoId AS Tipo_Id,ND.RazonSocial AS NomTercDoc,ND.Dv AS DvNit,CodCta,NumCheque,FecCheque,EnEfectivo,Beneficiario
,Integrado,EsEgreso,NitOtros,CodCiudad,NomCiudad,Referencia,TipDocRef,DocRef,IdCiaRef,TimeSys,VrTotal,Anulado
,TipEgr,NEgreso,Observacion,M.Estado AS EstadoConc,ItemExto,ValorExto
FROM Conciliacion AS C INNER JOIN ConcMovimiento AS M ON C.Numero=M.Numero
INNER JOIN Puc AS P ON M.IdCuenta=P.IdCuenta INNER JOIN Companias AS CN ON M.IdCia=CN.IdCia
LEFT JOIN TiposCom AS TC ON M.TipCom=TC.IdCom LEFT JOIN Terceros AS T ON M.IdTercero=T.IdTercero 
LEFT JOIN Terceros AS ND ON M.NitDoc=ND.IdTercero  
LEFT JOIN Cuentas AS CTA ON C.IdCta=CTA.IdCta
WHERE C.Numero BETWEEN @pmNumeroIni AND @pmNumeroFin
 

GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


CREATE PROCEDURE [dbo].[paQryConcExtracto] @pmNumeroIni INT,@pmNumeroFin INT
 AS 
 SELECT C.Numero AS NumConc,C.Fecha AS FechaConc,FecInicial,FecFinal,C.Estado AS EstadoReg,C.IdCta AS CodCta,NumeroCta
,IdBanco,Banco,Cuenta,DescCuenta,Criterios,IdUsuario,NomUsuario
--extracto
,Item,E.Fecha AS FechaExto,Concepto,VrImporte,VrSaldo,Referencia,NitTercero,RazonSocial,TipoTrans,NumTrans
,E.Estado AS EstadoConc,ItemMovi,ValorMovi
FROM Conciliacion AS C LEFT JOIN ConcExtracto AS E ON C.Numero=E.Numero
LEFT JOIN Terceros AS T ON E.NitTercero=T.IdTercero 
LEFT JOIN Cuentas AS CTA ON C.IdCta=CTA.IdCta
WHERE C.Numero BETWEEN @pmNumeroIni AND @pmNumeroFin 

GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paInsConcMovimiento] @pmNumero INT,@pmItem INT,@pmTipCom VARCHAR(3),@pmComprobante INT,@pmIdCia CHAR(2),@pmItemCom INT,@pmFecha SMALLDATETIME,@pmIdCuenta VARCHAR(16),@pmDetalle VARCHAR(250),@pmVrDebito MONEY,@pmVrCredito MONEY,@pmIdTercero VARCHAR(16),@pmVrBase MONEY,@pmTarifaBase DECIMAL(16,6),@pmTipDoc VARCHAR(3),@pmDocumento INT,@pmIdCiaDoc CHAR(2),@pmNitDoc VARCHAR(16),@pmCodCta VARCHAR(4),@pmNumCheque VARCHAR(20),@pmFecCheque SMALLDATETIME
,@pmEnEfectivo BIT,@pmBeneficiario VARCHAR(250),@pmIntegrado BIT,@pmEsEgreso BIT,@pmNitOtros VARCHAR(16),@pmCodCiudad VARCHAR(8),@pmNomCiudad VARCHAR(50),@pmReferencia VARCHAR(50),@pmTipDocRef VARCHAR(3),@pmDocRef INT,@pmIdCiaRef CHAR(2),@pmTimeSys SMALLDATETIME,@pmVrTotal MONEY,@pmAnulado BIT,@pmTipEgr VARCHAR(3),@pmNEgreso INT,@pmObservacion VARCHAR(250),@pmEstado INT,@pmItemExto INT,@pmValorExto MONEY
 AS 
 INSERT INTO ConcMovimiento (Numero,Item,TipCom,Comprobante,IdCia,ItemCom,Fecha,IdCuenta,Detalle,VrDebito,VrCredito,IdTercero,VrBase,TarifaBase,TipDoc,Documento,IdCiaDoc,NitDoc,CodCta,NumCheque,FecCheque,EnEfectivo,Beneficiario,Integrado,EsEgreso,NitOtros,CodCiudad,NomCiudad,Referencia,TipDocRef,DocRef,IdCiaRef,TimeSys,VrTotal,Anulado,TipEgr,NEgreso,Observacion,Estado,ItemExto,ValorExto)
 VALUES (@pmNumero,@pmItem,@pmTipCom,@pmComprobante,@pmIdCia,@pmItemCom,@pmFecha,@pmIdCuenta,@pmDetalle,@pmVrDebito,@pmVrCredito,@pmIdTercero,@pmVrBase,@pmTarifaBase,@pmTipDoc,@pmDocumento,@pmIdCiaDoc,@pmNitDoc,@pmCodCta,@pmNumCheque,@pmFecCheque,@pmEnEfectivo,@pmBeneficiario,@pmIntegrado,@pmEsEgreso,@pmNitOtros,@pmCodCiudad,@pmNomCiudad,@pmReferencia,@pmTipDocRef,@pmDocRef,@pmIdCiaRef,@pmTimeSys,@pmVrTotal,@pmAnulado,@pmTipEgr,@pmNEgreso
,@pmObservacion,@pmEstado,@pmItemExto,@pmValorExto)
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[paInsInfCnxion] @pmIdReg INT,@pmCnxEngine VARCHAR(3),@pmCnxDso VARCHAR(250),@pmCnxDbname VARCHAR(50)
,@pmCnxAutentic VARCHAR(3),@pmCnxUid VARCHAR(50),@pmCnxPwd VARCHAR(50),@pmCnxOwner VARCHAR(20),@pmCnxApp VARCHAR(10)
,@pmCnxProv VARCHAR(50),@pmCnxVersn INT
 AS 
 INSERT INTO InfCnxion (IdReg,CnxEngine,CnxDso,CnxDbname,CnxAutentic,CnxUid,CnxPwd,CnxOwner,CnxApp,CnxProv,CnxVersn)
 VALUES (@pmIdReg,@pmCnxEngine,@pmCnxDso,@pmCnxDbname,@pmCnxAutentic,@pmCnxUid,@pmCnxPwd,@pmCnxOwner,@pmCnxApp,@pmCnxProv,@pmCnxVersn)

GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[paUpInfCnxion] @pmIdReg INT,@pmCnxEngine VARCHAR(3),@pmCnxDso VARCHAR(250),@pmCnxDbname VARCHAR(50),@pmCnxAutentic VARCHAR(3),@pmCnxUid VARCHAR(50),@pmCnxPwd VARCHAR(50)
,@pmCnxOwner VARCHAR(20),@pmCnxApp VARCHAR(10),@pmCnxProv VARCHAR(50),@pmCnxVersn INT
 AS 
 UPDATE InfCnxion SET CnxEngine=@pmCnxEngine,CnxDso=@pmCnxDso,CnxDbname=@pmCnxDbname,CnxAutentic=@pmCnxAutentic,CnxUid=@pmCnxUid,CnxPwd=@pmCnxPwd,CnxOwner=@pmCnxOwner
,CnxApp=@pmCnxApp,CnxVersn=@pmCnxVersn,CnxProv=@pmCnxProv
 WHERE IdReg=@pmIdReg

GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[paQryInfCnxion] @pmIdReg INT
 AS 
 SELECT IdReg,CnxEngine,CnxProv,CnxDso,CnxDbname,CnxAutentic,CnxUid,CnxPwd,CnxOwner,CnxApp,CnxVersn
 FROM InfCnxion
 WHERE IdReg=@pmIdReg

GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paInsConcExtracto] @pmNumero INT,@pmItem INT,@pmFecha SMALLDATETIME,@pmConcepto VARCHAR(250),@pmVrImporte MONEY,@pmVrSaldo MONEY,@pmReferencia VARCHAR(50)
,@pmNitTercero VARCHAR(16),@pmTipoTrans VARCHAR(20),@pmNumTrans VARCHAR(50),@pmEstado INT,@pmItemMovi INT,@pmValorMovi MONEY
 AS 
 INSERT INTO ConcExtracto (Numero,Item,Fecha,Concepto,VrImporte,VrSaldo,Referencia,NitTercero,TipoTrans,NumTrans,Estado,ItemMovi,ValorMovi)
 VALUES (@pmNumero,@pmItem,@pmFecha,@pmConcepto,@pmVrImporte,@pmVrSaldo,@pmReferencia,@pmNitTercero,@pmTipoTrans,@pmNumTrans,@pmEstado,@pmItemMovi,@pmValorMovi)