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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE PROCEDURE paDelTerminales @pmIdTerminal VARCHAR(4)
 AS 
 DELETE FROM Terminales
 WHERE IdTerminal=@pmIdTerminal

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE paInsAcuPrestamosCau  @pmnAnno INT,@pmnMes INT,@pmIdCia CHAR(2)=Null,@pmIdCliente VARCHAR(16)=Null
 AS 
 INSERT INTO AcuPrestamos (nAnno,nMes,IdCia,IdCliente,IdAgencia,SaldoAnt,Prestamos,DevPrestamos,Recibos,DevRecibos,NotasDeb,NotasCre,Facturas,DevFacturas)
 SELECT YEAR(CASE C.TipDoc  WHEN 'PR1' THEN P.FecPrestamo ELSE C.Fecha END),MONTH(CASE C.TipDoc  WHEN 'PR1' THEN P.FecPrestamo ELSE C.Fecha END)
,C.IdCia,C.IdCliente,C.IdAgencia,0,SUM(CASE C.TipDoc WHEN 'PR1' THEN C.VrTotal ELSE 0 END),0,0,0
 ,SUM(CASE C.TipDoc WHEN 'ND2' THEN C.VrTotal ELSE 0 END),0,SUM(CASE C.TipDoc WHEN 'FIF' THEN C.VrTotal ELSE 0 END),0
 FROM Trn_FinCuotas AS C LEFT JOIN Trn_FinPrestamo AS P ON C.TipDoc=P.TipDoc  AND C.IdPrestamo=P.IdPrestamo AND C.IdCia=P.IdCia
 WHERE C.TipDoc IN ('PR1','ND2','FIF')  AND YEAR(CASE C.TipDoc  WHEN 'PR1' THEN P.FecPrestamo ELSE C.Fecha END)=@pmnAnno
 AND MONTH(CASE C.TipDoc  WHEN 'PR1' THEN P.FecPrestamo ELSE C.Fecha END)=@pmnMes AND C.IdCia LIKE ISNULL(@pmIdCia,'%%')
 AND C.IdCliente LIKE ISNULL(@pmIdCliente,'%')
 GROUP BY YEAR(CASE C.TipDoc  WHEN 'PR1' THEN P.FecPrestamo ELSE C.Fecha END),MONTH(CASE C.TipDoc  WHEN 'PR1' THEN P.FecPrestamo ELSE C.Fecha END),C.IdCia,C.IdCliente,C.IdAgencia
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE paInsCnxextna @pmIdReg INT,@pmCnxEngine VARCHAR(10),@pmCnxDso VARCHAR(250),@pmCnxDbname VARCHAR(50),@pmCnxAutentic VARCHAR(3)
,@pmCnxUid VARCHAR(50),@pmCnxPwd VARCHAR(50),@pmCnxOwner VARCHAR(20),@pmCnxObserv VARCHAR(250),@pmCnxTipo VARCHAR(10),@pmCnxPpties VARCHAR(250)
,@pmCnxDriver VARCHAR(100),@pmCnxPort VARCHAR(30),@pmCnxTerminal VARCHAR(4)
 AS 
 INSERT INTO Sys_Cnxextna (IdReg,CnxTipo,CnxEngine,CnxDso,CnxDbname,CnxAutentic,CnxUid,CnxPwd,CnxOwner,CnxPpties,CnxObserv,CnxDriver,CnxPort,CnxTerminal)
 VALUES (@pmIdReg,@pmCnxTipo,@pmCnxEngine,@pmCnxDso,@pmCnxDbname,@pmCnxAutentic,@pmCnxUid,@pmCnxPwd,@pmCnxOwner,@pmCnxPpties,@pmCnxObserv,@pmCnxDriver,@pmCnxPort,@pmCnxTerminal)
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE paInsDiferidos @pmIdDiferido VARCHAR(30),@pmNomDiferido VARCHAR(250),@pmIdTipDif VARCHAR(4),@pmIdGrupo VARCHAR(4),@pmIdCCosto VARCHAR(16),@pmIdSubCos VARCHAR(16),@pmIdDep VARCHAR(4)
,@pmIdRespons VARCHAR(16),@pmIdCia CHAR(2),@pmFecInicio SMALLDATETIME,@pmValorTotal MONEY,@pmVrAdiciones MONEY,@pmVrDisminucion MONEY,@pmNumPeriodos INT,@pmFecFinAmtza SMALLDATETIME
,@pmAjustable BIT,@pmContable BIT,@pmVrAjuInfAcum MONEY,@pmVrAmtzaAcuHis MONEY,@pmVrAmtzaAcum MONEY,@pmVrAjuInfAmtza MONEY,@pmCuotasAmtza INT,@pmVrCorrMonDifAcu MONEY
,@pmCorrMonDiferida MONEY,@pmNumMesesDif INT,@pmFecUltAmtza SMALLDATETIME,@pmCueEnGrupo BIT,@pmIdCuenta VARCHAR(16),@pmIdCueAmortiza VARCHAR(16),@pmIdCueAjuDif VARCHAR(16),@pmIdCueCorrDif VARCHAR(16)
,@pmIdCueAjuAmtza VARCHAR(16),@pmIdCueCorrAA VARCHAR(16),@pmNumDoc VARCHAR(20),@pmReferencia VARCHAR(50),@pmDesUbicacion VARCHAR(150),@pmIdEstado VARCHAR(4),@pmFecRetiro SMALLDATETIME
,@pmIdCausal VARCHAR(4),@pmComentRetiro VARCHAR(250),@pmComentarios VARCHAR(250),@pmTipoAmtza CHAR(1),@pmNitCliente VARCHAR(16),@pmAgenCliente VARCHAR(16),@pmCantGalones DECIMAL (14,4),@pmIdProv VARCHAR(16)
,@pmNumPlaca  VARCHAR(20),@pmCodInstala VARCHAR(4),@pmTipoInvers VARCHAR(10),@pmFecCreacion SMALLDATETIME,@pmIdUsuario VARCHAR(11)
 AS 
 INSERT INTO Diferidos (IdDiferido,NomDiferido,IdTipDif,IdGrupo,IdCCosto,IdSubCos,IdDep,IdRespons,IdCia,FecInicio,ValorTotal,VrAdiciones,VrDisminucion,NumPeriodos,FecFinAmtza,Ajustable,Contable,VrAjuInfAcum,VrAmtzaAcuHis,VrAmtzaAcum
,VrAjuInfAmtza,CuotasAmtza,VrCorrMonDifAcu,CorrMonDiferida,NumMesesDif,FecUltAmtza,CueEnGrupo,IdCuenta,IdCueAmortiza,IdCueAjuDif,IdCueCorrDif,IdCueAjuAmtza,IdCueCorrAA,NumDoc,Referencia,DesUbicacion,IdEstado,FecRetiro,IdCausal
,ComentRetiro,Comentarios,FecCreacion,IdUsuario,TipoAmtza,NitCliente,AgenCliente,CantGalones,IdProv,NumPlaca,CodInstala,TipoInvers)
 VALUES (@pmIdDiferido,@pmNomDiferido,@pmIdTipDif,@pmIdGrupo,@pmIdCCosto,@pmIdSubCos,@pmIdDep,@pmIdRespons,@pmIdCia,@pmFecInicio,@pmValorTotal,@pmVrAdiciones,@pmVrDisminucion,@pmNumPeriodos
,@pmFecFinAmtza,@pmAjustable,@pmContable,@pmVrAjuInfAcum,@pmVrAmtzaAcuHis,@pmVrAmtzaAcum,@pmVrAjuInfAmtza,@pmCuotasAmtza,@pmVrCorrMonDifAcu,@pmCorrMonDiferida,@pmNumMesesDif
,@pmFecUltAmtza,@pmCueEnGrupo,@pmIdCuenta,@pmIdCueAmortiza,@pmIdCueAjuDif,@pmIdCueCorrDif,@pmIdCueAjuAmtza,@pmIdCueCorrAA,@pmNumDoc,@pmReferencia,@pmDesUbicacion,@pmIdEstado,@pmFecRetiro,@pmIdCausal
,@pmComentRetiro,@pmComentarios,@pmFecCreacion,@pmIdUsuario,@pmTipoAmtza,@pmNitCliente,@pmAgenCliente,@pmCantGalones,@pmIdProv,@pmNumPlaca,@pmCodInstala,@pmTipoInvers)
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE paInsFinFacturas @pmTipDoc VARCHAR(3),@pmIdPrestamo INT,@pmIdCia CHAR(2),@pmItem INT,@pmNumFactura VARCHAR(20),@pmFecha SMALLDATETIME
,@pmFechaVence SMALLDATETIME,@pmVrFactura MONEY,@pmVrInteres MONEY,@pmDiasLiq DECIMAL(14,4),@pmTasaEfeMes DECIMAL(16,8),@pmTasaEfeDia DECIMAL(16,8),@pmFechaPago SMALLDATETIME,@pmItemPago INT
,@pmFacturado BIT,@pmTipFac VARCHAR(3),@pmFacturaInt INT,@pmFecFactura SMALLDATETIME,@pmFecInicio SMALLDATETIME,@pmFecFinal SMALLDATETIME
,@pmVrAbono MONEY,@pmCdCiaFac CHAR(2),@pmFechaLiq SMALLDATETIME,@pmDiasPlazo INT
 AS 
 INSERT INTO Trn_FinFacturas (TipDoc,IdPrestamo,IdCia,Item,NumFactura,Fecha,FechaVence,VrFactura,VrInteres,DiasLiq,TasaEfeMes,TasaEfeDia,FechaPago,ItemPago,FechaLiq,FecInicio,FecFinal,VrAbono,Facturado,TipFac,FacturaInt,CdCiaFac,FecFactura,DiasPlazo)
 VALUES (@pmTipDoc,@pmIdPrestamo,@pmIdCia,@pmItem,@pmNumFactura,@pmFecha,@pmFechaVence,@pmVrFactura,@pmVrInteres,@pmDiasLiq
,@pmTasaEfeMes,@pmTasaEfeDia,@pmFechaPago,@pmItemPago,@pmFechaLiq,@pmFecInicio,@pmFecFinal,@pmVrAbono,@pmFacturado,@pmTipFac,@pmFacturaInt,@pmCdCiaFac,@pmFecFactura,@pmDiasPlazo)
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE PROCEDURE paInsFinPrestamo @pmTipDoc VARCHAR(3),@pmIdPrestamo INT,@pmIdCia CHAR(2),@pmFecha SMALLDATETIME,@pmFecPrestamo SMALLDATETIME,@pmFecCorte SMALLDATETIME,@pmIdConcepto VARCHAR(4),@pmIdCliente VARCHAR(16),@pmIdAgencia VARCHAR(16),@pmVrPrestamo MONEY,@pmIdTasa VARCHAR(4),@pmNPlazos INT,@pmCausacion VARCHAR(10),@pmIdMora VARCHAR(4),@pmNContrato INT,@pmCdCiaCon CHAR(2)
,@pmNActaJunta INT,@pmNumCredito VARCHAR(20),@pmIdVend VARCHAR(16),@pmpVehiculo VARCHAR(10),@pmCdConductor VARCHAR(16),@pmTipoGarantia VARCHAR(10),@pmVrGarantia MONEY,@pmDescGarantia VARCHAR(250),@pmCxPagar BIT,@pmNumCuotaIni INT,@pmAcumCapital MONEY,@pmAcumIntereses MONEY,@pmAcumIntMora MONEY,@pmAcumCuotas INT,@pmPagosCapital MONEY,@pmPagosIntereses MONEY,@pmPagosIntMora MONEY,@pmCuotasPagadas INT
,@pmCuotasMora INT,@pmTipoAprob VARCHAR(20),@pmTipCom VARCHAR(3),@pmComprobante INT,@pmIdCiaCom CHAR(2),@pmAnulado BIT,@pmNumDev INT,@pmFecDev SMALLDATETIME,@pmObservacion VARCHAR(250),@pmIdEstado VARCHAR(4),@pmDiaCausac INT,@pmDiaCausac2 INT,@pmDiasVence VARCHAR(50),@pmIdLinea VARCHAR(4),@pmTipoPlazo CHAR(1),@pmTipoCausac INT,@pmCdTasa2 VARCHAR(4),@pmVrSeguro MONEY,@pmVrIntereses MONEY,@pmVrNeto MONEY,@pmVrCuota MONEY
,@pmTipoCredito VARCHAR(10),@pmCalcIntMora BIT,@pmNitEmpConv VARCHAR(16),@pmCuotasConv INT,@pmRefinanciado INT,@pmNumPrestRef INT,@pmCiaPrestRef CHAR(2),@pmVrRefinanc MONEY,@pmDescRefinanc  VARCHAR(150),@pmCdAlmacen VARCHAR(4),@pmCauAnticipado BIT,@pmOrigenAdd VARCHAR(10),@pmTimeSys SMALLDATETIME,@pmIdCiaCrea CHAR(2),@pmIdUsuario VARCHAR(11)
 AS 
 INSERT INTO Trn_FinPrestamo (TipDoc,IdPrestamo,IdCia,Fecha,FecPrestamo,FecCorte,DiaCausac,DiaCausac2,DiasVence,IdConcepto,IdCliente,IdAgencia,VrPrestamo,IdLinea,IdTasa,NPlazos,TipoPlazo,Causacion,IdMora,NContrato,CdCiaCon,NActaJunta,NumCredito,IdVend,pVehiculo,CdConductor,TipoGarantia,VrGarantia,DescGarantia,CxPagar,NumCuotaIni,AcumCapital,AcumIntereses,AcumIntMora,AcumCuotas,PagosCapital,PagosIntereses,PagosIntMora,CuotasPagadas,CuotasMora
,TipoAprob,TipoCausac,CdTasa2,VrSeguro,VrIntereses,VrNeto,VrCuota,TipoCredito,CalcIntMora,NitEmpConv,CuotasConv,Refinanciado,NumPrestRef,CiaPrestRef,VrRefinanc,DescRefinanc,CdAlmacen,CauAnticipado,OrigenAdd,TipCom,Comprobante,IdCiaCom,Anulado,NumDev,FecDev,Observacion,IdEstado,TimeSys,IdCiaCrea,IdUsuario)
 VALUES (@pmTipDoc,@pmIdPrestamo,@pmIdCia,@pmFecha,@pmFecPrestamo,@pmFecCorte,@pmDiaCausac,@pmDiaCausac2,@pmDiasVence,@pmIdConcepto,@pmIdCliente,@pmIdAgencia,@pmVrPrestamo,@pmIdLinea,@pmIdTasa,@pmNPlazos,@pmTipoPlazo,@pmCausacion,@pmIdMora,@pmNContrato,@pmCdCiaCon,@pmNActaJunta,@pmNumCredito,@pmIdVend,@pmpVehiculo,@pmCdConductor,@pmTipoGarantia,@pmVrGarantia,@pmDescGarantia
,@pmCxPagar,@pmNumCuotaIni,@pmAcumCapital,@pmAcumIntereses,@pmAcumIntMora,@pmAcumCuotas,@pmPagosCapital,@pmPagosIntereses,@pmPagosIntMora,@pmCuotasPagadas,@pmCuotasMora,@pmTipoAprob,@pmTipoCausac
,@pmCdTasa2,@pmVrSeguro,@pmVrIntereses,@pmVrNeto,@pmVrCuota,@pmTipoCredito,@pmCalcIntMora,@pmNitEmpConv,@pmCuotasConv,@pmRefinanciado,@pmNumPrestRef,@pmCiaPrestRef,@pmVrRefinanc,@pmDescRefinanc,@pmCdAlmacen,@pmCauAnticipado,@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 paInsNomAlqNov @pmTipoLiq VARCHAR(3),@pmNumero VARCHAR(20),@pmItem INT,@pmIdEmpleado VARCHAR(16),@pmNovIng INT,@pmNovRet INT,@pmNovTde INT,@pmNovTae INT,@pmNovTdp INT,@pmNovTap INT
,@pmNovVsp INT,@pmNovVte INT,@pmNovVst INT,@pmNovSln INT,@pmNovIge INT,@pmNovLma INT,@pmNovVac INT,@pmNovAvp INT,@pmNovVct INT,@pmNovIrp INT,@pmCodNewPen VARCHAR(8),@pmCodNewSal VARCHAR(8),@pmCodNewIns VARCHAR(4)
,@pmCodNewRie VARCHAR(4),@pmCodNewGru VARCHAR(4),@pmDiasNov INT,@pmDiasSan DECIMAL(14,4)
 AS 
 INSERT INTO Trn_NomAlqNov (TipoLiq,Numero,Item,IdEmpleado,NovIng,NovRet,NovTde,NovTae,NovTdp,NovTap,NovVsp,NovVte,NovVst,NovSln,NovIge,NovLma,NovVac,NovAvp,NovVct,NovIrp,CodNewPen,CodNewSal,CodNewIns,CodNewRie,CodNewGru,DiasNov,DiasSan)
 VALUES (@pmTipoLiq,@pmNumero,@pmItem,@pmIdEmpleado,@pmNovIng,@pmNovRet,@pmNovTde,@pmNovTae,@pmNovTdp,@pmNovTap,@pmNovVsp,@pmNovVte,@pmNovVst,@pmNovSln,@pmNovIge,@pmNovLma,@pmNovVac,@pmNovAvp,@pmNovVct
,@pmNovIrp,@pmCodNewPen,@pmCodNewSal,@pmCodNewIns,@pmCodNewRie,@pmCodNewGru,@pmDiasNov,@pmDiasSan)

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE paInsOComb @pmTipDoc VARCHAR(3),@pmOCargue INT,@pmIdCia CHAR(2),@pmFecha SMALLDATETIME,@pmFechaDesp SMALLDATETIME,@pmFechaVence SMALLDATETIME,@pmIdConcepto VARCHAR(4),@pmIdCliente VARCHAR(16),@pmIdAgencia VARCHAR(16),@pmVrSubTotal MONEY,@pmVrDescuento MONEY
,@pmVrImpuesto MONEY,@pmVrFletes MONEY,@pmVrOtros MONEY,@pmVrCargos MONEY,@pmVrOtrDcto MONEY,@pmVrSobretasa MONEY,@pmVrImpGlobal MONEY,@pmVrNeto MONEY,@pmCantidad DECIMAL(14,4),@pmIdVend VARCHAR(16),@pmTarifaCom DECIMAL(14,4),@pmCodTarCom VARCHAR(4),@pmDirEnvio VARCHAR(250)
,@pmIdLocal VARCHAR(8),@pmLugarEnv VARCHAR(50),@pmDiasEntraga INT,@pmIdVehiculo VARCHAR(10),@pmIdConductor VARCHAR(16),@pmNitEmpTrans VARCHAR(16),@pmEmpTrans VARCHAR(150),@pmCdRuta VARCHAR(4),@pmReferncia VARCHAR(50),@pmIdForma VARCHAR(4),@pmDetallePago VARCHAR(150)
,@pmMulPlazos BIT,@pmIdPlazo VARCHAR(4),@pmTipPed VARCHAR(3),@pmPedido INT,@pmIdCiaPed CHAR(2),@pmTipGui VARCHAR(3),@pmGuia INT,@pmIdCiaGui CHAR(2),@pmTipFac VARCHAR(3),@pmFactura INT,@pmIdCiaFac CHAR(2),@pmFechaFact SMALLDATETIME,@pmAutzaMora INT,@pmAutzaCupo INT,@pmAprobacion INT,@pmFechaAprob SMALLDATETIME
,@pmDetalleAprob VARCHAR(250),@pmCdUsuAprob VARCHAR(11),@pmAnulado BIT,@pmFecDev SMALLDATETIME,@pmObservacion VARCHAR(250),@pmIdEstado VARCHAR(4),@pmZonaFrontera BIT,@pmIdTerminal VARCHAR(4),@pmOrigenAdd VARCHAR(10),@pmTimeSys SMALLDATETIME,@pmIdCiaCrea CHAR(2),@pmIdUsuario VARCHAR(11)
 AS 
 INSERT INTO Trn_OComb (TipDoc,OCargue,IdCia,Fecha,FechaDesp,FechaVence,IdConcepto,IdCliente,IdAgencia,VrSubTotal,VrDescuento,VrImpuesto,VrFletes,VrOtros,VrCargos,VrOtrDcto,VrSobretasa,VrImpGlobal,VrNeto,Cantidad,IdVend,TarifaCom,CodTarCom,DirEnvio,IdLocal,LugarEnv,DiasEntraga,IdVehiculo,IdConductor,NitEmpTrans,EmpTrans,CdRuta,Referncia
,IdForma,DetallePago,MulPlazos,IdPlazo,TipPed,Pedido,IdCiaPed,TipGui,Guia,IdCiaGui,TipFac,Factura,IdCiaFac,FechaFact,AutzaMora,AutzaCupo,Aprobacion,FechaAprob,DetalleAprob,CdUsuAprob,IdTerminal,OrigenAdd,ZonaFrontera,Anulado,FecDev,Observacion,IdEstado,TimeSys,IdCiaCrea,IdUsuario)
 VALUES (@pmTipDoc,@pmOCargue,@pmIdCia,@pmFecha,@pmFechaDesp,@pmFechaVence,@pmIdConcepto,@pmIdCliente,@pmIdAgencia,@pmVrSubTotal,@pmVrDescuento,@pmVrImpuesto,@pmVrFletes,@pmVrOtros,@pmVrCargos,@pmVrOtrDcto,@pmVrSobretasa,@pmVrImpGlobal,@pmVrNeto,@pmCantidad,@pmIdVend,@pmTarifaCom,@pmCodTarCom
,@pmDirEnvio,@pmIdLocal,@pmLugarEnv,@pmDiasEntraga,@pmIdVehiculo,@pmIdConductor,@pmNitEmpTrans,@pmEmpTrans,@pmCdRuta,@pmReferncia,@pmIdForma,@pmDetallePago,@pmMulPlazos,@pmIdPlazo,@pmTipPed,@pmPedido,@pmIdCiaPed,@pmTipGui,@pmGuia,@pmIdCiaGui,@pmTipFac,@pmFactura,@pmIdCiaFac,@pmFechaFact,@pmAutzaMora,@pmAutzaCupo,@pmAprobacion
,@pmFechaAprob,@pmDetalleAprob,@pmCdUsuAprob,@pmIdTerminal,@pmOrigenAdd,@pmZonaFrontera,@pmAnulado,@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 paInsTanques @pmIdTanque VARCHAR(4),@pmIdProducto VARCHAR(16),@pmDescripcion VARCHAR(50),@pmCapTanq DECIMAL(14,4),@pmNivAgua DECIMAL(14,4),@pmCapNeta DECIMAL(14,4)
,@pmSaldoActual DECIMAL(14,4),@pmTnqGas BIT,@pmTnqUnido BIT,@pmTnqVirtual BIT,@pmCdNueTan VARCHAR(4),@pmNColor INT,@pmFechaAdd SMALLDATETIME,@pmInactivo BIT,@pmIdCiaDef CHAR(2)
,@pmCodigoApi VARCHAR(10),@pmCdTerminal VARCHAR(4)
 AS 
 INSERT INTO Tanques (IdTanque,IdProducto,Descripcion,CapTanq,NivAgua,CapNeta,SaldoActual,TnqGas,TnqUnido,TnqVirtual,CdNueTan,NColor,FechaAdd,Inactivo,IdCiaDef,CodigoApi,CdTerminal)
 VALUES (@pmIdTanque,@pmIdProducto,@pmDescripcion,@pmCapTanq,@pmNivAgua,@pmCapNeta,@pmSaldoActual,@pmTnqGas,@pmTnqUnido,@pmTnqVirtual,@pmCdNueTan,@pmNColor
,@pmFechaAdd,@pmInactivo,@pmIdCiaDef,@pmCodigoApi,@pmCdTerminal)
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE PROCEDURE paInsTerminales @pmIdTerminal VARCHAR(4),@pmTerminal VARCHAR(50),@pmIdCia CHAR(2),@pmInactivo BIT
 AS 
 INSERT INTO Terminales (IdTerminal,Terminal,IdCia,Inactivo)
 VALUES (@pmIdTerminal,@pmTerminal,@pmIdCia,@pmInactivo)

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE paInstm_Aportes @pmtmEst VARCHAR(5),@pmtmItem INT,@pmtmIdEmpleado VARCHAR(16),@pmtmApellido1 VARCHAR(50),@pmtmApellido2 VARCHAR(50),@pmtmNombre1 VARCHAR(50),@pmtmNombre2 VARCHAR(50),@pmtmNContto INT,@pmtmDiasPen DECIMAL(10,4),@pmtmDiasSal DECIMAL(10,4),@pmtmDiasRie DECIMAL(10,4),@pmtmDiasCom DECIMAL(10,4),@pmtmBasePen MONEY,@pmtmBaseSal MONEY,@pmtmBaseRie MONEY,@pmtmBaseCom MONEY,@pmtmBaseRef MONEY,@pmtmValorPen MONEY,@pmtmValorSal MONEY,@pmtmValorRie MONEY,@pmtmValorCom MONEY,@pmtmValorSena MONEY
,@pmtmValorIcbf MONEY,@pmtmValorFsp MONEY,@pmtmValorFsu MONEY,@pmtmValorESAP MONEY,@pmtmValorMin MONEY,@pmtmTarfaPen DECIMAL(10,4),@pmtmTarfaSal DECIMAL(10,4),@pmtmTarfaRie DECIMAL(10,4),@pmtmTarfaFsp DECIMAL(10,4),@pmtmTarfaFsu DECIMAL(10,4),@pmtmTarfaEsa DECIMAL(10,4),@pmtmTarfaMin DECIMAL(10,4),@pmtmTarfaCom DECIMAL(10,4),@pmtmTarfaIcb DECIMAL(10,4),@pmtmTarfaSen DECIMAL(10,4),@pmtmNovIng INT,@pmtmNovRet INT,@pmtmNovTda INT,@pmtmNovTaa INT,@pmtmNovTdp INT,@pmtmNovTap INT,@pmtmNovVsp INT,@pmtmNovVte INT,@pmtmNovVst INT,@pmtmNovSln INT,@pmtmNovIge INT,@pmtmNovLma INT,@pmtmNovVac INT
,@pmtmNovAvp INT,@pmtmNovIrp INT,@pmtmNovVct INT,@pmtmCodNewEps VARCHAR(8),@pmtmCodNewPen VARCHAR(8),@pmtmCodNewRie VARCHAR(8),@pmtmCodNewIns VARCHAR(4),@pmtmCodNewTar VARCHAR(4),@pmtmCodNewGru VARCHAR(4),@pmtmVrIncap MONEY,@pmtmVrLicencia MONEY,@pmtmVrVolAfi MONEY,@pmtmVrVolApo MONEY,@pmtmVrNoReten MONEY,@pmtmVrUpc MONEY,@pmtmVrIncIrp MONEY,@pmtmNAutLic VARCHAR(30),@pmtmNAutInc VARCHAR(30),@pmtmNAutIrp VARCHAR(30),@pmtmIdFonPen VARCHAR(8),@pmtmIdFonSal VARCHAR(8),@pmtmIdFonRie VARCHAR(8),@pmtmIdFonCaj VARCHAR(8),@pmtmIdInstala VARCHAR(4)
,@pmtmIdDep VARCHAR(4),@pmtmIdCCosto VARCHAR(16),@pmtmIdSubCos VARCHAR(16),@pmtmIdNom VARCHAR(4),@pmtmVehiculo VARCHAR(10),@pmtmVrBasico MONEY,@pmtmDiasNov DECIMAL(10,4),@pmtmCdFonAvp VARCHAR(8),@pmtmDiasSan DECIMAL(14,4)
 AS 
 INSERT INTO tm_Aportes (tmEst,tmItem,tmIdEmpleado,tmApellido1,tmApellido2,tmNombre1,tmNombre2,tmNContto,tmDiasPen,tmDiasSal,tmDiasRie,tmDiasCom,tmBasePen,tmBaseSal,tmBaseRie,tmBaseCom,tmBaseRef,tmValorPen,tmValorSal,tmValorRie,tmValorCom,tmValorSena,tmValorIcbf,tmValorFsp,tmValorFsu,tmTarfaPen,tmTarfaSal,tmTarfaRie,tmTarfaFsp,tmTarfaFsu,tmTarfaEsa,tmTarfaMin,tmTarfaCom,tmTarfaIcb,tmTarfaSen,tmNovIng,tmNovRet,tmNovTda,tmNovTaa,tmNovTdp,tmNovTap,tmNovVsp,tmNovVte,tmNovVst,tmNovSln,tmNovIge,tmNovLma,tmNovVac,tmNovAvp,tmNovIrp,tmNovVct,tmCodNewEps,tmCodNewPen,tmCodNewRie,tmCodNewIns,tmCodNewTar,tmCodNewGru
,tmVrIncap,tmVrLicencia,tmVrVolAfi,tmVrVolApo,tmVrNoReten,tmVrUpc,tmVrIncIrp,tmNAutLic,tmNAutInc,tmNAutIrp,tmIdFonPen,tmIdFonSal,tmIdFonRie,tmIdFonCaj,tmIdInstala,tmIdDep,tmIdCCosto,tmIdSubCos,tmIdNom,tmVehiculo,tmVrBasico,tmDiasNov,tmValorESAP,tmValorMin,tmCdFonAvp,tmDiasSan)
 VALUES (@pmtmEst,@pmtmItem,@pmtmIdEmpleado,@pmtmApellido1,@pmtmApellido2,@pmtmNombre1,@pmtmNombre2,@pmtmNContto,@pmtmDiasPen,@pmtmDiasSal,@pmtmDiasRie,@pmtmDiasCom,@pmtmBasePen,@pmtmBaseSal,@pmtmBaseRie,@pmtmBaseCom,@pmtmBaseRef,@pmtmValorPen,@pmtmValorSal,@pmtmValorRie,@pmtmValorCom,@pmtmValorSena,@pmtmValorIcbf,@pmtmValorFsp,@pmtmValorFsu,@pmtmTarfaPen,@pmtmTarfaSal,@pmtmTarfaRie,@pmtmTarfaFsp,@pmtmTarfaFsu,@pmtmTarfaEsa,@pmtmTarfaMin,@pmtmTarfaCom,@pmtmTarfaIcb,@pmtmTarfaSen,@pmtmNovIng,@pmtmNovRet,@pmtmNovTda,@pmtmNovTaa,@pmtmNovTdp,@pmtmNovTap
,@pmtmNovVsp,@pmtmNovVte,@pmtmNovVst,@pmtmNovSln,@pmtmNovIge,@pmtmNovLma,@pmtmNovVac,@pmtmNovAvp,@pmtmNovIrp,@pmtmNovVct,@pmtmCodNewEps,@pmtmCodNewPen,@pmtmCodNewRie,@pmtmCodNewIns,@pmtmCodNewTar,@pmtmCodNewGru,@pmtmVrIncap,@pmtmVrLicencia,@pmtmVrVolAfi,@pmtmVrVolApo,@pmtmVrNoReten,@pmtmVrUpc,@pmtmVrIncIrp,@pmtmNAutLic,@pmtmNAutInc,@pmtmNAutIrp,@pmtmIdFonPen,@pmtmIdFonSal,@pmtmIdFonRie,@pmtmIdFonCaj,@pmtmIdInstala,@pmtmIdDep,@pmtmIdCCosto,@pmtmIdSubCos,@pmtmIdNom,@pmtmVehiculo,@pmtmVrBasico,@pmtmDiasNov,@pmtmValorESAP,@pmtmValorMin,@pmtmCdFonAvp,@pmtmDiasSan)
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE paInstm_Aportes_Sel  @pmTipoLiq VARCHAR(3),@pmNumero VARCHAR(20),@pmtmEst VARCHAR(5)
 AS 
 INSERT INTO tm_Aportes (tmEst,tmItem,tmIdEmpleado,tmApellido1,tmApellido2,tmNombre1,tmNombre2,tmNContto,tmDiasPen,tmDiasSal,tmDiasRie,tmDiasCom,tmBasePen,tmBaseSal,tmBaseRie,tmBaseCom,tmBaseRef,tmValorPen,tmValorSal,tmValorRie,tmValorCom,tmValorSena,tmValorIcbf,tmValorFsp,tmValorFsu
,tmTarfaPen,tmTarfaSal,tmTarfaRie,tmTarfaFsp,tmTarfaFsu,tmTarfaEsa,tmTarfaMin,tmTarfaCom,tmTarfaIcb,tmTarfaSen,tmVrIncap,tmVrLicencia,tmVrVolAfi,tmVrVolApo,tmVrNoReten,tmVrUpc,tmVrIncIrp,tmNAutLic,tmNAutInc,tmNAutIrp,tmIdFonPen,tmIdFonSal,tmIdFonRie,tmIdFonCaj,tmIdInstala,tmIdDep,tmIdCCosto
,tmIdSubCos,tmIdNom,tmVehiculo,tmVrBasico,tmValorESAP,tmValorMin,tmCdFonAvp,tmDiasSan)
 SELECT @pmtmEst,Item,IdEmpleado,Apellido1,Apellido2,Nombre1,Nombre2,NContrato,DiasPen,DiasSal,DiasRie,DiasCom,VrIBCPen,VrIBCSal,VrIBCRie,VrIBCCajas,VrIBCRef,VrPension,VrSalud,VrRiesgos,VrCajas,VrSena,VrICBF,VrApoFsp,VrApoFsu,TarifPen,TarifSal,TarifRie,TarifFsp,TarifFsu,TarifEsap,TarifEdu
,TarifCom,TarifIcb,TarifSen,VrIncap,VrLicencia,VrApoVolAfi,VrApoVolApt,VrNoReten,VrUpc,VrIncIrp,NumAutLic,NumAutInc,NumAutIrp,IdFonPen,IdFonSal,IdFonRie,IdCajCom,IdInstala,'0','0','0','0','',SalBasico,VrESAP,VrMinEdu,CdFonAvp,0
 FROM Trn_NomAlqDet
 WHERE TipoLiq=@pmTipoLiq AND Numero=@pmNumero
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE  paInstm_PreMovi_Pre  @pmtmEst CHAR(2),@pmFechaIni SMALLDATETIME,@pmFechaFin SMALLDATETIME,@pmIdCliente VARCHAR(16)=Null
,@pmIdAgencia VARCHAR(16)=Null,@pmIdCia CHAR(2)=Null
 AS 
 INSERT INTO tm_PreMovi (tmEst,tmTipDoc,tmNumero,tmIdCia,tmItem,tmFecha,tmIdCliente,tmIdAgencia,tmIdVend,tmCargos,tmAbonos,tmNumCuota,tmTimeSys,tmFecVence,tmCdConcepto,tmConcepto,tmDetalle,tmReferencia
,tmTipoTasa,tmDTF_EA,tmTasaNom,tmPuntos,tmTasaEfe,tmTipRef,tmDocRef,tmIdCiaRef,tmNumForma,tmCdBanco,tmBeneficiario,tmCdForma,tmEnEfectivo,tmTotalAbono,tmVrAfavor,tmTipCom,tmComprobante,tmIdCiaCom
,tmAnulado,tmNumDev,tmFecDev,tmIdUsuario,tmSanClie,tmSanClieCia,tmSanAge,tmSanAgeCia)
 SELECT @pmtmEst,C.TipDoc,C.IdPrestamo,C.IdCia,Item,P.FecPrestamo,C.IdCliente,C.IdAgencia,IdVend,VrTotal,0,NumCuota,TimeSys,FechaVence,IdConcepto,Concepto,Detalle,Causacion
,TipoTasa,DTF_EA,TasaNom,Spread_TA,TasaEfe,'CAU',NumCausacion,IdCiaCau,NumCredito,'0',TipoGarantia,'0',0,VrAbonado,0,TipCom,Comprobante,IdCiaCom
,Anulado,NumDev,FecDev,IdUsuario,0,0,0,0
 FROM Trn_FinCuotas AS C INNER JOIN Trn_FinPrestamo AS P ON C.TipDoc=P.TipDoc  AND C.IdPrestamo=P.IdPrestamo  AND C.IdCia=P.IdCia
 WHERE C.TipDoc='PR1' AND P.FecPrestamo BETWEEN @pmFechaIni AND @pmFechaFin AND C.IdCliente LIKE ISNULL(@pmIdCliente,'%')
 AND C.IdAgencia LIKE ISNULL(@pmIdAgencia,'%') AND C.IdCia LIKE ISNULL(@pmIdCia,'%%')
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE paQryCnxextna @pmIdReg INT
 AS 
 SELECT IdReg,CnxTipo,CnxEngine,CnxDso,CnxDbname,CnxAutentic,CnxUid,CnxPwd
,CnxOwner,CnxObserv,CnxPpties,CnxDriver,CnxPort,CnxTerminal
 FROM Sys_Cnxextna
 WHERE IdReg=@pmIdReg
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE paQryCnxextnaLta @pmCnxTipo VARCHAR(10)=Null
 AS 
 SELECT IdReg,CnxTipo,CnxEngine,CnxDso,CnxDbname,CnxAutentic,CnxUid,CnxOwner,CnxObserv
,CnxPpties,CnxDriver,CnxPort,CnxTerminal,CnxPwd
 FROM Sys_Cnxextna
 WHERE  CnxTipo LIKE ISNULL(@pmCnxTipo,'%')
 ORDER BY IdReg
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE paQryCnxextnaTip @pmCnxTipo VARCHAR(10)
 AS 
 SELECT IdReg,CnxTipo,CnxEngine,CnxDso,CnxDbname,CnxAutentic,CnxUid,CnxPwd,CnxOwner,CnxObserv
,CnxPpties,CnxDriver,CnxPort,CnxTerminal
 FROM Sys_Cnxextna
 WHERE  CnxTipo=@pmCnxTipo
 ORDER BY IdReg
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE paQryDiferidos @pmIdDiferido VARCHAR(30)
 AS 
 SELECT IdDiferido,NomDiferido,IdTipDif,IdGrupo,IdCCosto,IdSubCos,IdDep,IdRespons,IdCia,FecInicio,ValorTotal,VrAdiciones,VrDisminucion,NumPeriodos
,FecFinAmtza,Ajustable,Contable,VrAjuInfAcum,VrAmtzaAcuHis,VrAmtzaAcum,VrAjuInfAmtza,CuotasAmtza,VrCorrMonDifAcu,CorrMonDiferida,NumMesesDif
,FecUltAmtza,CueEnGrupo,IdCuenta,IdCueAmortiza,IdCueAjuDif,IdCueCorrDif,IdCueAjuAmtza,IdCueCorrAA,NumDoc,Referencia,DesUbicacion,IdEstado
,FecRetiro,IdCausal,ComentRetiro,Comentarios,FecCreacion,IdUsuario,TipoAmtza,NitCliente,AgenCliente,CantGalones,IdProv
,NumPlaca,CodInstala,TipoInvers
 FROM Diferidos
 WHERE IdDiferido=@pmIdDiferido
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE paQryDiferidosAmt @pmFechaIni SMALLDATETIME,@pmFechaFin SMALLDATETIME
 AS 
 SELECT IdDiferido,NomDiferido,IdTipDif,IdGrupo,IdCCosto,IdSubCos,IdDep,IdRespons,IdCia,FecInicio,ValorTotal,VrAdiciones,VrDisminucion,NumPeriodos
,FecFinAmtza,Ajustable,Contable,CorrMonDiferida,NumMesesDif,FecUltAmtza,CueEnGrupo,IdCuenta,IdCueAmortiza,IdCueAjuDif,IdCueCorrDif,IdCueAjuAmtza,IdCueCorrAA
,IdEstado,FecRetiro,TipoAmtza,NitCliente,AgenCliente,CantGalones,IdProv,NumPlaca,CodInstala,TipoInvers
 FROM Diferidos
 WHERE FecInicio<=@pmFechaFin
  AND (IdEstado<>'9999'  OR (IdEstado='9999' AND FecRetiro>=@pmFechaIni))
 ORDER BY IdDiferido
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE paQryDiferidosHis @pmIdDiferido VARCHAR(30)
 AS 
 SELECT A.IdDiferido AS CdDiferido,NomDiferido,A.IdTipDif AS CdTipDif,TipoDiferido,G.IdLinea AS CodLinea,Linea,A.IdGrupo AS CodGrupo,Grupo,A.IdCCosto AS CdCentro
,CC.CCosto AS DifCentroCosto,A.IdSubCos AS CdSubcentro,SC.SubCosto AS DifSubCentro,A.IdDep AS CdDep,DP.Dependencia AS DifDependencia,DesUbicacion
,IdRespons,T.RazonSocial AS NomResponsable,A.IdCia AS CdCia,CIA.Compania AS DifNomCia,FecInicio,A.ValorTotal AS DifValTotal,A.VrAdiciones AS DifAdiciones,A.VrDisminucion AS DifDisminucion,NumPeriodos
,FecFinAmtza,Ajustable,Contable,VrAjuInfAcum,VrAmtzaAcuHis,VrAmtzaAcum,VrAjuInfAmtza,CuotasAmtza,VrCorrMonDifAcu,CorrMonDiferida,NumMesesDif
,FecUltAmtza,CueEnGrupo,IdCuenta,IdCueAmortiza,IdCueAjuDif,IdCueCorrDif,IdCueAjuAmtza,IdCueCorrAA,NumDoc,Referencia,A.IdEstado AS CdEstado,ED.Estado AS DifEstado
,FecRetiro,A.IdCausal AS CdCausal,Causal,ComentRetiro,Comentarios,TipoInvers,FecCreacion,A.IdUsuario AS IdUsuari
--DATOS DEL DIFERIDO
,AMZ.Numero AS NumRegistro,Fecha,AMZ.Estado AS EstadoDoc,AMZ.IdUsuario AS CdUsuario,Usuario,FechaCrea,AMZ.IdCia AS CdCia,CI.Compania AS NomCia
,Item,D.ValorTotal AS ValorInicial, D.VrAdiciones AS ValAdiciones, D.VrDisminucion AS ValDisminucion, VrAcuAjustes, VrAmortizaHis, VrAmortizaMes, VrAjustesInf
, VrAjustesAmtza,VrAmtzaHisAcum, VrAmortizaAcum, VrAjuAmtzaAcum, VrCorrMonAcum, VrBaseCorrMon, VrCorrMonDif, TarifaAju
,D.IdCia AS DetIdCia,CID.Compania AS DetNomCia,CdCCosto,CCD.CCosto AS NomCentroCosto,CdSubCos,SCD.SubCosto AS NomSubcentro, CdDep, DPD.Dependencia AS NomDepend
,NitRespons,TRD.RazonSocial AS DetResponsable,FecInicioAmtza, NPeriodos,TipoAmtza,NitCliente,AgenCliente,CantGalones
,IdProv,PV.RazonSocial AS NomProveedor,NumPlaca,CodInstala,Instlacion
 FROM Diferidos  AS A INNER JOIN AfGrupos AS G ON A.IdGrupo=G.IdGrupo INNER JOIN AfLineas AS L ON G.IdLinea=L.IdLinea
 INNER JOIN CentroCosto AS CC ON A.IdCCosto=CC.IdCCosto INNER JOIN AfTiposDif AS TD ON A.IdTipDif=TD.IdTipDif
 INNER JOIN Dependencias AS DP ON A.IdDep=DP.IdDep INNER JOIN Terceros AS T ON A.IdRespons=T.IdTercero
 INNER JOIN Companias AS CIA ON A.IdCia=CIA.IdCia  INNER JOIN Terceros AS PV ON A.IdProv=PV.IdTercero
 INNER JOIN AfEstados AS ED ON A.IdEstado=ED.IdEstado  
 LEFT JOIN SubCentros AS SC ON A.IdSubCos=SC.IdSubCos LEFT JOIN AfCausales AS CA ON A.IdCausal=CA.IdCausal
 LEFT JOIN Trn_AfAmtDetalle AS D ON A.IdDiferido=D.IdDiferido LEFT  JOIN Trn_AfAmortiza AS AMZ  ON D.Numero=AMZ.Numero
 LEFT JOIN Companias AS CI ON AMZ.IdCia=CI.IdCia  LEFT JOIN adm_Usuarios AS U ON AMZ.IdUsuario=U.IdUsuario
 LEFT JOIN Companias AS CID ON D.IdCia=CID.IdCia 
 LEFT JOIN CentroCosto AS CCD ON D.CdCCosto=CCD.IdCCosto 
 LEFT JOIN Dependencias AS DPD ON D.CdDep=DPD.IdDep LEFT JOIN Terceros AS TRD ON D.NitRespons=TRD.IdTercero
 LEFT JOIN SubCentros AS SCD ON D.CdSubCos=SCD.IdSubCos  LEFT JOIN Instalaciones AS I ON A.CodInstala=I.IdInstala
 WHERE A.IdDiferido LIKE ISNULL(@pmIdDiferido ,'%')
 ORDER BY NomDiferido
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE paQryDiferidosLta @pmIdGrupo VARCHAR(4)=Null,@pmIdLinea VARCHAR(4)=Null,@pmIdCCosto VARCHAR(16)=Null,@pmIdTipDif VARCHAR(4)=Null
,@pmIdDep VARCHAR(4)=Null,@pmIdRespons VARCHAR(16)=Null,@pmIdCia CHAR(2)=Null,@pmFechaIni SMALLDATETIME=Null,@pmFechaFin SMALLDATETIME=Null
,@pmIdEstado VARCHAR(4)=Null
 AS 
 SELECT IdDiferido,NomDiferido,A.IdTipDif AS CdTipDif,TipoDiferido,G.IdLinea AS CodLinea,Linea,A.IdGrupo AS CodGrupo,Grupo,A.IdCCosto AS CdCentro,CCosto,A.IdSubCos AS CdSubcentro,SubCosto
,A.IdDep AS CdDep,Dependencia,DesUbicacion,IdRespons,T.RazonSocial  AS NomResponsable,A.IdCia AS CdCia,Compania,FecInicio,ValorTotal,VrAdiciones,VrDisminucion,NumPeriodos
,FecFinAmtza,Ajustable,Contable,VrAjuInfAcum,VrAmtzaAcuHis,VrAmtzaAcum,VrAjuInfAmtza,CuotasAmtza,VrCorrMonDifAcu,CorrMonDiferida,NumMesesDif
,FecUltAmtza,CueEnGrupo,IdCuenta,IdCueAmortiza,IdCueAjuDif,IdCueCorrDif,IdCueAjuAmtza,IdCueCorrAA,NumPlaca,NumDoc,Referencia,A.IdEstado AS CdEstado,Estado
,FecRetiro,A.IdCausal AS CdCausal,Causal,ComentRetiro,Comentarios,FecCreacion,A.IdUsuario AS IdUsuari,Usuario,TipoAmtza,NitCliente,AgenCliente,CantGalones
,IdProv,PV.RazonSocial AS NomProveedor,CodInstala,Instlacion,TipoInvers
--datos del responsable
,T.Codigo AS TercCodigo,T.TipoId AS TercTipoId,T.Dv AS TercDv,T.NomCial AS TercNomCial,T.SiglaRaz AS TercSigla,T.Direccion AS TercDirecc,T.IdLocal AS CdLocalidad,Localidad
,LC.IdDep AS Cd_Depto,Departamento,T.Telefono AS TercTelefono,T.e_mail AS TercEmail
 FROM Diferidos  AS A INNER JOIN AfGrupos AS G ON A.IdGrupo=G.IdGrupo INNER JOIN AfLineas AS L ON G.IdLinea=L.IdLinea
 INNER JOIN CentroCosto AS CC ON A.IdCCosto=CC.IdCCosto INNER JOIN AfTiposDif AS TD ON A.IdTipDif=TD.IdTipDif
 INNER JOIN Dependencias AS DP ON A.IdDep=DP.IdDep INNER JOIN Terceros AS T ON A.IdRespons=T.IdTercero
 INNER JOIN Companias AS CI ON A.IdCia=CI.IdCia INNER JOIN Terceros AS PV ON A.IdProv=PV.IdTercero
 INNER JOIN AfEstados AS ED ON A.IdEstado=ED.IdEstado  INNER JOIN adm_Usuarios AS U ON A.IdUsuario=U.IdUsuario
 INNER JOIN Localidades AS LC ON T.IdLocal=LC.IdLocal INNER JOIN Departamentos AS D ON LC.IdDep=D.IdDep
 LEFT JOIN SubCentros AS SC ON A.IdSubCos=SC.IdSubCos LEFT JOIN AfCausales AS CA ON A.IdCausal=CA.IdCausal
 LEFT JOIN Instalaciones AS I ON A.CodInstala=I.IdInstala
 WHERE A.IdGrupo LIKE ISNULL(@pmIdGrupo,'%') AND G.IdLinea LIKE ISNULL(@pmIdLinea,'%')
 AND A.IdCCosto LIKE ISNULL(@pmIdCCosto,'%') AND  A.IdTipDif LIKE ISNULL(@pmIdTipDif,'%')
 AND A.IdDep LIKE ISNULL(@pmIdDep,'%') AND IdRespons LIKE ISNULL(@pmIdRespons,'%')
 AND  A.IdCia LIKE ISNULL(@pmIdCia,'%%')
 AND (FecInicio>=ISNULL(@pmFechaIni,CAST('19100101' AS SMALLDATETIME)) AND FecInicio<=ISNULL(@pmFechaFin,CAST('20781230' AS SMALLDATETIME)))
 AND A.IdEstado LIKE ISNULL(@pmIdEstado,'%')
 ORDER BY NomDiferido
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE paQryFinFacturas @pmTipDoc VARCHAR(3),@pmIdPrestamo INT,@pmIdCia CHAR(2)
 AS 
 SELECT TipDoc,IdPrestamo,IdCia,Item,NumFactura,Fecha,FechaVence,VrFactura,VrInteres,DiasLiq,TasaEfeMes,TasaEfeDia,FechaPago
,FechaLiq,FecInicio,FecFinal,VrAbono,ItemPago,Facturado,TipFac,FacturaInt,CdCiaFac,FecFactura,DiasPlazo
 FROM Trn_FinFacturas
 WHERE TipDoc=@pmTipDoc AND IdPrestamo=@pmIdPrestamo 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 paQryFinFacturasInt  @pmFechaIni SMALLDATETIME,@pmFechaFin SMALLDATETIME, @pmIdCia CHAR(2)=Null,@pmIdCliente VARCHAR(16)=Null
 AS 
 SELECT P.TipDoc AS TipPrest,P.IdPrestamo AS NumPrest,P.IdCia AS CdCia,Compania,Item,NumFactura,F.Fecha AS FecCausac,FechaVence,VrFactura,VrInteres,DiasLiq,TasaEfeMes,TasaEfeDia,FechaPago
,FechaLiq,FecInicio,FecFinal,VrAbono,ItemPago,Facturado,TipFac,FacturaInt,CdCiaFac,FecFactura,DiasPlazo
,T.RazonSocial AS NomCliente,IdCliente,NomAgencia AS Agencia,P.IdAgencia AS CdAgencia,NumCredito,P.Fecha AS FecRadica,FecPrestamo,FecCorte
,VrPrestamo,VrSeguro,VrCuota,NPlazos,TipoPlazo,Causacion,P.IdLinea AS CdLinea,LinCredito,TipoCredito,TipoCausac,P.IdTasa AS CdTasa,DescTasa
,AcumCapital,AcumIntereses,VrPrestamo-AcumCapital AS SaldoCapital,AcumCuotas,PagosCapital,PagosIntereses,PagosIntMora,CuotasPagadas
,P.IdVend AS CdVend,VN.RazonSocial AS NomAsesor,Refinanciado,NumPrestRef,CiaPrestRef,VrRefinanc,DescRefinanc,P.IdConcepto AS CdConcepto,Concepto,P.Observacion AS Observ
,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.e_mail AS TercEmail
,T.IdSector AS CdSector,SectorEco,T.IdRegimen AS CdRegimen,Regimen,T.TipEnte AS TercTipEnte
,CLI.IdSzona AS CdSubzona,Subzona,SZ.IdZona AS CdZona,Zona,CLI.IdGrupo AS CdGrupoCli,GrupoClie
,CLI.IdTipoTerc AS CdTipoTerc,TipoTercero,EsExento,FecIngreso,VrSalBasico,DirAgencia,CLI.IdLocalAge AS AgeIdCiudad
,LA.Localidad AS AgeCiudad,LA.IdDep AS AgeCodDep,DA.Departamento AS AgeDpto
 FROM  Trn_FinPrestamo AS P INNER JOIN Trn_FinFacturas AS F ON P.TipDoc=F.TipDoc AND P.IdPrestamo=F.IdPrestamo AND P.IdCia=F.IdCia
 INNER JOIN TasasInteres AS TI ON P.IdTasa=TI.IdTasa INNER JOIN Conceptos AS C ON P.IdConcepto=C.IdConcepto
 INNER JOIN Terceros AS T ON P.IdCliente=T.IdTercero   INNER JOIN Terceros AS VN ON P.IdVend=VN.IdTercero
 INNER JOIN Localidades AS L ON T.IdLocal=L.IdLocal  INNER JOIN Departamentos AS D ON L.IdDep=D.IdDep 
 INNER JOIN LineasCred AS LC ON P.IdLinea=LC.IdLinea  INNER JOIN Companias AS CI ON P.IdCia=CI.IdCia
 INNER JOIN TercCliePrestamo AS CLI ON P.IdCliente=CLI.IdClie AND P.IdAgencia=CLI.IdAgencia
 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 TiposTerc AS TTC ON CLI.IdTipoTerc=TTC.IdTipoTerc
 LEFT JOIN Localidades AS LA ON  CLI.IdLocalAge=LA.IdLocal  LEFT JOIN Departamentos AS DA ON LA.IdDep=DA.IdDep 
 WHERE F.FechaLiq BETWEEN @pmFechaIni AND @pmFechaFin AND P.TipoCredito='FACTURAS'  AND P.Anulado=0
 AND P.IdCia LIKE ISNULL(@pmIdCia,'%%')  AND P.IdCliente LIKE ISNULL(@pmIdCliente,'%')
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE PROCEDURE paQryFinPrestamo @pmTipDoc VARCHAR(3),@pmIdPrestamo INT,@pmIdCia CHAR(2)
 AS 
 SELECT TipDoc,IdPrestamo,IdCia,Fecha,FecPrestamo,FecCorte,IdConcepto,IdCliente,IdAgencia,VrPrestamo,IdTasa,NPlazos,Causacion,IdMora,NContrato,CdCiaCon,NActaJunta,NumCredito
,IdVend,pVehiculo,CdConductor,TipoGarantia,VrGarantia,DescGarantia,CxPagar,NumCuotaIni,AcumCapital,AcumIntereses,AcumIntMora,AcumCuotas,PagosCapital,PagosIntereses,PagosIntMora
,CuotasPagadas,CuotasMora,TipoAprob,TipoCausac,OrigenAdd,TipCom,Comprobante,IdCiaCom,Anulado,NumDev,FecDev,Observacion,IdEstado,DiaCausac ,DiaCausac2,DiasVence,IdLinea,TipoPlazo
,CdTasa2,VrSeguro,VrIntereses,VrNeto,VrCuota,TipoCredito,CalcIntMora,NitEmpConv,CuotasConv,Refinanciado,NumPrestRef,CiaPrestRef,VrRefinanc,DescRefinanc,CdAlmacen,CauAnticipado,TimeSys,FecUpdate,IdCiaCrea,IdUsuario
 FROM Trn_FinPrestamo
 WHERE TipDoc=@pmTipDoc AND IdPrestamo=@pmIdPrestamo 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 paQryFinPrestamoFac @pmTipDoc VARCHAR(3), @pmIdPrestamoIni INT,@pmIdPrestamoFin INT,@pmIdCia CHAR(2)
 AS 
 SELECT P.IdPrestamo AS NumPtmo,P.IdCia AS CdCia,Compania,P.Fecha AS FechaPtmo,FecPrestamo,FecCorte,P.IdConcepto AS CdConcepto,C.Concepto AS DescConcept,P.IdCliente AS NitCliente
,T.RazonSocial AS NomCliente,P.IdAgencia AS CdAgencia,NomAgencia AS Agencia,VrPrestamo,P.IdTasa AS CdTasa,DescTasa,TI.TipoInteres AS Tipo_Int,TasaEfectiva,NPlazos,TipoPlazo,P.Causacion AS TipoCausac,P.IdMora AS CdMora,DescMora
,TIM.TipoInteres AS Tipo_IntMora,TasaEM,P.NContrato AS NumContrato,CdCiaCon,TipoAprob,NActaJunta,NumCredito
,P.IdVend AS CdVend,VN.RazonSocial AS NomVendedor,pVehiculo,CdConductor,TipoGarantia,VrGarantia,DescGarantia,CxPagar,CdTasa2,VrSeguro,VrIntereses,VrNeto,P.VrCuota AS ValCuota,NumCuotaIni,AcumCapital,AcumIntereses,AcumIntMora,AcumCuotas
,PagosCapital,PagosIntereses,PagosIntMora,CuotasPagadas,CuotasMora,TipCom,TipoCom,Comprobante,IdCiaCom,Anulado,NumDev,FecDev,P.Observacion AS Observ,DiaCausac ,DiaCausac2,DiasVence,P.IdLinea AS CdLinea,LinCredito
,TipoCredito,P.CalcIntMora AS CaclMora,NitEmpConv,CuotasConv,Refinanciado,NumPrestRef,CiaPrestRef,VrRefinanc,DescRefinanc,CdAlmacen,CauAnticipado
,P.IdEstado AS CdEstado,Estado,OrigenAdd,TimeSys,FecUpdate,IdCiaCrea,P.IdUsuario AS Cdusuario,Usuario
--Datos de facturas
,Item,NumFactura,DF.Fecha AS FechaFact,FechaVence,VrFactura,VrInteres,DiasLiq,TasaEfeMes,TasaEfeDia,FechaPago,FechaLiq,FecInicio,FecFinal,VrAbono,ItemPago,Facturado,TipFac,FacturaInt,CdCiaFac,FecFactura,DiasPlazo
--Datos del 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.IdSector AS CdSector,SectorEco,T.IdRegimen AS CdRegimen,Regimen,T.TipEnte AS TercTipEnte
,CLI.IdSzona AS CdSubzona,Subzona,SZ.IdZona AS CdZona,Zona,CLI.IdGrupo AS CdGrupoCli,GrupoClie,CLI.IdTipoTerc AS CdTipoTerc,TipoTercero,EsExento,FecIngreso,VrSalBasico,DirAgencia
,CLI.IdLocalAge AS AgeIdCiudad,LA.Localidad AS AgeCiudad,LA.IdDep AS AgeCodDep,DA.Departamento AS AgeDpto,Comentarios
 FROM Trn_FinPrestamo AS P  INNER JOIN Companias AS CI ON P.IdCia=CI.IdCia
 INNER JOIN Conceptos AS C ON P.IdConcepto=C.IdConcepto  INNER JOIN adm_Usuarios AS U ON P.IdUsuario=U.IdUsuario
 INNER JOIN EstadoDoc AS ED ON P.IdEstado=ED.IdEstado
 INNER JOIN Terceros AS T ON P.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 Terceros AS VN ON P.IdVend=VN.IdTercero INNER JOIN TasasInteres AS TI ON P.IdTasa=TI.IdTasa
 INNER JOIN  TercCliePrestamo AS CLI ON P.IdCliente=CLI.IdClie AND P.IdAgencia=CLI.IdAgencia
 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 TiposTerc AS TTC ON CLI.IdTipoTerc=TTC.IdTipoTerc
 INNER JOIN Trn_FinFacturas AS DF ON P.TipDoc=DF.TipDoc AND P.IdPrestamo=DF.IdPrestamo AND P.IdCia=DF.IdCia
 LEFT JOIN LineasCred AS LC ON P.IdLinea=LC.IdLinea
 LEFT JOIN TasasIntmora AS TIM ON P.IdMora=TIM.IdMora LEFT JOIN TiposCom AS TC ON P.TipCom=TC.IdCom
 LEFT JOIN Localidades AS LA ON CLI.IdLocalAge=LA.IdLocal  LEFT JOIN Departamentos AS DA ON LA.IdDep=DA.IdDep 
 WHERE P.TipDoc=@pmTipDoc  AND P.IdPrestamo BETWEEN @pmIdPrestamoIni AND @pmIdPrestamoFin  AND P.IdCia=@pmIdCia
 ORDER BY  P.IdPrestamo
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE paQryFinPrestamoFmt @pmTipDoc VARCHAR(3), @pmIdPrestamoIni INT,@pmIdPrestamoFin INT,@pmIdCia CHAR(2)
 AS 
 SELECT P.IdPrestamo AS NumPtmo,P.IdCia AS CdCia,Compania,P.Fecha AS FechaPtmo,FecPrestamo,FecCorte,P.IdConcepto AS CdConcepto,C.Concepto AS DescConcept,P.IdCliente AS NitCliente
,T.RazonSocial AS NomCliente,P.IdAgencia AS CdAgencia,NomAgencia AS Agencia,VrPrestamo,P.IdTasa AS CdTasa,DescTasa,TI.TipoInteres AS Tipo_Int,TasaEfectiva,NPlazos,TipoPlazo,P.Causacion AS TipoCausac,P.IdMora AS CdMora,DescMora
,TIM.TipoInteres AS Tipo_IntMora,TasaEM,P.NContrato AS NumContrato,CdCiaCon,TipoAprob,NActaJunta,NumCredito
,P.IdVend AS CdVend,VN.RazonSocial AS NomVendedor,pVehiculo,CdConductor,TipoGarantia,VrGarantia,DescGarantia,CxPagar,CdTasa2,VrSeguro,VrIntereses,VrNeto,P.VrCuota AS ValCuota,NumCuotaIni,AcumCapital,AcumIntereses,AcumIntMora,AcumCuotas
,PagosCapital,PagosIntereses,PagosIntMora,CuotasPagadas,CuotasMora,TipCom,TipoCom,Comprobante,IdCiaCom,Anulado,NumDev,FecDev,P.Observacion AS Observ,DiaCausac ,DiaCausac2,DiasVence,P.IdLinea AS CdLinea,LinCredito
,TipoCredito,P.CalcIntMora AS CaclMora,NitEmpConv,CuotasConv,Refinanciado,NumPrestRef,CiaPrestRef,VrRefinanc,DescRefinanc,CdAlmacen,CauAnticipado
,P.IdEstado AS CdEstado,Estado,OrigenAdd,TimeSys,FecUpdate,IdCiaCrea,P.IdUsuario AS Cdusuario,Usuario
--Datos del detalle
,Item,FecCausac,FecVence,NumDias,PC.VrCuota AS TotalCuota,VrCapital,VrInteres,VrSaldo,TasaEfeMes,TasaEfeDia,PC.Causacion AS NumCausac,CdCiaCausac,VrCuoCausac,VrIntCausac,VrSaldoCausac
--Datos del 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.IdSector AS CdSector,SectorEco,T.IdRegimen AS CdRegimen,Regimen,T.TipEnte AS TercTipEnte
,CLI.IdSzona AS CdSubzona,Subzona,SZ.IdZona AS CdZona,Zona,CLI.IdGrupo AS CdGrupoCli,GrupoClie,CLI.IdTipoTerc AS CdTipoTerc,TipoTercero,EsExento,FecIngreso,VrSalBasico,DirAgencia
,CLI.IdLocalAge AS AgeIdCiudad,LA.Localidad AS AgeCiudad,LA.IdDep AS AgeCodDep,DA.Departamento AS AgeDpto,Comentarios
 FROM Trn_FinPrestamo AS P  INNER JOIN Companias AS CI ON P.IdCia=CI.IdCia
 INNER JOIN Conceptos AS C ON P.IdConcepto=C.IdConcepto  INNER JOIN adm_Usuarios AS U ON P.IdUsuario=U.IdUsuario
 INNER JOIN EstadoDoc AS ED ON P.IdEstado=ED.IdEstado
 INNER JOIN Terceros AS T ON P.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 Terceros AS VN ON P.IdVend=VN.IdTercero INNER JOIN TasasInteres AS TI ON P.IdTasa=TI.IdTasa
 INNER JOIN  TercCliePrestamo AS CLI ON P.IdCliente=CLI.IdClie AND P.IdAgencia=CLI.IdAgencia
 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 TiposTerc AS TTC ON CLI.IdTipoTerc=TTC.IdTipoTerc
 LEFT JOIN LineasCred AS LC ON P.IdLinea=LC.IdLinea
 LEFT JOIN TasasIntmora AS TIM ON P.IdMora=TIM.IdMora LEFT JOIN TiposCom AS TC ON P.TipCom=TC.IdCom
 LEFT JOIN Localidades AS LA ON CLI.IdLocalAge=LA.IdLocal  LEFT JOIN Departamentos AS DA ON LA.IdDep=DA.IdDep 
 LEFT JOIN Trn_FinAmtza AS PC ON P.TipDoc=PC.TipDoc AND P.IdPrestamo=PC.IdPrestamo AND P.IdCia=PC.IdCia
 WHERE P.TipDoc=@pmTipDoc  AND P.IdPrestamo BETWEEN @pmIdPrestamoIni AND @pmIdPrestamoFin  AND P.IdCia=@pmIdCia
 ORDER BY  P.IdPrestamo
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE paQryFinPrestamoCr @pmTipDoc VARCHAR(3), @pmFechaIni SMALLDATETIME,@pmFechaFin SMALLDATETIME,@pmIdCia CHAR(2)=Null
,@pmIdConcepto VARCHAR(4)=Null,@pmIdCliente VARCHAR(16)=Null,@pmIdVend VARCHAR(16)=Null,@pmIdTasa VARCHAR(4)=Null,@pmIdEstado VARCHAR(4)=Null
 AS 
 SELECT IdPrestamo,P.IdCia AS CdCia,Compania,Fecha,FecPrestamo,FecCorte,P.IdConcepto AS CdConcepto,Concepto,IdCliente,T.RazonSocial AS NomCliente,P.IdAgencia AS CdAgencia,NomAgencia AS Agencia
,VrPrestamo,VrSeguro,VrIntereses,VrNeto,VrCuota,P.IdTasa AS CdTasa,DescTasa,TI.TipoInteres AS Tipo_Int,TasaEfectiva,TipoPeriodo,CausaInteres,DTF_EA,Spread_TA,OpcCompra,NPlazos,Causacion
,P.IdMora AS CdMora,DescMora,TIM.TipoInteres AS TipoIntMora,TasaEA,TasaEM,TIM.DiasGracia AS Dias_Gracia,P.NContrato AS NumContrato,CdCiaCon,TipoAprob,NActaJunta,NumCredito,P.IdVend AS CdVend,VN.RazonSocial AS NomVendedor
,pVehiculo,CdConductor,CD.RazonSocial AS NomConductor,TipoGarantia,VrGarantia,DescGarantia,CxPagar,NumCuotaIni,AcumCapital,AcumIntereses,AcumIntMora,AcumCuotas
,PagosCapital,PagosIntereses,PagosIntMora,CuotasPagadas,CuotasMora,TipCom,TipoCom,Comprobante,IdCiaCom,Anulado,NumDev,FecDev,P.Observacion AS Observ,DiaCausac ,DiaCausac2,DiasVence,P.IdLinea AS CdLinea,LinCredito,TipoPlazo,CdTasa2
,TipoCredito,P.CalcIntMora AS CaclMora,NitEmpConv,CuotasConv,Refinanciado,NumPrestRef,CiaPrestRef,VrRefinanc,DescRefinanc,CdAlmacen,CauAnticipado,P.IdEstado AS CdEstado,Estado,OrigenAdd,TimeSys,FecUpdate,IdCiaCrea,P.IdUsuario AS Cdusuario,Usuario,TipDoc
--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.IdSector AS CdSector,SectorEco,T.IdRegimen AS CdRegimen,Regimen,T.TipEnte AS TercTipEnte
,CLI.IdSzona AS CdSubzona,Subzona,SZ.IdZona AS CdZona,Zona,CLI.IdGrupo AS CdGrupoCli,GrupoClie
,CLI.IdTipoTerc AS CdTipoTerc,TipoTercero,EsExento,FecIngreso,VrSalBasico,DirAgencia,CLI.IdLocalAge AS AgeIdCiudad,LA.Localidad AS AgeCiudad
,LA.IdDep AS AgeCodDep,DA.Departamento AS AgeDpto,Comentarios
 FROM Trn_FinPrestamo AS P  INNER JOIN Companias AS CI ON P.IdCia=CI.IdCia
 INNER JOIN Conceptos AS C ON P.IdConcepto=C.IdConcepto  INNER JOIN adm_Usuarios AS U ON P.IdUsuario=U.IdUsuario
 INNER JOIN EstadoDoc AS ED ON P.IdEstado=ED.IdEstado  INNER JOIN TasasInteres AS TI ON P.IdTasa=TI.IdTasa
 INNER JOIN Terceros AS T ON P.IdCliente=T.IdTercero   INNER JOIN Terceros AS VN ON P.IdVend=VN.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  TercCliePrestamo AS CLI ON P.IdCliente=CLI.IdClie AND P.IdAgencia=CLI.IdAgencia
 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 TiposTerc AS TTC ON CLI.IdTipoTerc=TTC.IdTipoTerc
 LEFT JOIN TasasIntmora AS TIM ON P.IdMora=TIM.IdMora LEFT JOIN TiposCom AS TC ON P.TipCom=TC.IdCom
 LEFT JOIN Localidades AS LA ON  CLI.IdLocalAge=LA.IdLocal  LEFT JOIN Departamentos AS DA ON LA.IdDep=DA.IdDep 
 LEFT JOIN Terceros AS CD ON P.CdConductor=CD.IdTercero  
 LEFT JOIN LineasCred AS LC ON P.IdLinea=LC.IdLinea
 WHERE TipDoc=@pmTipDoc  AND  Fecha BETWEEN @pmFechaIni AND @pmFechaFin  AND P.IdCia LIKE ISNULL(@pmIdCia,'%%')
 AND P.IdConcepto LIKE ISNULL(@pmIdConcepto,'%')  AND P.IdCliente LIKE ISNULL(@pmIdCliente,'%')
 AND P.IdVend LIKE ISNULL(@pmIdVend,'%') AND P.IdTasa LIKE ISNULL(@pmIdTasa,'%') AND P.IdEstado LIKE ISNULL(@pmIdEstado,'%')
 ORDER BY P.IdCia,IdPrestamo
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE paQryFinPrestamoLta @pmTipDoc VARCHAR(3), @pmFechaIni SMALLDATETIME,@pmFechaFin SMALLDATETIME,@pmIdCia CHAR(2)=Null
,@pmIdConcepto VARCHAR(4)=Null,@pmIdCliente VARCHAR(16)=Null,@pmIdVend VARCHAR(16)=Null,@pmIdTasa VARCHAR(4)=Null,@pmIdEstado VARCHAR(4)=Null
 AS 
 SELECT IdPrestamo,P.IdCia AS CdCia,Compania,Fecha,FecPrestamo,FecCorte,DiaCausac ,DiaCausac2,DiasVence,P.IdConcepto AS CdConcepto,Concepto,IdCliente,T.RazonSocial AS NomCliente,IdAgencia,VrPrestamo,P.IdTasa AS CdTasa,DescTasa
,NPlazos,TipoPlazo,Causacion,IdLinea,IdMora,VrSeguro,VrIntereses,VrNeto,VrCuota,NContrato,CdCiaCon,TipoAprob,TipoCausac,NActaJunta,NumCredito,IdVend,VN.RazonSocial AS NomVendedor,pVehiculo,CdConductor,TipoGarantia,VrGarantia,DescGarantia
,CxPagar,NumCuotaIni,AcumCapital,AcumIntereses,AcumIntMora,AcumCuotas,PagosCapital,PagosIntereses,PagosIntMora
,CuotasPagadas,CuotasMora,CdTasa2,TipoCredito,CalcIntMora,NitEmpConv,CuotasConv,Refinanciado,NumPrestRef,CiaPrestRef,VrRefinanc,DescRefinanc,CdAlmacen,CauAnticipado,TipCom,Comprobante,IdCiaCom,Anulado,NumDev,FecDev
,P.Observacion AS Observ,P.IdEstado AS CdEstado,Estado,OrigenAdd,TimeSys,FecUpdate,IdCiaCrea,P.IdUsuario AS Cdusuario,Usuario
 FROM Trn_FinPrestamo AS P  INNER JOIN Companias AS CI ON P.IdCia=CI.IdCia
 INNER JOIN Conceptos AS C ON P.IdConcepto=C.IdConcepto  INNER JOIN adm_Usuarios AS U ON P.IdUsuario=U.IdUsuario
 INNER JOIN EstadoDoc AS ED ON P.IdEstado=ED.IdEstado
 INNER JOIN Terceros AS T ON P.IdCliente=T.IdTercero INNER JOIN Terceros AS VN ON P.IdVend=VN.IdTercero
 INNER JOIN TasasInteres AS TI ON P.IdTasa=TI.IdTasa
 WHERE TipDoc=@pmTipDoc  AND  Fecha BETWEEN @pmFechaIni AND @pmFechaFin  AND P.IdCia LIKE ISNULL(@pmIdCia,'%%')
 AND P.IdConcepto LIKE ISNULL(@pmIdConcepto,'%')  AND P.IdCliente LIKE ISNULL(@pmIdCliente,'%')
 AND P.IdVend LIKE ISNULL(@pmIdVend,'%') AND P.IdTasa LIKE ISNULL(@pmIdTasa,'%') AND P.IdEstado LIKE ISNULL(@pmIdEstado,'%')
 ORDER BY P.IdCia,IdPrestamo
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE PROCEDURE paQryFinPrestamoRes @pmSaldo DECIMAL(12,2),@pmTipDoc VARCHAR(3),@pmIdCia CHAR(2)=Null,@pmIdConcepto VARCHAR(4)=Null,@pmIdCliente VARCHAR(16)=Null
,@pmIdVend VARCHAR(16)=Null,@pmIdTasa VARCHAR(4)=Null,@pmIdEstado VARCHAR(4)=Null
 AS 
 SELECT T.RazonSocial AS NomCliente,IdCliente,NomAgencia AS Agencia,P.IdAgencia AS CdAgencia,P.IdPrestamo AS NumPrest,P.IdCia AS CdCia,Compania
,NumCredito,P.Fecha AS FecRadica,FecPrestamo,DiaCausac,DiaCausac2,DiasVence,TipoAprob,NActaJunta,VrPrestamo,VrSeguro,VrIntereses,VrNeto,VrCuota,NPlazos,TipoPlazo,Causacion,P.IdLinea AS CdLinea,LinCredito,P.IdTasa AS CdTasa,DescTasa
,tmVrCuota AS VrCuotaDTF,tmDescCuota AS DescripCuota,tmVrMora AS VrEnMora,AcumCapital,AcumIntereses,VrPrestamo-AcumCapital AS SaldoCapital,tmDiasMora AS PromDiasMora
,CausaMora,GestionClie,AcuerdosClie,TipoGarantia,DescGarantia,VrGarantia,NumCuotaIni,AcumCuotas,CuotasMora
,PagosCapital,PagosIntereses,PagosIntMora,CuotasPagadas,CdTasa2,TipoCredito,P.CalcIntMora AS CalcMora,NitEmpConv,CuotasConv,P.IdVend AS CdVend,VN.RazonSocial AS NomAsesor,P.NContrato AS NumContrato,CdCiaCon,P.Observacion AS Observ
,Refinanciado,NumPrestRef,CiaPrestRef,VrRefinanc,DescRefinanc,P.IdConcepto AS CdConcepto,Concepto,CauAnticipado,P.IdEstado AS CdEstado,Estado
,TI.TipoInteres AS Tipo_Int,TasaEfectiva,TipoPeriodo,CausaInteres,DTF_EA,Spread_TA,OpcCompra
--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.IdSector AS CdSector,SectorEco,T.IdRegimen AS CdRegimen,Regimen,T.TipEnte AS TercTipEnte
,CLI.IdSzona AS CdSubzona,Subzona,SZ.IdZona AS CdZona,Zona,CLI.IdGrupo AS CdGrupoCli,GrupoClie
,CLI.IdTipoTerc AS CdTipoTerc,TipoTercero,EsExento,FecIngreso,VrSalBasico,DirAgencia,CLI.IdLocalAge AS AgeIdCiudad
,LA.Localidad AS AgeCiudad,LA.IdDep AS AgeCodDep,DA.Departamento AS AgeDpto
 FROM Trn_FinPrestamo AS P  INNER JOIN Companias AS CI ON P.IdCia=CI.IdCia
 INNER JOIN Conceptos AS C ON P.IdConcepto=C.IdConcepto   INNER JOIN EstadoDoc AS ED ON P.IdEstado=ED.IdEstado 
 INNER JOIN TasasInteres AS TI ON P.IdTasa=TI.IdTasa
 INNER JOIN Terceros AS T ON P.IdCliente=T.IdTercero   INNER JOIN Terceros AS VN ON P.IdVend=VN.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 TercCliePrestamo AS CLI ON P.IdCliente=CLI.IdClie AND P.IdAgencia=CLI.IdAgencia
 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 TiposTerc AS TTC ON CLI.IdTipoTerc=TTC.IdTipoTerc
 LEFT JOIN Localidades AS LA ON  CLI.IdLocalAge=LA.IdLocal  LEFT JOIN Departamentos AS DA ON LA.IdDep=DA.IdDep 
 LEFT JOIN Trn_FinObserv AS PD ON P.TipDoc=PD.TipDoc AND P.IdPrestamo=PD.IdPrestamo AND P.IdCia=PD.IdCia
 LEFT JOIN tm_PreMora AS PM ON P.TipDoc=PM.tmTipDoc AND P.IdPrestamo=PM.tmIdPrestamo AND P.IdCia=PM.tmIdCia
 LEFT JOIN LineasCred AS LC ON P.IdLinea=LC.IdLinea
 WHERE P.TipDoc=@pmTipDoc  AND P.Anulado=0   AND (VrPrestamo-AcumCapital)>=@pmSaldo
 AND P.IdCia LIKE ISNULL(@pmIdCia,'%%')  AND P.IdConcepto LIKE ISNULL(@pmIdConcepto,'%')
  AND P.IdCliente LIKE ISNULL(@pmIdCliente,'%')  AND P.IdVend LIKE ISNULL(@pmIdVend,'%') 
 AND P.IdTasa LIKE ISNULL(@pmIdTasa,'%') AND P.IdEstado LIKE ISNULL(@pmIdEstado,'%')
 ORDER BY P.IdCia,P.IdPrestamo
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE paQryNomAlqNov @pmTipoLiq VARCHAR(3),@pmNumero VARCHAR(20),@pmItem INT
 AS 
 SELECT TipoLiq,Numero,Item,IdEmpleado,NovIng,NovRet,NovTde,NovTae,NovTdp,NovTap,NovVsp,NovVte,NovVst,NovSln,NovIge,NovLma,NovVac,NovAvp
,NovVct,NovIrp,CodNewPen,CodNewSal,CodNewIns,CodNewRie,CodNewGru,DiasNov,DiasSan
 FROM Trn_NomAlqNov
 WHERE TipoLiq=@pmTipoLiq AND Numero=@pmNumero 
 AND (Item>=ISNULL(@pmItem,0)  AND Item<=ISNULL(@pmItem,2147483647))
 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 paQryNomAutoliq_Cr @pmTipoLiq VARCHAR(3),@pmNumero VARCHAR(20),@pmPerAutoliq VARCHAR(6)=Null
AS
 SELECT  A.TipoLiq AS TipoPla,A.Numero AS NumPla,Fecha,PerAutoliq,PerAutoSal,FechaPago,A.IdAportnte AS NitAportnte,NA.RazonSocial AS NomAportante,Presentacion,A.CodCia AS CodSucur,Compania,CodSucsal,DireccSuc,TelefSuc,CI.IdLocal AS CodCiuSuc
,LC.Localidad AS CiudadSuc,LC.IdDep AS CodDepSuc,CodFondo,F.Fondo AS NomFondo,CodArp,FRP.Fondo AS FondoArp,CodOper,EsCorr,PerCorr,NumCorr,TotalEmp,VrNomina,A.VrPension AS Total_Pen,A.VrSalud AS Total_Sal,A.VrRiesgos AS Total_Rie,A.VrCajas AS Total_Caj
,A.VrSena AS Total_Sen,A.VrIcbf AS Total_ICB,A.VrEsap AS Total_ESA,VrMinEduc,TotalAfipen,TotalAfiSal,TotalAfiRie,TotalAfiCaj,TotalAfiSen,TotalAfiIcb,TotalAfiEsa,TotalAfiEduc,TotAdmPen,TotAdmSal,TotAdmRie,TotAdmCom,A.Observacion AS Observ,LogAdmon,RedoMiles
,Anulado,A.IdEstado AS IdEstdo,Estado,TimeSys,A.FecUpdate AS Fec_Update,A.IdUsuario AS IdUsuari,Usuario
--datos del aportante
,NA.TipoId AS Apo_TipID,DvNit,IdRepLegal,RLG.RazonSocial AS NomRepLegal,RLG.TipoId AS RLegTID,RLG.Dv AS RLegDV,NA.Direccion AS Apo_dirccion,NA.IdLocal AS Apo_CodCiu,LA.Localidad AS Apo_NomCiu,LA.IdDep AS Apo_CodDep,Departamento,NA.Telefono AS Apo_tel,NA.Fax AS Apo_Fax,NA.e_mail AS Apo_email
,ClaseApo,TipoEco,TipoPersna,CIIUDane,FecIniCndato,TipAccCndato,FecFinCndato,CodOperInfo,TipoApo
--datos del detalle
,D.Item AS DetItem,D.IdEmpleado AS IdEmpldo,D.NContrato AS NumContrato,Apellido1,Apellido2,Nombre1,Nombre2,D.IdFonPen AS IdFdoPen,FP.Fondo AS FondoPen,TFP.TipoId AS Pen_TipID,FP.IdTercero AS Pen_Nit,TFP.Dv AS Pen_DV,TFP.RazonSocial AS Pen_Nombre
,IdFonSal,FS.Fondo AS FondoSal,TFS.TipoId AS Sal_TipID,FS.IdTercero AS Sal_Nit,TFS.Dv AS Sal_Dv,TFS.RazonSocial AS Sal_Nombre,IdFonRie,FR.Fondo AS FondoRie,TFR.TipoId AS Rie_TipID,FR.IdTercero AS Rie_nit,TFR.Dv AS Rie_Dv,TFR.RazonSocial AS Rie_nombre
,IdCajCom,FC.Fondo AS FondoCc,TFC.TipoId AS CC_TipId,FC.IdTercero AS CC_Nit,TFC.Dv AS CC_DV,TFC.RazonSocial AS cc_Nombre,SalBasico,VrIBCPen,DiasPen,TarifPen,D.VrPension AS VlrPension,VrApoFsp,VrApoFsu,VrApoVolAfi,VrApoVolApt,VrNoReten,VrIBCSal,DiasSal,TarifSal,D.VrSalud AS VlrSalud,D.VrUpc AS VlrUpc
,VrIncap,VrLicencia,NumAutInc,NumAutLic,VrIBCRie,DiasRie,TarifRie,D.VrRiesgos AS VlrRiesgos,VrIncIrp,NumAutIrp,D.IdInstala AS CodInstla,I.Instlacion AS CentroTrabaj,I.CodCiu AS CiuInstala
,VrIBCRef,VrIBCCajas,DiasCom,TarifCom,D.VrCajas AS VlrCajas,TarifSen,D.VrSena AS VlrSena,TarifIcb,D.VrICBF AS Vlricbf,TarifEsap,D.VrESAP AS VlrESAP,TarifEdu,VrMinEdu,TarifFsp,TarifFsu
--datos de novedades
,NovIng,NovRet,NovTde,NovTae,NovTdp,NovTap,NovVsp,NovVte,NovVst,NovSln,NovIge,NovLma,NovVac,NovAvp,NovVct,NovIrp,CodNewPen,FNP.Fondo AS NewFonPen,CodNewSal,FNS.Fondo AS NewFonEPS,CodNewIns,NI.Instlacion AS NewCentroTrabj,CodNewRie,CRN.Tarifa AS New_TarRie,CodNewGru
,NGR.TarfaTrab AS NewTarfTrab,NGR.TarfaEmp AS NewTarfEmp,DiasNov,DiasSan
--datos del empleado
,Apellidos,Nombres,E.Codigo AS EmpCodigo,E.TipoId AS Emp_TipID,E.Dv AS Emp_Dv,E.IdLugarCed AS Emp_IdLugrCed,L.Localidad AS Lugar_Ced,L.IdDep AS Emp_CodDep,E.Direccion AS Dirccion,E.Telefono AS Emp_Telfono,E.TelMovil AS Emp_TelMovil,E.e_mail AS Emp_Email,E.IdProf AS CodProf,Profesion,E.IdClase AS CodClaCta,NumCuenta
,E.IdBanco AS CodBanco,EsCondtor,pVehiculo,FecNacmto,LugarNacmto,TipoSangre,FactorRh,Sexo,Licencia,CatLicencia,VigLicencia,TallaCam,TallaPan,TallaZap,IdEstCivil,EstCivil,NHijos,E.Inactivo AS Emp_Inactvo
--datos del contrato
,C.IdTipCon AS CodTipCon,TipoContrato,FecIngreso,FecRetiro,Indefinido,C.IdCargo AS CodCargo,Cargo,C.IdArea AS CodArea,Area,C.IdDep AS CodDepe,Dependencia,C.IdCCosto AS CodCcto,CCosto,C.IdSubCos AS CodScost,SubCosto,C.IdNom AS CodTipNom,TipoNomina,C.IdClase AS CodClaRie,CR.ClaseRiesgo AS ClasRiesgo
,CR.Tarifa AS TarifaRie,VrSalario,VrAuxTrans,C.VrUpc AS Con_VrUpc,SalMinimo,SalIntegral,FPnomina,C.TipoLiquida AS TipLiq,FecAfiSeg,C.IdCot AS CodCotz,Cotizante,C.IdGrupo AS CodGrup,GAR.TarfaTrab AS Con_TrfaTrab,GAR.TarfaEmp AS TrfaEmp,TipoTrabj
,ATFinMes,SegFinMes,C.IdEstado AS Con_IdEstdo,C.Inactivo AS Con_inactivo,IdJornada,DenyPEN,DenyEPS,DenyARP,DenyCaj,D.CdFonAvp AS CodFondoAvp,E.CdFonAvp AS Emp_FondoAVP,TipoPlanilla,SubTipoCot
 FROM Trn_NomAutoliq AS A INNER JOIN Trn_NomAlqDet AS D ON A.TipoLiq=D.TipoLiq AND A.Numero=D.Numero 
 INNER JOIN  NomAportante AS NA ON A.IdAportnte=NA.IdAportnte  INNER JOIN Terceros AS RLG ON NA.IdRepLegal=RLG.IdTercero
 INNER JOIN EstadoDoc AS ED ON A.IdEstado=ED.IdEstado 
 INNER JOIN adm_Usuarios AS U ON A.IdUsuario=U.IdUsuario INNER JOIN Localidades AS LA ON NA.IdLocal=LA.IdLocal
 INNER JOIN Departamentos AS DE ON LA.IdDep=DE.IdDep
 INNER JOIN Empleados AS E ON D.IdEmpleado=E.IdEmpleado INNER JOIN Fondos AS FP ON D.IdFonPen=FP.IdFondo
 INNER JOIN Fondos AS FS ON D.IdFonSal=FS.IdFondo INNER JOIN Fondos AS FR ON D.IdFonRie=FR.IdFondo
 INNER JOIN Fondos  AS FC ON D.IdCajCom=FC.IdFondo INNER JOIN Terceros AS TFP ON FP.IdTercero=TFP.IdTercero
 INNER JOIN Terceros AS TFS ON FS.IdTercero=TFS.IdTercero  INNER JOIN Terceros AS TFR ON FR.IdTercero=TFR.IdTercero
 INNER JOIN Terceros AS TFC ON FC.IdTercero=TFC.IdTercero  INNER JOIN Instalaciones AS I ON D.IdInstala=I.IdInstala
 INNER JOIN Emp_Contrato AS C ON D.NContrato=C.NContrato  INNER JOIN Dependencias AS DP ON C.IdDep=DP.IdDep 
 INNER JOIN CentroCosto AS CO ON C.IdCCosto=CO.IdCCosto  INNER JOIN SubCentros AS SC ON C.IdSubCos=SC.IdSubCos
 INNER JOIN TiposNom AS TN ON C.IdNom=TN.IdNom  INNER JOIN TiposCot AS TCZ ON C.IdCot=TCZ.IdCot
 INNER JOIN Localidades AS L ON E.IdLugarCed=L.IdLocal
 INNER JOIN Profesiones AS P ON E.IdProf=P.IdProf  INNER JOIN EstadoCiv AS EC ON E.IdEstCivil=EC.IdEstado
 INNER JOIN TiposCon AS TC ON C.IdTipCon=TC.IdTipCon  INNER JOIN Cargos AS CG ON C.IdCargo=CG.IdCargo
 INNER JOIN Areas AS AR ON C.IdArea=AR.IdArea INNER JOIN ClaseRie AS CR ON C.IdClase=CR.IdClase 
 LEFT JOIN GruposAR AS GAR ON C.IdGrupo=GAR.IdGrupo
 LEFT JOIN Trn_NomAlqNov AS DN ON D.TipoLiq=DN.TipoLiq AND D.Numero=DN.Numero AND D.Item=DN.Item
 LEFT JOIN Fondos AS F ON A.CodFondo=F.IdFondo LEFT JOIN Fondos AS FRP ON A.CodArp=FRP.IdFondo
 LEFT JOIN Companias AS CI ON A.CodCia=CI.IdCia LEFT JOIN Localidades AS LC ON CI.IdLocal=LC.IdLocal
 LEFT JOIN Fondos AS FNP ON DN.CodNewPen=FNP.IdFondo  LEFT JOIN Fondos AS FNS ON DN.CodNewSal=FNS.IdFondo
 LEFT JOIN Instalaciones AS NI ON DN.CodNewIns=NI.IdInstala LEFT JOIN ClaseRie AS CRN ON DN.CodNewRie=CRN.IdClase
 LEFT JOIN GruposAR AS NGR ON DN.CodNewGru=NGR.IdGrupo
 WHERE A.TipoLiq=@pmTipoLiq AND A.Numero LIKE ISNULL(@pmNumero,'%') AND PerAutoliq LIKE ISNULL(@pmPerAutoliq,'%')
 ORDER BY A.Numero,Apellidos,Nombres
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE paQryOComb @pmTipDoc VARCHAR(3),@pmOCargue INT,@pmIdCia CHAR(2)
 AS 
 SELECT TipDoc,OCargue,IdCia,Fecha,FechaDesp,FechaVence,IdConcepto,IdCliente,IdAgencia,VrSubTotal,VrDescuento,VrImpuesto,VrFletes,VrOtros,VrCargos,VrOtrDcto
,VrSobretasa,VrImpGlobal,VrNeto,Cantidad,IdVend,TarifaCom,CodTarCom,DirEnvio,IdLocal,LugarEnv,DiasEntraga,IdVehiculo,IdConductor,NitEmpTrans,EmpTrans
,CdRuta,Referncia,IdForma,DetallePago,MulPlazos,IdPlazo,TipPed,Pedido,IdCiaPed,TipGui,Guia,IdCiaGui,TipFac,Factura,IdCiaFac,FechaFact,AutzaMora,AutzaCupo,Aprobacion,FechaAprob
,DetalleAprob,CdUsuAprob,OrigenAdd,ZonaFrontera,IdTerminal,Anulado,FecDev,Observacion,IdEstado,TimeSys,FecUpdate,IdCiaCrea,IdUsuario
 FROM Trn_OComb
 WHERE TipDoc=@pmTipDoc AND OCargue=@pmOCargue 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 paQryOComb_Cr @pmTipDoc VARCHAR(3),@pmOCargueIni INT,@pmOCargueFin INT,@pmIdCia CHAR(2)
 AS 
 SELECT O.TipDoc AS Tip_Doc,TipoDoc,OCargue,O.IdCia AS CdCia,Compania,O.Fecha AS FechaDoc,FechaDesp,FechaVence
,O.IdConcepto AS CdConcepto,Concepto,IdCliente,T.RazonSocial AS NomCliente,O.IdAgencia AS IdAgenc,Agencia,CodAgencia
,VrSubTotal,VrDescuento,VrImpuesto,VrFletes,VrOtros,VrCargos,VrOtrDcto,VrSobretasa,VrImpGlobal,VrNeto
,Cantidad,O.IdVend AS NitVendedor,VN.RazonSocial AS Vendedor,TarifaCom,DirEnvio,O.IdLocal AS CdCiudad,LE.Localidad AS CiudadEnvio,LE.IdDep AS CdDepEnvio,DE.Departamento AS DptoEnvio
,LugarEnv,DiasEntraga,O.IdVehiculo AS PlacaVeh,O.IdConductor AS CedConductor,TC.RazonSocial AS Conductor,LibMilitar,ClaseLib,FechaLib,CertJudicial,FecJudicial,VigJudicial,Licencia,CatLicencia,VigLicencia,NumTarjeta,VigTarjeta
,O.NitEmpTrans,EmpTrans,CdRuta,Ruta,Referncia,O.IdForma AS CdForma,FormaPago,DetallePago,MulPlazos
,O.IdPlazo AS CdPlazo,Plazo,NVmto,DiasPago,TipPed,Pedido,IdCiaPed,TipGui,Guia,IdCiaGui,TipFac,O.Factura AS NumFactura,IdCiaFac,FechaFact,AutzaMora,AutzaCupo
,Aprobacion,FechaAprob,DetalleAprob,CdUsuAprob,UA.Usuario AS UsuarioAprob,OrigenAdd,IdTerminal,ZonaFrontera,Anulado,FecDev,O.Observacion AS Observ,O.IdEstado AS CdEstado,Estado,O.IdUsuario AS IdUsuari,U.Usuario AS NomUsuario,Leyenda
,O.TimeSys AS Fec_Add,O.FecUpdate AS Fech_Update,IdCiaCrea
--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,Zona
,CLI.IdGrupo AS CdGrupoCli,GrupoClie,CLI.IdClase AS CdClaseCta,CLI.NumCuenta AS NumeroCuenta,CLI.IdBanco AS CdBanco,Banco,ExcIva,LiqFletes,CLI.FactSold AS FactSoldicom,Autoret,VrCupo,VrSaldo,Contrato,CLI.NContrato AS CliNumContrato
,DirAgncia,TelAgncia,FaxAgncia,A.IdLocal AS AgeIdCiudad,LA.Localidad AS AgeCiudad,LA.IdDep AS AgeCodDep,DA.Departamento AS AgeDpto,A.Referencia AS AgeReferencia
--vehiculo
,NumVeh,V.IdTipoVeh AS CdTipoVeh,TipoVehiculo,V.IdMarca AS CdMarcaVeh,MV.Marca AS MarcaVeh,Modelo,PesoVacio,PesoMax,NumMotor,SerieChasis,NumSerie,CdRemque,CarrCapac,V.UndCapc AS UnidCapacidad,Comptmtos,CapComp
--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,CdAgencia,K.CdCCosto AS KarCodCenCosto,KC.CCosto AS Kar_Ccosto
,K.CdSubCos AS KarCodSubcosto,KS.SubCosto AS Kar_Subcentro,K.pVehiculo AS KarPlacaVeh,K.Referencia AS KarReferencia,K.Descripcion AS KarDescripcion,Comptmntos,CdProdEquiv,TipOrd,NumOrden,IdCiaOrd,Cotizacion,IdCiaCot,K.Remision AS KarRemision,K.IdCiaRem AS CdCiaRem,K.Factura AS KarFactura,TipDocDev,NumDocDev
,Sobretasa,TasaNac,TasaDep,TasaMun,Soldicom,ImpGlobal,OtroImpto,Unidades,ItemCombo,Servcios,NoVentas,EsCombo,EsProdBase,K.ListaPrec AS KarLtaPrec,VrBase,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,M.Marca AS MarcaProd,Tamano,PM.UndMed AS CdUndMed
,SU.Unidad AS ProdUnidad,ExtciaMin,ExtciaMax,ExtciaAct,VrCostAnt,VrCosto,VrCostPmd,FecUltcom,FecUltVta,Seriales,Lotes,Combo,Tanques,DescripLong,Precio1,Precio2,Precio3,Precio4,Precio5
 FROM  Trn_OComb AS O INNER JOIN Trn_Kardex AS K ON O.TipDoc=K.TipDoc AND O.OCargue=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.IdCliente=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  Terceros AS VN ON O.IdVend=VN.IdTercero
 INNER JOIN Agencias  AS A ON O.IdAgencia=A.IdAgencia  INNER JOIN Formaspago AS FP ON O.IdForma=FP.IdForma
 INNER JOIN Terceros AS TC ON O.IdConductor=TC.IdTercero
 INNER JOIN TercCliente AS CLI ON O.IdCliente=CLI.IdClie 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 Localidades AS LA ON A.IdLocal=LA.IdLocal  INNER JOIN Departamentos AS DA ON LA.IdDep=DA.IdDep 
 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.IdLocal=LE.IdLocal  LEFT JOIN Departamentos AS DE ON LE.IdDep=DE.IdDep 
 LEFT JOIN Rutas AS R ON O.CdRuta=R.IdRuta
 LEFT JOIN CentroCosto AS KC ON K.CdCCosto=KC.IdCCosto LEFT JOIN SubCentros AS KS ON K.CdSubCos=KS.IdSubCos
 LEFT JOIN adm_Usuarios AS UA ON O.CdUsuAprob=UA.IdUsuario
 LEFT JOIN TercCndtores AS TCD ON O.IdConductor=TCD.IdConductor
 LEFT JOIN Vehiculos AS V ON O.IdVehiculo=V.IdVehiculo
 LEFT JOIN TiposVeh AS TV ON V.IdTipoVeh=TV.IdTipoVeh
 LEFT JOIN Marcas AS MV ON V.IdMarca=MV.IdMarca
  WHERE O.TipDoc=@pmTipDoc AND OCargue BETWEEN @pmOCargueIni AND @pmOCargueFin AND O.IdCia=@pmIdCia
 AND EsProdBase=0
 ORDER BY OCargue,Item
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE paQryOCombLta  @pmFechaIni SMALLDATETIME, @pmFechaFin SMALLDATETIME,@pmOCargueIni INT=Null,@pmOCargueFin INT=Null
,@pmIdCia CHAR(2)=Null,@pmIdCliente VARCHAR(16)=Null,@pmIdConcepto VARCHAR(4)=Null,@pmIdVend VARCHAR(16)=Null,@pmIdVehiculo VARCHAR(10)=Null
,@pmIdLocal VARCHAR(8)=Null,@pmIdEstado VARCHAR(4)=Null,@pmAnulado BIT=Null
 AS 
 SELECT OCargue,IdCia,Fecha,FechaDesp,FechaVence,O.IdConcepto AS CdConcepto,Concepto,IdCliente,T.RazonSocial AS NomCliente,IdAgencia,VrSubTotal,VrDescuento,VrImpuesto,VrFletes,VrOtros,VrCargos,VrOtrDcto,VrSobretasa,VrImpGlobal,VrNeto
,Cantidad,IdVend,V.RazonSocial AS Vendedor,TarifaCom,CodTarCom,DirEnvio,O.IdLocal AS CdCiudad,Localidad,LugarEnv,DiasEntraga,IdVehiculo,IdConductor,CD.RazonSocial AS Conductor,NitEmpTrans,EmpTrans,CdRuta,Referncia,IdForma,DetallePago,MulPlazos,IdPlazo
,TipPed,Pedido,IdCiaPed,TipGui,Guia,IdCiaGui,TipFac,Factura,IdCiaFac,FechaFact,AutzaMora,AutzaCupo,Aprobacion,FechaAprob,DetalleAprob,CdUsuAprob,OrigenAdd,Anulado,FecDev
,O.Observacion AS Observ,O.IdEstado AS CdEstado,Estado,ZonaFrontera,IdTerminal,O.IdUsuario AS IdUsuari,Usuario,TimeSys,IdCiaCrea,FecUpdate,TipDoc
 FROM Trn_OComb AS O  INNER JOIN Terceros AS T ON O.IdCliente=T.IdTercero
 INNER JOIN Terceros AS V ON O.IdVend=V.IdTercero
 INNER JOIN Terceros AS CD ON O.IdConductor=CD.IdTercero
 INNER JOIN  Conceptos AS CN ON O.IdConcepto=CN.IdConcepto
 INNER JOIN Localidades AS L ON O.IdLocal=L.IdLocal
 INNER JOIN EstadoDoc AS ED ON O.IdEstado=ED.IdEstado
 INNER JOIN adm_Usuarios AS U ON O.IdUsuario=U.IdUsuario 
 WHERE Fecha BETWEEN @pmFechaIni AND @pmFechaFin
 AND OCargue BETWEEN ISNULL(@pmOCargueIni,0) AND ISNULL(@pmOCargueFin,2147483647)
 AND IdCia LIKE ISNULL(@pmIdCia,'%%') AND IdCliente LIKE ISNULL(@pmIdCliente,'%') AND O.IdConcepto LIKE ISNULL(@pmIdConcepto,'%')
 AND O.IdEstado LIKE ISNULL(@pmIdEstado,'%') AND IdVend LIKE ISNULL(@pmIdVend,'%')
 AND IdVehiculo LIKE ISNULL(@pmIdVehiculo,'%')  AND O.IdLocal LIKE ISNULL(@pmIdLocal,'%')
 AND (Anulado=ISNULL(@pmAnulado,0) or Anulado=ISNULL(@pmAnulado,1))
 ORDER BY IdCia,OCargue
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE paQryOCombRel @pmTipDoc VARCHAR(3),@pmFechaIni SMALLDATETIME,@pmFechaFin SMALLDATETIME,@pmIdCia CHAR(2)=Null
,@pmIdConcepto VARCHAR(4)=Null,@pmIdCliente VARCHAR(16)=Null,@pmIdAgencia VARCHAR(16)=Null,@pmIdVend VARCHAR(16)=Null,@pmIdLocal VARCHAR(8)=Null
,@pmIdConductor VARCHAR(16)=Null,@pmIdVehiculo VARCHAR(10)=Null,@pmIdEstado VARCHAR(4)=Null,@pmAnulado BIT=Null
 AS 
 SELECT TipDoc,OCargue,O.IdCia AS CdCia,Compania,Fecha,FechaDesp,FechaVence,O.IdConcepto AS CdConcepto,Concepto,IdCliente,T.RazonSocial AS NomCliente
,O.IdAgencia AS IdAgenc,Agencia,CodAgencia,VrSubTotal,VrDescuento,VrImpuesto,VrFletes,VrOtros,VrCargos,VrOtrDcto,VrSobretasa,VrImpGlobal,VrNeto
,Cantidad,O.IdVend AS NitVendedor,VN.RazonSocial AS Vendedor,TarifaCom,DirEnvio,O.IdLocal AS CdCiudad,LE.Localidad AS CiudadEnvio,LE.IdDep AS CdDepEnvio,DE.Departamento AS DptoEnvio
,LugarEnv,DiasEntraga,O.IdVehiculo AS PlacaVeh,O.IdConductor AS CedConductor,TC.RazonSocial AS Conductor,LibMilitar,ClaseLib,FechaLib,CertJudicial,FecJudicial,VigJudicial,Licencia,CatLicencia,VigLicencia,NumTarjeta,VigTarjeta
,O.NitEmpTrans,EmpTrans,CdRuta,Ruta,Referncia,O.IdForma AS CdForma,FormaPago,DetallePago,MulPlazos
,O.IdPlazo AS CdPlazo,Plazo,NVmto,DiasPago,TipPed,Pedido,IdCiaPed,TipGui,Guia,IdCiaGui,TipFac,Factura,IdCiaFac,FechaFact,AutzaMora,AutzaCupo
,Aprobacion,FechaAprob,DetalleAprob,CdUsuAprob,UA.Usuario AS UsuarioAprob,OrigenAdd,ZonaFrontera,Anulado,FecDev,O.Observacion AS Observ,IdTerminal,O.IdEstado AS CdEstado,Estado,O.IdUsuario AS IdUsuari,U.Usuario AS NomUsuario
,TimeSys,O.FecUpdate AS Fech_Update,IdCiaCrea
--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,Zona
,CLI.IdGrupo AS CdGrupoCli,GrupoClie,CLI.IdClase AS CdClaseCta,CLI.NumCuenta AS NumeroCuenta,CLI.IdBanco AS CdBanco,Banco,ExcIva,LiqFletes,CLI.FactSold AS FactSoldicom,Autoret,VrCupo,VrSaldo,Contrato,CLI.NContrato AS CliNumContrato
,DirAgncia,TelAgncia,FaxAgncia,A.IdLocal AS AgeIdCiudad,LA.Localidad AS AgeCiudad,LA.IdDep AS AgeCodDep,DA.Departamento AS AgeDpto,A.Referencia AS AgeReferencia
--vehiculo
,NumVeh,V.IdTipoVeh AS CdTipoVeh,TipoVehiculo,V.IdMarca AS CdMarcaVeh,MV.Marca AS MarcaVeh,Modelo,PesoVacio,PesoMax,NumMotor,SerieChasis,NumSerie,CdRemque,CarrCapac,V.UndCapc AS UnidCapacidad,Comptmtos,CapComp
 FROM  Trn_OComb AS O  INNER JOIN Companias AS CN ON O.IdCia=CN.IdCia 
 INNER JOIN Terceros AS T ON O.IdCliente=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  Terceros AS VN ON O.IdVend=VN.IdTercero
 INNER JOIN Agencias  AS A ON O.IdAgencia=A.IdAgencia  INNER JOIN Formaspago AS FP ON O.IdForma=FP.IdForma
 INNER JOIN Terceros AS TC ON O.IdConductor=TC.IdTercero
 INNER JOIN TercCliente AS CLI ON O.IdCliente=CLI.IdClie 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 Localidades AS LA ON A.IdLocal=LA.IdLocal  INNER JOIN Departamentos AS DA ON LA.IdDep=DA.IdDep 
 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.IdLocal=LE.IdLocal  LEFT JOIN Departamentos AS DE ON LE.IdDep=DE.IdDep 
 LEFT JOIN Rutas AS R ON O.CdRuta=R.IdRuta
 LEFT JOIN adm_Usuarios AS UA ON O.CdUsuAprob=UA.IdUsuario
 LEFT JOIN TercCndtores AS TCD ON O.IdConductor=TCD.IdConductor
 LEFT JOIN Vehiculos AS V ON O.IdVehiculo=V.IdVehiculo
 LEFT JOIN TiposVeh AS TV ON V.IdTipoVeh=TV.IdTipoVeh
 LEFT JOIN Marcas AS MV ON V.IdMarca=MV.IdMarca
  WHERE TipDoc=@pmTipDoc  AND Fecha BETWEEN @pmFechaIni AND @pmFechaFin
 AND O.IdCia LIKE ISNULL(@pmIdCia,'%%') AND O.IdConcepto LIKE ISNULL(@pmIdConcepto,'%')
 AND IdCliente LIKE ISNULL(@pmIdCliente,'%')  AND O.IdAgencia LIKE ISNULL(@pmIdAgencia ,'%')
 AND O.IdVend LIKE ISNULL(@pmIdVend,'%') AND O.IdLocal LIKE ISNULL(@pmIdLocal,'%')
 AND O.IdConductor LIKE ISNULL(@pmIdConductor,'%') AND O.IdVehiculo LIKE ISNULL(@pmIdVehiculo,'%')
 AND O.IdEstado LIKE ISNULL(@pmIdEstado,'%')
 AND (Anulado=ISNULL(@pmAnulado,0) or Anulado=ISNULL(@pmAnulado,1))
 ORDER BY O.IdCia,OCargue
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE paQryTanques @pmIdTanque VARCHAR(4)
 AS 
 SELECT IdTanque,IdProducto,Descripcion,CapTanq,NivAgua,CapNeta,SaldoActual
 ,TnqGas,TnqUnido,TnqVirtual,CdNueTan,NColor,FechaAdd,FechaUpdate,Inactivo,IdCiaDef,CodigoApi,CdTerminal
 FROM Tanques
 WHERE IdTanque=@pmIdTanque
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE paQryTanquesLta @pmIdProducto VARCHAR(16)=Null
 AS 
 SELECT IdTanque,T.IdProducto AS CdProd,DescripProd,Descripcion,CapTanq,NivAgua,CapNeta,SaldoActual
,TnqGas,TnqUnido,TnqVirtual,CdNueTan,NColor,T.FechaAdd AS Fec_Add,T.FechaUpdate AS Fec_Upd,IdCiaDef,CdTerminal
 FROM Tanques AS T INNER JOIN ProdMcias AS P ON T.IdProducto=P.IdProducto
 WHERE T.Inactivo=0 AND  T.IdProducto LIKE ISNULL(@pmIdProducto,'%')
 ORDER BY DescripProd,IdTanque
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE PROCEDURE paQryTerminales @pmIdTerminal VARCHAR(4)
 AS 
 SELECT IdTerminal,Terminal,IdCia,Inactivo
 FROM Terminales
 WHERE IdTerminal=@pmIdTerminal
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE paQrytm_Aportes @pmtmEst VARCHAR(5),@pmtmIdInstala VARCHAR(4)=Null,@pmtmIdDep VARCHAR(4)=Null,@pmtmIdNom VARCHAR(4)=Null
,@pmIdArea VARCHAR(4)=Null
 AS 
 SELECT tmIdEmpleado,Apellidos,Nombres,tmNContto,tmDiasPen,tmBasePen,tmTarfaPen,tmValorPen,tmTarfaFsp,tmValorFsp,tmTarfaFsu,tmValorFsu,tmDiasSal
,tmBaseSal,tmTarfaSal,tmValorSal,tmDiasRie,tmBaseRie,tmTarfaRie,tmValorRie,tmDiasCom,tmBaseCom,tmTarfaCom,tmValorCom,tmTarfaIcb,tmValorIcbf,tmTarfaSen,tmValorSena
,tmTarfaMin,tmValorMin,tmTarfaEsa,tmValorESAP,tmDiasNov,tmNovIng,tmNovRet,tmNovTda,tmNovTaa,tmNovTdp,tmNovTap,tmNovVsp,tmNovVte,tmNovVst,tmNovSln,tmNovIge,tmNovLma,tmNovVac,tmNovAvp,tmNovIrp,tmNovVct,tmNAutInc,tmVrIncap,tmNAutLic,tmVrLicencia,tmNAutIrp,tmVrVolAfi,tmVrVolApo,tmVrNoReten,tmVrUpc,tmVrIncIrp
,tmIdFonPen,FP.Fondo AS FondoPen,tmIdFonSal,FS.Fondo AS FondoEps,tmIdFonRie,FR.Fondo AS FondoArp,tmIdFonCaj,CC.Fondo AS CajaComp,tmIdInstala,Instlacion,tmIdDep,Dependencia,tmIdCCosto,CCosto,tmIdSubCos,SubCosto,tmIdNom,TipoNomina,tmVehiculo,tmVrBasico
,tmCodNewEps,tmCodNewPen,tmCodNewRie,tmCodNewIns,tmCodNewTar,CRN.ClaseRiesgo AS NewClaseRie,CRN.Tarifa AS NewTarifa,tmCodNewGru,TarfaEmp,TarfaTrab,tmEst,tmItem,tmApellido1,tmApellido2,tmNombre1,tmNombre2,tmBaseRef,tmDiasSan
--Datos empleado
,Codigo,TipoId,Dv,IdLugarCed,Localidad,E.Direccion AS Dirccion,Telefono,TelMovil,e_mail,E.IdProf AS CodProf,Profesion,E.IdClase AS CodClaCta,ClaseCuenta,NumCuenta,E.IdBanco AS CodBanco,Banco,EsCondtor,FecNacmto,LugarNacmto,TipoSangre,FactorRh,Sexo,Licencia,CatLicencia,VigLicencia
,E.IdLugar AS CodLugar,LugarLic,TallaCam,TallaPan,TallaZap,IdEstCivil,EstCivil,NHijos,E.Inactivo AS Emp_Inactvo
--datos del contrato
,C.IdTipCon AS CodTipCon,TipoContrato,FecIngreso,FecRetiro,Indefinido,C.IdCargo AS CodCargo,Cargo,C.IdArea AS CodArea,Area,C.IdClase AS CodClaRie,CR.ClaseRiesgo AS ClasRiesgo,CR.Tarifa AS TarifaRie,VrSalario,VrAuxTrans,SalMinimo,SalIntegral,FPnomina,C.TipoLiquida AS TipLiq,FecAfiSeg
,ATFinMes,SegFinMes,DchDotacion,DchCafeteria,C.IdEstado AS Con_IdEstdo,Estado,C.Inactivo AS Con_inactivo,C.IdJornada AS CodJnada,Jornada,TipoTrabj,tmCdFonAvp,CdFonAvp
 FROM tm_Aportes AS A INNER JOIN Empleados AS E ON A.tmIdEmpleado=E.IdEmpleado INNER JOIN  Emp_Contrato AS C ON A.tmNContto=C.NContrato
 INNER JOIN Dependencias AS DP ON A.tmIdDep=DP.IdDep  INNER JOIN Instalaciones AS I ON A.tmIdInstala=I.IdInstala
 INNER JOIN CentroCosto AS CO ON A.tmIdCCosto=CO.IdCCosto  INNER JOIN SubCentros AS SC ON A.tmIdSubCos=SC.IdSubCos
 INNER JOIN TiposNom AS TN ON A.tmIdNom=TN.IdNom INNER JOIN Fondos AS FP ON A.tmIdFonPen=FP.IdFondo
 INNER JOIN Fondos AS FS ON A.tmIdFonSal=FS.IdFondo  INNER JOIN Fondos AS FR ON A.tmIdFonRie=FR.IdFondo
 INNER JOIN Fondos AS CC ON A.tmIdFonCaj=CC.IdFondo INNER JOIN Localidades AS L ON E.IdLugarCed=L.IdLocal
 INNER JOIN Profesiones AS P ON E.IdProf=P.IdProf   INNER JOIN ClaseCta AS CT ON E.IdClase=CT.IdClase
 INNER JOIN Bancos AS B ON E.IdBanco=B.IdBanco INNER JOIN EstadoCiv AS EC ON E.IdEstCivil=EC.IdEstado
 INNER JOIN ExpLicencias AS EL ON E.IdLugar=EL.IdLugar
 INNER JOIN TiposCon AS TC ON C.IdTipCon=TC.IdTipCon
 INNER JOIN Cargos AS CG ON C.IdCargo=CG.IdCargo  INNER JOIN Areas AS AR ON C.IdArea=AR.IdArea
 INNER JOIN ClaseRie AS CR ON C.IdClase=CR.IdClase INNER JOIN Jornadas AS J ON C.IdJornada=J.IdJornada
 INNER JOIN EstadoDoc AS ECO ON C.IdEstado=ECO.IdEstado  LEFT JOIN ClaseRie AS CRN ON A.tmCodNewTar=CRN.IdClase
 LEFT JOIN GruposAR AS GAR ON A.tmCodNewGru=GAR.IdGrupo
 WHERE tmEst=@pmtmEst AND tmIdInstala LIKE ISNULL(@pmtmIdInstala,'%') AND tmIdDep LIKE ISNULL(@pmtmIdDep,'%')
 AND tmIdNom LIKE ISNULL(@pmtmIdNom,'%') AND C.IdArea LIKE ISNULL(@pmIdArea,'%')
 ORDER BY Apellidos,Nombres,tmItem
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE paQrytm_AportesDet @pmtmEst VARCHAR(5),@pmtmItem INT=Null
 AS 
 SELECT tmEst,tmItem,tmIdEmpleado,tmApellido1,tmApellido2,tmNombre1,tmNombre2,tmNContto,tmDiasPen,tmDiasSal,tmDiasRie,tmDiasCom,tmBasePen,tmBaseSal,tmBaseRie,tmBaseCom,tmBaseRef,tmValorPen,tmValorSal,tmValorRie,tmValorCom,tmValorSena
,tmValorIcbf,tmValorFsp,tmValorFsu,tmValorESAP,tmValorMin,tmTarfaPen,tmTarfaSal,tmTarfaRie,tmTarfaFsp,tmTarfaFsu,tmTarfaEsa,tmTarfaMin,tmTarfaCom,tmTarfaIcb,tmTarfaSen,tmNovIng,tmNovRet,tmNovTda,tmNovTaa,tmNovTdp,tmNovTap,tmNovVsp,tmNovVte
,tmNovVst,tmNovSln,tmNovIge,tmNovLma,tmNovVac,tmNovAvp,tmNovIrp,tmNovVct,tmCodNewEps,tmCodNewPen,tmCodNewRie,tmCodNewIns,tmCodNewTar,tmCodNewGru,tmVrIncap,tmVrLicencia,tmVrVolAfi,tmVrVolApo,tmVrNoReten,tmVrUpc,tmVrIncIrp,tmNAutLic,tmNAutInc,tmNAutIrp
,tmIdFonPen,tmIdFonSal,tmIdFonRie,tmIdFonCaj,tmIdInstala,tmIdDep,tmIdCCosto,tmIdSubCos,tmIdNom,tmVehiculo,tmVrBasico,tmDiasNov,tmCdFonAvp,tmDiasSan
 FROM tm_Aportes
 WHERE tmEst=@pmtmEst
 AND (tmItem>=ISNULL(@pmtmItem,0)  AND tmItem<=ISNULL(@pmtmItem,2147483647))
 ORDER BY tmItem
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE paQrytm_AportesDsn @pmtmEst VARCHAR(5)
 AS 
 SELECT tmItem,tmIdEmpleado,tmApellido1,tmApellido2,tmNombre1,tmNombre2,tmNovIng,tmNovRet,tmNovTda,tmNovTaa,tmNovTdp,tmNovTap,tmNovVsp,tmNovVte
,tmNovVst,tmNovSln,tmNovIge,tmNovLma,tmNovVac,tmNovAvp,tmNovIrp,tmNovVct,tmCodNewEps,FS.Fondo AS NuevaEps,tmCodNewPen,FP.Fondo AS NuevoFdoPen,tmCodNewIns,Instlacion
,tmCodNewTar,tmCodNewGru,tmDiasNov,tmDiasSan
 FROM tm_Aportes AS A LEFT JOIN Instalaciones AS I ON A.tmCodNewIns=I.IdInstala
 LEFT JOIN Fondos AS FP ON A.tmCodNewPen=FP.IdFondo LEFT JOIN Fondos AS FS ON A.tmCodNewEps=FS.IdFondo
 WHERE tmEst=@pmtmEst AND tmDiasNov>0
 ORDER BY tmItem
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE paQrytm_AportesDso @pmtmEst VARCHAR(5)
 AS 
 SELECT tmItem,tmIdEmpleado,tmApellido1,tmApellido2,tmNombre1,tmNombre2,tmVrBasico,tmDiasPen,tmBasePen,tmTarfaPen,tmValorPen,tmVrVolAfi,tmVrVolApo,tmVrNoReten,tmValorFsp,tmValorFsu
,tmDiasSal,tmBaseSal,tmTarfaSal,tmValorSal,tmVrUpc,tmNAutInc,tmVrIncap,tmNAutLic,tmVrLicencia,tmDiasRie,tmBaseRie,tmTarfaRie,tmValorRie,tmVrIncIrp,tmNAutIrp
,tmDiasCom,tmBaseCom,tmTarfaCom,tmValorCom,tmTarfaIcb,tmValorIcbf,tmTarfaSen,tmValorSena,tmBaseRef,tmTarfaEsa,tmValorESAP,tmTarfaMin,tmValorMin
,tmTarfaFsp,tmTarfaFsu,tmIdFonPen,FP.Fondo AS FondoPen,tmIdFonSal,FS.Fondo AS FondoEps,tmIdFonRie,FR.Fondo AS FondoArp,tmIdFonCaj,CC.Fondo AS CajaComp
,tmIdInstala,Instlacion,tmNContto,tmIdNom,tmCdFonAvp,tmDiasSan
 FROM tm_Aportes AS A INNER JOIN Instalaciones AS I ON A.tmIdInstala=I.IdInstala
 INNER JOIN Fondos AS FP ON A.tmIdFonPen=FP.IdFondo INNER JOIN Fondos AS FS ON A.tmIdFonSal=FS.IdFondo
 INNER JOIN Fondos AS FR ON A.tmIdFonRie=FR.IdFondo INNER JOIN Fondos AS CC ON A.tmIdFonCaj=CC.IdFondo
 WHERE tmEst=@pmtmEst
 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 paQryTraManifiestoNov  @pmFechaIni SMALLDATETIME,@pmFechaFin SMALLDATETIME,@pmIdCia CHAR(2)=Null
, @pmIdVehiculo VARCHAR(10)=Null,@pmIdPoseedor VARCHAR(16)=Null
 AS 
SELECT M.TipDoc AS CdTipDoc,M.Manifiesto AS NumManif,M.IdCia AS CdCia,Compania,Fecha,FecDespacho,FecEntrega,M.IdOrigen AS CdOrigen,CO.Localidad AS CiuOrigen,CO.IdDep AS CodDepOrigen,DPO.Departamento AS DptoOrigen
,M.IdDestino AS CdDestino,CD.Localidad AS CiuDestino,CD.IdDep AS CodDepDestino,DPD.Departamento AS DptoDestino,M.IdRuta AS CdRuta,R.Ruta AS DescRuta
,M.IdVehiculo AS PlacaVeh,M.IdConductor AS CedConductor,CDT.RazonSocial AS NomConductor,nRemolque,TipoAfiVehic,M.IdPropietario AS NitPropietario,NP.RazonSocial AS Propietario,M.IdPoseedor AS NitPoseedor,T.RazonSocial AS Poseedor
,VrFletes,VrRetencion,VrReteIca,VrDescuento,VrAnticipo,VrAntAdic,VrNeto,VrPagos,VrCargos,VrDctos,TarifaFlete,M.Cantidad AS CantTotal,PesoTotal,BaseRet,TarifaRet,TarifaIca
,FechaPago,PagoCargue,PagoDescargue,NumMintrans,EdoMintrans,M.Cumplido AS NumCumplido,M.IdCiaCump AS CdCiaCump,M.FechaCump AS FecCumplido,EstCumplido
,M.TipOdp AS TipoOdp,M.OrdPago AS NumOrdPago,M.IdCiaOdp AS CdCiaOdp,FechaOdp,EstOrden,Anulado,FecDev,M.Observacion AS Observ,M.IdEstado AS CdEstado,Estado
,NomRemite,NomDestino,LugarFletes,NumAnticipo,NumCheque,TipoMintrans,MucMintrans,ContIntegral,VolumenCarga
--Datos de novedades
,Item,C.Descripcion AS Descrpcion,Tarifa,TipoConc,RubroConcep,C.Cantidad AS CantConc,VrUnitario,TarifIva,C.IdConcepto AS CdConcepto,Concepto,CdCuenta,NomCuenta
,NitTercero,TC.RazonSocial AS NomTercero,CdTipoEsc,TipoEscolta,FechaNov,VrBase,TipoTarif,RefConc
--datos del propietario
,T.TipoId AS TercTipId,T.Dv AS TercDv,T.Codigo AS TercCodigo,T.NomCial AS TercNomCial,T.Direccion AS TercDireccion,T.IdLocal AS TercCdCiudad,L.Localidad AS NomCiudad,T.Telefono AS TercTelefono,T.e_mail AS TercEmail
,NumVeh,V.IdTipoVeh AS CdTipVeh,TipoVehiculo,V.IdMarca AS CdMarca,MV.Marca AS MarcaVeh,Modelo,Config,V.Descripcion AS VehDescripcion
 FROM Trn_TraManifiesto AS M INNER JOIN  Trn_TraManifAnexo AS MA ON M.TipDoc=MA.TipDoc AND  M.Manifiesto=MA.Manifiesto AND  M.IdCia=MA.IdCia
 INNER JOIN Companias AS CN ON M.IdCia=CN.IdCia  INNER JOIN EstadoDoc AS ED ON M.IdEstado=ED.IdEstado
 INNER JOIN Localidades AS CO ON  M.IdOrigen=CO.IdLocal INNER JOIN Departamentos AS DPO ON CO.IdDep=DPO.IdDep
 INNER JOIN Localidades AS CD ON M.IdDestino=CD.IdLocal INNER JOIN Departamentos AS DPD ON CD.IdDep=DPD.IdDep
 INNER JOIN Rutas AS R ON M.IdRuta=R.IdRuta  INNER JOIN Terceros AS  CDT ON M.IdConductor=CDT.IdTercero 
 INNER JOIN Terceros AS NP ON M.IdPropietario=NP.IdTercero INNER JOIN Terceros AS T ON M.IdPoseedor=T.IdTercero
 INNER JOIN Localidades AS L ON T.IdLocal=L.IdLocal
 INNER JOIN Vehiculos AS V ON M.IdVehiculo=V.IdVehiculo  INNER JOIN TiposVeh AS TV ON V.IdTipoVeh=TV.IdTipoVeh 
 INNER JOIN Marcas AS MV ON V.IdMarca=MV.IdMarca
 INNER JOIN  Trn_TraConceptos AS C ON M.TipDoc=C.TipDoc AND M.Manifiesto=C.Documento AND M.IdCia=C.IdCia
 LEFT JOIN ConcDiversos AS CDV ON C.IdConcepto=CDV.IdConcepto
 LEFT JOIN TiposEsc AS TE ON C.CdTipoEsc=TE.IdTipoEsc LEFT JOIN Terceros AS TC ON C.NitTercero=TC.IdTercero
 LEFT JOIN Puc AS P ON C.CdCuenta=P.IdCuenta
 WHERE M.TipDoc='MUC'  AND  M.Fecha BETWEEN @pmFechaIni AND @pmFechaFin  AND M.IdCia LIKE ISNULL(@pmIdCia,'%%')
 AND M.IdVehiculo  LIKE ISNULL(@pmIdVehiculo ,'%') AND M.IdPoseedor LIKE ISNULL(@pmIdPoseedor,'%')
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE paQryTraManifiestoRen  @pmFechaIni SMALLDATETIME,@pmFechaFin SMALLDATETIME,@pmIdCia CHAR(2)=Null
,@pmIdVehiculo VARCHAR(10)=Null,@pmIdPoseedor VARCHAR(16)=Null
 AS 
SELECT RM.TipDoc AS TipMuc,RM.Manifiesto AS NumManif,RM.IdCia AS CdCia,Compania,M.Fecha AS MucFecha,M.FecDespacho AS MucFecDespacho,RM.TipRem AS TipRem,RM.Remesa AS NumRemesa,RM.IdCiaRem AS CdCiaRem
,RM.ItemRem AS ItemRemsa,R.Fecha AS RemFecha,R.FecDespacho AS RemFecDespacho,IdMercancia,DescripMcias,D.Cantidad AS RemCantidad,D.PesoNeto AS RemPesoNeto,D.UndMed AS CdUmPeso,UMP.Unidad AS UmPeso,D.Volumen AS RemVolumen,UndVol,Cases,Cajas,Palets
,D.TarifClie AS RemTarifClie,UndTarifa,D.VrDeclarado AS RemVrDeclara,D.VrSeguro AS RemVrSeguro,D.TarifSeguro AS RemTarifSeguro,RM.TarifPago AS MucTarifPago,RM.TarifTabla AS MucTarifTabla,UndTarifPago,D.Cumplido AS NumCumplido,D.IdCiaCump AS CdCiaCump
,D.FechaCump AS FecCumplido,CantidadCump,PesoCump,VolCump,CasesCump,CajasCump,PaletsCump,TarifCump,UndTarCump,PagoCump,UndTarPagoCump,DetalleCump,D.TarifClieFac AS RemTarifFact,D.TarifOdp AS RemTarifOdp,D.IdUnd AS CdUnd,UM.Unidad AS UndMedida,Remision,DocCliente,Referencia1,Referencia2,Referencia3
,NitRemite,Remitente,NitDestntario,Destinatario,D.IdOrigen AS CdCiuOrigen,LO.Localidad AS CiuOrigen,LO.IdDep AS CodDepOrigen,DPO.Departamento AS DptoOrigen,D.IdDestino AS CdCiuDestino,LD.Localidad AS CiuDestino,LD.IdDep AS CodDepDestino,DPD.Departamento AS DptoDestino
,IdCliente,T.RazonSocial AS NomCliente,R.IdAgencia AS CdAgencia,Agencia,R.Modalidad AS TipoRemesa,M.IdVehiculo AS PlacaVeh,M.TipoAfiVehic AS TipoAfiVeh,M.IdConductor AS CedCondutor,CDT.RazonSocial AS NomConductor,M.nRemolque AS NumRemolque
,M.IdPoseedor AS NitPoseedor,NP.RazonSocial AS NomPoseedor,M.VrFletes AS MucVrFletes,M.VrRetencion AS MucVrRetencion,M.VrReteIca AS MucVrReteIca,M.VrAnticipo+VrAntAdic AS MucAnticipos,M.TarifaRet AS MucTarifRet,M.TarifaIca AS MucTarifIca,M.IdRuta AS CdRuta
,M.TipOdp AS CdTipOdp,M.OrdPago AS MucNumOdp,M.IdCiaOdp AS MucCiaOdp,M.FechaOdp AS FecOrdenPago,EstOrden,M.Observacion AS MucObserv
--datos de orden pago
,OP.PesoTotal AS OdpPesoNeto,OP.Unidades AS OdpCantidad,OP.Volumen AS OdpVolumen,OP.TarifaTabla AS OdpTarifTabla,OP.TarifaFlete AS OdpTarifFlete,OP.UnidTarifa AS OdpUndTarifa,OP.VrTotalFletes AS OdpTotalFletes,OP.VrDescuento AS OdpVrDcto
,OP.VrRetencion AS OdpVrRetFte,OP.VrReteIca AS OdpReteIca,OP.VrAnticipos AS OdpAnticipos,OP.VrFaltantes AS OdpFaltantes,VrSeguros,VrFondos,VrAportes,VrOtrosDctos,VrImpuestos,VrEstampilla
,VrOtrosPagos,OP.VrNeto AS OdpVrNetoPagar,VrRemesas,VrRecCaja,OP.TarifaRet AS OdpTarifRet,OP.TarifaIca AS OdpTarifIca
,OP.IdPoseedor AS OdpNitPoseedor,ODP.Anulado AS OdpAnulado,ODP.Observacion AS OdpObserv,TipEgr,Egreso,IdCiaEgr,NumCheque,OP.Referencia AS OdpReferencia
,R.NumPedido AS nPedido,IdCiaPed,FechaPed,R.TipDcm AS TipOdc,R.NumDocmto AS nOCargue,R.IdCiaDcm AS CdCiaOdc,R.FechaDcm AS FecOCargue
,R.EstCumplido AS RemEdoCumplido,R.EstFactura AS RemEdoFactura,SerieGuia,NumGuia,R.Observacion AS RemObserv
--columnas de detalles ocultas,TipFac,Factura,IdCiaFac,FechaFac,TipOdp,NumeroOdp,IdCiaOdp,PesoCont
--datos de factura
,D.TipFac AS CdTipFact,Factura,NumFactura,D.IdCiaFac AS CdCiaFact,FechaFac,FacCantidad,FacUnidades,FacPesoNeto,FacVolumen,FacValorTotal,FacCostoTotal,FacFaltantes
,NumDevFactura,DfaCantidad,DfaUnidades,DfaPesoNeto,DfaVolumen,DfaValorTotal,DfaCostoTotal,DfaFaltantes
,NumNotaDb,NumNotaCr,NotCantidad,NotPesoNeto,NotVolumen,NotValorTotal,NotCostoTotal
 FROM Trn_TraManifRem AS RM INNER JOIN Trn_TraManifiesto AS M ON RM.TipDoc=M.TipDoc AND RM.Manifiesto=M.Manifiesto AND RM.IdCia=M.IdCia
 INNER JOIN Trn_TraRemMcias AS D ON RM.TipRem=D.TipDoc AND RM.Remesa=D.NumOrden AND RM.IdCiaRem=D.IdCia AND RM.ItemRem=D.Item
 INNER JOIN Trn_TraRemesa AS R ON RM.TipRem=R.TipDoc AND RM.Remesa=R.NumOrden AND RM.IdCiaRem=R.IdCia
 INNER JOIN Companias AS CN ON RM.IdCia=CN.IdCia 
 INNER JOIN Localidades AS LO ON D.IdOrigen=LO.IdLocal INNER JOIN Departamentos AS DPO ON LO.IdDep=DPO.IdDep
 INNER JOIN Localidades AS LD ON D.IdDestino=LD.IdLocal INNER JOIN Departamentos AS DPD ON LD.IdDep=DPD.IdDep
 INNER JOIN UndMed AS UM ON D.IdUnd=UM.IdUnd INNER JOIN Terceros AS T ON R.IdCliente=T.IdTercero
 INNER JOIN Vehiculos AS V ON M.IdVehiculo=V.IdVehiculo INNER JOIN Terceros AS CDT ON M.IdConductor=CDT.IdTercero 
 INNER JOIN Terceros AS NP ON M.IdPoseedor=NP.IdTercero 
 LEFT JOIN Trn_TraOrdenPago AS ODP ON M.TipOdp=ODP.TipDoc AND M.OrdPago=ODP.OrdPago AND M.IdCiaOdp=ODP.IdCia
 LEFT JOIN Trn_TraOrdenManif AS OP ON M.TipOdp=OP.TipDoc AND M.OrdPago=OP.OrdPago AND M.IdCiaOdp=OP.IdCia
 LEFT JOIN Agencias AS A ON R.IdAgencia=A.IdAgencia LEFT JOIN Sys_Um AS UMP ON D.UndMed=UMP.UndMed 
--subconsulta de facturas 
LEFT JOIN (SELECT TipRem,Remesa,IdCiaRem,ItemRem,SUM(CASE WHEN SUBSTRING(TipDoc,1,2)='FC' THEN Cantidad ELSE 0 END) AS FacCantidad
,SUM(CASE WHEN SUBSTRING(TipDoc,1,2)='FC' THEN Cantidad*VrUnitario ELSE 0 END) AS FacValorTotal
,SUM(CASE WHEN SUBSTRING(TipDoc,1,2)='FC' THEN Cantidad*VrCosto ELSE 0 END) AS FacCostoTotal
,SUM(CASE WHEN SUBSTRING(TipDoc,1,2)='FC' THEN VrFaltante ELSE 0 END) AS FacFaltantes
,SUM(CASE WHEN SUBSTRING(TipDoc,1,2)='FC' THEN Unidades ELSE 0 END) AS FacUnidades
,SUM(CASE WHEN SUBSTRING(TipDoc,1,2)='FC' THEN PesoNeto ELSE 0 END) AS FacPesoNeto
,SUM(CASE WHEN SUBSTRING(TipDoc,1,2)='FC' THEN Volumen ELSE 0 END) AS FacVolumen
,SUM(CASE WHEN SUBSTRING(TipDoc,1,2)='FC' THEN Cases ELSE 0 END) AS FacCases
,SUM(CASE WHEN SUBSTRING(TipDoc,1,2)='FC' THEN Cajas ELSE 0 END) AS FacCajas
,SUM(CASE WHEN SUBSTRING(TipDoc,1,2)='FC' THEN Palets ELSE 0 END) AS FacPalets
,SUM(CASE WHEN SUBSTRING(TipDoc,1,2)='DF' THEN Cantidad ELSE 0 END) AS DfaCantidad
,SUM(CASE WHEN SUBSTRING(TipDoc,1,2)='DF' THEN Cantidad*VrUnitario ELSE 0 END) AS DfaValorTotal
,SUM(CASE WHEN SUBSTRING(TipDoc,1,2)='DF' THEN Cantidad*VrCosto ELSE 0 END) AS DfaCostoTotal
,SUM(CASE WHEN SUBSTRING(TipDoc,1,2)='DF' THEN VrFaltante ELSE 0 END) AS DfaFaltantes
,SUM(CASE WHEN SUBSTRING(TipDoc,1,2)='DF' THEN Unidades ELSE 0 END) AS DfaUnidades
,SUM(CASE WHEN SUBSTRING(TipDoc,1,2)='DF' THEN PesoNeto ELSE 0 END) AS DfaPesoNeto
,SUM(CASE WHEN SUBSTRING(TipDoc,1,2)='DF' THEN Volumen ELSE 0 END) AS DfaVolumen
,SUM(CASE WHEN SUBSTRING(TipDoc,1,2)='DF' THEN Cases ELSE 0 END) AS DfaCases
,SUM(CASE WHEN SUBSTRING(TipDoc,1,2)='DF' THEN Cajas ELSE 0 END) AS DfaCajas
,SUM(CASE WHEN SUBSTRING(TipDoc,1,2)='DF' THEN Palets ELSE 0 END) AS DfaPalets
,SUM(CASE TipDoc WHEN 'NDB' THEN Cantidad WHEN 'NCR' THEN Cantidad*-1 ELSE 0 END) AS NotCantidad
,SUM(CASE TipDoc WHEN 'NDB' THEN Cantidad*VrUnitario WHEN 'NCR' THEN (Cantidad*VrUnitario)*-1 ELSE 0 END) AS NotValorTotal
,SUM(CASE TipDoc WHEN 'NDB' THEN Cantidad*VrCosto WHEN 'NCR' THEN (Cantidad*VrCosto)*-1 ELSE 0 END) AS NotCostoTotal
,SUM(CASE TipDoc WHEN 'NDB' THEN PesoNeto WHEN 'NCR' THEN PesoNeto*-1 ELSE 0 END) AS NotPesoNeto
,SUM(CASE TipDoc WHEN 'NDB' THEN Volumen WHEN 'NCR' THEN Volumen*-1 ELSE 0 END) AS NotVolumen
,MAX(CASE WHEN SUBSTRING(TipDoc,1,2)='FC' THEN Factura ELSE 0 END) AS NumFactura
,MAX(CASE WHEN SUBSTRING(TipDoc,1,2)='DF' THEN Factura ELSE 0 END) AS NumDevFactura
,MAX(CASE TipDoc WHEN 'NDB' THEN Factura ELSE 0 END) AS NumNotaDb
,MAX(CASE TipDoc WHEN 'NCR' THEN Factura ELSE 0 END) AS NumNotaCr
 FROM Trn_TraFacRemesas GROUP BY TipRem,Remesa,IdCiaRem,ItemRem) AS RF ON RM.TipRem=RF.TipRem AND RM.Remesa=RF.Remesa AND RM.IdCiaRem=RF.IdCiaRem AND RM.ItemRem=RF.ItemRem
 WHERE  RM.TipDoc='MUC' AND M.Fecha BETWEEN @pmFechaIni AND @pmFechaFin AND M.Anulado=0 AND M.IdCia LIKE ISNULL(@pmIdCia,'%%')
  AND  M.IdVehiculo LIKE ISNULL(@pmIdVehiculo,'%') AND M.IdPoseedor LIKE ISNULL(@pmIdPoseedor,'%')
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE paQryTraRemesaMuc @pmFechaIni SMALLDATETIME,@pmFechaFin SMALLDATETIME,@pmIdCia CHAR(2)=Null,@pmIdCliente VARCHAR(16)=Null
 AS 
SELECT R.TipDoc AS CdTipDoc,R.NumOrden AS NumRemesa,R.IdCia AS CdCia,Compania,R.Fecha AS FecRemesa,FecDespacho,IdCliente,T.RazonSocial AS NomCliente,R.IdAgencia AS CdAgencia,Agencia
,R.IdVehiculo AS PlacaVeh,R.IdConductor AS CedConductor,CDT.RazonSocial AS NomConductor,R.TipoAfiVehic AS TipoAfiVeh,R.Modalidad AS TipoRemesa,R.TipDcm AS TipOdc,R.NumDocmto AS nOCargue,R.IdCiaDcm AS CdCiaOdc,R.FechaDcm AS FecOCargue
,EstCumplido,EstFactura,CdConcepto,Concepto,SerieGuia,NumGuia,R.Observacion AS Observ,R.IdEstado AS CdEdstado
--detalles
,Item,IdMercancia,DescripMcias,D.Cantidad AS Cant,PesoNeto,D.UndMed AS CdUmPeso,UMP.Unidad AS UmPeso,D.Volumen AS Volmen,UndVol,D.IdUnd AS CdUnd,UM.Unidad AS UndMedida,Cases,Cajas,Palets
,NitRemite,Remitente,NitDestntario,Destinatario,DirOrigen,IdOrigen,LO.Localidad AS CiuOrigen,LO.IdDep AS CodDepOrigen,DPO.Departamento AS DptoOrigen
,DirDestino,IdDestino,LD.Localidad AS CiuDestino,LD.IdDep AS CodDepDestino,DPD.Departamento AS DptoDestino,TarifClie,D.TarifPago AS RemTarifPago
,D.TarifTabla AS RemTarifTabla,D.VrDeclarado AS ValDeclarado,D.VrSeguro AS ValSeguro,TarifSeguro,UndTarifa,UndTarifPago
,Remision,DocCliente,Referencia1,Referencia2,Referencia3,Cumplido,IdCiaCump,FechaCump,DetalleCump
,CantidadCump,PesoCump,VolCump,CasesCump,CajasCump,PaletsCump,TarifCump,PagoCump,UndTarCump,UndTarPagoCump
--columnas ocultas
--,NumManif,IdCiaManif,TipOdp,NumeroOdp,IdCiaOdp,TarifOdp
--datos del pedido
,NumPedido,IdCiaPed,R.FechaPed AS FecPedido,ModalidadPed
--datos de factura
,TipFac,Factura,IdCiaFac,FechaFac,TarifClieFac,NumFactura,NumDevFact,FacCantidad,FacValorTotal,FacCostoTotal,FacFaltantes,FacPesoNeto
--datos de manifiesto
,TipoMuc,MucNumero,MucCdCia,FechaMuc,FecDespMuc,MucPlacaVeh,MucCdPoseedor,NP.RazonSocial AS NomPoseedor,MucCdRuta,MucTarifTabla,MucTarifPago,MUC.VrFletes AS VrTotFletes,MUC.VrRetencion AS MucVrRetencion
,MUC.VrReteIca AS MucVrReteIca,MUC.VrDescuento AS MucVrDcto,MUC.VrAnticipo AS MucVrAnticipo,VrAntAdic,MUC.TarifaRet AS MucTarifRet,MUC.TarifaIca AS MucTarifIca
,NumMintrans,EdoMintrans,MucObserv,MucTipOdp,MucOrdPago,MucCdCiaOdp
--datos del cliente
,T.TipoId AS TercTipId,T.Dv AS TercDv,T.Direccion AS TercDireccion,T.IdLocal AS TercCdCiudad,L.Localidad AS NomCiudad,T.Telefono AS TercTelefono,T.e_mail AS TercEmail
,CLI.IdSzona AS CdSubzona,Subzona,SZ.IdZona AS CdZona,Zona,CLI.IdGrupo AS CdGrupoCli,GrupoClie
 FROM Trn_TraRemesa AS R INNER JOIN Trn_TraRemMcias AS D ON R.TipDoc=D.TipDoc AND R.NumOrden=D.NumOrden AND R.IdCia=D.IdCia
 INNER JOIN Companias AS CN ON R.IdCia=CN.IdCia  INNER JOIN Terceros AS T ON R.IdCliente=T.IdTercero 
 INNER JOIN Vehiculos AS V ON R.IdVehiculo=V.IdVehiculo INNER JOIN Terceros AS CDT ON R.IdConductor=CDT.IdTercero 
 INNER JOIN Localidades AS L ON T.IdLocal=L.IdLocal
 INNER JOIN TercCliente AS CLI ON R.IdCliente=CLI.IdClie 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 Localidades AS LO ON D.IdOrigen=LO.IdLocal INNER JOIN Departamentos AS DPO ON LO.IdDep=DPO.IdDep
 INNER JOIN Localidades AS LD ON D.IdDestino=LD.IdLocal INNER JOIN Departamentos AS DPD ON LD.IdDep=DPD.IdDep
 INNER JOIN UndMed AS UM ON D.IdUnd=UM.IdUnd LEFT JOIN Sys_Um AS UMP ON D.UndMed=UMP.UndMed
 LEFT JOIN Agencias AS A ON R.IdAgencia=A.IdAgencia LEFT JOIN Conceptos AS C ON R.CdConcepto=C.IdConcepto 
--==== Consulta de pedidos
 LEFT JOIN (SELECT P.TipDoc AS TipoPed,P.Pedido AS nPedido,P.IdCia AS CdCiaPed,P.FecDespacho AS FechaPed,P.IdVend AS CdVend,P.Modalidad AS ModalidadPed
,Cotizacion,IdCiaCot,NumAprob,IdCiaApr,FecAprob,P.Observacion AS PedObserv,D.IdMercancia AS PedCdMcia,D.DescripMcias AS PedDescMcia
,SUM(D.Cantidad) AS PedCantidad,SUM(D.PesoNeto) AS PedPesoNeto,SUM(D.Volumen) AS PedVolumen,SUM(D.Cases) AS PedCases
,SUM(D.Cajas) AS PedCajas,SUM(D.Palets) AS PedPalets,MAX(D.TarifClie) AS PedTarifClie,MAX(D.TarifPago) AS PedTarifPago
 FROM Trn_TraPedido AS P INNER JOIN Trn_TraPedMcias AS D ON P.TipDoc=D.TipDoc AND P.Pedido=D.Pedido AND P.IdCia=D.IdCia
 WHERE P.TipDoc='PDT' AND P.Anulado=0 GROUP BY P.TipDoc,P.Pedido,P.IdCia,P.FecDespacho,P.IdVend,P.Modalidad,Cotizacion,IdCiaCot
,NumAprob,IdCiaApr,FecAprob,P.Observacion,D.IdMercancia,D.DescripMcias) AS PD ON R.NumPedido=PD.nPedido AND R.IdCiaPed=PD.CdCiaPed AND D.IdMercancia=PD.PedCdMcia
--==== consulta de facturas-detalles
 LEFT JOIN (SELECT TipRem,Remesa,IdCiaRem,ItemRem,SUM(CASE WHEN SUBSTRING(TipDoc,1,2)='DF' THEN Cantidad*-1 ELSE Cantidad END) AS FacCantidad
,SUM(CASE WHEN SUBSTRING(TipDoc,1,2)='DF' THEN (Cantidad*VrUnitario)*-1 ELSE Cantidad*VrUnitario END) AS FacValorTotal
,SUM(CASE WHEN SUBSTRING(TipDoc,1,2)='DF' THEN (Cantidad*VrCosto)*-1 ELSE Cantidad*VrCosto END) AS FacCostoTotal
,SUM(CASE WHEN SUBSTRING(TipDoc,1,2)='DF' THEN Unidades*-1 ELSE Unidades END) AS FacUnidades
,SUM(CASE WHEN SUBSTRING(TipDoc,1,2)='DF' THEN PesoNeto*-1 ELSE PesoNeto END) AS FacPesoNeto
,SUM(CASE WHEN SUBSTRING(TipDoc,1,2)='DF' THEN Volumen*-1 ELSE Volumen END) AS FacVolumen
,SUM(CASE WHEN SUBSTRING(TipDoc,1,2)='DF' THEN Cases*-1 ELSE Cases END) AS FacCases
,SUM(CASE WHEN SUBSTRING(TipDoc,1,2)='DF' THEN Cajas*-1 ELSE Cajas END) AS FacCajas
,SUM(CASE WHEN SUBSTRING(TipDoc,1,2)='DF' THEN Palets*-1 ELSE Palets END) AS FacPalets
,SUM(CASE WHEN SUBSTRING(TipDoc,1,2)='DF' THEN VrFaltante*-1 ELSE VrFaltante END) AS FacFaltantes
,MAX(CASE WHEN SUBSTRING(TipDoc,1,2)='DF' THEN 0 ELSE Factura END) AS NumFactura
,MAX(CASE WHEN SUBSTRING(TipDoc,1,2)='DF' THEN Factura ELSE 0 END) AS NumDevFact
FROM Trn_TraFacRemesas GROUP BY TipRem,Remesa,IdCiaRem,ItemRem) AS RF ON R.TipDoc=RF.TipRem AND R.NumOrden=RF.Remesa AND R.IdCia=RF.IdCiaRem AND D.Item=RF.ItemRem
--==== consulta de manifiestos 
 LEFT JOIN (SELECT RM.TipRem AS MucTipoRem,RM.Remesa AS MucNumRemesa,RM.IdCiaRem AS MucCdCiaRem,RM.ItemRem AS MucItemRem,RM.TipDoc AS TipoMuc,RM.Manifiesto AS MucNumero,RM.IdCia AS MucCdCia
,RM.TarifTabla AS MucTarifTabla,RM.TarifPago AS MucTarifPago,RemMintrans,M.Fecha AS FechaMuc,M.FecDespacho AS FecDespMuc,M.IdRuta AS MucCdRuta,M.IdOrigen AS MucCdOrigen,M.IdDestino AS MucCdDestino,M.IdVehiculo AS MucPlacaVeh
,M.IdConductor AS MucCedCond,M.nRemolque AS MucnRemolque,TipoAfiVehic,M.IdPoseedor AS MucCdPoseedor,VrFletes,VrRetencion,VrReteIca,VrDescuento,VrAnticipo,VrAntAdic,VrNeto,VrPagos
,VrCargos,VrDctos,TarifaFlete,TarifaRet,TarifaIca,NumMintrans,EdoMintrans,M.TipOdp AS MucTipOdp,M.OrdPago AS MucOrdPago,M.IdCiaOdp AS MucCdCiaOdp,M.FechaOdp AS MucFecOdp,M.Observacion AS MucObserv
FROM Trn_TraManifRem AS RM INNER JOIN Trn_TraManifiesto AS M ON RM.TipDoc=M.TipDoc AND RM.Manifiesto=M.Manifiesto AND RM.IdCia=M.IdCia
WHERE M.Anulado=0) AS MUC ON R.TipDoc=MUC.MucTipoRem AND R.NumOrden=MUC.MucNumRemesa AND R.IdCia=MUC.MucCdCiaRem AND D.Item=MUC.MucItemRem
--===== Orden de pago
 LEFT JOIN (SELECT M.TipMuc AS OdpTipMuc,M.Manifiesto AS OdpManif,M.IdCiaMuc AS OdpCdCiaMuc,M.TipDoc AS TipOdp,M.OrdPago AS NumOPago,M.IdCia AS OdpCdCia,OP.Fecha AS FechaOdp
,VrTotalFletes,VrDescuento,VrRetencion,VrReteIca,VrAnticipos,VrFaltantes,VrSeguros,VrFondos,VrAportes,VrOtrosDctos,VrImpuestos,VrEstampilla,VrOtrosPagos,VrNeto
,TarifaTabla,TarifaFlete,UnidTarifa,PesoTotal,Unidades,Volumen,TarifaRet,TarifaIca,TipoLiq,TipEgr,Egreso,IdCiaEgr,NumCheque,Referencia,CantFaltante,UnidadFalt,TolFaltNeto,OP.Observacion AS OdpObserv
 FROM Trn_TraOrdenManif AS M INNER JOIN Trn_TraOrdenPago AS OP ON M.TipDoc=OP.TipDoc AND M.OrdPago=OP.OrdPago AND M.IdCia=OP.IdCia
 WHERE OP.Anulado=0) AS ODP ON MUC.MucTipOdp=ODP.TipOdp AND MUC.MucOrdPago=ODP.NumOPago AND MUC.MucCdCiaOdp=ODP.OdpCdCia
 LEFT JOIN Terceros AS NP ON MUC.MucCdPoseedor=NP.IdTercero 
 WHERE R.TipDoc='RMT' AND R.Fecha BETWEEN @pmFechaIni AND  @pmFechaFin AND R.IdCia LIKE ISNULL(@pmIdCia,'%%')
 AND R.IdCliente LIKE ISNULL(@pmIdCliente,'%') AND R.Anulado=0
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE paQryTraRemesaNov  @pmFechaIni SMALLDATETIME,@pmFechaFin SMALLDATETIME,@pmIdCia CHAR(2)=Null
,@pmIdVehiculo VARCHAR(10)=Null,@pmIdCliente VARCHAR(16)=Null,@pmModalidad VARCHAR(10)=Null
 AS 
 SELECT R.TipDoc AS TipRem,R.NumOrden AS NumRemesa,R.IdCia AS CdCia,Compania,Fecha,FecDespacho,FecEntrega,HoraLlegada,IdCliente,NomCliente,T.RazonSocial AS NombreClie
,R.IdAgencia AS CdAgencia,A.Agencia AS NomAgencia,A.CodAgencia AS Cod_Agencia,IdClieFact,IdRemitente,NomRemite,IdDestinatario,NomDestino,IdLocOrigen,CO.Localidad AS CiuOrigen,CO.IdDep AS CodDepOrigen,DPO.Departamento AS DptoOrigen
,IdLocDestino,CD.Localidad AS CiuDestino,CD.IdDep AS CodDepDestino,DPD.Departamento AS DptoDestino,IdLocFletes,CF.Localidad AS LugarFletes,R.IdVehiculo AS PlacaVeh,R.IdConductor AS CedConductor,CDT.RazonSocial AS NomConductor
,nRemolque,TipoAfiVehic,Modalidad,TipoTarifa,VrCobro,VrPagos,VrFletes,VrCargue,VrDesCargue,VrEscolta,VrDevContdor,VrTraUrbano,VrEmbalajes,VrCargos,VrDctos,R.VrDeclarado AS ValDeclarado,R.VrSeguro AS ValSeguro,R.Cantidad AS CantTotal,PesoTotal,Items
,IdMneda,VrTasa,NumPedido,IdCiaPed,FechaPed,TipDcm,NumDocmto,IdCiaDcm,FechaDcm,NumManif,IdCiaManif,EstCumplido,EstFactura,CdConcepto,CR.Concepto AS DesConcRem,CdRutaTarif,TipCom,Comprobante,IdCiaCom,SerieGuia,NumGuia,CdForma,NumCausac,CdCiaCausac,EdoCausac
,Anulado,FecDev,R.Observacion AS Observ,R.IdEstado AS CdEstado,Estado,TimeSys,RA.IdVend AS CdVend,VN.RazonSocial AS Vendedor,LugarCargue,LugarDescargue,NomContacto,TelContacto,emlContacto,ContacDestino,TelContacDest,emlContacDest,ManifMintrans,RemMintrans,TipoRuta,TipoMintrans
--datos de conceptos
,Item,C.Descripcion AS Descrpcion,Tarifa,TipoConc,RubroConcep,C.Cantidad AS CantConc,VrUnitario,TarifIva,C.IdConcepto AS CodConcepto,CDV.Concepto AS DescConcepto,CdCuenta,NomCuenta
,NitTercero,TC.RazonSocial AS NomTercero,CdTipoEsc,TipoEscolta,FechaNov,VrBase,TipoTarif,RefConc
--Datos del cliente
,T.TipoId AS TercTipId,T.Dv AS TercDv,T.Codigo AS TercCodigo,T.NomCial AS TercNomCial,T.Direccion AS TercDireccion,T.IdLocal AS TercCdCiudad,L.Localidad AS NomCiudad,T.Telefono AS TercTelefono,T.e_mail AS TercEmail
,NitRepLeg,NomRepLeg,CLI.IdSzona AS CdSubzona,Subzona,SZ.IdZona AS CdZona,Zona,CLI.IdGrupo AS CdGrupoCli,GrupoClie
--Datos del vehiculo
,NumVeh,V.IdTipoVeh AS CdTipVeh,TipoVehiculo,V.IdMarca AS CdMarca,M.Marca AS MarcaVeh,Modelo,Config,V.Descripcion AS VehDescripcion,V.IdPoseedor AS NitPoseedor
 FROM Trn_TraRemesa AS R INNER JOIN Trn_TraRemAnexo AS RA ON R.TipDoc=RA.TipDoc  AND R.NumOrden=RA.NumOrden AND R.IdCia=RA.IdCia
 INNER JOIN Companias AS CN ON R.IdCia=CN.IdCia  INNER JOIN Terceros AS T ON R.IdCliente=T.IdTercero 
 INNER JOIN Localidades AS L ON T.IdLocal=L.IdLocal
 INNER JOIN TercCliente AS CLI ON R.IdCliente=CLI.IdClie 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 Localidades AS CO ON R.IdLocOrigen=CO.IdLocal INNER JOIN Departamentos AS DPO ON CO.IdDep=DPO.IdDep
 INNER JOIN Localidades AS CD ON R.IdLocDestino=CD.IdLocal INNER JOIN Departamentos AS DPD ON CD.IdDep=DPD.IdDep
 INNER JOIN Localidades AS CF ON R.IdLocFletes=CF.IdLocal  INNER JOIN Terceros AS  CDT ON R.IdConductor=CDT.IdTercero 
 INNER JOIN EstadoDoc AS ED ON R.IdEstado=ED.IdEstado 
 INNER JOIN Vehiculos AS V ON R.IdVehiculo=V.IdVehiculo  INNER JOIN TiposVeh AS TV ON V.IdTipoVeh=TV.IdTipoVeh 
 INNER JOIN Marcas AS M ON V.IdMarca=M.IdMarca 
 INNER JOIN Trn_TraConceptos AS C ON R.TipDoc=C.TipDoc AND R.NumOrden=C.Documento AND R.IdCia=C.IdCia
 LEFT JOIN ConcDiversos AS CDV ON C.IdConcepto=CDV.IdConcepto
 LEFT JOIN TiposEsc AS TE ON C.CdTipoEsc=TE.IdTipoEsc LEFT JOIN Terceros AS TC ON C.NitTercero=TC.IdTercero
 LEFT JOIN Puc AS P ON C.CdCuenta=P.IdCuenta
 LEFT JOIN Agencias AS A ON R.IdAgencia=A.IdAgencia LEFT JOIN Terceros AS VN ON RA.IdVend=VN.IdTercero  
 LEFT JOIN Conceptos AS CR ON R.CdConcepto=CR.IdConcepto 
 WHERE R.TipDoc='RMT'  AND R.Fecha BETWEEN @pmFechaIni AND @pmFechaFin AND R.IdCia LIKE ISNULL(@pmIdCia,'%%')
 AND R.IdVehiculo LIKE ISNULL(@pmIdVehiculo,'%') AND R.IdCliente LIKE ISNULL(@pmIdCliente,'%') AND R.Modalidad LIKE ISNULL(@pmModalidad,'%')
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE paUpCnxextna @pmIdReg INT,@pmCnxEngine VARCHAR(10),@pmCnxDso VARCHAR(250),@pmCnxDbname VARCHAR(50),@pmCnxAutentic VARCHAR(3)
,@pmCnxUid VARCHAR(50),@pmCnxPwd VARCHAR(50),@pmCnxOwner VARCHAR(20),@pmCnxObserv VARCHAR(250),@pmCnxTipo VARCHAR(10),@pmCnxPpties VARCHAR(250)
,@pmCnxDriver VARCHAR(100),@pmCnxPort VARCHAR(30),@pmCnxTerminal VARCHAR(4)
 AS 
 UPDATE Sys_Cnxextna SET CnxEngine=@pmCnxEngine,CnxDso=@pmCnxDso,CnxDbname=@pmCnxDbname,CnxAutentic=@pmCnxAutentic,CnxUid=@pmCnxUid,CnxPwd=@pmCnxPwd
,CnxOwner=@pmCnxOwner,CnxObserv=@pmCnxObserv,CnxTipo=@pmCnxTipo,CnxPpties=@pmCnxPpties,CnxDriver=@pmCnxDriver,CnxPort=@pmCnxPort,CnxTerminal=@pmCnxTerminal
 WHERE IdReg=@pmIdReg
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE paUpDiferidos @pmIdDiferido VARCHAR(30),@pmNomDiferido VARCHAR(250),@pmIdTipDif VARCHAR(4),@pmIdGrupo VARCHAR(4),@pmIdCCosto VARCHAR(16),@pmIdSubCos VARCHAR(16)
,@pmIdDep VARCHAR(4),@pmIdRespons VARCHAR(16),@pmIdCia CHAR(2),@pmFecInicio SMALLDATETIME,@pmValorTotal MONEY,@pmVrAdiciones MONEY,@pmVrDisminucion MONEY,@pmNumPeriodos INT
,@pmFecFinAmtza SMALLDATETIME,@pmAjustable BIT,@pmContable BIT,@pmVrAjuInfAcum MONEY,@pmVrAmtzaAcuHis MONEY,@pmVrAmtzaAcum MONEY,@pmVrAjuInfAmtza MONEY,@pmCuotasAmtza INT
,@pmVrCorrMonDifAcu MONEY,@pmCorrMonDiferida MONEY,@pmNumMesesDif INT,@pmFecUltAmtza SMALLDATETIME,@pmCueEnGrupo BIT,@pmIdCuenta VARCHAR(16),@pmIdCueAmortiza VARCHAR(16)
,@pmIdCueAjuDif VARCHAR(16),@pmIdCueCorrDif VARCHAR(16),@pmIdCueAjuAmtza VARCHAR(16),@pmIdCueCorrAA VARCHAR(16),@pmNumDoc VARCHAR(20),@pmReferencia VARCHAR(50)
,@pmDesUbicacion VARCHAR(150),@pmIdEstado VARCHAR(4),@pmFecRetiro SMALLDATETIME,@pmIdCausal VARCHAR(4),@pmComentRetiro VARCHAR(250),@pmComentarios VARCHAR(250)
,@pmTipoAmtza CHAR(1),@pmNitCliente VARCHAR(16),@pmAgenCliente VARCHAR(16),@pmCantGalones DECIMAL (14,4),@pmIdProv VARCHAR(16),@pmNumPlaca  VARCHAR(20),@pmCodInstala VARCHAR(4),@pmTipoInvers VARCHAR(10)
 AS 
 UPDATE Diferidos SET NomDiferido=@pmNomDiferido,IdTipDif=@pmIdTipDif,IdGrupo=@pmIdGrupo,IdCCosto=@pmIdCCosto,IdSubCos=@pmIdSubCos,IdDep=@pmIdDep
,IdRespons=@pmIdRespons,IdCia=@pmIdCia,FecInicio=@pmFecInicio,ValorTotal=@pmValorTotal,VrAdiciones=@pmVrAdiciones,VrDisminucion=@pmVrDisminucion
,NumPeriodos=@pmNumPeriodos,FecFinAmtza=@pmFecFinAmtza,Ajustable=@pmAjustable,Contable=@pmContable,VrAjuInfAcum=@pmVrAjuInfAcum,VrAmtzaAcuHis=@pmVrAmtzaAcuHis
,VrAmtzaAcum=@pmVrAmtzaAcum,VrAjuInfAmtza=@pmVrAjuInfAmtza,CuotasAmtza=@pmCuotasAmtza,VrCorrMonDifAcu=@pmVrCorrMonDifAcu,CorrMonDiferida=@pmCorrMonDiferida,NumMesesDif=@pmNumMesesDif
,FecUltAmtza=@pmFecUltAmtza,CueEnGrupo=@pmCueEnGrupo,IdCuenta=@pmIdCuenta,IdCueAmortiza=@pmIdCueAmortiza,IdCueAjuDif=@pmIdCueAjuDif,IdCueCorrDif=@pmIdCueCorrDif,IdCueAjuAmtza=@pmIdCueAjuAmtza
,IdCueCorrAA=@pmIdCueCorrAA,NumDoc=@pmNumDoc,Referencia=@pmReferencia,DesUbicacion=@pmDesUbicacion,IdEstado=@pmIdEstado,FecRetiro=@pmFecRetiro,IdCausal=@pmIdCausal
,ComentRetiro=@pmComentRetiro,Comentarios=@pmComentarios,TipoAmtza=@pmTipoAmtza,NitCliente=@pmNitCliente,AgenCliente=@pmAgenCliente,CantGalones=@pmCantGalones,IdProv=@pmIdProv
,NumPlaca=@pmNumPlaca,CodInstala=@pmCodInstala,TipoInvers=@pmTipoInvers
 WHERE IdDiferido=@pmIdDiferido
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE PROCEDURE paUpFinPrestamo @pmTipDoc VARCHAR(3),@pmIdPrestamo INT,@pmIdCia CHAR(2),@pmFecha SMALLDATETIME,@pmFecPrestamo SMALLDATETIME,@pmFecCorte SMALLDATETIME,@pmIdConcepto VARCHAR(4),@pmIdCliente VARCHAR(16),@pmIdAgencia VARCHAR(16),@pmVrPrestamo MONEY,@pmIdTasa VARCHAR(4),@pmNPlazos INT,@pmCausacion VARCHAR(10),@pmIdMora VARCHAR(4)
,@pmNContrato INT,@pmCdCiaCon CHAR(2),@pmNActaJunta INT,@pmNumCredito VARCHAR(20),@pmIdVend VARCHAR(16),@pmpVehiculo VARCHAR(10),@pmCdConductor VARCHAR(16),@pmTipoGarantia VARCHAR(10),@pmVrGarantia MONEY,@pmDescGarantia VARCHAR(250),@pmCxPagar BIT,@pmNumCuotaIni INT,@pmAcumCapital MONEY,@pmAcumIntereses MONEY,@pmAcumIntMora MONEY,@pmAcumCuotas INT,@pmPagosCapital MONEY
,@pmPagosIntereses MONEY,@pmPagosIntMora MONEY,@pmCuotasPagadas INT,@pmCuotasMora INT,@pmTipoAprob VARCHAR(20),@pmTipCom VARCHAR(3),@pmComprobante INT,@pmIdCiaCom CHAR(2),@pmAnulado BIT,@pmNumDev INT,@pmFecDev SMALLDATETIME,@pmObservacion VARCHAR(250),@pmIdEstado VARCHAR(4)
,@pmDiaCausac INT,@pmDiaCausac2 INT,@pmDiasVence VARCHAR(50),@pmIdLinea VARCHAR(4),@pmTipoPlazo CHAR(1),@pmTipoCausac INT,@pmCdTasa2 VARCHAR(4),@pmVrSeguro MONEY,@pmVrIntereses MONEY,@pmVrNeto MONEY,@pmVrCuota MONEY,@pmTipoCredito VARCHAR(10),@pmCalcIntMora BIT,@pmNitEmpConv VARCHAR(16),@pmCuotasConv INT
,@pmRefinanciado INT,@pmNumPrestRef INT,@pmCiaPrestRef CHAR(2),@pmVrRefinanc MONEY,@pmDescRefinanc  VARCHAR(150),@pmCdAlmacen VARCHAR(4),@pmCauAnticipado BIT,@pmFecUpdate SMALLDATETIME
 AS 
 UPDATE Trn_FinPrestamo SET Fecha=@pmFecha,FecPrestamo=@pmFecPrestamo,FecCorte=@pmFecCorte,IdConcepto=@pmIdConcepto,IdCliente=@pmIdCliente,IdAgencia=@pmIdAgencia,VrPrestamo=@pmVrPrestamo,IdTasa=@pmIdTasa,NPlazos=@pmNPlazos,Causacion=@pmCausacion,IdMora=@pmIdMora,NContrato=@pmNContrato,CdCiaCon=@pmCdCiaCon,NActaJunta=@pmNActaJunta,NumCredito=@pmNumCredito,IdVend=@pmIdVend
,pVehiculo=@pmpVehiculo,CdConductor=@pmCdConductor,TipoGarantia=@pmTipoGarantia,VrGarantia=@pmVrGarantia,DescGarantia=@pmDescGarantia,CxPagar=@pmCxPagar,NumCuotaIni=@pmNumCuotaIni,AcumCapital=@pmAcumCapital,AcumIntereses=@pmAcumIntereses,AcumIntMora=@pmAcumIntMora,AcumCuotas=@pmAcumCuotas,PagosCapital=@pmPagosCapital,PagosIntereses=@pmPagosIntereses,PagosIntMora=@pmPagosIntMora,CuotasPagadas=@pmCuotasPagadas
,CuotasMora=@pmCuotasMora,TipoAprob=@pmTipoAprob,TipCom=@pmTipCom,Comprobante=@pmComprobante,IdCiaCom=@pmIdCiaCom,Anulado=@pmAnulado,NumDev=@pmNumDev,FecDev=@pmFecDev,Observacion=@pmObservacion,IdEstado=@pmIdEstado
,CdTasa2=@pmCdTasa2,VrSeguro=@pmVrSeguro,VrIntereses=@pmVrIntereses,VrNeto=@pmVrNeto,VrCuota=@pmVrCuota,FecUpdate=@pmFecUpdate
,DiaCausac=@pmDiaCausac,DiaCausac2=@pmDiaCausac2,DiasVence=@pmDiasVence,IdLinea=@pmIdLinea,TipoPlazo=@pmTipoPlazo,TipoCausac=@pmTipoCausac
,TipoCredito=@pmTipoCredito,CalcIntMora=@pmCalcIntMora,NitEmpConv=@pmNitEmpConv,CuotasConv=@pmCuotasConv
,Refinanciado=@pmRefinanciado,NumPrestRef=@pmNumPrestRef,CiaPrestRef=@pmCiaPrestRef,VrRefinanc=@pmVrRefinanc,DescRefinanc=@pmDescRefinanc,CdAlmacen=@pmCdAlmacen,CauAnticipado=@pmCauAnticipado
 WHERE TipDoc=@pmTipDoc AND IdPrestamo=@pmIdPrestamo 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 paUpNomAlqNov @pmTipoLiq VARCHAR(3),@pmNumero VARCHAR(20),@pmItem INT,@pmIdEmpleado VARCHAR(16),@pmNovIng INT,@pmNovRet INT,@pmNovTde INT,@pmNovTae INT,@pmNovTdp INT,@pmNovTap INT
,@pmNovVsp INT,@pmNovVte INT,@pmNovVst INT,@pmNovSln INT,@pmNovIge INT,@pmNovLma INT,@pmNovVac INT,@pmNovAvp INT,@pmNovVct INT,@pmNovIrp INT,@pmCodNewPen VARCHAR(8),@pmCodNewSal VARCHAR(8)
,@pmCodNewIns VARCHAR(4),@pmCodNewRie VARCHAR(4),@pmCodNewGru VARCHAR(4),@pmDiasNov INT,@pmDiasSan DECIMAL(14,4)
 AS 
 UPDATE Trn_NomAlqNov SET IdEmpleado=@pmIdEmpleado,NovIng=@pmNovIng,NovRet=@pmNovRet,NovTde=@pmNovTde,NovTae=@pmNovTae,NovTdp=@pmNovTdp,NovTap=@pmNovTap,NovVsp=@pmNovVsp,NovVte=@pmNovVte
,NovVst=@pmNovVst,NovSln=@pmNovSln,NovIge=@pmNovIge,NovLma=@pmNovLma,NovVac=@pmNovVac,NovAvp=@pmNovAvp,NovVct=@pmNovVct,NovIrp=@pmNovIrp,CodNewPen=@pmCodNewPen,CodNewSal=@pmCodNewSal
,CodNewIns=@pmCodNewIns,CodNewRie=@pmCodNewRie,CodNewGru=@pmCodNewGru,DiasNov=@pmDiasNov,DiasSan=@pmDiasSan
 WHERE TipoLiq=@pmTipoLiq AND Numero=@pmNumero 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 paUpOComb @pmTipDoc VARCHAR(3),@pmOCargue INT,@pmIdCia CHAR(2),@pmFecha SMALLDATETIME,@pmFechaDesp SMALLDATETIME,@pmFechaVence SMALLDATETIME,@pmIdConcepto VARCHAR(4),@pmIdCliente VARCHAR(16),@pmIdAgencia VARCHAR(16)
,@pmVrSubTotal MONEY,@pmVrDescuento MONEY,@pmVrImpuesto MONEY,@pmVrFletes MONEY,@pmVrOtros MONEY,@pmVrCargos MONEY,@pmVrOtrDcto MONEY,@pmVrSobretasa MONEY,@pmVrImpGlobal MONEY,@pmVrNeto MONEY,@pmCantidad DECIMAL(14,4),@pmIdVend VARCHAR(16)
,@pmTarifaCom DECIMAL(14,4),@pmCodTarCom VARCHAR(4),@pmDirEnvio VARCHAR(250),@pmIdLocal VARCHAR(8),@pmLugarEnv VARCHAR(50),@pmDiasEntraga INT,@pmIdVehiculo VARCHAR(10),@pmIdConductor VARCHAR(16),@pmNitEmpTrans VARCHAR(16),@pmEmpTrans VARCHAR(150)
,@pmCdRuta VARCHAR(4),@pmReferncia VARCHAR(50),@pmIdForma VARCHAR(4),@pmDetallePago VARCHAR(150),@pmMulPlazos BIT,@pmIdPlazo VARCHAR(4),@pmTipPed VARCHAR(3),@pmPedido INT,@pmIdCiaPed CHAR(2),@pmTipGui VARCHAR(3),@pmGuia INT,@pmIdCiaGui CHAR(2),@pmTipFac VARCHAR(3),@pmFactura INT,@pmIdCiaFac CHAR(2)
,@pmFechaFact SMALLDATETIME,@pmAutzaMora INT,@pmAutzaCupo INT,@pmAprobacion INT,@pmFechaAprob SMALLDATETIME,@pmDetalleAprob VARCHAR(250),@pmCdUsuAprob VARCHAR(11),@pmAnulado BIT,@pmFecDev SMALLDATETIME
,@pmObservacion VARCHAR(250),@pmIdEstado VARCHAR(4),@pmZonaFrontera BIT,@pmIdTerminal VARCHAR(4),@pmFecUpdate SMALLDATETIME
 AS 
 UPDATE Trn_OComb SET Fecha=@pmFecha,FechaDesp=@pmFechaDesp,FechaVence=@pmFechaVence,IdConcepto=@pmIdConcepto,IdCliente=@pmIdCliente,IdAgencia=@pmIdAgencia,VrSubTotal=@pmVrSubTotal,VrDescuento=@pmVrDescuento,VrImpuesto=@pmVrImpuesto,VrFletes=@pmVrFletes,VrOtros=@pmVrOtros,VrCargos=@pmVrCargos
,VrOtrDcto=@pmVrOtrDcto,VrSobretasa=@pmVrSobretasa,VrImpGlobal=@pmVrImpGlobal,VrNeto=@pmVrNeto,Cantidad=@pmCantidad,IdVend=@pmIdVend,TarifaCom=@pmTarifaCom,CodTarCom=@pmCodTarCom,DirEnvio=@pmDirEnvio,IdLocal=@pmIdLocal,LugarEnv=@pmLugarEnv,DiasEntraga=@pmDiasEntraga,IdVehiculo=@pmIdVehiculo
,IdConductor=@pmIdConductor,NitEmpTrans=@pmNitEmpTrans,EmpTrans=@pmEmpTrans,CdRuta=@pmCdRuta,Referncia=@pmReferncia,IdForma=@pmIdForma,DetallePago=@pmDetallePago,MulPlazos=@pmMulPlazos,IdPlazo=@pmIdPlazo,TipGui=@pmTipGui,Guia=@pmGuia,IdCiaGui=@pmIdCiaGui,TipFac=@pmTipFac,Factura=@pmFactura
,IdCiaFac=@pmIdCiaFac,FechaFact=@pmFechaFact,TipPed=@pmTipPed,Pedido=@pmPedido,IdCiaPed=@pmIdCiaPed,AutzaMora=@pmAutzaMora,AutzaCupo=@pmAutzaCupo,Aprobacion=@pmAprobacion,FechaAprob=@pmFechaAprob,DetalleAprob=@pmDetalleAprob,CdUsuAprob=@pmCdUsuAprob,Anulado=@pmAnulado
,FecDev=@pmFecDev,Observacion=@pmObservacion,IdEstado=@pmIdEstado,ZonaFrontera=@pmZonaFrontera,IdTerminal=@pmIdTerminal,FecUpdate=@pmFecUpdate
 WHERE TipDoc=@pmTipDoc AND OCargue=@pmOCargue 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 paUpTanques @pmIdTanque VARCHAR(4),@pmIdProducto VARCHAR(16),@pmDescripcion VARCHAR(50),@pmCapTanq DECIMAL(14,4),@pmNivAgua DECIMAL(14,4),@pmCapNeta DECIMAL(14,4)
,@pmSaldoActual DECIMAL(14,4),@pmTnqGas BIT,@pmTnqUnido BIT,@pmTnqVirtual BIT,@pmCdNueTan VARCHAR(4),@pmNColor INT
,@pmFechaUpdate SMALLDATETIME,@pmInactivo BIT,@pmIdCiaDef CHAR(2),@pmCodigoApi VARCHAR(10),@pmCdTerminal VARCHAR(4)
 AS 
 UPDATE Tanques SET IdProducto=@pmIdProducto,Descripcion=@pmDescripcion,CapTanq=@pmCapTanq,NivAgua=@pmNivAgua,CapNeta=@pmCapNeta,SaldoActual=@pmSaldoActual
,TnqGas=@pmTnqGas,TnqUnido=@pmTnqUnido,TnqVirtual=@pmTnqVirtual,CdNueTan=@pmCdNueTan,NColor=@pmNColor,FechaUpdate=@pmFechaUpdate,Inactivo=@pmInactivo
,IdCiaDef =@pmIdCiaDef,CodigoApi=@pmCodigoApi,CdTerminal=@pmCdTerminal
 WHERE IdTanque=@pmIdTanque
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE PROCEDURE paUpTerminales @pmIdTerminal VARCHAR(4),@pmTerminal VARCHAR(50),@pmIdCia CHAR(2),@pmInactivo BIT
 AS 
 UPDATE Terminales SET Terminal=@pmTerminal,IdCia=@pmIdCia,Inactivo=@pmInactivo
 WHERE IdTerminal=@pmIdTerminal
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE paUptm_AportesDet @pmtmEst VARCHAR(5),@pmtmItem INT,@pmtmIdEmpleado VARCHAR(16),@pmtmApellido1 VARCHAR(50),@pmtmApellido2 VARCHAR(50),@pmtmNombre1 VARCHAR(50),@pmtmNombre2 VARCHAR(50),@pmtmNContto INT,@pmtmDiasPen DECIMAL(10,4),@pmtmDiasSal DECIMAL(10,4),@pmtmDiasRie DECIMAL(10,4),@pmtmDiasCom DECIMAL(10,4),@pmtmBasePen MONEY,@pmtmBaseSal MONEY,@pmtmBaseRie MONEY,@pmtmBaseCom MONEY,@pmtmBaseRef MONEY,@pmtmValorPen MONEY,@pmtmValorSal MONEY,@pmtmValorRie MONEY,@pmtmValorCom MONEY,@pmtmValorSena MONEY,@pmtmValorIcbf MONEY,@pmtmValorFsp MONEY,@pmtmValorFsu MONEY,@pmtmValorESAP MONEY,@pmtmValorMin MONEY,@pmtmTarfaPen DECIMAL(10,4),@pmtmTarfaSal DECIMAL(10,4),@pmtmTarfaRie DECIMAL(10,4)
,@pmtmTarfaFsp DECIMAL(10,4),@pmtmTarfaFsu DECIMAL(10,4),@pmtmTarfaEsa DECIMAL(10,4),@pmtmTarfaMin DECIMAL(10,4),@pmtmTarfaCom DECIMAL(10,4),@pmtmTarfaIcb DECIMAL(10,4),@pmtmTarfaSen DECIMAL(10,4),@pmtmVrIncap MONEY,@pmtmVrLicencia MONEY,@pmtmVrVolAfi MONEY,@pmtmVrVolApo MONEY,@pmtmVrNoReten MONEY
,@pmtmVrUpc MONEY,@pmtmVrIncIrp MONEY,@pmtmNAutLic VARCHAR(30),@pmtmNAutInc VARCHAR(30),@pmtmNAutIrp VARCHAR(30),@pmtmIdFonPen VARCHAR(8),@pmtmIdFonSal VARCHAR(8),@pmtmIdFonRie VARCHAR(8),@pmtmIdFonCaj VARCHAR(8),@pmtmIdInstala VARCHAR(4),@pmtmIdDep VARCHAR(4),@pmtmIdCCosto VARCHAR(16),@pmtmIdSubCos VARCHAR(16),@pmtmIdNom VARCHAR(4),@pmtmVehiculo VARCHAR(10),@pmtmVrBasico MONEY
,@pmtmCdFonAvp VARCHAR(8),@pmtmDiasSan DECIMAL(14,4)
 AS 
 UPDATE tm_Aportes SET tmIdEmpleado=@pmtmIdEmpleado,tmApellido1=@pmtmApellido1,tmApellido2=@pmtmApellido2,tmNombre1=@pmtmNombre1,tmNombre2=@pmtmNombre2,tmNContto=@pmtmNContto,tmDiasPen=@pmtmDiasPen,tmDiasSal=@pmtmDiasSal,tmDiasRie=@pmtmDiasRie,tmDiasCom=@pmtmDiasCom,tmBasePen=@pmtmBasePen,tmBaseSal=@pmtmBaseSal,tmBaseRie=@pmtmBaseRie,tmBaseCom=@pmtmBaseCom,tmBaseRef=@pmtmBaseRef,tmValorPen=@pmtmValorPen,tmValorSal=@pmtmValorSal,tmValorRie=@pmtmValorRie,tmValorCom=@pmtmValorCom,tmValorSena=@pmtmValorSena,tmValorIcbf=@pmtmValorIcbf,tmValorFsp=@pmtmValorFsp,tmValorFsu=@pmtmValorFsu,tmValorESAP=@pmtmValorESAP
,tmValorMin=@pmtmValorMin,tmTarfaPen=@pmtmTarfaPen,tmTarfaSal=@pmtmTarfaSal,tmTarfaRie=@pmtmTarfaRie,tmTarfaFsp=@pmtmTarfaFsp,tmTarfaFsu=@pmtmTarfaFsu,tmTarfaEsa=@pmtmTarfaEsa,tmTarfaMin=@pmtmTarfaMin,tmTarfaCom=@pmtmTarfaCom,tmTarfaIcb=@pmtmTarfaIcb,tmTarfaSen=@pmtmTarfaSen,tmVrIncap=@pmtmVrIncap,tmVrLicencia=@pmtmVrLicencia,tmVrVolAfi=@pmtmVrVolAfi,tmVrVolApo=@pmtmVrVolApo,tmVrNoReten=@pmtmVrNoReten,tmVrUpc=@pmtmVrUpc,tmVrIncIrp=@pmtmVrIncIrp,tmNAutLic=@pmtmNAutLic,tmNAutInc=@pmtmNAutInc,tmNAutIrp=@pmtmNAutIrp,tmIdFonPen=@pmtmIdFonPen,tmIdFonSal=@pmtmIdFonSal,tmIdFonRie=@pmtmIdFonRie,tmIdFonCaj=@pmtmIdFonCaj,tmIdInstala=@pmtmIdInstala
,tmIdDep=@pmtmIdDep,tmIdCCosto=@pmtmIdCCosto,tmIdSubCos=@pmtmIdSubCos,tmIdNom=@pmtmIdNom,tmVehiculo=@pmtmVehiculo,tmVrBasico=@pmtmVrBasico,tmCdFonAvp=@pmtmCdFonAvp,tmDiasSan=@pmtmDiasSan
 WHERE tmEst=@pmtmEst AND tmItem=@pmtmItem
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE paUptm_AportesNov @pmtmEst VARCHAR(5),@pmtmItem INT,@pmtmNovIng INT,@pmtmNovRet INT,@pmtmNovTda INT,@pmtmNovTaa INT,@pmtmNovTdp INT,@pmtmNovTap INT
,@pmtmNovVsp INT,@pmtmNovVte INT,@pmtmNovVst INT,@pmtmNovSln INT,@pmtmNovIge INT,@pmtmNovLma INT,@pmtmNovVac INT,@pmtmNovAvp INT,@pmtmNovIrp INT,@pmtmNovVct INT
,@pmtmCodNewEps VARCHAR(8),@pmtmCodNewPen VARCHAR(8),@pmtmCodNewRie VARCHAR(8),@pmtmCodNewIns VARCHAR(4),@pmtmCodNewTar VARCHAR(4),@pmtmCodNewGru VARCHAR(4),@pmtmDiasNov DECIMAL(10,4),@pmtmDiasSan DECIMAL(14,4)
 AS 
 UPDATE tm_Aportes SET tmNovIng=@pmtmNovIng,tmNovRet=@pmtmNovRet,tmNovTda=@pmtmNovTda,tmNovTaa=@pmtmNovTaa,tmNovTdp=@pmtmNovTdp,tmNovTap=@pmtmNovTap,tmNovVsp=@pmtmNovVsp,tmNovVte=@pmtmNovVte,tmNovVst=@pmtmNovVst,tmNovSln=@pmtmNovSln
,tmNovIge=@pmtmNovIge,tmNovLma=@pmtmNovLma,tmNovVac=@pmtmNovVac,tmNovAvp=@pmtmNovAvp,tmNovIrp=@pmtmNovIrp,tmNovVct=@pmtmNovVct,tmCodNewEps=@pmtmCodNewEps,tmCodNewPen=@pmtmCodNewPen,tmCodNewRie=@pmtmCodNewRie,tmCodNewIns=@pmtmCodNewIns
,tmCodNewTar=@pmtmCodNewTar,tmCodNewGru=@pmtmCodNewGru,tmDiasNov=@pmtmDiasNov,tmDiasSan=@pmtmDiasSan
 WHERE tmEst=@pmtmEst AND tmItem=@pmtmItem
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO