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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE PROCEDURE paDelTiposPrend @pmIdPrend VARCHAR(4)
 AS 
 DELETE FROM TiposPrend
 WHERE IdPrend=@pmIdPrend

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE PROCEDURE paDelTiposZona @pmIdTipZona VARCHAR(4)
 AS 
 DELETE FROM TiposZona
 WHERE IdTipZona=@pmIdTipZona

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE paInsMayContratos @pmNContrato INT,@pmIdCia CHAR(2),@pmFecha SMALLDATETIME,@pmIdCliente VARCHAR(16),@pmIdAgencia VARCHAR(16),@pmIdTipo VARCHAR(4),@pmIdBonif VARCHAR(4),@pmConvBonif BIT,@pmCuotaFija BIT
,@pmCondPago BIT,@pmDiasGracia INT,@pmModPlazo VARCHAR(10),@pmPlazo INT,@pmFechaVence SMALLDATETIME,@pmModRecursos VARCHAR(150),@pmVrInversion MONEY,@pmVrInventario MONEY,@pmVrCuota MONEY,@pmModCuota VARCHAR(10)
,@pmIdBandera VARCHAR(4),@pmAprobJunta INT,@pmFechaAprob SMALLDATETIME,@pmObservAprob VARCHAR(250),@pmObservacion VARCHAR(250),@pmIdVend VARCHAR(16),@pmIdEstado VARCHAR(4),@pmTimeSys SMALLDATETIME,@pmIdCiaCrea CHAR(2),@pmIdUsuario VARCHAR(11)
,@pmFechaInicio SMALLDATETIME,@pmCuotaMin DECIMAL(14,4),@pmTipoAprob VARCHAR(10),@pmNumInterno VARCHAR(20),@pmInvCombus DECIMAL(14,4),@pmCuotaBase DECIMAL(14,4),@pmFechaFirma SMALLDATETIME,@pmCdAprobNeg VARCHAR(4),@pmArchivoAprob VARCHAR(50)
,@pmClaseContrato VARCHAR(10),@pmDescTipoCont VARCHAR(250),@pmGarantHipoteca BIT,@pmGarantPrenda BIT,@pmGarantPoliza BIT,@pmGarantBanco BIT,@pmGarantPagare BIT,@pmGarantOtro BIT,@pmCdTipoBien VARCHAR(4),@pmVrAvaludo MONEY,@pmNroEscritura VARCHAR(50)
,@pmCdTipoPoliza VARCHAR(4),@pmDescPoliza VARCHAR(150),@pmVigencPoliza INT,@pmFecVencPoliza SMALLDATETIME,@pmValorPoliza MONEY,@pmEdoGarantias INT,@pmDescGarantOtro VARCHAR(250),@pmInverContab INT,@pmInverContrac INT,@pmVolContMes DECIMAL(14,4)
,@pmVolContTotal DECIMAL(14,4),@pmVolViabMes DECIMAL(14,4),@pmVolViabTotal DECIMAL(14,4),@pmInvCtbObra MONEY,@pmInvCtbCapital MONEY,@pmInvCtbEfectivo MONEY,@pmInvCtbImagen MONEY,@pmInvCtbMntmto MONEY,@pmFecDesemb SMALLDATETIME,@pmInvContObra MONEY
,@pmInvContCapital MONEY,@pmInvContEfectivo MONEY,@pmInvContImagen MONEY,@pmCrtlAprovInv INT,@pmCrtlAprovImg INT,@pmCdTipoBloq VARCHAR(4),@pmEdoDocmtos INT,@pmDescEdoDoc VARCHAR(250),@pmRentabInicial DECIMAL(14,4),@pmGraciaBonif INT
,@pmActFechaIni INT,@pmCdZonaCont VARCHAR(4),@pmContVigente BIT,@pmVolContrac INT,@pmDescHipotec VARCHAR(250),@pmVrAvaComerc MONEY,@pmFecRegEscritura SMALLDATETIME,@pmNotariaEsc VARCHAR(50),@pmCdTipPrenda VARCHAR(4),@pmVrAvaPrenda MONEY
,@pmPolizaPrenda VARCHAR(30),@pmFecVigPrenda SMALLDATETIME,@pmVrAsegPrenda MONEY
 AS 
 INSERT INTO Trn_MayContratos (NContrato,IdCia,Fecha,IdCliente,IdAgencia,IdTipo,IdBonif,ConvBonif,CuotaFija,CondPago,DiasGracia,ModPlazo,Plazo,FechaVence,ModRecursos,VrInversion,VrInventario,VrCuota,ModCuota,IdBandera,AprobJunta,FechaAprob
,ObservAprob,Observacion,IdVend,IdEstado,TimeSys,IdCiaCrea,IdUsuario,FechaInicio,CuotaMin,TipoAprob,NumInterno,InvCombus,CuotaBase
,FechaFirma,CdAprobNeg,ArchivoAprob,ClaseContrato,DescTipoCont,GarantHipoteca,GarantPrenda,GarantPoliza,GarantBanco,GarantPagare,GarantOtro,CdTipoBien,VrAvaludo,NroEscritura,CdTipoPoliza,DescPoliza,VigencPoliza,FecVencPoliza,ValorPoliza,EdoGarantias,DescGarantOtro,InverContab,InverContrac
,VolContMes,VolContTotal,VolViabMes,VolViabTotal,InvCtbObra,InvCtbCapital,InvCtbEfectivo,InvCtbImagen,InvCtbMntmto,FecDesemb,InvContObra,InvContCapital,InvContEfectivo,InvContImagen,CrtlAprovInv,CrtlAprovImg,CdTipoBloq,EdoDocmtos,DescEdoDoc,RentabInicial,GraciaBonif
,ActFechaIni,CdZonaCont,ContVigente,VolContrac,DescHipotec,VrAvaComerc,FecRegEscritura,NotariaEsc,CdTipPrenda,VrAvaPrenda,PolizaPrenda,FecVigPrenda,VrAsegPrenda)
 VALUES (@pmNContrato,@pmIdCia,@pmFecha,@pmIdCliente,@pmIdAgencia,@pmIdTipo,@pmIdBonif,@pmConvBonif,@pmCuotaFija,@pmCondPago,@pmDiasGracia,@pmModPlazo,@pmPlazo,@pmFechaVence,@pmModRecursos,@pmVrInversion
,@pmVrInventario,@pmVrCuota,@pmModCuota,@pmIdBandera,@pmAprobJunta,@pmFechaAprob,@pmObservAprob,@pmObservacion,@pmIdVend,@pmIdEstado,@pmTimeSys,@pmIdCiaCrea,@pmIdUsuario,@pmFechaInicio,@pmCuotaMin,@pmTipoAprob,@pmNumInterno,@pmInvCombus,@pmCuotaBase
,@pmFechaFirma,@pmCdAprobNeg,@pmArchivoAprob,@pmClaseContrato,@pmDescTipoCont,@pmGarantHipoteca,@pmGarantPrenda,@pmGarantPoliza,@pmGarantBanco,@pmGarantPagare,@pmGarantOtro,@pmCdTipoBien,@pmVrAvaludo,@pmNroEscritura,@pmCdTipoPoliza,@pmDescPoliza,@pmVigencPoliza,@pmFecVencPoliza,@pmValorPoliza
,@pmEdoGarantias,@pmDescGarantOtro,@pmInverContab,@pmInverContrac,@pmVolContMes,@pmVolContTotal,@pmVolViabMes,@pmVolViabTotal,@pmInvCtbObra,@pmInvCtbCapital,@pmInvCtbEfectivo,@pmInvCtbImagen,@pmInvCtbMntmto,@pmFecDesemb,@pmInvContObra,@pmInvContCapital,@pmInvContEfectivo,@pmInvContImagen
,@pmCrtlAprovInv,@pmCrtlAprovImg,@pmCdTipoBloq,@pmEdoDocmtos,@pmDescEdoDoc,@pmRentabInicial,@pmGraciaBonif,@pmActFechaIni,@pmCdZonaCont,@pmContVigente,@pmVolContrac,@pmDescHipotec,@pmVrAvaComerc,@pmFecRegEscritura,@pmNotariaEsc,@pmCdTipPrenda,@pmVrAvaPrenda,@pmPolizaPrenda,@pmFecVigPrenda,@pmVrAsegPrenda)
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE PROCEDURE paInsMercancias @pmIdMercancia VARCHAR(16),@pmDescripMcia VARCHAR(250),@pmCodigoMcia VARCHAR(16),@pmIdGrupo VARCHAR(10),@pmUndMed VARCHAR(10)
,@pmIdUnd VARCHAR(4),@pmIdEmp VARCHAR(4),@pmIdNat VARCHAR(4),@pmIdMnjo VARCHAR(4),@pmIdTmcia VARCHAR(4),@pmEstadoMcia VARCHAR(20),@pmContenedor BIT
,@pmIdProducto VARCHAR(16),@pmIdEstado VARCHAR(4),@pmInactivo BIT,@pmFechaAdd SMALLDATETIME,@pmIdUsuario VARCHAR(11)
 AS 
 INSERT INTO Mercancias (IdMercancia,DescripMcia,CodigoMcia,IdGrupo,UndMed,IdUnd,IdEmp,IdNat,IdMnjo,IdTmcia,EstadoMcia,Contenedor,IdProducto,IdEstado,Inactivo,FechaAdd,IdUsuario)
 VALUES (@pmIdMercancia,@pmDescripMcia,@pmCodigoMcia,@pmIdGrupo,@pmUndMed,@pmIdUnd,@pmIdEmp,@pmIdNat,@pmIdMnjo,@pmIdTmcia,@pmEstadoMcia,@pmContenedor,@pmIdProducto
,@pmIdEstado,@pmInactivo,@pmFechaAdd,@pmIdUsuario)
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE PROCEDURE paInsTiposPrend @pmIdPrend VARCHAR(4),@pmTipoPrenda VARCHAR(50),@pmInactivo BIT
 AS 
 INSERT INTO TiposPrend (IdPrend,TipoPrenda,Inactivo)
 VALUES (@pmIdPrend,@pmTipoPrenda,@pmInactivo)

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE PROCEDURE paInsTiposZona @pmIdTipZona VARCHAR(4),@pmTipoZona VARCHAR(50),@pmInactivo BIT
 AS 
 INSERT INTO TiposZona (IdTipZona,TipoZona,Inactivo)
 VALUES (@pmIdTipZona,@pmTipoZona,@pmInactivo)

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE paQryMayAgenciasLta @pmNContratoIni INT,@pmNContratoFin INT,@pmIdCia CHAR(2)
 AS 
 SELECT  Item,CA.IdAgencia AS CdAgencia,Agencia,CodAgencia,A.IdClie AS NitCliente,RazonSocial,A.IdLocal AS CdCiuAgenc,L.Localidad AS CiudadAgenc
,L.IdDep AS CdDptoAgenc,D.Departamento AS DptoAgenc,TelAgncia,FaxAgncia,CodSicom,A.FecIngreso AS Fec_Ingreso
,A.TipoZona AS AgeTipoZona,CdTipAgenc,TipoAgencia,CdSubAgenc,SubTipoAgenc,CdGrupAgenc,GrupoAgenc,CdSubGrupAgenc,SubGrupoAgenc
,Comentarios,A.Inactivo AS AgeInactivo,CA.NContrato AS NumContrato,IdCia
 FROM Trn_MayAgencias AS CA INNER JOIN Agencias AS A ON CA.IdAgencia=A.IdAgencia
 INNER JOIN Terceros AS T ON A.IdClie=T.IdTercero
 INNER JOIN Localidades AS L ON A.IdLocal=L.IdLocal INNER JOIN Departamentos AS D ON L.IdDep=D.IdDep 
 LEFT JOIN TiposAgenc AS TAG ON A.CdTipAgenc=TAG.IdTipAgenc LEFT JOIN TiposComAgenc AS STA ON A.CdSubAgenc=STA.IdSubAgenc
 LEFT JOIN GruposAgenc AS GA ON A.CdGrupAgenc=GA.IdGrupo LEFT JOIN SubGruposAgenc AS SGA ON A.CdSubGrupAgenc=SGA.IdSubAgenc
 WHERE CA.NContrato BETWEEN @pmNContratoIni AND @pmNContratoFin AND CA.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 paQryMayAnotacionesLta @pmNContratoIni INT,@pmNContratoFin INT,@pmIdCia CHAR(2)
 AS 
 SELECT Item,Fecha,Detalle,A.IdUsuario AS CdUsuario,Usuario,NContrato,IdCia
 FROM Trn_MayAnotaciones AS A INNER JOIN adm_Usuarios AS U ON A.IdUsuario=U.IdUsuario
 WHERE NContrato BETWEEN @pmNContratoIni AND @pmNContratoFin AND 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 paQryMayBonifLta @pmNContratoIni INT,@pmNContratoFin INT,@pmIdCia CHAR(2)
 AS 
 SELECT Item,TipoProd,VrInicial,VrFinal,Unidad,Periodo,Tarifa,TipoTarifa
,CdSubgrupo,Subgrupo,NContrato,IdCia
 FROM Trn_MayBonif  AS  B  LEFT JOIN SubGrupos AS S ON B.CdSubgrupo=S.IdSubgrupo
 WHERE NContrato BETWEEN @pmNContratoIni AND @pmNContratoFin 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 paQryMayContDocLta @pmNContratoIni INT,@pmNContratoFin INT,@pmIdCia CHAR(2)
 AS 
 SELECT Item,Dependencia,D.IdDoc AS CdDoc,NombreDoc,FechaRad,Estado,Comentarios,FechaAprob
,CdUsuario,NContrato,IdCia
 FROM Trn_MayContDoc AS D INNER JOIN MayTiposDoc AS T ON D.IdDoc=T.IdDoc
 WHERE NContrato BETWEEN @pmNContratoIni AND @pmNContratoFin AND 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 paQryMayContratos @pmNContrato INT,@pmIdCia CHAR(2)
 AS 
 SELECT NContrato,IdCia,Fecha,IdCliente,IdAgencia,IdTipo,IdBonif,ConvBonif,CuotaFija,CondPago,DiasGracia,ModPlazo,Plazo
,FechaVence,ModRecursos,VrInversion,VrInventario,VrCuota,ModCuota,IdBandera,IdVend,AprobJunta,FechaAprob,ObservAprob
,Observacion,IdEstado,FechaInicio,CuotaMin,TipoAprob,NumInterno,InvCombus,CuotaBase,TimeSys,FecUpdate,IdCiaCrea,IdUsuario
,FechaFirma,CdAprobNeg,ArchivoAprob,ClaseContrato,DescTipoCont,GarantHipoteca,GarantPrenda,GarantPoliza,GarantBanco,GarantPagare
,GarantOtro,CdTipoBien,VrAvaludo,NroEscritura,CdTipoPoliza,DescPoliza,VigencPoliza,FecVencPoliza,ValorPoliza,EdoGarantias
,DescGarantOtro,InverContab,InverContrac,VolContMes,VolContTotal,VolViabMes,VolViabTotal,InvCtbObra,InvCtbCapital,InvCtbEfectivo,InvCtbImagen
,InvCtbMntmto,FecDesemb,InvContObra,InvContCapital,InvContEfectivo,InvContImagen,CrtlAprovInv,CrtlAprovImg,CdTipoBloq,EdoDocmtos,DescEdoDoc,RentabInicial,GraciaBonif
,ActFechaIni,CdZonaCont,ContVigente,VolContrac,DescHipotec,VrAvaComerc,FecRegEscritura,NotariaEsc,CdTipPrenda,VrAvaPrenda,PolizaPrenda,FecVigPrenda,VrAsegPrenda
 FROM Trn_MayContratos
 WHERE NContrato=@pmNContrato 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 paQryMayContratos_Cr  @pmNContratoIni INT=Null,@pmNContratoFin INT=Null,@pmIdCia CHAR(2)=Null
 AS 
 SELECT C.NContrato AS NumContrato,C.IdCia AS CdCia,Compania,NumInterno,Fecha,FechaInicio,ActFechaIni,FechaFirma,IdCliente,T.RazonSocial AS NomCliente,C.IdAgencia AS CdAgencia,Agencia
,ClaseContrato,C.IdTipo AS CdTipo,TipContrato,DescTipoCont,C.IdBonif AS CdBonif,TipoBonif,ConvBonif,CuotaFija,CondPago,C.DiasGracia AS Dias_Gracia
,ModPlazo,C.Plazo AS PlazoCont,FechaVence,VrCuota,ModCuota,CuotaMin,CuotaBase,GraciaBonif,C.IdBandera AS CdBandera,TipoBandera,CdZonaCont,TZN.TipoZona AS TipoZonaCont
,C.IdVend AS CdVend,V.RazonSocial AS Vendedor,AprobJunta,FechaAprob,ObservAprob,CdAprobNeg,TA.TipoAprob AS Tipo_Aprob,ArchivoAprob,C.Observacion AS Observ
,GarantHipoteca,GarantPrenda,GarantPoliza,GarantBanco,GarantPagare,GarantOtro,CdTipoBien,TipoBien,VrAvaludo,VrAvaComerc,NroEscritura,FecRegEscritura,NotariaEsc,DescHipotec
,CdTipoPoliza,TipoPoliza,DescPoliza,VigencPoliza,FecVencPoliza,ValorPoliza,CdTipPrenda,TipoPrenda,VrAvaPrenda,PolizaPrenda,FecVigPrenda,VrAsegPrenda,EdoGarantias,DescGarantOtro
,VolContrac,C.VolContMes AS VlmContMes,C.VolContTotal AS VlmContTotal,C.VolViabMes AS VlmViabMes,C.VolViabTotal AS VlmViabTotal,InverContab,InverContrac,C.InvCtbObra AS InvCntbObra,C.InvCtbCapital AS InvCntbCapital
,C.InvCtbEfectivo AS InvCntbEfe,C.InvCtbImagen AS InvCntbImagen,C.InvCtbMntmto AS InvCntbMnto,C.InvContObra AS InvConObra,C.InvContCapital AS InvConCapital,C.InvContEfectivo AS InvConEfe
,C.InvContImagen AS InvConImagen,C.FecDesemb AS FechaDesemb,C.CrtlAprovInv AS CntrolAprobInv,C.CrtlAprovImg AS CntrolAprobImagen,RentabInicial,ModRecursos,VrInversion,VrInventario,InvCombus
,ContVigente,C.IdEstado AS CdEstado,Estado,NColor,CdTipoBloq,TBO.TipoBloqueo AS ConTipoBloq,EdoDocmtos,C.DescEdoDoc AS ConDescEstdo,TimeSys,FecUpdate,IdCiaCrea,C.IdUsuario AS CdUsuario,Usuario
--datos de bonificaciones
,Item,TipoProd,VrInicial,VrFinal,Unidad,Tarifa,TipoTarifa,Periodo,CdProducto,DescripProd,CdSubgrupo,Subgrupo
--Información del tercero
,T.TipoId AS TercTipo,T.Dv AS TercDv,T.Codigo AS TercCodigo,T.NomCial AS TercNomCial,T.SiglaRaz AS TercSigal,T.Direccion AS TercDireccion
,T.IdLocal AS TercCdCiudad,L.Localidad AS NomCiudad,L.IdDep AS CdDep,D.Departamento AS TercDpto,T.Telefono AS TercTelefono,T.Fax AS TercFax,T.e_mail AS TercEmail
,T.SitioWeb AS TercSitioWeb,T.IdSector AS CdSector,SectorEco,T.IdRegimen AS CdRegimen,Regimen,T.TipEnte AS TercTipEnte
--información del cliente
,NitRepLeg,NomRepLeg,CLI.NitContac AS ClieNitContac,CLI.NomContac AS ClieNomContacto,CLI.TelContac AS ClieTelContac,CLI.emlContac AS ClieEmailContac,CargContac,DiasEntga,CLI.IdSzona AS CdSubzona,Subzona,SZ.IdZona AS CdZona,Zona
,CLI.IdGrupo AS CdGrupoCli,GrupoClie,CLI.IdClase AS CdClaseCta,NumCuenta,CLI.IdBanco AS CdBanco,Banco,ExcIva,LiqFletes,CLI.FactSold AS FactSoldicom,Autoret,VrCupo,VrSaldo
,CLI.FecUpCupo AS FechaActCupo,Cmntario1,Cmntario2,Cmntario3,CLI.IdPlazo AS CliCdPlazo,PZ.Plazo AS PlazoCli,CLI.CdPlazoComb AS CdPlazoComb,PZC.Plazo AS PlazoComb,CdTipBloq,TBC.TipoBloqueo AS CliTipoBloq
,CLI.DescEdoDoc AS CliDescEdoDoc,EdoRadicaDoc,FecPlazoDoc
--agencias
,A.DirAgncia AS AgeDireccion,A.TelAgncia AS AgeTelefono,A.FaxAgncia AS AgeFax,A.IdLocal AS AgeIdCiudad,LA.Localidad AS AgeCiudad,LA.IdDep AS AgeCodDep,DA.Departamento AS AgeDpto
,A.Referencia AS AgeReferencia,A.IdPlazo AS AgeCdPlazo,PZA.Plazo AS AgePlazo,A.CdPlazoComb AS AgeCdPlazoComb,PZAC.Plazo AS AgePlazoComb,VrCupoCre,A.FecUpCupo AS AgeFecActCupo,CodSicom
,A.FecIngreso AS AgeFecIngreso,Comentarios,A.TipoZona AS AgeTipoZona,CdTipAgenc,TipoAgencia,CdSubAgenc,SubTipoAgenc,CdGrupAgenc,GrupoAgenc
,CdSubGrupAgenc,SubGrupoAgenc,CdFntePago,FuentePago,PlazoSobtasa,DocFinanc,FecVigCond,TasaIntArranq
 FROM Trn_MayContratos AS C INNER JOIN MayTipos AS TC ON C.IdTipo=TC.IdTipo INNER JOIN MayTiposBonif AS B ON C.IdBonif=B.IdBonif
 INNER JOIN Companias AS CN ON C.IdCia=CN.IdCia  INNER JOIN  MayEstados AS E ON C.IdEstado=E.IdEstado
 INNER JOIN  TiposBan AS TB ON C.IdBandera=TB.IdBandera INNER JOIN adm_Usuarios AS U ON C.IdUsuario=U.IdUsuario
 INNER JOIN Terceros AS T ON C.IdCliente=T.IdTercero INNER JOIN TercCliente AS CLI ON C.IdCliente=CLI.IdClie
 INNER JOIN Terceros AS V ON C.IdVend=V.IdTercero 
 INNER JOIN Localidades AS L ON T.IdLocal=L.IdLocal  INNER JOIN Departamentos AS D ON L.IdDep=D.IdDep 
 INNER JOIN  RegimenDian AS RG ON T.IdRegimen=RG.IdRegimen INNER JOIN SectoresEco AS SE ON T.IdSector=SE.IdSector
 INNER JOIN Subzonas AS SZ ON CLI.IdSzona=SZ.IdSzona  INNER JOIN  Zonas AS Z ON SZ.IdZona=Z.IdZona
 INNER JOIN GruposCli AS GC ON CLI.IdGrupo=GC.IdGrupo  INNER JOIN Bancos AS BC ON CLI.IdBanco=BC.IdBanco
 INNER JOIN Plazos AS PZ ON CLI.IdPlazo=PZ.IdPlazo
 INNER JOIN Trn_MayBonif AS MB ON C.NContrato=MB.NContrato AND C.IdCia=MB.IdCia
 LEFT JOIN  TiposBloq AS TBO ON C.CdTipoBloq=TBO.IdTipBloq LEFT JOIN MayTiposAprob AS TA ON C.CdAprobNeg=TA.IdAprob
 LEFT JOIN MayTiposBienes AS TBN ON C.CdTipoBien=TBN.IdTipBien LEFT JOIN MayTiposPoliza AS TPZ ON C.CdTipoPoliza=TPZ.IdTipoPol
 LEFT JOIN TiposPrend AS TPD ON C.CdTipPrenda=TPD.IdPrend LEFT JOIN TiposZona AS TZN ON C.CdZonaCont=TZN.IdTipZona
 LEFT JOIN Plazos AS PZC ON CLI.CdPlazoComb=PZC.IdPlazo LEFT JOIN TiposBloq AS TBC ON CLI.CdTipBloq=TBC.IdTipBloq
 LEFT JOIN  Agencias   AS A ON C.IdAgencia=A.IdAgencia
 LEFT JOIN Localidades AS LA ON A.IdLocal=LA.IdLocal LEFT JOIN Departamentos AS DA ON LA.IdDep=DA.IdDep 
 LEFT JOIN Plazos AS PZA ON A.IdPlazo=PZA.IdPlazo LEFT JOIN Plazos AS PZAC ON A.CdPlazoComb=PZAC.IdPlazo
 LEFT JOIN TiposAgenc AS TAG ON A.CdTipAgenc=TAG.IdTipAgenc LEFT JOIN TiposComAgenc AS STA ON A.CdSubAgenc=STA.IdSubAgenc
 LEFT JOIN GruposAgenc AS GA ON A.CdGrupAgenc=GA.IdGrupo LEFT JOIN SubGruposAgenc AS SGA ON A.CdSubGrupAgenc=SGA.IdSubAgenc
 LEFT JOIN FuentesPago AS FP ON A.CdFntePago=FP.IdFuente 
 LEFT JOIN SubGrupos AS SG ON MB.CdSubgrupo=SG.IdSubgrupo  LEFT JOIN ProdMcias AS PM ON MB.CdProducto=PM.IdProducto
 WHERE   C.NContrato BETWEEN ISNULL(@pmNContratoIni,0) AND ISNULL(@pmNContratoFin,2147483647)
 AND  C.IdCia LIKE ISNULL(@pmIdCia,'%%')
 ORDER BY C.NContrato
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE paQryMayContratosLta @pmFechaIni SMALLDATETIME,@pmFechaFin SMALLDATETIME,@pmIdCia CHAR(2)=Null
,@pmIdCliente VARCHAR(16)=Null,@pmIdTipo VARCHAR(4)=Null
 AS 
 SELECT C.NContrato AS NumContrato,C.IdCia AS CdCia,Compania,Fecha,FechaInicio,IdCliente,T.RazonSocial AS NomCliente,C.IdAgencia AS CdAgencia,Agencia,ClaseContrato,C.IdTipo AS CdTipo,TipContrato
,DescTipoCont,C.IdBonif AS CdBonif,TipoBonif,ConvBonif,CuotaFija,CondPago,C.DiasGracia AS Dias_Gracia,ModPlazo,Plazo,FechaVence,ModRecursos,VrInversion,VrInventario,VrCuota,CuotaMin,CuotaBase,ModCuota
,C.IdBandera AS CdBandera,TipoBandera,C.IdVend AS CdVend,V.RazonSocial AS Vendedor,TipoAprob,AprobJunta,FechaAprob,ObservAprob,C.Observacion AS Observ,NumInterno,InvCombus
,FechaFirma,CdAprobNeg,ArchivoAprob,GarantHipoteca,GarantPrenda,GarantPoliza,GarantBanco,GarantPagare
,GarantOtro,CdTipoBien,VrAvaludo,NroEscritura,CdTipoPoliza,DescPoliza,VigencPoliza,FecVencPoliza,ValorPoliza,EdoGarantias
,DescGarantOtro,InverContab,InverContrac,C.VolContMes AS VlmenContMes,C.VolContTotal AS VlmenContTotal,C.VolViabMes AS VlmenViabMes,C.VolViabTotal AS VlmenViabTotal,C.InvCtbObra AS VrCtbObra,C.InvCtbCapital AS VrCtbCapital
,C.InvCtbEfectivo AS VrCtbEfectivo,C.InvCtbImagen AS VrCtbImagen,C.InvCtbMntmto AS VrCtbMnto,C.FecDesemb AS FechDesemb,C.InvContObra AS VrContObra,C.InvContCapital AS VrContCaptial,C.InvContEfectivo AS VrContEfectivo,C.InvContImagen AS VrContImagen
,C.CrtlAprovInv AS ConCrtlInv,C.CrtlAprovImg AS ConCrtlImag,CdTipoBloq,EdoDocmtos,DescEdoDoc,RentabInicial,GraciaBonif,ActFechaIni,CdZonaCont,ContVigente,VolContrac,DescHipotec,VrAvaComerc,FecRegEscritura
,NotariaEsc,CdTipPrenda,VrAvaPrenda,PolizaPrenda,FecVigPrenda,VrAsegPrenda,C.IdEstado AS CdEstado,Estado,NColor,TimeSys,FecUpdate,IdCiaCrea,C.IdUsuario AS CdUsuario,Usuario
 FROM Trn_MayContratos AS C INNER JOIN MayTipos AS TC ON C.IdTipo=TC.IdTipo
 INNER JOIN MayTiposBonif AS B ON C.IdBonif=B.IdBonif INNER JOIN Terceros AS T ON C.IdCliente=T.IdTercero
 INNER JOIN Companias AS CN ON C.IdCia=CN.IdCia  INNER JOIN  MayEstados AS E ON C.IdEstado=E.IdEstado
 INNER JOIN  TiposBan AS TB ON C.IdBandera=TB.IdBandera INNER JOIN adm_Usuarios AS U ON C.IdUsuario=U.IdUsuario
 INNER JOIN Terceros AS V ON C.IdVend=V.IdTercero  LEFT JOIN  Agencias   AS A ON C.IdAgencia=A.IdAgencia
 WHERE Fecha BETWEEN @pmFechaIni AND @pmFechaFin AND C.IdCia LIKE ISNULL(@pmIdCia,'%%')
 AND IdCliente LIKE ISNULL(@pmIdCliente,'%')  AND C.IdTipo LIKE ISNULL(@pmIdTipo,'%') 
 ORDER BY C.NContrato
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE paQryMayContratosRel  @pmFechaIni SMALLDATETIME,@pmFechaFin SMALLDATETIME,@pmIdCia CHAR(2)=Null
,@pmIdCliente VARCHAR(16)=Null,@pmIdTipo VARCHAR(4)=Null
 AS 
 SELECT C.NContrato AS NumContrato,C.IdCia AS CdCia,Compania,Fecha,FechaInicio,FechaFirma,IdCliente,T.RazonSocial AS NomCliente,C.IdAgencia AS CdAgencia,Agencia,ClaseContrato,C.IdTipo AS CdTipo,TipContrato
,DescTipoCont,C.IdBonif AS CdBonif,TipoBonif,ConvBonif,CuotaFija,CondPago,C.DiasGracia AS Dias_Gracia,ModPlazo,C.Plazo AS PlazoContrato,FechaVence,VrCuota,CuotaMin,CuotaBase,ModCuota
,C.IdBandera AS CdBandera,TipoBandera,C.IdVend AS CdVend,V.RazonSocial AS Vendedor,CdAprobNeg,TA.TipoAprob AS Tipo_Aprob,AprobJunta,FechaAprob,ObservAprob,ArchivoAprob,C.Observacion AS Observ,NumInterno
,GarantHipoteca,GarantPrenda,GarantPoliza,GarantBanco,GarantPagare,GarantOtro,CdTipoBien,TipoBien,VrAvaludo,NroEscritura,CdTipoPoliza,TipoPoliza,DescPoliza,VigencPoliza,FecVencPoliza,ValorPoliza,EdoGarantias
,DescGarantOtro,InverContab,InverContrac,C.VolContMes AS VlmenContMes,C.VolContTotal AS VlmenContTotal,C.VolViabMes AS VlmenViabMes,C.VolViabTotal AS VlmenViabTotal,C.InvCtbObra AS VrCtbObra,C.InvCtbCapital AS VrCtbCapital
,ModRecursos,VrInversion,VrInventario,InvCombus,C.InvCtbEfectivo AS VrCtbEfectivo,C.InvCtbImagen AS VrCtbImagen,C.InvCtbMntmto AS VrCtbMnto,C.FecDesemb AS FechDesemb,C.InvContObra AS VrContObra,C.InvContCapital AS VrContCaptial
,C.InvContEfectivo AS VrContEfectivo,C.InvContImagen AS VrContImagen,C.CrtlAprovInv AS ConCrtlInv,C.CrtlAprovImg AS ConCrtlImag,CdTipoBloq,TBO.TipoBloqueo AS ConTipoBloq,EdoDocmtos,C.DescEdoDoc AS ConDescEstdo,RentabInicial,GraciaBonif
,ActFechaIni,CdZonaCont,TZN.TipoZona AS TipoZonaCont,ContVigente,VolContrac,DescHipotec,VrAvaComerc,FecRegEscritura,NotariaEsc,CdTipPrenda,TipoPrenda,VrAvaPrenda,PolizaPrenda,FecVigPrenda,VrAsegPrenda
,C.IdEstado AS CdEstado,Estado,NColor,TimeSys,FecUpdate,IdCiaCrea,C.IdUsuario AS CdUsuario,Usuario,dbo.FuncMayAgencias(C.NContrato,C.IdCia) AS CantAgencias,dbo.FuncMayBonifTarif(C.NContrato,C.IdCia)  AS TarifBonif
,dbo.FuncMayNomAgencia(C.NContrato,C.IdCia) AS NombreEds
--datos cliente
,T.TipoId AS TercTipo,T.Dv AS TercDv,T.Codigo AS TercCodigo,T.NomCial AS TercNomCial,T.SiglaRaz AS TercSigal,T.Direccion AS TercDireccion
,T.IdLocal AS TercCdCiudad,L.Localidad AS NomCiudad,L.IdDep AS CdDep,D.Departamento AS TercDpto,T.Telefono AS TercTelefono,T.Fax AS TercFax,T.e_mail AS TercEmail
,T.SitioWeb AS TercSitioWeb,T.IdSector AS CdSector,SectorEco,T.IdRegimen AS CdRegimen,Regimen,T.TipEnte AS TercTipEnte
--información del cliente
,NitRepLeg,NomRepLeg,CLI.NitContac AS ClieNitContac,CLI.NomContac AS ClieNomContacto,CLI.TelContac AS ClieTelContac,CLI.emlContac AS ClieEmailContac,CargContac,DiasEntga,CLI.IdSzona AS CdSubzona,Subzona,SZ.IdZona AS CdZona,Zona
,CLI.IdGrupo AS CdGrupoCli,GrupoClie,CLI.IdClase AS CdClaseCta,NumCuenta,CLI.IdBanco AS CdBanco,Banco,ExcIva,LiqFletes,CLI.FactSold AS FactSoldicom,Autoret,VrCupo,VrSaldo,CLI.FecUpCupo AS FechaActCupo,Cmntario1,Cmntario2,Cmntario3
,CLI.IdPlazo AS CliCdPlazo,PZ.Plazo AS PlazoCli,CLI.CdPlazoComb AS CdPlazoComb,PZC.Plazo AS PlazoComb,CdTipBloq,TBC.TipoBloqueo AS CliTipoBloq ,CLI.DescEdoDoc AS CliDescEdoDoc,EdoRadicaDoc,FecPlazoDoc
--agencia
,A.DirAgncia AS AgeDireccion,A.TelAgncia AS AgeTelefono,A.FaxAgncia AS AgeFax,A.IdLocal AS AgeIdCiudad,LA.Localidad AS AgeCiudad,LA.IdDep AS AgeCodDep,DA.Departamento AS AgeDpto,A.Referencia AS AgeReferencia
,A.IdPlazo AS AgeCdPlazo,PZA.Plazo AS AgePlazo,A.CdPlazoComb AS AgeCdPlazoComb,PZAC.Plazo AS AgePlazoComb,VrCupoCre,A.FecUpCupo AS AgeFecActCupo,CodSicom,A.FecIngreso AS AgeFecIngreso,Comentarios
,A.TipoZona AS AgeTipoZona,CdTipAgenc,TipoAgencia,CdSubAgenc,SubTipoAgenc,CdGrupAgenc,GrupoAgenc,CdSubGrupAgenc,SubGrupoAgenc,CdFntePago,FuentePago,PlazoSobtasa,DocFinanc,FecVigCond,TasaIntArranq
 FROM Trn_MayContratos AS C INNER JOIN MayTipos AS TC ON C.IdTipo=TC.IdTipo
 INNER JOIN MayTiposBonif AS B ON C.IdBonif=B.IdBonif INNER JOIN Terceros AS T ON C.IdCliente=T.IdTercero
 INNER JOIN Localidades AS L ON T.IdLocal=L.IdLocal  INNER JOIN Departamentos AS D ON L.IdDep=D.IdDep
 INNER JOIN  RegimenDian AS RG ON T.IdRegimen=RG.IdRegimen INNER JOIN SectoresEco AS SE ON T.IdSector=SE.IdSector
 INNER JOIN TercCliente AS CLI ON C.IdCliente=CLI.IdClie  INNER JOIN Companias AS CN ON C.IdCia=CN.IdCia 
 INNER JOIN  MayEstados AS E ON C.IdEstado=E.IdEstado INNER JOIN  TiposBan AS TB ON C.IdBandera=TB.IdBandera
 INNER JOIN adm_Usuarios AS U ON C.IdUsuario=U.IdUsuario INNER JOIN Terceros AS V ON C.IdVend=V.IdTercero 
 INNER JOIN Subzonas AS SZ ON CLI.IdSzona=SZ.IdSzona  INNER JOIN  Zonas AS Z ON SZ.IdZona=Z.IdZona
 INNER JOIN GruposCli AS GC ON CLI.IdGrupo=GC.IdGrupo INNER JOIN Bancos AS BC ON CLI.IdBanco=BC.IdBanco
 INNER JOIN Plazos AS PZ ON CLI.IdPlazo=PZ.IdPlazo 
 LEFT JOIN  TiposBloq AS TBO ON C.CdTipoBloq=TBO.IdTipBloq LEFT JOIN MayTiposAprob AS TA ON C.CdAprobNeg=TA.IdAprob
 LEFT JOIN MayTiposBienes AS TBN ON C.CdTipoBien=TBN.IdTipBien LEFT JOIN MayTiposPoliza AS TPZ ON C.CdTipoPoliza=TPZ.IdTipoPol
 LEFT JOIN TiposPrend AS TPD ON C.CdTipPrenda=TPD.IdPrend LEFT JOIN TiposZona AS TZN ON C.CdZonaCont=TZN.IdTipZona
 LEFT JOIN Plazos AS PZC ON CLI.CdPlazoComb=PZC.IdPlazo LEFT JOIN TiposBloq AS TBC ON CLI.CdTipBloq=TBC.IdTipBloq
 LEFT JOIN  Agencias   AS A ON C.IdAgencia=A.IdAgencia
 LEFT JOIN Localidades AS LA ON A.IdLocal=LA.IdLocal LEFT JOIN Departamentos AS DA ON LA.IdDep=DA.IdDep 
 LEFT JOIN Plazos AS PZA ON A.IdPlazo=PZA.IdPlazo LEFT JOIN Plazos AS PZAC ON A.CdPlazoComb=PZAC.IdPlazo
 LEFT JOIN TiposAgenc AS TAG ON A.CdTipAgenc=TAG.IdTipAgenc LEFT JOIN TiposComAgenc AS STA ON A.CdSubAgenc=STA.IdSubAgenc
 LEFT JOIN GruposAgenc AS GA ON A.CdGrupAgenc=GA.IdGrupo LEFT JOIN SubGruposAgenc AS SGA ON A.CdSubGrupAgenc=SGA.IdSubAgenc
 LEFT JOIN FuentesPago AS FP ON A.CdFntePago=FP.IdFuente 
 WHERE Fecha BETWEEN @pmFechaIni AND @pmFechaFin AND C.IdCia LIKE ISNULL(@pmIdCia,'%%')
 AND IdCliente LIKE ISNULL(@pmIdCliente,'%')  AND C.IdTipo LIKE ISNULL(@pmIdTipo,'%') 
 ORDER BY C.NContrato
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE paQryMayInventarioLta @pmNContratoIni INT,@pmNContratoFin INT,@pmIdCia CHAR(2)
 AS 
 SELECT Item,I.IdArticulo AS CodArticulo,Detalle,Cantidad,UndMed,VrUnitario,VrTotal,TipoArticulo,Referencia
,I.IdTipo AS CdTipo,TipContrato,Estado,NumReq,IdCiaReq,CantReq,NumOrden,IdCiaOrd,CantOrden
,TipDoc,Documento,IdCiaDoc,CantEntrega,FecEntrega,NContrato,IdCia,CdProd,Descrpcion
 FROM Trn_MayInventario AS I INNER JOIN MayItemsInv AS R ON I.IdArticulo=R.IdArticulo
 INNER JOIN MayTipos AS T ON I.IdTipo=T.IdTipo
 WHERE NContrato BETWEEN @pmNContratoIni AND @pmNContratoFin 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 paQryMayPsptoLta @pmNContratoIni INT,@pmNContratoFin INT,@pmIdCia CHAR(2)
 AS 
 SELECT Item,TipoProd,Cantidad,Unidad,CantMes,CdSubgrupo,Subgrupo,NContrato,IdCia
 FROM Trn_MayPspto AS PS  LEFT JOIN SubGrupos AS S ON PS.CdSubgrupo=S.IdSubgrupo
 WHERE NContrato BETWEEN @pmNContratoIni AND @pmNContratoFin AND IdCia=@pmIdCia
 ORDER BY Item

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE PROCEDURE paQryMercancias @pmIdMercancia VARCHAR(16)
 AS 
 SELECT IdMercancia,DescripMcia,CodigoMcia,IdGrupo,UndMed,IdUnd,IdEmp,IdNat,IdMnjo,IdTmcia,EstadoMcia,Contenedor,IdProducto
,IdEstado,Inactivo,FechaAdd,FechaUpdate,IdUsuario
 FROM Mercancias
 WHERE IdMercancia=@pmIdMercancia
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE paQryMercanciasLta @pmIdGrupo VARCHAR(10)=Null,@pmIdNat VARCHAR(4)=Null,@pmIdTmcia VARCHAR(4)=Null
,@pmInactivo BIT=Null
 AS 
 SELECT IdMercancia,DescripMcia,M.IdGrupo AS CdGrupo,GrupoMcia,M.UndMed AS Und_Med,M.IdUnd AS CdUndPre,Unidad,UM.IdEmp AS CdEmp
,M.IdNat AS CdNat,Natlzaprod,M.IdMnjo AS CdMnjo,ManejoMcia,M.IdTmcia AS CdTmcia,TipoMcia,Contenedor,M.IdProducto AS CdProducto,DescripProd,CodigoMcia
,M.IdEmp AS CdEmp,Empaque,EstadoMcia,M.IdEstado AS CdEstado,Estado,M.Inactivo AS Inactvo,M.FechaAdd AS FechaCrea,M.FechaUpdate AS FechaAct,M.IdUsuario AS CdUsuario,Usuario
 FROM Mercancias AS M INNER JOIN GruposMcia AS G ON M.IdGrupo=G.IdGrupo
 INNER JOIN UndMed AS UM ON M.IdUnd=UM.IdUnd    INNER JOIN TiposNat AS N ON M.IdNat=N.IdNat
 INNER JOIN TiposMnjo AS MM ON M.IdMnjo=MM.IdMnjo INNER JOIN TiposMcia AS TM ON M.IdTmcia=TM.IdTmcia
 INNER JOIN EstadoPro AS EP ON M.IdEstado=EP.IdEstado  INNER JOIN adm_Usuarios AS U ON M.IdUsuario=U.IdUsuario 
 LEFT JOIN ProdMcias AS P ON M.IdProducto=P.IdProducto LEFT JOIN Empaques AS E ON M.IdEmp=E.IdEmp
 WHERE M.IdGrupo LIKE ISNULL(@pmIdGrupo,'%') AND M.IdNat LIKE ISNULL(@pmIdNat,'%') AND M.IdTmcia LIKE ISNULL(@pmIdTmcia,'%')
  AND (M.Inactivo=ISNULL(@pmInactivo,0) or  M.Inactivo=ISNULL(@pmInactivo,1))
 ORDER BY DescripMcia
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE PROCEDURE paQryTiposPrend @pmIdPrend VARCHAR(4)
 AS 
 IF @pmIdPrend IS NULL
  SELECT IdPrend,TipoPrenda
  FROM TiposPrend
  WHERE Inactivo=0
 ORDER BY TipoPrenda
 ELSE
  SELECT IdPrend,TipoPrenda,Inactivo
  FROM TiposPrend
  WHERE IdPrend=@pmIdPrend
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE PROCEDURE paQryTiposZona @pmIdTipZona VARCHAR(4)
 AS  
 IF @pmIdTipZona IS NULL
  SELECT IdTipZona,TipoZona
  FROM TiposZona
  WHERE Inactivo=0
  ORDER BY TipoZona
 ELSE
  SELECT IdTipZona,TipoZona,Inactivo
  FROM TiposZona
  WHERE IdTipZona=@pmIdTipZona

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE paUpMayContratos @pmNContrato INT,@pmIdCia CHAR(2),@pmFecha SMALLDATETIME,@pmIdCliente VARCHAR(16),@pmIdAgencia VARCHAR(16),@pmIdTipo VARCHAR(4),@pmIdBonif VARCHAR(4),@pmConvBonif BIT
,@pmCuotaFija BIT,@pmCondPago BIT,@pmDiasGracia INT,@pmModPlazo VARCHAR(10),@pmPlazo INT,@pmFechaVence SMALLDATETIME,@pmModRecursos VARCHAR(150),@pmVrInversion MONEY,@pmVrInventario MONEY,@pmVrCuota MONEY
,@pmModCuota VARCHAR(10),@pmIdBandera VARCHAR(4),@pmAprobJunta INT,@pmFechaAprob SMALLDATETIME,@pmObservAprob VARCHAR(250),@pmObservacion VARCHAR(250),@pmIdVend VARCHAR(16),@pmIdEstado VARCHAR(4),@pmFecUpdate SMALLDATETIME
,@pmFechaInicio SMALLDATETIME,@pmCuotaMin DECIMAL(14,4),@pmTipoAprob VARCHAR(10),@pmNumInterno VARCHAR(20),@pmInvCombus DECIMAL(14,4),@pmCuotaBase DECIMAL(14,4),@pmFechaFirma SMALLDATETIME,@pmCdAprobNeg VARCHAR(4),@pmArchivoAprob VARCHAR(50)
,@pmClaseContrato VARCHAR(10),@pmDescTipoCont VARCHAR(250),@pmGarantHipoteca BIT,@pmGarantPrenda BIT,@pmGarantPoliza BIT,@pmGarantBanco BIT,@pmGarantPagare BIT,@pmGarantOtro BIT,@pmCdTipoBien VARCHAR(4),@pmVrAvaludo MONEY,@pmNroEscritura VARCHAR(50)
,@pmCdTipoPoliza VARCHAR(4),@pmDescPoliza VARCHAR(150),@pmVigencPoliza INT,@pmFecVencPoliza SMALLDATETIME,@pmValorPoliza MONEY,@pmEdoGarantias INT,@pmDescGarantOtro VARCHAR(250),@pmInverContab INT,@pmInverContrac INT,@pmVolContMes DECIMAL(14,4)
,@pmVolContTotal DECIMAL(14,4),@pmVolViabMes DECIMAL(14,4),@pmVolViabTotal DECIMAL(14,4),@pmInvCtbObra MONEY,@pmInvCtbCapital MONEY,@pmInvCtbEfectivo MONEY,@pmInvCtbImagen MONEY,@pmInvCtbMntmto MONEY,@pmFecDesemb SMALLDATETIME,@pmInvContObra MONEY
,@pmInvContCapital MONEY,@pmInvContEfectivo MONEY,@pmInvContImagen MONEY,@pmCrtlAprovInv INT,@pmCrtlAprovImg INT,@pmCdTipoBloq VARCHAR(4),@pmEdoDocmtos INT,@pmDescEdoDoc VARCHAR(250),@pmRentabInicial DECIMAL(14,4),@pmGraciaBonif INT
,@pmActFechaIni INT,@pmCdZonaCont VARCHAR(4),@pmContVigente BIT,@pmVolContrac INT,@pmDescHipotec VARCHAR(250),@pmVrAvaComerc MONEY,@pmFecRegEscritura SMALLDATETIME,@pmNotariaEsc VARCHAR(50),@pmCdTipPrenda VARCHAR(4),@pmVrAvaPrenda MONEY
,@pmPolizaPrenda VARCHAR(30),@pmFecVigPrenda SMALLDATETIME,@pmVrAsegPrenda MONEY
 AS 
 UPDATE Trn_MayContratos SET Fecha=@pmFecha,IdCliente=@pmIdCliente,IdAgencia=@pmIdAgencia,IdTipo=@pmIdTipo,IdBonif=@pmIdBonif,ConvBonif=@pmConvBonif,CuotaFija=@pmCuotaFija,CondPago=@pmCondPago,DiasGracia=@pmDiasGracia
,ModPlazo=@pmModPlazo,Plazo=@pmPlazo,FechaVence=@pmFechaVence,ModRecursos=@pmModRecursos,VrInversion=@pmVrInversion,VrInventario=@pmVrInventario,VrCuota=@pmVrCuota,ModCuota=@pmModCuota,IdBandera=@pmIdBandera
,AprobJunta=@pmAprobJunta,FechaAprob=@pmFechaAprob,ObservAprob=@pmObservAprob,Observacion=@pmObservacion,IdEstado=@pmIdEstado,IdVend=@pmIdVend,FechaInicio=@pmFechaInicio,CuotaMin=@pmCuotaMin,TipoAprob=@pmTipoAprob
,NumInterno=@pmNumInterno,InvCombus=@pmInvCombus,CuotaBase=@pmCuotaBase,FecUpdate=@pmFecUpdate
,FechaFirma=@pmFechaFirma,CdAprobNeg=@pmCdAprobNeg,ArchivoAprob=@pmArchivoAprob,ClaseContrato=@pmClaseContrato,DescTipoCont=@pmDescTipoCont,GarantHipoteca=@pmGarantHipoteca,GarantPrenda=@pmGarantPrenda,GarantPoliza=@pmGarantPoliza,GarantBanco=@pmGarantBanco
,GarantPagare=@pmGarantPagare,GarantOtro=@pmGarantOtro,CdTipoBien=@pmCdTipoBien,VrAvaludo=@pmVrAvaludo,NroEscritura=@pmNroEscritura,CdTipoPoliza=@pmCdTipoPoliza,DescPoliza=@pmDescPoliza,VigencPoliza=@pmVigencPoliza,FecVencPoliza=@pmFecVencPoliza,ValorPoliza=@pmValorPoliza
,EdoGarantias=@pmEdoGarantias,DescGarantOtro=@pmDescGarantOtro,InverContab=@pmInverContab,InverContrac=@pmInverContrac,VolContMes=@pmVolContMes,VolContTotal=@pmVolContTotal,VolViabMes=@pmVolViabMes,VolViabTotal=@pmVolViabTotal,InvCtbObra=@pmInvCtbObra
,InvCtbCapital=@pmInvCtbCapital,InvCtbEfectivo=@pmInvCtbEfectivo,InvCtbImagen=@pmInvCtbImagen,InvCtbMntmto=@pmInvCtbMntmto,FecDesemb=@pmFecDesemb,InvContObra=@pmInvContObra,InvContCapital=@pmInvContCapital,InvContEfectivo=@pmInvContEfectivo,InvContImagen=@pmInvContImagen,CrtlAprovInv=@pmCrtlAprovInv
,CrtlAprovImg=@pmCrtlAprovImg,CdTipoBloq=@pmCdTipoBloq,EdoDocmtos=@pmEdoDocmtos,DescEdoDoc=@pmDescEdoDoc,RentabInicial=@pmRentabInicial,GraciaBonif=@pmGraciaBonif,ActFechaIni=@pmActFechaIni,CdZonaCont=@pmCdZonaCont,ContVigente=@pmContVigente,VolContrac=@pmVolContrac
,DescHipotec=@pmDescHipotec,VrAvaComerc=@pmVrAvaComerc,FecRegEscritura=@pmFecRegEscritura,NotariaEsc=@pmNotariaEsc,CdTipPrenda=@pmCdTipPrenda,VrAvaPrenda=@pmVrAvaPrenda,PolizaPrenda=@pmPolizaPrenda,FecVigPrenda=@pmFecVigPrenda,VrAsegPrenda=@pmVrAsegPrenda
 WHERE NContrato=@pmNContrato AND IdCia=@pmIdCia
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE PROCEDURE paUpMercancias @pmIdMercancia VARCHAR(16),@pmDescripMcia VARCHAR(250),@pmCodigoMcia VARCHAR(16),@pmIdGrupo VARCHAR(10),@pmUndMed VARCHAR(10),@pmIdUnd VARCHAR(4)
,@pmIdEmp VARCHAR(4),@pmIdNat VARCHAR(4),@pmIdMnjo VARCHAR(4),@pmIdTmcia VARCHAR(4),@pmEstadoMcia VARCHAR(20),@pmContenedor BIT,@pmIdProducto VARCHAR(16)
,@pmIdEstado VARCHAR(4),@pmInactivo BIT,@pmFechaUpdate SMALLDATETIME
 AS 
 UPDATE Mercancias SET DescripMcia=@pmDescripMcia,CodigoMcia=@pmCodigoMcia,IdGrupo=@pmIdGrupo,UndMed=@pmUndMed,IdUnd=@pmIdUnd
,IdNat=@pmIdNat,IdMnjo=@pmIdMnjo,IdTmcia=@pmIdTmcia,Contenedor=@pmContenedor,IdProducto=@pmIdProducto,IdEstado=@pmIdEstado,Inactivo=@pmInactivo
,IdEmp=@pmIdEmp,EstadoMcia=@pmEstadoMcia,FechaUpdate=@pmFechaUpdate
 WHERE IdMercancia=@pmIdMercancia
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE PROCEDURE paUpTiposPrend @pmIdPrend VARCHAR(4),@pmTipoPrenda VARCHAR(50),@pmInactivo BIT
 AS 
 UPDATE TiposPrend SET TipoPrenda=@pmTipoPrenda,Inactivo=@pmInactivo
 WHERE IdPrend=@pmIdPrend

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE PROCEDURE paUpTiposZona @pmIdTipZona VARCHAR(4),@pmTipoZona VARCHAR(50),@pmInactivo BIT
 AS 
 UPDATE TiposZona SET TipoZona=@pmTipoZona,Inactivo=@pmInactivo
 WHERE IdTipZona=@pmIdTipZona

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO