if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInsDiferidos]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paInsDiferidos]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInsNiifPoliticas]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paInsNiifPoliticas]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInstm_AfAmt]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paInstm_AfAmt]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInstm_AfAmt_Sel]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paInstm_AfAmt_Sel]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryDiferidos]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryDiferidos]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryDiferidosAmt]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryDiferidosAmt]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryDiferidosHis]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryDiferidosHis]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryDiferidosLta]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryDiferidosLta]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryNiifPoliticasLta]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryNiifPoliticasLta]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryNiifRevelacLta]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryNiifRevelacLta]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQrytm_AfAmtLta]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQrytm_AfAmtLta]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paUpDiferidos]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paUpDiferidos]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paUpNiifPoliticas]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paUpNiifPoliticas]
GO

SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[paQryDiferidosHis] @pmIdDiferido VARCHAR(30)
 AS 
 SELECT A.IdDiferido AS CdDiferido,NomDiferido,A.IdTipDif AS CdTipDif,TipoDiferido,G.IdLinea AS CodLinea,Linea,A.IdGrupo AS CodGrupo,Grupo,A.IdCCosto AS CdCentro
,CC.CCosto AS DifCentroCosto,A.IdSubCos AS CdSubcentro,SC.SubCosto AS DifSubCentro,A.IdDep AS CdDep,DP.Dependencia AS DifDependencia,DesUbicacion
,IdRespons,T.RazonSocial AS NomResponsable,A.IdCia AS CdCia,CIA.Compania AS DifNomCia,FecInicio,A.ValorTotal AS DifValTotal,A.VrAdiciones AS DifAdiciones,A.VrDisminucion AS DifDisminucion,NumPeriodos
,FecFinAmtza,Ajustable,Contable,VrAjuInfAcum,VrAmtzaAcuHis,VrAmtzaAcum,VrAjuInfAmtza,CuotasAmtza,VrCorrMonDifAcu,CorrMonDiferida,NumMesesDif
,FecUltAmtza,CueEnGrupo,IdCuenta,IdCueAmortiza,IdCueAjuDif,IdCueCorrDif,IdCueAjuAmtza,IdCueCorrAA,NumDoc,Referencia,A.IdEstado AS CdEstado,ED.Estado AS DifEstado
,FecRetiro,A.IdCausal AS CdCausal,Causal,ComentRetiro,Comentarios,TipoInvers,FecCreacion,A.IdUsuario AS IdUsuari
--DATOS DEL DIFERIDO
,AMZ.Numero AS NumRegistro,Fecha,AMZ.Estado AS EstadoDoc,AMZ.IdUsuario AS CdUsuario,Usuario,FechaCrea,AMZ.IdCia AS CdCia,CI.Compania AS NomCia
,Item,D.ValorTotal AS ValorInicial, D.VrAdiciones AS ValAdiciones, D.VrDisminucion AS ValDisminucion, VrAcuAjustes, VrAmortizaHis, VrAmortizaMes, VrAjustesInf
, VrAjustesAmtza,VrAmtzaHisAcum, VrAmortizaAcum, VrAjuAmtzaAcum, VrCorrMonAcum, VrBaseCorrMon, VrCorrMonDif, TarifaAju
,D.IdCia AS DetIdCia,CID.Compania AS DetNomCia,CdCCosto,CCD.CCosto AS NomCentroCosto,CdSubCos,SCD.SubCosto AS NomSubcentro, CdDep, DPD.Dependencia AS NomDepend
,NitRespons,TRD.RazonSocial AS DetResponsable,FecInicioAmtza, NPeriodos,TipoAmtza,NitCliente,AgenCliente,CantGalones
,IdProv,PV.RazonSocial AS NomProveedor,NumPlaca,CodInstala,Instlacion,TipoContable
 FROM Diferidos  AS A INNER JOIN AfGrupos AS G ON A.IdGrupo=G.IdGrupo INNER JOIN AfLineas AS L ON G.IdLinea=L.IdLinea
 INNER JOIN CentroCosto AS CC ON A.IdCCosto=CC.IdCCosto INNER JOIN AfTiposDif AS TD ON A.IdTipDif=TD.IdTipDif
 INNER JOIN Dependencias AS DP ON A.IdDep=DP.IdDep INNER JOIN Terceros AS T ON A.IdRespons=T.IdTercero
 INNER JOIN Companias AS CIA ON A.IdCia=CIA.IdCia  INNER JOIN Terceros AS PV ON A.IdProv=PV.IdTercero
 INNER JOIN AfEstados AS ED ON A.IdEstado=ED.IdEstado  
 LEFT JOIN SubCentros AS SC ON A.IdSubCos=SC.IdSubCos LEFT JOIN AfCausales AS CA ON A.IdCausal=CA.IdCausal
 LEFT JOIN Trn_AfAmtDetalle AS D ON A.IdDiferido=D.IdDiferido LEFT  JOIN Trn_AfAmortiza AS AMZ  ON D.Numero=AMZ.Numero
 LEFT JOIN Companias AS CI ON AMZ.IdCia=CI.IdCia  LEFT JOIN adm_Usuarios AS U ON AMZ.IdUsuario=U.IdUsuario
 LEFT JOIN Companias AS CID ON D.IdCia=CID.IdCia 
 LEFT JOIN CentroCosto AS CCD ON D.CdCCosto=CCD.IdCCosto 
 LEFT JOIN Dependencias AS DPD ON D.CdDep=DPD.IdDep LEFT JOIN Terceros AS TRD ON D.NitRespons=TRD.IdTercero
 LEFT JOIN SubCentros AS SCD ON D.CdSubCos=SCD.IdSubCos  LEFT JOIN Instalaciones AS I ON A.CodInstala=I.IdInstala
 WHERE A.IdDiferido LIKE ISNULL(@pmIdDiferido ,'%')
 ORDER BY NomDiferido
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[paQryDiferidosLta] @pmIdGrupo VARCHAR(4)=Null,@pmIdLinea VARCHAR(4)=Null,@pmIdCCosto VARCHAR(16)=Null,@pmIdTipDif VARCHAR(4)=Null
,@pmIdDep VARCHAR(4)=Null,@pmIdRespons VARCHAR(16)=Null,@pmIdCia CHAR(2)=Null,@pmFechaIni SMALLDATETIME=Null,@pmFechaFin SMALLDATETIME=Null
,@pmIdEstado VARCHAR(4)=Null
 AS 
 SELECT IdDiferido,NomDiferido,A.IdTipDif AS CdTipDif,TipoDiferido,G.IdLinea AS CodLinea,Linea,A.IdGrupo AS CodGrupo,Grupo,A.IdCCosto AS CdCentro,CCosto,A.IdSubCos AS CdSubcentro,SubCosto
,A.IdDep AS CdDep,Dependencia,DesUbicacion,IdRespons,T.RazonSocial  AS NomResponsable,A.IdCia AS CdCia,Compania,FecInicio,ValorTotal,VrAdiciones,VrDisminucion,NumPeriodos
,FecFinAmtza,Ajustable,Contable,VrAjuInfAcum,VrAmtzaAcuHis,VrAmtzaAcum,VrAjuInfAmtza,CuotasAmtza,VrCorrMonDifAcu,CorrMonDiferida,NumMesesDif
,FecUltAmtza,CueEnGrupo,IdCuenta,IdCueAmortiza,IdCueAjuDif,IdCueCorrDif,IdCueAjuAmtza,IdCueCorrAA,NumPlaca,NumDoc,Referencia,A.IdEstado AS CdEstado,Estado
,FecRetiro,A.IdCausal AS CdCausal,Causal,ComentRetiro,Comentarios,FecCreacion,A.IdUsuario AS IdUsuari,Usuario,TipoAmtza,NitCliente,AgenCliente,CantGalones
,IdProv,PV.RazonSocial AS NomProveedor,CodInstala,Instlacion,TipoInvers,TipoContable,CodNiifDif,CodNiifAmtza
--datos del responsable
,T.Codigo AS TercCodigo,T.TipoId AS TercTipoId,T.Dv AS TercDv,T.NomCial AS TercNomCial,T.SiglaRaz AS TercSigla,T.Direccion AS TercDirecc,T.IdLocal AS CdLocalidad,Localidad
,LC.IdDep AS Cd_Depto,Departamento,T.Telefono AS TercTelefono,T.e_mail AS TercEmail
 FROM Diferidos  AS A INNER JOIN AfGrupos AS G ON A.IdGrupo=G.IdGrupo INNER JOIN AfLineas AS L ON G.IdLinea=L.IdLinea
 INNER JOIN CentroCosto AS CC ON A.IdCCosto=CC.IdCCosto INNER JOIN AfTiposDif AS TD ON A.IdTipDif=TD.IdTipDif
 INNER JOIN Dependencias AS DP ON A.IdDep=DP.IdDep INNER JOIN Terceros AS T ON A.IdRespons=T.IdTercero
 INNER JOIN Companias AS CI ON A.IdCia=CI.IdCia INNER JOIN Terceros AS PV ON A.IdProv=PV.IdTercero
 INNER JOIN AfEstados AS ED ON A.IdEstado=ED.IdEstado  INNER JOIN adm_Usuarios AS U ON A.IdUsuario=U.IdUsuario
 INNER JOIN Localidades AS LC ON T.IdLocal=LC.IdLocal INNER JOIN Departamentos AS D ON LC.IdDep=D.IdDep
 LEFT JOIN SubCentros AS SC ON A.IdSubCos=SC.IdSubCos LEFT JOIN AfCausales AS CA ON A.IdCausal=CA.IdCausal
 LEFT JOIN Instalaciones AS I ON A.CodInstala=I.IdInstala
 WHERE A.IdGrupo LIKE ISNULL(@pmIdGrupo,'%') AND G.IdLinea LIKE ISNULL(@pmIdLinea,'%')
 AND A.IdCCosto LIKE ISNULL(@pmIdCCosto,'%') AND  A.IdTipDif LIKE ISNULL(@pmIdTipDif,'%')
 AND A.IdDep LIKE ISNULL(@pmIdDep,'%') AND IdRespons LIKE ISNULL(@pmIdRespons,'%')
 AND  A.IdCia LIKE ISNULL(@pmIdCia,'%%')
 AND (FecInicio>=ISNULL(@pmFechaIni,CAST('19100101' AS SMALLDATETIME)) AND FecInicio<=ISNULL(@pmFechaFin,CAST('20781230' AS SMALLDATETIME)))
 AND A.IdEstado LIKE ISNULL(@pmIdEstado,'%')
 ORDER BY NomDiferido
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paInsNiifPoliticas] @pmIdPolitica INT,@pmDescripcion VARCHAR(250),@pmClasePol VARCHAR(100),@pmObjetivo VARCHAR(1000),@pmAlcance VARCHAR(2500),@pmReconocimiento VARCHAR(2500),@pmMed_Inicial VARCHAR(2000)
,@pmMed_Final VARCHAR(2000),@pmPdoVigencia INT,@pmVigencia VARCHAR(250),@pmNotas VARCHAR(1000),@pmAnexo VARCHAR(150),@pmInactivo BIT,@pmFechaCrea SMALLDATETIME,@pmIdUsuario VARCHAR(11)
 AS 
 INSERT INTO Trn_NiifPoliticas (IdPolitica,Descripcion,ClasePol,Objetivo,Alcance,Reconocimiento,Med_Inicial,Med_Final,PdoVigencia,Vigencia,Notas,Anexo,Inactivo,FechaCrea,IdUsuario)
 VALUES (@pmIdPolitica,@pmDescripcion,@pmClasePol,@pmObjetivo,@pmAlcance,@pmReconocimiento,@pmMed_Inicial,@pmMed_Final,@pmPdoVigencia,@pmVigencia,@pmNotas,@pmAnexo,@pmInactivo,@pmFechaCrea,@pmIdUsuario)
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paUpNiifPoliticas] @pmIdPolitica INT,@pmDescripcion VARCHAR(250),@pmClasePol VARCHAR(100),@pmObjetivo VARCHAR(1000),@pmAlcance VARCHAR(2500),@pmReconocimiento VARCHAR(2500),@pmMed_Inicial VARCHAR(2000)
,@pmMed_Final VARCHAR(2000),@pmPdoVigencia INT,@pmVigencia VARCHAR(250),@pmNotas VARCHAR(1000),@pmAnexo VARCHAR(150),@pmInactivo BIT
 AS 
 UPDATE Trn_NiifPoliticas SET Descripcion=@pmDescripcion,ClasePol=@pmClasePol,Objetivo=@pmObjetivo,Alcance=@pmAlcance,Reconocimiento=@pmReconocimiento,Med_Inicial=@pmMed_Inicial,Med_Final=@pmMed_Final
,PdoVigencia=@pmPdoVigencia,Vigencia=@pmVigencia,Notas=@pmNotas,Anexo=@pmAnexo,Inactivo=@pmInactivo
 WHERE IdPolitica=@pmIdPolitica
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryNiifPoliticasLta]
 AS 
 SELECT [Id],P.IdPolitica,P.Descripcion,ClasePol,Objetivo,Alcance,Reconocimiento,Med_Inicial,Med_Final
,PdoVigencia,Vigencia,Notas,Anexo,P.Inactivo,P.FechaCrea,P.IdUsuario AS CdUsuario,Usuario
,CdCuenta,NomCuenta
 FROM Trn_NiifPoliticas AS P INNER JOIN adm_Usuarios AS U ON P.IdUsuario=U.IdUsuario
 LEFT JOIN Trn_NiifPolCuentas AS C ON P.IdPolitica=C.IdPolitica
 LEFT JOIN PucNiif AS PC ON C.CdCuenta=PC.IdCuenta
 WHERE P.Inactivo=0
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paInstm_AfAmt_Sel] @pmtmNumero VARCHAR(5),@pmNumero INT
 AS 
 INSERT INTO tm_AfAmt (tmNumero,tmItem,tmIdDiferido,tmCostoIni,tmAdiciones,tmDisminucion,tmAcuAjustes,tmAmortizaHis,tmAmortizaMes,tmAjustesInf,tmAjustesAmtza,tmAmortizaAcum
,tmAjuAmtzaAcum,tmCorrMonAcum,tmBaseCorrMon,tmCorrMonDif,tmAmtzaHisAcum,tmTarifa,tmIdCia,tmCdCCosto,tmCdSubCos,tmCdDep,tmNitRespons,tmNumPeriodos,tmFechaIni
,tmEstadoReg,tmObservacion,tmCuota,tmGalones,tmNitProvee,tmInversion,tmCdGrupo,tmTipoContab)
 SELECT @pmtmNumero,Item,DT.IdDiferido,DT.ValorTotal,DT.VrAdiciones,DT.VrDisminucion,VrAcuAjustes,VrAmortizaHis,VrAmortizaMes,VrAjustesInf,VrAjustesAmtza,VrAmortizaAcum,VrAjuAmtzaAcum
,VrCorrMonAcum,VrBaseCorrMon,VrCorrMonDif,VrAmtzaHisAcum,TarifaAju,DT.IdCia,CdCCosto,CdSubCos,CdDep,NitRespons,NPeriodos,FecInicioAmtza,1,'',CuotaGal,VentaGal,IdProv,VrInversion,CdGrupoDif,TipoContable
 FROM Trn_AfAmtDetalle AS DT INNER JOIN Diferidos AS D ON DT.IdDiferido=D.IdDiferido
 WHERE Numero=@pmNumero
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paUpDiferidos] @pmIdDiferido VARCHAR(30),@pmNomDiferido VARCHAR(250),@pmIdTipDif VARCHAR(4),@pmIdGrupo VARCHAR(4),@pmIdCCosto VARCHAR(16),@pmIdSubCos VARCHAR(16)
,@pmIdDep VARCHAR(4),@pmIdRespons VARCHAR(16),@pmIdCia CHAR(2),@pmFecInicio SMALLDATETIME,@pmValorTotal MONEY,@pmVrAdiciones MONEY,@pmVrDisminucion MONEY,@pmNumPeriodos INT
,@pmFecFinAmtza SMALLDATETIME,@pmAjustable BIT,@pmContable BIT,@pmVrAjuInfAcum MONEY,@pmVrAmtzaAcuHis MONEY,@pmVrAmtzaAcum MONEY,@pmVrAjuInfAmtza MONEY,@pmCuotasAmtza INT
,@pmVrCorrMonDifAcu MONEY,@pmCorrMonDiferida MONEY,@pmNumMesesDif INT,@pmFecUltAmtza SMALLDATETIME,@pmCueEnGrupo BIT,@pmIdCuenta VARCHAR(16),@pmIdCueAmortiza VARCHAR(16)
,@pmIdCueAjuDif VARCHAR(16),@pmIdCueCorrDif VARCHAR(16),@pmIdCueAjuAmtza VARCHAR(16),@pmIdCueCorrAA VARCHAR(16),@pmNumDoc VARCHAR(20),@pmReferencia VARCHAR(50)
,@pmDesUbicacion VARCHAR(150),@pmIdEstado VARCHAR(4),@pmFecRetiro SMALLDATETIME,@pmIdCausal VARCHAR(4),@pmComentRetiro VARCHAR(250),@pmComentarios VARCHAR(250)
,@pmTipoAmtza CHAR(1),@pmNitCliente VARCHAR(16),@pmAgenCliente VARCHAR(16),@pmCantGalones DECIMAL (14,4),@pmIdProv VARCHAR(16),@pmNumPlaca  VARCHAR(20),@pmCodInstala VARCHAR(4),@pmTipoInvers VARCHAR(10)
,@pmTipoContable VARCHAR(10),@pmCodNiifDif VARCHAR(16),@pmCodNiifAmtza VARCHAR(16)
 AS 
 UPDATE Diferidos SET NomDiferido=@pmNomDiferido,IdTipDif=@pmIdTipDif,IdGrupo=@pmIdGrupo,IdCCosto=@pmIdCCosto,IdSubCos=@pmIdSubCos,IdDep=@pmIdDep
,IdRespons=@pmIdRespons,IdCia=@pmIdCia,FecInicio=@pmFecInicio,ValorTotal=@pmValorTotal,VrAdiciones=@pmVrAdiciones,VrDisminucion=@pmVrDisminucion
,NumPeriodos=@pmNumPeriodos,FecFinAmtza=@pmFecFinAmtza,Ajustable=@pmAjustable,Contable=@pmContable,VrAjuInfAcum=@pmVrAjuInfAcum,VrAmtzaAcuHis=@pmVrAmtzaAcuHis
,VrAmtzaAcum=@pmVrAmtzaAcum,VrAjuInfAmtza=@pmVrAjuInfAmtza,CuotasAmtza=@pmCuotasAmtza,VrCorrMonDifAcu=@pmVrCorrMonDifAcu,CorrMonDiferida=@pmCorrMonDiferida,NumMesesDif=@pmNumMesesDif
,FecUltAmtza=@pmFecUltAmtza,CueEnGrupo=@pmCueEnGrupo,IdCuenta=@pmIdCuenta,IdCueAmortiza=@pmIdCueAmortiza,IdCueAjuDif=@pmIdCueAjuDif,IdCueCorrDif=@pmIdCueCorrDif,IdCueAjuAmtza=@pmIdCueAjuAmtza
,IdCueCorrAA=@pmIdCueCorrAA,NumDoc=@pmNumDoc,Referencia=@pmReferencia,DesUbicacion=@pmDesUbicacion,IdEstado=@pmIdEstado,FecRetiro=@pmFecRetiro,IdCausal=@pmIdCausal
,ComentRetiro=@pmComentRetiro,Comentarios=@pmComentarios,TipoAmtza=@pmTipoAmtza,NitCliente=@pmNitCliente,AgenCliente=@pmAgenCliente,CantGalones=@pmCantGalones,IdProv=@pmIdProv
,NumPlaca=@pmNumPlaca,CodInstala=@pmCodInstala,TipoInvers=@pmTipoInvers,TipoContable=@pmTipoContable,CodNiifDif=@pmCodNiifDif,CodNiifAmtza=@pmCodNiifAmtza
 WHERE IdDiferido=@pmIdDiferido
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[paQryDiferidos] @pmIdDiferido VARCHAR(30)
 AS 
 SELECT IdDiferido,NomDiferido,IdTipDif,IdGrupo,IdCCosto,IdSubCos,IdDep,IdRespons,IdCia,FecInicio,ValorTotal,VrAdiciones,VrDisminucion,NumPeriodos
,FecFinAmtza,Ajustable,Contable,VrAjuInfAcum,VrAmtzaAcuHis,VrAmtzaAcum,VrAjuInfAmtza,CuotasAmtza,VrCorrMonDifAcu,CorrMonDiferida,NumMesesDif
,FecUltAmtza,CueEnGrupo,IdCuenta,IdCueAmortiza,IdCueAjuDif,IdCueCorrDif,IdCueAjuAmtza,IdCueCorrAA,NumDoc,Referencia,DesUbicacion,IdEstado
,FecRetiro,IdCausal,ComentRetiro,Comentarios,FecCreacion,IdUsuario,TipoAmtza,NitCliente,AgenCliente,CantGalones,IdProv
,NumPlaca,CodInstala,TipoInvers,TipoContable,CodNiifDif,CodNiifAmtza
 FROM Diferidos
 WHERE IdDiferido=@pmIdDiferido
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paInsDiferidos] @pmIdDiferido VARCHAR(30),@pmNomDiferido VARCHAR(250),@pmIdTipDif VARCHAR(4),@pmIdGrupo VARCHAR(4),@pmIdCCosto VARCHAR(16),@pmIdSubCos VARCHAR(16),@pmIdDep VARCHAR(4)
,@pmIdRespons VARCHAR(16),@pmIdCia CHAR(2),@pmFecInicio SMALLDATETIME,@pmValorTotal MONEY,@pmVrAdiciones MONEY,@pmVrDisminucion MONEY,@pmNumPeriodos INT,@pmFecFinAmtza SMALLDATETIME
,@pmAjustable BIT,@pmContable BIT,@pmVrAjuInfAcum MONEY,@pmVrAmtzaAcuHis MONEY,@pmVrAmtzaAcum MONEY,@pmVrAjuInfAmtza MONEY,@pmCuotasAmtza INT,@pmVrCorrMonDifAcu MONEY
,@pmCorrMonDiferida MONEY,@pmNumMesesDif INT,@pmFecUltAmtza SMALLDATETIME,@pmCueEnGrupo BIT,@pmIdCuenta VARCHAR(16),@pmIdCueAmortiza VARCHAR(16),@pmIdCueAjuDif VARCHAR(16),@pmIdCueCorrDif VARCHAR(16)
,@pmIdCueAjuAmtza VARCHAR(16),@pmIdCueCorrAA VARCHAR(16),@pmNumDoc VARCHAR(20),@pmReferencia VARCHAR(50),@pmDesUbicacion VARCHAR(150),@pmIdEstado VARCHAR(4),@pmFecRetiro SMALLDATETIME
,@pmIdCausal VARCHAR(4),@pmComentRetiro VARCHAR(250),@pmComentarios VARCHAR(250),@pmTipoAmtza CHAR(1),@pmNitCliente VARCHAR(16),@pmAgenCliente VARCHAR(16),@pmCantGalones DECIMAL (14,4),@pmIdProv VARCHAR(16)
,@pmNumPlaca  VARCHAR(20),@pmCodInstala VARCHAR(4),@pmTipoInvers VARCHAR(10),@pmTipoContable VARCHAR(10),@pmCodNiifDif VARCHAR(16),@pmCodNiifAmtza VARCHAR(16),@pmFecCreacion SMALLDATETIME,@pmIdUsuario VARCHAR(11)
 AS 
 INSERT INTO Diferidos (IdDiferido,NomDiferido,IdTipDif,IdGrupo,IdCCosto,IdSubCos,IdDep,IdRespons,IdCia,FecInicio,ValorTotal,VrAdiciones,VrDisminucion,NumPeriodos,FecFinAmtza,Ajustable,Contable,VrAjuInfAcum,VrAmtzaAcuHis,VrAmtzaAcum
,VrAjuInfAmtza,CuotasAmtza,VrCorrMonDifAcu,CorrMonDiferida,NumMesesDif,FecUltAmtza,CueEnGrupo,IdCuenta,IdCueAmortiza,IdCueAjuDif,IdCueCorrDif,IdCueAjuAmtza,IdCueCorrAA,NumDoc,Referencia,DesUbicacion,IdEstado,FecRetiro,IdCausal
,ComentRetiro,Comentarios,FecCreacion,IdUsuario,TipoAmtza,NitCliente,AgenCliente,CantGalones,IdProv,NumPlaca,CodInstala,TipoInvers,TipoContable,CodNiifDif,CodNiifAmtza)
 VALUES (@pmIdDiferido,@pmNomDiferido,@pmIdTipDif,@pmIdGrupo,@pmIdCCosto,@pmIdSubCos,@pmIdDep,@pmIdRespons,@pmIdCia,@pmFecInicio,@pmValorTotal,@pmVrAdiciones,@pmVrDisminucion,@pmNumPeriodos
,@pmFecFinAmtza,@pmAjustable,@pmContable,@pmVrAjuInfAcum,@pmVrAmtzaAcuHis,@pmVrAmtzaAcum,@pmVrAjuInfAmtza,@pmCuotasAmtza,@pmVrCorrMonDifAcu,@pmCorrMonDiferida,@pmNumMesesDif
,@pmFecUltAmtza,@pmCueEnGrupo,@pmIdCuenta,@pmIdCueAmortiza,@pmIdCueAjuDif,@pmIdCueCorrDif,@pmIdCueAjuAmtza,@pmIdCueCorrAA,@pmNumDoc,@pmReferencia,@pmDesUbicacion,@pmIdEstado,@pmFecRetiro,@pmIdCausal
,@pmComentRetiro,@pmComentarios,@pmFecCreacion,@pmIdUsuario,@pmTipoAmtza,@pmNitCliente,@pmAgenCliente,@pmCantGalones,@pmIdProv,@pmNumPlaca,@pmCodInstala,@pmTipoInvers,@pmTipoContable,@pmCodNiifDif,@pmCodNiifAmtza)
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[paQrytm_AfAmtLta] @pmtmNumero VARCHAR(5)
 AS 
 SELECT tmItem,tmIdDiferido,NomDiferido,tmTipoContab,tmCostoIni,tmAdiciones,tmDisminucion,tmAcuAjustes,(tmCostoIni+tmAdiciones-tmDisminucion+tmAcuAjustes) AS CostoAjustado
,tmTarifa,tmAjustesInf,tmAmortizaMes,tmAmortizaAcum,tmAjuAmtzaAcum,tmAmortizaAcum+tmAjuAmtzaAcum AS TotalAmortizaAcum,tmAjustesAmtza
,tmAmtzaHisAcum,tmAmortizaHis,tmCorrMonAcum,tmBaseCorrMon,tmCorrMonDif,tmEstadoReg,tmObservacion,tmFechaIni,tmNumPeriodos
,tmIdCia,Compania,tmCdCCosto,CCosto,tmCdSubCos,SubCosto,tmCdDep,Dependencia,tmNitRespons,RazonSocial,D.IdEstado AS CdEstado,Estado,FecRetiro
,Ajustable,Contable,G.IdLinea AS CdLinea,Linea,D.IdGrupo AS CdGrupo,Grupo,tmCuota,tmGalones,IdProv,tmInversion,tmCdGrupo
 FROM tm_AfAmt AS T INNER JOIN Diferidos AS D ON T.tmIdDiferido=D.IdDiferido
 INNER JOIN  AfGrupos AS G ON D.IdGrupo=G.IdGrupo INNER JOIN AfLineas AS L ON G.IdLinea=L.IdLinea
 INNER JOIN AfEstados AS ED ON D.IdEstado=ED.IdEstado
 LEFT JOIN CentroCosto AS CC ON T.tmCdCCosto=CC.IdCCosto
 LEFT JOIN Dependencias AS DP ON T.tmCdDep=DP.IdDep LEFT JOIN Terceros AS TR ON T.tmNitRespons=TR.IdTercero
 LEFT JOIN Companias AS CI ON T.tmIdCia=CI.IdCia LEFT JOIN SubCentros AS SC ON  T.tmCdSubCos=SC.IdSubCos
 WHERE tmNumero=@pmtmNumero
 ORDER BY NomDiferido
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[paQryDiferidosAmt] @pmFechaIni SMALLDATETIME,@pmFechaFin SMALLDATETIME
 AS 
 SELECT IdDiferido,NomDiferido,IdTipDif,IdGrupo,IdCCosto,IdSubCos,IdDep,IdRespons,IdCia,FecInicio,ValorTotal,VrAdiciones,VrDisminucion,NumPeriodos
,FecFinAmtza,Ajustable,Contable,CorrMonDiferida,NumMesesDif,FecUltAmtza,CueEnGrupo,IdCuenta,IdCueAmortiza,IdCueAjuDif,IdCueCorrDif,IdCueAjuAmtza,IdCueCorrAA
,IdEstado,FecRetiro,TipoAmtza,NitCliente,AgenCliente,CantGalones,IdProv,NumPlaca,CodInstala,TipoInvers,TipoContable,CodNiifDif,CodNiifAmtza
 FROM Diferidos
 WHERE FecInicio<=@pmFechaFin
  AND (IdEstado<>'9999'  OR (IdEstado='9999' AND FecRetiro>=@pmFechaIni))
 ORDER BY IdDiferido
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[paQryNiifRevelacLta] @pmFechaIni SMALLDATETIME,@pmFechaFin SMALLDATETIME,@pmTipCom VARCHAR(3),@pmIdCia CHAR(2)
 AS 
 SELECT R.TipCom,TipoCom,R.Comprobante AS NumComp,R.IdCia AS CdCia,Compania,C.Fecha,C.IdTercero AS NitTercero,T.RazonSocial,C.VrTotal,C.IdCta AS CdCta,NumeroCta
,CTA.IdBanco AS CdBanco,Banco,EnEfectivo,C.NumCheque AS ComNumCheque,FecCheque,Anulado,NumDev,FecDev,pVehiculo,C.VehPropio,CedCondtor,CD.RazonSocial AS NomConductor
,C.CodConce,CN.Concepto,Beneficiario,C.Integrado AS EsIntegracion,C.EsEgreso AS ComEsEgreso,C.Observacion AS Observ,C.IdEstado AS CdEstado
,TipoNiif,CtbTipo,CtbNumero,FisTipo,FisNumero,R.Item,R.TipoRev,R.Titulo,R.Comentario,R.CdCuenta,NomCuenta,R.FechaCrea,R.IdUsuario AS CdUsuario,Usuario
 FROM Trn_NiifRevelac AS R INNER JOIN Trn_NiifComp AS C ON R.TipCom=C.TipCom AND R.Comprobante=C.Comprobante AND R.IdCia=C.IdCia
 INNER JOIN Companias AS CI ON R.IdCia=CI.IdCia  INNER JOIN adm_Usuarios AS U ON R.IdUsuario=U.IdUsuario
 INNER JOIN TiposCom AS TC ON R.TipCom=TC.IdCom INNER JOIN Terceros AS T ON C.IdTercero=T.IdTercero
 LEFT JOIN Conceptos AS CN ON C.CodConce=CN.IdConcepto LEFT JOIN Terceros AS CD ON C.CedCondtor=CD.IdTercero 
 LEFT JOIN CtasCorrientes AS CTA ON C.IdCta=CTA.IdCta LEFT JOIN Bancos AS BCT ON CTA.IdBanco=BCT.IdBanco
 LEFT JOIN PucNiif AS P ON R.CdCuenta=P.IdCuenta
 WHERE C.Fecha BETWEEN @pmFechaIni AND @pmFechaFin AND R.TipCom LIKE ISNULL(@pmTipCom,'%')
 AND C.IdCia LIKE ISNULL(@pmIdCia,'%%')
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paInstm_AfAmt] @pmtmNumero VARCHAR(5),@pmtmItem INT,@pmtmIdDiferido VARCHAR(30),@pmtmCostoIni MONEY,@pmtmAdiciones MONEY,@pmtmDisminucion MONEY,@pmtmAcuAjustes MONEY,@pmtmAmortizaHis MONEY
,@pmtmAmortizaMes MONEY,@pmtmAjustesInf MONEY,@pmtmAjustesAmtza MONEY,@pmtmAmortizaAcum MONEY,@pmtmAjuAmtzaAcum MONEY,@pmtmCorrMonAcum MONEY,@pmtmBaseCorrMon MONEY,@pmtmCorrMonDif MONEY,@pmtmAmtzaHisAcum MONEY,@pmtmTarifa DECIMAL(14,4)
,@pmtmIdCia CHAR(2),@pmtmCdCCosto VARCHAR(16),@pmtmCdSubCos VARCHAR(16),@pmtmCdDep VARCHAR(4),@pmtmNitRespons VARCHAR(16),@pmtmNumPeriodos INT,@pmtmFechaIni SMALLDATETIME,@pmtmEstadoReg INT,@pmtmObservacion VARCHAR(150)
,@pmtmCuota DECIMAL(14,4),@pmtmGalones DECIMAL(14,4),@pmtmNitProvee VARCHAR(16),@pmtmInversion MONEY,@pmtmCdGrupo VARCHAR(4),@pmtmTipoContab VARCHAR(10)
 AS 
 INSERT INTO tm_AfAmt (tmNumero,tmItem,tmIdDiferido,tmCostoIni,tmAdiciones,tmDisminucion,tmAcuAjustes,tmAmortizaHis,tmAmortizaMes,tmAjustesInf,tmAjustesAmtza,tmAmortizaAcum,tmAjuAmtzaAcum,tmCorrMonAcum,tmBaseCorrMon,tmCorrMonDif
,tmAmtzaHisAcum,tmTarifa,tmIdCia,tmCdCCosto,tmCdSubCos,tmCdDep,tmNitRespons,tmNumPeriodos,tmFechaIni,tmEstadoReg,tmObservacion,tmCuota,tmGalones,tmNitProvee,tmInversion,tmCdGrupo,tmTipoContab)
 VALUES (@pmtmNumero,@pmtmItem,@pmtmIdDiferido,@pmtmCostoIni,@pmtmAdiciones,@pmtmDisminucion,@pmtmAcuAjustes,@pmtmAmortizaHis,@pmtmAmortizaMes,@pmtmAjustesInf,@pmtmAjustesAmtza,@pmtmAmortizaAcum,@pmtmAjuAmtzaAcum
,@pmtmCorrMonAcum,@pmtmBaseCorrMon,@pmtmCorrMonDif,@pmtmAmtzaHisAcum,@pmtmTarifa,@pmtmIdCia,@pmtmCdCCosto,@pmtmCdSubCos,@pmtmCdDep,@pmtmNitRespons,@pmtmNumPeriodos,@pmtmFechaIni,@pmtmEstadoReg,@pmtmObservacion,@pmtmCuota,@pmtmGalones,@pmtmNitProvee,@pmtmInversion,@pmtmCdGrupo,@pmtmTipoContab)
GO