--Elimina las tablas temporales de reportes auxiliares contables --y vuelve a crear las estructuras. if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQrytm_MovCue]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) DROP PROCEDURE [dbo].[paQrytm_MovCue] GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQrytm_NiifMov]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) DROP PROCEDURE [dbo].[paQrytm_NiifMov] GO DELETE FROM tm_MovCue GO DROP TABLE tm_MovCue GO DELETE FROM tm_NiifMov GO DROP TABLE tm_NiifMov GO CREATE TABLE tm_MovCue ( Id BIGINT IDENTITY ( 1,1 ) NOT NULL, tmEst CHAR(2) DEFAULT ('01') NOT NULL, TipCom VARCHAR(3) NOT NULL, Comprobante INT DEFAULT ((0)) NOT NULL, IdCia CHAR(2) DEFAULT ('01') NOT NULL, Item INT DEFAULT ((0)) NOT NULL, Fecha SMALLDATETIME NOT NULL, IdCuenta VARCHAR(16) NOT NULL, Detalle VARCHAR(250), VrDebito MONEY DEFAULT ((0)) NOT NULL, VrCredito MONEY DEFAULT ((0)) NOT NULL, IdTercero VARCHAR(16) NOT NULL, IdVehiculo VARCHAR(10) DEFAULT ('0') NOT NULL, IdCCosto VARCHAR(16) DEFAULT ('0') NOT NULL, IdSubCos VARCHAR(16) DEFAULT ('0') NOT NULL, VrBase MONEY DEFAULT ((0)) NOT NULL, TarifaBase DECIMAL(14,4) DEFAULT ((0)) NOT NULL, TipDoc VARCHAR(3), Documento INT DEFAULT ((0)) NOT NULL, IdCiaDoc CHAR(2) DEFAULT ('01'), CodConce VARCHAR(4), NitDoc VARCHAR(16), TipFac VARCHAR(3), Factura VARCHAR(15), IdCiaFac CHAR(2) DEFAULT ('01'), ItemFac INT DEFAULT ((0)) NOT NULL, FecVence SMALLDATETIME, CodCta VARCHAR(4), NumCheque VARCHAR(20), Integrado BIT DEFAULT ((0)) NOT NULL, TipoAplica CHAR(1) DEFAULT ('N'), Consolida BIT DEFAULT ((0)) NOT NULL, CodCargo VARCHAR(4), NitOtros VARCHAR(16), CodSubgpo VARCHAR(8), CiuOrigen VARCHAR(8), CodAgncia VARCHAR(16) DEFAULT ('0') NOT NULL, VehPropio INT DEFAULT ((0)) NOT NULL, Referncia VARCHAR(50), TipDocRef VARCHAR(3), DocRef INT DEFAULT ((0)) NOT NULL, IdCiaRef CHAR(2) DEFAULT ('01'), VrSanCue MONEY DEFAULT ((0)) NOT NULL, VrSanCueCia MONEY DEFAULT ((0)) NOT NULL, VrSanNit MONEY DEFAULT ((0)) NOT NULL, VrSanNitCia MONEY DEFAULT ((0)) NOT NULL, VrSanVeh MONEY DEFAULT ((0)) NOT NULL, VrSanVehCia MONEY DEFAULT ((0)) NOT NULL, VrSanCc MONEY DEFAULT ((0)) NOT NULL, VrSanCcCia MONEY DEFAULT ((0)) NOT NULL, VrSanCcSub MONEY DEFAULT ((0)) NOT NULL, VrSanCcSubCia MONEY DEFAULT ((0)) NOT NULL, VrSanNitCc MONEY DEFAULT ((0)) NOT NULL, VrSanNitCcCia MONEY DEFAULT ((0)) NOT NULL, VrSanNitCcSub MONEY DEFAULT ((0)) NOT NULL, VrSanNitCcSubCia MONEY DEFAULT ((0)) NOT NULL, VrSanNitAge MONEY DEFAULT ((0)) NOT NULL, VrSanNitAgeCia MONEY DEFAULT ((0)) NOT NULL, VrSanNitAgeCc MONEY DEFAULT ((0)) NOT NULL, VrSanNitAgeCcCia MONEY DEFAULT ((0)) NOT NULL, VrSanNitAgeCcSub MONEY DEFAULT ((0)) NOT NULL, VrSanNitAgeCcSubCia MONEY DEFAULT ((0)) NOT NULL, VrSanNitVeh MONEY DEFAULT ((0)) NOT NULL, VrSanNitVehCia MONEY DEFAULT ((0)) NOT NULL, VrSanNitVehCc MONEY DEFAULT ((0)) NOT NULL, VrSanNitVehCcCia MONEY DEFAULT ((0)) NOT NULL, VrSanVehCc MONEY DEFAULT ((0)) NOT NULL, VrSanVehCcCia MONEY DEFAULT ((0)) NOT NULL, VrSanVehCcSub MONEY DEFAULT ((0)) NOT NULL, VrSanVehCcSubCia MONEY DEFAULT ((0)) NOT NULL CONSTRAINT PK_tm_MovCue PRIMARY KEY NONCLUSTERED (Id), CONSTRAINT CK_tm_MovCueCodAgncia CHECK ((len([CodAgncia])>(0))), CONSTRAINT CK_tm_MovCueIdCCosto CHECK ((len([IdCCosto])>(0))), CONSTRAINT CK_tm_MovCueIdCia CHECK ((len([IdCia])>(0))), CONSTRAINT CK_tm_MovCueIdCuenta CHECK ((len([IdCuenta])>(0))), CONSTRAINT CK_tm_MovCueIdSubCos CHECK ((len([IdSubCos])>(0))), CONSTRAINT CK_tm_MovCueIdTercero CHECK ((len([IdTercero])>(0))), CONSTRAINT CK_tm_MovCueIdVehiculo CHECK ((len([IdVehiculo])>(0))), CONSTRAINT CK_tm_MovCueTipCom CHECK ((len([TipCom])>(0))), CONSTRAINT CK_tm_MovCuetmEst CHECK ((len([tmEst])>(0)))) GO CREATE TABLE tm_NiifMov ( Id BIGINT IDENTITY ( 1,1 ) NOT NULL, tmEst CHAR(2) DEFAULT ('01') NOT NULL, TipCom VARCHAR(3) NOT NULL, Comprobante INT DEFAULT ((0)) NOT NULL, IdCia CHAR(2) DEFAULT ('01') NOT NULL, Item INT DEFAULT ((0)) NOT NULL, Fecha SMALLDATETIME NOT NULL, IdCuenta VARCHAR(16) NOT NULL, Detalle VARCHAR(250), VrDebito MONEY DEFAULT ((0)) NOT NULL, VrCredito MONEY DEFAULT ((0)) NOT NULL, IdTercero VARCHAR(16) NOT NULL, IdVehiculo VARCHAR(10) DEFAULT ('0') NOT NULL, IdCCosto VARCHAR(16) DEFAULT ('0') NOT NULL, IdSubCos VARCHAR(16) DEFAULT ('0') NOT NULL, VrBase MONEY DEFAULT ((0)) NOT NULL, TarifaBase DECIMAL(14,4) DEFAULT ((0)) NOT NULL, TipDoc VARCHAR(3), Documento INT DEFAULT ((0)) NOT NULL, IdCiaDoc CHAR(2) DEFAULT ('01'), CodConce VARCHAR(4), NitDoc VARCHAR(16), TipFac VARCHAR(3), Factura VARCHAR(15), IdCiaFac CHAR(2) DEFAULT ('01'), ItemFac INT DEFAULT ((0)) NOT NULL, FecVence SMALLDATETIME, CodCta VARCHAR(4), NumCheque VARCHAR(20), Integrado BIT DEFAULT ((0)) NOT NULL, TipoAplica CHAR(1) DEFAULT ('N'), Consolida BIT DEFAULT ((0)) NOT NULL, CodCargo VARCHAR(4), NitOtros VARCHAR(16), CodSubgpo VARCHAR(8), CiuOrigen VARCHAR(8), CodAgncia VARCHAR(16) DEFAULT ('0') NOT NULL, VehPropio INT DEFAULT ((0)) NOT NULL, Referncia VARCHAR(50), TipDocRef VARCHAR(3), DocRef INT DEFAULT ((0)) NOT NULL, IdCiaRef CHAR(2) DEFAULT ('01'), VrSanCue MONEY DEFAULT ((0)) NOT NULL, VrSanCueCia MONEY DEFAULT ((0)) NOT NULL, VrSanNit MONEY DEFAULT ((0)) NOT NULL, VrSanNitCia MONEY DEFAULT ((0)) NOT NULL, VrSanVeh MONEY DEFAULT ((0)) NOT NULL, VrSanVehCia MONEY DEFAULT ((0)) NOT NULL, VrSanCc MONEY DEFAULT ((0)) NOT NULL, VrSanCcCia MONEY DEFAULT ((0)) NOT NULL, VrSanCcSub MONEY DEFAULT ((0)) NOT NULL, VrSanCcSubCia MONEY DEFAULT ((0)) NOT NULL, VrSanNitCc MONEY DEFAULT ((0)) NOT NULL, VrSanNitCcCia MONEY DEFAULT ((0)) NOT NULL, VrSanNitCcSub MONEY DEFAULT ((0)) NOT NULL, VrSanNitCcSubCia MONEY DEFAULT ((0)) NOT NULL, VrSanNitAge MONEY DEFAULT ((0)) NOT NULL, VrSanNitAgeCia MONEY DEFAULT ((0)) NOT NULL, VrSanNitAgeCc MONEY DEFAULT ((0)) NOT NULL, VrSanNitAgeCcCia MONEY DEFAULT ((0)) NOT NULL, VrSanNitAgeCcSub MONEY DEFAULT ((0)) NOT NULL, VrSanNitAgeCcSubCia MONEY DEFAULT ((0)) NOT NULL, VrSanNitVeh MONEY DEFAULT ((0)) NOT NULL, VrSanNitVehCia MONEY DEFAULT ((0)) NOT NULL, VrSanNitVehCc MONEY DEFAULT ((0)) NOT NULL, VrSanNitVehCcCia MONEY DEFAULT ((0)) NOT NULL, VrSanVehCc MONEY DEFAULT ((0)) NOT NULL, VrSanVehCcCia MONEY DEFAULT ((0)) NOT NULL, VrSanVehCcSub MONEY DEFAULT ((0)) NOT NULL, VrSanVehCcSubCia MONEY DEFAULT ((0)) NOT NULL, CodCuentaCG VARCHAR(16) CONSTRAINT PK_tm_NiifMov PRIMARY KEY NONCLUSTERED (Id), CONSTRAINT CK_tm_NiifMovCodAgncia CHECK ((len([CodAgncia])>(0))), CONSTRAINT CK_tm_NiifMovIdCCosto CHECK ((len([IdCCosto])>(0))), CONSTRAINT CK_tm_NiifMovIdCia CHECK ((len([IdCia])>(0))), CONSTRAINT CK_tm_NiifMovIdCuenta CHECK ((len([IdCuenta])>(0))), CONSTRAINT CK_tm_NiifMovIdSubCos CHECK ((len([IdSubCos])>(0))), CONSTRAINT CK_tm_NiifMovIdTercero CHECK ((len([IdTercero])>(0))), CONSTRAINT CK_tm_NiifMovIdVehiculo CHECK ((len([IdVehiculo])>(0))), CONSTRAINT CK_tm_NiifMovTipCom CHECK ((len([TipCom])>(0))), CONSTRAINT CK_tm_NiifMovtmEst CHECK ((len([tmEst])>(0)))) GO CREATE CLUSTERED INDEX IX_tm_MovCueIdCuenta ON tm_MovCue(tmEst,IdCuenta,Id) CREATE CLUSTERED INDEX IX_tm_NiifMovIdCuenta ON tm_NiifMov(tmEst,IdCuenta,Id) SET ANSI_NULLS OFF GO SET QUOTED_IDENTIFIER OFF GO CREATE PROCEDURE [dbo].[paQrytm_MovCue] @pmtmEst CHAR(2),@pmIdCia CHAR(2)=Null,@pmIdCuenta VARCHAR(16)=Null,@pmIdTercero VARCHAR(16)=Null ,@pmIdCCosto VARCHAR(16)=Null AS SELECT C.IdCuenta AS CdCuenta,NomCuenta,TipCom,TipoCom,Comprobante,C.IdCia AS CdCia,Compania,Item,Fecha,Detalle,VrDebito,VrCredito,C.IdTercero AS NitTercero,T.RazonSocial AS RaznSocial ,IdVehiculo,C.IdCCosto AS CdCenCosto,CCosto,C.IdSubCos AS CdSubcentro,SubCosto,VrBase,TarifaBase,TipDoc,TipoDoc,Documento,IdCiaDoc,CodConce,Concepto ,NitDoc,ND.RazonSocial AS NomNitDoc,TipFac,Factura,IdCiaFac,ItemFac,FecVence,CodCta,NumeroCta,CTE.IdBanco AS CodBanco,Banco ,NumCheque,Integrado,TipoAplica,Consolida,CodCargo,NitOtros,NIO.RazonSocial AS NomNitOtros,CodSubgpo,Subgrupo,CiuOrigen,LD.Localidad AS NomCiudad ,CodAgncia,A.Agencia AS NomAgencia,VehPropio,Referncia,TipDocRef,DocRef,IdCiaRef,VrSanCue,VrSanCueCia,VrSanNit,VrSanNitCia,VrSanVeh,VrSanVehCia ,VrSanCc,VrSanCcCia,VrSanCcSub,VrSanCcSubCia,VrSanNitCc,VrSanNitCcCia,VrSanNitCcSub,VrSanNitCcSubCia,VrSanNitAge,VrSanNitAgeCia ,VrSanNitAgeCc,VrSanNitAgeCcCia,VrSanNitAgeCcSub,VrSanNitAgeCcSubCia,VrSanNitVeh ,VrSanNitVehCia,VrSanNitVehCc,VrSanNitVehCcCia,VrSanVehCc,VrSanVehCcCia,VrSanVehCcSub,VrSanVehCcSubCia,tmEst ,TC.IdDiario AS CdDiario,Diario --Información del tercero ,T.TipoId AS TercTipo,T.Dv AS TercDv,T.NomCial AS TercNomCial,T.SiglaRaz AS TercSigal,T.Direccion AS TercDireccion ,T.IdLocal AS TercCdCiudad,L.Localidad AS TercCiudad,L.IdDep AS CdDep,Departamento,T.Telefono AS TercTelefono,T.TipEnte AS TercTipEnte ,CodAgencia,DirAgncia,Referencia FROM tm_MovCue AS C INNER JOIN Terceros AS T ON C.IdTercero=T.IdTercero INNER JOIN Localidades AS L ON T.IdLocal=L.IdLocal INNER JOIN Departamentos AS DP ON L.IdDep=DP.IdDep INNER JOIN Puc AS P ON C.IdCuenta=P.IdCuenta INNER JOIN CentroCosto AS O ON C.IdCCosto=O.IdCCosto LEFT JOIN TiposCom AS TC ON C.TipCom=TC.IdCom LEFT JOIN Diarios AS DR ON TC.IdDiario=DR.IdDiario LEFT JOIN Companias AS CI ON C.IdCia=CI.IdCia LEFT JOIN SubCentros AS SC ON C.IdSubCos=SC.IdSubCos LEFT JOIN Terceros AS NIO ON C.NitOtros=NIO.IdTercero LEFT JOIN Terceros AS ND ON C.NitDoc=ND.IdTercero LEFT JOIN Conceptos AS CN ON C.CodConce=CN.IdConcepto LEFT JOIN Localidades AS LD ON C.CiuOrigen=LD.IdLocal LEFT JOIN SubGrupos AS SG ON C.CodSubgpo=SG.IdSubgrupo LEFT JOIN Agencias AS A ON C.CodAgncia=A.IdAgencia LEFT JOIN Sys_TiposDoc AS TD ON C.TipDoc=TD.IdDoc LEFT JOIN CtasCorrientes AS CTE ON C.CodCta=CTE.IdCta LEFT JOIN Bancos AS B ON CTE.IdBanco=B.IdBanco WHERE tmEst=@pmtmEst -- AND C.IdCia LIKE ISNULL(@pmIdCia,'%%') AND C.IdCuenta LIKE ISNULL(@pmIdCuenta,'%') -- AND C.IdTercero LIKE ISNULL(@pmIdTercero ,'%') AND C.IdCCosto LIKE ISNULL(@pmIdCCosto,'%') --ORDER BY C.IdCuenta,Fecha,C.IdCia,TipCom GO SET ANSI_NULLS OFF GO SET QUOTED_IDENTIFIER OFF GO CREATE PROCEDURE [dbo].[paQrytm_NiifMov] @pmtmEst CHAR(2),@pmIdCia CHAR(2)=Null,@pmIdCuenta VARCHAR(16)=Null,@pmIdTercero VARCHAR(16)=Null ,@pmIdCCosto VARCHAR(16)=Null AS SELECT C.IdCuenta AS CdCuenta,NomCuenta,TipCom,TipoCom,Comprobante,C.IdCia AS CdCia,Compania,Item,Fecha,Detalle,VrDebito,VrCredito,C.IdTercero AS NitTercero,T.RazonSocial AS RaznSocial ,IdVehiculo,C.IdCCosto AS CdCenCosto,CCosto,C.IdSubCos AS CdSubcentro,SubCosto,VrBase,TarifaBase,TipDoc,TipoDoc,Documento,IdCiaDoc,CodConce,Concepto ,NitDoc,ND.RazonSocial AS NomNitDoc,TipFac,Factura,IdCiaFac,ItemFac,FecVence,CodCta,NumeroCta,CTE.IdBanco AS CodBanco,Banco ,NumCheque,Integrado,TipoAplica,Consolida,CodCargo,NitOtros,NIO.RazonSocial AS NomNitOtros,CodSubgpo,Subgrupo,CiuOrigen,LD.Localidad AS NomCiudad ,CodAgncia,A.Agencia AS NomAgencia,VehPropio,Referncia,TipDocRef,DocRef,IdCiaRef,CodCuentaCG,VrSanCue,VrSanCueCia,VrSanNit,VrSanNitCia,VrSanVeh,VrSanVehCia ,VrSanCc,VrSanCcCia,VrSanCcSub,VrSanCcSubCia,VrSanNitCc,VrSanNitCcCia,VrSanNitCcSub,VrSanNitCcSubCia,VrSanNitAge,VrSanNitAgeCia ,VrSanNitAgeCc,VrSanNitAgeCcCia,VrSanNitAgeCcSub,VrSanNitAgeCcSubCia,VrSanNitVeh ,VrSanNitVehCia,VrSanNitVehCc,VrSanNitVehCcCia,VrSanVehCc,VrSanVehCcCia,VrSanVehCcSub,VrSanVehCcSubCia,tmEst ,TC.IdDiario AS CdDiario,Diario --Información del tercero ,T.TipoId AS TercTipo,T.Dv AS TercDv,T.NomCial AS TercNomCial,T.SiglaRaz AS TercSigal,T.Direccion AS TercDireccion ,T.IdLocal AS TercCdCiudad,L.Localidad AS TercCiudad,L.IdDep AS CdDep,Departamento,T.Telefono AS TercTelefono,T.TipEnte AS TercTipEnte ,CodAgencia,DirAgncia,Referencia FROM tm_NiifMov AS C INNER JOIN Terceros AS T ON C.IdTercero=T.IdTercero INNER JOIN Localidades AS L ON T.IdLocal=L.IdLocal INNER JOIN Departamentos AS DP ON L.IdDep=DP.IdDep INNER JOIN PucNiif AS P ON C.IdCuenta=P.IdCuenta INNER JOIN CentroCosto AS O ON C.IdCCosto=O.IdCCosto LEFT JOIN TiposCom AS TC ON C.TipCom=TC.IdCom LEFT JOIN Diarios AS DR ON TC.IdDiario=DR.IdDiario LEFT JOIN Companias AS CI ON C.IdCia=CI.IdCia LEFT JOIN SubCentros AS SC ON C.IdSubCos=SC.IdSubCos LEFT JOIN Terceros AS NIO ON C.NitOtros=NIO.IdTercero LEFT JOIN Terceros AS ND ON C.NitDoc=ND.IdTercero LEFT JOIN Conceptos AS CN ON C.CodConce=CN.IdConcepto LEFT JOIN Localidades AS LD ON C.CiuOrigen=LD.IdLocal LEFT JOIN SubGrupos AS SG ON C.CodSubgpo=SG.IdSubgrupo LEFT JOIN Agencias AS A ON C.CodAgncia=A.IdAgencia LEFT JOIN Sys_TiposDoc AS TD ON C.TipDoc=TD.IdDoc LEFT JOIN CtasCorrientes AS CTE ON C.CodCta=CTE.IdCta LEFT JOIN Bancos AS B ON CTE.IdBanco=B.IdBanco WHERE tmEst=@pmtmEst --AND C.IdCia LIKE ISNULL(@pmIdCia,'%%') AND C.IdCuenta LIKE ISNULL(@pmIdCuenta,'%') --AND C.IdTercero LIKE ISNULL(@pmIdTercero ,'%') AND C.IdCCosto LIKE ISNULL(@pmIdCCosto,'%') -- ORDER BY C.IdCuenta,Fecha,C.IdCia,TipCom GO