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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE paDelMayInvmov @pmTipDoc VARCHAR(3),@pmDocumento INT,@pmIdCia CHAR(2)
 AS
 DELETE FROM Trn_MayInvmov
 WHERE TipDoc=@pmTipDoc AND Documento=@pmDocumento 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 paInsAcuCuentasCc_Sel @pmFechaIni  SMALLDATETIME,@pmFechaFin SMALLDATETIME,@pmnMes INT
,@pmIdCia CHAR(2)=Null,@pmIdCuenta VARCHAR(16)=Null
 AS 
 BEGIN
  DECLARE @TrnNombre VARCHAR(20)
  SET @TrnNombre = 'AcuCuentasCc'
  BEGIN TRANSACTION @TrnNombre
  INSERT INTO AcuCuentasCc (nAnno,nMes,IdCia,IdCuenta,IdCCosto,IdSubCos,TipoAcum,SaldoAnterior,TotalDebitos,TotalCreditos)
  SELECT YEAR(Fecha),@pmnMes,IdCia,D.IdCuenta,IdCCosto,'-1','CC',0,SUM(VrDebito),SUM(VrCredito)
  FROM Trn_ComDetalle AS D INNER JOIN Puc AS P ON D.IdCuenta=P.IdCuenta
  WHERE Fecha BETWEEN @pmFechaIni AND @pmFechaFin AND Movimiento<>0 AND CentroCosto<>0 
  AND IdCia LIKE ISNULL(@pmIdCia,'%%') AND D.IdCuenta LIKE ISNULL(@pmIdCuenta,'%') 
  GROUP BY  YEAR(Fecha),IdCia,D.IdCuenta,IdCCosto
 --centro de costo-subcentro
  INSERT INTO AcuCuentasCc (nAnno,nMes,IdCia,IdCuenta,IdCCosto,IdSubCos,TipoAcum,SaldoAnterior,TotalDebitos,TotalCreditos)
  SELECT YEAR(Fecha),@pmnMes,IdCia,D.IdCuenta,IdCCosto,IdSubCos,'CS',0,SUM(VrDebito),SUM(VrCredito)
  FROM Trn_ComDetalle AS D INNER JOIN Puc AS P ON D.IdCuenta=P.IdCuenta
  WHERE Fecha BETWEEN @pmFechaIni AND @pmFechaFin AND Movimiento<>0 AND CentroCosto<>0 AND SubCentro<>0
  AND IdCia LIKE ISNULL(@pmIdCia,'%%') AND D.IdCuenta LIKE ISNULL(@pmIdCuenta,'%') 
  GROUP BY  YEAR(Fecha),IdCia,D.IdCuenta,IdCCosto,IdSubCos
 COMMIT TRANSACTION  @TrnNombre
END
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE paInsCompCaja @pmTipDoc VARCHAR(3),@pmRecibo INT,@pmIdCia CHAR(2),@pmFecha SMALLDATETIME,@pmIdConcepto VARCHAR(4),@pmVrEfectivo MONEY,@pmVrOtros MONEY,@pmVrTotal MONEY
,@pmVrTotDebito MONEY,@pmVrTotCredito MONEY,@pmEnEfectivo BIT,@pmCdCta VARCHAR(4),@pmIdTercero VARCHAR(16),@pmCdAgencia VARCHAR(16),@pmIdCCosto VARCHAR(16),@pmIdSubCos VARCHAR(16)
,@pmIdCajero VARCHAR(11),@pmNumForma VARCHAR(20),@pmCdBanco VARCHAR(4),@pmReferencia VARCHAR(50),@pmTipCom VARCHAR(3),@pmComprobante INT,@pmIdCiaCom CHAR(2)
,@pmAnulado BIT,@pmNumDev INT,@pmFecDev SMALLDATETIME,@pmObservacion VARCHAR(250),@pmIdEstado VARCHAR(4),@pmOrigenAdd VARCHAR(10),@pmTimeSys SMALLDATETIME,@pmIdCiaCrea CHAR(2),@pmIdUsuario VARCHAR(11)
,@pmNumVehiculo VARCHAR(10),@pmFacCalidad INT,@pmSemInicial INT,@pmSemFinal INT
 AS 
 INSERT INTO Trn_CompCaja (TipDoc,Recibo,IdCia,Fecha,IdConcepto,VrEfectivo,VrOtros,VrTotal,VrTotDebito,VrTotCredito,EnEfectivo,CdCta,IdTercero,CdAgencia,IdCCosto,IdSubCos,IdCajero,NumForma,CdBanco,Referencia
,NumVehiculo,FacCalidad,SemInicial,SemFinal,OrigenAdd,TipCom,Comprobante,IdCiaCom,Anulado,NumDev,FecDev,Observacion,IdEstado,TimeSys,IdCiaCrea,IdUsuario)
 VALUES (@pmTipDoc,@pmRecibo,@pmIdCia,@pmFecha,@pmIdConcepto,@pmVrEfectivo,@pmVrOtros,@pmVrTotal,@pmVrTotDebito,@pmVrTotCredito,@pmEnEfectivo,@pmCdCta,@pmIdTercero,@pmCdAgencia,@pmIdCCosto
,@pmIdSubCos,@pmIdCajero,@pmNumForma,@pmCdBanco,@pmReferencia,@pmNumVehiculo,@pmFacCalidad,@pmSemInicial,@pmSemFinal,@pmOrigenAdd,@pmTipCom,@pmComprobante,@pmIdCiaCom
,@pmAnulado,@pmNumDev,@pmFecDev,@pmObservacion,@pmIdEstado,@pmTimeSys,@pmIdCiaCrea,@pmIdUsuario)


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE paInsFacCalidad @pmIdVehiculo VARCHAR(10),@pmnSemana INT,@pmFecha SMALLDATETIME,@pmVrPagado MONEY
,@pmTipRec VARCHAR(3),@pmRecibo INT,@pmIdCiaRec CHAR(2),@pmObservacion VARCHAR(250),@pmIdUsuario VARCHAR(11)
 AS 
 INSERT INTO Trn_FacCalidad (IdVehiculo,nSemana,Fecha,VrPagado,TipRec,Recibo,IdCiaRec,Observacion,IdUsuario)
 VALUES (@pmIdVehiculo,@pmnSemana,@pmFecha,@pmVrPagado,@pmTipRec,@pmRecibo,@pmIdCiaRec,@pmObservacion,@pmIdUsuario)
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE paInsFondos @pmIdFondo VARCHAR(8),@pmFondo VARCHAR(100),@pmIdClase VARCHAR(4)
,@pmIdTercero VARCHAR(16),@pmCodigoEps VARCHAR(10),@pmCodigoPen VARCHAR(10),@pmCodigoArp VARCHAR(10),@pmInactivo BIT
 AS 
 INSERT INTO Fondos (IdFondo,Fondo,IdClase,IdTercero,CodigoEps,CodigoPen,CodigoArp,Inactivo)
 VALUES (@pmIdFondo,@pmFondo,@pmIdClase,@pmIdTercero,@pmCodigoEps,@pmCodigoPen,@pmCodigoArp,@pmInactivo)
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE paInsMayInventario @pmNContrato INT,@pmIdCia CHAR(2),@pmItem INT,@pmIdArticulo VARCHAR(16),@pmDetalle VARCHAR(150)
,@pmCantidad DECIMAL(14,4),@pmVrUnitario MONEY,@pmVrTotal MONEY,@pmTipoArticulo VARCHAR(10),@pmReferencia VARCHAR(50)
,@pmIdTipo VARCHAR(4),@pmTipDoc VARCHAR(3),@pmDocumento INT,@pmIdCiaDoc CHAR(2),@pmCantEntrega DECIMAL(14,4)
,@pmFecEntrega SMALLDATETIME,@pmEstado VARCHAR(10),@pmCdProd VARCHAR(16),@pmNumReq INT,@pmIdCiaReq CHAR(2),@pmCantReq DECIMAL(14,4)
,@pmNumOrden INT,@pmIdCiaOrd CHAR(2),@pmCantOrden DECIMAL(14,4)
 AS 
 INSERT INTO Trn_MayInventario (NContrato,IdCia,Item,IdArticulo,Detalle,Cantidad,VrUnitario,VrTotal,TipoArticulo,Referencia,IdTipo,TipDoc,Documento,IdCiaDoc
,CantEntrega,FecEntrega,Estado,CdProd,NumReq,IdCiaReq,CantReq,NumOrden,IdCiaOrd,CantOrden)
 VALUES (@pmNContrato,@pmIdCia,@pmItem,@pmIdArticulo,@pmDetalle,@pmCantidad,@pmVrUnitario,@pmVrTotal,@pmTipoArticulo,@pmReferencia
,@pmIdTipo,@pmTipDoc,@pmDocumento,@pmIdCiaDoc,@pmCantEntrega,@pmFecEntrega,@pmEstado
,@pmCdProd,@pmNumReq,@pmIdCiaReq,@pmCantReq,@pmNumOrden,@pmIdCiaOrd,@pmCantOrden)
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE paInsMayInvmov @pmTipDoc VARCHAR(3),@pmDocumento INT,@pmIdCia CHAR(2),@pmItem INT,@pmFechaDoc SMALLDATETIME,@pmIdArticulo VARCHAR(16)
,@pmCantidad DECIMAL(14,4),@pmNContrato INT,@pmIdCiaCon CHAR(2),@pmItemArt INT
 AS 
 INSERT INTO Trn_MayInvmov (TipDoc,Documento,IdCia,Item,FechaDoc,IdArticulo,Cantidad,NContrato,IdCiaCon,ItemArt)
 VALUES (@pmTipDoc,@pmDocumento,@pmIdCia,@pmItem,@pmFechaDoc,@pmIdArticulo,@pmCantidad,@pmNContrato,@pmIdCiaCon,@pmItemArt)

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE paInsMayLiqTotales @pmNLiquida INT,@pmIdCia CHAR(2),@pmItem INT,@pmNContrato INT,@pmIdCiaCon CHAR(2)
,@pmIdCliente VARCHAR(16),@pmIdAgencia VARCHAR(16),@pmUnidades DECIMAL(14,4),@pmVrBonif MONEY,@pmVrAbono MONEY
,@pmVrCuotaFija MONEY,@pmDiasGracia INT,@pmTipoNcr VARCHAR(3),@pmNotaCred INT,@pmIdCiaNot CHAR(2),@pmLogLiquida VARCHAR(250)
 AS 
 INSERT INTO Trn_MayLiqTotales (NLiquida,IdCia,Item,NContrato,IdCiaCon,IdCliente,IdAgencia,Unidades,VrBonif,VrAbono
,VrCuotaFija,DiasGracia,TipoNcr,NotaCred,IdCiaNot,LogLiquida)
 VALUES (@pmNLiquida,@pmIdCia,@pmItem,@pmNContrato,@pmIdCiaCon,@pmIdCliente,@pmIdAgencia,@pmUnidades
,@pmVrBonif,@pmVrAbono,@pmVrCuotaFija,@pmDiasGracia,@pmTipoNcr,@pmNotaCred,@pmIdCiaNot,@pmLogLiquida)
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE paInsMayTiposBonif @pmIdBonif VARCHAR(4),@pmTipoBonif VARCHAR(50)
,@pmAboCartera BIT,@pmAboPstmo BIT,@pmInactivo BIT
 AS 
 INSERT INTO MayTiposBonif (IdBonif,TipoBonif,AboCartera,AboPstmo,Inactivo)
 VALUES (@pmIdBonif,@pmTipoBonif,@pmAboCartera,@pmAboPstmo,@pmInactivo)
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE paInsOCompra @pmTipDoc VARCHAR(3),@pmOCompra INT,@pmIdCia CHAR(2),@pmFecha SMALLDATETIME,@pmFechaVence SMALLDATETIME,@pmIdConcepto VARCHAR(4),@pmIdProv VARCHAR(16),@pmVrSubTotal MONEY,@pmVrDescuento MONEY,@pmVrImpuesto MONEY,@pmVrFletes MONEY,@pmVrOtros MONEY,@pmVrCargos MONEY,@pmVrOtrDcto MONEY,@pmVrSobtasa MONEY,@pmVrImpGlobal MONEY
,@pmVrNeto MONEY,@pmCantidad DECIMAL(14,4),@pmIdEmpresa VARCHAR(16),@pmDirEntrega VARCHAR(250),@pmIdLocEtga VARCHAR(8),@pmDiasEntraga INT,@pmNitContac VARCHAR(16),@pmNomContac VARCHAR(150),@pmTelContac VARCHAR(20),@pmemlContac VARCHAR(100),@pmIdCCosto VARCHAR(16),@pmIdSubCos VARCHAR(16),@pmIdForma VARCHAR(4),@pmDetallePago VARCHAR(100),@pmMulPlazos BIT,@pmIdPlazo VARCHAR(4)
,@pmCdMney VARCHAR(5),@pmNitEmpTrans VARCHAR(16),@pmEmpTrans VARCHAR(150),@pmLiqFletes BIT,@pmpVehiculo VARCHAR(10),@pmCdConductor VARCHAR(16),@pmTipEnt VARCHAR(3),@pmEntrada INT,@pmIdCiaEnt CHAR(2),@pmNumAutoriza INT,@pmModalidad VARCHAR(10),@pmVigencia VARCHAR(10),@pmAnulado BIT,@pmFecDev SMALLDATETIME,@pmObservacion VARCHAR(250),@pmNumAprob INT,@pmFecAprob SMALLDATETIME
,@pmCdUsuAprob VARCHAR(11),@pmIdEstado VARCHAR(4),@pmVrRetencion MONEY,@pmVrReteICA MONEY,@pmTarifaRet DECIMAL(14,4),@pmTarifaIca DECIMAL(14,4),@pmCodTarRet VARCHAR(4),@pmCodTarIca VARCHAR(4)
,@pmOrigenAdd VARCHAR(10),@pmTimeSys SMALLDATETIME,@pmIdCiaCrea CHAR(2),@pmIdUsuario VARCHAR(11)
 AS 
 INSERT INTO Trn_OCompra (TipDoc,OCompra,IdCia,Fecha,FechaVence,IdConcepto,IdProv,VrSubTotal,VrDescuento,VrImpuesto,VrFletes,VrOtros,VrCargos,VrOtrDcto,VrSobtasa,VrImpGlobal,VrRetencion,VrReteICA,VrNeto,Cantidad,TarifaRet,TarifaIca,CodTarRet,CodTarIca,IdEmpresa,DirEntrega,IdLocEtga,DiasEntraga,NitContac,NomContac,TelContac,emlContac,IdCCosto,IdSubCos,IdForma,DetallePago,MulPlazos,IdPlazo,CdMney,NitEmpTrans,EmpTrans
,LiqFletes,pVehiculo,CdConductor,TipEnt,Entrada,IdCiaEnt,NumAutoriza,Modalidad,Vigencia,OrigenAdd,Anulado,FecDev,Observacion,NumAprob,FecAprob,CdUsuAprob,IdEstado,TimeSys,IdCiaCrea,IdUsuario)
 VALUES (@pmTipDoc,@pmOCompra,@pmIdCia,@pmFecha,@pmFechaVence,@pmIdConcepto,@pmIdProv,@pmVrSubTotal,@pmVrDescuento,@pmVrImpuesto,@pmVrFletes,@pmVrOtros,@pmVrCargos,@pmVrOtrDcto,@pmVrSobtasa,@pmVrImpGlobal,@pmVrRetencion,@pmVrReteICA,@pmVrNeto,@pmCantidad,@pmTarifaRet,@pmTarifaIca,@pmCodTarRet,@pmCodTarIca,@pmIdEmpresa,@pmDirEntrega,@pmIdLocEtga,@pmDiasEntraga,@pmNitContac,@pmNomContac
,@pmTelContac,@pmemlContac,@pmIdCCosto,@pmIdSubCos,@pmIdForma,@pmDetallePago,@pmMulPlazos,@pmIdPlazo,@pmCdMney,@pmNitEmpTrans,@pmEmpTrans,@pmLiqFletes,@pmpVehiculo,@pmCdConductor,@pmTipEnt,@pmEntrada,@pmIdCiaEnt,@pmNumAutoriza,@pmModalidad,@pmVigencia,@pmOrigenAdd,@pmAnulado,@pmFecDev,@pmObservacion,@pmNumAprob,@pmFecAprob,@pmCdUsuAprob,@pmIdEstado,@pmTimeSys,@pmIdCiaCrea,@pmIdUsuario)
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE paQryBodegasDso @pmTipoInv VARCHAR(10)=Null,@pmIdCia CHAR(2)=Null
 AS 
 IF @pmIdCia IS NULL
  SELECT IdBodega,Bodega,Bodega+' '+IdBodega AS DsBod
  FROM Bodegas  WHERE  Inactivo=0 AND TipoInv LIKE ISNULL(@pmTipoInv,'%')
  ORDER BY Bodega 
 ELSE
  SELECT B.IdBodega  AS IdBodega,Bodega,Bodega+' '+B.IdBodega AS DsBod
  FROM Bodegas AS B LEFT JOIN BodegasCias AS C ON B.IdBodega=C.IdBodega
   WHERE  B.Inactivo=0 AND B.TipoInv LIKE ISNULL(@pmTipoInv,'%')
  AND (C.IdCia=@pmIdCia OR C.IdCia IS NULL)
  ORDER BY Bodega 
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE paQryCompCaja @pmTipDoc VARCHAR(3),@pmRecibo INT,@pmIdCia CHAR(2)
 AS 
 SELECT TipDoc,Recibo,IdCia,Fecha,IdConcepto,VrEfectivo,VrOtros,VrTotal,VrTotDebito,VrTotCredito,EnEfectivo,CdCta,IdTercero,CdAgencia,IdCCosto,IdSubCos,IdCajero
,NumForma,CdBanco,Referencia,NumVehiculo,FacCalidad,SemInicial,SemFinal,OrigenAdd,TipCom,Comprobante,IdCiaCom,Anulado,NumDev,FecDev,Observacion,IdEstado,TimeSys,FecUpdate,IdCiaCrea,IdUsuario
 FROM Trn_CompCaja
 WHERE TipDoc=@pmTipDoc AND Recibo=@pmRecibo 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 paQryCompCajaBus @pmTipDoc VARCHAR(3),@pmFechaIni SMALLDATETIME, @pmFechaFin SMALLDATETIME
,@pmReciboIni INT=Null,@pmReciboFin INT=Null,@pmIdCia CHAR(2)=Null,@pmIdConcepto VARCHAR(4)=Null,@pmIdTercero VARCHAR(16)=Null
,@pmEnEfectivo BIT=Null,@pmIdCajero VARCHAR(11)=Null,@pmIdEstado VARCHAR(4)=Null,@pmAnulado BIT=Null,@pmIdUsuario VARCHAR(11)=Null
 AS 
 SELECT Recibo,C.IdCia AS CdCia,Compania,Fecha,C.IdConcepto AS CdConcepto,Concepto,VrEfectivo,VrOtros,VrTotal,VrTotDebito,VrTotCredito,EnEfectivo,NumVehiculo
,CdCta,NumeroCta,CTA.IdBanco AS CtaIdBanco,B.Banco AS CtaBanco,C.IdTercero AS NitTercero,RazonSocial,CdAgencia,C.IdCCosto AS CdCcosto,CCosto,C.IdSubCos AS CdSubCosto,IdCajero,UC.Usuario AS NomCajero
,NumForma,CdBanco,BC.Banco AS NomBanco,Referencia,FacCalidad,SemInicial,SemFinal,OrigenAdd,TipCom,Comprobante,IdCiaCom,Anulado,NumDev,FecDev,C.Observacion AS Observ
,C.IdEstado AS CdEstado,Estado,TimeSys,FecUpdate,IdCiaCrea,C.IdUsuario AS IdUsuari,U.Usuario AS NomUsuario,TipDoc
 FROM Trn_CompCaja AS C INNER JOIN Conceptos AS CN ON C.IdConcepto=CN.IdConcepto INNER JOIN Companias AS CI ON C.IdCia=CI.IdCia
 INNER JOIN Terceros AS T ON C.IdTercero=T.IdTercero  INNER JOIN adm_Usuarios AS UC ON C.IdCajero=UC.IdUsuario
 INNER JOIN adm_Usuarios AS U ON C.IdUsuario=U.IdUsuario  INNER JOIN EstadoDoc AS ED ON C.IdEstado=ED.IdEstado
 INNER JOIN CentroCosto AS CC ON C.IdCCosto=CC.IdCCosto
 LEFT JOIN CtasCorrientes AS CTA ON C.CdCta=CTA.IdCta LEFT JOIN Bancos AS B ON CTA.IdBanco=B.IdBanco
 LEFT JOIN Bancos AS BC ON C.CdBanco=BC.IdBanco
 WHERE TipDoc=@pmTipDoc AND Fecha BETWEEN @pmFechaIni AND @pmFechaFin AND Recibo BETWEEN ISNULL(@pmReciboIni,0) AND ISNULL(@pmReciboFin,2147483647)
 AND C.IdCia LIKE ISNULL(@pmIdCia,'%%') AND C.IdTercero LIKE ISNULL(@pmIdTercero,'%') AND C.IdConcepto LIKE ISNULL(@pmIdConcepto,'%')
 AND C.IdEstado LIKE ISNULL(@pmIdEstado,'%') AND IdCajero LIKE ISNULL(@pmIdCajero,'%') AND C.IdUsuario LIKE ISNULL(@pmIdUsuario,'%')
 AND (Anulado=ISNULL(@pmAnulado,0) or Anulado=ISNULL(@pmAnulado,1))
 AND (EnEfectivo=ISNULL(@pmEnEfectivo,0) or EnEfectivo=ISNULL(@pmEnEfectivo,1))
 ORDER BY C.IdCia,Recibo
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE paQryCompCaja_Cr @pmTipDoc VARCHAR(3),@pmReciboIni INT,@pmReciboFin INT,@pmIdCia CHAR(2)
 AS 
 SELECT R.TipDoc AS Tip_Doc,TipoDoc,R.Recibo AS NumRecibo,R.IdCia AS CdCia,Compania,R.Fecha AS FechaDoc,R.IdConcepto AS CdConcepto,CN.Concepto AS ConceptoDoc
,VrEfectivo,VrOtros,VrTotal,VrTotDebito,VrTotCredito,EnEfectivo,CdCta,NumeroCta,CTA.IdBanco AS CtaIdBanco,BCT.Banco AS CtaBanco
,R.IdTercero AS Nit_Tercero,T.RazonSocial AS NomTercero,CdAgencia,A.Agencia AS NomAgencia,A.CodAgencia AS Cod_Agencia,R.IdCCosto AS IdCenCost,CC.CCosto AS CentCosto,R.IdSubCos AS IdSubCent,SC.SubCosto AS SubcCosto
,IdCajero,UC.Usuario AS NomCajero,NumForma,CdBanco,BR.Banco AS DocBanco,R.Referencia AS DocReferencia,FacCalidad,SemInicial,SemFinal,OrigenAdd,TipCom,TipoCom,Comprobante,IdCiaCom
,Anulado,NumDev,FecDev,R.Observacion AS Observ,R.IdEstado AS CdEstado,Estado,TimeSys,R.FecUpdate AS Fec_Update,IdCiaCrea,R.IdUsuario AS IdUsuari,U.Usuario AS NombreUsuario,Leyenda,NumVehiculo
--detalle de cuentas
,Item,D.IdConcepto AS DetCodConcepto,CD.Concepto AS DetConcepto,D.IdCuenta AS CdCuenta,NomCuenta,Detalle,VrDebito,VrCredito,NitTercero,DT.RazonSocial AS DetRazonSocial
,pVehiculo,CdCCosto,DC.CCosto AS DetCentCosto,CdSubCos,DS.SubCosto AS DetSubCentCos,VrBase,TarifaBase,TipFac,Factura,IdCiaFac,ItemFac,FecVence,NumCheque,TipoAplica
,NitOtros,D.CodAgencia AS DetCodAgencia,DA.Agencia AS DetAgencia,Referncia,TipDocRef,DocRef,IdCiaRef
--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,DP.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
--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,DAG.Departamento AS AgeDpto,A.Referencia AS AgeReferencia
 FROM Trn_CompCaja AS R INNER JOIN Trn_CompCajaDet AS D ON R.TipDoc=D.TipDoc AND R.Recibo=D.Recibo AND R.IdCia=D.IdCia
 INNER JOIN Companias AS CI ON R.IdCia=CI.IdCia  INNER JOIN Sys_TiposDoc AS TD ON R.TipDoc=TD.IdDoc
 INNER JOIN EstadoDoc AS ED ON R.IdEstado=ED.IdEstado  INNER JOIN adm_Usuarios AS U ON R.IdUsuario=U.IdUsuario 
 INNER JOIN adm_Usuarios AS UC ON R.IdCajero=UC.IdUsuario  INNER JOIN CentroCosto AS CC ON R.IdCCosto=CC.IdCCosto 
 INNER JOIN  Conceptos AS CN ON R.IdConcepto=CN.IdConcepto  INNER JOIN Terceros AS T ON R.IdTercero =T.IdTercero
 INNER JOIN ConcDiversos AS CD ON D.IdConcepto=CD.IdConcepto  INNER JOIN Puc AS P ON D.IdCuenta=P.IdCuenta
 INNER JOIN Localidades AS L ON T.IdLocal=L.IdLocal  INNER JOIN Departamentos AS DP ON L.IdDep=DP.IdDep 
 INNER JOIN  RegimenDian AS RG ON T.IdRegimen=RG.IdRegimen INNER JOIN SectoresEco AS SE ON T.IdSector=SE.IdSector
 LEFT JOIN  CtasCorrientes AS CTA ON R.CdCta=CTA.IdCta LEFT JOIN Bancos AS BCT ON CTA.IdBanco=BCT.IdBanco
 LEFT JOIN Agencias  AS A ON R.CdAgencia=A.IdAgencia LEFT JOIN SubCentros AS SC ON R.IdSubCos=SC.IdSubCos
 LEFT JOIN Bancos AS BR ON R.CdBanco=BR.IdBanco   LEFT JOIN TiposCom AS TCM ON R.TipCom=TCM.IdCom
 LEFT JOIN Terceros AS DT ON D.NitTercero=DT.IdTercero  LEFT JOIN CentroCosto AS DC ON D.CdCCosto=DC.IdCCosto 
 LEFT JOIN SubCentros AS DS ON D.CdSubCos=DS.IdSubCos LEFT JOIN Agencias AS DA ON D.CodAgencia=DA.IdAgencia
 LEFT JOIN Localidades AS LA ON A.IdLocal=LA.IdLocal  LEFT JOIN Departamentos AS DAG ON LA.IdDep=DAG.IdDep
 WHERE R.TipDoc=@pmTipDoc AND R.Recibo BETWEEN @pmReciboIni AND @pmReciboFin AND R.IdCia=@pmIdCia
 ORDER BY R.Recibo,Item
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE paQryCompCajaBusDet @pmTipDoc VARCHAR(3),@pmFechaIni SMALLDATETIME,@pmFechaFin SMALLDATETIME
,@pmReciboIni INT=Null,@pmReciboFin INT=Null,@pmIdCia CHAR(2)=Null,@pmIdConcepto VARCHAR(4)=Null,@pmIdTercero VARCHAR(16)=Null
,@pmEnEfectivo BIT=Null,@pmIdCajero VARCHAR(11)=Null,@pmIdEstado VARCHAR(4)=Null,@pmAnulado BIT=Null,@pmIdUsuario VARCHAR(11)=Null
,@pmCdCta VARCHAR(4)=Null,@pmIdCCosto VARCHAR(16)=Null
 AS 
 SELECT R.TipDoc AS Tip_Doc,R.Recibo AS NumRecibo,R.IdCia AS CdCia,Compania,R.Fecha AS FechaDoc,R.IdConcepto AS CdConcepto,CN.Concepto AS ConceptoDoc
,VrEfectivo,VrOtros,VrTotal,VrTotDebito,VrTotCredito,EnEfectivo,CdCta,NumeroCta,CTA.IdBanco AS CtaIdBanco,BCT.Banco AS CtaBanco,NumVehiculo
,R.IdTercero AS Nit_Tercero,T.RazonSocial AS NomTercero,CdAgencia,A.Agencia AS NomAgencia,A.CodAgencia AS Cod_Agencia,R.IdCCosto AS IdCenCost,CC.CCosto AS CentCosto,R.IdSubCos AS IdSubCent,SC.SubCosto AS SubcCosto
,IdCajero,UC.Usuario AS NomCajero,NumForma,CdBanco,BR.Banco AS DocBanco,R.Referencia AS DocReferencia,OrigenAdd,TipCom,Comprobante,IdCiaCom,FacCalidad,SemInicial,SemFinal
,Anulado,NumDev,FecDev,R.Observacion AS Observ,R.IdEstado AS CdEstado,Estado,TimeSys,R.FecUpdate AS Fec_Update,IdCiaCrea,R.IdUsuario AS IdUsuari,U.Usuario AS NombreUsuario
--detalle de cuentas
,Item,D.IdConcepto AS DetCodConcepto,CD.Concepto AS DetConcepto,D.IdCuenta AS CdCuenta,NomCuenta,Detalle,VrDebito,VrCredito,NitTercero,DT.RazonSocial AS DetRazonSocial
,pVehiculo,CdCCosto,DC.CCosto AS DetCentCosto,CdSubCos,DS.SubCosto AS DetSubCentCos,VrBase,TarifaBase,TipFac,Factura,IdCiaFac,ItemFac,FecVence,NumCheque,TipoAplica
,NitOtros,D.CodAgencia AS DetCodAgencia,DA.Agencia AS DetAgencia,Referncia,TipDocRef,DocRef,IdCiaRef
--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,DP.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
 FROM Trn_CompCaja AS R INNER JOIN Trn_CompCajaDet AS D ON R.TipDoc=D.TipDoc AND R.Recibo=D.Recibo AND R.IdCia=D.IdCia
 INNER JOIN Companias AS CI ON R.IdCia=CI.IdCia  INNER JOIN EstadoDoc AS ED ON R.IdEstado=ED.IdEstado
 INNER JOIN adm_Usuarios AS U ON R.IdUsuario=U.IdUsuario 
 INNER JOIN adm_Usuarios AS UC ON R.IdCajero=UC.IdUsuario  INNER JOIN CentroCosto AS CC ON R.IdCCosto=CC.IdCCosto 
 INNER JOIN  Conceptos AS CN ON R.IdConcepto=CN.IdConcepto  INNER JOIN Terceros AS T ON R.IdTercero =T.IdTercero
 INNER JOIN ConcDiversos AS CD ON D.IdConcepto=CD.IdConcepto  INNER JOIN Puc AS P ON D.IdCuenta=P.IdCuenta
 INNER JOIN Localidades AS L ON T.IdLocal=L.IdLocal  INNER JOIN Departamentos AS DP ON L.IdDep=DP.IdDep 
 INNER JOIN  RegimenDian AS RG ON T.IdRegimen=RG.IdRegimen INNER JOIN SectoresEco AS SE ON T.IdSector=SE.IdSector
 LEFT JOIN  CtasCorrientes AS CTA ON R.CdCta=CTA.IdCta LEFT JOIN Bancos AS BCT ON CTA.IdBanco=BCT.IdBanco
 LEFT JOIN Agencias  AS A ON R.CdAgencia=A.IdAgencia LEFT JOIN SubCentros AS SC ON R.IdSubCos=SC.IdSubCos
 LEFT JOIN Bancos AS BR ON R.CdBanco=BR.IdBanco  
 LEFT JOIN Terceros AS DT ON D.NitTercero=DT.IdTercero  LEFT JOIN CentroCosto AS DC ON D.CdCCosto=DC.IdCCosto 
 LEFT JOIN SubCentros AS DS ON D.CdSubCos=DS.IdSubCos LEFT JOIN Agencias AS DA ON D.CodAgencia=DA.IdAgencia
 WHERE R.TipDoc=@pmTipDoc AND R.Fecha BETWEEN @pmFechaIni AND @pmFechaFin
 AND R.Recibo BETWEEN ISNULL(@pmReciboIni,0) AND ISNULL(@pmReciboFin,2147483647)
 AND R.IdCia LIKE ISNULL(@pmIdCia,'%%') AND R.IdConcepto LIKE ISNULL(@pmIdConcepto,'%')
 AND CdCta LIKE ISNULL(@pmCdCta,'%')  AND R.IdTercero LIKE ISNULL(@pmIdTercero,'%')
 AND R.IdCCosto LIKE ISNULL(@pmIdCCosto,'%')  AND R.IdEstado LIKE ISNULL(@pmIdEstado,'%')
 AND IdCajero LIKE ISNULL(@pmIdCajero,'%') AND R.IdUsuario LIKE ISNULL(@pmIdUsuario,'%')
 AND (EnEfectivo=ISNULL(@pmEnEfectivo,0) or EnEfectivo=ISNULL(@pmEnEfectivo,1))
 AND (Anulado=ISNULL(@pmAnulado,0) or Anulado=ISNULL(@pmAnulado,1))
 ORDER BY R.IdCia,R.Recibo
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE paQryCompCajaBusPag @pmTipDoc VARCHAR(3),@pmFechaIni SMALLDATETIME, @pmFechaFin SMALLDATETIME
,@pmReciboIni INT=Null,@pmReciboFin INT=Null,@pmIdCia CHAR(2)=Null,@pmIdConcepto VARCHAR(4)=Null,@pmIdTercero VARCHAR(16)=Null
,@pmEnEfectivo BIT=Null,@pmIdCajero VARCHAR(11)=Null,@pmIdEstado VARCHAR(4)=Null,@pmAnulado BIT=Null,@pmIdUsuario VARCHAR(11)=Null
,@pmIdForma VARCHAR(4)=Null
 AS 
 SELECT Recibo,C.IdCia AS CdCia,Compania,C.Fecha AS FechaRec,C.IdConcepto AS CdConcepto,Concepto,VrEfectivo,VrOtros,VrTotal,VrTotDebito,VrTotCredito,EnEfectivo
,C.IdTercero AS NitTercero,T.RazonSocial AS NomTercero,C.IdCCosto AS CdCcosto,CCosto,C.IdSubCos AS CdSubCosto,IdCajero,UC.Usuario AS NomCajero,NumVehiculo
,Referencia,OrigenAdd,TipCom,Comprobante,IdCiaCom,Anulado,NumDev,FecDev,C.Observacion AS Observ,FacCalidad,SemInicial,SemFinal
,C.IdEstado AS CdEstado,Estado,TimeSys,FecUpdate,IdCiaCrea,C.IdUsuario AS IdUsuari,U.Usuario AS NomUsuario
--detalles pagos
,Item,P.IdForma AS CdForma,FormaPago,Detalle,VrPagado,VrCambio,EsCaja,P.IdBanco AS CodBanco,B.Banco AS NomBanco,NitCliente,TP.RazonSocial AS PagNomTercero
,P.NumForma AS NumCheque,FecForma,CtaForma,Beneficiario,Referncia1,Referncia2,CdLocal,LF.Localidad AS CiudadPlaza,NumAutoriza,VrDenom,Cantidad,TipDenom,TipRef,DocRef,IdCiaRef
,P.CdCta AS CtaCod,NumeroCta,CTA.IdBanco AS CtaIdBanco,BC.Banco AS CtaBanco
--Información del tercero
,T.TipoId AS TercTipoId,T.Dv AS TercDv,T.Codigo AS TercCodigo,T.NomCial AS TercNomCial,T.Direccion AS TercDireccion,T.IdLocal AS TercCdCiudad,L.Localidad AS NomCiudad,L.IdDep AS CdDep,Departamento,T.Telefono AS TercTelefono
 FROM Trn_CompCaja AS C INNER JOIN Conceptos AS CN ON C.IdConcepto=CN.IdConcepto INNER JOIN Companias AS CI ON C.IdCia=CI.IdCia
 INNER JOIN Terceros AS T ON C.IdTercero=T.IdTercero  INNER JOIN adm_Usuarios AS UC ON C.IdCajero=UC.IdUsuario
 INNER JOIN adm_Usuarios AS U ON C.IdUsuario=U.IdUsuario  INNER JOIN EstadoDoc AS ED ON C.IdEstado=ED.IdEstado
 INNER JOIN CentroCosto AS CC ON C.IdCCosto=CC.IdCCosto 
 INNER JOIN Trn_Pagos AS P ON C.TipDoc=P.TipDoc AND  C.Recibo=P.Documento AND  C.IdCia=P.IdCia
 INNER JOIN Bancos AS B ON P.IdBanco=B.IdBanco   INNER JOIN Formaspago AS FP ON P.IdForma=FP.IdForma
 LEFT JOIN CtasCorrientes AS CTA ON P.CdCta=CTA.IdCta LEFT JOIN Bancos AS BC ON CTA.IdBanco=BC.IdBanco
 LEFT JOIN Localidades AS LF ON P.CdLocal=LF.IdLocal  LEFT JOIN Terceros AS TP ON P.NitCliente=TP.IdTercero
 LEFT JOIN Localidades AS L ON T.IdLocal=L.IdLocal  LEFT JOIN Departamentos AS D ON L.IdDep=D.IdDep 
  WHERE C.TipDoc=@pmTipDoc AND C.Fecha BETWEEN @pmFechaIni AND @pmFechaFin AND C.Recibo BETWEEN ISNULL(@pmReciboIni,0) AND ISNULL(@pmReciboFin,2147483647)
 AND C.IdCia LIKE ISNULL(@pmIdCia,'%%') AND C.IdTercero LIKE ISNULL(@pmIdTercero,'%') AND C.IdConcepto LIKE ISNULL(@pmIdConcepto,'%')
 AND C.IdEstado LIKE ISNULL(@pmIdEstado,'%') AND IdCajero LIKE ISNULL(@pmIdCajero,'%') AND C.IdUsuario LIKE ISNULL(@pmIdUsuario,'%')
 AND (Anulado=ISNULL(@pmAnulado,0) or Anulado=ISNULL(@pmAnulado,1))
 AND (EnEfectivo=ISNULL(@pmEnEfectivo,0) or EnEfectivo=ISNULL(@pmEnEfectivo,1))
 AND P.IdForma LIKE ISNULL(@pmIdForma,'%')
 ORDER BY C.IdCia,Recibo
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE paQryCompCajaLta @pmTipDoc VARCHAR(3),@pmFechaIni SMALLDATETIME, @pmFechaFin SMALLDATETIME
,@pmReciboIni INT=Null,@pmReciboFin INT=Null,@pmIdCia CHAR(2)=Null,@pmIdConcepto VARCHAR(4)=Null,@pmIdTercero VARCHAR(16)=Null
,@pmEnEfectivo BIT=Null,@pmIdCajero VARCHAR(11)=Null,@pmIdEstado VARCHAR(4)=Null,@pmAnulado BIT=Null
 AS 
 SELECT Recibo,IdCia,Fecha,C.IdConcepto AS CdConcepto,Concepto,VrEfectivo,VrOtros,VrTotal,VrTotDebito,VrTotCredito,EnEfectivo,CdCta,C.IdTercero AS NitTercero,RazonSocial,CdAgencia,IdCCosto,IdSubCos
,IdCajero,UC.Usuario AS NomCajero,NumForma,CdBanco,Referencia,FacCalidad,SemInicial,SemFinal,OrigenAdd,TipCom,Comprobante,IdCiaCom
,Anulado,NumDev,FecDev,C.Observacion AS Observ,C.IdEstado AS CdEstado,Estado,TimeSys,FecUpdate,IdCiaCrea,C.IdUsuario AS IdUsuari,U.Usuario AS NomUsuario,TipDoc,NumVehiculo
 FROM Trn_CompCaja AS C INNER JOIN Conceptos AS CN ON C.IdConcepto=CN.IdConcepto
 INNER JOIN Terceros AS T ON C.IdTercero=T.IdTercero
 INNER JOIN adm_Usuarios AS UC ON C.IdCajero=UC.IdUsuario
 INNER JOIN adm_Usuarios AS U ON C.IdUsuario=U.IdUsuario
 INNER JOIN EstadoDoc AS ED ON C.IdEstado=ED.IdEstado
 WHERE TipDoc=@pmTipDoc AND Fecha BETWEEN @pmFechaIni AND @pmFechaFin AND Recibo BETWEEN ISNULL(@pmReciboIni,0) AND ISNULL(@pmReciboFin,2147483647)
 AND IdCia LIKE ISNULL(@pmIdCia,'%%') AND C.IdTercero LIKE ISNULL(@pmIdTercero,'%') AND C.IdConcepto LIKE ISNULL(@pmIdConcepto,'%')
 AND C.IdEstado LIKE ISNULL(@pmIdEstado,'%') AND IdCajero LIKE ISNULL(@pmIdCajero,'%')
 AND (Anulado=ISNULL(@pmAnulado,0) or Anulado=ISNULL(@pmAnulado,1))
 AND (EnEfectivo=ISNULL(@pmEnEfectivo,0) or EnEfectivo=ISNULL(@pmEnEfectivo,1))
 ORDER BY IdCia,Recibo
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE paQryCompCajaRel @pmTipDoc VARCHAR(3),@pmFechaIni SMALLDATETIME,@pmFechaFin SMALLDATETIME,@pmIdCia CHAR(2)=Null
,@pmIdConcepto VARCHAR(4)=Null,@pmCdCta VARCHAR(4)=Null,@pmEnEfectivo BIT=Null,@pmIdTercero VARCHAR(16)=Null,@pmCdAgencia VARCHAR(16)=Null
,@pmIdCCosto VARCHAR(16)=Null,@pmIdCajero VARCHAR(11)=Null,@pmAnulado BIT=Null
 AS 
 SELECT R.TipDoc AS Tip_Doc,R.Recibo AS NumRecibo,R.IdCia AS CdCia,Compania,R.Fecha AS FechaDoc,R.IdConcepto AS CdConcepto,CN.Concepto AS ConceptoDoc
,VrEfectivo,VrOtros,VrTotal,VrTotDebito,VrTotCredito,EnEfectivo,CdCta,NumeroCta,CTA.IdBanco AS CtaIdBanco,BCT.Banco AS CtaBanco
,R.IdTercero AS Nit_Tercero,T.RazonSocial AS NomTercero,CdAgencia,A.Agencia AS NomAgencia,A.CodAgencia AS Cod_Agencia,R.IdCCosto AS IdCenCost,CC.CCosto AS CentCosto,R.IdSubCos AS IdSubCent,SC.SubCosto AS SubcCosto
,IdCajero,UC.Usuario AS NomCajero,NumForma,CdBanco,BR.Banco AS DocBanco,R.Referencia AS DocReferencia,FacCalidad,SemInicial,SemFinal,OrigenAdd,TipCom,Comprobante,IdCiaCom
,Anulado,NumDev,FecDev,R.Observacion AS Observ,R.IdEstado AS CdEstado,Estado,TimeSys,R.FecUpdate AS Fec_Update,IdCiaCrea,R.IdUsuario AS IdUsuari,U.Usuario AS NombreUsuario
--detalle de cuentas
,Item,D.IdConcepto AS DetCodConcepto,CD.Concepto AS DetConcepto,D.IdCuenta AS CdCuenta,NomCuenta,Detalle,VrDebito,VrCredito,NitTercero,DT.RazonSocial AS DetRazonSocial
,pVehiculo,CdCCosto,DC.CCosto AS DetCentCosto,CdSubCos,DS.SubCosto AS DetSubCentCos,VrBase,TarifaBase,TipFac,Factura,IdCiaFac,ItemFac,FecVence,NumCheque,TipoAplica
,NitOtros,D.CodAgencia AS DetCodAgencia,DA.Agencia AS DetAgencia,Referncia,TipDocRef,DocRef,IdCiaRef
--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,DP.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
--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,DAG.Departamento AS AgeDpto,A.Referencia AS AgeReferencia,NumVehiculo
 FROM Trn_CompCaja AS R INNER JOIN Trn_CompCajaDet AS D ON R.TipDoc=D.TipDoc AND R.Recibo=D.Recibo AND R.IdCia=D.IdCia
 INNER JOIN Companias AS CI ON R.IdCia=CI.IdCia  INNER JOIN EstadoDoc AS ED ON R.IdEstado=ED.IdEstado
 INNER JOIN adm_Usuarios AS U ON R.IdUsuario=U.IdUsuario 
 INNER JOIN adm_Usuarios AS UC ON R.IdCajero=UC.IdUsuario  INNER JOIN CentroCosto AS CC ON R.IdCCosto=CC.IdCCosto 
 INNER JOIN  Conceptos AS CN ON R.IdConcepto=CN.IdConcepto  INNER JOIN Terceros AS T ON R.IdTercero =T.IdTercero
 INNER JOIN ConcDiversos AS CD ON D.IdConcepto=CD.IdConcepto  INNER JOIN Puc AS P ON D.IdCuenta=P.IdCuenta
 INNER JOIN Localidades AS L ON T.IdLocal=L.IdLocal  INNER JOIN Departamentos AS DP ON L.IdDep=DP.IdDep 
 INNER JOIN  RegimenDian AS RG ON T.IdRegimen=RG.IdRegimen INNER JOIN SectoresEco AS SE ON T.IdSector=SE.IdSector
 LEFT JOIN  CtasCorrientes AS CTA ON R.CdCta=CTA.IdCta LEFT JOIN Bancos AS BCT ON CTA.IdBanco=BCT.IdBanco
 LEFT JOIN Agencias  AS A ON R.CdAgencia=A.IdAgencia LEFT JOIN SubCentros AS SC ON R.IdSubCos=SC.IdSubCos
 LEFT JOIN Bancos AS BR ON R.CdBanco=BR.IdBanco  
 LEFT JOIN Terceros AS DT ON D.NitTercero=DT.IdTercero  LEFT JOIN CentroCosto AS DC ON D.CdCCosto=DC.IdCCosto 
 LEFT JOIN SubCentros AS DS ON D.CdSubCos=DS.IdSubCos LEFT JOIN Agencias AS DA ON D.CodAgencia=DA.IdAgencia
 LEFT JOIN Localidades AS LA ON A.IdLocal=LA.IdLocal  LEFT JOIN Departamentos AS DAG ON LA.IdDep=DAG.IdDep
 WHERE R.TipDoc=@pmTipDoc AND R.Fecha BETWEEN @pmFechaIni AND @pmFechaFin
 AND R.IdCia LIKE ISNULL(@pmIdCia,'%%') AND R.IdConcepto LIKE ISNULL(@pmIdConcepto,'%')
 AND CdCta LIKE ISNULL(@pmCdCta,'%')  AND R.IdTercero LIKE ISNULL(@pmIdTercero,'%')
 AND CdAgencia LIKE ISNULL(@pmCdAgencia,'%')  AND R.IdCCosto LIKE ISNULL(@pmIdCCosto,'%')
 AND IdCajero LIKE ISNULL(@pmIdCajero,'%')
 AND (EnEfectivo=ISNULL(@pmEnEfectivo,0) or EnEfectivo=ISNULL(@pmEnEfectivo,1))
 AND (Anulado=ISNULL(@pmAnulado,0) or Anulado=ISNULL(@pmAnulado,1))
 ORDER BY R.IdCia,R.Recibo
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE paQryEntradasIns  @pmTipDoc VARCHAR(3),@pmEntradaIni INT=Null,@pmEntradaFin INT=Null,@pmIdCia CHAR(2)=Null
,@pmIdProv VARCHAR(16)=Null,@pmFechaIni SMALLDATETIME=Null,@pmFechaFin SMALLDATETIME=Null
 AS 
 SELECT Entrada,E.IdCia AS CdCia,Compania,E.Fecha AS FechaDoc,Item,K.IdProducto AS CodProducto,DescripProd,K.IdBodega AS CdBodega,Bodega
,Entradas,K.IdUnd AS CdUnid,UM.Unidad AS UnidMedida,VrUnitario,Entradas*VrUnitario AS CostoTotal,K.TarifaIva AS KarTarifIva,VrIvaEnt
,TarifaDct,VrDctoEnt,K.Referencia AS KarReferencia,Descripcion,Unidades,TipOrd,NumOrden,IdCiaOrd,Remision,IdCiaRem
,K.CdCCosto AS KarCodCenCosto,KC.CCosto AS Kar_Ccosto,K.CdSubCos AS KarCodSubcosto,KS.SubCosto AS Kar_Subcentro
,E.IdProv AS NitPrevee,RazonSocial,E.Factura AS NumFactura,E.IdConcepto AS CdConcepto,Concepto,E.Observacion AS Observ
 FROM Trn_Entradas AS E INNER JOIN Trn_Kardex AS K ON E.TipDoc=K.TipDoc AND E.Entrada=K.Documento AND E.IdCia=K.IdCia
 INNER JOIN Companias AS CN ON E.IdCia=CN.IdCia INNER JOIN Terceros AS T ON E.IdProv=T.IdTercero
 INNER JOIN  Conceptos AS C ON E.IdConcepto=C.IdConcepto  INNER JOIN ProdMcias  AS PM ON K.IdProducto=PM.IdProducto 
 INNER JOIN Bodegas AS B ON K.IdBodega=B.IdBodega  INNER JOIN UndMed AS UM ON K.IdUnd=UM.IdUnd
 LEFT JOIN CentroCosto AS KC ON K.CdCCosto=KC.IdCCosto LEFT JOIN SubCentros AS KS ON K.CdSubCos=KS.IdSubCos
 WHERE E.TipDoc=@pmTipDoc AND Anulado=0
 AND Entrada BETWEEN ISNULL(@pmEntradaIni,0) AND ISNULL(@pmEntradaFin,2147483647)
 AND E.IdCia LIKE ISNULL(@pmIdCia,'%%') AND E.IdProv LIKE ISNULL(@pmIdProv,'%')
 AND (E.Fecha>=ISNULL(@pmFechaIni,CAST('19100101' AS SMALLDATETIME)) AND E.Fecha<=ISNULL(@pmFechaFin,CAST('20781230' AS SMALLDATETIME)))
 ORDER BY E.IdCia,Entrada,Item
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE paQryFondos @pmIdFondo VARCHAR(8)
 AS 
  SELECT IdFondo,Fondo,IdClase,IdTercero,CodigoEps,CodigoPen,CodigoArp,Inactivo
  FROM Fondos
  WHERE IdFondo=@pmIdFondo
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE paQryFondosCla @pmIdClase VARCHAR(4)
 AS 
  SELECT IdFondo,Fondo,IdClase,IdTercero,CodigoEps,CodigoPen,CodigoArp,Inactivo
  FROM Fondos
  WHERE IdClase=@pmIdClase
  ORDER BY IdFondo
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE paQryFondosLta @pmIdClase VARCHAR(4)=Null,@pmInactivo BIT=Null
 AS
  SELECT IdFondo,Fondo,F.IdClase AS CodClase,ClaseFondo,IdTercero,CodigoEps,CodigoPen,CodigoArp,F.Inactivo AS Inctivo
  FROM Fondos AS F INNER JOIN ClaseFon AS C ON F.IdClase=C.IdClase
  WHERE F.IdClase LIKE ISNULL(@pmIdClase,'%') 
  AND (F.Inactivo=ISNULL(@pmInactivo,0) or F.Inactivo=ISNULL(@pmInactivo,1))
  ORDER BY Fondo
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE paQryKardexProCc @pmTipDoc VARCHAR(3),@pmDocumento INT,@pmIdCia CHAR(2)
 AS 
 SELECT IdSubgrupo,K.IdProducto AS tmIdProducto,DescripProd,TipoRef,IdProv,CdCCosto,CdSubCos
,SUM(VrCostoEnt) AS SCOSENT,SUM(VrCostoSal) AS SCOSSAL
,SUM(VrPrecio*Entradas) AS SVALENT,SUM(VrPrecio*Salidas) AS SVALSAL
,SUM(VrBruto*Entradas) AS SBRUENT,SUM(VrBruto*Salidas) AS SBRUSAL
,SUM(CASE WHEN  Salidas>0 THEN VrIvaSal ELSE VrIvaEnt END) AS SIVA
,SUM(CASE WHEN  Salidas>0 THEN VrDctoSal ELSE VrDctoEnt END) AS SDCT
,SUM(CASE WHEN  Salidas>0 THEN VrReteSal ELSE VrReteEnt END) AS SRET
,SUM(CASE WHEN  Salidas>0 THEN VrIcaSal ELSE VrIcaEnt END) AS SICA
 FROM Trn_Kardex AS K INNER JOIN ProdMcias AS P ON K.IdProducto=P.IdProducto
 WHERE TipDoc=@pmTipDoc  AND Documento=@pmDocumento AND IdCia=@pmIdCia
 GROUP BY  IdSubgrupo,K.IdProducto,DescripProd,TipoRef,IdProv,CdCCosto,CdSubCos
 ORDER BY IdSubgrupo,K.IdProducto
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE paQryMayContratosAct  @pmIdCia CHAR(2)=Null
 AS 
 SELECT C.NContrato AS NumContrato,IdCia,Fecha,IdCliente,T.RazonSocial AS NomCliente,C.IdAgencia AS CdAgencia,Agencia
,IdTipo,C.IdBonif AS CdBonif,TipoBonif,AboCartera,AboPstmo,ConvBonif,CuotaFija,CondPago,C.DiasGracia AS Dias_Gracia,ModPlazo,C.Plazo AS PlazoCont
,FechaVence,VrCuota,ModCuota,C.IdBandera AS CdBandera,C.IdVend AS CdVend,C.IdEstado AS CdEstado,FechaInicio,CuotaMin,TipoAprob,NumInterno,InvCombus,CuotaBase
--datos del cliente
,CL.IdPlazo AS CliCdPlazo,PZ.Plazo AS CliPlazo,PZ.NVmto AS CliNVce,PZ.DiasPago AS CliDiasPago
,CL.IdVend AS CliVend,VrCupo,VrSaldo,CL.IdEstado AS CliEstado
--datos de agencia
,A.IdPlazo AS AgeCdPlazo,PA.Plazo AS AgePlazo,PA.NVmto AS AgeNVce,PA.DiasPago AS AgeDiasPago
,A.IdVend AS AgeCdVend,VrCupoCre,VrSaldoAct,Referencia,A.IdEstado AS AgeEstado,A.Inactivo AS AgeInactivo
 FROM Trn_MayContratos AS C INNER JOIN MayTiposBonif AS B ON C.IdBonif=B.IdBonif
 INNER JOIN Terceros AS T ON C.IdCliente=T.IdTercero 
 INNER JOIN TercCliente AS CL ON C.IdCliente=CL.IdClie
 INNER JOIN Plazos AS PZ ON CL.IdPlazo=PZ.IdPlazo
 LEFT JOIN  Agencias   AS A ON C.IdAgencia=A.IdAgencia
 LEFT JOIN Plazos AS PA ON A.IdPlazo=PA.IdPlazo
 WHERE   C.IdEstado<>'0002' AND C.IdEstado<>'9999' AND AprobJunta>0 
 AND ( ConvBonif<>0 OR CuotaFija<>0) AND CL.Inactivo=0
 AND IdCia LIKE ISNULL(@pmIdCia,'%%')
 ORDER BY IdCia,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 paQryMayInventario @pmNContrato INT,@pmIdCia CHAR(2),@pmItem INT
 AS 
 SELECT NContrato,IdCia,Item,IdArticulo,Detalle,Cantidad,VrUnitario,VrTotal,TipoArticulo,Referencia,IdTipo
,TipDoc,Documento,IdCiaDoc,CantEntrega,FecEntrega,Estado
,CdProd,NumReq,IdCiaReq,CantReq,NumOrden,IdCiaOrd,CantOrden
 FROM Trn_MayInventario
 WHERE NContrato=@pmNContrato AND IdCia=@pmIdCia  
  AND (Item>=ISNULL(@pmItem,-1)  AND Item<=ISNULL(@pmItem,2147483647))
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE paQryMayInventarioLta @pmNContrato 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=@pmNContrato 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 paQryMayInventarioTot @pmNContrato INT,@pmIdCia CHAR(2)
 AS 
 SELECT COUNT(Item) AS SCOUNT,SUM(Cantidad) AS SCANT,SUM(VrTotal) AS SVALTOT
,SUM(CantEntrega) AS SCANENT,SUM(CantReq) AS SCANTREQ,SUM(CantOrden) AS SCANTODC
 FROM Trn_MayInventario
 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 paQryMayLiqTotalesAbo @pmNLiquida INT,@pmIdCia CHAR(2),@pmTipoNcr VARCHAR(3)
 AS 
 SELECT Item,T.NContrato AS NumContrato,IdCiaCon,T.IdCliente AS NitCliente,RazonSocial,T.IdAgencia AS Id_Agencia,Agencia
,Unidades,VrBonif,VrAbono,VrCuotaFija,T.DiasGracia AS Dias_Gracia,NotaCred,IdCiaNot
,L.Fecha AS FechaLiq,TipCom,Comprobante,IdCiaCom,L.Observacion AS Observ
,CN.IdVend AS CdVend,TC.IdLocal AS CdCiuClie,A.IdLocal AS CdCiuAge
 FROM Trn_MayLiquida AS L INNER JOIN Trn_MayLiqTotales  AS T ON L.NLiquida=T.NLiquida AND L.IdCia=T.IdCia
 INNER JOIN Terceros AS TC ON T.IdCliente=TC.IdTercero
 LEFT JOIN Trn_MayContratos AS CN ON T.NContrato=CN.NContrato AND T.IdCiaCon=CN.IdCia
 LEFT JOIN Agencias AS A ON T.IdAgencia=A.IdAgencia
 WHERE L.NLiquida=@pmNLiquida AND L.IdCia=@pmIdCia AND Estado<>2 AND VrAbono>0
 AND TipoNcr=@pmTipoNcr AND NotaCred<=0
 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 paQryMayLiqTotalesAbp @pmNLiquida INT,@pmIdCia CHAR(2),@pmTipoNcr VARCHAR(3)
 AS 
 SELECT COUNT(Item) AS SCANT
 FROM  Trn_MayLiqTotales 
 WHERE NLiquida=@pmNLiquida AND IdCia=@pmIdCia AND TipoNcr=@pmTipoNcr AND NotaCred<=0 AND VrAbono>0
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE paQryMayLiqTotalesLta @pmNLiquida INT,@pmIdCia CHAR(2)
 AS 
 SELECT Item,T.IdCliente AS NitCliente,TC.RazonSocial AS NombreCliente,T.IdAgencia AS Id_Agencia,Agencia
,Unidades,VrBonif,VrAbono,T.DiasGracia AS Dias_Gracia,L.Fecha AS FechaLiq,LogLiquida
--datos del contrato
,T.NContrato AS NumContrato,CN.Fecha AS FechaContrato,FechaInicio,FechaVence,IdCiaCon,CN.IdTipo AS CdTipo,TipContrato,CN.IdBonif AS CdBonif,TipoBonif
,CN.IdVend AS CdVend,V.RazonSocial AS Vendedor,CN.IdBandera AS CdBandera,TipoBandera
,TipCom,Comprobante,IdCiaCom,L.Observacion AS Observ,TipoNcr,NotaCred,IdCiaNot,VrCuotaFija
--datos del cliente
,TC.TipoId AS TipIdClie,TC.Dv AS DvCliente,TC.NomCial AS NomCialClie,TC.SiglaRaz AS SiglaClie,TC.IdLocal AS CdCiuClie,LT.Localidad AS CiudadCliente
,DP.Departamento AS DptoClie,A.IdLocal AS CdCiuAge,LA.Localidad AS CiudadAgencia,DA.Departamento AS DptoAgencia
 FROM Trn_MayLiquida AS L INNER JOIN Trn_MayLiqTotales  AS T ON L.NLiquida=T.NLiquida AND L.IdCia=T.IdCia
 INNER JOIN Terceros AS TC ON T.IdCliente=TC.IdTercero
 INNER JOIN Localidades AS LT ON TC.IdLocal=LT.IdLocal  INNER JOIN Departamentos AS DP ON LT.IdDep=DP.IdDep 
 LEFT JOIN Trn_MayContratos AS CN ON T.NContrato=CN.NContrato AND T.IdCiaCon=CN.IdCia
 LEFT JOIN Agencias AS A ON T.IdAgencia=A.IdAgencia
 LEFT JOIN Localidades AS LA ON A.IdLocal=LA.IdLocal  INNER JOIN Departamentos AS DA ON LA.IdDep=DA.IdDep 
 LEFT JOIN Terceros AS V ON CN.IdVend=V.IdTercero
 LEFT JOIN  MayTipos AS TCN ON CN.IdTipo=TCN.IdTipo
 LEFT JOIN  MayTiposBonif AS TB ON CN.IdBonif=TB.IdBonif
 LEFT JOIN  TiposBan AS B ON CN.IdBandera=B.IdBandera
 WHERE L.NLiquida=@pmNLiquida AND L.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 paQryMayTiposBonif @pmIdBonif VARCHAR(4)
 AS 
 IF @pmIdBonif IS NULL
  SELECT IdBonif,TipoBonif,AboCartera,AboPstmo
  FROM MayTiposBonif
  WHERE Inactivo=0
  ORDER BY IdBonif
 ELSE
 SELECT IdBonif,TipoBonif,AboCartera,AboPstmo,Inactivo
 FROM MayTiposBonif
 WHERE IdBonif=@pmIdBonif
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE paQryNomPrmSaludLta @pmIdEmpleado VARCHAR(16)=Null,@pmnAnno INT=Null
 AS 
 SELECT N.IdEmpleado AS CdEmpleado,Apellidos,Nombres,nAnno,VrBase,nMeses,VrPromedio
 FROM Trn_NomPrmSalud AS N INNER JOIN Empleados AS E ON N.IdEmpleado=E.IdEmpleado
 WHERE N.IdEmpleado like ISNULL(@pmIdEmpleado,'%')
 AND (nAnno>=ISNULL(@pmnAnno,0)  AND nAnno<=ISNULL(@pmnAnno,2147483647))
 ORDER BY Apellidos,Nombres,nAnno

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE paQryOCompra @pmTipDoc VARCHAR(3),@pmOCompra INT,@pmIdCia CHAR(2)
 AS 
 SELECT TipDoc,OCompra,IdCia,Fecha,FechaVence,IdConcepto,IdProv,VrSubTotal,VrDescuento,VrImpuesto,VrFletes,VrOtros,VrCargos,VrOtrDcto,VrSobtasa,VrImpGlobal,VrNeto
,VrRetencion,VrReteICA,TarifaRet,TarifaIca,CodTarRet,CodTarIca,Cantidad,IdEmpresa,DirEntrega,IdLocEtga,DiasEntraga,NitContac,NomContac,TelContac,emlContac
,IdCCosto,IdSubCos,IdForma,DetallePago,MulPlazos,IdPlazo,CdMney,NitEmpTrans
,EmpTrans,LiqFletes,pVehiculo,CdConductor,TipEnt,Entrada,IdCiaEnt,NumAutoriza,Modalidad,Vigencia,OrigenAdd,Anulado,FecDev,Observacion
,NumAprob,FecAprob,CdUsuAprob,IdEstado,TimeSys,FecUpdate,IdCiaCrea,IdUsuario
 FROM Trn_OCompra
 WHERE TipDoc=@pmTipDoc AND OCompra=@pmOCompra 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  paQryOCompra_Cr @pmTipDoc VARCHAR(3),@pmOCompraIni INT,@pmOCompraFin INT,@pmIdCia CHAR(2)
 AS 
 SELECT O.TipDoc AS Tip_Doc,TipoDoc,OCompra,O.IdCia AS CdCia,Compania,O.Fecha AS FechaDoc,FechaVence,O.IdConcepto AS CdConcepto,Concepto
,O.IdProv AS NitProvee,T.RazonSocial AS NomProvee,VrSubTotal,VrDescuento,VrImpuesto,VrFletes,VrOtros,VrCargos,VrOtrDcto,VrSobtasa,VrImpGlobal,VrNeto,Cantidad
,VrRetencion,VrReteICA,O.TarifaRet AS OrdTarifRet,O.TarifaIca AS OrdTarifIca,O.CodTarRet AS OrdCodTarRef,O.CodTarIca AS OrdCodTarIca,IdEmpresa,TE.RazonSocial AS NomEmpresa,DirEntrega
,IdLocEtga,LE.Localidad AS CiudadEntrega,LE.IdDep AS CdDepEntrega,DE.Departamento AS DptoEntrega,DiasEntraga,O.NitContac AS Nit_Contacto,O.NomContac AS NombContacto,O.TelContac AS Tel_Contacto,O.emlContac AS Eml_Contacto
,O.IdCCosto AS CdCentCosto,CC.CCosto AS CentCosto,O.IdSubCos AS CdSubcost,SC.SubCosto AS SubcCosto,O.IdForma AS CdForma,FormaPago,DetallePago
,MulPlazos,O.IdPlazo AS CdPlazo,Plazo,NVmto,DiasPago,O.CdMney AS CodMoney,NitEmpTrans,EmpTrans,O.LiqFletes AS Liq_Fletes,O.pVehiculo AS OrdPlacaVeh
,CdConductor,TCD.RazonSocial AS Conductor,TipEnt,Entrada,IdCiaEnt,NumAutoriza,Modalidad,Vigencia,OrigenAdd,Anulado,FecDev,O.Observacion AS Observ
,NumAprob,FecAprob,CdUsuAprob,O.IdEstado AS CdEstado,Estado,O.TimeSys AS Fec_Add,O.FecUpdate AS Fec_Update,IdCiaCrea,O.IdUsuario AS CdUsuario,Usuario,Leyenda
--Información del tercero
,T.TipoId AS TercTipoId,T.Dv AS TercDv,T.Codigo AS TercCodigo,T.NomCial AS TercNomCial,T.Direccion AS TercDireccion,T.IdLocal AS TercIdCiu,L.Localidad AS TercCiudad,L.IdDep AS TercIdDep,D.Departamento AS TercDpto
,T.Telefono AS TercTelefono,T.Fax AS TercFax,T.e_mail AS TercEmail,T.IdSector AS CdSector,SectorEco,T.IdRegimen AS CdRegimen,Regimen,T.TipEnte AS TercEnte
--Información del proveedor
,TP.IdGrupo AS ProvGrupo,GrupoClie,TP.NitContac AS ProvNitContact,TP.NomContac AS ProvNomContacto,TP.TelContac AS ProvTelContact,TP.emlContac AS ProvEmailContac,CargContac,DirOrdComp,DiasEntga
,TP.IdClase AS CodClase,NumCuenta,TP.IdBanco AS CdBanco,Banco,Autoret
--detalles
,Item,K.IdProducto AS CodProducto,DescripProd,K.IdBodega AS CdBodega,Bodega,Descrip AS BodDescrip,CdTanque,Entradas,Salidas,K.IdUnd AS CdUnid,UM.Unidad AS UnidMedida,VrUnitario,VrPrecio,VrCostProm,K.TarifaIva AS KarTarifIva,VrIvaEnt,VrIvaSal,TarifaDct,VrDctoEnt,VrDctoSal
,VrCostoEnt,VrCostoSal,K.TarifaRet AS KarTarifRet,VrReteEnt,VrReteSal,K.TarifaIca AS KarTarifIca,VrIcaEnt,VrIcaSal,VrBruto,CdUbic,NumLote,FechLote,K.IdTercero AS KarNitTercero,K.CdCCosto AS KarCodCenCosto,KC.CCosto AS Kar_Ccosto
,K.CdSubCos AS KarCodSubcosto,KS.SubCosto AS Kar_Subcentro,K.pVehiculo AS PlacaVeh,K.Referencia AS KarReferencia,Descripcion,TipOrd,NumOrden,IdCiaOrd,Cotizacion,IdCiaCot,Remision,IdCiaRem,K.Factura AS KarFactura,TipDocDev,NumDocDev,Sobretasa,TasaNac,TasaDep,TasaMun,Soldicom,ImpGlobal
,OtroImpto,Unidades,ItemCombo,Servcios,EsCombo,EsProdBase,CdMoneda,VrTasaCamb,VrDivisa1,VrDivisa2,VrDivisa3,Referencia2,FecOrden
--productos
,PM.Referencia AS Prod_Referencia,TipoRef,PM.IdSubgrupo AS CdSubgrupo,Subgrupo,S.IdGrupo AS CdGrupo,Grupo,G.IdLinea AS CdLinea,Linea,PM.IdMarca AS CdMarca,Marca,Tamano,PM.UndMed AS CdUndMed
,SU.Unidad AS ProdUnidad,ExtciaMin,ExtciaMax,ExtciaAct,VrCostAnt,VrCosto,VrCostPmd,FecUltcom,FecUltVta,Seriales,Lotes,Combo,Tanques,DescripLong
 FROM  Trn_OCompra AS  O INNER JOIN Trn_Kardex AS K ON O.TipDoc=K.TipDoc AND O.OCompra=K.Documento AND O.IdCia=K.IdCia
 INNER JOIN Companias AS CN ON O.IdCia=CN.IdCia  INNER JOIN Sys_TiposDoc AS TD ON O.TipDoc=TD.IdDoc
 INNER JOIN Terceros AS T ON O.IdProv=T.IdTercero INNER JOIN  Conceptos AS C ON O.IdConcepto=C.IdConcepto
 INNER JOIN EstadoDoc AS ED ON O.IdEstado=ED.IdEstado   INNER JOIN adm_Usuarios AS U ON O.IdUsuario=U.IdUsuario
 INNER JOIN  Plazos AS PZ ON O.IdPlazo=PZ.IdPlazo INNER JOIN Formaspago AS FP ON O.IdForma=FP.IdForma
 INNER JOIN Terceros AS TE ON O.IdEmpresa=TE.IdTercero  INNER JOIN CentroCosto AS CC ON O.IdCCosto=CC.IdCCosto
 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 ProdMcias  AS PM ON K.IdProducto=PM.IdProducto INNER JOIN Bodegas AS B ON K.IdBodega=B.IdBodega
 INNER JOIN UndMed AS UM ON K.IdUnd=UM.IdUnd
 INNER JOIN SubGrupos AS S ON PM.IdSubgrupo=S.IdSubgrupo INNER JOIN Grupos AS G ON S.IdGrupo=G.IdGrupo 
 INNER JOIN Lineas AS LN ON G.IdLinea=LN.IdLinea INNER JOIN Marcas AS M ON PM.IdMarca=M.IdMarca
 INNER JOIN Sys_Um AS SU ON PM.UndMed=SU.UndMed 
 LEFT JOIN Localidades AS LE ON O.IdLocEtga=LE.IdLocal  LEFT JOIN Departamentos AS DE ON LE.IdDep=DE.IdDep 
 LEFT JOIN Terceros AS TCD ON O.CdConductor=TCD.IdTercero
 LEFT JOIN CentroCosto AS KC ON K.CdCCosto=KC.IdCCosto LEFT JOIN SubCentros AS KS ON K.CdSubCos=KS.IdSubCos
 LEFT JOIN SubCentros AS SC ON O.IdSubCos=SC.IdSubCos
 LEFT JOIN TercProvee AS TP ON O.IdProv=TP.IdProv
 LEFT JOIN GruposCli AS GP ON TP.IdGrupo=GP.IdGrupo
 LEFT JOIN Bancos AS BP ON TP.IdBanco=BP.IdBanco
 WHERE O.TipDoc=@pmTipDoc AND OCompra BETWEEN @pmOCompraIni AND @pmOCompraFin  AND O.IdCia=@pmIdCia
 ORDER BY OCompra,Item
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE paQryOCompraDet @pmOCompra INT,@pmIdCia CHAR(2)
 AS 
 SELECT Item,K.IdProducto AS CdProducto,Descripcion,K.IdBodega AS CdBodega,Bodega,CdTanque,Entradas,VrPrecio AS CostoBruto,Entradas*VrPrecio AS TotalCosto,TarifaDct,VrDctoEnt
,VrUnitario AS CostoNeto,Entradas*VrUnitario AS TotalCostNeto,TarifaIva,VrIvaEnt,CdCCosto,CdSubCos,K.Referencia AS Referncia,Unidades
,CodTarDct,CodTarIva,ImpGlobal,Servcios,Tanques,DescripProd,O.Fecha AS FecOrden,DiasEntraga,FechaVence,O.IdProv AS NitProvee,T.RazonSocial AS Proveedor
,Modalidad,Vigencia,IdCCosto,IdSubCos,TipEnt,Entrada,IdCiaEnt,K.CodTarRet AS CdTarifRet,K.TarifaRet AS TarifRet,VrReteEnt,K.CodTarIca AS CdTarifIca,K.TarifaIca AS TarifIca,VrIcaEnt
 FROM  Trn_OCompra AS O INNER JOIN Trn_Kardex AS K ON O.TipDoc=K.TipDoc AND O.OCompra=K.Documento AND O.IdCia=K.IdCia
 INNER JOIN ProdMcias AS P ON K.IdProducto=P.IdProducto
 INNER JOIN Bodegas AS B ON K.IdBodega=B.IdBodega
 INNER JOIN Terceros AS T ON O.IdProv=T.IdTercero
 WHERE OCompra=@pmOCompra AND O.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 paQryOCompraLta @pmFechaIni SMALLDATETIME, @pmFechaFin SMALLDATETIME,@pmOCompraIni INT=Null,@pmOCompraFin INT=Null
,@pmIdCia CHAR(2)=Null,@pmIdProv VARCHAR(16)=Null,@pmIdConcepto VARCHAR(4)=Null,@pmIdEstado VARCHAR(4)=Null,@pmAnulado BIT=Null
,@pmModalidad VARCHAR(10)=Null
 AS 
 SELECT OCompra,IdCia,Fecha,FechaVence,O.IdConcepto AS CdConcepto,Concepto,IdProv,T.RazonSocial AS Proveedor,VrSubTotal,VrDescuento,VrImpuesto,VrFletes,VrOtros,VrCargos,VrOtrDcto,VrSobtasa,VrImpGlobal,VrNeto
,VrRetencion,VrReteICA,TarifaRet,TarifaIca,CodTarRet,CodTarIca,Cantidad,IdEmpresa,E.RazonSocial AS Empresa,DirEntrega,IdLocEtga,DiasEntraga,NitContac,NomContac,TelContac,emlContac,IdCCosto,IdSubCos,IdForma,DetallePago,MulPlazos,IdPlazo,CdMney,NitEmpTrans
,EmpTrans,LiqFletes,pVehiculo,CdConductor,TipEnt,Entrada,IdCiaEnt,NumAutoriza,Modalidad,Vigencia,OrigenAdd,Anulado,FecDev,O.Observacion AS Observ,NumAprob,FecAprob,CdUsuAprob
,O.IdEstado AS CdEstado,Estado,TimeSys,FecUpdate,IdCiaCrea,O.IdUsuario AS IdUsuari,Usuario
 FROM Trn_OCompra AS O INNER JOIN Terceros AS T ON O.IdProv=T.IdTercero
 INNER JOIN  Conceptos AS C ON O.IdConcepto=C.IdConcepto
 INNER JOIN EstadoDoc AS ED ON O.IdEstado=ED.IdEstado
 INNER JOIN adm_Usuarios AS U ON O.IdUsuario=U.IdUsuario 
 INNER JOIN Terceros AS E ON O.IdEmpresa=E.IdTercero
 WHERE Fecha BETWEEN @pmFechaIni AND @pmFechaFin AND OCompra BETWEEN ISNULL(@pmOCompraIni,0) AND ISNULL(@pmOCompraFin,2147483647)
 AND IdCia LIKE ISNULL(@pmIdCia,'%%') AND IdProv LIKE ISNULL(@pmIdProv,'%') AND O.IdConcepto LIKE ISNULL(@pmIdConcepto,'%')
 AND O.IdEstado LIKE ISNULL(@pmIdEstado,'%') AND Modalidad LIKE ISNULL(@pmModalidad,'%')
 AND (Anulado=ISNULL(@pmAnulado,0) or Anulado=ISNULL(@pmAnulado,1))
 ORDER BY IdCia,OCompra
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE  paQryOCompraRel @pmTipDoc VARCHAR(3),@pmFechaIni SMALLDATETIME,@pmFechaFin SMALLDATETIME,@pmIdCia CHAR(2)=Null
,@pmIdConcepto VARCHAR(4)=Null,@pmIdProv VARCHAR(16)=Null,@pmIdEmpresa VARCHAR(16)=Null,@pmIdLocEtga VARCHAR(8)=Null,@pmIdCCosto VARCHAR(16)=Null
,@pmModalidad VARCHAR(10)=Null,@pmAnulado BIT=Null,@pmIdEstado VARCHAR(4)=Null
 AS 
 SELECT TipDoc,OCompra,O.IdCia AS CdCia,Compania,Fecha,FechaVence,O.IdConcepto AS CdConcepto,Concepto
,O.IdProv AS NitProvee,T.RazonSocial AS NomProvee,VrSubTotal,VrDescuento,VrImpuesto,VrFletes,VrOtros,VrCargos,VrOtrDcto,VrSobtasa,VrImpGlobal,VrNeto,Cantidad
,VrRetencion,VrReteICA,TarifaRet,TarifaIca,CodTarRet,CodTarIca,IdEmpresa,TE.RazonSocial AS NomEmpresa,DirEntrega
,IdLocEtga,LE.Localidad AS CiudadEntrega,LE.IdDep AS CdDepEntrega,DE.Departamento AS DptoEntrega,DiasEntraga,O.NitContac AS Nit_Contacto,O.NomContac AS NombContacto,O.TelContac AS Tel_Contacto,O.emlContac AS Eml_Contacto
,O.IdCCosto AS CdCentCosto,CCosto,O.IdSubCos AS CdSubcost,SubCosto,O.IdForma AS CdForma,FormaPago,DetallePago
,MulPlazos,O.IdPlazo AS CdPlazo,Plazo,NVmto,DiasPago,O.CdMney AS OrdMoney,NitEmpTrans,EmpTrans,O.LiqFletes AS Liq_Fletes,pVehiculo
,CdConductor,TCD.RazonSocial AS Conductor,TipEnt,Entrada,IdCiaEnt,NumAutoriza,Modalidad,Vigencia,OrigenAdd,Anulado,FecDev,O.Observacion AS Observ
,NumAprob,FecAprob,CdUsuAprob,O.IdEstado AS CdEstado,Estado,TimeSys,O.FecUpdate AS Fec_Update,IdCiaCrea,O.IdUsuario AS CdUsuario,Usuario
--Información del tercero
,T.TipoId AS TercTipoId,T.Dv AS TercDv,T.Codigo AS TercCodigo,T.NomCial AS TercNomCial,T.Direccion AS TercDireccion,T.IdLocal AS TercIdCiu,L.Localidad AS TercCiudad,L.IdDep AS TercIdDep,D.Departamento AS TercDpto
,T.Telefono AS TercTelefono,T.Fax AS TercFax,T.e_mail AS TercEmail,T.IdSector AS CdSector,SectorEco,T.IdRegimen AS CdRegimen,Regimen,T.TipEnte AS TercEnte
--Información del proveedor
,TP.IdGrupo AS ProvGrupo,GrupoClie,TP.NitContac AS ProvNitContact,TP.NomContac AS ProvNomContacto,TP.TelContac AS ProvTelContact,TP.emlContac AS ProvEmailContac,CargContac,DirOrdComp,DiasEntga
,TP.IdClase AS CodClase,NumCuenta,TP.IdBanco AS CdBanco,Banco,Autoret
 FROM  Trn_OCompra AS  O INNER JOIN Companias AS CN ON O.IdCia=CN.IdCia
 INNER JOIN Terceros AS T ON O.IdProv=T.IdTercero INNER JOIN  Conceptos AS C ON O.IdConcepto=C.IdConcepto
 INNER JOIN EstadoDoc AS ED ON O.IdEstado=ED.IdEstado   INNER JOIN adm_Usuarios AS U ON O.IdUsuario=U.IdUsuario
 INNER JOIN  Plazos AS PZ ON O.IdPlazo=PZ.IdPlazo INNER JOIN Formaspago AS FP ON O.IdForma=FP.IdForma
 INNER JOIN Terceros AS TE ON O.IdEmpresa=TE.IdTercero  INNER JOIN CentroCosto AS CC ON O.IdCCosto=CC.IdCCosto
 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
 LEFT JOIN Localidades AS LE ON O.IdLocEtga=LE.IdLocal  LEFT JOIN Departamentos AS DE ON LE.IdDep=DE.IdDep 
 LEFT JOIN Terceros AS TCD ON O.CdConductor=TCD.IdTercero
 LEFT JOIN SubCentros AS SC ON O.IdSubCos=SC.IdSubCos
 LEFT JOIN TercProvee AS TP ON O.IdProv=TP.IdProv
 LEFT JOIN GruposCli AS GP ON TP.IdGrupo=GP.IdGrupo
 LEFT JOIN Bancos AS BP ON TP.IdBanco=BP.IdBanco
 WHERE TipDoc=@pmTipDoc  AND Fecha BETWEEN @pmFechaIni AND @pmFechaFin
 AND O.IdCia LIKE ISNULL(@pmIdCia,'%%') AND O.IdConcepto LIKE ISNULL(@pmIdConcepto,'%')
 AND O.IdProv LIKE ISNULL(@pmIdProv,'%') AND IdEmpresa LIKE ISNULL(@pmIdEmpresa,'%')
 AND IdLocEtga LIKE ISNULL(@pmIdLocEtga,'%') AND O.IdCCosto LIKE ISNULL(@pmIdCCosto,'%')
 AND Modalidad LIKE ISNULL(@pmModalidad,'%') AND O.IdEstado LIKE ISNULL(@pmIdEstado,'%')
 AND (Anulado=ISNULL(@pmAnulado,0) or Anulado=ISNULL(@pmAnulado,1))
 ORDER BY O.IdCia,OCompra
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE paQrytm_FCalidad @pmtmEst CHAR(2)
AS
 SELECT  tmIdVehiculo,NumVeh,tmSemana, tmFecha, tmTarifa, tmVrPagado, tmTipRec, tmRecibo, tmIdCia, tmEstado,tmObserv
,V.IdTipoVeh AS CdTipoVeh,TipoVehiculo,V.IdMarca AS CdMarca,Marca,Modelo,Descripcion,IdPropietario,T.RazonSocial AS Propietario
,IdPoseedor,P.RazonSocial AS Poseedor,IdConductor,CD.RazonSocial AS Conductor,TipoAfil,FecIngreso,V.FechaAdd AS FechaCrea
 FROM  tm_FCalidad AS F INNER JOIN Vehiculos AS V ON F.tmIdVehiculo=V.IdVehiculo 
 INNER JOIN TiposVeh AS TV ON V.IdTipoVeh=TV.IdTipoVeh INNER JOIN Marcas AS M ON V.IdMarca=M.IdMarca
 INNER JOIN Terceros AS T ON V.IdPropietario=T.IdTercero INNER JOIN Terceros AS P ON V.IdPoseedor=P.IdTercero
 INNER JOIN Terceros AS CD ON V.IdConductor=CD.IdTercero
 WHERE tmEst=@pmtmEst
 ORDER BY tmIdVehiculo,tmSemana

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE paQrytm_KdexOdc @pmtmNumero VARCHAR(5)
 AS 
 SELECT tmItem,tmIdProducto,DescripProd,tmIdBodega,tmCdTanque,tmEntradas,tmVrPrecio,tmTarifaDct,tmVrDcto,tmVrUnitario
,(tmEntradas*tmVrPrecio)-tmVrDcto AS VrTotal,tmTarifaIva,tmVrIva,tmTarifaRet,tmVrRete,tmTarifaIca,tmVrIca,tmUnidades,Bodega,tmReferencia,tmDescripcion,tmImpGlobal,tmSobretasa
,tmCdCCosto,tmCdSubCos,tmCodTarDct,tmCodTarRet,tmCodTarIca,tmServcios,Tanques
 FROM  tm_Kdex AS K INNER JOIN ProdMcias AS P ON K.tmIdProducto=P.IdProducto INNER JOIN Bodegas AS B ON K.tmIdBodega=B.IdBodega
 WHERE  tmNumero=@pmtmNumero 
 ORDER BY tmItem
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE paQryVehPropietariosLta @pmIdVehiculo VARCHAR(10)=Null
 AS 
 SELECT  VP.IdVehiculo AS PlacaVeh,NumVeh,Item,NitPropietario,T.RazonSocial AS Propietario,PorcPartic,IdPropietario,NP.RazonSocial AS VehPropietario
,V.IdTipoVeh AS CdTipo,TipoVehiculo,V.IdMarca AS CdMarca,Marca,V.IdColor AS CdColor,NomColor,Modelo,NumMotor,SerieChasis,NumSerie
,NitEmpresa,NE.RazonSocial AS Empresa,IdPoseedor,NT.RazonSocial AS Poseedor,IdConductor,NC.RazonSocial AS Conductor
,V.IdPpd AS CdTipProp,TipoProp,VehPropio,Adquisc,VrAvaludo,NContrato,FecIngreso, FecVigencia, FecRetiro,V.IdAdmon AS CdAdmon,TipoAdmon
,V.IdEstado AS CdEstado,Estado,V.Inactivo AS Inactvo
--datos del propietario
,T.Codigo AS TercCodigo,T.TipoId AS TercTipoId,T.Dv AS TercDv,T.Direccion AS TercDireccion,T.Telefono AS TercTelefono,T.e_mail AS TercEmail
 FROM VehPropietarios AS VP INNER JOIN  Vehiculos  AS V ON VP.IdVehiculo=V.IdVehiculo INNER JOIN Terceros AS T ON  VP.NitPropietario=T.IdTercero
 INNER JOIN TiposVeh AS TV ON V.IdTipoVeh=TV.IdTipoVeh  INNER JOIN Marcas AS M ON V.IdMarca=M.IdMarca
 INNER JOIN TiposCol AS CL ON V.IdColor=CL.IdColor 
 INNER JOIN Terceros AS NP ON  V.IdPropietario=NP.IdTercero  INNER JOIN Terceros AS NT ON  V.IdPoseedor=NT.IdTercero
 INNER JOIN Terceros AS NC ON  V.IdConductor=NC.IdTercero
 INNER JOIN TiposPpt AS TP ON V.IdPpd=TP.IdPpd  INNER JOIN EstadoVeh AS EV ON V.IdEstado=EV.IdEstado  
 INNER JOIN TiposAdm AS TA ON V.IdAdmon=TA.IdAdmon  LEFT JOIN Terceros AS NE ON  V.NitEmpresa=NE.IdTercero
 WHERE VP.IdVehiculo LIKE ISNULL(@pmIdVehiculo,'%') 
 ORDER BY  VP.IdVehiculo,Item
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE paUpCompCaja @pmTipDoc VARCHAR(3),@pmRecibo INT,@pmIdCia CHAR(2),@pmFecha SMALLDATETIME,@pmIdConcepto VARCHAR(4),@pmVrEfectivo MONEY,@pmVrOtros MONEY,@pmVrTotal MONEY,@pmVrTotDebito MONEY
,@pmVrTotCredito MONEY,@pmEnEfectivo BIT,@pmCdCta VARCHAR(4),@pmIdTercero VARCHAR(16),@pmCdAgencia VARCHAR(16),@pmIdCCosto VARCHAR(16),@pmIdSubCos VARCHAR(16),@pmIdCajero VARCHAR(11),@pmNumForma VARCHAR(20)
,@pmCdBanco VARCHAR(4),@pmReferencia VARCHAR(50),@pmTipCom VARCHAR(3),@pmComprobante INT,@pmIdCiaCom CHAR(2),@pmAnulado BIT,@pmNumDev INT,@pmFecDev SMALLDATETIME
,@pmObservacion VARCHAR(250),@pmIdEstado VARCHAR(4),@pmFecUpdate SMALLDATETIME,@pmNumVehiculo VARCHAR(10),@pmFacCalidad INT,@pmSemInicial INT,@pmSemFinal INT
 AS 
 UPDATE Trn_CompCaja SET Fecha=@pmFecha,IdConcepto=@pmIdConcepto,VrEfectivo=@pmVrEfectivo,VrOtros=@pmVrOtros,VrTotal=@pmVrTotal,VrTotDebito=@pmVrTotDebito,VrTotCredito=@pmVrTotCredito,EnEfectivo=@pmEnEfectivo,CdCta=@pmCdCta
,IdTercero=@pmIdTercero,CdAgencia=@pmCdAgencia,IdCCosto=@pmIdCCosto,IdSubCos=@pmIdSubCos,IdCajero=@pmIdCajero,NumForma=@pmNumForma,CdBanco=@pmCdBanco,Referencia=@pmReferencia,TipCom=@pmTipCom
,Comprobante=@pmComprobante,IdCiaCom=@pmIdCiaCom,Anulado=@pmAnulado,NumDev=@pmNumDev,FecDev=@pmFecDev,Observacion=@pmObservacion,IdEstado=@pmIdEstado,FecUpdate=@pmFecUpdate,NumVehiculo=@pmNumVehiculo
,FacCalidad=@pmFacCalidad,SemInicial=@pmSemInicial,SemFinal=@pmSemFinal
 WHERE TipDoc=@pmTipDoc AND Recibo=@pmRecibo 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 paUpFacCalidad @pmIdVehiculo VARCHAR(10),@pmnSemana INT,@pmFecha SMALLDATETIME,@pmVrPagado MONEY
,@pmTipRec VARCHAR(3),@pmRecibo INT,@pmIdCiaRec CHAR(2),@pmObservacion VARCHAR(250)
 AS 
 UPDATE Trn_FacCalidad SET Fecha=@pmFecha,VrPagado=@pmVrPagado
,TipRec=@pmTipRec,Recibo=@pmRecibo,IdCiaRec=@pmIdCiaRec,Observacion=@pmObservacion
 WHERE IdVehiculo=@pmIdVehiculo AND nSemana=@pmnSemana
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE paUpFondos @pmIdFondo VARCHAR(8),@pmFondo VARCHAR(100)
,@pmIdClase VARCHAR(4),@pmIdTercero VARCHAR(16),@pmCodigoEps VARCHAR(10),@pmCodigoPen VARCHAR(10),@pmCodigoArp VARCHAR(10),@pmInactivo BIT
 AS 
 UPDATE Fondos SET Fondo=@pmFondo,IdClase=@pmIdClase,IdTercero=@pmIdTercero
,CodigoEps=@pmCodigoEps,CodigoPen=@pmCodigoPen,CodigoArp=@pmCodigoArp,Inactivo=@pmInactivo
 WHERE IdFondo=@pmIdFondo
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE paUpMayInventario @pmNContrato INT,@pmIdCia CHAR(2),@pmItem INT,@pmIdArticulo VARCHAR(16),@pmDetalle VARCHAR(150),@pmCantidad DECIMAL(14,4)
,@pmVrUnitario MONEY,@pmVrTotal MONEY,@pmTipoArticulo VARCHAR(10),@pmReferencia VARCHAR(50),@pmIdTipo VARCHAR(4),@pmTipDoc VARCHAR(3),@pmDocumento INT
,@pmIdCiaDoc CHAR(2),@pmCantEntrega DECIMAL(14,4),@pmFecEntrega SMALLDATETIME,@pmEstado VARCHAR(10),@pmCdProd VARCHAR(16),@pmNumReq INT,@pmIdCiaReq CHAR(2)
,@pmCantReq DECIMAL(14,4),@pmNumOrden INT,@pmIdCiaOrd CHAR(2),@pmCantOrden DECIMAL(14,4)
 AS 
 UPDATE Trn_MayInventario SET IdArticulo=@pmIdArticulo,Detalle=@pmDetalle,Cantidad=@pmCantidad,VrUnitario=@pmVrUnitario,VrTotal=@pmVrTotal,TipoArticulo=@pmTipoArticulo
,Referencia=@pmReferencia,IdTipo=@pmIdTipo,TipDoc=@pmTipDoc,Documento=@pmDocumento,IdCiaDoc=@pmIdCiaDoc,CantEntrega=@pmCantEntrega,FecEntrega=@pmFecEntrega,Estado=@pmEstado
,CdProd=@pmCdProd,NumReq=@pmNumReq,IdCiaReq=@pmIdCiaReq,CantReq=@pmCantReq,NumOrden=@pmNumOrden,IdCiaOrd=@pmIdCiaOrd,CantOrden=@pmCantOrden
 WHERE NContrato=@pmNContrato AND IdCia=@pmIdCia AND Item=@pmItem
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE paUpMayTiposBonif @pmIdBonif VARCHAR(4),@pmTipoBonif VARCHAR(50)
,@pmAboCartera BIT,@pmAboPstmo BIT,@pmInactivo BIT
 AS 
 UPDATE MayTiposBonif SET TipoBonif=@pmTipoBonif,Inactivo=@pmInactivo,AboCartera=@pmAboCartera
,AboPstmo=@pmAboPstmo
 WHERE IdBonif=@pmIdBonif
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE paUpOCompra @pmTipDoc VARCHAR(3),@pmOCompra INT,@pmIdCia CHAR(2),@pmFecha SMALLDATETIME,@pmFechaVence SMALLDATETIME,@pmIdConcepto VARCHAR(4),@pmIdProv VARCHAR(16),@pmVrSubTotal MONEY,@pmVrDescuento MONEY,@pmVrImpuesto MONEY,@pmVrFletes MONEY
,@pmVrOtros MONEY,@pmVrCargos MONEY,@pmVrOtrDcto MONEY,@pmVrSobtasa MONEY,@pmVrImpGlobal MONEY,@pmVrNeto MONEY,@pmCantidad DECIMAL(14,4),@pmIdEmpresa VARCHAR(16),@pmDirEntrega VARCHAR(250),@pmIdLocEtga VARCHAR(8),@pmDiasEntraga INT,@pmNitContac VARCHAR(16),@pmNomContac VARCHAR(150)
,@pmTelContac VARCHAR(20),@pmemlContac VARCHAR(100),@pmIdCCosto VARCHAR(16),@pmIdSubCos VARCHAR(16),@pmIdForma VARCHAR(4),@pmDetallePago VARCHAR(100),@pmMulPlazos BIT,@pmIdPlazo VARCHAR(4),@pmCdMney VARCHAR(5),@pmNitEmpTrans VARCHAR(16),@pmEmpTrans VARCHAR(150),@pmLiqFletes BIT
,@pmpVehiculo VARCHAR(10),@pmCdConductor VARCHAR(16),@pmTipEnt VARCHAR(3),@pmEntrada INT,@pmIdCiaEnt CHAR(2),@pmNumAutoriza INT,@pmModalidad VARCHAR(10),@pmVigencia VARCHAR(10),@pmAnulado BIT,@pmFecDev SMALLDATETIME,@pmObservacion VARCHAR(250),@pmNumAprob INT,@pmFecAprob SMALLDATETIME
,@pmCdUsuAprob VARCHAR(11),@pmIdEstado VARCHAR(4),@pmVrRetencion MONEY,@pmVrReteICA MONEY,@pmTarifaRet DECIMAL(14,4),@pmTarifaIca DECIMAL(14,4),@pmCodTarRet VARCHAR(4),@pmCodTarIca VARCHAR(4),@pmFecUpdate SMALLDATETIME
 AS 
 UPDATE Trn_OCompra SET Fecha=@pmFecha,FechaVence=@pmFechaVence,IdConcepto=@pmIdConcepto,IdProv=@pmIdProv,VrSubTotal=@pmVrSubTotal,VrDescuento=@pmVrDescuento,VrImpuesto=@pmVrImpuesto,VrFletes=@pmVrFletes,VrOtros=@pmVrOtros,VrCargos=@pmVrCargos,VrOtrDcto=@pmVrOtrDcto,VrSobtasa=@pmVrSobtasa,VrImpGlobal=@pmVrImpGlobal
,VrNeto=@pmVrNeto,Cantidad=@pmCantidad,IdEmpresa=@pmIdEmpresa,DirEntrega=@pmDirEntrega,IdLocEtga=@pmIdLocEtga,DiasEntraga=@pmDiasEntraga,NitContac=@pmNitContac,NomContac=@pmNomContac,TelContac=@pmTelContac,emlContac=@pmemlContac,IdCCosto=@pmIdCCosto,IdSubCos=@pmIdSubCos,IdForma=@pmIdForma,DetallePago=@pmDetallePago
,MulPlazos=@pmMulPlazos,IdPlazo=@pmIdPlazo,CdMney=@pmCdMney,NitEmpTrans=@pmNitEmpTrans,EmpTrans=@pmEmpTrans,LiqFletes=@pmLiqFletes,pVehiculo=@pmpVehiculo,CdConductor=@pmCdConductor,TipEnt=@pmTipEnt,Entrada=@pmEntrada,IdCiaEnt=@pmIdCiaEnt,NumAutoriza=@pmNumAutoriza,Modalidad=@pmModalidad,Vigencia=@pmVigencia
,Anulado=@pmAnulado,FecDev=@pmFecDev,Observacion=@pmObservacion,NumAprob=@pmNumAprob,FecAprob=@pmFecAprob,CdUsuAprob=@pmCdUsuAprob,IdEstado=@pmIdEstado
,VrRetencion=@pmVrRetencion,VrReteICA=@pmVrReteICA,TarifaRet=@pmTarifaRet,TarifaIca=@pmTarifaIca,CodTarRet=@pmCodTarRet,CodTarIca=@pmCodTarIca,FecUpdate=@pmFecUpdate
 WHERE TipDoc=@pmTipDoc AND OCompra=@pmOCompra AND IdCia=@pmIdCia
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO