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

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

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

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

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

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

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

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

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

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

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

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE paInsNomVac @pmIdEmpleado VARCHAR(16),@pmNumero INT,@pmFecha SMALLDATETIME,@pmNContrato INT,@pmFecInicial SMALLDATETIME,@pmFecFinal SMALLDATETIME,@pmCantidad DECIMAL(10,4),@pmVrLiquida MONEY,@pmVrBase MONEY,@pmFecIniPmd SMALLDATETIME,@pmFecFinPmd SMALLDATETIME
,@pmDiasBase INT,@pmDiasNoRem INT,@pmDiasVac INT,@pmNNovedad INT,@pmNPeriodos INT,@pmCantRem INT,@pmVrRemndo MONEY,@pmVrTotDed MONEY,@pmVrSalario MONEY,@pmpVehiculo VARCHAR(10),@pmTipCom VARCHAR(3),@pmComprobante INT,@pmIdCiaCom CHAR(2),@pmObservacion VARCHAR(250),@pmLogPmdio VARCHAR(600)
,@pmIdEstado VARCHAR(4),@pmAnulado BIT,@pmDiasAjuste INT, @pmTimeSys SMALLDATETIME,@pmIdUsuario VARCHAR(11)
 AS 
 INSERT INTO Trn_NomVac (IdEmpleado,Numero,Fecha,NContrato,FecInicial,FecFinal,Cantidad,VrLiquida,VrBase,FecIniPmd,FecFinPmd,DiasBase,DiasNoRem,DiasVac,NNovedad,NPeriodos,CantRem,VrRemndo,VrTotDed,VrSalario,pVehiculo,DiasAjuste,TipCom,Comprobante,IdCiaCom,Observacion,LogPmdio,IdEstado,Anulado,TimeSys,IdUsuario)
 VALUES (@pmIdEmpleado,@pmNumero,@pmFecha,@pmNContrato,@pmFecInicial,@pmFecFinal,@pmCantidad,@pmVrLiquida,@pmVrBase,@pmFecIniPmd,@pmFecFinPmd,@pmDiasBase,@pmDiasNoRem,@pmDiasVac,@pmNNovedad,@pmNPeriodos,@pmCantRem,@pmVrRemndo,@pmVrTotDed,@pmVrSalario,@pmpVehiculo,@pmDiasAjuste
,@pmTipCom,@pmComprobante,@pmIdCiaCom,@pmObservacion,@pmLogPmdio,@pmIdEstado,@pmAnulado,@pmTimeSys,@pmIdUsuario)
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE paInsNomVac_Sel @pmIdEmpleado VARCHAR(16),@pmNewCedula VARCHAR(16)
 AS 
 INSERT INTO Trn_NomVac (IdEmpleado,Numero,Fecha,NContrato,FecInicial,FecFinal,Cantidad,VrLiquida,VrBase,FecIniPmd,FecFinPmd,DiasBase
,DiasNoRem,DiasVac,NNovedad,NPeriodos,CantRem,VrRemndo,VrTotDed,VrSalario,pVehiculo,DiasAjuste,TipCom,Comprobante,IdCiaCom,Observacion,LogPmdio,IdEstado,Anulado,TimeSys,IdUsuario)
 SELECT @pmNewCedula,Numero,Fecha,NContrato,FecInicial,FecFinal,Cantidad,VrLiquida,VrBase,FecIniPmd,FecFinPmd,DiasBase
,DiasNoRem,DiasVac,NNovedad,NPeriodos,CantRem,VrRemndo,VrTotDed,VrSalario,pVehiculo,DiasAjuste,TipCom,Comprobante,IdCiaCom,Observacion,LogPmdio,IdEstado,Anulado,TimeSys,IdUsuario
 FROM  Trn_NomVac WHERE IdEmpleado=@pmIdEmpleado
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE paInsNotas @pmTipDoc VARCHAR(3),@pmNumNota INT,@pmIdCia CHAR(2),@pmFecha SMALLDATETIME,@pmIdConcepto VARCHAR(4),@pmIdCliente VARCHAR(16),@pmIdAgencia VARCHAR(16),@pmFechaVence SMALLDATETIME,@pmVrSubTotal MONEY,@pmVrImpuesto MONEY,@pmVrRetencion MONEY,@pmVrReteICA MONEY,@pmVrReteIVA MONEY,@pmVrCosto MONEY,@pmVrSancion MONEY,@pmVrOtros MONEY,@pmVrNeto MONEY,@pmVrAplicado MONEY,@pmVrAnticipo MONEY,@pmCantidad DECIMAL(14,4),@pmBaseImp MONEY,@pmBaseRet MONEY,@pmBaseIca MONEY
,@pmBaseRiv MONEY,@pmTarifaIva DECIMAL(14,4),@pmTarifaRet DECIMAL(14,4),@pmTarifaIca DECIMAL(14,4),@pmTarifaRiv DECIMAL(14,4),@pmCodTarIva VARCHAR(4),@pmCodTarRet VARCHAR(4),@pmCodTarIca VARCHAR(4),@pmCodTarRiv VARCHAR(4),@pmIdCCosto VARCHAR(16),@pmIdSubCos VARCHAR(16),@pmIdVend VARCHAR(16),@pmTarifaCom DECIMAL(14,4),@pmCodTarCom VARCHAR(4),@pmCdCajero VARCHAR(11),@pmIdLocal VARCHAR(8),@pmReferencia VARCHAR(50),@pmpVehiculo VARCHAR(10),@pmCdConductor VARCHAR(16),@pmTipDcm VARCHAR(3),@pmDocumento INT
,@pmIdCiaDcm CHAR(2),@pmFecDcm SMALLDATETIME,@pmCdProducto VARCHAR(16),@pmCdForma VARCHAR(4),@pmNumCheque VARCHAR(20),@pmCdBanco VARCHAR(4),@pmMulPlazos BIT,@pmIdPlazo VARCHAR(4),@pmPrestCaja BIT,@pmModalidad VARCHAR(10),@pmTipCom VARCHAR(3),@pmComprobante INT,@pmIdCiaCom CHAR(2),@pmObservacion VARCHAR(250),@pmIdEstado VARCHAR(4),@pmnRemesa INT,@pmCdCiaRem CHAR(2),@pmItemRem INT,@pmOrigenAdd VARCHAR(10),@pmTimeSys SMALLDATETIME,@pmIdCiaCrea CHAR(2),@pmIdUsuario VARCHAR(11)
 AS 
 INSERT INTO Trn_Notas (TipDoc,NumNota,IdCia,Fecha,IdConcepto,IdCliente,IdAgencia,FechaVence,VrSubTotal,VrImpuesto,VrRetencion,VrReteICA,VrReteIVA,VrCosto,VrSancion,VrOtros,VrNeto,VrAplicado,VrAnticipo,Cantidad,BaseImp,BaseRet,BaseIca,BaseRiv,TarifaIva,TarifaRet,TarifaIca,TarifaRiv,CodTarIva,CodTarRet,CodTarIca,CodTarRiv,IdCCosto,IdSubCos,IdVend,TarifaCom,CodTarCom,CdCajero,IdLocal,Referencia,pVehiculo,CdConductor,TipDcm,Documento,IdCiaDcm,FecDcm,CdProducto,CdForma,NumCheque,CdBanco,MulPlazos,IdPlazo,PrestCaja,Modalidad,nRemesa,CdCiaRem,ItemRem,OrigenAdd,TipCom,Comprobante,IdCiaCom,Observacion,IdEstado,TimeSys,IdCiaCrea,IdUsuario)
 VALUES (@pmTipDoc,@pmNumNota,@pmIdCia,@pmFecha,@pmIdConcepto,@pmIdCliente,@pmIdAgencia,@pmFechaVence,@pmVrSubTotal,@pmVrImpuesto,@pmVrRetencion,@pmVrReteICA,@pmVrReteIVA,@pmVrCosto,@pmVrSancion,@pmVrOtros,@pmVrNeto,@pmVrAplicado,@pmVrAnticipo,@pmCantidad,@pmBaseImp,@pmBaseRet,@pmBaseIca,@pmBaseRiv,@pmTarifaIva,@pmTarifaRet,@pmTarifaIca,@pmTarifaRiv,@pmCodTarIva,@pmCodTarRet,@pmCodTarIca,@pmCodTarRiv,@pmIdCCosto,@pmIdSubCos,@pmIdVend,@pmTarifaCom,@pmCodTarCom,@pmCdCajero,@pmIdLocal,@pmReferencia,@pmpVehiculo
,@pmCdConductor,@pmTipDcm,@pmDocumento,@pmIdCiaDcm,@pmFecDcm,@pmCdProducto,@pmCdForma,@pmNumCheque,@pmCdBanco,@pmMulPlazos,@pmIdPlazo,@pmPrestCaja,@pmModalidad,@pmnRemesa,@pmCdCiaRem,@pmItemRem,@pmOrigenAdd,@pmTipCom,@pmComprobante,@pmIdCiaCom,@pmObservacion,@pmIdEstado,@pmTimeSys,@pmIdCiaCrea,@pmIdUsuario)
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE paQryNomVac @pmIdEmpleado VARCHAR(16),@pmNumero INT
 AS 
 SELECT IdEmpleado,Numero,Fecha,NContrato,FecInicial,FecFinal,Cantidad,VrLiquida,VrBase,FecIniPmd,FecFinPmd,DiasBase,DiasNoRem,DiasVac,NNovedad,NPeriodos,CantRem
,VrRemndo,VrTotDed,VrSalario,pVehiculo,DiasAjuste,TipCom,Comprobante,IdCiaCom,Observacion,LogPmdio,Anulado,IdEstado,TimeSys,FecUpdate,IdUsuario
 FROM Trn_NomVac
 WHERE IdEmpleado=@pmIdEmpleado AND Numero=@pmNumero
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE 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,DiasAjuste,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 paQryNomVac_Crr @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
 AS 
 SELECT Q.IdEmpleado AS IdEmpldo,Apellidos,Nombres,Numero,Fecha,Q.NContrato AS NumContrato,FecInicial,FecFinal,Cantidad,VrLiquida,VrBase,FecIniPmd,FecFinPmd,DiasBase,DiasNoRem
,DiasVac,NNovedad,NPeriodos,CantRem,VrRemndo,VrTotDed,Q.VrSalario AS Liq_SalBasico,Q.pVehiculo AS PlacaVeh,DiasAjuste,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
--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
 FROM Trn_NomVac AS Q 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 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 (Numero>=ISNULL(@pmNumero,0)  AND 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,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_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,DiasAjuste,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,DiasAjuste,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,DiasAjuste
,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 paQryNotas @pmTipDoc VARCHAR(3),@pmNumNota INT,@pmIdCia CHAR(2)
 AS 
 SELECT TipDoc,NumNota,IdCia,Fecha,IdConcepto,IdCliente,IdAgencia,FechaVence,VrSubTotal,VrImpuesto,VrRetencion,VrReteICA,VrReteIVA,VrCosto,VrSancion,VrOtros,VrNeto
,VrAplicado,VrAnticipo,Cantidad,BaseImp,BaseRet,BaseIca,BaseRiv,TarifaIva,TarifaRet,TarifaIca,TarifaRiv,CodTarIva,CodTarRet,CodTarIca,CodTarRiv,IdCCosto,IdSubCos
,IdVend,TarifaCom,CodTarCom,CdCajero,IdLocal,Referencia,pVehiculo,CdConductor,TipDcm,Documento,IdCiaDcm,FecDcm,CdProducto,CdForma,NumCheque,CdBanco
,MulPlazos,IdPlazo,PrestCaja,Modalidad,nRemesa,CdCiaRem,ItemRem,OrigenAdd,TipCom,Comprobante,IdCiaCom,Observacion,IdEstado,TimeSys,FecUpdate,IdCiaCrea,IdUsuario
 FROM Trn_Notas
 WHERE TipDoc=@pmTipDoc AND NumNota=@pmNumNota 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 paQryNotas_Cr @pmTipDoc VARCHAR(3),@pmNumNotaIni INT,@pmNumNotaFin INT,@pmIdCia CHAR(2)
 AS 
 SELECT N.TipDoc AS Tip_Doc,TipoDoc,NumNota,N.IdCia AS CdCia,Compania,N.Fecha AS FechaDoc,N.IdConcepto AS CdConcepto,Concepto,IdCliente,T.RazonSocial AS NomCliente
,N.IdAgencia AS IdAgenc,A.Agencia AS NomAgencia,CodAgencia,FechaVence,VrSubTotal,VrImpuesto,VrRetencion,VrReteICA,VrReteIVA,N.VrCosto AS VrTotCosto,VrSancion,VrOtros,VrNeto
,VrAplicado,VrAnticipo,Cantidad,BaseImp,BaseRet,BaseIca,BaseRiv,TarifaIva,TarifaRet,TarifaIca,TarifaRiv
,N.IdCCosto AS CdCentCosto,CC.CCosto AS NotCenCosto,N.IdSubCos AS CdSubCentro,SC.SubCosto AS NotSubCentro,N.IdVend AS NitVend,VN.RazonSocial AS Vendedor,TarifaCom
,N.IdLocal AS NotCdCiudad,LN.Localidad AS NotCiudad,N.Referencia AS NotReferencia,pVehiculo,CdConductor,TC.RazonSocial AS Conductor,TipDcm,N.Documento AS NumDocmento,IdCiaDcm,FecDcm,nRemesa,CdCiaRem,ItemRem,CdProducto,DescripProd
,CdForma,N.NumCheque AS NroCheque,CdBanco,BN.Banco AS NomBanco,MulPlazos,N.IdPlazo AS CdPlazo,Plazo,NVmto,DiasPago,PrestCaja,Modalidad,OrigenAdd
,N.TipCom AS CodTipoComp,TipoCom,N.Comprobante AS NumComprob,IdCiaCom,N.Observacion AS Observ,N.IdEstado AS CdEstado,Estado,N.TimeSys AS Fec_Add,N.FecUpdate AS Fec_Update
,N.IdCiaCrea AS CodCiaCrea,N.IdUsuario AS IdUsuari,Usuario,Leyenda
--detalle del comprobante
,Item,D.IdCuenta AS CdCuenta,NomCuenta,Detalle,VrDebito,VrCredito,D.IdTercero AS NitTercero,DT.RazonSocial AS NomTercero,IdVehiculo,D.IdCCosto AS DetCodCentCost,DC.CCosto AS DetCentCost
,D.IdSubCos AS DetCodSubCentro,DS.SubCosto  AS DetSubCentro,VrBase,TarifaBase,D.TipDoc AS DetTipoDoc,D.Documento AS DetNumDoc,IdCiaDoc,TipFac,Factura,IdCiaFac,ItemFac
,FecVence,D.NumCheque AS DetNumCheque,TipoAplica,NitOtros,Referncia
--Información del tercero
,T.TipoId AS TercTipo,T.Dv AS TercDv,T.Codigo AS TercCodigo,T.NomCial AS TercNomCial,T.SiglaRaz AS TercSigal,T.Direccion AS TercDireccion
,T.IdLocal AS TercCdCiudad,L.Localidad AS NomCiudad,L.IdDep AS CdDep,DP.Departamento AS TercDpto,T.Telefono AS TercTelefono,T.Fax AS TercFax,T.e_mail AS TercEmail
,T.SitioWeb AS TercSitioWeb,T.IdSector AS CdSector,SectorEco,T.IdRegimen AS CdRegimen,Regimen,T.TipEnte AS TercTipEnte
--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 CliCodBanco,BC.Banco AS CliBanco,ExcIva,LiqFletes,CLI.FactSold AS FactSoldicom,Autoret,VrCupo,VrSaldo,Contrato,CLI.NContrato AS CliNumContrato
,DirAgncia,TelAgncia,FaxAgncia,A.IdLocal AS AgeIdCiudad,LA.Localidad AS AgeCiudad,LA.IdDep AS AgeCodDep,DA.Departamento AS AgeDpto,A.Referencia AS AgeReferencia
 FROM Trn_Notas AS N INNER JOIN Companias AS CN ON N.IdCia=CN.IdCia  INNER JOIN Sys_TiposDoc AS TD ON N.TipDoc=TD.IdDoc
 INNER JOIN Terceros AS T ON N.IdCliente=T.IdTercero INNER JOIN  Conceptos AS C ON N.IdConcepto=C.IdConcepto
 INNER JOIN EstadoDoc AS ED ON N.IdEstado=ED.IdEstado   INNER JOIN adm_Usuarios AS U ON N.IdUsuario=U.IdUsuario
 INNER JOIN  Plazos AS PZ ON N.IdPlazo=PZ.IdPlazo INNER JOIN  Terceros AS VN ON N.IdVend=VN.IdTercero
 INNER JOIN Agencias  AS A ON N.IdAgencia=A.IdAgencia  INNER JOIN Localidades  AS LN ON N.IdLocal=LN.IdLocal
 INNER JOIN CentroCosto AS CC ON N.IdCCosto=CC.IdCCosto 
 INNER JOIN TercCliente AS CLI ON N.IdCliente=CLI.IdClie INNER JOIN Subzonas AS SZ ON CLI.IdSzona=SZ.IdSzona 
 INNER JOIN  Zonas AS Z ON SZ.IdZona=Z.IdZona  INNER JOIN GruposCli AS GC ON CLI.IdGrupo=GC.IdGrupo
 INNER JOIN Bancos AS BC ON CLI.IdBanco=BC.IdBanco
 INNER JOIN Localidades AS LA ON A.IdLocal=LA.IdLocal  INNER JOIN Departamentos AS DA ON LA.IdDep=DA.IdDep 
 INNER JOIN Localidades AS L ON T.IdLocal=L.IdLocal  INNER JOIN Departamentos AS DP ON L.IdDep=DP.IdDep 
 INNER JOIN  RegimenDian AS RG ON T.IdRegimen=RG.IdRegimen INNER JOIN SectoresEco AS SE ON T.IdSector=SE.IdSector
 LEFT JOIN ProdMcias  AS PM ON N.CdProducto=PM.IdProducto  LEFT JOIN Bancos AS BN ON N.CdBanco=BN.IdBanco
 LEFT JOIN TiposCom AS TCM ON N.TipCom=TCM.IdCom  LEFT JOIN SubCentros AS SC ON N.IdSubCos=SC.IdSubCos
 LEFT JOIN Terceros AS TC ON N.CdConductor=TC.IdTercero
 LEFT JOIN  Trn_ComDetalle AS D ON N.TipCom=D.TipCom AND N.Comprobante=D.Comprobante AND N.IdCiaCom=D.IdCia
 LEFT JOIN Puc AS P ON D.IdCuenta=P.IdCuenta LEFT JOIN Terceros AS DT ON D.IdTercero=DT.IdTercero
 LEFT JOIN CentroCosto AS DC ON D.IdCCosto=DC.IdCCosto  LEFT JOIN SubCentros AS DS ON D.IdSubCos=DS.IdSubCos
 WHERE N.TipDoc=@pmTipDoc AND NumNota BETWEEN @pmNumNotaIni AND @pmNumNotaFin AND N.IdCia=@pmIdCia
 ORDER BY  NumNota
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE paQryNotasLta @pmTipDoc VARCHAR(3),@pmFechaIni SMALLDATETIME, @pmFechaFin SMALLDATETIME,@pmNotaIni INT=Null,@pmNotaFin INT=Null
,@pmIdCia CHAR(2)=Null,@pmIdCliente VARCHAR(16)=Null,@pmIdConcepto VARCHAR(4)=Null,@pmIdVend VARCHAR(16)=Null,@pmModalidad VARCHAR(10)=Null
 AS 
 SELECT NumNota,IdCia,Fecha,N.IdConcepto AS CdConcepto,Concepto,IdCliente,T.RazonSocial AS NomCliente,IdAgencia,FechaVence,VrSubTotal,VrImpuesto,VrRetencion,VrReteICA,VrReteIVA,VrCosto,VrSancion,VrOtros,VrNeto
,VrAplicado,VrAnticipo,Cantidad,BaseImp,BaseRet,BaseIca,BaseRiv,TarifaIva,TarifaRet,TarifaIca,TarifaRiv,CodTarIva,CodTarRet,CodTarIca,CodTarRiv,N.IdCCosto AS CodCcosto,CCosto,IdSubCos,IdVend,V.RazonSocial AS Vendedor
,TarifaCom,CodTarCom,CdCajero,N.IdLocal AS CodCiudad,Localidad,Referencia,pVehiculo,CdConductor,TipDcm,Documento,IdCiaDcm,FecDcm,nRemesa,CdCiaRem,ItemRem,CdProducto,Modalidad,PrestCaja
,CdForma,NumCheque,CdBanco,MulPlazos,IdPlazo,OrigenAdd,TipCom,Comprobante,IdCiaCom,N.Observacion AS Observ,N.IdEstado AS CdEstado,Estado,TimeSys,FecUpdate,IdCiaCrea,N.IdUsuario AS IdUsuari,Usuario,TipDoc
 FROM Trn_Notas AS N  INNER JOIN Terceros AS T ON N.IdCliente=T.IdTercero
 INNER JOIN Terceros AS V ON N.IdVend=V.IdTercero
 INNER JOIN  Conceptos AS CN ON N.IdConcepto=CN.IdConcepto
 INNER JOIN EstadoDoc AS ED ON N.IdEstado=ED.IdEstado
 INNER JOIN adm_Usuarios AS U ON N.IdUsuario=U.IdUsuario 
 INNER JOIN   CentroCosto AS CC ON N.IdCCosto=CC.IdCCosto
 INNER JOIN Localidades AS L ON N.IdLocal=L.IdLocal
 WHERE TipDoc=@pmTipDoc   AND Fecha BETWEEN @pmFechaIni AND @pmFechaFin AND NumNota BETWEEN ISNULL(@pmNotaIni,0) AND ISNULL(@pmNotaFin,2147483647)
 AND IdCia LIKE ISNULL(@pmIdCia,'%%') AND IdCliente LIKE ISNULL(@pmIdCliente,'%') AND N.IdConcepto LIKE ISNULL(@pmIdConcepto,'%')
 AND IdVend LIKE ISNULL(@pmIdVend,'%') AND Modalidad LIKE ISNULL(@pmModalidad,'%')
 ORDER BY IdCia,NumNota
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE paQryNotasRel @pmTipDoc VARCHAR(3),@pmFechaIni SMALLDATETIME,@pmFechaFin SMALLDATETIME,@pmIdCia CHAR(2)=Null
,@pmIdConcepto VARCHAR(4)=Null,@pmIdCliente VARCHAR(16)=Null,@pmIdAgencia VARCHAR(16)=Null,@pmIdCCosto  VARCHAR(16)=Null
,@pmIdVend VARCHAR(16)=Null,@pmIdLocal VARCHAR(8)=Null,@pmModalidad VARCHAR(10)=Null
 AS 
 SELECT TipDoc,NumNota,N.IdCia AS CdCia,Compania,Fecha,N.IdConcepto AS CdConcepto,Concepto,IdCliente,T.RazonSocial AS NomCliente
,N.IdAgencia AS IdAgenc,A.Agencia AS NomAgencia,CodAgencia,FechaVence,VrSubTotal,VrImpuesto,VrRetencion,VrReteICA,VrReteIVA,N.VrCosto AS ValCosto,VrSancion,VrOtros,VrNeto
,VrAplicado,VrAnticipo,Cantidad,BaseImp,BaseRet,BaseIca,BaseRiv,TarifaIva,TarifaRet,TarifaIca,TarifaRiv
,N.IdCCosto AS CdCentCosto,CCosto,N.IdSubCos AS CdSubCentro,SubCosto,N.IdVend AS NitVend,VN.RazonSocial AS Vendedor,TarifaCom
,N.IdLocal AS NotCdCiudad,LN.Localidad AS NotCiudad,N.Referencia AS NotReferencia,pVehiculo,CdConductor,TC.RazonSocial AS Conductor,TipDcm
,Documento,IdCiaDcm,FecDcm,nRemesa,CdCiaRem,ItemRem,CdProducto,DescripProd,CdForma,NumCheque,CdBanco,BN.Banco AS NomBanco,MulPlazos,N.IdPlazo AS CdPlazo,Plazo,NVmto,DiasPago,PrestCaja,Modalidad,OrigenAdd
,TipCom,Comprobante,IdCiaCom,N.Observacion AS Observ,N.IdEstado AS CdEstado,Estado,TimeSys,N.FecUpdate AS Fec_Update,IdCiaCrea,N.IdUsuario AS IdUsuari,Usuario
--Información del tercero
,T.TipoId AS TercTipo,T.Dv AS TercDv,T.Codigo AS TercCodigo,T.NomCial AS TercNomCial,T.SiglaRaz AS TercSigal,T.Direccion AS TercDireccion
,T.IdLocal AS TercCdCiudad,L.Localidad AS NomCiudad,L.IdDep AS CdDep,DP.Departamento AS TercDpto,T.Telefono AS TercTelefono,T.Fax AS TercFax,T.e_mail AS TercEmail
,T.SitioWeb AS TercSitioWeb,T.IdSector AS CdSector,SectorEco,T.IdRegimen AS CdRegimen,Regimen,T.TipEnte AS TercTipEnte
--información del cliente
,NitRepLeg,NomRepLeg,CLI.NitContac AS ClieNitContac,CLI.NomContac AS ClieNomContacto,CLI.TelContac AS ClieTelContac,CLI.emlContac AS ClieEmailContac,CargContac,DiasEntga,CLI.IdSzona AS CdSubzona,Subzona,SZ.IdZona AS CdZona,Zona
,CLI.IdGrupo AS CdGrupoCli,GrupoClie,CLI.IdClase AS CdClaseCta,NumCuenta,CLI.IdBanco AS CliCodBanco,BC.Banco AS CliBanco,ExcIva,LiqFletes,CLI.FactSold AS FactSoldicom,Autoret,VrCupo,VrSaldo,Contrato,CLI.NContrato AS CliNumContrato
,DirAgncia,TelAgncia,FaxAgncia,A.IdLocal AS AgeIdCiudad,LA.Localidad AS AgeCiudad,LA.IdDep AS AgeCodDep,DA.Departamento AS AgeDpto,A.Referencia AS AgeReferencia,CdFntePago,FuentePago
 FROM Trn_Notas AS N INNER JOIN Companias AS CN ON N.IdCia=CN.IdCia
 INNER JOIN Terceros AS T ON N.IdCliente=T.IdTercero INNER JOIN  Conceptos AS C ON N.IdConcepto=C.IdConcepto
 INNER JOIN EstadoDoc AS ED ON N.IdEstado=ED.IdEstado   INNER JOIN adm_Usuarios AS U ON N.IdUsuario=U.IdUsuario
 INNER JOIN  Plazos AS PZ ON N.IdPlazo=PZ.IdPlazo INNER JOIN  Terceros AS VN ON N.IdVend=VN.IdTercero
 INNER JOIN Agencias  AS A ON N.IdAgencia=A.IdAgencia  INNER JOIN Localidades  AS LN ON N.IdLocal=LN.IdLocal
 INNER JOIN CentroCosto AS CC ON N.IdCCosto=CC.IdCCosto 
 INNER JOIN TercCliente AS CLI ON N.IdCliente=CLI.IdClie INNER JOIN Subzonas AS SZ ON CLI.IdSzona=SZ.IdSzona 
 INNER JOIN  Zonas AS Z ON SZ.IdZona=Z.IdZona  INNER JOIN GruposCli AS GC ON CLI.IdGrupo=GC.IdGrupo
 INNER JOIN Bancos AS BC ON CLI.IdBanco=BC.IdBanco
 INNER JOIN Localidades AS LA ON A.IdLocal=LA.IdLocal  INNER JOIN Departamentos AS DA ON LA.IdDep=DA.IdDep 
 INNER JOIN Localidades AS L ON T.IdLocal=L.IdLocal  INNER JOIN Departamentos AS DP ON L.IdDep=DP.IdDep 
 INNER JOIN  RegimenDian AS RG ON T.IdRegimen=RG.IdRegimen INNER JOIN SectoresEco AS SE ON T.IdSector=SE.IdSector
 LEFT JOIN ProdMcias  AS PM ON N.CdProducto=PM.IdProducto  LEFT JOIN Bancos AS BN ON N.CdBanco=BN.IdBanco
 LEFT JOIN SubCentros AS SC ON N.IdSubCos=SC.IdSubCos LEFT JOIN Terceros AS TC ON N.CdConductor=TC.IdTercero
 LEFT JOIN FuentesPago AS FPG ON A.CdFntePago=FPG.IdFuente
 WHERE TipDoc=@pmTipDoc  AND Fecha BETWEEN @pmFechaIni AND @pmFechaFin
 AND N.IdCia LIKE ISNULL(@pmIdCia,'%%') AND N.IdConcepto LIKE ISNULL(@pmIdConcepto,'%')
 AND IdCliente LIKE ISNULL(@pmIdCliente,'%')  AND N.IdAgencia LIKE ISNULL(@pmIdAgencia ,'%')
 AND N.IdCCosto LIKE ISNULL(@pmIdCCosto,'%') AND N.IdVend LIKE ISNULL(@pmIdVend,'%') 
 AND N.IdLocal LIKE ISNULL(@pmIdLocal,'%')  AND Modalidad LIKE ISNULL(@pmModalidad,'%')
 ORDER BY N.IdCia,NumNota
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE paQryTiposDocFor
--tipos de documento de formas de pago
 AS 
 SELECT  IdDoc,TipoDoc,IdDoc+'  '+ TipoDoc AS DsTip
 FROM  Sys_TiposDoc 
 WHERE IdDoc IN ('REC','FCO','FO1','FO2','FO3','FO4','FO5','PLA','RCO' ,'RC1','RC2','ODP')
 ORDER BY IdDoc
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE paQryTiposDocPag
 AS 
 SELECT  IdDoc,TipoDoc,IdDoc+'  '+ TipoDoc AS DsTip
 FROM  Sys_TiposDoc 
 WHERE IdDoc IN ('REC','FCO','FO1','FO2','FO3','FO4','FO5','PLA','RCO'
,'PAS','EGO','CNS','DNP','DVP','DVC','ODB','DEC','DRC','DFO','DO1','DO2','DO3','DO4','DO5','OD1','RC1','RC2','OD2','ODP','DOP')
 ORDER BY IdDoc
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE paUpNomVac @pmIdEmpleado VARCHAR(16),@pmNumero INT,@pmFecha SMALLDATETIME,@pmNContrato INT,@pmFecInicial SMALLDATETIME,@pmFecFinal SMALLDATETIME,@pmCantidad DECIMAL(10,4),@pmVrLiquida MONEY,@pmVrBase MONEY,@pmFecIniPmd SMALLDATETIME,@pmFecFinPmd SMALLDATETIME,@pmDiasBase INT,@pmDiasNoRem INT
,@pmDiasVac INT,@pmNNovedad INT,@pmNPeriodos INT,@pmCantRem INT,@pmVrRemndo MONEY,@pmVrTotDed MONEY,@pmVrSalario MONEY,@pmpVehiculo VARCHAR(10),@pmTipCom VARCHAR(3),@pmComprobante INT,@pmIdCiaCom CHAR(2),@pmObservacion VARCHAR(250),@pmLogPmdio VARCHAR(600),@pmIdEstado VARCHAR(4),@pmAnulado BIT,@pmDiasAjuste INT,@pmFecUpdate SMALLDATETIME
 AS 
 UPDATE Trn_NomVac SET Fecha=@pmFecha,NContrato=@pmNContrato,FecInicial=@pmFecInicial,FecFinal=@pmFecFinal,Cantidad=@pmCantidad,VrLiquida=@pmVrLiquida,VrBase=@pmVrBase,FecIniPmd=@pmFecIniPmd,FecFinPmd=@pmFecFinPmd,DiasBase=@pmDiasBase,DiasNoRem=@pmDiasNoRem,DiasVac=@pmDiasVac,NNovedad=@pmNNovedad,NPeriodos=@pmNPeriodos
,CantRem=@pmCantRem,VrRemndo=@pmVrRemndo,VrTotDed=@pmVrTotDed,VrSalario=@pmVrSalario,pVehiculo=@pmpVehiculo,TipCom=@pmTipCom,Comprobante=@pmComprobante,IdCiaCom=@pmIdCiaCom,Observacion=@pmObservacion,LogPmdio=@pmLogPmdio,IdEstado=@pmIdEstado,Anulado=@pmAnulado,DiasAjuste=@pmDiasAjuste,FecUpdate=@pmFecUpdate
 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 paUpNotas @pmTipDoc VARCHAR(3),@pmNumNota INT,@pmIdCia CHAR(2),@pmFecha SMALLDATETIME,@pmIdConcepto VARCHAR(4),@pmIdCliente VARCHAR(16),@pmIdAgencia VARCHAR(16),@pmFechaVence SMALLDATETIME,@pmVrSubTotal MONEY,@pmVrImpuesto MONEY,@pmVrRetencion MONEY,@pmVrReteICA MONEY,@pmVrReteIVA MONEY,@pmVrCosto MONEY,@pmVrSancion MONEY,@pmVrOtros MONEY,@pmVrNeto MONEY,@pmVrAplicado MONEY
,@pmVrAnticipo MONEY,@pmCantidad DECIMAL(14,4),@pmBaseImp MONEY,@pmBaseRet MONEY,@pmBaseIca MONEY,@pmBaseRiv MONEY,@pmTarifaIva DECIMAL(14,4),@pmTarifaRet DECIMAL(14,4),@pmTarifaIca DECIMAL(14,4),@pmTarifaRiv DECIMAL(14,4),@pmCodTarIva VARCHAR(4),@pmCodTarRet VARCHAR(4),@pmCodTarIca VARCHAR(4),@pmCodTarRiv VARCHAR(4),@pmIdCCosto VARCHAR(16),@pmIdSubCos VARCHAR(16),@pmIdVend VARCHAR(16),@pmTarifaCom DECIMAL(14,4)
,@pmCodTarCom VARCHAR(4),@pmCdCajero VARCHAR(11),@pmIdLocal VARCHAR(8),@pmReferencia VARCHAR(50),@pmpVehiculo VARCHAR(10),@pmCdConductor VARCHAR(16),@pmTipDcm VARCHAR(3),@pmDocumento INT,@pmIdCiaDcm CHAR(2),@pmFecDcm SMALLDATETIME,@pmCdProducto VARCHAR(16),@pmCdForma VARCHAR(4),@pmNumCheque VARCHAR(20),@pmCdBanco VARCHAR(4),@pmMulPlazos BIT,@pmIdPlazo VARCHAR(4),@pmPrestCaja BIT
,@pmModalidad VARCHAR(10),@pmTipCom VARCHAR(3),@pmComprobante INT,@pmIdCiaCom CHAR(2),@pmObservacion VARCHAR(250),@pmIdEstado VARCHAR(4),@pmnRemesa INT,@pmCdCiaRem CHAR(2),@pmItemRem INT,@pmFecUpdate SMALLDATETIME
 AS 
 UPDATE Trn_Notas SET Fecha=@pmFecha,IdConcepto=@pmIdConcepto,IdCliente=@pmIdCliente,IdAgencia=@pmIdAgencia,FechaVence=@pmFechaVence,VrSubTotal=@pmVrSubTotal,VrImpuesto=@pmVrImpuesto,VrRetencion=@pmVrRetencion,VrReteICA=@pmVrReteICA,VrReteIVA=@pmVrReteIVA,VrCosto=@pmVrCosto,VrSancion=@pmVrSancion,VrOtros=@pmVrOtros,VrNeto=@pmVrNeto,VrAplicado=@pmVrAplicado,VrAnticipo=@pmVrAnticipo,Cantidad=@pmCantidad,BaseImp=@pmBaseImp
,BaseRet=@pmBaseRet,BaseIca=@pmBaseIca,BaseRiv=@pmBaseRiv,TarifaIva=@pmTarifaIva,TarifaRet=@pmTarifaRet,TarifaIca=@pmTarifaIca,TarifaRiv=@pmTarifaRiv,CodTarIva=@pmCodTarIva,CodTarRet=@pmCodTarRet,CodTarIca=@pmCodTarIca,CodTarRiv=@pmCodTarRiv,IdCCosto=@pmIdCCosto,IdSubCos=@pmIdSubCos,IdVend=@pmIdVend,TarifaCom=@pmTarifaCom,CodTarCom=@pmCodTarCom,CdCajero=@pmCdCajero,IdLocal=@pmIdLocal,Referencia=@pmReferencia
,pVehiculo=@pmpVehiculo,CdConductor=@pmCdConductor,TipDcm=@pmTipDcm,Documento=@pmDocumento,IdCiaDcm=@pmIdCiaDcm,FecDcm=@pmFecDcm,CdProducto=@pmCdProducto,CdForma=@pmCdForma,NumCheque=@pmNumCheque,CdBanco=@pmCdBanco,MulPlazos=@pmMulPlazos,IdPlazo=@pmIdPlazo,PrestCaja=@pmPrestCaja,Modalidad=@pmModalidad,TipCom=@pmTipCom,Comprobante=@pmComprobante,IdCiaCom=@pmIdCiaCom,Observacion=@pmObservacion
,IdEstado=@pmIdEstado,nRemesa=@pmnRemesa,CdCiaRem=@pmCdCiaRem,ItemRem=@pmItemRem,FecUpdate=@pmFecUpdate
 WHERE TipDoc=@pmTipDoc AND NumNota=@pmNumNota AND IdCia=@pmIdCia
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO