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

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

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

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

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

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

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

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

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

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

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

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

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

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

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE paQryActivosHis @pmIdActivo VARCHAR(30)=Null
 AS 
 SELECT A.IdActivo AS CdActivo,NomActivo,NumSerie,CodInvent,CodBarras,G.IdLinea AS CodLinea,Linea,A.IdGrupo AS CodGrupo,Grupo,A.IdCCosto AS CdCentro,CC.CCosto AS ActCentroCosto
,A.IdSubCos AS CdSubcentro,SC.SubCosto AS ActSubCentro,A.IdInstala AS CdInstala,I.Instlacion AS ActInstalacion,A.IdDep AS CdDep,DEP.Dependencia AS NomDependencia
,IdRespons,TR.RazonSocial AS Responsable,A.IdCia AS CdCia,CI.Compania AS NomCia,IdProv,T.RazonSocial AS Proveedor,FecCompra,FecActivacion,NumOrden,IdCiaOrden,NumEntrada,IdCiaEnt,VrCompra,VrComercial,VrBaseDep
,A.VrMejoras AS TotMejoras,A.VrDisminucion AS TotDisminucion,A.VidaUtil AS Vida_util,FecFinDep,MetodoDep,Ajustable,Contable,CueEnGrupo,VrAjuInfAcum,VrDepAcuHis,VrDepAcuAju,VrAjuInfDep,NumMesesDep
,FecUltDep,Marca,Modelo,NumPlaca,NumDoc,Referencia,A.IdEstado AS CdEstado,ED.Estado AS ActEstado,FecRetiro,A.IdCausal AS CdCausal,Causal,VrVenta,FecCreacion
--datos de depreciacion
,D.Numero AS NumDeprecia,Fecha,D.IdUsuario AS CdUsuario,Usuario,Item,VrCostoIni,DP.VrMejoras AS ValMejoras,DP.VrDisminucion AS ValDisminuc,VrAcuAjustes,VrDepreciaHis,VrDepreciaMes,VrAjustesInf,VrAjustesDep,VrDepreciaAcum
,VrAjuDepAcum,TarifaAju,DP.IdCia AS DetCodCia,CN.Compania AS DetCompania,CdCCosto,CCD.CCosto AS DetCentroCosto,CdSubCos,SCD.SubCosto AS DetSubCentro,CdInstala,INS.Instlacion AS DetInstacion,CdDep,DE.Dependencia AS DetDependencia
,NitRespons,TRD.RazonSocial AS DetNomResponsable,FecIniDep,MesesVida,MetodoDeprec
 FROM Activos 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 Instalaciones AS I ON A.IdInstala=I.IdInstala
 INNER JOIN Dependencias AS DEP ON A.IdDep=DEP.IdDep INNER JOIN Terceros AS TR ON A.IdRespons=TR.IdTercero
 INNER JOIN Companias AS CI ON A.IdCia=CI.IdCia INNER JOIN Terceros AS T ON A.IdProv=T.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_AfDetalle AS DP ON A.IdActivo=DP.IdActivo  LEFT JOIN Trn_AfDeprecia  AS D ON  DP.Numero=D.Numero
 LEFT JOIN adm_Usuarios AS U ON D.IdUsuario=U.IdUsuario
 LEFT JOIN Companias AS CN ON DP.IdCia=CN.IdCia
 LEFT JOIN CentroCosto AS CCD ON DP.CdCCosto=CCD.IdCCosto LEFT JOIN Instalaciones AS INS ON DP.CdInstala=INS.IdInstala
 LEFT JOIN Dependencias AS DE ON DP.CdDep=DE.IdDep LEFT JOIN Terceros AS TRD ON DP.NitRespons=TRD.IdTercero
 LEFT JOIN SubCentros AS SCD ON DP.CdSubCos=SCD.IdSubCos
 WHERE A.IdActivo LIKE ISNULL(@pmIdActivo,'%')
 ORDER BY NomActivo

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
 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
--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 paQryAfDepreciaLta @pmNumeroIni INT,@pmNumeroFin INT
 AS 
 SELECT D.Numero AS NumDeprecia,Fecha,D.Estado AS EstadoDoc,D.IdUsuario AS CdUsuario,Usuario,FechaCrea,D.IdCia AS CdCia
,Item,DP.IdActivo AS CdActivo,NomActivo,VrCostoIni,DP.VrMejoras AS ValMejoras,DP.VrDisminucion AS ValDisminuc,VrAcuAjustes,VrDepreciaHis,VrDepreciaMes,VrAjustesInf,VrAjustesDep,VrDepreciaAcum
,VrAjuDepAcum,TarifaAju,DP.IdCia AS DetCodCia,Compania,CdCCosto,CCosto,CdSubCos,SubCosto,CdInstala,Instlacion,CdDep,Dependencia,NitRespons,RazonSocial,FecIniDep,MesesVida,MetodoDeprec
--datos del activo
,NumSerie,A.IdGrupo AS CdGrupo,Grupo,G.IdLinea AS CdLinea,Linea,Marca,Modelo,NumPlaca,Referencia,A.IdEstado AS CdEstado,ED.Estado AS NomEstado
,FecActivacion,FecFinDep,Ajustable,Contable
 FROM Trn_AfDeprecia AS D INNER JOIN  Trn_AfDetalle AS DP ON D.Numero=DP.Numero
 INNER JOIN adm_Usuarios AS U ON D.IdUsuario=U.IdUsuario
 INNER JOIN Activos AS A ON DP.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 AfEstados AS ED ON A.IdEstado=ED.IdEstado INNER JOIN Companias AS CN ON DP.IdCia=CN.IdCia
 INNER JOIN CentroCosto AS CC ON DP.CdCCosto=CC.IdCCosto INNER JOIN Instalaciones AS INS ON DP.CdInstala=INS.IdInstala
 INNER JOIN Dependencias AS DE ON DP.CdDep=DE.IdDep INNER JOIN Terceros AS TR ON DP.NitRespons=TR.IdTercero
 LEFT JOIN SubCentros AS SC ON DP.CdSubCos=SC.IdSubCos
 WHERE D.Numero BETWEEN @pmNumeroIni  AND  @pmNumeroFin
 ORDER BY D.Numero,Item

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE paQryAfDepreciaRes  @pmFechaIni SMALLDATETIME,@pmFechaFin SMALLDATETIME,@pmIdLinea VARCHAR(4)=Null,@pmIdGrupo VARCHAR(4)=Null
,@pmIdCia CHAR(2)=Null,@pmIdInstala VARCHAR(4)=Null,@pmIdDep VARCHAR(4)=Null,@pmIdRespons VARCHAR(16)=Null,@pmIdCCosto VARCHAR(16)=Null
,@pmIdActivo VARCHAR(30)=Null
 AS 
 SELECT DP.IdActivo AS CdActivo,NomActivo,Fecha,D.Numero AS NumDeprecia,D.IdUsuario AS CdUsuario,Usuario,FechaCrea,D.IdCia AS DepCdCia
,Item,VrCostoIni,DP.VrMejoras AS ValMejoras,DP.VrDisminucion AS ValDisminuc,VrAcuAjustes,VrDepreciaHis,VrDepreciaMes,VrAjustesInf,VrAjustesDep,VrDepreciaAcum
,VrAjuDepAcum,TarifaAju,DP.IdCia AS DetCodCia,CN.Compania AS DetCompania,CdCCosto,CCD.CCosto AS DetCentroCosto,CdSubCos,SCD.SubCosto AS DetSubCentro,CdInstala,INS.Instlacion AS DetInstacion
,CdDep,DE.Dependencia AS DetDependencia,NitRespons,TRD.RazonSocial AS DetNomResponsable,FecIniDep,MesesVida,MetodoDeprec
--datos del activo
,G.IdLinea AS CodLinea,Linea,A.IdGrupo AS CodGrupo,Grupo,A.IdCCosto AS CdCentro,CC.CCosto AS ActCentroCosto,A.IdSubCos AS CdSubcentro,SC.SubCosto AS ActSubCentro,A.IdInstala AS CdInstala,I.Instlacion AS ActInstalacion
,A.IdDep AS CdDep,DEP.Dependencia AS NomDependencia,IdRespons,TR.RazonSocial AS Responsable,A.IdCia AS CdCia,CI.Compania AS NomCia,IdProv,T.RazonSocial AS Proveedor
,FecCompra,FecActivacion,NumOrden,IdCiaOrden,NumEntrada,IdCiaEnt,VrCompra,VrComercial,VrBaseDep,A.VrMejoras AS TotMejoras,A.VrDisminucion AS TotDisminucion,A.VidaUtil AS Vida_util,FecFinDep,MetodoDep
,Ajustable,Contable,CueEnGrupo,VrAjuInfAcum,VrDepAcuHis,VrDepAcuAju,VrAjuInfDep,NumMesesDep,FecUltDep,Marca,Modelo,NumPlaca,NumDoc,Referencia,NumSerie,CodInvent,CodBarras
,A.IdEstado AS CdEstado,ED.Estado AS ActEstado,FecRetiro,A.IdCausal AS CdCausal,Causal,VrVenta,FecCreacion
 FROM  Trn_AfDetalle AS DP  INNER JOIN Trn_AfDeprecia  AS D ON  DP.Numero=D.Numero INNER JOIN adm_Usuarios AS U ON D.IdUsuario=U.IdUsuario
 INNER JOIN Activos AS A ON DP.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 I ON A.IdInstala=I.IdInstala
 INNER JOIN Dependencias AS DEP ON A.IdDep=DEP.IdDep INNER JOIN Terceros AS TR ON A.IdRespons=TR.IdTercero
 INNER JOIN Companias AS CI ON A.IdCia=CI.IdCia INNER JOIN Terceros AS T ON A.IdProv=T.IdTercero
 INNER JOIN AfEstados AS ED ON A.IdEstado=ED.IdEstado 
 INNER JOIN Companias AS CN ON DP.IdCia=CN.IdCia
 LEFT JOIN SubCentros AS SC ON A.IdSubCos=SC.IdSubCos LEFT JOIN AfCausales AS CA ON A.IdCausal=CA.IdCausal
 LEFT JOIN CentroCosto AS CCD ON DP.CdCCosto=CCD.IdCCosto LEFT JOIN Instalaciones AS INS ON DP.CdInstala=INS.IdInstala
 LEFT JOIN Dependencias AS DE ON DP.CdDep=DE.IdDep LEFT JOIN Terceros AS TRD ON DP.NitRespons=TRD.IdTercero
 LEFT JOIN SubCentros AS SCD ON DP.CdSubCos=SCD.IdSubCos
 WHERE  D.Fecha BETWEEN  @pmFechaIni  AND @pmFechaFin AND D.Estado<>0  AND DP.IdActivo LIKE ISNULL(@pmIdActivo,'%')
 AND DP.IdCia LIKE ISNULL(@pmIdCia,'%%')  AND  G.IdLinea LIKE ISNULL(@pmIdLinea,'%') AND A.IdGrupo LIKE ISNULL(@pmIdGrupo,'%')  
 AND DP.CdInstala LIKE ISNULL(@pmIdInstala,'%') AND DP.CdDep LIKE ISNULL(@pmIdDep,'%')
 AND DP.NitRespons LIKE ISNULL(@pmIdRespons,'%')  AND DP.CdCCosto LIKE ISNULL(@pmIdCCosto,'%')
 ORDER BY NomActivo,Fecha
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE paQryAfDifIngresoCr @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 NumIngreso,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,FecActivacion, D.NumPeriodos AS NumMeses, VrBase, VrMejoras, D.VrDisminucion AS ValDisminuc, D.VrAjuInfAcum AS ValAjuInfAcum, D.VrAmortizaHis AS ValAmzaHis
, VrAmortizaAju,D.VrAjuInfAmtza AS ValAjuInflacAmza, NumMesesAmtza, VrCorrMonDif, NumMesesCorr, D.FecUltAmtza AS FecUltAmortza, Concepto
--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,RazonSocial,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_AfDifIngreso 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 
 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 paQryAfDifIngresoLta @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,Anulado,M.Observacion AS Observ,TipCom,Comprobante,IdCiaCom
,M.FecCreacion AS FechaCrea,M.IdUsuario AS CdUsuario,Usuario
, Item,D.IdDiferido AS CdDiferido,NomDiferido,FecActivacion, D.NumPeriodos AS NumMeses, VrBase, VrMejoras, D.VrDisminucion AS ValDisminuc, D.VrAjuInfAcum AS ValAjuInfAcum, D.VrAmortizaHis AS ValAmzaHis
, VrAmortizaAju,D.VrAjuInfAmtza AS ValAjuInflacAmza, NumMesesAmtza, VrCorrMonDif, NumMesesCorr, D.FecUltAmtza AS FecUltAmortza, Concepto
--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,RazonSocial,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_AfDifIngreso 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 
 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 OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE paQryAfDifRetiroCr @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 NumRetiro,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,D.IdCausal AS CdCausal,Causal, VrCostoIni, D.VrAdiciones AS ValAdiciones, D.VrDisminucion AS ValDisminuciones, VrAcuAjustes, VrAmortizaAcum, VrAjuAmtzaAcum, FechaRet, Concepto
--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,RazonSocial,A.IdCia AS DifIdCia,FecInicio,ValorTotal,A.VrAdiciones AS DifVrAdiciones,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,FecRetiro,ComentRetiro
 FROM Trn_AfDifMovi AS M INNER JOIN Trn_AfDifRetiro 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 AfCausales AS CR ON D.IdCausal=CR.IdCausal
 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 
 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 paQryAfDifRetiroLta @pmIdMov VARCHAR(4),@pmNumeroIni INT,@pmNumeroFin INT
,@pmIdCia CHAR(2)
AS
 SELECT M.IdMov AS CdMov,TipoMov,M.Numero AS NumRetiro,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,D.IdCausal AS CdCausal,Causal, VrCostoIni, D.VrAdiciones AS ValAdiciones, D.VrDisminucion AS ValDisminuciones, VrAcuAjustes, VrAmortizaAcum, VrAjuAmtzaAcum, FechaRet, Concepto
--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,RazonSocial,A.IdCia AS DifIdCia,FecInicio,ValorTotal,A.VrAdiciones AS DifVrAdiciones,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,FecRetiro,ComentRetiro
 FROM Trn_AfDifMovi AS M INNER JOIN Trn_AfDifRetiro 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 AfCausales AS CR ON D.IdCausal=CR.IdCausal
 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 
 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 OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE paQryAfDifTrasladoCr @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 NumTraslado,M.IdCia AS CdCia,CI.Compania AS NomCia,Fecha,Anulado,M.Observacion AS Observ,TipCom,Comprobante,IdCiaCom
,M.FecCreacion AS FechaCrea,M.IdUsuario AS CdUsuario,Usuario
,Item,D.IdDiferido AS CdDiferido,NomDiferido,D.IdGrupo AS CdGrupoOri,GOR.Grupo AS GrupoOrigen,D.IdCCosto AS CdCentroOrig,CCO.CCosto AS CentroOrigen,D.IdSubCos AS CdSubCosOrig,SCO.SubCosto AS SubCentroOrig
,D.IdDep AS CdDepOrig,DPO.Dependencia AS DepOrigen,D.IdRespons AS IdResponsOrig,TRO.RazonSocial AS ResponsOrigen,Ubicacion,CdCiaDest,CND.Compania  AS CiaDestino,CdGrupoDest,GDT.Grupo AS NomGrupoDestino,CdCCostoDest,CCD.CCosto AS CentroDestino
,CdSubCosDest,SCD.SubCosto AS SubCenDestino,CdDepDest,DPD.Dependencia AS DepDestino,CdResponsDest,TRD.RazonSocial AS NomRespDestino,UbicacionDest,Concepto
--Datos del diferido
,A.IdTipDif AS CdTipDif,TipoDiferido,G.IdLinea AS CodLinea,L.Linea AS LineaDif,A.IdGrupo AS CodGrupo,G.Grupo AS DifGrupo,A.IdCCosto AS CdCentro,CC.CCosto AS DifCentroCosto,A.IdSubCos AS CdSubcentro,SC.SubCosto AS DifSubCentro
,A.IdDep AS CdDep,DP.Dependencia AS DependDif,DesUbicacion,A.IdRespons AS DifNitRespons,T.RazonSocial AS NombreRespons,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_AfDifTraslado 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 
 INNER JOIN CentroCosto AS CCO ON D.IdCCosto=CCO.IdCCosto  INNER JOIN Dependencias AS DPO ON D.IdDep=DPO.IdDep INNER JOIN Terceros AS TRO ON D.IdRespons=TRO.IdTercero
 INNER JOIN AfGrupos AS  GOR ON D.IdGrupo=GOR.IdGrupo INNER JOIN AfLineas AS LOR ON GOR.IdLinea=LOR.IdLinea
 LEFT JOIN   Companias AS CND ON D.CdCiaDest=CND.IdCia LEFT JOIN AfGrupos AS GDT ON D.CdGrupoDest=GDT.IdGrupo
 LEFT JOIN CentroCosto AS CCD ON D.CdCCostoDest=CCD.IdCCosto 
 LEFT JOIN Dependencias AS DPD ON D.CdDepDest=DPD.IdDep LEFT JOIN Terceros AS TRD ON D.CdResponsDest=TRD.IdTercero
 LEFT JOIN SubCentros AS SC ON A.IdSubCos=SC.IdSubCos LEFT JOIN SubCentros AS SCO ON D.IdSubCos=SCO.IdSubCos
 LEFT JOIN SubCentros AS SCD ON D.CdSubCosDest=SCD.IdSubCos
 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 paQryAfDifTrasladoLta @pmIdMov VARCHAR(4),@pmNumeroIni INT,@pmNumeroFin INT,@pmIdCia CHAR(2)
 AS 
 SELECT M.IdMov AS CdMov,TipoMov,M.Numero AS NumTraslado,M.IdCia AS CdCia,CI.Compania AS NomCia,Fecha,Anulado,M.Observacion AS Observ,TipCom,Comprobante,IdCiaCom
,M.FecCreacion AS FechaCrea,M.IdUsuario AS CdUsuario,Usuario
,Item,D.IdDiferido AS CdDiferido,NomDiferido,D.IdGrupo AS CdGrupoOri,GOR.Grupo AS GrupoOrigen,D.IdCCosto AS CdCentroOrig,CCO.CCosto AS CentroOrigen,D.IdSubCos AS CdSubCosOrig,SCO.SubCosto AS SubCentroOrig
,D.IdDep AS CdDepOrig,DPO.Dependencia AS DepOrigen,D.IdRespons AS IdResponsOrig,TRO.RazonSocial AS ResponsOrigen,Ubicacion,CdCiaDest,CND.Compania  AS CiaDestino,CdGrupoDest,GDT.Grupo AS NomGrupoDestino,CdCCostoDest,CCD.CCosto AS CentroDestino
,CdSubCosDest,SCD.SubCosto AS SubCenDestino,CdDepDest,DPD.Dependencia AS DepDestino,CdResponsDest,TRD.RazonSocial AS NomRespDestino,UbicacionDest,Concepto
--Datos del diferido
,A.IdTipDif AS CdTipDif,TipoDiferido,G.IdLinea AS CodLinea,L.Linea AS LineaDif,A.IdGrupo AS CodGrupo,G.Grupo AS DifGrupo,A.IdCCosto AS CdCentro,CC.CCosto AS DifCentroCosto,A.IdSubCos AS CdSubcentro,SC.SubCosto AS DifSubCentro
,A.IdDep AS CdDep,DP.Dependencia AS DependDif,DesUbicacion,A.IdRespons AS DifNitRespons,T.RazonSocial AS NombreRespons,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_AfDifTraslado 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 
 INNER JOIN CentroCosto AS CCO ON D.IdCCosto=CCO.IdCCosto  INNER JOIN Dependencias AS DPO ON D.IdDep=DPO.IdDep INNER JOIN Terceros AS TRO ON D.IdRespons=TRO.IdTercero
 INNER JOIN AfGrupos AS  GOR ON D.IdGrupo=GOR.IdGrupo INNER JOIN AfLineas AS LOR ON GOR.IdLinea=LOR.IdLinea
 LEFT JOIN   Companias AS CND ON D.CdCiaDest=CND.IdCia LEFT JOIN AfGrupos AS GDT ON D.CdGrupoDest=GDT.IdGrupo
 LEFT JOIN CentroCosto AS CCD ON D.CdCCostoDest=CCD.IdCCosto 
 LEFT JOIN Dependencias AS DPD ON D.CdDepDest=DPD.IdDep LEFT JOIN Terceros AS TRD ON D.CdResponsDest=TRD.IdTercero
 LEFT JOIN SubCentros AS SC ON A.IdSubCos=SC.IdSubCos LEFT JOIN SubCentros AS SCO ON D.IdSubCos=SCO.IdSubCos
 LEFT JOIN SubCentros AS SCD ON D.CdSubCosDest=SCD.IdSubCos
 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 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
--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,RazonSocial,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 
 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
--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,RazonSocial,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 
 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 OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE paQryAfMantnmtoCr @pmNumeroIni INT,@pmNumeroFin INT
AS
 SELECT Numero,M.IdCia AS CdCia,CM.Compania AS CiaMnmto,Fecha,M.IdActivo AS CdActivo,NomActivo,M.NumContrato AS Num_Contrato,NitEmpresa,NomEmpresa
,FinContrato,Diagnostico,M.Comentarios,M.IdUsuario AS CdUsuario,Usuario
,G.IdLinea AS CodLinea,Linea,A.IdGrupo AS CodGrupo,Grupo,A.IdCCosto AS CdCentro,CCosto,A.IdSubCos AS CdSubcentro,SubCosto
,A.IdInstala AS CdInstala,Instlacion,A.IdDep AS CdDep,Dependencia,IdRespons,TR.RazonSocial AS Responsable,A.IdCia AS CdCia,CI.Compania AS CiaAfijo
,VrBaseDep,VrMejoras,VrDisminucion,FecActivacion,A.VidaUtil AS Vida_util,FecFinDep,MetodoDep,Ajustable,Contable
,A.IdEstado AS CdEstado,Estado,FecRetiro
,Mantenimiento,AX.NumContrato AS NContratoActual,FecIniCont,FecFinCont,Periodicidad,FecUltMnmto,NitEmpMnmto,ComentMnmto
 FROM Trn_AfMantnmto AS M INNER JOIN Activos AS A ON M.IdActivo=A.IdActivo INNER JOIN Companias AS CM ON M.IdCia=CM.IdCia
 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 I ON A.IdInstala=I.IdInstala
 INNER JOIN Dependencias AS DP ON A.IdDep=DP.IdDep INNER JOIN Terceros AS TR ON A.IdRespons=TR.IdTercero
 INNER JOIN adm_Usuarios AS U ON M.IdUsuario=U.IdUsuario  INNER JOIN AfEstados AS ED ON A.IdEstado=ED.IdEstado
 INNER JOIN Companias AS CI ON A.IdCia=CI.IdCia LEFT JOIN SubCentros AS SC ON A.IdSubCos=SC.IdSubCos
 LEFT JOIN Activos_Anexo AS AX ON M.IdActivo=AX.IdActivo
 WHERE  Numero BETWEEN @pmNumeroIni  AND @pmNumeroFin
 ORDER BY  Numero

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE paQryAfMovIngresoCr @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 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
--datos del ingreso
,Item,I.IdActivo AS CdActivo,NomActivo,I.FecActivacion AS FecIngreso,VrBaseDep,I.VrMejoras AS ValMejoras,I.VrDisminucion AS ValDisminucion,I.VrAjuInfAcum AS ValAjuInf
,I.VrDepAcuHis AS ValDepHis,I.VrDepAcuAju AS ValDepAcumAju,I.VrAjuInfDep AS ValAjuInfDep,I.NumMesesDep AS NumMeses,I.FecUltDep AS FechaUltDep,Concepto
,A.VidaUtil AS MesesVidaUtil,FecFinDep,MetodoDep,Ajustable,Contable
--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
,IdRespons,TR.RazonSocial AS Responsable,IdProv,TP.RazonSocial AS Proveedor,FecCompra,NumOrden,NumEntrada,A.IdEstado AS CdEstado,Estado
 FROM Trn_AfMovimiento AS M  INNER JOIN Trn_AfMovIngreso AS I ON M.IdMov=I.IdMov AND M.Numero=I.Numero AND M.IdCia=I.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 I.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 Terceros AS TP ON A.IdProv=TP.IdTercero INNER JOIN AfEstados AS ED ON A.IdEstado=ED.IdEstado
 LEFT JOIN SubCentros AS SC ON A.IdSubCos=SC.IdSubCos
 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 I.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 paQryAfMovIngresoLta @pmIdMov VARCHAR(4),@pmNumero 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
--datos del ingreso
,Item,I.IdActivo AS CdActivo,NomActivo,I.FecActivacion AS FecIngreso,VrBaseDep,I.VrMejoras AS ValMejoras,I.VrDisminucion AS ValDisminucion,I.VrAjuInfAcum AS ValAjuInf
,I.VrDepAcuHis AS ValDepHis,I.VrDepAcuAju AS ValDepAcumAju,I.VrAjuInfDep AS ValAjuInfDep,I.NumMesesDep AS NumMeses,I.FecUltDep AS FechaUltDep,Concepto
,A.VidaUtil AS MesesVidaUtil,FecFinDep,MetodoDep,Ajustable,Contable
--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
,IdRespons,TR.RazonSocial AS Responsable,IdProv,TP.RazonSocial AS Proveedor,FecCompra,NumOrden,NumEntrada,A.IdEstado AS CdEstado,Estado
 FROM Trn_AfMovimiento AS M  INNER JOIN Trn_AfMovIngreso AS I ON M.IdMov=I.IdMov AND M.Numero=I.Numero AND M.IdCia=I.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 I.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 Terceros AS TP ON A.IdProv=TP.IdTercero INNER JOIN AfEstados AS ED ON A.IdEstado=ED.IdEstado
 LEFT JOIN SubCentros AS SC ON A.IdSubCos=SC.IdSubCos
 WHERE M.IdMov=@pmIdMov AND M.Numero=@pmNumero AND M.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 paQryAfMovRetiroCr @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 NumRetiro,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,R.IdActivo AS CdActivo,NomActivo,R.IdCausal AS CdCausal,Causal,NitTercero, NomTercero, VrCostoIni, R.VrMejoras AS ValMejoras,R.VrDisminucion AS ValDisminuc, VrAcuAjustes, VrDepAcum
,VrAjuDepAcum, VrIngreso,FechaRet, IdCueDeb, IdCueIng, Concepto
--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
,FecCompra,NumOrden,NumEntrada,FecActivacion,VrBaseDep,A.VrMejoras AS TotMejoras,A.VrDisminucion AS TotDisminuc,A.VidaUtil AS MesesVida,FecFinDep,MetodoDep,Ajustable,Contable,FecUltDep
,A.IdEstado AS CdEstado,Estado
 FROM Trn_AfMovimiento AS M  INNER JOIN Trn_AfMovRetiro AS R ON M.IdMov=R.IdMov AND M.Numero=R.Numero AND M.IdCia=R.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 AfCausales AS CR ON R.IdCausal=CR.IdCausal
 INNER JOIN Activos AS A ON R.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
 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 R.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 paQryAfMovRetiroLta @pmIdMov VARCHAR(4),@pmNumeroIni INT,@pmNumeroFin INT,@pmIdCia CHAR(2)
 AS
 SELECT M.IdMov AS CdMov,TipoMov,M.Numero AS NumRetiro,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,R.IdActivo AS CdActivo,NomActivo,R.IdCausal AS CdCausal,Causal,NitTercero, NomTercero, VrCostoIni, R.VrMejoras AS ValMejoras,R.VrDisminucion AS ValDisminuc, VrAcuAjustes, VrDepAcum
,VrAjuDepAcum, VrIngreso,FechaRet, IdCueDeb, IdCueIng, Concepto
--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
,FecCompra,NumOrden,NumEntrada,FecActivacion,VrBaseDep,A.VrMejoras AS TotMejoras,A.VrDisminucion AS TotDisminuc,A.VidaUtil AS MesesVida,FecFinDep,MetodoDep,Ajustable,Contable,FecUltDep
,A.IdEstado AS CdEstado,Estado
 FROM Trn_AfMovimiento AS M  INNER JOIN Trn_AfMovRetiro AS R ON M.IdMov=R.IdMov AND M.Numero=R.Numero AND M.IdCia=R.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 AfCausales AS CR ON R.IdCausal=CR.IdCausal
 INNER JOIN Activos AS A ON R.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
 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 OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE paQryAfMovTrasladoCr  @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 NumTraslado,M.IdCia AS CdCia,CN.Compania AS CiaOrigen,Fecha,Documento,Anulado,M.Observacion AS Observ,TipCom,Comprobante,IdCiaCom
,M.FecCreacion AS FechaCrea,M.IdUsuario AS CdUsuario,Usuario
--datos del traslado
,Item,T.IdActivo AS CdActivo,NomActivo,T.IdGrupo AS CdGrupoOri,GOR.Grupo AS GrupoOrigen,GOR.IdLinea AS CdLineaOri,LOR.Linea AS LineaOrigen,T.IdCCosto AS CdCentroOrigen,CCO.CCosto AS CentroOrigen
,T.IdSubCos AS CdSubCosOrig,SCO.SubCosto AS SubCentroOrig,T.IdInstala AS CdInsOrigen,ITO.Instlacion AS InsOrigen,T.IdDep AS CdDepOrig,DPO.Dependencia AS DepOrigen,T.IdRespons AS NitRespOrigen,TRO.RazonSocial AS ResponsOrigen
,CdCiaDest,CND.Compania  AS CiaDestino,CdGrupoDest,GDT.Grupo AS NomGrupoDestino,CdCCostoDest,CCD.CCosto AS CentroDestino,CdSubCosDest,SCD.SubCosto AS SubCenDestino,CdInstalaDest
,IDT.Instlacion AS InsDestino,CdDepDest,DPD.Dependencia AS DepDestino,CdResponsDest,TRD.RazonSocial AS NomRespDestino,Concepto,Ubicacion,UbicacionDest
--datos del activo
,NumSerie,A.IdGrupo AS CdGrupo,G.Grupo AS ActGrupo,G.IdLinea AS CdLinea,L.Linea AS ActLinea,A.IdCCosto AS CdCentro,CC.CCosto AS ActivoCentro,A.IdSubCos AS CdSubCentro,SC.SubCosto AS ActSubcentro,A.IdInstala AS CdInstala,INS.Instlacion AS ActInstalacion
,A.IdDep AS CdDep,DP.Dependencia AS ActDepend,Marca,Modelo,NumPlaca,Referencia,FecActivacion,A.VidaUtil,FecFinDep,MetodoDep,FecUltDep
,A.IdRespons AS NitReponsb,TR.RazonSocial AS Responsable,A.IdEstado AS CdEstado,Estado
 FROM Trn_AfMovimiento AS M  INNER JOIN Trn_AfMovTraslado AS T ON M.IdMov=T.IdMov AND M.Numero=T.Numero AND M.IdCia=T.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 T.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 
 INNER JOIN CentroCosto AS CCO ON T.IdCCosto=CCO.IdCCosto INNER JOIN Instalaciones AS ITO ON T.IdInstala=ITO.IdInstala
 INNER JOIN Dependencias AS DPO ON T.IdDep=DPO.IdDep INNER JOIN Terceros AS TRO ON T.IdRespons=TRO.IdTercero
 INNER JOIN AfGrupos AS  GOR ON T.IdGrupo=GOR.IdGrupo INNER JOIN AfLineas AS LOR ON GOR.IdLinea=LOR.IdLinea
 LEFT JOIN   Companias AS CND ON T.CdCiaDest=CND.IdCia LEFT JOIN AfGrupos AS GDT ON T.CdGrupoDest=GDT.IdGrupo
 LEFT JOIN CentroCosto AS CCD ON T.CdCCostoDest=CCD.IdCCosto LEFT JOIN Instalaciones AS IDT ON T.CdInstalaDest=IDT.IdInstala
 LEFT JOIN Dependencias AS DPD ON T.CdDepDest=DPD.IdDep LEFT JOIN Terceros AS TRD ON T.CdResponsDest=TRD.IdTercero
 LEFT JOIN SubCentros AS SC ON A.IdSubCos=SC.IdSubCos LEFT JOIN SubCentros AS SCO ON T.IdSubCos=SCO.IdSubCos
 LEFT JOIN SubCentros AS SCD ON T.CdSubCosDest=SCD.IdSubCos
 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 T.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 paQryAfMovTrasladoLta  @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,CN.Compania AS CiaOrigen,Fecha,Documento,Anulado,M.Observacion AS Observ,TipCom,Comprobante,IdCiaCom
,M.FecCreacion AS FechaCrea,M.IdUsuario AS CdUsuario,Usuario
--datos del traslado
,Item,T.IdActivo AS CdActivo,NomActivo,T.IdGrupo AS CdGrupoOri,GOR.Grupo AS GrupoOrigen,GOR.IdLinea AS CdLineaOri,LOR.Linea AS LineaOrigen,T.IdCCosto AS CdCentroOrigen,CCO.CCosto AS CentroOrigen
,T.IdSubCos AS CdSubCosOrig,SCO.SubCosto AS SubCentroOrig,T.IdInstala AS CdInsOrigen,ITO.Instlacion AS InsOrigen,T.IdDep AS CdDepOrig,DPO.Dependencia AS DepOrigen,T.IdRespons AS NitRespOrigen,TRO.RazonSocial AS ResponsOrigen
,CdCiaDest,CND.Compania  AS CiaDestino,CdGrupoDest,GDT.Grupo AS NomGrupoDestino,CdCCostoDest,CCD.CCosto AS CentroDestino,CdSubCosDest,SCD.SubCosto AS SubCenDestino,CdInstalaDest
,IDT.Instlacion AS InsDestino,CdDepDest,DPD.Dependencia AS DepDestino,CdResponsDest,TRD.RazonSocial AS NomRespDestino,Concepto,Ubicacion,UbicacionDest
--datos del activo
,NumSerie,A.IdGrupo AS CdGrupo,G.Grupo AS ActGrupo,G.IdLinea AS CdLinea,L.Linea AS ActLinea,A.IdCCosto AS CdCentro,CC.CCosto AS ActivoCentro,A.IdSubCos AS CdSubCentro,SC.SubCosto AS ActSubcentro,A.IdInstala AS CdInstala,INS.Instlacion AS ActInstalacion
,A.IdDep AS CdDep,DP.Dependencia AS ActDepend,Marca,Modelo,NumPlaca,Referencia,FecActivacion,A.VidaUtil,FecFinDep,MetodoDep,FecUltDep
,A.IdRespons AS NitReponsb,TR.RazonSocial AS Responsable,A.IdEstado AS CdEstado,Estado
 FROM Trn_AfMovimiento AS M  INNER JOIN Trn_AfMovTraslado AS T ON M.IdMov=T.IdMov AND M.Numero=T.Numero AND M.IdCia=T.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 T.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 
 INNER JOIN CentroCosto AS CCO ON T.IdCCosto=CCO.IdCCosto INNER JOIN Instalaciones AS ITO ON T.IdInstala=ITO.IdInstala
 INNER JOIN Dependencias AS DPO ON T.IdDep=DPO.IdDep INNER JOIN Terceros AS TRO ON T.IdRespons=TRO.IdTercero
 INNER JOIN AfGrupos AS  GOR ON T.IdGrupo=GOR.IdGrupo INNER JOIN AfLineas AS LOR ON GOR.IdLinea=LOR.IdLinea
 LEFT JOIN   Companias AS CND ON T.CdCiaDest=CND.IdCia LEFT JOIN AfGrupos AS GDT ON T.CdGrupoDest=GDT.IdGrupo
 LEFT JOIN CentroCosto AS CCD ON T.CdCCostoDest=CCD.IdCCosto LEFT JOIN Instalaciones AS IDT ON T.CdInstalaDest=IDT.IdInstala
 LEFT JOIN Dependencias AS DPD ON T.CdDepDest=DPD.IdDep LEFT JOIN Terceros AS TRD ON T.CdResponsDest=TRD.IdTercero
 LEFT JOIN SubCentros AS SC ON A.IdSubCos=SC.IdSubCos LEFT JOIN SubCentros AS SCO ON T.IdSubCos=SCO.IdSubCos
 LEFT JOIN SubCentros AS SCD ON T.CdSubCosDest=SCD.IdSubCos
 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 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
--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
 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
--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
 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 OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE paQryAfPignoraCr  @pmNumeroIni INT, @pmNumeroFin INT
 AS 
 SELECT Numero,Fecha,P.IdActivo AS CdActivo,NomActivo,TipoAccion,NomTercero,Comentarios,Garantia
,G.IdLinea AS CodLinea,Linea,A.IdGrupo AS CodGrupo,Grupo,A.IdCCosto AS CdCentro,CCosto,A.IdSubCos AS CdSubcentro,SubCosto
,A.IdInstala AS CdInstala,Instlacion,A.IdDep AS CdDep,Dependencia,IdRespons,TR.RazonSocial AS Responsable,A.IdCia AS CdCia,Compania
,VrBaseDep,VrMejoras,VrDisminucion,FecActivacion,A.VidaUtil AS Vida_util,FecFinDep,MetodoDep,Ajustable,Contable
,A.IdEstado AS CdEstado,Estado,FecRetiro,P.IdUsuario AS IdUsuari,Usuario
 FROM Trn_AfPignora AS P INNER JOIN Activos AS A ON P.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 I ON A.IdInstala=I.IdInstala
 INNER JOIN Dependencias AS DP ON A.IdDep=DP.IdDep INNER JOIN Terceros AS TR ON A.IdRespons=TR.IdTercero
 INNER JOIN adm_Usuarios AS U ON P.IdUsuario=U.IdUsuario  INNER JOIN AfEstados AS ED ON A.IdEstado=ED.IdEstado
 INNER JOIN Companias AS CI ON A.IdCia=CI.IdCia LEFT JOIN SubCentros AS SC ON A.IdSubCos=SC.IdSubCos
 WHERE  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 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
 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

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

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

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

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

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

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE paInsTercCndtores @pmIdConductor VARCHAR(16),@pmIdGrupo VARCHAR(4),@pmIdModd VARCHAR(4),@pmTipoSangre CHAR(2),@pmFactorRh CHAR(1),@pmLibMilitar VARCHAR(25),@pmClaseLib INT,@pmFechaLib SMALLDATETIME,@pmDistMil VARCHAR(10)
,@pmCertJudicial VARCHAR(25),@pmFecJudicial SMALLDATETIME,@pmVigJudicial SMALLDATETIME,@pmLicencia VARCHAR(25),@pmIdLugar VARCHAR(8),@pmCatLicencia INT,@pmFecLicencia SMALLDATETIME,@pmVigLicencia SMALLDATETIME
,@pmFecNacmto SMALLDATETIME,@pmLugarNacmto VARCHAR(30),@pmSexo CHAR(1),@pmIdEstCivil VARCHAR(4),@pmNHijos INT,@pmPasporte VARCHAR(25),@pmVigPaspte SMALLDATETIME
,@pmIdClase VARCHAR(4),@pmNumCuenta VARCHAR(30),@pmIdBanco VARCHAR(4),@pmCdLocCue VARCHAR(8),@pmCdFonPen VARCHAR(8),@pmCdFonEps VARCHAR(8),@pmCdFonArp VARCHAR(8),@pmpVehiculo VARCHAR(10),@pmPathFoto VARCHAR(30)
,@pmPathFirma VARCHAR(30),@pmPathLic VARCHAR(30),@pmCmntarios VARCHAR(250),@pmIdEstado VARCHAR(4),@pmInactivo BIT,@pmNumTarjeta VARCHAR(30),@pmEdicTarjeta INT,@pmFecTarjeta SMALLDATETIME
,@pmVigTarjeta SMALLDATETIME,@pmPasSeguridad VARCHAR(30),@pmFecPasSeg SMALLDATETIME,@pmVigPasSeg SMALLDATETIME,@pmNitEmpTrans VARCHAR(16),@pmFechaAdd SMALLDATETIME,@pmIdUsuario VARCHAR(11)
,@pmNum_Contrato INT,@pmContrato_Activo BIT,@pmCdTipCon VARCHAR(4),@pmFec_Ingreso SMALLDATETIME,@pmFec_Retiro SMALLDATETIME,@pmFec_Vigencia SMALLDATETIME,@pmFec_IngresoAnt SMALLDATETIME,@pmFec_UltimoAcc SMALLDATETIME
,@pmPasadoJudicial VARCHAR(250),@pmRestriccion VARCHAR(50)
 AS 
 INSERT INTO TercCndtores (IdConductor,IdGrupo,IdModd,TipoSangre,FactorRh,LibMilitar,ClaseLib,FechaLib,DistMil,CertJudicial,FecJudicial,VigJudicial,Licencia,IdLugar,CatLicencia,FecLicencia,VigLicencia,IdClase,NumCuenta,IdBanco,CdLocCue,CdFonPen,CdFonEps,CdFonArp,pVehiculo
,FecNacmto,LugarNacmto,Sexo,IdEstCivil,NHijos,Pasporte,VigPaspte,PathFoto,PathFirma,PathLic,Cmntarios,IdEstado,Inactivo,NumTarjeta,EdicTarjeta,FecTarjeta,VigTarjeta,PasSeguridad,FecPasSeg,VigPasSeg,NitEmpTrans,FechaAdd,IdUsuario,Num_Contrato,Contrato_Activo,CdTipCon,Fec_Ingreso
 ,Fec_Retiro,Fec_Vigencia,Fec_IngresoAnt,Fec_UltimoAcc,PasadoJudicial,Restriccion)
 VALUES (@pmIdConductor,@pmIdGrupo,@pmIdModd,@pmTipoSangre,@pmFactorRh,@pmLibMilitar,@pmClaseLib,@pmFechaLib,@pmDistMil,@pmCertJudicial,@pmFecJudicial,@pmVigJudicial,@pmLicencia,@pmIdLugar,@pmCatLicencia,@pmFecLicencia,@pmVigLicencia,@pmIdClase
,@pmNumCuenta,@pmIdBanco,@pmCdLocCue,@pmCdFonPen,@pmCdFonEps,@pmCdFonArp,@pmpVehiculo,@pmFecNacmto,@pmLugarNacmto,@pmSexo,@pmIdEstCivil,@pmNHijos,@pmPasporte,@pmVigPaspte,@pmPathFoto,@pmPathFirma,@pmPathLic,@pmCmntarios
,@pmIdEstado,@pmInactivo,@pmNumTarjeta,@pmEdicTarjeta,@pmFecTarjeta,@pmVigTarjeta,@pmPasSeguridad,@pmFecPasSeg,@pmVigPasSeg,@pmNitEmpTrans,@pmFechaAdd,@pmIdUsuario,@pmNum_Contrato,@pmContrato_Activo,@pmCdTipCon,@pmFec_Ingreso
 ,@pmFec_Retiro,@pmFec_Vigencia,@pmFec_IngresoAnt,@pmFec_UltimoAcc,@pmPasadoJudicial,@pmRestriccion)
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE paInsTercCndtores_Sel @pmIdConductor VARCHAR(16),@pmNewConductor VARCHAR(16)
 AS 
 IF EXISTS (SELECT IdConductor FROM  TercCndtores WHERE IdConductor=@pmIdConductor)
  INSERT INTO TercCndtores (IdConductor,IdGrupo,IdModd,TipoSangre,FactorRh,LibMilitar,ClaseLib,FechaLib,DistMil,CertJudicial,FecJudicial,VigJudicial,Licencia,IdLugar,CatLicencia,FecLicencia,VigLicencia,IdClase,NumCuenta,IdBanco,CdLocCue,CdFonPen,CdFonEps,CdFonArp,pVehiculo
 ,FecNacmto,LugarNacmto,Sexo,IdEstCivil,NHijos,Pasporte,VigPaspte,PathFoto,PathFirma,PathLic,Cmntarios,IdEstado,Inactivo,NumTarjeta,EdicTarjeta,FecTarjeta,VigTarjeta,PasSeguridad,FecPasSeg,VigPasSeg,NitEmpTrans,FechaAdd,IdUsuario
,Num_Contrato,Contrato_Activo,CdTipCon,Fec_Ingreso,Fec_Retiro,Fec_Vigencia,Fec_IngresoAnt,Fec_UltimoAcc,PasadoJudicial,Restriccion)
  SELECT @pmNewConductor,IdGrupo,IdModd,TipoSangre,FactorRh,LibMilitar,ClaseLib,FechaLib,DistMil,CertJudicial,FecJudicial,VigJudicial,Licencia,IdLugar,CatLicencia,FecLicencia,VigLicencia,IdClase,NumCuenta,IdBanco,CdLocCue,CdFonPen,CdFonEps,CdFonArp,pVehiculo
 ,FecNacmto,LugarNacmto,Sexo,IdEstCivil,NHijos,Pasporte,VigPaspte,PathFoto,PathFirma,PathLic,Cmntarios,IdEstado,Inactivo,NumTarjeta,EdicTarjeta,FecTarjeta,VigTarjeta,PasSeguridad,FecPasSeg,VigPasSeg,NitEmpTrans,FechaAdd,IdUsuario
,Num_Contrato,Contrato_Activo,CdTipCon,Fec_Ingreso,Fec_Retiro,Fec_Vigencia,Fec_IngresoAnt,Fec_UltimoAcc,PasadoJudicial,Restriccion
  FROM TercCndtores 
  WHERE IdConductor=@pmIdConductor
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE paQryTercCndtores @pmIdConductor VARCHAR(16)
 AS 
 SELECT IdConductor,IdGrupo,IdModd,TipoSangre,FactorRh,FecNacmto,LugarNacmto,Sexo,IdEstCivil,NHijos,LibMilitar,ClaseLib,FechaLib,DistMil,CertJudicial
,FecJudicial,VigJudicial,Pasporte,VigPaspte,Licencia,IdLugar,CatLicencia,FecLicencia,VigLicencia,IdClase,NumCuenta,IdBanco,CdLocCue,CdFonPen,CdFonEps
,CdFonArp,pVehiculo,PathFoto,PathFirma,PathLic,Cmntarios,IdEstado,Inactivo,NumTarjeta,EdicTarjeta,FecTarjeta,VigTarjeta,PasSeguridad,FecPasSeg,VigPasSeg,NitEmpTrans
,FechaAdd,FechaUpdate,IdUsuario,Num_Contrato,Contrato_Activo,CdTipCon,Fec_Ingreso,Fec_Retiro,Fec_Vigencia,Fec_IngresoAnt,Fec_UltimoAcc,PasadoJudicial,Restriccion
 FROM TercCndtores
 WHERE IdConductor=@pmIdConductor
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE paQryTercCndtoresLta @pmIdLocal VARCHAR(8)=Null,@pmIdProf VARCHAR(4)=Null,@pmIdGrupo VARCHAR(4)=Null
,@pmIdBanco VARCHAR(4)=Null,@pmIdLugar VARCHAR(8)=Null,@pmCatLicencia INT=Null,@pmIdModd VARCHAR(4)=Null,@pmEsEmpleado BIT=Null
,@pmEsPropietario BIT=Null,@pmIdEstado VARCHAR(4)=Null,@pmIdEstadoTer VARCHAR(4)=Null,@pmInactivo BIT=Null,@pmInactivoTer BIT=Null
 AS 
 SELECT IdConductor,RazonSocial,T.Codigo AS CodCondtor,TipoId,Dv,FecExpCed,IdLugarCed,LC.Localidad AS LugarCedula,T.Direccion AS Dirccion,T.IdLocal AS CodCiudad
,L.Localidad AS Ciudad,L.IdDep AS CodDpto,Departamento,Telefono,Fax,TelMovil,e_mail,T.IdProf AS CdProf,Profesion,C.IdGrupo AS CodGrupo,GrupoClie,C.IdModd AS CdModd,Modalidad,TipoSangre,FactorRh
,FecNacmto,LugarNacmto,Sexo,IdEstCivil,EstCivil,NHijos,LibMilitar,ClaseLib,FechaLib,DistMil,CertJudicial,FecJudicial,VigJudicial,Licencia,C.IdLugar AS CodLugarLic,LugarLic,CatLicencia,FecLicencia,VigLicencia,C.IdClase AS CdClaseCta
,Pasporte,VigPaspte,ClaseCuenta,NumCuenta,C.IdBanco AS CodBanco,Banco,CdLocCue,LCT.Localidad AS LugarCuenta,CdFonPen,FP.Fondo AS FondoPen,CdFonEps,FS.Fondo AS Eps,CdFonArp
,FR.Fondo AS FondoRiesgo,pVehiculo,PathFoto,PathFirma,PathLic,Observacion,Cmntarios,EsCliente,EsVendedor,EsPropietario,EsEmpleado,EsOperario,IniStgNom
,C.IdEstado AS CdEstado,EC.Estado AS EstadoCond,C.Inactivo AS Inactiv,T.IdEstado AS IdEstdTerc,ET.Estado AS EstadoTerc,T.Inactivo AS Ter_Inactivo
,NumTarjeta,EdicTarjeta,FecTarjeta,VigTarjeta,PasSeguridad,FecPasSeg,VigPasSeg,NitEmpTrans,C.IdUsuario AS IdUsuari,Usuario,EC.NColor AS EstdNColor,C.FechaAdd AS Fec_Add,C.FechaUpdate AS Fec_Upd
,Num_Contrato,Contrato_Activo,CdTipCon,Fec_Ingreso,Fec_Retiro,Fec_Vigencia,Fec_IngresoAnt,Fec_UltimoAcc,PasadoJudicial,Restriccion
 FROM TercCndtores AS C INNER JOIN Terceros AS T ON C.IdConductor=T.IdTercero
 INNER JOIN GruposCli AS G ON C.IdGrupo=G.IdGrupo INNER JOIN ExpLicencias AS LL ON C.IdLugar=LL.IdLugar
 INNER JOIN ClaseCta AS CTA ON  C.IdClase=CTA.IdClase  INNER JOIN Bancos AS B ON C.IdBanco=B.IdBanco
 INNER JOIN EstadoTer AS EC ON C.IdEstado=EC.IdEstado INNER JOIN adm_Usuarios AS U ON C.IdUsuario=U.IdUsuario
 INNER JOIN TiposMod AS M ON C.IdModd=M.IdModd INNER JOIN EstadoCiv AS ECV ON C.IdEstCivil=ECV.IdEstado
 INNER JOIN Localidades AS L ON T.IdLocal=L.IdLocal INNER JOIN Departamentos AS D ON L.IdDep=D.IdDep
 INNER JOIN Profesiones AS PF ON T.IdProf=PF.IdProf  INNER JOIN EstadoTer AS ET ON T.IdEstado=ET.IdEstado
 INNER JOIN  Localidades AS LC ON T.IdLugarCed=LC.IdLocal 
 LEFT JOIN  Localidades AS LCT ON  C.CdLocCue=LCT.IdLocal 
 LEFT JOIN Fondos AS FP ON C.CdFonPen=FP.IdFondo LEFT JOIN Fondos AS FS ON C.CdFonEps=FS.IdFondo
 LEFT JOIN Fondos AS FR ON C.CdFonArp=FR.IdFondo
 WHERE T.IdLocal LIKE ISNULL(@pmIdLocal,'%')  AND T.IdProf LIKE ISNULL(@pmIdProf,'%')
 AND C.IdGrupo LIKE ISNULL(@pmIdGrupo,'%')   AND C.IdBanco LIKE ISNULL(@pmIdBanco,'%')
 AND C.IdLugar LIKE ISNULL(@pmIdLugar,'%') AND C.IdModd LIKE ISNULL(@pmIdModd,'%')
 AND C.IdEstado LIKE ISNULL(@pmIdEstado,'%') AND T.IdEstado LIKE ISNULL(@pmIdEstadoTer,'%')
 AND (CatLicencia>=ISNULL(@pmCatLicencia,0)  AND CatLicencia<=ISNULL(@pmCatLicencia,2147483647))
 AND (C.Inactivo=ISNULL(@pmInactivo,0) or C.Inactivo=ISNULL(@pmInactivo,1))
 AND (T.Inactivo=ISNULL(@pmInactivoTer,0) or T.Inactivo=ISNULL(@pmInactivoTer,1))
 AND (EsEmpleado=ISNULL(@pmEsEmpleado,0) or EsEmpleado=ISNULL(@pmEsEmpleado,1))
 AND (EsPropietario=ISNULL(@pmEsPropietario,0) or EsPropietario=ISNULL(@pmEsPropietario,1))
 ORDER BY T.RazonSocial
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE paUpTercCndtores @pmIdConductor VARCHAR(16),@pmIdGrupo VARCHAR(4),@pmIdModd VARCHAR(4),@pmTipoSangre CHAR(2),@pmFactorRh CHAR(1),@pmLibMilitar VARCHAR(25),@pmClaseLib INT,@pmFechaLib SMALLDATETIME,@pmDistMil VARCHAR(10),@pmCertJudicial VARCHAR(25)
,@pmFecJudicial SMALLDATETIME,@pmVigJudicial SMALLDATETIME,@pmLicencia VARCHAR(25),@pmIdLugar VARCHAR(8),@pmCatLicencia INT,@pmFecLicencia SMALLDATETIME,@pmVigLicencia SMALLDATETIME
,@pmFecNacmto SMALLDATETIME,@pmLugarNacmto VARCHAR(30),@pmSexo CHAR(1),@pmIdEstCivil VARCHAR(4),@pmNHijos INT,@pmPasporte VARCHAR(25),@pmVigPaspte SMALLDATETIME,@pmIdClase VARCHAR(4),@pmNumCuenta VARCHAR(30),@pmIdBanco VARCHAR(4)
,@pmCdLocCue VARCHAR(8),@pmCdFonPen VARCHAR(8),@pmCdFonEps VARCHAR(8),@pmCdFonArp VARCHAR(8),@pmpVehiculo VARCHAR(10),@pmPathFoto VARCHAR(30),@pmPathFirma VARCHAR(30),@pmPathLic VARCHAR(30),@pmCmntarios VARCHAR(250)
,@pmIdEstado VARCHAR(4),@pmInactivo BIT,@pmNumTarjeta VARCHAR(30),@pmEdicTarjeta INT,@pmFecTarjeta SMALLDATETIME,@pmVigTarjeta SMALLDATETIME,@pmPasSeguridad VARCHAR(30),@pmFecPasSeg SMALLDATETIME,@pmVigPasSeg SMALLDATETIME,@pmNitEmpTrans VARCHAR(16),@pmFechaUpdate SMALLDATETIME
,@pmNum_Contrato INT,@pmContrato_Activo BIT,@pmCdTipCon VARCHAR(4),@pmFec_Ingreso SMALLDATETIME,@pmFec_Retiro SMALLDATETIME,@pmFec_Vigencia SMALLDATETIME,@pmFec_IngresoAnt SMALLDATETIME,@pmFec_UltimoAcc SMALLDATETIME
,@pmPasadoJudicial VARCHAR(250),@pmRestriccion VARCHAR(50)
 AS 
 UPDATE TercCndtores SET IdGrupo=@pmIdGrupo,IdModd=@pmIdModd,TipoSangre=@pmTipoSangre,FactorRh=@pmFactorRh,LibMilitar=@pmLibMilitar,ClaseLib=@pmClaseLib,FechaLib=@pmFechaLib,DistMil=@pmDistMil,CertJudicial=@pmCertJudicial,FecJudicial=@pmFecJudicial,VigJudicial=@pmVigJudicial
,Licencia=@pmLicencia,IdLugar=@pmIdLugar,CatLicencia=@pmCatLicencia,FecLicencia=@pmFecLicencia,VigLicencia=@pmVigLicencia,IdClase=@pmIdClase,NumCuenta=@pmNumCuenta,IdBanco=@pmIdBanco,CdLocCue=@pmCdLocCue,CdFonPen=@pmCdFonPen,CdFonEps=@pmCdFonEps
,CdFonArp=@pmCdFonArp,FecNacmto=@pmFecNacmto,LugarNacmto=@pmLugarNacmto,Sexo=@pmSexo,IdEstCivil=@pmIdEstCivil,NHijos=@pmNHijos,Pasporte=@pmPasporte,VigPaspte=@pmVigPaspte,pVehiculo=@pmpVehiculo,PathFoto=@pmPathFoto,PathFirma=@pmPathFirma
,PathLic=@pmPathLic,Cmntarios=@pmCmntarios,IdEstado=@pmIdEstado,Inactivo=@pmInactivo,NumTarjeta=@pmNumTarjeta,EdicTarjeta=@pmEdicTarjeta,FecTarjeta=@pmFecTarjeta,VigTarjeta=@pmVigTarjeta,PasSeguridad=@pmPasSeguridad,FecPasSeg=@pmFecPasSeg,VigPasSeg=@pmVigPasSeg
,NitEmpTrans=@pmNitEmpTrans,FechaUpdate=@pmFechaUpdate,Num_Contrato=@pmNum_Contrato,Contrato_Activo=@pmContrato_Activo,CdTipCon=@pmCdTipCon,Fec_Ingreso=@pmFec_Ingreso,Fec_Retiro=@pmFec_Retiro,Fec_Vigencia=@pmFec_Vigencia,Fec_IngresoAnt=@pmFec_IngresoAnt
,Fec_UltimoAcc=@pmFec_UltimoAcc,PasadoJudicial=@pmPasadoJudicial,Restriccion=@pmRestriccion
 WHERE IdConductor=@pmIdConductor
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

