if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInsAfAmtDetalle]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[paInsAfAmtDetalle]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInsAfDifValoriza]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[paInsAfDifValoriza]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInsAfMovValoriza]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[paInsAfMovValoriza]
GO

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].[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].[paQryAfAmortizaLta]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[paQryAfAmortizaLta]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryAfAmortizaRes]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[paQryAfAmortizaRes]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryAfDifValorizaCr]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[paQryAfDifValorizaCr]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryAfDifValorizaLta]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[paQryAfDifValorizaLta]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryAfMovValoriza]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[paQryAfMovValoriza]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryAfMovValorizaCr]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[paQryAfMovValorizaCr]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryAfMovValorizaLta]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[paQryAfMovValorizaLta]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryAfDifValoriza]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[paQryAfDifValoriza]
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].[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

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE paInsAfAmtDetalle @pmNumero INT,@pmItem INT,@pmIdDiferido VARCHAR(30),@pmValorTotal MONEY,@pmVrAdiciones MONEY
,@pmVrDisminucion MONEY,@pmVrAcuAjustes MONEY,@pmVrAmortizaHis MONEY,@pmVrAmortizaMes MONEY,@pmVrAjustesInf MONEY
,@pmVrAjustesAmtza MONEY,@pmVrAmtzaHisAcum MONEY,@pmVrAmortizaAcum MONEY,@pmVrAjuAmtzaAcum MONEY,@pmVrCorrMonAcum MONEY
,@pmVrBaseCorrMon MONEY,@pmVrCorrMonDif MONEY,@pmTarifaAju DECIMAL(14,4),@pmIdCia CHAR(2),@pmCdCCosto VARCHAR(16),@pmCdSubCos VARCHAR(16)
,@pmCdDep VARCHAR(4),@pmNitRespons VARCHAR(16),@pmFecInicioAmtza SMALLDATETIME,@pmNPeriodos INT,@pmCuotaGal DECIMAL(14,4),@pmVentaGal DECIMAL(14,4)
 AS 
 INSERT INTO Trn_AfAmtDetalle (Numero,Item,IdDiferido,ValorTotal,VrAdiciones,VrDisminucion,VrAcuAjustes,VrAmortizaHis,VrAmortizaMes,VrAjustesInf,VrAjustesAmtza
,VrAmtzaHisAcum,VrAmortizaAcum,VrAjuAmtzaAcum,VrCorrMonAcum,VrBaseCorrMon,VrCorrMonDif,TarifaAju,IdCia,CdCCosto,CdSubCos,CdDep,NitRespons,FecInicioAmtza,NPeriodos,CuotaGal,VentaGal)
 VALUES (@pmNumero,@pmItem,@pmIdDiferido,@pmValorTotal,@pmVrAdiciones,@pmVrDisminucion,@pmVrAcuAjustes,@pmVrAmortizaHis,@pmVrAmortizaMes
,@pmVrAjustesInf,@pmVrAjustesAmtza,@pmVrAmtzaHisAcum,@pmVrAmortizaAcum,@pmVrAjuAmtzaAcum,@pmVrCorrMonAcum,@pmVrBaseCorrMon,@pmVrCorrMonDif,@pmTarifaAju
,@pmIdCia,@pmCdCCosto,@pmCdSubCos,@pmCdDep,@pmNitRespons,@pmFecInicioAmtza,@pmNPeriodos,@pmCuotaGal,@pmVentaGal )
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE paInsAfDifValoriza @pmIdMov VARCHAR(4),@pmNumero INT,@pmIdCia CHAR(2),@pmItem INT
,@pmIdDiferido VARCHAR(30),@pmConcepto VARCHAR(250),@pmValor MONEY,@pmNitProvee VARCHAR(16)
 AS 
 INSERT INTO Trn_AfDifValoriza (IdMov,Numero,IdCia,Item,IdDiferido,Concepto,Valor,NitProvee)
 VALUES (@pmIdMov,@pmNumero,@pmIdCia,@pmItem,@pmIdDiferido,@pmConcepto,@pmValor,@pmNitProvee)
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE paInsAfMovValoriza @pmIdMov VARCHAR(4),@pmNumero INT,@pmIdCia CHAR(2)
,@pmItem INT,@pmIdActivo VARCHAR(30),@pmConcepto VARCHAR(250),@pmValor MONEY
,@pmNitProvee VARCHAR(16),@pmFecAdquis SMALLDATETIME,@pmNumDocmto VARCHAR(20)
,@pmDescripParte VARCHAR(150)
 AS 
 INSERT INTO Trn_AfMovValoriza (IdMov,Numero,IdCia,Item,IdActivo,Concepto,Valor,NitProvee,FecAdquis,NumDocmto,DescripParte)
 VALUES (@pmIdMov,@pmNumero,@pmIdCia,@pmItem,@pmIdActivo,@pmConcepto,@pmValor,@pmNitProvee,@pmFecAdquis,@pmNumDocmto,@pmDescripParte)
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE 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)
,@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)
 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)
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE 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)
 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)
 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)
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE 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)
 SELECT @pmtmNumero,Item,IdDiferido,ValorTotal,VrAdiciones,VrDisminucion,VrAcuAjustes,VrAmortizaHis,VrAmortizaMes,VrAjustesInf,VrAjustesAmtza,VrAmortizaAcum,VrAjuAmtzaAcum
,VrCorrMonAcum,VrBaseCorrMon,VrCorrMonDif,VrAmtzaHisAcum,TarifaAju,IdCia,CdCCosto,CdSubCos,CdDep,NitRespons,NPeriodos,FecInicioAmtza,1,'',CuotaGal,VentaGal
 FROM Trn_AfAmtDetalle
 WHERE Numero=@pmNumero
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE paQryAfAmortizaLta  @pmNumeroIni INT,@pmNumeroFin INT
 AS 
 SELECT 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.IdDiferido AS CdDiferido,NomDiferido, 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,CCosto,CdSubCos,SubCosto, CdDep, Dependencia,NitRespons,RazonSocial,FecInicioAmtza, NPeriodos
--datos del diferido
,A.IdTipDif AS CdTipDif,TipoDiferido,G.IdLinea AS CodLinea,Linea,A.IdGrupo AS CodGrupo,Grupo,A.IdCCosto AS DifCdCentro,A.IdSubCos AS DifCdSubcentro
,A.IdDep AS DifCdDep,DesUbicacion,IdRespons,A.IdCia AS DifIdCia,FecInicio,A.ValorTotal AS DifValTotal,A.VrAdiciones AS DifValAdiciones,A.VrDisminucion AS DifValDisminuc,A.NumPeriodos AS DifNumMeses
,FecFinAmtza,Ajustable,Contable,A.VrAjuInfAcum AS DifAjusInfAcum,A.VrAmtzaAcuHis AS DifAmtzaAcuHis,VrAmtzaAcum,A.VrAjuInfAmtza AS DifAjuInfAmtza,CuotasAmtza,VrCorrMonDifAcu,CorrMonDiferida,NumMesesDif
,A.FecUltAmtza AS DifFecUltAmza,CueEnGrupo,IdCuenta,IdCueAmortiza,IdCueAjuDif,IdCueCorrDif,IdCueAjuAmtza,IdCueCorrAA,NumDoc,Referencia,A.IdEstado AS CdEstado,ED.Estado AS EstadoDif
,Comentarios,A.FecCreacion AS DifFecCrea,VentaGal,CuotaGal
 FROM Trn_AfAmortiza AS AMZ INNER JOIN Trn_AfAmtDetalle AS D ON AMZ.Numero=D.Numero
 INNER JOIN Companias AS CI ON AMZ.IdCia=CI.IdCia  INNER JOIN adm_Usuarios AS U ON AMZ.IdUsuario=U.IdUsuario
 INNER JOIN Diferidos AS A ON D.IdDiferido=A.IdDiferido  INNER JOIN AfGrupos AS G ON A.IdGrupo=G.IdGrupo INNER JOIN AfLineas AS L ON G.IdLinea=L.IdLinea
 INNER JOIN AfTiposDif AS TD ON A.IdTipDif=TD.IdTipDif  INNER JOIN AfEstados AS ED ON A.IdEstado=ED.IdEstado 
 INNER JOIN Companias AS CID ON D.IdCia=CID.IdCia 
 INNER JOIN CentroCosto AS CC ON D.CdCCosto=CC.IdCCosto 
 INNER JOIN Dependencias AS DP ON D.CdDep=DP.IdDep INNER JOIN Terceros AS T ON D.NitRespons=T.IdTercero
 LEFT JOIN SubCentros AS SC ON D.CdSubCos=SC.IdSubCos 
 WHERE  AMZ.Numero BETWEEN @pmNumeroIni  AND @pmNumeroFin
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE paQryAfAmortizaRes @pmFechaIni SMALLDATETIME,@pmFechaFin SMALLDATETIME,@pmIdLinea VARCHAR(4)=Null,@pmIdGrupo VARCHAR(4)=Null
,@pmIdCia CHAR(2)=Null,@pmIdTipDif VARCHAR(4)=Null,@pmIdDep VARCHAR(4)=Null,@pmIdRespons VARCHAR(16)=Null,@pmIdCCosto VARCHAR(16)=Null
,@pmIdDiferido VARCHAR(30)
 AS 
 SELECT D.IdDiferido AS CdDiferido,NomDiferido,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,VentaGal,CuotaGal
--datos del diferido
,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,FecCreacion,A.IdUsuario AS IdUsuari
--DATOS DEL DIFERIDO
 FROM Trn_AfAmtDetalle AS D INNER JOIN Trn_AfAmortiza AS  AMZ  ON D.Numero=AMZ.Numero  INNER JOIN Companias AS CI ON AMZ.IdCia=CI.IdCia
 INNER JOIN adm_Usuarios AS U ON AMZ.IdUsuario=U.IdUsuario
 INNER JOIN Diferidos  AS A ON D.IdDiferido=A.IdDiferido  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 AfEstados AS ED ON A.IdEstado=ED.IdEstado   INNER JOIN Companias AS CID ON D.IdCia=CID.IdCia 
 INNER JOIN CentroCosto AS CCD ON D.CdCCosto=CCD.IdCCosto 
 INNER JOIN Dependencias AS DPD ON D.CdDep=DPD.IdDep INNER JOIN Terceros AS TRD ON D.NitRespons=TRD.IdTercero
 LEFT JOIN SubCentros AS SC ON A.IdSubCos=SC.IdSubCos LEFT JOIN AfCausales AS CA ON A.IdCausal=CA.IdCausal
 LEFT JOIN SubCentros AS SCD ON D.CdSubCos=SCD.IdSubCos 
 WHERE Fecha BETWEEN  @pmFechaIni  AND @pmFechaFin AND AMZ.Estado<>0 AND D.IdDiferido LIKE ISNULL(@pmIdDiferido ,'%')
 AND AMZ.IdCia LIKE ISNULL(@pmIdCia,'%%')  AND  G.IdLinea LIKE ISNULL(@pmIdLinea,'%') AND A.IdGrupo LIKE ISNULL(@pmIdGrupo,'%')  
 AND D.CdDep LIKE ISNULL(@pmIdDep,'%')  AND D.NitRespons LIKE ISNULL(@pmIdRespons,'%')  AND D.CdCCosto LIKE ISNULL(@pmIdCCosto,'%')
 AND A.IdTipDif LIKE ISNULL(@pmIdTipDif,'%')
 ORDER BY AMZ.Numero,NomDiferido
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE paQryAfDifValorizaCr @pmIdMov VARCHAR(4),@pmFechaIni SMALLDATETIME,@pmFechaFin SMALLDATETIME
,@pmIdCia CHAR(2),@pmIdGrupo VARCHAR(4)=Null,@pmIdLinea VARCHAR(4)=Null,@pmIdTipDif VARCHAR(4)=Null,@pmIdDiferido VARCHAR(30)=Null
 AS 
 SELECT M.IdMov AS CdMov,TipoMov,M.Numero AS NroValoriza,M.IdCia AS CdCia,Compania,Fecha,Anulado,M.Observacion AS Observ,TipCom,Comprobante,IdCiaCom
,M.FecCreacion AS FechaCrea,M.IdUsuario AS CdUsuario,Usuario
,Item,D.IdDiferido AS CdDiferido,NomDiferido,Concepto,Valor,NitProvee,PV.RazonSocial AS Proveedor
--Datos del diferido
,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 DifIdCia,FecInicio,ValorTotal,VrAdiciones,A.VrDisminucion AS DifValDisminuc,A.NumPeriodos AS DifNumMeses
,FecFinAmtza,Ajustable,Contable,A.VrAjuInfAcum AS DifAjusInfAcum,A.VrAmtzaAcuHis AS DifAmtzaAcuHis,VrAmtzaAcum,A.VrAjuInfAmtza AS DifAjuInfAmtza,CuotasAmtza,VrCorrMonDifAcu,CorrMonDiferida,NumMesesDif
,A.FecUltAmtza AS DifFecUltAmza,CueEnGrupo,IdCuenta,IdCueAmortiza,IdCueAjuDif,IdCueCorrDif,IdCueAjuAmtza,IdCueCorrAA,NumDoc,Referencia,A.IdEstado AS CdEstado,Estado
,Comentarios,A.FecCreacion AS DifFecCrea
 FROM Trn_AfDifMovi AS M INNER JOIN Trn_AfDifValoriza AS D ON M. IdMov=D.IdMov AND M.Numero=D.Numero AND M.IdCia=D.IdCia
 INNER JOIN AfTiposMov AS TM ON M.IdMov=TM.IdMov  INNER JOIN Companias AS CI ON M.IdCia=CI.IdCia 
 INNER JOIN adm_Usuarios AS U ON M.IdUsuario=U.IdUsuario
 INNER JOIN Diferidos AS A ON D.IdDiferido=A.IdDiferido 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 AfEstados AS ED ON A.IdEstado=ED.IdEstado 
 LEFT JOIN SubCentros AS SC ON A.IdSubCos=SC.IdSubCos LEFT JOIN Terceros AS PV ON D.NitProvee=PV.IdTercero
 WHERE M.IdMov=@pmIdMov AND Fecha BETWEEN @pmFechaIni AND @pmFechaFin AND M.IdCia LIKE ISNULL(@pmIdCia,'%%')
 AND A.IdGrupo LIKE ISNULL(@pmIdGrupo,'%') AND G.IdLinea LIKE ISNULL(@pmIdLinea,'%') AND D.IdDiferido LIKE ISNULL(@pmIdDiferido,'%')
 AND A.IdTipDif LIKE ISNULL(@pmIdTipDif,'%')
 ORDER BY M.Numero,NomDiferido
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE paQryAfDifValorizaLta @pmIdMov VARCHAR(4),@pmNumeroIni INT,@pmNumeroFin INT,@pmIdCia CHAR(2)
 AS 
 SELECT M.IdMov AS CdMov,TipoMov,M.Numero AS NroValoriza,M.IdCia AS CdCia,Compania,Fecha,Anulado,M.Observacion AS Observ,TipCom,Comprobante,IdCiaCom
,M.FecCreacion AS FechaCrea,M.IdUsuario AS CdUsuario,Usuario
,Item,D.IdDiferido AS CdDiferido,NomDiferido,Concepto,Valor,NitProvee,PV.RazonSocial AS Proveedor
--Datos del diferido
,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 Responsable,A.IdCia AS DifIdCia,FecInicio,ValorTotal,VrAdiciones,A.VrDisminucion AS DifValDisminuc,A.NumPeriodos AS DifNumMeses
,FecFinAmtza,Ajustable,Contable,A.VrAjuInfAcum AS DifAjusInfAcum,A.VrAmtzaAcuHis AS DifAmtzaAcuHis,VrAmtzaAcum,A.VrAjuInfAmtza AS DifAjuInfAmtza,CuotasAmtza,VrCorrMonDifAcu,CorrMonDiferida,NumMesesDif
,A.FecUltAmtza AS DifFecUltAmza,CueEnGrupo,IdCuenta,IdCueAmortiza,IdCueAjuDif,IdCueCorrDif,IdCueAjuAmtza,IdCueCorrAA,NumDoc,Referencia,A.IdEstado AS CdEstado,Estado
,Comentarios,A.FecCreacion AS DifFecCrea
 FROM Trn_AfDifMovi AS M INNER JOIN Trn_AfDifValoriza AS D ON M. IdMov=D.IdMov AND M.Numero=D.Numero AND M.IdCia=D.IdCia
 INNER JOIN AfTiposMov AS TM ON M.IdMov=TM.IdMov  INNER JOIN Companias AS CI ON M.IdCia=CI.IdCia 
 INNER JOIN adm_Usuarios AS U ON M.IdUsuario=U.IdUsuario
 INNER JOIN Diferidos AS A ON D.IdDiferido=A.IdDiferido 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 AfEstados AS ED ON A.IdEstado=ED.IdEstado 
 LEFT JOIN SubCentros AS SC ON A.IdSubCos=SC.IdSubCos  LEFT JOIN Terceros AS PV ON D.NitProvee=PV.IdTercero
 WHERE M.IdMov=@pmIdMov AND M.Numero BETWEEN @pmNumeroIni AND @pmNumeroFin AND  M.IdCia=@pmIdCia
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE paQryAfMovValoriza @pmIdMov VARCHAR(4),@pmNumero INT,@pmIdCia CHAR(2)
 AS 
 SELECT IdMov,Numero,IdCia,Item,IdActivo,Concepto,Valor,NitProvee,FecAdquis,NumDocmto,DescripParte
 FROM Trn_AfMovValoriza
 WHERE IdMov=@pmIdMov AND Numero=@pmNumero AND IdCia=@pmIdCia
 ORDER BY Item
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE paQryAfMovValorizaCr @pmIdMov VARCHAR(4),@pmFechaIni SMALLDATETIME,@pmFechaFin SMALLDATETIME
,@pmIdCia CHAR(2)=Null,@pmIdGrupo VARCHAR(4)=Null,@pmIdLinea VARCHAR(4)=Null,@pmIdActivo VARCHAR(30)=Null
 AS 
 SELECT M.IdMov AS CdMov,TipoMov,M.Numero AS NumValoriza,M.IdCia AS CdCia,Compania,Fecha,Documento,Anulado,M.Observacion AS Observ,TipCom,Comprobante,IdCiaCom
,M.FecCreacion AS FechaCrea,M.IdUsuario AS CdUsuario,Usuario,Item,V.IdActivo AS CdActivo,NomActivo,Concepto,Valor
,NitProvee,PV.RazonSocial AS Proveedor,FecAdquis,NumDocmto,DescripParte
--datos del activo
,NumSerie,A.IdGrupo AS CdGrupo,Grupo,G.IdLinea AS CdLinea,Linea,A.IdCCosto AS CdCentro,CCosto,A.IdSubCos AS CdSubCentro,SubCosto,A.IdInstala AS CdInstala,Instlacion
,A.IdDep AS CdDep,Dependencia,Marca,Modelo,NumPlaca,Referencia,IdRespons,TR.RazonSocial AS Responsable,A.IdEstado AS CdEstado,Estado
,FecActivacion,VrBaseDep,VrMejoras,VrDisminucion,A.VidaUtil AS MesesVida,FecFinDep,MetodoDep
 FROM Trn_AfMovimiento AS M  INNER JOIN Trn_AfMovValoriza AS V ON M.IdMov=V.IdMov AND M.Numero=V.Numero AND M.IdCia=V.IdCia
 INNER JOIN Companias AS CN ON M.IdCia=CN.IdCia INNER JOIN AfTiposMov AS TM ON M.IdMov=TM.IdMov
 INNER JOIN adm_Usuarios AS U ON M.IdUsuario=U.IdUsuario
 INNER JOIN Activos AS A ON V.IdActivo=A.IdActivo 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 Instalaciones AS INS ON A.IdInstala=INS.IdInstala
 INNER JOIN Dependencias AS DP ON A.IdDep=DP.IdDep INNER JOIN Terceros AS TR ON A.IdRespons=TR.IdTercero
 INNER JOIN AfEstados AS ED ON A.IdEstado=ED.IdEstado  LEFT JOIN SubCentros AS SC ON A.IdSubCos=SC.IdSubCos
 LEFT JOIN Terceros AS PV ON  V.NitProvee=PV.IdTercero
 WHERE M.IdMov=@pmIdMov AND  Fecha BETWEEN @pmFechaIni AND @pmFechaFin AND M.IdCia LIKE ISNULL(@pmIdCia,'%%')
 AND A.IdGrupo LIKE ISNULL(@pmIdGrupo,'%') AND G.IdLinea LIKE ISNULL(@pmIdLinea,'%') AND V.IdActivo LIKE ISNULL(@pmIdActivo,'%')
 ORDER BY M.Numero,NomActivo
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE paQryAfMovValorizaLta @pmIdMov VARCHAR(4),@pmNumeroIni INT,@pmNumeroFin INT,@pmIdCia CHAR(2)
 AS 
 SELECT M.IdMov AS CdMov,TipoMov,M.Numero AS NumIngreso,M.IdCia AS CdCia,Compania,Fecha,Documento,Anulado,M.Observacion AS Observ,TipCom,Comprobante,IdCiaCom
,M.FecCreacion AS FechaCrea,M.IdUsuario AS CdUsuario,Usuario,Item,V.IdActivo AS CdActivo,NomActivo,Concepto,Valor
,NitProvee,PV.RazonSocial AS Proveedor,FecAdquis,NumDocmto,DescripParte
--datos del activo
,NumSerie,A.IdGrupo AS CdGrupo,Grupo,G.IdLinea AS CdLinea,Linea,A.IdCCosto AS CdCentro,CCosto,A.IdSubCos AS CdSubCentro,SubCosto,A.IdInstala AS CdInstala,Instlacion
,A.IdDep AS CdDep,Dependencia,Marca,Modelo,NumPlaca,Referencia,IdRespons,TR.RazonSocial AS Responsable,A.IdEstado AS CdEstado,Estado
,FecActivacion,VrBaseDep,VrMejoras,VrDisminucion,A.VidaUtil AS MesesVida,FecFinDep,MetodoDep
 FROM Trn_AfMovimiento AS M  INNER JOIN Trn_AfMovValoriza AS V ON M.IdMov=V.IdMov AND M.Numero=V.Numero AND M.IdCia=V.IdCia
 INNER JOIN Companias AS CN ON M.IdCia=CN.IdCia INNER JOIN AfTiposMov AS TM ON M.IdMov=TM.IdMov
 INNER JOIN adm_Usuarios AS U ON M.IdUsuario=U.IdUsuario
 INNER JOIN Activos AS A ON V.IdActivo=A.IdActivo 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 Instalaciones AS INS ON A.IdInstala=INS.IdInstala
 INNER JOIN Dependencias AS DP ON A.IdDep=DP.IdDep INNER JOIN Terceros AS TR ON A.IdRespons=TR.IdTercero
 INNER JOIN AfEstados AS ED ON A.IdEstado=ED.IdEstado  LEFT JOIN SubCentros AS SC ON A.IdSubCos=SC.IdSubCos
 LEFT JOIN Terceros AS PV ON V.NitProvee=PV.IdTercero
 WHERE M.IdMov=@pmIdMov AND M.Numero BETWEEN @pmNumeroIni AND @pmNumeroFin AND M.IdCia=@pmIdCia
 ORDER BY Item
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE paQryAfDifValoriza @pmIdMov VARCHAR(4),@pmNumero INT,@pmIdCia CHAR(2)
 AS 
 SELECT IdMov,Numero,IdCia,Item,IdDiferido,Concepto,Valor,NitProvee
 FROM Trn_AfDifValoriza
 WHERE IdMov=@pmIdMov AND Numero=@pmNumero AND IdCia=@pmIdCia
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE 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
 FROM Diferidos
 WHERE IdDiferido=@pmIdDiferido
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE 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
 FROM Diferidos
 WHERE FecInicio<=@pmFechaFin
  AND (IdEstado<>'9999'  OR (IdEstado='9999' AND FecRetiro>=@pmFechaIni))
 ORDER BY IdDiferido
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE 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,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
 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 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 
 WHERE A.IdDiferido LIKE ISNULL(@pmIdDiferido ,'%')
 ORDER BY NomDiferido
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE 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,RazonSocial,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,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
--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 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
 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 QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE paQrytm_AfAmtLta @pmtmNumero VARCHAR(5)
 AS 
 SELECT  tmItem,tmIdDiferido,NomDiferido,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
 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 QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE 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)
 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
 WHERE IdDiferido=@pmIdDiferido
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO