if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInsEmp_Contrato]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].[paInsEmp_Contrato] GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInsNomAutoliq]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].[paInsNomAutoliq] GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInsNomFijos]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].[paInsNomFijos] GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInsNomVacDet]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].[paInsNomVacDet] GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInsNomVacDet_Sel]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].[paInsNomVacDet_Sel] GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInstm_Nomina]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].[paInstm_Nomina] GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInstm_Nomina_Liq]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].[paInstm_Nomina_Liq] GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInstm_Nomina_Sel]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].[paInstm_Nomina_Sel] GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInstm_Nomina_Vac]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].[paInstm_Nomina_Vac] GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryEmp_Contrato]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].[paQryEmp_Contrato] GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryEmp_ContratoAnt]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].[paQryEmp_ContratoAnt] GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryEmp_ContratoDso]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].[paQryEmp_ContratoDso] GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryEmp_ContratoEca]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].[paQryEmp_ContratoEca] GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryEmp_ContratoLab]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].[paQryEmp_ContratoLab] GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryNomAutoliq]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].[paQryNomAutoliq] 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].[paQryNomAutoliqDso]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].[paQryNomAutoliqDso] GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryNomFijos]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].[paQryNomFijos] GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryNomFijosLta]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].[paQryNomFijosLta] GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryNomVac_Cr]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].[paQryNomVac_Cr] GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryNomVacDet]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].[paQryNomVacDet] GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryNomVacDetCon]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].[paQryNomVacDetCon] GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryNomVacDet_Cr]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].[paQryNomVacDet_Cr] GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryNomVacDetRes]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].[paQryNomVacDetRes] GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryNomVacIng]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].[paQryNomVacIng] GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQrytm_Nomina]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].[paQrytm_Nomina] GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQrytm_NominaVac]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].[paQrytm_NominaVac] GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paUpEmp_Contrato]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].[paUpEmp_Contrato] GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paUpNomAutoliq]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].[paUpNomAutoliq] GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paUpNomFijos]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].[paUpNomFijos] GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paUpNomVacDet]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].[paUpNomVacDet] GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paUptm_Nomina]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].[paUptm_Nomina] GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInsMayContratos]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].[paInsMayContratos] GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInsPresupuesto]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].[paInsPresupuesto] GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInsTanqDispens]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].[paInsTanqDispens] GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInstm_BalCom]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].[paInstm_BalCom] GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryMayContratos]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].[paQryMayContratos] GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryMayContratos_Cr]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].[paQryMayContratos_Cr] GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryMayContratosAct]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].[paQryMayContratosAct] GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryMayContratosLta]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].[paQryMayContratosLta] GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryPresupuesto]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].[paQryPresupuesto] GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryPresupuesto_Cr]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].[paQryPresupuesto_Cr] GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryPresupuestoMes]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].[paQryPresupuestoMes] GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryPresupuestoPer]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].[paQryPresupuestoPer] GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryTanqDispens]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].[paQryTanqDispens] GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryTanqDispensLta]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].[paQryTanqDispensLta] GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryTanqDispensMan]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].[paQryTanqDispensMan] GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryTanqDispensPro]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].[paQryTanqDispensPro] GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQrytm_BalCom]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].[paQrytm_BalCom] GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paUpMayContratos]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].[paUpMayContratos] GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paUpPresupuesto]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].[paUpPresupuesto] GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paUpTanqDispens]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].[paUpTanqDispens] GO SET QUOTED_IDENTIFIER ON GO SET ANSI_NULLS OFF GO CREATE PROCEDURE paInsEmp_Contrato @pmNContrato INT,@pmIdEmpleado VARCHAR(16),@pmIdTipCon VARCHAR(4),@pmFecIngreso SMALLDATETIME,@pmFecVigencia SMALLDATETIME,@pmFecRetiro SMALLDATETIME,@pmIndefinido BIT,@pmIdCargo VARCHAR(4),@pmIdJornada VARCHAR(4) ,@pmIdDep VARCHAR(4),@pmIdArea VARCHAR(4),@pmIdInstala VARCHAR(4),@pmIdCCosto VARCHAR(16),@pmIdSubCos VARCHAR(16),@pmIdCia CHAR(2),@pmIdNom VARCHAR(4),@pmTipoLiquida CHAR(1),@pmModPromdio CHAR(1),@pmVrSalario MONEY,@pmVrAuxTrans MONEY,@pmSalMinimo BIT,@pmSalIntegral BIT ,@pmFPnomina VARCHAR(10),@pmModoRet INT,@pmFecAfiSeg SMALLDATETIME,@pmATFinMes BIT,@pmSegFinMes BIT,@pmDenyPEN BIT,@pmDenyEPS BIT,@pmDenyARP BIT,@pmDenyCaj BIT,@pmDenyCes BIT,@pmDenyPri BIT,@pmDenyVac BIT,@pmDenyHex BIT,@pmBasePrest CHAR(2) ,@pmBaseSeg CHAR(2),@pmDchDotacion BIT,@pmDchCafeteria BIT,@pmReclmto VARCHAR(50),@pmNotas VARCHAR(250),@pmIdEstado VARCHAR(4),@pmInactivo BIT,@pmIdClase VARCHAR(4),@pmTipoTrabj INT,@pmIdCot VARCHAR(4),@pmVrUpc MONEY,@pmIdGrupo VARCHAR(4) ,@pmDenyDcto BIT,@pmSubTipoCot VARCHAR(3),@pmFecAdd SMALLDATETIME,@pmIdUsuario VARCHAR(11) AS INSERT INTO Emp_Contrato (NContrato,IdEmpleado,IdTipCon,FecIngreso,FecVigencia,FecRetiro,Indefinido,IdCargo,IdJornada,IdDep,IdArea,IdInstala,IdCCosto,IdCia,IdNom,TipoLiquida,ModPromdio,VrSalario,VrAuxTrans,SalMinimo,SalIntegral,FPnomina,ModoRet,FecAfiSeg,ATFinMes,SegFinMes,DenyPEN,DenyEPS ,DenyARP,DenyCaj,DenyCes,DenyPri,DenyVac,DenyHex,BasePrest,BaseSeg,DchDotacion,DchCafeteria,Reclmto,Notas,IdEstado,Inactivo,IdSubCos,IdClase,TipoTrabj,FecAdd,IdUsuario,IdCot,VrUpc,IdGrupo,DenyDcto,SubTipoCot) VALUES (@pmNContrato,@pmIdEmpleado,@pmIdTipCon,@pmFecIngreso,@pmFecVigencia,@pmFecRetiro,@pmIndefinido,@pmIdCargo,@pmIdJornada,@pmIdDep,@pmIdArea,@pmIdInstala,@pmIdCCosto,@pmIdCia,@pmIdNom,@pmTipoLiquida,@pmModPromdio,@pmVrSalario,@pmVrAuxTrans,@pmSalMinimo ,@pmSalIntegral,@pmFPnomina,@pmModoRet,@pmFecAfiSeg,@pmATFinMes,@pmSegFinMes,@pmDenyPEN,@pmDenyEPS,@pmDenyARP,@pmDenyCaj,@pmDenyCes,@pmDenyPri,@pmDenyVac,@pmDenyHex,@pmBasePrest,@pmBaseSeg,@pmDchDotacion,@pmDchCafeteria,@pmReclmto,@pmNotas ,@pmIdEstado,@pmInactivo,@pmIdSubCos,@pmIdClase,@pmTipoTrabj,@pmFecAdd,@pmIdUsuario,@pmIdCot,@pmVrUpc,@pmIdGrupo,@pmDenyDcto,@pmSubTipoCot) GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS OFF GO CREATE PROCEDURE paInsNomAutoliq @pmTipoLiq VARCHAR(3),@pmNumero VARCHAR(20),@pmFecha SMALLDATETIME,@pmPerAutoliq VARCHAR(6),@pmPerAutoSal VARCHAR(6),@pmFechaPago SMALLDATETIME,@pmIdAportnte VARCHAR(16),@pmPresentacion CHAR(1),@pmCodCia CHAR(2),@pmCodFondo VARCHAR(8),@pmCodArp VARCHAR(8),@pmCodOper VARCHAR(10),@pmEsCorr BIT,@pmPerCorr VARCHAR(6) ,@pmNumCorr VARCHAR(20),@pmTotalEmp INT,@pmVrNomina MONEY,@pmVrPension MONEY,@pmVrSalud MONEY,@pmVrRiesgos MONEY,@pmVrCajas MONEY,@pmVrSena MONEY,@pmVrIcbf MONEY,@pmVrEsap MONEY,@pmVrMinEduc MONEY,@pmTotalAfipen INT,@pmTotalAfiSal INT,@pmTotalAfiRie INT,@pmTotalAfiCaj INT,@pmTotalAfiSen INT,@pmTotalAfiIcb INT,@pmTotalAfiEsa INT,@pmTotalAfiEduc INT,@pmTotAdmPen INT ,@pmTotAdmSal INT,@pmTotAdmRie INT,@pmTotAdmCom INT,@pmObservacion VARCHAR(250),@pmLogAdmon VARCHAR(250),@pmRedoMiles BIT,@pmAnulado BIT,@pmIdEstado VARCHAR(4),@pmTimeSys SMALLDATETIME,@pmIdUsuario VARCHAR(11),@pmTipoPlanilla VARCHAR(3) AS INSERT INTO Trn_NomAutoliq (TipoLiq,Numero,Fecha,PerAutoliq,PerAutoSal,FechaPago,IdAportnte,Presentacion,CodCia,CodFondo,CodArp,CodOper,EsCorr,PerCorr,NumCorr,TotalEmp,VrNomina,VrPension,VrSalud,VrRiesgos,VrCajas,VrSena,VrIcbf,VrEsap,VrMinEduc,TotalAfipen,TotalAfiSal,TotalAfiRie,TotalAfiCaj,TotalAfiSen,TotalAfiIcb,TotalAfiEsa,TotalAfiEduc,TotAdmPen,TotAdmSal,TotAdmRie,TotAdmCom,Observacion,LogAdmon,RedoMiles,Anulado,IdEstado,TimeSys,IdUsuario,TipoPlanilla) VALUES (@pmTipoLiq,@pmNumero,@pmFecha,@pmPerAutoliq,@pmPerAutoSal,@pmFechaPago,@pmIdAportnte,@pmPresentacion,@pmCodCia,@pmCodFondo,@pmCodArp,@pmCodOper,@pmEsCorr,@pmPerCorr,@pmNumCorr,@pmTotalEmp,@pmVrNomina,@pmVrPension,@pmVrSalud,@pmVrRiesgos,@pmVrCajas,@pmVrSena,@pmVrIcbf,@pmVrEsap,@pmVrMinEduc,@pmTotalAfipen,@pmTotalAfiSal,@pmTotalAfiRie,@pmTotalAfiCaj,@pmTotalAfiSen,@pmTotalAfiIcb ,@pmTotalAfiEsa,@pmTotalAfiEduc,@pmTotAdmPen,@pmTotAdmSal,@pmTotAdmRie,@pmTotAdmCom,@pmObservacion,@pmLogAdmon,@pmRedoMiles,@pmAnulado,@pmIdEstado,@pmTimeSys,@pmIdUsuario,@pmTipoPlanilla) GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_NULLS OFF GO CREATE PROCEDURE paInsNomFijos @pmIdEmpleado VARCHAR(16),@pmnAnno INT,@pmnMes INT,@pmTarifa DECIMAL(10,4),@pmVrBase MONEY,@pmVrSalarios MONEY,@pmVrPrima MONEY,@pmVrVacacnes MONEY,@pmVrSIntExcto MONEY ,@pmVrOtrosIng MONEY,@pmVrIngresos MONEY,@pmVrPensiones MONEY,@pmVrDedcido MONEY,@pmVrExcento MONEY,@pmVrCertEdu MONEY,@pmVrCertVda MONEY,@pmVrCertAFC MONEY,@pmVrCertfcado MONEY,@pmPeriodos DECIMAL(10,4),@pmVrTotalBase MONEY ,@pmOrignPorc VARCHAR(10),@pmFecAdd SMALLDATETIME,@pmIdUsuario VARCHAR(11),@pmVrSalud MONEY AS INSERT INTO Trn_NomFijos (IdEmpleado,nAnno,nMes,Tarifa,VrBase,VrSalarios,VrPrima,VrVacacnes,VrSIntExcto,VrOtrosIng,VrIngresos,VrPensiones,VrDedcido,VrExcento,VrCertEdu,VrCertVda,VrCertAFC,VrCertfcado,Periodos,VrTotalBase,OrignPorc,FecAdd,IdUsuario,VrSalud) VALUES (@pmIdEmpleado,@pmnAnno,@pmnMes,@pmTarifa,@pmVrBase,@pmVrSalarios,@pmVrPrima,@pmVrVacacnes,@pmVrSIntExcto,@pmVrOtrosIng,@pmVrIngresos,@pmVrPensiones,@pmVrDedcido,@pmVrExcento,@pmVrCertEdu,@pmVrCertVda,@pmVrCertAFC ,@pmVrCertfcado,@pmPeriodos,@pmVrTotalBase,@pmOrignPorc,@pmFecAdd,@pmIdUsuario,@pmVrSalud) GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS OFF GO CREATE PROCEDURE paInsNomVacDet @pmIdEmpleado VARCHAR(16),@pmNumero INT,@pmItem INT,@pmIdConcepto VARCHAR(4),@pmDetalle VARCHAR(100),@pmCantidad INT,@pmVrDevgado MONEY,@pmVrDeducido MONEY ,@pmTarifa DECIMAL(10,4),@pmVrBaseLiq MONEY,@pmVrCertfcado MONEY,@pmVrPension MONEY,@pmNPrestamo INT,@pmNCuota INT,@pmIdPeriodo VARCHAR(8),@pmVrSalud MONEY AS INSERT INTO Trn_NomVacDet (IdEmpleado,Numero,Item,IdConcepto,Detalle,Cantidad,VrDevgado,VrDeducido,Tarifa,VrBaseLiq,VrCertfcado,VrPension,NPrestamo,NCuota,IdPeriodo,VrSalud) VALUES (@pmIdEmpleado,@pmNumero,@pmItem,@pmIdConcepto,@pmDetalle,@pmCantidad,@pmVrDevgado,@pmVrDeducido,@pmTarifa,@pmVrBaseLiq,@pmVrCertfcado,@pmVrPension,@pmNPrestamo,@pmNCuota,@pmIdPeriodo,@pmVrSalud) GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS OFF GO CREATE PROCEDURE paInsNomVacDet_Sel @pmIdEmpleado VARCHAR(16),@pmNewCedula VARCHAR(16) AS INSERT INTO Trn_NomVacDet (IdEmpleado,Numero,Item,IdConcepto,Detalle,Cantidad,VrDevgado,VrDeducido,Tarifa,VrBaseLiq,VrCertfcado,VrPension,NPrestamo,NCuota,IdPeriodo,VrSalud) SELECT @pmNewCedula,Numero,Item,IdConcepto,Detalle,Cantidad,VrDevgado,VrDeducido,Tarifa,VrBaseLiq,VrCertfcado,VrPension,NPrestamo,NCuota,IdPeriodo,VrSalud FROM Trn_NomVacDet WHERE IdEmpleado=@pmIdEmpleado GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS OFF GO CREATE PROCEDURE paInstm_Nomina @pmtmNumero VARCHAR(5),@pmtmIdEmpleado VARCHAR(16),@pmtmItem INT,@pmtmIdConcepto VARCHAR(4),@pmtmClaseCon VARCHAR(10),@pmtmDetalle VARCHAR(100),@pmtmCantDevg DECIMAL(10,4),@pmtmCantDed DECIMAL(10,4),@pmtmVrUnitario MONEY,@pmtmVrTotDevg MONEY,@pmtmVrTotDed MONEY ,@pmtmUnidad VARCHAR(10),@pmtmClaseLiq VARCHAR(10),@pmtmVrOrigen MONEY,@pmtmTarifa DECIMAL(10,4),@pmtmDiasCalc DECIMAL(10,4),@pmtmDiasNov DECIMAL(10,4),@pmtmCodFondo VARCHAR(8),@pmtmNPrestamo INT,@pmtmNCuota INT,@pmtmOrigCargue VARCHAR(10),@pmtmVrBaseLiq MONEY,@pmtmNContto INT,@pmtmFecIni SMALLDATETIME ,@pmtmFecFin SMALLDATETIME,@pmtmIdPeriodo VARCHAR(8),@pmtmFecUltLiq SMALLDATETIME,@pmtmFecIniPmd SMALLDATETIME,@pmtmFecFinPmd SMALLDATETIME,@pmtmLogPmdio VARCHAR(600),@pmtmVrPension MONEY,@pmtmVrSalud MONEY AS INSERT INTO tm_Nomina (tmNumero,tmIdEmpleado,tmItem,tmIdConcepto,tmClaseCon,tmDetalle,tmCantDevg,tmCantDed,tmVrUnitario,tmVrTotDevg,tmVrTotDed,tmUnidad,tmClaseLiq,tmVrOrigen,tmTarifa,tmDiasCalc,tmDiasNov,tmCodFondo,tmNPrestamo,tmNCuota,tmOrigCargue,tmVrBaseLiq,tmNContto,tmFecIni,tmFecFin,tmIdPeriodo,tmFecUltLiq,tmFecIniPmd,tmFecFinPmd,tmLogPmdio,tmVrPension,tmVrSalud) VALUES (@pmtmNumero,@pmtmIdEmpleado,@pmtmItem,@pmtmIdConcepto,@pmtmClaseCon,@pmtmDetalle,@pmtmCantDevg,@pmtmCantDed,@pmtmVrUnitario,@pmtmVrTotDevg,@pmtmVrTotDed,@pmtmUnidad,@pmtmClaseLiq,@pmtmVrOrigen,@pmtmTarifa ,@pmtmDiasCalc,@pmtmDiasNov,@pmtmCodFondo,@pmtmNPrestamo,@pmtmNCuota,@pmtmOrigCargue,@pmtmVrBaseLiq,@pmtmNContto,@pmtmFecIni,@pmtmFecFin,@pmtmIdPeriodo,@pmtmFecUltLiq,@pmtmFecIniPmd,@pmtmFecFinPmd,@pmtmLogPmdio,@pmtmVrPension,@pmtmVrSalud) GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS OFF GO CREATE PROCEDURE paInstm_Nomina_Liq @pmIdEmpleado VARCHAR(16),@pmNumero INT,@pmtmNumero VARCHAR(5) AS INSERT INTO tm_Nomina (tmNumero,tmItem,tmIdEmpleado,tmIdConcepto,tmClaseCon,tmDetalle,tmCantDevg,tmCantDed,tmVrUnitario,tmVrTotDevg,tmVrTotDed,tmUnidad ,tmClaseLiq,tmVrOrigen,tmTarifa,tmDiasCalc,tmDiasNov,tmCodFondo,tmNPrestamo,tmNCuota,tmOrigCargue,tmVrBaseLiq,tmNContto,tmFecIni,tmFecFin,tmIdPeriodo,tmFecUltLiq ,tmFecIniPmd,tmFecFinPmd,tmLogPmdio,tmVrPension,tmVrSalud) SELECT @pmtmNumero,Item,D.IdEmpleado,D.IdConcepto,ClaseCon,Detalle ,'CntDev'= CASE WHEN VrDeducido<=0 THEN Cantidad ELSE 0 END ,'CntDed'= CASE WHEN VrDeducido>0 THEN Cantidad ELSE 0 END ,VrUnitario,VrDevngado,VrDeducido,Unidad,ClaseLiq,VrBase,D.Tarifa,DiasBase,D.DiasNoRem,CodFondo ,NPrestamo,NCuota,'LIQUDACION',VrBaseLiq,NContrato,FecDesde,FecHasta,IdPeriodo,FecUltLiq,FecIniPmd,FecFinPmd,LogPmdio,0,0 FROM Trn_NomRetDet AS D INNER JOIN Trn_NomRet AS R ON D.IdEmpleado=R.IdEmpleado AND D.Numero=R.Numero INNER JOIN NomConceptos AS C ON D.IdConcepto=C.IdConcepto WHERE D.IdEmpleado=@pmIdEmpleado AND D.Numero=@pmNumero AND ClaseLiq NOT IN ('APORTE','PROVISION') GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS OFF GO CREATE PROCEDURE paInstm_Nomina_Sel @pmIdPeriodo VARCHAR(8),@pmKeyRegistro VARCHAR(30),@pmtmNumero VARCHAR(5) AS INSERT INTO tm_Nomina (tmNumero,tmItem,tmIdEmpleado,tmIdConcepto,tmClaseCon,tmDetalle,tmCantDevg,tmCantDed,tmVrUnitario,tmVrTotDevg,tmVrTotDed,tmUnidad ,tmClaseLiq,tmVrOrigen,tmTarifa,tmDiasCalc,tmDiasNov,tmCodFondo,tmNPrestamo,tmNCuota,tmOrigCargue,tmVrBaseLiq,tmNContto,tmFecIni,tmFecFin,tmIdPeriodo,tmVrPension,tmVrSalud) SELECT @pmtmNumero,Item,D.IdEmpleado,D.IdConcepto,ClaseCon,Detalle,CantDevg,CantDed,VrUnitario,VrTotDevg,VrTotDed,D.Unidad,ClaseLiq,VrOrigen,D.Tarifa,DiasCalc ,DiasNov,CodFondo,NPrestamo,NCuota,OrigCargue,VrBaseLiq,NContrato,FecInicial,FecFinal,D.IdPeriodo,0,0 FROM Trn_NomDetalle AS D INNER JOIN NomConceptos AS C ON D.IdConcepto=C.IdConcepto INNER JOIN Trn_Nomina AS N ON D.IdPeriodo=N.IdPeriodo AND D.KeyRegistro=N.KeyRegistro WHERE D.IdPeriodo=@pmIdPeriodo AND D.KeyRegistro=@pmKeyRegistro AND ClaseLiq IN ('NOMINA','NOVEDAD') GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS OFF GO CREATE PROCEDURE paInstm_Nomina_Vac @pmIdEmpleado VARCHAR(16),@pmNumero INT,@pmtmNumero VARCHAR(5) AS INSERT INTO tm_Nomina (tmNumero,tmItem,tmIdEmpleado,tmIdConcepto,tmClaseCon,tmDetalle,tmCantDevg,tmCantDed,tmVrUnitario,tmVrTotDevg,tmVrTotDed,tmUnidad ,tmClaseLiq,tmVrOrigen,tmTarifa,tmDiasCalc,tmDiasNov,tmCodFondo,tmNPrestamo,tmNCuota,tmOrigCargue,tmVrBaseLiq,tmNContto,tmFecIni,tmFecFin,tmIdPeriodo,tmVrPension,tmVrSalud) SELECT @pmtmNumero,Item,IdEmpleado,D.IdConcepto,ClaseCon,Detalle,0,Cantidad,0,VrDevgado,VrDeducido,Unidad,'NOMINA',VrBaseLiq,D.Tarifa,0,0,'',NPrestamo,NCuota,'VAC',VrCertfcado ,0,getdate(),getdate(),IdPeriodo,VrPension,VrSalud FROM Trn_NomVacDet AS D INNER JOIN NomConceptos AS C ON D.IdConcepto=C.IdConcepto WHERE IdEmpleado=@pmIdEmpleado AND Numero=@pmNumero GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_NULLS OFF GO CREATE PROCEDURE paQryEmp_Contrato @pmNContrato INT AS SELECT NContrato,IdEmpleado,IdTipCon,FecIngreso,FecVigencia,FecRetiro,Indefinido,IdCargo,IdJornada,IdDep,IdArea,IdInstala,IdCCosto,IdSubCos,IdCia,IdNom ,IdClase,TipoTrabj,IdCot,IdGrupo,VrUpc,TipoLiquida,ModPromdio,VrSalario,VrAuxTrans,SalMinimo,SalIntegral,FPnomina,ModoRet,FecAfiSeg,ATFinMes,SegFinMes,DenyPEN,DenyEPS,DenyARP ,DenyCaj,DenyCes,DenyPri,DenyVac,DenyHex,DenyDcto,BasePrest,BaseSeg,DchDotacion,DchCafeteria,Reclmto,Notas,IdEstado,Inactivo,SubTipoCot,FecAdd,FecUpdate,IdUsuario FROM Emp_Contrato WHERE NContrato=@pmNContrato GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_NULLS OFF GO CREATE PROCEDURE paQryEmp_ContratoAnt @pmIdEmpleado VARCHAR(16),@pmNContratoExc INT AS SELECT NContrato,IdEmpleado,IdTipCon,FecIngreso,FecVigencia,FecRetiro,Indefinido,IdCargo,IdJornada,IdDep,IdArea,IdInstala,IdCCosto,IdSubCos,IdCia,IdNom ,TipoTrabj,IdCot,IdGrupo,VrUpc,TipoLiquida,ModPromdio,VrSalario,VrAuxTrans,SalMinimo,SalIntegral,FPnomina,ModoRet,FecAfiSeg,ATFinMes,SegFinMes,DenyPEN,DenyEPS,DenyARP ,DenyCaj,DenyCes,DenyPri,DenyVac,DenyHex,DenyDcto,BasePrest,BaseSeg,DchDotacion,DchCafeteria,Reclmto,Notas,IdEstado,Inactivo,SubTipoCot,FecAdd,FecUpdate,IdUsuario FROM Emp_Contrato WHERE IdEmpleado=@pmIdEmpleado AND NContrato<>@pmNContratoExc ORDER BY FecIngreso,NContrato GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_NULLS OFF GO CREATE PROCEDURE paQryEmp_ContratoDso @pmIdEmpleado VARCHAR(16)=Null,@pmIndefinido BIT=Null,@pmIdTipCon VARCHAR(4)=Null,@pmIdCargo VARCHAR(4)=Null,@pmIdDep VARCHAR(4)=Null ,@pmIdArea VARCHAR(4)=Null,@pmIdInstala VARCHAR(4)=Null,@pmIdCCosto VARCHAR(16)=Null,@pmIdSubCos VARCHAR(16)=Null,@pmIdNom VARCHAR(4)=Null,@pmTipoLiquida CHAR(1)=Null ,@pmSalMinimo BIT=Null,@pmSalIntegral BIT=Null,@pmFPnomina VARCHAR(10)=Null,@pmModoRet INT=Null,@pmIdClase VARCHAR(4)=Null,@pmInactivo BIT=Null,@pmIdEstado VARCHAR(4)=Null ,@pmNContrato INT=Null,@pmFecIngresoIni SMALLDATETIME=Null,@pmFecIngresoFin SMALLDATETIME=Null,@pmATFinMes BIT=Null,@pmSegFinMes BIT=Null,@pmDchDotacion BIT=Null --parametros de empleados ,@pmTipoSangre CHAR(2)=Null,@pmFactorRh CHAR(1)=Null,@pmSexo CHAR(1)=Null,@pmIdEstCivil VARCHAR(4)=Null,@pmClaseLib INT=Null, @pmIdProf VARCHAR(4)=Null,@pmTallaCam CHAR(2)=Null ,@pmTallaPan CHAR(2)=Null,@pmTallaZap CHAR(2)=Null,@pmIdFonPen VARCHAR(8)=Null,@pmIdFonEps VARCHAR(8)=Null,@pmIdFonArp VARCHAR(8)=Null,@pmIdFonCes VARCHAR(8)=Null,@pmIdCajaCom VARCHAR(8)=Null ,@pmIdBanco VARCHAR(4)=Null,@pmEmp_IdEstado VARCHAR(4)=Null,@pmEmp_Inactivo BIT=Null,@pmEsCondtor BIT=Null,@pmFecNacmtoIni SMALLDATETIME=Null,@pmFecNacmtoFin SMALLDATETIME=Null AS SELECT C.IdEmpleado AS IdEmplado,Apellidos,Nombres,C.NContrato AS NCntrato,C.IdTipCon AS CodTCon,TipoContrato,FecIngreso,FecVigencia,FecRetiro,Indefinido,C.IdCargo AS CodCargo,Cargo ,C.IdJornada AS IdJrnada,Jornada,C.IdArea AS CodArea,Area,C.IdDep AS CodDep,Dependencia,C.IdInstala AS IdInstla,Instlacion,C.IdCCosto AS IdCCto,CCosto,C.IdSubCos AS IdSubcen,SubCosto,C.IdCia AS CodCia,Compania ,C.IdClase AS CodClaRie,ClaseRiesgo,Tarifa,TipoTrabj,C.IdNom AS CodNom,TipoNomina,C.TipoLiquida AS TipoLiq,ModPromdio,VrSalario,VrAuxTrans,SalMinimo,SalIntegral,FPnomina,ModoRet,FecAfiSeg,ATFinMes,SegFinMes,DenyPEN,DenyEPS,DenyARP ,DenyCaj,DenyCes,DenyPri,DenyVac,DenyHex,DenyDcto,BasePrest,BaseSeg,DchDotacion,DchCafeteria,Reclmto,Notas,C.IdEstado AS IdEstdCon,EC.Estado AS EstdoCon,EC.NColor AS ConEstcolor,C.Inactivo AS Inactvo,C.FecAdd AS Fec_Add,C.FecUpdate AS Fec_Update ,C.IdUsuario AS IdUsuari,U.Usuario AS Con_Usuario,IdCot,C.IdGrupo AS GrupAR,VrUpc --empleados ,E.Direccion AS Dirccion,E.IdLocal AS CodCiu,L.Localidad AS Ciudad,Telefono,TelMovil,e_mail,VvdaPropia,FecNacmto,LugarNacmto,TipoSangre,FactorRh,Sexo,IdEstCivil,EstCivil,NHijos,TallaCam,TallaPan,TallaZap,UsaGafas,E.IdProf AS CodProf,Profesion ,LibMilitar,ClaseLib,FechaLib,DistMil,CertJudicial,FecJudicial,VigJudicial,Pasporte,VigPaspte,Licencia,E.IdLugar AS CodLugar,LugarLic,CatLicencia,FecLicencia,VigLicencia,IdLugarCed,LC.Localidad AS LugarCed,Conyugue,ConyFecNac,ConyTrabaja ,ConyEmpresa,ConyCargo,ConyFing,ConyTel,E.IdClase AS CodClase,ClaseCuenta,NumCuenta,E.IdBanco AS CodBco,Banco,IdFonPen,FP.Fondo AS FondPen,IdFonEps,FS.Fondo AS FondEps,IdFonArp,FR.Fondo AS FondArp,IdFonCes,FC.Fondo AS FondCes ,IdCajaCom,CC.Fondo AS CajaComp,FecVincula,FecUltAum,FecUltCes,FecUltPri,FecUltVac,FecUltDot,FecUltExm,FecUltCap,NCAnterior,FecIngAnt,FecRetAnt,EsCondtor,pVehiculo,FotoEmp,FotoLic,FotoFirma,Observacion,E.IdEstado AS Emp_IdEstado ,ET.Estado AS Emp_estado,E.Inactivo AS Emp_Inactivo,TipoId,Codigo,ET.NColor AS EmpEstcolor,E.FechaAdd AS EmpFecAdd,E.FechaUpdate AS EmpFecupd,E.IdUsuario AS Emp_IdUsuario,UE.Usuario AS Emp_Usuario,SubTipoCot FROM Emp_Contrato AS C INNER JOIN Empleados AS E ON C.IdEmpleado=E.IdEmpleado INNER JOIN TiposCon AS TC ON C.IdTipCon=TC.IdTipCon INNER JOIN Cargos AS CG ON C.IdCargo=CG.IdCargo INNER JOIN Jornadas AS J ON C.IdJornada=J.IdJornada INNER JOIN Dependencias AS D ON C.IdDep=D.IdDep INNER JOIN Areas AS AR ON C.IdArea=AR.IdArea INNER JOIN Instalaciones AS I ON C.IdInstala=I.IdInstala INNER JOIN CentroCosto AS CO ON C.IdCCosto=CO.IdCCosto INNER JOIN SubCentros AS SC ON C.IdSubCos=SC.IdSubCos INNER JOIN Companias AS CI ON C.IdCia=CI.IdCia INNER JOIN TiposNom AS TN ON C.IdNom=TN.IdNom INNER JOIN EstadoDoc AS EC ON C.IdEstado=EC.IdEstado INNER JOIN adm_Usuarios AS U ON C.IdUsuario=U.IdUsuario INNER JOIN ClaseRie AS CR ON C.IdClase=CR.IdClase INNER JOIN Localidades AS L ON E.IdLocal=L.IdLocal INNER JOIN Localidades AS LC ON E.IdLugarCed=LC.IdLocal INNER JOIN EstadoCiv AS ECV ON E.IdEstCivil=ECV.IdEstado INNER JOIN ExpLicencias AS LL ON E.IdLugar=LL.IdLugar INNER JOIN Profesiones AS P ON E.IdProf=P.IdProf INNER JOIN Fondos AS FP ON E.IdFonPen=FP.IdFondo INNER JOIN Fondos AS FS ON E.IdFonEps=FS.IdFondo INNER JOIN Fondos AS FR ON E.IdFonArp=FR.IdFondo INNER JOIN Fondos AS FC ON E.IdFonCes=FC.IdFondo INNER JOIN Fondos AS CC ON E.IdCajaCom=CC.IdFondo INNER JOIN ClaseCta AS CT ON E.IdClase=CT.IdClase INNER JOIN Bancos AS B ON E.IdBanco=B.IdBanco INNER JOIN EstadoTer AS ET ON E.IdEstado=ET.IdEstado INNER JOIN adm_Usuarios AS UE ON E.IdUsuario=UE.IdUsuario WHERE C.IdEmpleado LIKE ISNULL(@pmIdEmpleado,'%') AND C.IdTipCon LIKE ISNULL(@pmIdTipCon,'%') AND C.IdCargo LIKE ISNULL(@pmIdCargo,'%') AND C.IdDep LIKE ISNULL(@pmIdDep,'%') AND C.IdArea LIKE ISNULL(@pmIdArea,'%') AND C.IdInstala LIKE ISNULL(@pmIdInstala,'%') AND C.IdCCosto LIKE ISNULL(@pmIdCCosto,'%') AND C.IdSubCos LIKE ISNULL(@pmIdSubCos,'%') AND C.IdNom LIKE ISNULL(@pmIdNom,'%') AND C.TipoLiquida LIKE ISNULL(@pmTipoLiquida,'%') AND FPnomina LIKE ISNULL(@pmFPnomina,'%') AND C.IdClase LIKE ISNULL(@pmIdClase,'%') AND C.IdEstado LIKE ISNULL(@pmIdEstado,'%') AND (C.NContrato>=ISNULL(@pmNContrato,0) AND C.NContrato<=ISNULL(@pmNContrato,2147483647)) AND (ModoRet>=ISNULL(@pmModoRet,0) AND ModoRet<=ISNULL(@pmModoRet,2147483647)) AND (SalMinimo=ISNULL(@pmSalMinimo,0) or SalMinimo=ISNULL(@pmSalMinimo,1)) AND (Indefinido=ISNULL(@pmIndefinido,0) or Indefinido=ISNULL(@pmIndefinido,1)) AND (SalIntegral=ISNULL(@pmSalIntegral,0) or SalIntegral=ISNULL(@pmSalIntegral,1)) AND (C.Inactivo=ISNULL(@pmInactivo,0) or C.Inactivo=ISNULL(@pmInactivo,1)) AND (ATFinMes=ISNULL(@pmATFinMes,0) or ATFinMes=ISNULL(@pmATFinMes,1)) AND (SegFinMes=ISNULL(@pmSegFinMes,0) or SegFinMes=ISNULL(@pmSegFinMes,1)) AND (DchDotacion=ISNULL(@pmDchDotacion,0) or DchDotacion=ISNULL(@pmDchDotacion,1)) AND (FecIngreso>=ISNULL(@pmFecIngresoIni,CAST('19100101' AS SMALLDATETIME)) AND FecIngreso<=ISNULL(@pmFecIngresoFin,CAST('20781230' AS SMALLDATETIME))) AND TipoSangre LIKE ISNULL(@pmTipoSangre,'%%') AND FactorRh LIKE ISNULL(@pmFactorRh,'%') AND Sexo LIKE ISNULL(@pmSexo,'%') AND IdEstCivil LIKE ISNULL(@pmIdEstCivil,'%') AND E.IdProf LIKE ISNULL(@pmIdProf,'%') AND TallaCam LIKE ISNULL(@pmTallaCam,'%%') AND TallaPan LIKE ISNULL(@pmTallaPan,'%%') AND TallaZap LIKE ISNULL(@pmTallaZap,'%%') AND IdFonPen LIKE ISNULL(@pmIdFonPen,'%') AND IdFonCes LIKE ISNULL(@pmIdFonCes,'%') AND IdFonArp LIKE ISNULL(@pmIdFonArp,'%') AND IdFonEps LIKE ISNULL(@pmIdFonEps,'%') AND IdCajaCom LIKE ISNULL(@pmIdCajaCom ,'%') AND E.IdBanco LIKE ISNULL(@pmIdBanco,'%') AND E.IdEstado LIKE ISNULL(@pmEmp_IdEstado,'%') AND (ClaseLib>=ISNULL(@pmClaseLib,0) AND ClaseLib<=ISNULL(@pmClaseLib,2147483647)) AND (E.Inactivo=ISNULL(@pmEmp_Inactivo,0) or E.Inactivo=ISNULL(@pmEmp_Inactivo,1)) AND (EsCondtor=ISNULL(@pmEsCondtor,0) or EsCondtor=ISNULL(@pmEsCondtor,1)) AND (FecNacmto>=ISNULL(@pmFecNacmtoIni,CAST('19100101' AS SMALLDATETIME)) AND FecNacmto<=ISNULL(@pmFecNacmtoFin,CAST('20781230' AS SMALLDATETIME))) ORDER BY Apellidos,Nombres,C.NContrato GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_NULLS OFF GO CREATE PROCEDURE paQryEmp_ContratoEca @pmIdCargo VARCHAR(4)=Null,@pmIdDep VARCHAR(4)=Null,@pmIdArea VARCHAR(4)=Null,@pmIdInstala VARCHAR(4)=Null,@pmIdNom VARCHAR(4)=Null ,@pmIdEstado VARCHAR(4)=Null,@pmInactivo BIT=Null,@pmIdProf VARCHAR(4)=Null,@pmSexo CHAR(1)=Null,@pmEsCondtor BIT=Null,@pmFecNacmtoIni SMALLDATETIME=Null,@pmFecNacmtoFin SMALLDATETIME=Null AS SELECT C.IdEmpleado AS IdEmplado,Apellidos,Nombres,C.NContrato AS NCntrato,TipoId,Codigo,E.Direccion AS Dirccion,E.IdLocal AS CodCiu,L.Localidad AS Ciudad,Telefono,TelMovil,e_mail,VvdaPropia,FecNacmto,LugarNacmto,TipoSangre,FactorRh,Sexo ,IdEstCivil,EstCivil,NHijos,TallaCam,TallaPan,TallaZap,UsaGafas,E.IdProf AS CodProf,Profesion,LibMilitar,ClaseLib,FechaLib,DistMil,CertJudicial,FecJudicial,VigJudicial,Pasporte,VigPaspte,Licencia,E.IdLugar AS CodLugar,LugarLic,CatLicencia,FecLicencia,VigLicencia ,IdLugarCed,LC.Localidad AS LugarCed,Conyugue,ConyFecNac,ConyTrabaja,ConyEmpresa,ConyCargo,ConyFing,ConyTel,E.IdClase AS CodClase,ClaseCuenta,NumCuenta,E.IdBanco AS CodBco,Banco,IdFonPen,FP.Fondo AS FondPen,IdFonEps,FS.Fondo AS FondEps ,IdFonArp,FR.Fondo AS FondArp,IdFonCes,FC.Fondo AS FondCes,IdCajaCom,CC.Fondo AS CajaComp,FecVincula,FecUltAum,FecUltCes,FecUltPri,FecUltVac,FecUltDot,FecUltExm,FecUltCap,NCAnterior,FecIngAnt,FecRetAnt,EsCondtor,pVehiculo ,FotoEmp,FotoLic,FotoFirma,Observacion,E.IdEstado AS Emp_IdEstado,ET.Estado AS Emp_estado,E.Inactivo AS Emp_Inactivo,ET.NColor AS EmpEstcolor,E.IdUsuario AS Emp_IdUsuario,UE.Usuario AS Emp_Usuario --datos del contrato ,C.IdTipCon AS CodTCon,TipoContrato,FecIngreso,FecVigencia,FecRetiro,Indefinido,C.IdCargo AS CodCargo,Cargo,C.IdJornada AS IdJrnada,Jornada,C.IdArea AS CodArea,Area,C.IdDep AS CodDep,Dependencia,C.IdInstala AS IdInstla,Instlacion ,C.IdCCosto AS IdCCto,CCosto,C.IdSubCos AS IdSubcen,SubCosto,C.IdCia AS CodCia,Compania,C.IdClase AS CodClaRie,ClaseRiesgo,Tarifa,TipoTrabj,C.IdNom AS CodNom,TipoNomina,C.TipoLiquida AS TipoLiq,VrSalario,VrAuxTrans,SalMinimo,SalIntegral ,DchDotacion,DchCafeteria,Reclmto,Notas,C.IdEstado AS IdEstdCon,EC.Estado AS EstdoCon,EC.NColor AS ConEstcolor,C.Inactivo AS Inactvo,C.IdUsuario AS IdUsuari,U.Usuario AS Con_Usuario,SubTipoCot FROM Emp_Contrato AS C INNER JOIN Empleados AS E ON C.IdEmpleado=E.IdEmpleado INNER JOIN TiposCon AS TC ON C.IdTipCon=TC.IdTipCon INNER JOIN Cargos AS CG ON C.IdCargo=CG.IdCargo INNER JOIN Jornadas AS J ON C.IdJornada=J.IdJornada INNER JOIN Dependencias AS D ON C.IdDep=D.IdDep INNER JOIN Areas AS AR ON C.IdArea=AR.IdArea INNER JOIN Instalaciones AS I ON C.IdInstala=I.IdInstala INNER JOIN CentroCosto AS CO ON C.IdCCosto=CO.IdCCosto INNER JOIN SubCentros AS SC ON C.IdSubCos=SC.IdSubCos INNER JOIN Companias AS CI ON C.IdCia=CI.IdCia INNER JOIN TiposNom AS TN ON C.IdNom=TN.IdNom INNER JOIN EstadoDoc AS EC ON C.IdEstado=EC.IdEstado INNER JOIN adm_Usuarios AS U ON C.IdUsuario=U.IdUsuario INNER JOIN ClaseRie AS CR ON C.IdClase=CR.IdClase INNER JOIN Localidades AS L ON E.IdLocal=L.IdLocal INNER JOIN Localidades AS LC ON E.IdLugarCed=LC.IdLocal INNER JOIN EstadoCiv AS ECV ON E.IdEstCivil=ECV.IdEstado INNER JOIN ExpLicencias AS LL ON E.IdLugar=LL.IdLugar INNER JOIN Profesiones AS P ON E.IdProf=P.IdProf INNER JOIN Fondos AS FP ON E.IdFonPen=FP.IdFondo INNER JOIN Fondos AS FS ON E.IdFonEps=FS.IdFondo INNER JOIN Fondos AS FR ON E.IdFonArp=FR.IdFondo INNER JOIN Fondos AS FC ON E.IdFonCes=FC.IdFondo INNER JOIN Fondos AS CC ON E.IdCajaCom=CC.IdFondo INNER JOIN ClaseCta AS CT ON E.IdClase=CT.IdClase INNER JOIN Bancos AS B ON E.IdBanco=B.IdBanco INNER JOIN EstadoTer AS ET ON E.IdEstado=ET.IdEstado INNER JOIN adm_Usuarios AS UE ON E.IdUsuario=UE.IdUsuario WHERE C.IdCargo LIKE ISNULL(@pmIdCargo,'%') AND C.IdDep LIKE ISNULL(@pmIdDep,'%') AND C.IdArea LIKE ISNULL(@pmIdArea,'%') AND C.IdInstala LIKE ISNULL(@pmIdInstala,'%') AND C.IdNom LIKE ISNULL(@pmIdNom,'%') AND C.IdEstado LIKE ISNULL(@pmIdEstado,'%') AND Sexo LIKE ISNULL(@pmSexo,'%') AND E.IdProf LIKE ISNULL(@pmIdProf,'%') AND (C.Inactivo=ISNULL(@pmInactivo,0) or C.Inactivo=ISNULL(@pmInactivo,1)) AND (EsCondtor=ISNULL(@pmEsCondtor,0) or EsCondtor=ISNULL(@pmEsCondtor,1)) AND (FecNacmto>=ISNULL(@pmFecNacmtoIni,CAST('19100101' AS SMALLDATETIME)) AND FecNacmto<=ISNULL(@pmFecNacmtoFin,CAST('20781230' AS SMALLDATETIME))) ORDER BY Apellidos,Nombres,C.NContrato GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_NULLS OFF GO CREATE PROCEDURE paQryEmp_ContratoLab @pmNContrato INT,@pmInactivo BIT=Null AS SELECT C.IdEmpleado AS IdEmplado,Apellidos,Nombres,C.NContrato AS NCntrato,C.IdTipCon AS CodTCon,TipoContrato,FecIngreso,FecVigencia,FecRetiro,Indefinido,C.IdCargo AS CodCargo,Cargo ,C.IdJornada AS IdJrnada,Jornada,Rangos,C.IdArea AS CodArea,Area,C.IdDep AS CodDep,Dependencia,C.IdClase AS CodCla,ClaseRiesgo,Tarifa,TipoTrabj,C.IdInstala AS IdInstla,Instlacion,C.IdCCosto AS IdCCto,CCosto,C.IdSubCos AS IdSubcen,SubCosto,C.IdCia AS CodCia,Compania,C.IdNom AS CodNom ,TipoNomina,Asistencia,NoDevenga,C.TipoLiquida AS TipoLiq,ModPromdio,VrSalario,VrAuxTrans,SalMinimo,SalIntegral,FPnomina,ModoRet,FecAfiSeg,ATFinMes,SegFinMes,DenyPEN,DenyEPS,DenyARP,DenyCaj,DenyCes,DenyPri,DenyVac ,DenyHex,DenyDcto,BasePrest,BaseSeg,DchDotacion,DchCafeteria,Reclmto,Notas,C.IdEstado AS IdEstdCon,EC.Estado AS EstdoCon,C.Inactivo AS Inactvo,IdCot,C.IdGrupo AS GrupAR,VrUpc,SubTipoCot --datos del empleado ,IdFonPen,FP.Fondo AS FondPen,IdFonEps,FS.Fondo AS FondEps,IdFonArp,FR.Fondo AS FondArp,IdFonCes,FC.Fondo AS FondCes,IdCajaCom,CC.Fondo AS CajaComp ,E.IdClase AS CodClase,ClaseCuenta,NumCuenta,E.IdBanco AS CodBco,Banco,FecUltAum,FecUltCes,FecUltPri,FecUltVac,FecUltIng,FecUltRet,FecVincula,FecUltDot,FecUltExm,FecUltCap,NCAnterior,FecIngAnt,FecRetAnt,EsCondtor,pVehiculo ,E.IdProf AS CodProf,Profesion,Observacion,E.IdEstado AS Emp_IdEstdo,ET.Estado AS Emp_Estdo,E.Inactivo AS Emp_Inactivo ,C.FecAdd AS Fec_Add,C.FecUpdate AS Fec_Update,C.IdUsuario AS IdUsuari,Usuario FROM Emp_Contrato AS C INNER JOIN Empleados AS E ON C.IdEmpleado=E.IdEmpleado INNER JOIN TiposCon AS TC ON C.IdTipCon=TC.IdTipCon INNER JOIN Cargos AS CG ON C.IdCargo=CG.IdCargo INNER JOIN Jornadas AS J ON C.IdJornada=J.IdJornada INNER JOIN Dependencias AS D ON C.IdDep=D.IdDep INNER JOIN Areas AS AR ON C.IdArea=AR.IdArea INNER JOIN Instalaciones AS I ON C.IdInstala=I.IdInstala INNER JOIN CentroCosto AS CO ON C.IdCCosto=CO.IdCCosto INNER JOIN SubCentros AS SC ON C.IdSubCos=SC.IdSubCos INNER JOIN ClaseRie AS CR ON C.IdClase=CR.IdClase INNER JOIN Companias AS CI ON C.IdCia=CI.IdCia INNER JOIN TiposNom AS TN ON C.IdNom=TN.IdNom INNER JOIN EstadoDoc AS EC ON C.IdEstado=EC.IdEstado INNER JOIN adm_Usuarios AS U ON C.IdUsuario=U.IdUsuario INNER JOIN Profesiones AS P ON E.IdProf=P.IdProf INNER JOIN Fondos AS FP ON E.IdFonPen=FP.IdFondo INNER JOIN Fondos AS FS ON E.IdFonEps=FS.IdFondo INNER JOIN Fondos AS FR ON E.IdFonArp=FR.IdFondo INNER JOIN Fondos AS FC ON E.IdFonCes=FC.IdFondo INNER JOIN Fondos AS CC ON E.IdCajaCom=CC.IdFondo INNER JOIN ClaseCta AS CT ON E.IdClase=CT.IdClase INNER JOIN Bancos AS B ON E.IdBanco=B.IdBanco INNER JOIN EstadoTer AS ET ON E.IdEstado=ET.IdEstado WHERE C.NContrato=@pmNContrato AND (C.Inactivo=ISNULL(@pmInactivo,0) or C.Inactivo=ISNULL(@pmInactivo,1)) GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS OFF GO CREATE PROCEDURE paQryNomAutoliq @pmTipoLiq VARCHAR(3),@pmNumero VARCHAR(20) AS SELECT TipoLiq,Numero,Fecha,PerAutoliq,PerAutoSal,FechaPago,IdAportnte,Presentacion,CodCia,CodFondo,CodArp,CodOper,EsCorr,PerCorr,NumCorr,TotalEmp,VrNomina,VrPension,VrSalud,VrRiesgos,VrCajas,VrSena,VrIcbf,VrEsap,VrMinEduc,TotalAfipen,TotalAfiSal ,TotalAfiRie,TotalAfiCaj,TotalAfiSen,TotalAfiIcb,TotalAfiEsa,TotalAfiEduc,TotAdmPen,TotAdmSal,TotAdmRie,TotAdmCom,Observacion,LogAdmon,RedoMiles,Anulado,IdEstado,TimeSys,FecUpdate,IdUsuario,TipoPlanilla FROM Trn_NomAutoliq WHERE TipoLiq=@pmTipoLiq AND Numero=@pmNumero 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 --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 OFF GO SET ANSI_NULLS OFF GO CREATE PROCEDURE paQryNomAutoliqDso @pmFechaIni SMALLDATETIME,@pmFechaFin SMALLDATETIME,@pmTipoLiq VARCHAR(3)=Null,@pmNumero VARCHAR(20)=Null ,@pmPerAutoliq VARCHAR(6)=Null,@pmCodFondo VARCHAR(8)=Null,@pmEsCorr BIT=Null,@pmIdEstado VARCHAR(4)=Null,@pmPresentacion CHAR(1)=Null,@pmAnulado BIT=Null AS SELECT TipoLiq,Numero,Fecha,PerAutoliq,PerAutoSal,FechaPago,A.IdAportnte AS NitAportnte,RazonSocial,Presentacion,CodCia,CodFondo,F.Fondo AS NomFondo,CodArp,FR.Fondo AS FondoArp,CodOper,EsCorr,PerCorr,NumCorr,TotalEmp,VrNomina,VrPension,VrSalud,VrRiesgos ,VrCajas,VrSena,VrIcbf,VrEsap,VrMinEduc,TotalAfipen,TotalAfiSal,TotalAfiRie,TotalAfiCaj,TotalAfiSen,TotalAfiIcb,TotalAfiEsa,TotalAfiEduc,TotAdmPen,TotAdmSal,TotAdmRie,TotAdmCom,Observacion ,LogAdmon,RedoMiles,Anulado,A.IdEstado AS IdEstdo,Estado,TipoPlanilla,TimeSys,A.FecUpdate AS Fec_Updte,A.IdUsuario AS IdUsuari,Usuario FROM Trn_NomAutoliq AS A INNER JOIN NomAportante AS NA ON A.IdAportnte=NA.IdAportnte INNER JOIN EstadoDoc AS ED ON A.IdEstado=ED.IdEstado INNER JOIN adm_Usuarios AS U ON A.IdUsuario=U.IdUsuario LEFT JOIN Fondos AS F ON A.CodFondo=F.IdFondo LEFT JOIN Fondos AS FR ON A.CodArp=FR.IdFondo WHERE TipoLiq LIKE ISNULL(@pmTipoLiq,'%') AND Numero LIKE ISNULL(@pmNumero,'%') AND PerAutoliq LIKE ISNULL(@pmPerAutoliq,'%') AND CodFondo LIKE ISNULL(@pmCodFondo,'%') AND A.IdEstado LIKE ISNULL(@pmIdEstado,'%') AND Presentacion LIKE ISNULL(@pmPresentacion,'%') AND (Fecha>=ISNULL(@pmFechaIni,CAST('19100101' AS SMALLDATETIME)) AND Fecha<=ISNULL(@pmFechaFin,CAST('20781230' AS SMALLDATETIME))) AND (Anulado=ISNULL(@pmAnulado,0) or Anulado=ISNULL(@pmAnulado,1)) AND (EsCorr=ISNULL(@pmEsCorr,0) or EsCorr=ISNULL(@pmEsCorr,1)) ORDER BY TipoLiq,Numero GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_NULLS OFF GO CREATE PROCEDURE paQryNomFijos @pmIdEmpleado VARCHAR(16),@pmnAnno INT,@pmnMes INT AS SELECT IdEmpleado,nAnno,nMes,Tarifa,VrBase,VrSalarios,VrPrima,VrVacacnes,VrSIntExcto,VrOtrosIng,VrIngresos,VrPensiones,VrSalud,VrDedcido,VrExcento,VrCertEdu,VrCertVda ,VrCertAFC,VrCertfcado,VrTotalBase,Periodos,OrignPorc,FecAdd,FecUpdate,IdUsuario FROM Trn_NomFijos WHERE IdEmpleado=@pmIdEmpleado AND nAnno=@pmnAnno AND nMes=@pmnMes GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_NULLS OFF GO CREATE PROCEDURE paQryNomFijosLta @pmIdEmpleado VARCHAR(16)=Null,@pmnAnno INT=Null,@pmnMes INT=Null AS SELECT F.IdEmpleado AS IdEmpledo,Apellidos,Nombres,nAnno,nMes,Tarifa,VrBase,VrSalarios,VrPrima,VrVacacnes,VrSIntExcto,VrOtrosIng,VrIngresos,VrPensiones,VrSalud,VrDedcido,VrExcento ,VrCertEdu,VrCertVda,VrCertAFC,VrCertfcado,VrTotalBase,Periodos,OrignPorc,Codigo,NContrato,FecUltIng,F.IdUsuario AS IdUsuari,Usuario,FecAdd,FecUpdate FROM Trn_NomFijos AS F INNER JOIN Empleados AS E ON F.IdEmpleado=E.IdEmpleado INNER JOIN adm_Usuarios AS U ON F.IdUsuario=U.IdUsuario WHERE F.IdEmpleado like ISNULL(@pmIdEmpleado,'%') AND (nAnno>=ISNULL(@pmnAnno,0) AND nAnno<=ISNULL(@pmnAnno,2147483647)) AND (nMes>=ISNULL(@pmnMes,0) AND nMes<=ISNULL(@pmnMes,2147483647)) ORDER BY Apellidos,Nombres,nAnno,nMes GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS OFF GO CREATE PROCEDURE paQryNomVac_Cr @pmIdEmpleado VARCHAR(16),@pmNumero INT AS SELECT V.IdEmpleado AS IdEmpldo,Apellidos,Nombres,V.Numero AS NumReg,Fecha,V.NContrato AS NumContrato,FecInicial,FecFinal,V.Cantidad AS DiasCal,VrLiquida,VrBase,FecIniPmd,FecFinPmd,DiasBase,DiasNoRem,DiasVac ,NNovedad,NPeriodos,CantRem,VrRemndo,VrTotDed,V.VrSalario AS Vac_VrBasico,V.pVehiculo AS PlacaVeh,TipCom,TipoCom,Comprobante,IdCiaCom,V.Observacion AS Observ,LogPmdio,Anulado,V.IdEstado AS Vac_IdEstdo,ED.Estado AS Vac_Estado ,TimeSys,V.FecUpdate AS FecUpd,V.IdUsuario AS IdUsuari,Usuario --datos del detalle de liquidacion ,Item,D.IdConcepto AS CodConcepto,Concepto,ClaseCon,Detalle,D.Cantidad AS Cant,Unidad,VrDevgado,VrDeducido,D.Tarifa AS Tarfa,VrBaseLiq,VrCertfcado,VrPension,NPrestamo,NCuota,IdPeriodo --datos del empleado ,Codigo,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,IdFonPen,FP.Fondo AS FondoPen ,IdFonEps,FS.Fondo AS FondoEps,IdFonArp,FR.Fondo AS FondoArp,IdCajaCom,CC.Fondo AS CajaComp,EsCondtor,FecNacmto,Licencia,CatLicencia,VigLicencia,E.IdLugar AS CodLugar,LugarLic,TallaCam,TallaPan,TallaZap,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 CodDep,Dependencia,C.IdInstala AS CodInst,Instlacion,C.IdCCosto AS CodCcto,CCosto ,C.IdSubCos AS CodSubcen,SubCosto,C.IdNom AS CodNom,TipoNomina,C.TipoLiquida AS TipLiq,Asistencia,NoDevenga,C.IdClase AS CodClaRie,ClaseRiesgo,CR.Tarifa AS TarifaRie,C.VrSalario AS SalBasico,VrAuxTrans,SalMinimo,SalIntegral,FPnomina,ATFinMes ,SegFinMes,DchDotacion,DchCafeteria,C.IdEstado AS Con_IdEstdo,ECO.Estado AS Con_Estado,C.Inactivo AS Con_inactivo,C.IdJornada AS CodJnada,Jornada ,NumVeh,TipoAdmon,VehPropio,VrSalud FROM Trn_NomVac AS V INNER JOIN Empleados AS E ON V.IdEmpleado=E.IdEmpleado INNER JOIN Emp_Contrato AS C ON V.NContrato=C.NContrato INNER JOIN adm_Usuarios AS U ON V.IdUsuario=U.IdUsuario INNER JOIN EstadoDoc AS ED ON V.IdEstado=ED.IdEstado 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 Fondos AS FP ON E.IdFonPen=FP.IdFondo INNER JOIN Fondos AS FS ON E.IdFonEps=FS.IdFondo INNER JOIN Fondos AS FR ON E.IdFonArp=FR.IdFondo INNER JOIN Fondos AS CC ON E.IdCajaCom=CC.IdFondo INNER JOIN ExpLicencias AS EL ON E.IdLugar=EL.IdLugar INNER JOIN TiposCon AS TC ON C.IdTipCon=TC.IdTipCon INNER JOIN Dependencias AS DP ON C.IdDep=DP.IdDep INNER JOIN Instalaciones AS I ON C.IdInstala=I.IdInstala 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 EstadoDoc AS ECO ON C.IdEstado=ECO.IdEstado 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 LEFT JOIN Trn_NomVacDet AS D ON V.IdEmpleado=D.IdEmpleado AND V.Numero=D.Numero LEFT JOIN NomConceptos AS CN ON D.IdConcepto=CN.IdConcepto LEFT JOIN TiposCom AS TM ON V.TipCom=TM.IdCom LEFT JOIN NomVehiculos AS VH ON V.pVehiculo=VH.IdVehiculo WHERE V.IdEmpleado LIKE ISNULL(@pmIdEmpleado,'%') AND (V.Numero>=ISNULL(@pmNumero,0) AND V.Numero<=ISNULL(@pmNumero,2147483647)) ORDER BY V.Numero GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS OFF GO CREATE PROCEDURE paQryNomVacDet @pmIdEmpleado VARCHAR(16),@pmNumero INT,@pmItem INT AS SELECT IdEmpleado,Numero,Item,IdConcepto,Detalle,Cantidad,VrDevgado,VrDeducido,Tarifa,VrBaseLiq,VrCertfcado,VrPension ,VrSalud,NPrestamo,NCuota,IdPeriodo FROM Trn_NomVacDet WHERE IdEmpleado=@pmIdEmpleado 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 paQryNomVacDetCon @pmIdEmpleado VARCHAR(16),@pmIdConcepto VARCHAR(4),@pmMes VARCHAR(6) ,@pmIdPeriodo VARCHAR(8)=Null,@pmNContrato INT=Null AS SELECT SUM(VD.Cantidad) AS SCANT,SUM(VrDevgado) AS SDEVNG,SUM(VrDeducido) AS STOTDED,SUM(VrBaseLiq) AS STOTBAS ,SUM(VrCertfcado) AS STOTCER,SUM(VrPension) AS STOTPEN,SUM(VrSalud) AS STOTSALUD FROM Trn_NomVacDet AS VD INNER JOIN Trn_NomVac AS V ON VD.IdEmpleado=V.IdEmpleado AND VD.Numero=V.Numero WHERE VD.IdEmpleado=@pmIdEmpleado AND IdConcepto=@pmIdConcepto AND Anulado=0 AND SUBSTRING(IdPeriodo,1,6)=@pmMes AND IdPeriodo LIKE ISNULL(@pmIdPeriodo,'%') AND (NContrato>=ISNULL(@pmNContrato,0) AND NContrato<=ISNULL(@pmNContrato,2147483647)) GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS OFF GO CREATE PROCEDURE paQryNomVacDet_Cr @pmIdEmpleado VARCHAR(16)=Null,@pmFechaIni SMALLDATETIME=Null,@pmFechaFin SMALLDATETIME=Null,@pmNumero INT=Null ,@pmIdNom VARCHAR(4)=Null,@pmIdDep VARCHAR(4)=Null,@pmIdArea VARCHAR(4)=Null,@pmIdTipCon VARCHAR(4)=Null,@pmIdInstala VARCHAR(4)=Null ,@pmIdCCosto VARCHAR(16)=Null,@pmIdEstado VARCHAR(4)=Null,@pmAnulado BIT=Null,@pmNContrato INT=Null,@pmFecVacIni SMALLDATETIME=Null ,@pmFecVacFin SMALLDATETIME=Null,@pmIdConcepto VARCHAR(4)=Null AS SELECT Q.IdEmpleado AS IdEmpldo,Apellidos,Nombres,Q.Numero AS NumLiquida,Fecha,Q.NContrato AS NumContrato,FecInicial,FecFinal,Q.Cantidad AS CantDias,VrLiquida,VrBase,FecIniPmd,FecFinPmd,DiasBase,DiasNoRem ,DiasVac,NNovedad,NPeriodos,CantRem,VrRemndo,VrTotDed,Q.VrSalario AS Liq_SalBasico,Q.pVehiculo AS PlacaVeh,TipCom,TipoCom,Comprobante,IdCiaCom,Q.Observacion AS Observ,LogPmdio,Anulado ,Q.IdEstado AS IdEstdo,ED.Estado AS Liq_estado,TimeSys,Q.FecUpdate AS FecUpd,Q.IdUsuario AS IdUsuari,Usuario --detalle de liq vac ,Item,D.IdConcepto AS CodConcepto,Concepto,Detalle,ClaseCon,D.Cantidad AS Cant,VrDevgado,VrDeducido,D.Tarifa AS Tarfa,VrBaseLiq,VrCertfcado,VrPension,NPrestamo,NCuota,IdPeriodo --datos del empleado ,Codigo,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,IdFonPen,FP.Fondo AS FondoPen,IdFonEps,FS.Fondo AS FondoEps ,IdFonArp,FR.Fondo AS FondoArp,IdCajaCom,CC.Fondo AS CajaComp,EsCondtor,E.pVehiculo AS Emp_Vehiculo,FecNacmto,Licencia,CatLicencia,VigLicencia,E.IdLugar AS CodLugar,LugarLic,TallaCam,TallaPan,TallaZap,FecUltCes,FecUltPri,FecUltVac,E.Inactivo AS Emp_Inactvo --datos del contrato ,C.IdTipCon AS CodTipCon,TipoContrato,FecIngreso,FecVigencia,FecRetiro,Indefinido,C.IdCargo AS CodCargo,Cargo,C.IdArea AS CodArea,Area,C.IdDep AS CodDep,Dependencia,C.IdInstala AS CodInst,Instlacion,C.IdJornada AS CodJnada,Jornada ,C.IdNom AS CodNom,TipoNomina,C.TipoLiquida AS TipLiq,C.IdCCosto AS CodCcto,CCosto,C.IdSubCos AS CodSubcen,SubCosto,Asistencia,NoDevenga,TipoTrabj ,C.IdClase AS CodClaRie,ClaseRiesgo,CR.Tarifa AS TarifaRie,C.VrSalario AS SalBasico,VrAuxTrans,SalMinimo,SalIntegral,FPnomina,ModPromdio,ATFinMes,SegFinMes,DchDotacion,DchCafeteria,DenyCes,DenyPri,DenyVac,BasePrest ,C.IdEstado AS Con_IdEstdo,ECO.Estado AS Con_Estado,C.Inactivo AS Con_inactivo,VrSalud FROM Trn_NomVac AS Q INNER JOIN Trn_NomVacDet AS D ON Q.IdEmpleado=D.IdEmpleado AND Q.Numero=D.Numero INNER JOIN adm_Usuarios AS U ON Q.IdUsuario=U.IdUsuario INNER JOIN EstadoDoc AS ED ON Q.IdEstado=ED.IdEstado INNER JOIN Empleados AS E ON Q.IdEmpleado=E.IdEmpleado INNER JOIN NomConceptos AS CN ON D.IdConcepto=CN.IdConcepto 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 Fondos AS FP ON E.IdFonPen=FP.IdFondo INNER JOIN Fondos AS FS ON E.IdFonEps=FS.IdFondo INNER JOIN Fondos AS FR ON E.IdFonArp=FR.IdFondo INNER JOIN Fondos AS CC ON E.IdCajaCom=CC.IdFondo INNER JOIN ExpLicencias AS EL ON E.IdLugar=EL.IdLugar INNER JOIN Emp_Contrato AS C ON Q.NContrato=C.NContrato INNER JOIN TiposCon AS TC ON C.IdTipCon=TC.IdTipCon INNER JOIN Instalaciones AS I ON C.IdInstala=I.IdInstala INNER JOIN EstadoDoc AS ECO ON C.IdEstado=ECO.IdEstado 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 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 LEFT JOIN TiposCom AS TM ON Q.TipCom=TM.IdCom WHERE Q.IdEmpleado LIKE ISNULL(@pmIdEmpleado,'%') AND C.IdNom LIKE ISNULL(@pmIdNom,'%') AND C.IdDep LIKE ISNULL(@pmIdDep,'%') AND C.IdArea LIKE ISNULL(@pmIdArea,'%') AND C.IdTipCon LIKE ISNULL(@pmIdTipCon,'%') AND C.IdInstala LIKE ISNULL(@pmIdInstala,'%') AND C.IdCCosto LIKE ISNULL(@pmIdCCosto,'%') AND Q.IdEstado LIKE ISNULL(@pmIdEstado,'%') AND D.IdConcepto LIKE ISNULL(@pmIdConcepto,'%') AND (Q.Numero>=ISNULL(@pmNumero,0) AND Q.Numero<=ISNULL(@pmNumero,2147483647)) AND (Fecha>=ISNULL(@pmFechaIni,CAST('19100101' AS SMALLDATETIME)) AND Fecha<=ISNULL(@pmFechaFin,CAST('20781230' AS SMALLDATETIME))) AND (Q.NContrato >=ISNULL(@pmNContrato ,0) AND Q.NContrato <=ISNULL(@pmNContrato,2147483647)) AND (Anulado=ISNULL(@pmAnulado,0) or Anulado=ISNULL(@pmAnulado,1)) AND (FecInicial>=ISNULL(@pmFecVacIni,CAST('19100101' AS SMALLDATETIME)) AND FecInicial<=ISNULL(@pmFecVacFin,CAST('20781230' AS SMALLDATETIME))) ORDER BY Apellidos,Nombres,Q.Numero,Item GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS OFF GO CREATE PROCEDURE paQryNomVacDetRes @pmFechaIni SMALLDATETIME,@pmFechaFin SMALLDATETIME ,@pmIdEmpleado VARCHAR(16)=Null,@pmNContrato INT=Null,@pmIdArea VARCHAR(4)=Null,@pmIdDep VARCHAR(4)=Null ,@pmIdNom VARCHAR(4)=Null,@pmIdInstala VARCHAR(4)=Null,@pmIdCCosto VARCHAR(16)=Null,@pmInactivo BIT=Null ,@pmEmp_Inactivo BIT=Null,@pmFecVacIni SMALLDATETIME=Null,@pmFecVacFin SMALLDATETIME=Null AS SELECT Q.IdEmpleado AS IdEmpldo,Q.Numero AS NumLiquida,Q.NContrato AS NumContrato,Fecha,FecInicial,FecFinal,Q.Cantidad AS CantDias ,VrLiquida,VrBase,FecIniPmd,FecFinPmd,DiasBase,DiasNoRem,DiasVac,NNovedad,NPeriodos,CantRem,VrRemndo,VrTotDed,Q.VrSalario AS Liq_SalBasico ,Q.pVehiculo AS PlacaVeh,TipCom,Comprobante,IdCiaCom,Q.Observacion AS Observ --detalle de liq vac ,Item,IdConcepto,Detalle,D.Cantidad AS Cant,VrDevgado,VrDeducido,Tarifa,VrBaseLiq,VrCertfcado,VrPension,NPrestamo,NCuota,IdPeriodo,VrSalud --datos del contrato ,IdArea,IdDep,IdInstala,IdNom,TipoLiquida,IdCCosto,IdSubCos,C.VrSalario AS SalBasico,VrAuxTrans FROM Trn_NomVac AS Q INNER JOIN Empleados AS E ON Q.IdEmpleado=E.IdEmpleado INNER JOIN Emp_Contrato AS C ON Q.NContrato=C.NContrato LEFT JOIN Trn_NomVacDet AS D ON Q.IdEmpleado=D.IdEmpleado AND Q.Numero=D.Numero WHERE Fecha BETWEEN @pmFechaIni AND @pmFechaFin AND Anulado=0 AND Q.IdEmpleado LIKE ISNULL(@pmIdEmpleado,'%') AND IdDep LIKE ISNULL(@pmIdDep,'%') AND IdArea LIKE ISNULL(@pmIdArea,'%') AND IdNom LIKE ISNULL(@pmIdNom,'%') AND IdInstala LIKE ISNULL(@pmIdInstala,'%') AND IdCCosto LIKE ISNULL(@pmIdCCosto,'%') AND (Q.NContrato >=ISNULL(@pmNContrato ,0) AND Q.NContrato <=ISNULL(@pmNContrato,2147483647)) AND (FecInicial>=ISNULL(@pmFecVacIni,CAST('19100101' AS SMALLDATETIME)) AND FecInicial<=ISNULL(@pmFecVacFin,CAST('20781230' AS SMALLDATETIME))) AND (C.Inactivo=ISNULL(@pmInactivo,0) or C.Inactivo=ISNULL(@pmInactivo,1)) AND (E.Inactivo=ISNULL(@pmEmp_Inactivo,0) or E.Inactivo=ISNULL(@pmEmp_Inactivo,1)) ORDER BY IdEmpldo,Q.Numero GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS OFF GO CREATE PROCEDURE paQryNomVacIng @pmFecIni SMALLDATETIME,@pmFecFin SMALLDATETIME,@pmIdNom VARCHAR(4)=Null,@pmIdArea VARCHAR(4)=Null ,@pmIdDep VARCHAR(4)=Null,@pmIdInstala VARCHAR(4)=Null,@pmIdCCosto VARCHAR(16)=Null,@pmIdTipCon VARCHAR(4)=Null,@pmIdEmpleado VARCHAR(16)=Null ,@pmNContrato INT=Null,@pmInactivo BIT=Null,@pmEmp_Inactivo BIT=Null AS SELECT Q.IdEmpleado AS IdEmpldo,Apellidos,Nombres,Q.Numero AS NumLiquida,Fecha,Q.NContrato AS NumContrato,FecInicial,FecFinal,Q.Cantidad AS CantDias,VrLiquida,VrBase,FecIniPmd,FecFinPmd,DiasBase,DiasNoRem ,DiasVac,CantRem,VrRemndo,VrTotDed,Q.VrSalario AS Liq_SalBasico,Q.pVehiculo AS PlacaVeh ,Item,D.IdConcepto AS CodConcepto,Concepto,Detalle,ClaseCon,CN.Tarifa AS TarifDft,D.Cantidad AS Cant,VrDevgado,VrDeducido,D.Tarifa AS Tarfa,VrBaseLiq,VrCertfcado,VrPension,VrSalud,NPrestamo ,IdTipCon,IdArea,IdDep,IdInstala,IdNom,TipoLiquida,IdCCosto,IdSubCos,C.VrSalario AS SalBasico,VrAuxTrans,E.Inactivo AS Emp_Inactvo,C.Inactivo AS Con_inactivo,IdFonPen,IdFonEps FROM Trn_NomVac AS Q INNER JOIN Empleados AS E ON Q.IdEmpleado=E.IdEmpleado INNER JOIN Emp_Contrato AS C ON Q.NContrato=C.NContrato LEFT JOIN Trn_NomVacDet AS D ON Q.IdEmpleado=D.IdEmpleado AND Q.Numero=D.Numero LEFT JOIN NomConceptos AS CN ON D.IdConcepto=CN.IdConcepto WHERE FecInicial BETWEEN @pmFecIni AND @pmFecFin AND IdNom LIKE ISNULL(@pmIdNom,'%') AND IdArea LIKE ISNULL(@pmIdArea,'%') AND IdDep LIKE ISNULL(@pmIdDep,'%') AND IdInstala LIKE ISNULL(@pmIdInstala,'%') AND IdCCosto LIKE ISNULL(@pmIdCCosto,'%') AND IdTipCon LIKE ISNULL(@pmIdTipCon,'%') AND Q.IdEmpleado LIKE ISNULL(@pmIdEmpleado,'%') AND (Q.NContrato>=ISNULL(@pmNContrato,0) AND Q.NContrato<=ISNULL(@pmNContrato,2147483647)) AND (C.Inactivo=ISNULL(@pmInactivo,0) or C.Inactivo=ISNULL(@pmInactivo,1)) AND (E.Inactivo=ISNULL(@pmEmp_Inactivo,0) or E.Inactivo=ISNULL(@pmEmp_Inactivo,1)) ORDER BY Q.IdEmpleado,Q.Numero,Item GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_NULLS OFF GO CREATE PROCEDURE paQrytm_Nomina @pmtmNumero VARCHAR(5),@pmtmIdEmpleado VARCHAR(16),@pmtmItem INT AS SELECT tmNumero,tmIdEmpleado,tmItem,tmIdConcepto,tmClaseCon,tmDetalle,tmCantDevg,tmCantDed,tmVrUnitario,tmVrTotDevg,tmVrTotDed,tmUnidad,tmClaseLiq ,tmVrOrigen,tmTarifa,tmDiasCalc,tmDiasNov,tmCodFondo,tmNPrestamo,tmNCuota,tmOrigCargue,tmVrBaseLiq,tmNContto,tmFecIni,tmFecFin,tmIdPeriodo ,tmFecUltLiq,tmFecIniPmd,tmFecFinPmd,tmLogPmdio,tmVrPension,tmVrSalud FROM tm_Nomina WHERE tmNumero=@pmtmNumero AND tmIdEmpleado like ISNULL(@pmtmIdEmpleado,'%') AND (tmItem>=ISNULL(@pmtmItem,0) AND tmItem<=ISNULL(@pmtmItem,2147483647)) ORDER BY tmIdEmpleado,tmItem GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS OFF GO CREATE PROCEDURE paQrytm_NominaVac @pmtmNumero VARCHAR(5),@pmtmIdEmpleado VARCHAR(16) AS SELECT tmItem,tmIdConcepto,tmDetalle,tmClaseCon,tmCantDed,tmUnidad,tmVrUnitario,tmVrTotDed,tmVrOrigen,tmTarifa ,tmNPrestamo,tmNCuota,tmDiasCalc,tmVrPension AS VrApoPension,tmVrBaseLiq AS VrCertf,tmIdEmpleado,tmIdPeriodo ,tmVrSalud FROM tm_Nomina WHERE tmNumero=@pmtmNumero AND tmClaseLiq IN ('NOMINA','NOVEDAD') AND tmIdEmpleado like ISNULL(@pmtmIdEmpleado,'%') ORDER BY tmIdEmpleado,tmItem GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_NULLS OFF GO CREATE PROCEDURE paUpEmp_Contrato @pmNContrato INT,@pmIdEmpleado VARCHAR(16),@pmIdTipCon VARCHAR(4),@pmFecIngreso SMALLDATETIME,@pmFecVigencia SMALLDATETIME,@pmFecRetiro SMALLDATETIME,@pmIndefinido BIT,@pmIdCargo VARCHAR(4),@pmIdJornada VARCHAR(4),@pmIdDep VARCHAR(4),@pmIdArea VARCHAR(4),@pmIdInstala VARCHAR(4),@pmIdCCosto VARCHAR(16),@pmIdSubCos VARCHAR(16) ,@pmIdCia CHAR(2),@pmIdNom VARCHAR(4),@pmTipoLiquida CHAR(1),@pmModPromdio CHAR(1),@pmVrSalario MONEY,@pmVrAuxTrans MONEY,@pmSalMinimo BIT,@pmSalIntegral BIT,@pmFPnomina VARCHAR(10),@pmModoRet INT,@pmFecAfiSeg SMALLDATETIME,@pmATFinMes BIT,@pmSegFinMes BIT,@pmDenyPEN BIT,@pmDenyEPS BIT,@pmDenyARP BIT,@pmDenyCaj BIT,@pmDenyCes BIT,@pmDenyPri BIT ,@pmDenyVac BIT,@pmDenyHex BIT,@pmBasePrest CHAR(2),@pmBaseSeg CHAR(2),@pmDchDotacion BIT,@pmDchCafeteria BIT,@pmReclmto VARCHAR(50),@pmNotas VARCHAR(250),@pmIdEstado VARCHAR(4),@pmInactivo BIT,@pmIdClase VARCHAR(4),@pmTipoTrabj INT,@pmIdCot VARCHAR(4),@pmVrUpc MONEY,@pmIdGrupo VARCHAR(4),@pmDenyDcto BIT,@pmSubTipoCot VARCHAR(3),@pmFecUpdate SMALLDATETIME AS UPDATE Emp_Contrato SET IdEmpleado=@pmIdEmpleado,IdTipCon=@pmIdTipCon,FecIngreso=@pmFecIngreso,FecVigencia=@pmFecVigencia,FecRetiro=@pmFecRetiro,Indefinido=@pmIndefinido,IdCargo=@pmIdCargo,IdJornada=@pmIdJornada,IdDep=@pmIdDep,IdArea=@pmIdArea,IdInstala=@pmIdInstala,IdCCosto=@pmIdCCosto,IdCia=@pmIdCia,IdNom=@pmIdNom,TipoLiquida=@pmTipoLiquida,ModPromdio=@pmModPromdio ,VrSalario=@pmVrSalario,VrAuxTrans=@pmVrAuxTrans,SalMinimo=@pmSalMinimo,SalIntegral=@pmSalIntegral,FPnomina=@pmFPnomina,ModoRet=@pmModoRet,FecAfiSeg=@pmFecAfiSeg,ATFinMes=@pmATFinMes,SegFinMes=@pmSegFinMes,DenyPEN=@pmDenyPEN,DenyEPS=@pmDenyEPS,DenyARP=@pmDenyARP,DenyCaj=@pmDenyCaj,DenyCes=@pmDenyCes,DenyPri=@pmDenyPri,DenyVac=@pmDenyVac,DenyHex=@pmDenyHex ,BasePrest=@pmBasePrest,BaseSeg=@pmBaseSeg,DchDotacion=@pmDchDotacion,DchCafeteria=@pmDchCafeteria,Reclmto=@pmReclmto,Notas=@pmNotas,IdEstado=@pmIdEstado,Inactivo=@pmInactivo,FecUpdate=@pmFecUpdate,IdSubCos=@pmIdSubCos,IdClase=@pmIdClase,TipoTrabj=@pmTipoTrabj ,IdCot=@pmIdCot,VrUpc=@pmVrUpc,IdGrupo=@pmIdGrupo,DenyDcto=@pmDenyDcto,SubTipoCot =@pmSubTipoCot WHERE NContrato=@pmNContrato GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS OFF GO CREATE PROCEDURE paUpNomAutoliq @pmTipoLiq VARCHAR(3),@pmNumero VARCHAR(20),@pmFecha SMALLDATETIME,@pmPerAutoliq VARCHAR(6),@pmPerAutoSal VARCHAR(6),@pmFechaPago SMALLDATETIME,@pmIdAportnte VARCHAR(16),@pmPresentacion CHAR(1),@pmCodCia CHAR(2),@pmCodFondo VARCHAR(8),@pmCodArp VARCHAR(8),@pmCodOper VARCHAR(10),@pmEsCorr BIT,@pmPerCorr VARCHAR(6),@pmNumCorr VARCHAR(20),@pmTotalEmp INT,@pmVrNomina MONEY,@pmVrPension MONEY,@pmVrSalud MONEY,@pmVrRiesgos MONEY,@pmVrCajas MONEY ,@pmVrSena MONEY,@pmVrIcbf MONEY,@pmVrEsap MONEY,@pmVrMinEduc MONEY,@pmTotalAfipen INT,@pmTotalAfiSal INT,@pmTotalAfiRie INT,@pmTotalAfiCaj INT,@pmTotalAfiSen INT,@pmTotalAfiIcb INT,@pmTotalAfiEsa INT,@pmTotalAfiEduc INT,@pmTotAdmPen INT,@pmTotAdmSal INT,@pmTotAdmRie INT,@pmTotAdmCom INT,@pmObservacion VARCHAR(250),@pmLogAdmon VARCHAR(250),@pmRedoMiles BIT,@pmAnulado BIT,@pmIdEstado VARCHAR(4),@pmFecUpdate SMALLDATETIME,@pmTipoPlanilla VARCHAR(3) AS UPDATE Trn_NomAutoliq SET Fecha=@pmFecha,PerAutoliq=@pmPerAutoliq,PerAutoSal=@pmPerAutoSal,FechaPago=@pmFechaPago,IdAportnte=@pmIdAportnte,Presentacion=@pmPresentacion,CodCia=@pmCodCia,CodFondo=@pmCodFondo,CodArp=@pmCodArp,CodOper=@pmCodOper,EsCorr=@pmEsCorr,PerCorr=@pmPerCorr,NumCorr=@pmNumCorr,TotalEmp=@pmTotalEmp,VrNomina=@pmVrNomina,VrPension=@pmVrPension,VrSalud=@pmVrSalud,VrRiesgos=@pmVrRiesgos,VrCajas=@pmVrCajas,VrSena=@pmVrSena,VrIcbf=@pmVrIcbf,VrEsap=@pmVrEsap,VrMinEduc=@pmVrMinEduc ,TotalAfipen=@pmTotalAfipen,TotalAfiSal=@pmTotalAfiSal,TotalAfiRie=@pmTotalAfiRie,TotalAfiCaj=@pmTotalAfiCaj,TotalAfiSen=@pmTotalAfiSen,TotalAfiIcb=@pmTotalAfiIcb,TotalAfiEsa=@pmTotalAfiEsa,TotalAfiEduc=@pmTotalAfiEduc,TotAdmPen=@pmTotAdmPen,TotAdmSal=@pmTotAdmSal,TotAdmRie=@pmTotAdmRie,TotAdmCom=@pmTotAdmCom,Observacion=@pmObservacion,LogAdmon=@pmLogAdmon,RedoMiles=@pmRedoMiles,Anulado=@pmAnulado,IdEstado=@pmIdEstado,FecUpdate=@pmFecUpdate,TipoPlanilla=@pmTipoPlanilla WHERE TipoLiq=@pmTipoLiq AND Numero=@pmNumero GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_NULLS OFF GO CREATE PROCEDURE paUpNomFijos @pmIdEmpleado VARCHAR(16),@pmnAnno INT,@pmnMes INT,@pmTarifa DECIMAL(10,4),@pmVrBase MONEY,@pmVrSalarios MONEY,@pmVrPrima MONEY,@pmVrVacacnes MONEY,@pmVrSIntExcto MONEY,@pmVrOtrosIng MONEY,@pmVrIngresos MONEY ,@pmVrPensiones MONEY,@pmVrDedcido MONEY,@pmVrExcento MONEY,@pmVrCertEdu MONEY,@pmVrCertVda MONEY,@pmVrCertAFC MONEY,@pmVrCertfcado MONEY,@pmPeriodos DECIMAL(10,4),@pmVrTotalBase MONEY,@pmOrignPorc VARCHAR(10),@pmFecUpdate SMALLDATETIME ,@pmVrSalud MONEY AS UPDATE Trn_NomFijos SET Tarifa=@pmTarifa,VrBase=@pmVrBase,VrSalarios=@pmVrSalarios,VrPrima=@pmVrPrima,VrVacacnes=@pmVrVacacnes,VrSIntExcto=@pmVrSIntExcto,VrOtrosIng=@pmVrOtrosIng,VrIngresos=@pmVrIngresos,VrPensiones=@pmVrPensiones,VrDedcido=@pmVrDedcido ,VrExcento=@pmVrExcento,VrCertEdu=@pmVrCertEdu,VrCertVda=@pmVrCertVda,VrCertAFC=@pmVrCertAFC,VrCertfcado=@pmVrCertfcado,Periodos=@pmPeriodos,VrTotalBase=@pmVrTotalBase,OrignPorc=@pmOrignPorc,FecUpdate=@pmFecUpdate ,VrSalud=@pmVrSalud WHERE IdEmpleado=@pmIdEmpleado AND nAnno=@pmnAnno AND nMes=@pmnMes GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS OFF GO CREATE PROCEDURE paUpNomVacDet @pmIdEmpleado VARCHAR(16),@pmNumero INT,@pmItem INT,@pmIdConcepto VARCHAR(4),@pmDetalle VARCHAR(100),@pmCantidad INT,@pmVrDevgado MONEY,@pmVrDeducido MONEY ,@pmTarifa DECIMAL(10,4),@pmVrBaseLiq MONEY,@pmVrCertfcado MONEY,@pmVrPension MONEY,@pmNPrestamo INT,@pmNCuota INT,@pmIdPeriodo VARCHAR(8),@pmVrSalud MONEY AS UPDATE Trn_NomVacDet SET IdConcepto=@pmIdConcepto,Detalle=@pmDetalle,Cantidad=@pmCantidad,VrDevgado=@pmVrDevgado,VrDeducido=@pmVrDeducido,Tarifa=@pmTarifa,VrBaseLiq=@pmVrBaseLiq,VrCertfcado=@pmVrCertfcado ,VrPension=@pmVrPension,VrSalud=@pmVrSalud,NPrestamo=@pmNPrestamo,NCuota=@pmNCuota,IdPeriodo=@pmIdPeriodo WHERE IdEmpleado=@pmIdEmpleado AND Numero=@pmNumero AND Item=@pmItem GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS OFF GO CREATE PROCEDURE paUptm_Nomina @pmtmNumero VARCHAR(5),@pmtmIdEmpleado VARCHAR(16),@pmtmItem INT,@pmtmIdConcepto VARCHAR(4),@pmtmClaseCon VARCHAR(10),@pmtmDetalle VARCHAR(100),@pmtmCantDevg DECIMAL(10,4) ,@pmtmCantDed DECIMAL(10,4),@pmtmVrUnitario MONEY,@pmtmVrTotDevg MONEY,@pmtmVrTotDed MONEY,@pmtmUnidad VARCHAR(10),@pmtmClaseLiq VARCHAR(10),@pmtmVrOrigen MONEY,@pmtmTarifa DECIMAL(10,4),@pmtmDiasCalc DECIMAL(10,4) ,@pmtmDiasNov DECIMAL(10,4),@pmtmCodFondo VARCHAR(8),@pmtmNPrestamo INT,@pmtmNCuota INT,@pmtmOrigCargue VARCHAR(10),@pmtmVrBaseLiq MONEY,@pmtmNContto INT,@pmtmFecIni SMALLDATETIME,@pmtmFecFin SMALLDATETIME,@pmtmIdPeriodo VARCHAR(8) ,@pmtmVrPension MONEY,@pmtmVrSalud MONEY AS UPDATE tm_Nomina SET tmIdConcepto=@pmtmIdConcepto,tmClaseCon=@pmtmClaseCon,tmDetalle=@pmtmDetalle,tmCantDevg=@pmtmCantDevg,tmCantDed=@pmtmCantDed,tmVrUnitario=@pmtmVrUnitario,tmVrTotDevg=@pmtmVrTotDevg,tmVrTotDed=@pmtmVrTotDed ,tmUnidad=@pmtmUnidad,tmClaseLiq=@pmtmClaseLiq,tmVrOrigen=@pmtmVrOrigen,tmTarifa=@pmtmTarifa,tmDiasCalc=@pmtmDiasCalc,tmDiasNov=@pmtmDiasNov,tmCodFondo=@pmtmCodFondo,tmNPrestamo=@pmtmNPrestamo,tmNCuota=@pmtmNCuota,tmOrigCargue=@pmtmOrigCargue ,tmVrBaseLiq=@pmtmVrBaseLiq,tmNContto=@pmtmNContto,tmFecIni=@pmtmFecIni,tmFecFin=@pmtmFecFin,tmIdPeriodo=@pmtmIdPeriodo,tmVrPension=@pmtmVrPension,tmVrSalud=@pmtmVrSalud WHERE tmNumero=@pmtmNumero AND tmIdEmpleado=@pmtmIdEmpleado AND tmItem=@pmtmItem GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_NULLS OFF GO CREATE PROCEDURE paInsMayContratos @pmNContrato INT,@pmIdCia CHAR(2),@pmFecha SMALLDATETIME,@pmIdCliente VARCHAR(16),@pmIdAgencia VARCHAR(16),@pmIdTipo VARCHAR(4),@pmIdBonif VARCHAR(4),@pmConvBonif BIT,@pmCuotaFija BIT ,@pmCondPago BIT,@pmDiasGracia INT,@pmModPlazo VARCHAR(10),@pmPlazo INT,@pmFechaVence SMALLDATETIME,@pmModRecursos VARCHAR(150),@pmVrInversion MONEY,@pmVrInventario MONEY,@pmVrCuota MONEY,@pmModCuota VARCHAR(10) ,@pmIdBandera VARCHAR(4),@pmAprobJunta INT,@pmFechaAprob SMALLDATETIME,@pmObservAprob VARCHAR(250),@pmObservacion VARCHAR(250),@pmIdVend VARCHAR(16),@pmIdEstado VARCHAR(4),@pmTimeSys SMALLDATETIME,@pmIdCiaCrea CHAR(2),@pmIdUsuario VARCHAR(11) ,@pmFechaInicio SMALLDATETIME,@pmCuotaMin DECIMAL(14,4),@pmTipoAprob VARCHAR(10),@pmNumInterno VARCHAR(20),@pmInvCombus DECIMAL(14,4) AS INSERT INTO Trn_MayContratos (NContrato,IdCia,Fecha,IdCliente,IdAgencia,IdTipo,IdBonif,ConvBonif,CuotaFija,CondPago,DiasGracia,ModPlazo,Plazo,FechaVence,ModRecursos,VrInversion,VrInventario,VrCuota,ModCuota,IdBandera,AprobJunta,FechaAprob ,ObservAprob,Observacion,IdVend,IdEstado,TimeSys,IdCiaCrea,IdUsuario,FechaInicio,CuotaMin,TipoAprob,NumInterno,InvCombus) VALUES (@pmNContrato,@pmIdCia,@pmFecha,@pmIdCliente,@pmIdAgencia,@pmIdTipo,@pmIdBonif,@pmConvBonif,@pmCuotaFija,@pmCondPago,@pmDiasGracia,@pmModPlazo,@pmPlazo,@pmFechaVence,@pmModRecursos,@pmVrInversion ,@pmVrInventario,@pmVrCuota,@pmModCuota,@pmIdBandera,@pmAprobJunta,@pmFechaAprob,@pmObservAprob,@pmObservacion,@pmIdVend,@pmIdEstado,@pmTimeSys,@pmIdCiaCrea,@pmIdUsuario,@pmFechaInicio,@pmCuotaMin,@pmTipoAprob,@pmNumInterno,@pmInvCombus) GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_NULLS ON GO CREATE PROCEDURE paInsPresupuesto @pmNumero INT,@pmIdCia CHAR(2),@pmnAnno INT,@pmnMes INT ,@pmObservacion VARCHAR(250),@pmIdEstado VARCHAR(4),@pmTipoPsto INT, @pmDetCentros BIT ,@pmTimeSys SMALLDATETIME,@pmIdCiaCrea CHAR(2),@pmIdUsuario VARCHAR(11) AS INSERT INTO Trn_Presupuesto (Numero,IdCia,nAnno,nMes,Observacion,IdEstado,TimeSys,IdCiaCrea,IdUsuario,TipoPsto,DetCentros) VALUES (@pmNumero,@pmIdCia,@pmnAnno,@pmnMes,@pmObservacion,@pmIdEstado,@pmTimeSys,@pmIdCiaCrea,@pmIdUsuario,@pmTipoPsto, @pmDetCentros) GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS OFF GO CREATE PROCEDURE paInsTanqDispens @pmIdMngra VARCHAR(10),@pmNumDisp VARCHAR(4),@pmNumPos VARCHAR(4),@pmNumMngra VARCHAR(4),@pmIdTanque VARCHAR(4),@pmDescripcion VARCHAR(50) ,@pmLecGalAnt DECIMAL(14,4),@pmLecGalUlt DECIMAL(14,4),@pmLecPesAnt MONEY,@pmLecPesUlt MONEY,@pmLtaPrec CHAR(1),@pmFechaAdd SMALLDATETIME,@pmInactivo BIT,@pmLecGalMax DECIMAL(14,4) AS INSERT INTO TanqDispens (IdMngra,NumDisp,NumPos,NumMngra,IdTanque,Descripcion,LecGalAnt,LecGalUlt,LecPesAnt,LecPesUlt,LtaPrec,FechaAdd,Inactivo,LecGalMax) VALUES (@pmIdMngra,@pmNumDisp,@pmNumPos,@pmNumMngra,@pmIdTanque,@pmDescripcion,@pmLecGalAnt,@pmLecGalUlt,@pmLecPesAnt,@pmLecPesUlt,@pmLtaPrec,@pmFechaAdd,@pmInactivo,@pmLecGalMax) GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_NULLS OFF GO CREATE PROCEDURE paInstm_BalCom @pmtmEst CHAR(2),@pmtmIdCuenta VARCHAR(16),@pmtmItem INT,@pmtmIdTercero VARCHAR(16) ,@pmtmIdVehiculo VARCHAR(10),@pmtmIdCCosto VARCHAR(16),@pmtmIdSubCos VARCHAR(16),@pmtmCodAgncia VARCHAR(16) ,@pmtmTipo VARCHAR(4),@pmtmSaldoAnterior MONEY,@pmtmTotalDebitos MONEY,@pmtmTotalCreditos MONEY,@pmtmNuevoSaldo MONEY ,@pmtmVrPresupto MONEY AS INSERT INTO tm_BalCom (tmEst,tmIdCuenta,tmItem,tmIdTercero,tmIdVehiculo,tmIdCCosto,tmIdSubCos,tmCodAgncia,tmTipo,tmSaldoAnterior ,tmTotalDebitos,tmTotalCreditos,tmNuevoSaldo,tmVrPresupto) VALUES (@pmtmEst,@pmtmIdCuenta,@pmtmItem,@pmtmIdTercero,@pmtmIdVehiculo,@pmtmIdCCosto,@pmtmIdSubCos,@pmtmCodAgncia ,@pmtmTipo,@pmtmSaldoAnterior,@pmtmTotalDebitos,@pmtmTotalCreditos,@pmtmNuevoSaldo,@pmtmVrPresupto) GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_NULLS OFF GO CREATE PROCEDURE paQryMayContratos @pmNContrato INT,@pmIdCia CHAR(2) AS SELECT NContrato,IdCia,Fecha,IdCliente,IdAgencia,IdTipo,IdBonif,ConvBonif,CuotaFija,CondPago,DiasGracia,ModPlazo,Plazo ,FechaVence,ModRecursos,VrInversion,VrInventario,VrCuota,ModCuota,IdBandera,IdVend,AprobJunta,FechaAprob,ObservAprob ,Observacion,IdEstado,FechaInicio,CuotaMin,TipoAprob,NumInterno,InvCombus,TimeSys,FecUpdate,IdCiaCrea,IdUsuario FROM Trn_MayContratos WHERE NContrato=@pmNContrato AND IdCia=@pmIdCia GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS OFF GO CREATE PROCEDURE paQryMayContratos_Cr @pmNContratoIni INT=Null,@pmNContratoFin INT=Null,@pmIdCia CHAR(2)=Null ,@pmFechaIni SMALLDATETIME=Null,@pmFechaFin SMALLDATETIME=Null,@pmIdCliente VARCHAR(16)=Null,@pmIdAgencia VARCHAR(16)=Null ,@pmIdTipo VARCHAR(4)=Null,@pmIdBonif VARCHAR(4)=Null,@pmConvBonif BIT=Null,@pmAprobJuntaIni INT=Null,@pmAprobJuntaFin INT=Null ,@pmIdEstado VARCHAR(4)=Null,@pmIdVend VARCHAR(16)=Null AS SELECT C.NContrato AS NumContrato,C.IdCia AS CdCia,Compania,Fecha,IdCliente,T.RazonSocial AS NomCliente,C.IdAgencia AS CdAgencia,Agencia,C.IdTipo AS CdTipo,TipContrato,C.IdBonif AS CdBonif,TipoBonif ,ConvBonif,CuotaFija,CondPago,C.DiasGracia AS Dias_Gracia,ModPlazo,Plazo,FechaVence,ModRecursos,VrInversion,VrInventario,VrCuota,ModCuota,C.IdBandera AS CdBandera,TipoBandera ,C.IdVend AS CdVend,V.RazonSocial AS Vendedor,AprobJunta,FechaAprob,ObservAprob,C.Observacion AS Observ,C.IdEstado AS CdEstado,Estado,NColor,TimeSys,FecUpdate,IdCiaCrea,C.IdUsuario AS CdUsuario,Usuario ,FechaInicio,CuotaMin,TipoAprob,NumInterno,InvCombus --datos de bonificaciones ,Item,TipoProd,VrInicial,VrFinal,Unidad,Tarifa,TipoTarifa,Periodo,CdProducto,DescripProd,CdSubgrupo,Subgrupo --Información del tercero ,T.TipoId AS TercTipo,T.Dv AS TercDv,T.Codigo AS TercCodigo,T.NomCial AS TercNomCial,T.SiglaRaz AS TercSigal,T.Direccion AS TercDireccion ,T.IdLocal AS TercCdCiudad,L.Localidad AS NomCiudad,L.IdDep AS CdDep,D.Departamento AS TercDpto,T.Telefono AS TercTelefono,T.Fax AS TercFax,T.e_mail AS TercEmail ,T.SitioWeb AS TercSitioWeb,T.IdSector AS CdSector,SectorEco,T.IdRegimen AS CdRegimen,Regimen,T.TipEnte AS TercTipEnte --información del cliente ,NitRepLeg,NomRepLeg,CLI.NitContac AS ClieNitContac,CLI.NomContac AS ClieNomContacto,CLI.TelContac AS ClieTelContac,CLI.emlContac AS ClieEmailContac,CargContac,DiasEntga,CLI.IdSzona AS CdSubzona,Subzona,Zona ,CLI.IdGrupo AS CdGrupoCli,GrupoClie,CLI.IdClase AS CdClaseCta,NumCuenta,CLI.IdBanco AS CdBanco,Banco,ExcIva,LiqFletes,CLI.FactSold AS FactSoldicom,Autoret,VrCupo,VrSaldo,Contrato ,A.DirAgncia AS AgeDireccion,A.TelAgncia AS AgeTelefono,A.FaxAgncia AS AgeFax,A.IdLocal AS AgeIdCiudad,LA.Localidad AS AgeCiudad,LA.IdDep AS AgeCodDep,DA.Departamento AS AgeDpto,A.Referencia AS AgeReferencia FROM Trn_MayContratos AS C INNER JOIN MayTipos AS TC ON C.IdTipo=TC.IdTipo INNER JOIN MayTiposBonif AS B ON C.IdBonif=B.IdBonif INNER JOIN Terceros AS T ON C.IdCliente=T.IdTercero INNER JOIN Companias AS CN ON C.IdCia=CN.IdCia INNER JOIN MayEstados AS E ON C.IdEstado=E.IdEstado INNER JOIN TiposBan AS TB ON C.IdBandera=TB.IdBandera INNER JOIN adm_Usuarios AS U ON C.IdUsuario=U.IdUsuario INNER JOIN Terceros AS V ON C.IdVend=V.IdTercero INNER JOIN Trn_MayBonif AS MB ON C.NContrato=MB.NContrato AND C.IdCia=MB.IdCia INNER JOIN TercCliente AS CLI ON C.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 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 Agencias AS A ON C.IdAgencia=A.IdAgencia LEFT JOIN SubGrupos AS SG ON MB.CdSubgrupo=SG.IdSubgrupo LEFT JOIN ProdMcias AS PM ON MB.CdProducto=PM.IdProducto LEFT JOIN Localidades AS LA ON A.IdLocal=LA.IdLocal LEFT JOIN Departamentos AS DA ON LA.IdDep=DA.IdDep WHERE C.IdCia LIKE ISNULL(@pmIdCia,'%%') AND C.NContrato BETWEEN ISNULL(@pmNContratoIni,0) AND ISNULL(@pmNContratoFin,2147483647) AND IdCliente LIKE ISNULL(@pmIdCliente,'%') AND C.IdAgencia LIKE ISNULL(@pmIdAgencia,'%') AND C.IdTipo LIKE ISNULL(@pmIdTipo,'%') AND C.IdBonif LIKE ISNULL(@pmIdBonif,'%') AND (ConvBonif=ISNULL(@pmConvBonif,0) or ConvBonif=ISNULL(@pmConvBonif,1)) AND AprobJunta BETWEEN ISNULL(@pmAprobJuntaIni,-1) AND ISNULL(@pmAprobJuntaFin,2147483647) AND C.IdEstado LIKE ISNULL(@pmIdEstado,'%') AND C.IdVend LIKE ISNULL(@pmIdVend,'%') AND (Fecha>=ISNULL(@pmFechaIni,CAST('19100101' AS SMALLDATETIME)) AND Fecha<=ISNULL(@pmFechaFin,CAST('20781230' AS SMALLDATETIME))) ORDER BY C.NContrato GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS OFF GO CREATE PROCEDURE paQryMayContratosAct @pmIdCia CHAR(2)=Null AS SELECT C.NContrato AS NumContrato,IdCia,Fecha,IdCliente,T.RazonSocial AS NomCliente,C.IdAgencia AS CdAgencia,Agencia ,IdTipo,C.IdBonif AS CdBonif,TipoBonif,AboCartera,ConvBonif,CuotaFija,CondPago,C.DiasGracia AS Dias_Gracia,ModPlazo,C.Plazo AS PlazoCont ,FechaVence,VrCuota,ModCuota,C.IdBandera AS CdBandera,C.IdVend AS CdVend,C.IdEstado AS CdEstado,FechaInicio,CuotaMin,TipoAprob,NumInterno,InvCombus --datos del cliente ,CL.IdPlazo AS CliCdPlazo,PZ.Plazo AS CliPlazo,PZ.NVmto AS CliNVce,PZ.DiasPago AS CliDiasPago ,CL.IdVend AS CliVend,VrCupo,VrSaldo,CL.IdEstado AS CliEstado --datos de agencia ,A.IdPlazo AS AgeCdPlazo,PA.Plazo AS AgePlazo,PA.NVmto AS AgeNVce,PA.DiasPago AS AgeDiasPago ,A.IdVend AS AgeCdVend,VrCupoCre,VrSaldoAct,Referencia,A.IdEstado AS AgeEstado,A.Inactivo AS AgeInactivo FROM Trn_MayContratos AS C INNER JOIN MayTiposBonif AS B ON C.IdBonif=B.IdBonif INNER JOIN Terceros AS T ON C.IdCliente=T.IdTercero INNER JOIN TercCliente AS CL ON C.IdCliente=CL.IdClie INNER JOIN Plazos AS PZ ON CL.IdPlazo=PZ.IdPlazo LEFT JOIN Agencias AS A ON C.IdAgencia=A.IdAgencia LEFT JOIN Plazos AS PA ON A.IdPlazo=PA.IdPlazo WHERE C.IdEstado<>'0002' AND C.IdEstado<>'9999' AND AprobJunta>0 AND ( ConvBonif<>0 OR CuotaFija<>0) AND CL.Inactivo=0 AND IdCia LIKE ISNULL(@pmIdCia,'%%') ORDER BY IdCia,C.NContrato GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS OFF GO CREATE PROCEDURE paQryMayContratosLta @pmFechaIni SMALLDATETIME,@pmFechaFin SMALLDATETIME,@pmIdCia CHAR(2)=Null ,@pmNContratoIni INT=Null,@pmNContratoFin INT=Null,@pmIdCliente VARCHAR(16)=Null,@pmIdAgencia VARCHAR(16)=Null,@pmIdTipo VARCHAR(4)=Null ,@pmIdBonif VARCHAR(4)=Null,@pmConvBonif BIT=Null,@pmAprobJuntaIni INT=Null,@pmAprobJuntaFin INT=Null,@pmIdEstado VARCHAR(4)=Null ,@pmIdVend VARCHAR(16)=Null AS SELECT C.NContrato AS NumContrato,C.IdCia AS CdCia,Compania,Fecha,FechaInicio,IdCliente,T.RazonSocial AS NomCliente,C.IdAgencia AS CdAgencia,Agencia,C.IdTipo AS CdTipo,TipContrato,C.IdBonif AS CdBonif,TipoBonif ,ConvBonif,CuotaFija,CondPago,C.DiasGracia AS Dias_Gracia,ModPlazo,Plazo,FechaVence,ModRecursos,VrInversion,VrInventario,VrCuota,CuotaMin,ModCuota,C.IdBandera AS CdBandera,TipoBandera ,C.IdVend AS CdVend,V.RazonSocial AS Vendedor,TipoAprob,AprobJunta,FechaAprob,ObservAprob,C.Observacion AS Observ,NumInterno,InvCombus,C.IdEstado AS CdEstado,Estado,NColor,TimeSys,FecUpdate,IdCiaCrea,C.IdUsuario AS CdUsuario,Usuario FROM Trn_MayContratos AS C INNER JOIN MayTipos AS TC ON C.IdTipo=TC.IdTipo INNER JOIN MayTiposBonif AS B ON C.IdBonif=B.IdBonif INNER JOIN Terceros AS T ON C.IdCliente=T.IdTercero INNER JOIN Companias AS CN ON C.IdCia=CN.IdCia INNER JOIN MayEstados AS E ON C.IdEstado=E.IdEstado INNER JOIN TiposBan AS TB ON C.IdBandera=TB.IdBandera INNER JOIN adm_Usuarios AS U ON C.IdUsuario=U.IdUsuario INNER JOIN Terceros AS V ON C.IdVend=V.IdTercero LEFT JOIN Agencias AS A ON C.IdAgencia=A.IdAgencia WHERE Fecha BETWEEN @pmFechaIni AND @pmFechaFin AND C.IdCia LIKE ISNULL(@pmIdCia,'%%') AND C.NContrato BETWEEN ISNULL(@pmNContratoIni,0) AND ISNULL(@pmNContratoFin,2147483647) AND IdCliente LIKE ISNULL(@pmIdCliente,'%') AND C.IdAgencia LIKE ISNULL(@pmIdAgencia,'%') AND C.IdTipo LIKE ISNULL(@pmIdTipo,'%') AND C.IdBonif LIKE ISNULL(@pmIdBonif,'%') AND (ConvBonif=ISNULL(@pmConvBonif,0) or ConvBonif=ISNULL(@pmConvBonif,1)) AND AprobJunta BETWEEN ISNULL(@pmAprobJuntaIni,-1) AND ISNULL(@pmAprobJuntaFin,2147483647) AND C.IdEstado LIKE ISNULL(@pmIdEstado,'%') AND C.IdVend LIKE ISNULL(@pmIdVend,'%') ORDER BY C.NContrato GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_NULLS ON GO CREATE PROCEDURE paQryPresupuesto @pmNumero INT,@pmIdCia CHAR(2) AS SELECT Numero,IdCia,nAnno,nMes,Observacion,IdEstado,TimeSys,FecUpdate,IdCiaCrea,IdUsuario ,TipoPsto,DetCentros FROM Trn_Presupuesto WHERE Numero=@pmNumero AND IdCia=@pmIdCia GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS OFF GO CREATE PROCEDURE paQryPresupuesto_Cr @pmNumero INT,@pmIdCia CHAR(2) AS SELECT P.Numero AS NumPsto,P.IdCia AS CdCia,Compania,nAnno,nMes,Observacion,Item,D.IdCuenta AS CdCuenta,NomCuenta,D.IdCCosto AS CdCentro,CCosto ,D.IdSubCos AS CdSubcentro,SubCosto,VrPresupuesto,SaldoAnterior,TotalDebitos,TotalCreditos,Movimiento,CentroCosto,SubCentro,Naturaleza ,D.FecUpdate AS FecModifica,CdUsuario,UM.Usuario AS UsuarioModifica ,P.IdEstado AS CdEstado,Estado,TimeSys,P.FecUpdate AS Fec_Update,IdCiaCrea,P.IdUsuario AS Id_Usuario,U.Usuario AS UsuarioCrea ,TipoPsto,DetCentros FROM Trn_Presupuesto AS P INNER JOIN Trn_PresDetalle AS D ON P.Numero=D.Numero AND P.IdCia=D.IdCia INNER JOIN Companias AS CN ON P.IdCia=CN.IdCia INNER JOIN EstadoDoc AS ED ON P.IdEstado=ED.IdEstado INNER JOIN adm_Usuarios AS U ON P.IdUsuario=U.IdUsuario INNER JOIN Puc AS PC ON D.IdCuenta=PC.IdCuenta LEFT JOIN CentroCosto AS CC ON D.IdCCosto=CC.IdCCosto LEFT JOIN SubCentros AS SC ON D.IdSubCos=SC.IdSubCos LEFT JOIN adm_Usuarios AS UM ON D.CdUsuario=UM.IdUsuario WHERE P.Numero=@pmNumero AND P.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 paQryPresupuestoMes @pmIdCia CHAR(2),@pmnAnno INT,@pmnMes INT AS SELECT Numero,IdCia,nAnno,nMes,Observacion,IdEstado,TimeSys,FecUpdate,IdCiaCrea,IdUsuario ,TipoPsto,DetCentros FROM Trn_Presupuesto WHERE IdCia=@pmIdCia AND nAnno=@pmnAnno AND nMes=@pmnMes ORDER BY Numero GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS OFF GO CREATE PROCEDURE paQryPresupuestoPer @pmnAnno INT,@pmnMesIni INT,@pmnMesFin INT,@pmIdCia CHAR(2)=Null AS SELECT P.Numero AS NumPsto,P.IdCia AS CdCia,Compania,nAnno,nMes,Observacion,Item,D.IdCuenta AS CdCuenta,NomCuenta,D.IdCCosto AS CdCentro,CCosto ,D.IdSubCos AS CdSubcentro,SubCosto,VrPresupuesto,SaldoAnterior,TotalDebitos,TotalCreditos,Movimiento,CentroCosto,SubCentro,Naturaleza ,D.FecUpdate AS FecModifica,CdUsuario,UM.Usuario AS UsuarioModifica ,P.IdEstado AS CdEstado,Estado,TimeSys,P.FecUpdate AS Fec_Update,IdCiaCrea,P.IdUsuario AS Id_Usuario,U.Usuario AS UsuarioCrea ,TipoPsto,DetCentros FROM Trn_Presupuesto AS P INNER JOIN Trn_PresDetalle AS D ON P.Numero=D.Numero AND P.IdCia=D.IdCia INNER JOIN Companias AS CN ON P.IdCia=CN.IdCia INNER JOIN EstadoDoc AS ED ON P.IdEstado=ED.IdEstado INNER JOIN adm_Usuarios AS U ON P.IdUsuario=U.IdUsuario INNER JOIN Puc AS PC ON D.IdCuenta=PC.IdCuenta LEFT JOIN CentroCosto AS CC ON D.IdCCosto=CC.IdCCosto LEFT JOIN SubCentros AS SC ON D.IdSubCos=SC.IdSubCos LEFT JOIN adm_Usuarios AS UM ON D.CdUsuario=UM.IdUsuario WHERE nAnno=@pmnAnno AND nMes BETWEEN @pmnMesIni AND @pmnMesFin AND P.IdCia LIKE ISNULL(@pmIdCia,'%%') ORDER BY nMes GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS OFF GO CREATE PROCEDURE paQryTanqDispens @pmIdMngra VARCHAR(10) AS SELECT IdMngra,NumDisp,NumPos,NumMngra,IdTanque,Descripcion,LecGalAnt,LecGalUlt,LecPesAnt,LecPesUlt ,LtaPrec,LecGalMax,FechaAdd,FechaUpdate,Inactivo FROM TanqDispens WHERE IdMngra=@pmIdMngra GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS OFF GO CREATE PROCEDURE paQryTanqDispensLta @pmIdTanque VARCHAR(4)=Null,@pmIdProducto VARCHAR(16)=Null AS SELECT IdMngra,NumDisp,NumPos,NumMngra,D.IdTanque AS NumTanq,D.Descripcion AS DescripDisp,LecGalAnt,LecGalUlt,LecPesAnt,LecPesUlt,LtaPrec ,T.IdProducto AS CodProd,DescripProd,T.Descripcion AS DescripTanq,CapTanq,NivAgua,CapNeta,SaldoActual,TnqGas,TnqUnido,TnqVirtual,NColor ,D.FechaAdd AS Fec_Add,D.FechaUpdate AS Fec_Upd,LecGalMax FROM TanqDispens AS D INNER JOIN Tanques AS T ON D.IdTanque=T.IdTanque INNER JOIN ProdMcias AS P ON T.IdProducto=P.IdProducto WHERE D.Inactivo=0 AND D.IdTanque LIKE ISNULL(@pmIdTanque,'%') AND T.IdProducto LIKE ISNULL(@pmIdProducto,'%') ORDER BY D.IdTanque,IdMngra GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS OFF GO CREATE PROCEDURE paQryTanqDispensMan @pmNumDisp VARCHAR(4),@pmNumPos VARCHAR(4),@pmNumMngra VARCHAR(4) AS SELECT IdMngra,NumDisp,NumPos,NumMngra,D.IdTanque AS CdTanque,D.Descripcion AS DescripDisp,LecGalAnt,LecGalUlt ,LecPesAnt,LecPesUlt,LtaPrec,D.Inactivo AS Disp_inactivo,LecGalMax --tanques ,T.IdProducto AS CdProd,T.Descripcion AS Tan_Descrip,CapTanq,NivAgua,CapNeta,SaldoActual ,TnqGas,TnqUnido,TnqVirtual,T.Inactivo AS Tan_inactivo --productos ,DescripProd,Referencia,TipoRef,IdBodega,IdUnd,Tanques,ExtciaAct,Precio1 ,Precio2,Precio3,Precio4,Precio5,P.Inactivo AS Pro_Inactivo FROM TanqDispens AS D INNER JOIN Tanques AS T ON D.IdTanque=T.IdTanque INNER JOIN ProdMcias AS P ON T.IdProducto=P.IdProducto WHERE NumDisp=@pmNumDisp AND NumPos=@pmNumPos AND NumMngra=@pmNumMngra ORDER BY IdMngra GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS OFF GO CREATE PROCEDURE paQryTanqDispensPro @pmIdMngra VARCHAR(10) AS SELECT IdMngra,NumDisp,NumPos,NumMngra,D.IdTanque AS CdTanque,D.Descripcion AS DescripDisp,LecGalAnt,LecGalUlt ,LecPesAnt,LecPesUlt,LtaPrec,D.Inactivo AS Disp_inactivo,LecGalMax --tanques ,T.IdProducto AS CdProd,T.Descripcion AS Tan_Descrip,CapTanq,NivAgua,CapNeta,SaldoActual ,TnqGas,TnqUnido,TnqVirtual,T.Inactivo AS Tan_inactivo --productos ,DescripProd,Referencia,TipoRef,IdBodega,IdUnd,Tanques,ExtciaAct,Precio1 ,Precio2,Precio3,Precio4,Precio5,P.Inactivo AS Pro_Inactivo FROM TanqDispens AS D INNER JOIN Tanques AS T ON D.IdTanque=T.IdTanque INNER JOIN ProdMcias AS P ON T.IdProducto=P.IdProducto WHERE IdMngra=@pmIdMngra GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS OFF GO CREATE PROCEDURE paQrytm_BalCom @pmtmEst CHAR(2),@pmtmIdCuenta VARCHAR(16)=Null ,@pmtmIdTercero VARCHAR(16)=Null,@pmtmCodAgncia VARCHAR(16)=Null AS SELECT BC.tmIdCuenta AS IdCuent,NomCuenta,tmTipo,tmSaldoAnterior,tmTotalDebitos,tmTotalCreditos ,tmSaldoAnterior+tmTotalDebitos-tmTotalCreditos AS NuevoSaldo ,tmIdTercero,RazonSocial,tmCodAgncia,A.Agencia AS NomAgencia,tmIdVehiculo,tmIdCCosto,CCosto,tmIdSubCos,SubCosto ,tmVrPresupto --información del tercero ,TipoId,Dv,Direccion,T.IdLocal AS CdCiudad,Localidad,L.IdDep AS CdDepto,Departamento,Telefono --información de agencia y vehículos ,CodAgencia,Referencia--,NumVeh ,tmItem,tmNivel1,tmNivel2,tmNivel4,tmNivel6,tmNivel9,tmNivel12 FROM tm_BalCom AS BC INNER JOIN Puc AS P ON BC.tmIdCuenta=P.IdCuenta INNER JOIN Terceros AS T ON BC.tmIdTercero=T.IdTercero INNER JOIN Localidades AS L ON T.IdLocal=L.IdLocal INNER JOIN Departamentos AS D ON L.IdDep=D.IdDep INNER JOIN CentroCosto AS CC ON BC.tmIdCCosto=CC.IdCCosto LEFT JOIN SubCentros AS SC ON BC.tmIdSubCos=SC.IdSubCos LEFT JOIN tm_NivCue AS NV ON BC.tmIdCuenta=NV.tmIdCuenta LEFT JOIN Agencias AS A ON BC.tmCodAgncia=A.IdAgencia -- LEFT JOIN Vehiculos AS V ON BC.tmIdVehiculo=V.IdVehiculo WHERE tmEst=@pmtmEst AND BC.tmIdCuenta LIKE ISNULL(@pmtmIdCuenta,'%') AND tmIdTercero LIKE ISNULL(@pmtmIdTercero,'%') AND tmCodAgncia LIKE ISNULL(@pmtmCodAgncia,'%') ORDER BY BC.tmIdCuenta,RazonSocial,tmCodAgncia,tmIdCCosto,tmItem GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_NULLS OFF GO CREATE PROCEDURE paUpMayContratos @pmNContrato INT,@pmIdCia CHAR(2),@pmFecha SMALLDATETIME,@pmIdCliente VARCHAR(16),@pmIdAgencia VARCHAR(16),@pmIdTipo VARCHAR(4),@pmIdBonif VARCHAR(4),@pmConvBonif BIT ,@pmCuotaFija BIT,@pmCondPago BIT,@pmDiasGracia INT,@pmModPlazo VARCHAR(10),@pmPlazo INT,@pmFechaVence SMALLDATETIME,@pmModRecursos VARCHAR(150),@pmVrInversion MONEY,@pmVrInventario MONEY,@pmVrCuota MONEY ,@pmModCuota VARCHAR(10),@pmIdBandera VARCHAR(4),@pmAprobJunta INT,@pmFechaAprob SMALLDATETIME,@pmObservAprob VARCHAR(250),@pmObservacion VARCHAR(250),@pmIdVend VARCHAR(16),@pmIdEstado VARCHAR(4),@pmFecUpdate SMALLDATETIME ,@pmFechaInicio SMALLDATETIME,@pmCuotaMin DECIMAL(14,4),@pmTipoAprob VARCHAR(10),@pmNumInterno VARCHAR(20),@pmInvCombus DECIMAL(14,4) AS UPDATE Trn_MayContratos SET Fecha=@pmFecha,IdCliente=@pmIdCliente,IdAgencia=@pmIdAgencia,IdTipo=@pmIdTipo,IdBonif=@pmIdBonif,ConvBonif=@pmConvBonif,CuotaFija=@pmCuotaFija,CondPago=@pmCondPago,DiasGracia=@pmDiasGracia ,ModPlazo=@pmModPlazo,Plazo=@pmPlazo,FechaVence=@pmFechaVence,ModRecursos=@pmModRecursos,VrInversion=@pmVrInversion,VrInventario=@pmVrInventario,VrCuota=@pmVrCuota,ModCuota=@pmModCuota,IdBandera=@pmIdBandera ,AprobJunta=@pmAprobJunta,FechaAprob=@pmFechaAprob,ObservAprob=@pmObservAprob,Observacion=@pmObservacion,IdEstado=@pmIdEstado,IdVend=@pmIdVend,FechaInicio=@pmFechaInicio,CuotaMin=@pmCuotaMin,TipoAprob=@pmTipoAprob ,NumInterno=@pmNumInterno,InvCombus=@pmInvCombus,FecUpdate=@pmFecUpdate WHERE NContrato=@pmNContrato AND IdCia=@pmIdCia GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_NULLS ON GO CREATE PROCEDURE paUpPresupuesto @pmNumero INT,@pmIdCia CHAR(2),@pmnAnno INT,@pmnMes INT,@pmObservacion VARCHAR(250),@pmIdEstado VARCHAR(4) ,@pmTipoPsto INT, @pmDetCentros BIT,@pmFecUpdate SMALLDATETIME AS UPDATE Trn_Presupuesto SET nAnno=@pmnAnno,nMes=@pmnMes,Observacion=@pmObservacion,IdEstado=@pmIdEstado ,TipoPsto=@pmTipoPsto, DetCentros=@pmDetCentros, FecUpdate=@pmFecUpdate WHERE Numero=@pmNumero AND IdCia=@pmIdCia GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS OFF GO CREATE PROCEDURE paUpTanqDispens @pmIdMngra VARCHAR(10),@pmNumDisp VARCHAR(4),@pmNumPos VARCHAR(4),@pmNumMngra VARCHAR(4),@pmIdTanque VARCHAR(4),@pmDescripcion VARCHAR(50),@pmLecGalAnt DECIMAL(14,4),@pmLecGalUlt DECIMAL(14,4) ,@pmLecPesAnt MONEY,@pmLecPesUlt MONEY,@pmLtaPrec CHAR(1),@pmFechaUpdate SMALLDATETIME,@pmInactivo BIT,@pmLecGalMax DECIMAL(14,4) AS UPDATE TanqDispens SET NumDisp=@pmNumDisp,NumPos=@pmNumPos,NumMngra=@pmNumMngra,IdTanque=@pmIdTanque,Descripcion=@pmDescripcion,LecGalAnt=@pmLecGalAnt,LecGalUlt=@pmLecGalUlt,LecPesAnt=@pmLecPesAnt,LecPesUlt=@pmLecPesUlt ,LtaPrec=@pmLtaPrec,FechaUpdate=@pmFechaUpdate,Inactivo=@pmInactivo,LecGalMax=@pmLecGalMax WHERE IdMngra=@pmIdMngra GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO