if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInsDocSoporte]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paInsDocSoporte]
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_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].[paInsTraFijos]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paInsTraFijos]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryDocSoporte]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryDocSoporte]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryDocSoporteFmt]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryDocSoporteFmt]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryDocSoporteRel]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryDocSoporteRel]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryNomDetalleRes]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryNomDetalleRes]
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].[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].[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].[paQryTraFijos]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryTraFijos]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryTraFijosLta]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryTraFijosLta]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paUpDocSoporte]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paUpDocSoporte]
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].[paUpTraFijos]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paUpTraFijos]
GO


SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[paQryNomDetalleRes] @pmFecIni SMALLDATETIME,@pmFecFin SMALLDATETIME,@pmIdPeriodo VARCHAR(8)=Null
,@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,@pmIdEstado VARCHAR(4)=Null,@pmIdTipCon VARCHAR(4)=Null,@pmEsRetiro BIT=Null,@pmNLiqRet INT=Null
,@pmInactivo BIT=Null,@pmEmp_Inactivo BIT=Null,@pmIdConcepto VARCHAR(4)=Null,@pmClaseLiq VARCHAR(10)=Null,@pmClaseCon VARCHAR(10)=Null,@pmNPrestamo INT=Null
 AS 
 SELECT N.IdEmpleado AS IdEmpldo,Apellidos,Nombres,N.NContrato AS NumCntrato,N.KeyRegistro AS KeyReg,N.IdPeriodo AS NumPeriodo,FecInicial,FecFinal,Cantidad,CantNoRem,VrDevengado,VrDeducido
,N.IdNom AS CodNmna,TipoNomina,N.IdDep AS CodDep,Dependencia,N.IdCCosto AS CodCcto,CCosto,N.IdSubCos AS CodSubcos,SubCosto,N.IdInstala AS CodIstala,Instlacion
,N.VrSalario AS SalBasico,N.pVehiculo AS PlacaVeh,TipCom,TipoCom,Comprobante,IdCiaCom,N.Observacion AS Observ,N.IdEstado AS IdEstdo,ED.Estado AS Nom_Estado,EsRetiro,NLiqRet,TipoLiq,TimeSys
,N.FecUpdate AS Fec_Update,N.IdUsuario AS IdUsuari,Usuario
--datos del detalle
,Item,D.IdConcepto AS CodConcept,Concepto,Detalle,CantDevg,CantDed,VrUnitario,VrTotDevg,VrTotDed,ClaseCon,D.Unidad AS Undad,ClaseLiq,VrOrigen,D.Tarifa AS Tarfa,DiasCalc,DiasNov,CodFondo,FCO.Fondo AS NomFondo
,NPrestamo,NCuota,OrigCargue,VrBaseLiq,VrBasExceso,D.HoraInicial,D.HoraFinal,D.TipoIncLab
--Datos 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,FC.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.IdClase AS CodClaRie,ClaseRiesgo,CR.Tarifa AS TarifaRie,C.VrSalario AS VlrSalario,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,TipoTrabj,C.IdGrupo AS CodGrup
 FROM Trn_Nomina AS N INNER JOIN Empleados AS E ON N.IdEmpleado=E.IdEmpleado
 INNER JOIN TiposNom AS TN ON N.IdNom=TN.IdNom INNER JOIN Dependencias AS DP ON N.IdDep=DP.IdDep
 INNER JOIN CentroCosto AS CC ON N.IdCCosto=CC.IdCCosto INNER JOIN SubCentros AS SC ON N.IdSubCos=SC.IdSubCos
 INNER JOIN EstadoDoc AS ED ON N.IdEstado=ED.IdEstado INNER JOIN adm_Usuarios AS U ON N.IdUsuario=U.IdUsuario
 INNER JOIN Instalaciones AS I ON N.IdInstala=I.IdInstala  INNER JOIN  Emp_Contrato AS C ON N.NContrato=C.NContrato
 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 FC ON E.IdCajaCom=FC.IdFondo 
 INNER JOIN ExpLicencias AS EL ON E.IdLugar=EL.IdLugar  INNER JOIN TiposCon AS TC ON C.IdTipCon=TC.IdTipCon
 INNER JOIN Cargos AS CG ON C.IdCargo=CG.IdCargo  INNER JOIN Areas AS AR ON C.IdArea=AR.IdArea
 INNER JOIN ClaseRie AS CR ON C.IdClase=CR.IdClase INNER JOIN Jornadas AS J ON C.IdJornada=J.IdJornada
 INNER JOIN EstadoDoc AS ECO ON C.IdEstado=ECO.IdEstado 
 INNER JOIN  Trn_NomDetalle AS D ON N.IdPeriodo=D.IdPeriodo AND  N.KeyRegistro=D.KeyRegistro
 INNER JOIN NomConceptos AS NC ON D.IdConcepto=NC.IdConcepto
 LEFT JOIN TiposCom AS TCM ON N.TipCom=TCM.IdCom  LEFT JOIN Fondos AS FCO ON D.CodFondo=FCO.IdFondo
 WHERE FecInicial>=@pmFecIni  AND FecFinal<=@pmFecFin AND N.IdEmpleado LIKE ISNULL(@pmIdEmpleado,'%')  AND  N.IdPeriodo LIKE ISNULL(@pmIdPeriodo,'%')
 AND N.IdNom LIKE ISNULL(@pmIdNom,'%') AND N.IdDep LIKE ISNULL(@pmIdDep,'%') AND N.IdInstala LIKE ISNULL(@pmIdInstala,'%')
 AND N.IdCCosto LIKE ISNULL(@pmIdCCosto,'%') AND N.IdEstado LIKE ISNULL(@pmIdEstado,'%') AND C.IdArea LIKE ISNULL(@pmIdArea,'%')
 AND C.IdTipCon LIKE ISNULL(@pmIdTipCon,'%') AND D.IdConcepto LIKE ISNULL(@pmIdConcepto,'%') AND ClaseLiq LIKE ISNULL(@pmClaseLiq,'%')
 AND ClaseCon LIKE ISNULL(@pmClaseCon,'%')
 AND (N.NContrato>=ISNULL(@pmNContrato,0)  AND N.NContrato<=ISNULL(@pmNContrato,2147483647))
 AND (NLiqRet>=ISNULL(@pmNLiqRet,-2)  AND NLiqRet<=ISNULL(@pmNLiqRet,2147483647))
 AND (EsRetiro=ISNULL(@pmEsRetiro,0) or EsRetiro=ISNULL(@pmEsRetiro,1))
 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))
 AND (NPrestamo>=ISNULL(@pmNPrestamo,0)  AND NPrestamo<=ISNULL(@pmNPrestamo,2147483647))
--Descuentos en vacaciones
UNION ALL
 SELECT V.IdEmpleado,Apellidos,Nombres,V.NContrato,V.IdEmpleado,D.IdPeriodo,V.FecInicial,V.FecFinal,V.Cantidad,0,V.VrLiquida,V.VrTotDed
,C.IdNom,TipoNomina,C.IdDep,Dependencia,C.IdCCosto,CCosto,C.IdSubCos,SubCosto,C.IdInstala,Instlacion,V.VrSalario,V.pVehiculo,V.TipCom,TipoCom,V.Comprobante,V.IdCiaCom
,V.Observacion,V.IdEstado,ED.Estado,0,V.Numero,C.TipoLiquida,V.TimeSys,V.FecUpdate,V.IdUsuario,Usuario
,D.Item,D.IdConcepto,Concepto,D.Detalle,0,CASE D.IdConcepto WHEN 'DCP' THEN 0 WHEN 'DCS' THEN 0 WHEN 'FSP' THEN 0 WHEN 'FSU' THEN 0 ELSE D.Cantidad END AS CantDed,0,D.VrDevgado,D.VrDeducido,ClaseCon,'%','NOMINA',D.VrBaseLiq,D.Tarifa
,CASE D.IdConcepto WHEN 'DCP' THEN 0 WHEN 'DCS' THEN 0 WHEN 'FSP' THEN 0 WHEN 'FSU' THEN 0 ELSE D.Cantidad END AS DiasCalc,0,D.CodFondo,FD.Fondo,D.NPrestamo,D.NCuota,'VAC',D.VrBaseLiq,0,Null,Null,''
,E.Codigo,E.IdLugarCed,Localidad,E.Direccion,E.Telefono,E.TelMovil,E.e_mail,E.IdProf,Profesion,E.IdClase,ClaseCuenta,NumCuenta,E.IdBanco,Banco,IdFonPen,FP.Fondo
,IdFonEps,FS.Fondo,IdFonArp,FR.Fondo,IdCajaCom,FC.Fondo,EsCondtor,FecNacmto,Licencia,CatLicencia,VigLicencia,E.IdLugar,LugarLic,TallaCam,TallaPan,TallaZap,E.Inactivo
,C.IdTipCon,TipoContrato,FecIngreso,FecRetiro,Indefinido,C.IdCargo,Cargo,C.IdArea,Area,C.IdClase,ClaseRiesgo,CR.Tarifa,C.VrSalario,VrAuxTrans,SalMinimo,SalIntegral,FPnomina
,ATFinMes,SegFinMes,DchDotacion,DchCafeteria,C.IdEstado,ECO.Estado,C.Inactivo,C.IdJornada,Jornada,TipoTrabj,C.IdGrupo
 FROM Trn_NomVac AS V INNER JOIN Trn_NomVacDet AS D ON V.IdEmpleado=D.IdEmpleado AND V.Numero=D.Numero
 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 NomConceptos AS NC ON D.IdConcepto=NC.IdConcepto INNER JOIN TiposNom AS TN ON C.IdNom=TN.IdNom INNER JOIN Dependencias AS DPD ON C.IdDep=DPD.IdDep
 INNER JOIN CentroCosto AS CC ON C.IdCCosto=CC.IdCCosto INNER JOIN Instalaciones AS I ON C.IdInstala=I.IdInstala
 INNER JOIN TiposCon AS TC ON C.IdTipCon=TC.IdTipCon
 INNER JOIN Areas AS AR ON C.IdArea=AR.IdArea INNER JOIN Cargos AS CG ON C.IdCargo=CG.IdCargo
 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 FC ON E.IdCajaCom=FC.IdFondo 
 INNER JOIN ExpLicencias AS EL ON E.IdLugar=EL.IdLugar
 INNER JOIN ClaseRie AS CR ON C.IdClase=CR.IdClase INNER JOIN Jornadas AS J ON C.IdJornada=J.IdJornada
 INNER JOIN EstadoDoc AS ECO ON C.IdEstado=ECO.IdEstado INNER JOIN EstadoDoc AS ED ON V.IdEstado=ED.IdEstado
 LEFT JOIN SubCentros AS SC ON C.IdSubCos=SC.IdSubCos LEFT JOIN TiposCom AS TCM ON V.TipCom=TCM.IdCom
 LEFT JOIN Fondos AS FD ON D.CodFondo=FD.IdFondo
 WHERE V.Anulado=0 
 AND (SUBSTRING(D.IdPeriodo,1,6)>=SUBSTRING(CONVERT(VARCHAR(30),@pmFecIni,112),1,6) AND SUBSTRING(D.IdPeriodo,1,6)<=SUBSTRING(CONVERT(VARCHAR(30),@pmFecFin,112),1,6))
 AND D.IdPeriodo LIKE ISNULL(@pmIdPeriodo,'%') AND V.IdEmpleado LIKE ISNULL(@pmIdEmpleado,'%')
 AND C.IdNom LIKE ISNULL(@pmIdNom,'%') AND C.IdDep LIKE ISNULL(@pmIdDep,'%') AND C.IdInstala LIKE ISNULL(@pmIdInstala,'%')
 AND C.IdCCosto LIKE ISNULL(@pmIdCCosto,'%') AND V.IdEstado LIKE ISNULL(@pmIdEstado,'%') AND C.IdArea LIKE ISNULL(@pmIdArea,'%')
 AND C.IdTipCon LIKE ISNULL(@pmIdTipCon,'%') AND D.IdConcepto LIKE ISNULL(@pmIdConcepto,'%')
 AND ClaseCon LIKE ISNULL(@pmClaseCon,'%')
 AND (V.NContrato>=ISNULL(@pmNContrato,0)  AND V.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))
 AND (D.NPrestamo>=ISNULL(@pmNPrestamo,0)  AND D.NPrestamo<=ISNULL(@pmNPrestamo,2147483647))
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryDocSoporte] @pmTipDoc VARCHAR(3),@pmNumDoc INT,@pmIdCia CHAR(2)
 AS 
 SELECT TipDoc,NumDoc,IdCia,Fecha,IdTercero,IdAdquiriente,IdConcepto,IdMedPago,MetPago,Modalidad,NumFactura,FechaFac,FechaVence,SubTotal,Descuento,Retencion,ReteIca,Bomberil
,ImpAviTab,OtrosCargos,OtrosDctos,ValorTotal,BaseRet,BaseBom,TarifaRet,TarifaIca,TarifaBom,TarifaAvta,CodTarRet,CodTarIca,CodTarifBom,CodTarifAvta,DescServicio,CodRes,CdCCosto,CdSubCos,TipCom,Comprobante,IdCiaCom
,TipDocRef,NumDocRef,IdCiaRef,Anulado,TipDocDev,NumDev,CdCiaDev,FecDev,Observacion,IdEstado,CodCueProv,OrigenAdd,TimeSys,FecUpdate,IdCiaCrea,IdUsuario
 FROM Trn_DocSoporte
 WHERE TipDoc=@pmTipDoc AND NumDoc=@pmNumDoc AND IdCia=@pmIdCia
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paInsDocSoporte] @pmTipDoc VARCHAR(3),@pmNumDoc INT,@pmIdCia CHAR(2),@pmFecha SMALLDATETIME,@pmIdTercero VARCHAR(16),@pmIdAdquiriente VARCHAR(16),@pmIdConcepto VARCHAR(4),@pmIdMedPago VARCHAR(4),@pmMetPago INT,@pmModalidad VARCHAR(20),@pmNumFactura VARCHAR(15),@pmFechaFac SMALLDATETIME,@pmFechaVence SMALLDATETIME,@pmSubTotal DECIMAL(16,6),@pmDescuento DECIMAL(16,6),@pmRetencion DECIMAL(16,6),@pmReteIca DECIMAL(16,6),@pmBomberil DECIMAL(16,6),@pmImpAviTab DECIMAL(16,6),@pmOtrosCargos DECIMAL(16,6),@pmOtrosDctos DECIMAL(16,6),@pmValorTotal DECIMAL(16,6)
,@pmBaseRet DECIMAL(16,6),@pmBaseBom DECIMAL(16,6),@pmTarifaRet DECIMAL(14,4),@pmTarifaIca DECIMAL(14,4),@pmTarifaBom DECIMAL(14,4),@pmTarifaAvta DECIMAL(14,4),@pmCodTarRet VARCHAR(4),@pmCodTarIca VARCHAR(4),@pmCodTarifBom VARCHAR(4),@pmCodTarifAvta VARCHAR(4),@pmDescServicio VARCHAR(500),@pmCodRes VARCHAR(4),@pmCdCCosto VARCHAR(16),@pmCdSubCos VARCHAR(16),@pmTipCom VARCHAR(3),@pmComprobante INT,@pmIdCiaCom CHAR(2),@pmTipDocRef VARCHAR(3),@pmNumDocRef INT,@pmIdCiaRef CHAR(2),@pmAnulado BIT,@pmTipDocDev VARCHAR(3),@pmNumDev INT,@pmCdCiaDev CHAR(2),@pmFecDev SMALLDATETIME
,@pmObservacion VARCHAR(500),@pmIdEstado VARCHAR(4),@pmCodCueProv VARCHAR(16),@pmOrigenAdd VARCHAR(10),@pmTimeSys SMALLDATETIME,@pmIdCiaCrea CHAR(2),@pmIdUsuario VARCHAR(11)
 AS 
 INSERT INTO Trn_DocSoporte (TipDoc,NumDoc,IdCia,Fecha,IdTercero,IdAdquiriente,IdConcepto,IdMedPago,MetPago,Modalidad,NumFactura,FechaFac,FechaVence,SubTotal,Descuento,Retencion,ReteIca,Bomberil,ImpAviTab,OtrosCargos,OtrosDctos,ValorTotal,BaseRet,BaseBom,TarifaRet,TarifaIca,TarifaBom,TarifaAvta,CodTarRet,CodTarIca,CodTarifBom,CodTarifAvta,DescServicio,CodRes,CdCCosto,CdSubCos,TipCom,Comprobante,IdCiaCom,TipDocRef,NumDocRef,IdCiaRef,Anulado,TipDocDev,NumDev,CdCiaDev,FecDev,Observacion,IdEstado,OrigenAdd,TimeSys,IdCiaCrea,IdUsuario,CodCueProv)
 VALUES (@pmTipDoc,@pmNumDoc,@pmIdCia,@pmFecha,@pmIdTercero,@pmIdAdquiriente,@pmIdConcepto,@pmIdMedPago,@pmMetPago,@pmModalidad,@pmNumFactura,@pmFechaFac,@pmFechaVence,@pmSubTotal,@pmDescuento,@pmRetencion,@pmReteIca,@pmBomberil,@pmImpAviTab,@pmOtrosCargos,@pmOtrosDctos,@pmValorTotal,@pmBaseRet,@pmBaseBom,@pmTarifaRet,@pmTarifaIca,@pmTarifaBom,@pmTarifaAvta,@pmCodTarRet,@pmCodTarIca,@pmCodTarifBom,@pmCodTarifAvta,@pmDescServicio,@pmCodRes,@pmCdCCosto,@pmCdSubCos,@pmTipCom,@pmComprobante,@pmIdCiaCom,@pmTipDocRef,@pmNumDocRef,@pmIdCiaRef,@pmAnulado,@pmTipDocDev,@pmNumDev,@pmCdCiaDev,@pmFecDev,@pmObservacion,@pmIdEstado,@pmOrigenAdd,@pmTimeSys,@pmIdCiaCrea,@pmIdUsuario,@pmCodCueProv)
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paUpDocSoporte] @pmTipDoc VARCHAR(3),@pmNumDoc INT,@pmIdCia CHAR(2),@pmFecha SMALLDATETIME,@pmIdTercero VARCHAR(16),@pmIdAdquiriente VARCHAR(16),@pmIdConcepto VARCHAR(4),@pmIdMedPago VARCHAR(4),@pmMetPago INT,@pmModalidad VARCHAR(20),@pmNumFactura VARCHAR(15),@pmFechaFac SMALLDATETIME,@pmFechaVence SMALLDATETIME,@pmSubTotal DECIMAL(16,6),@pmDescuento DECIMAL(16,6),@pmRetencion DECIMAL(16,6),@pmReteIca DECIMAL(16,6),@pmBomberil DECIMAL(16,6),@pmImpAviTab DECIMAL(16,6),@pmOtrosCargos DECIMAL(16,6),@pmOtrosDctos DECIMAL(16,6),@pmValorTotal DECIMAL(16,6),@pmBaseRet DECIMAL(16,6),@pmBaseBom DECIMAL(16,6),@pmTarifaRet DECIMAL(14,4),@pmTarifaIca DECIMAL(14,4),@pmTarifaBom DECIMAL(14,4),@pmTarifaAvta DECIMAL(14,4),@pmCodTarRet VARCHAR(4),@pmCodTarIca VARCHAR(4),@pmCodTarifBom VARCHAR(4),@pmCodTarifAvta VARCHAR(4),@pmDescServicio VARCHAR(500),@pmCodRes VARCHAR(4),@pmCdCCosto VARCHAR(16),@pmCdSubCos VARCHAR(16),@pmTipCom VARCHAR(3),@pmComprobante INT,@pmIdCiaCom CHAR(2)
,@pmTipDocRef VARCHAR(3),@pmNumDocRef INT,@pmIdCiaRef CHAR(2),@pmAnulado BIT,@pmTipDocDev VARCHAR(3),@pmNumDev INT,@pmCdCiaDev CHAR(2),@pmFecDev SMALLDATETIME,@pmObservacion VARCHAR(500),@pmIdEstado VARCHAR(4),@pmCodCueProv VARCHAR(16),@pmFecUpdate SMALLDATETIME
 AS 
 UPDATE Trn_DocSoporte SET Fecha=@pmFecha,IdTercero=@pmIdTercero,IdAdquiriente=@pmIdAdquiriente,IdConcepto=@pmIdConcepto,IdMedPago=@pmIdMedPago,MetPago=@pmMetPago,Modalidad=@pmModalidad,NumFactura=@pmNumFactura,FechaFac=@pmFechaFac,FechaVence=@pmFechaVence,SubTotal=@pmSubTotal,Descuento=@pmDescuento,Retencion=@pmRetencion,ReteIca=@pmReteIca,Bomberil=@pmBomberil,ImpAviTab=@pmImpAviTab,OtrosCargos=@pmOtrosCargos,OtrosDctos=@pmOtrosDctos,ValorTotal=@pmValorTotal,BaseRet=@pmBaseRet,BaseBom=@pmBaseBom,TarifaRet=@pmTarifaRet,TarifaIca=@pmTarifaIca,TarifaBom=@pmTarifaBom,TarifaAvta=@pmTarifaAvta,CodTarRet=@pmCodTarRet,CodTarIca=@pmCodTarIca,CodTarifBom=@pmCodTarifBom,CodTarifAvta=@pmCodTarifAvta,DescServicio=@pmDescServicio,CodRes=@pmCodRes,CdCCosto=@pmCdCCosto,CdSubCos=@pmCdSubCos,TipCom=@pmTipCom,Comprobante=@pmComprobante,IdCiaCom=@pmIdCiaCom
,TipDocRef=@pmTipDocRef,NumDocRef=@pmNumDocRef,IdCiaRef=@pmIdCiaRef,Anulado=@pmAnulado,TipDocDev=@pmTipDocDev,NumDev=@pmNumDev,CdCiaDev=@pmCdCiaDev
,FecDev=@pmFecDev,Observacion=@pmObservacion,IdEstado=@pmIdEstado,CodCueProv=@pmCodCueProv,FecUpdate=@pmFecUpdate
 WHERE TipDoc=@pmTipDoc AND NumDoc=@pmNumDoc AND IdCia=@pmIdCia
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[paQryDocSoporteRel] @pmTipDoc VARCHAR(3),@pmFechaIni SMALLDATETIME,@pmFechaFin SMALLDATETIME,@pmIdCia CHAR(2)=Null,@pmnIdTercero VARCHAR(16)=Null
AS 
 SELECT D.TipDoc,TipoDoc,D.NumDoc,D.IdCia AS CdCia,CI.Compania,D.Fecha,D.IdTercero AS NitTercero,T.RazonSocial,D.IdAdquiriente,N.RazonSocial AS Adquiriente
 ,D.Modalidad,D.IdConcepto,Concepto,D.IdMedPago,MedioPago,D.MetPago,D.NumFactura,D.FechaFac,D.FechaVence,D.SubTotal,D.Descuento,D.Retencion,D.ReteIca,D.Bomberil,D.ImpAviTab,D.OtrosCargos,D.OtrosDctos,D.ValorTotal
 ,D.BaseRet,D.BaseBom,D.TarifaRet,D.TarifaIca,D.TarifaBom,D.TarifaAvta,D.CodTarRet,D.CodTarIca,D.CodTarifBom,D.CodTarifAvta,D.DescServicio
 ,D.CodRes,R.Resolucion,R.Prefijo,R.NumInicial,R.NumFinal,R.FechaExpRes,R.FechaVigencia,R.Establecimiento,D.CdCCosto,CCosto,D.CdSubCos,SubCosto,D.TipCom,TipoCom,D.Comprobante,D.IdCiaCom,D.TipDocRef,D.NumDocRef,D.IdCiaRef
,D.CodCueProv,D.Anulado,D.TipDocDev,D.NumDev,D.CdCiaDev,D.FecDev,D.Observacion,D.IdEstado AS CdEstado,ED.Estado,D.TimeSys AS FechaCrea,D.OrigenAdd,D.FecUpdate,D.IdCiaCrea,D.IdUsuario,Usuario
,T.TipoId AS TercTipo,T.Dv AS TercDv,T.Codigo AS TercCodigo,T.Direccion AS TercDireccion,T.IdLocal AS TercCdCiudad,L.Localidad AS TercCiudad
,L.IdDep AS CdDep,DP.Departamento AS TercDpto,T.Telefono AS TercTelefono,T.e_mail AS TercEmail,T.SitioWeb AS TercSitioWeb,T.IdSector AS CdSector,SectorEco
,T.IdRegimen AS CdRegimen,Regimen,T.TipEnte AS TercTipEnte
FROM Trn_DocSoporte AS D INNER JOIN Sys_TiposDoc AS TD ON D.TipDoc=TD.IdDoc
INNER JOIN Companias AS CI ON D.IdCia=CI.IdCia INNER JOIN Terceros AS T ON D.IdTercero=T.IdTercero
INNER JOIN adm_Usuarios AS U ON D.IdUsuario=U.IdUsuario INNER JOIN EstadoDoc AS ED ON D.IdEstado=ED.IdEstado 
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 TiposComRes AS R ON D.CodRes=R.IdRes LEFT JOIN Terceros AS N ON D.IdAdquiriente=N.IdTercero
LEFT JOIN Conceptos AS CN ON D.IdConcepto=CN.IdConcepto LEFT JOIN NomMediosPago AS MP ON D.IdMedPago=MP.IdMedPago
LEFT JOIN TiposCom AS TC ON D.TipCom=TC.IdCom LEFT JOIN CentroCosto AS CC ON D.CdCCosto=CC.IdCCosto
LEFT JOIN SubCentros AS SC ON D.CdSubCos=SC.IdSubCos
WHERE D.TipDoc=@pmTipDoc AND D.Fecha BETWEEN @pmFechaIni AND @pmFechaFin
AND (D.IdCia=@pmIdCia OR @pmIdCia IS NULL)
AND D.IdTercero LIKE ISNULL(@pmnIdTercero,'%')
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[paQryDocSoporteFmt] @pmTipDoc VARCHAR(3),@pmNumDocIni INT,@pmNumDocFin INT,@pmIdCia CHAR(2)
AS 
 SELECT D.TipDoc,TipoDoc,D.NumDoc,D.IdCia AS CdCia,CI.Compania,D.Fecha,D.IdTercero AS NitTercero,T.RazonSocial,D.IdAdquiriente,N.RazonSocial AS Adquiriente
 ,D.Modalidad,D.IdConcepto,Concepto,D.IdMedPago,MedioPago,D.MetPago,D.NumFactura,D.FechaFac,D.FechaVence,D.SubTotal,D.Descuento,D.Retencion,D.ReteIca,D.Bomberil,D.ImpAviTab,D.OtrosCargos,D.OtrosDctos,D.ValorTotal
 ,D.BaseRet,D.BaseBom,D.TarifaRet,D.TarifaIca,D.TarifaBom,D.TarifaAvta,D.CodTarRet,D.CodTarIca,D.CodTarifBom,D.CodTarifAvta,D.DescServicio
,D.CdCCosto,CCosto,D.CdSubCos,SubCosto,D.TipCom,TipoCom,D.Comprobante,D.IdCiaCom,D.TipDocRef,D.NumDocRef,D.IdCiaRef,D.CodCueProv,D.Anulado,D.TipDocDev,D.NumDev,D.CdCiaDev,D.FecDev
,D.Observacion,D.IdEstado AS CdEstado,ED.Estado,D.TimeSys AS FechaCrea,D.OrigenAdd,D.FecUpdate,D.IdCiaCrea,D.IdUsuario,Usuario
--detalles
,DT.Item,DT.IdProducto,P.DescripProd,DT.Descripcion,DT.Cantidad,DT.VrUnitario,DT.ValorOper,DT.VrDcto,DT.VrRetencion,DT.VrRetIca,DT.VrBomberil,DT.VrAviTab,DT.TarifaDct AS PorcDcto
,DT.TarifaRet AS PorcRet,DT.TarifaIca AS PorcICA,DT.TarifaBom AS PorcBom,DT.TarifaAvta AS PorcAvta,DT.CodTarDct AS DetCodDcto,DT.CodTarRet AS DetCodRet
,DT.CodTarIca AS DetCodIca,DT.CodTarifBom AS DetCodBom,DT.CodTarifAvta AS DetCodAvta,DT.ItemCom,DT.CdCuenta,PC.NomCuenta,DT.CdCueCre
--Datos resolución
,D.CodRes,R.Resolucion,R.Prefijo,R.NumInicial,R.NumFinal,R.FechaExpRes,R.FechaVigencia,R.Establecimiento
,T.TipoId AS TercTipo,T.Dv AS TercDv,T.Codigo AS TercCodigo,T.Direccion AS TercDireccion,T.IdLocal AS TercCdCiudad,L.Localidad AS TercCiudad
,L.IdDep AS CdDep,DP.Departamento AS TercDpto,T.Telefono AS TercTelefono,T.e_mail AS TercEmail,T.SitioWeb AS TercSitioWeb,T.IdSector AS CdSector,SectorEco
,T.IdRegimen AS CdRegimen,Regimen,T.TipEnte AS TercTipEnte
FROM Trn_DocSoporte AS D INNER JOIN Sys_TiposDoc AS TD ON D.TipDoc=TD.IdDoc
INNER JOIN Companias AS CI ON D.IdCia=CI.IdCia INNER JOIN Terceros AS T ON D.IdTercero=T.IdTercero
INNER JOIN adm_Usuarios AS U ON D.IdUsuario=U.IdUsuario INNER JOIN EstadoDoc AS ED ON D.IdEstado=ED.IdEstado 
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
INNER JOIN Trn_DocSoporteDet AS DT ON D.TipDoc=DT.TipDoc AND D.NumDoc=DT.NumDoc AND D.IdCia=DT.IdCia
LEFT JOIN ProdMcias AS P ON DT.IdProducto=P.IdProducto
LEFT JOIN Conceptos AS CN ON D.IdConcepto=CN.IdConcepto LEFT JOIN NomMediosPago AS MP ON D.IdMedPago=MP.IdMedPago
LEFT JOIN TiposComRes AS R ON D.CodRes=R.IdRes LEFT JOIN Terceros AS N ON D.IdAdquiriente=N.IdTercero
LEFT JOIN TiposCom AS TC ON D.TipCom=TC.IdCom LEFT JOIN CentroCosto AS CC ON D.CdCCosto=CC.IdCCosto
LEFT JOIN SubCentros AS SC ON D.CdSubCos=SC.IdSubCos LEFT JOIN Puc AS PC ON DT.CdCuenta=PC.IdCuenta
WHERE D.TipDoc=@pmTipDoc AND D.NumDoc BETWEEN @pmNumDocIni AND @pmNumDocFin AND D.IdCia=@pmIdCia
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[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,LiqParcial,DiasDom,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,D.CodFondo,FD.Fondo AS DetFondo
--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 LEFT JOIN Fondos AS FD ON D.CodFondo=FD.IdFondo
 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 ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[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,LiqParcial,DiasDom,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
,D.CodFondo,FD.Fondo AS DetFondo
--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
 LEFT JOIN Fondos AS FD ON D.CodFondo=FD.IdFondo
 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 ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[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,tmCodFondo
 FROM tm_Nomina
 WHERE tmNumero=@pmtmNumero  AND tmClaseLiq IN ('NOMINA','NOVEDAD')
 AND tmIdEmpleado like ISNULL(@pmtmIdEmpleado,'%')
ORDER BY tmIdEmpleado,tmItem
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[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,tmBasExceso,tmNitTercero,tmHoraInicial,tmHoraFinal,tmTipoInc)
 SELECT @pmtmNumero,Item,IdEmpleado,D.IdConcepto,ClaseCon,Detalle,0,Cantidad,0,VrDevgado,VrDeducido,Unidad,'NOMINA',VrBaseLiq,D.Tarifa,0,0,D.CodFondo,NPrestamo,NCuota,'VAC',VrCertfcado
,0,getdate(),getdate(),IdPeriodo,VrPension,VrSalud,0,'',Null,Null,''
 FROM Trn_NomVacDet  AS D INNER JOIN NomConceptos AS C ON D.IdConcepto=C.IdConcepto
 WHERE IdEmpleado=@pmIdEmpleado AND Numero=@pmNumero
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[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,@pmCodFondo VARCHAR(8)
 AS 
 INSERT INTO Trn_NomVacDet (IdEmpleado,Numero,Item,IdConcepto,Detalle,Cantidad,VrDevgado,VrDeducido,Tarifa,VrBaseLiq,VrCertfcado,VrPension,NPrestamo,NCuota,IdPeriodo,VrSalud,CodFondo)
 VALUES (@pmIdEmpleado,@pmNumero,@pmItem,@pmIdConcepto,@pmDetalle,@pmCantidad,@pmVrDevgado,@pmVrDeducido,@pmTarifa,@pmVrBaseLiq,@pmVrCertfcado,@pmVrPension,@pmNPrestamo,@pmNCuota,@pmIdPeriodo,@pmVrSalud,@pmCodFondo)
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[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,@pmCodFondo VARCHAR(8)
 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,CodFondo=@pmCodFondo
 WHERE IdEmpleado=@pmIdEmpleado AND Numero=@pmNumero AND Item=@pmItem
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[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,CodFondo
 FROM Trn_NomVacDet
 WHERE IdEmpleado=@pmIdEmpleado AND Numero=@pmNumero
 AND (Item>=ISNULL(@pmItem,0)  AND Item<=ISNULL(@pmItem,2147483647))
 ORDER BY Item
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[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,CodFondo)
 SELECT @pmNewCedula,Numero,Item,IdConcepto,Detalle,Cantidad,VrDevgado,VrDeducido,Tarifa,VrBaseLiq,VrCertfcado,VrPension,NPrestamo,NCuota,IdPeriodo,VrSalud,CodFondo
 FROM  Trn_NomVacDet 
 WHERE IdEmpleado=@pmIdEmpleado
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryTraFijosLta] @pmTipoConc VARCHAR(10)=Null
 AS 
 SELECT Numero,IdConcepto,DescConc,TipoConc,Tarifa,TipoTarif,TipoAfilVeh,CdTipoVeh,TipoVehiculo,TipoTerc
,CdCiuOrigen,LO.Localidad AS CiuOrigen,LO.IdDep AS CdDepOri,DPO.Departamento AS DptoOrigen
,CdCiuDestino,LD.Localidad AS CiuDestino,LD.IdDep AS CdDepDest,DPD.Departamento AS DptoDestino
,pVehiculo,CedConductor,CDT.RazonSocial AS NomConductor,CdRuta,R.Ruta,NitCliente,T.RazonSocial AS NomCliente,CdMercancia,M.DescripMcia
,TipoVigencia,FechaInicial,FechaFinal,F.IdentProp,NP.RazonSocial AS NomPropietario,F.Anulado
 FROM TraFijos AS F LEFT JOIN TiposVeh AS TV ON F.CdTipoVeh=TV.IdTipoVeh
 LEFT JOIN Localidades AS LO ON F.CdCiuOrigen=LO.IdLocal LEFT JOIN Departamentos AS DPO ON LO.IdDep=DPO.IdDep
 LEFT JOIN Localidades AS LD ON F.CdCiuDestino=LD.IdLocal LEFT JOIN Departamentos AS DPD ON LD.IdDep=DPD.IdDep
 LEFT JOIN Rutas AS R ON F.CdRuta=R.IdRuta LEFT JOIN Terceros AS CDT ON F.CedConductor=CDT.IdTercero
 LEFT JOIN Terceros AS T ON F.NitCliente=T.IdTercero LEFT JOIN Mercancias AS M ON F.CdMercancia=M.IdMercancia
 LEFT JOIN Terceros AS NP ON F.IdentProp=NP.IdTercero
 WHERE TipoConc LIKE ISNULL(@pmTipoConc,'%')
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paUpTraFijos] @pmNumero INT,@pmIdConcepto VARCHAR(4),@pmDescConc VARCHAR(250),@pmTipoConc VARCHAR(10),@pmTarifa DECIMAL(14,4),@pmTipoTarif CHAR(1),@pmTipoAfilVeh VARCHAR(10)
,@pmCdTipoVeh VARCHAR(4),@pmAnulado BIT,@pmTipoTerc CHAR(1),@pmCdCiuOrigen VARCHAR(8),@pmCdCiuDestino VARCHAR(8),@pmpVehiculo VARCHAR(10),@pmCedConductor VARCHAR(16),@pmCdRuta VARCHAR(4)
,@pmTipoVigencia CHAR(1),@pmFechaInicial SMALLDATETIME,@pmFechaFinal SMALLDATETIME,@pmNitCliente VARCHAR(16),@pmCdMercancia VARCHAR(16),@pmIdentProp VARCHAR(16)
 AS 
 UPDATE TraFijos SET IdConcepto=@pmIdConcepto,DescConc=@pmDescConc,TipoConc=@pmTipoConc,Tarifa=@pmTarifa,TipoTarif=@pmTipoTarif,TipoAfilVeh=@pmTipoAfilVeh,CdTipoVeh=@pmCdTipoVeh
,Anulado=@pmAnulado,TipoTerc=@pmTipoTerc,CdCiuOrigen=@pmCdCiuOrigen,CdCiuDestino=@pmCdCiuDestino,pVehiculo=@pmpVehiculo,CedConductor=@pmCedConductor,CdRuta=@pmCdRuta
,TipoVigencia=@pmTipoVigencia,FechaInicial=@pmFechaInicial,FechaFinal=@pmFechaFinal,NitCliente=@pmNitCliente,CdMercancia=@pmCdMercancia,IdentProp=@pmIdentProp
 WHERE Numero=@pmNumero
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paInsTraFijos] @pmNumero INT,@pmIdConcepto VARCHAR(4),@pmDescConc VARCHAR(250),@pmTipoConc VARCHAR(10)
,@pmTarifa DECIMAL(14,4),@pmTipoTarif CHAR(1),@pmTipoAfilVeh VARCHAR(10),@pmCdTipoVeh VARCHAR(4),@pmAnulado BIT,@pmTipoTerc CHAR(1)
,@pmCdCiuOrigen VARCHAR(8),@pmCdCiuDestino VARCHAR(8),@pmpVehiculo VARCHAR(10),@pmCedConductor VARCHAR(16),@pmCdRuta VARCHAR(4)
,@pmTipoVigencia CHAR(1),@pmFechaInicial SMALLDATETIME,@pmFechaFinal SMALLDATETIME,@pmNitCliente VARCHAR(16),@pmCdMercancia VARCHAR(16),@pmIdentProp VARCHAR(16)
 AS 
 INSERT INTO TraFijos (Numero,IdConcepto,DescConc,TipoConc,Tarifa,TipoTarif,TipoAfilVeh,CdTipoVeh,TipoTerc,Anulado,CdCiuOrigen,CdCiuDestino,pVehiculo,CedConductor,CdRuta,TipoVigencia,FechaInicial,FechaFinal,NitCliente,CdMercancia,IdentProp)
 VALUES (@pmNumero,@pmIdConcepto,@pmDescConc,@pmTipoConc,@pmTarifa,@pmTipoTarif,@pmTipoAfilVeh,@pmCdTipoVeh,@pmTipoTerc
,@pmAnulado,@pmCdCiuOrigen,@pmCdCiuDestino,@pmpVehiculo,@pmCedConductor,@pmCdRuta,@pmTipoVigencia,@pmFechaInicial,@pmFechaFinal,@pmNitCliente,@pmCdMercancia,@pmIdentProp)
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryTraFijos] @pmNumero INT
 AS 
 SELECT Numero,IdConcepto,DescConc,TipoConc,Tarifa,TipoTarif,TipoAfilVeh,CdTipoVeh,TipoTerc,Anulado
,CdCiuOrigen,CdCiuDestino,pVehiculo,CedConductor,CdRuta,TipoVigencia,FechaInicial,FechaFinal,NitCliente,CdMercancia,IdentProp
 FROM TraFijos
 WHERE Numero=@pmNumero
GO