if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInsNomAjustes]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paInsNomAjustes]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInsNomAjustesDet]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paInsNomAjustesDet]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryNomAjustes]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryNomAjustes]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryNomAjustesLta]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryNomAjustesLta]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQrytm_KdexCom]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQrytm_KdexCom]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paUpNomAjustes]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paUpNomAjustes]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryNomAjustesLta] @pmTipNota VARCHAR(3),@pmFechaIni SMALLDATETIME,@pmFechaFin SMALLDATETIME
 AS 
 SELECT A.TipNota,TD.TipoDoc,A.NumNota,A.IdCia,Compania,A.IdPeriodo,A.FechaInicial,A.FechaFinal,A.IdEmpleado,E.Apellidos,E.Nombres,A.NContrato
,D.Item,D.IdConcepto,C.Concepto,C.ClaseCon,D.Detalle,D.CantDevg,D.CantDed,D.VrUnitario,D.VrTotDevg,D.VrTotDed,D.VrBaseLiq,D.VrBasExceso,D.Unidad,D.ClaseLiq,D.VrOrigen,D.Tarifa
,D.DiasCalc,D.DiasNov,D.FecInicial AS DetFecFinal,D.FecFinal AS DetFecInicial,D.CodFondo,FN.Fondo,D.NPrestamo,D.NCuota,D.OrigCargue,D.NitTercero,T.RazonSocial AS NomTercero,D.TipoIncLab
,A.IdRegNom,A.NumLiquida,A.NumSemLiq,A.IdNom,TipoNomina,A.TipoLiq,A.Observacion,A.NA_Estado,A.NA_Prefijo,A.NA_Numero,A.NA_FecEnvio,A.NA_CUNE,A.NA_QR
,A.NE_Prefijo,A.NE_Numero,A.NE_CUNE,A.NE_QR,A.FechaCrea,A.IdCiaCrea,A.IdUsuario,Usuario
--contrato/empleado
,CN.IdArea,Area,CN.IdDep AS CodDep,DP.Dependencia,CN.IdCargo,Cargo,CN.IdTipCon,TipoContrato,CN.FecIngreso,CN.FecVigencia,CN.FecRetiro,CN.VrSalario,CN.VrAuxTrans,CN.Inactivo
,E.Codigo,E.IdLugarCed,LC.Localidad,E.Direccion,E.Telefono,E.TelMovil,E.e_mail,E.IdProf AS CodProf,Profesion
FROM Trn_NomAjustes AS A INNER JOIN Trn_NomAjustesDet AS D ON A.TipNota=D.TipNota AND A.NumNota=D.NumNota AND A.IdCia=D.IdCia
INNER JOIN Empleados AS E ON A.IdEmpleado=E.IdEmpleado INNER JOIN adm_Usuarios AS U ON A.IdUsuario=U.IdUsuario
INNER JOIN TiposNom AS TN ON A.IdNom=TN.IdNom INNER JOIN NomConceptos AS C ON D.IdConcepto=C.IdConcepto
INNER JOIN Emp_Contrato AS CN ON A.NContrato=CN.NContrato INNER JOIN Dependencias AS DP ON CN.IdDep=DP.IdDep
INNER JOIN Areas AS AR ON CN.IdArea=AR.IdArea INNER JOIN Cargos AS CG ON CN.IdCargo=CG.IdCargo 
INNER JOIN Profesiones AS P ON E.IdProf=P.IdProf INNER JOIN Companias AS CI ON A.IdCia=CI.IdCia
INNER JOIN TiposCon AS TC ON CN.IdTipCon=TC.IdTipCon
LEFT JOIN Sys_TiposDoc AS TD ON A.TipNota=TD.IdDoc LEFT JOIN Fondos AS FN ON D.CodFondo=FN.IdFondo
LEFT JOIN Terceros AS T ON D.NitTercero=T.IdTercero LEFT JOIN Localidades AS LC ON E.IdLugarCed=LC.IdLocal
WHERE A.FechaInicial BETWEEN @pmFechaIni AND @pmFechaFin AND (A.TipNota=@pmTipNota OR @pmTipNota IS NULL)
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paInsNomAjustes] @pmTipNota VARCHAR(3),@pmNumNota INT,@pmIdCia CHAR(2),@pmIdEmpleado VARCHAR(16),@pmNContrato INT,@pmIdPeriodo VARCHAR(8),@pmFechaInicial SMALLDATETIME,@pmFechaFinal SMALLDATETIME,@pmIdRegNom VARCHAR(30),@pmNumLiquida INT,@pmNumSemLiq INT,@pmIdNom VARCHAR(4),@pmTipoLiq CHAR(1),@pmObservacion VARCHAR(2000),@pmNA_Estado INT
,@pmNA_Prefijo VARCHAR(20),@pmNA_Numero INT,@pmNA_FecEnvio SMALLDATETIME,@pmNA_CUNE VARCHAR(500),@pmNE_Prefijo VARCHAR(20),@pmNE_Numero INT,@pmNE_CUNE VARCHAR(500),@pmFechaCrea SMALLDATETIME,@pmIdCiaCrea CHAR(2),@pmIdUsuario VARCHAR(11)
 AS 
 INSERT INTO Trn_NomAjustes (TipNota,NumNota,IdCia,IdEmpleado,NContrato,IdPeriodo,FechaInicial,FechaFinal,IdRegNom,NumLiquida,NumSemLiq,IdNom,TipoLiq,Observacion,NA_Estado,NA_Prefijo,NA_Numero,NA_FecEnvio,NA_CUNE,NE_Prefijo,NE_Numero,NE_CUNE,FechaCrea,IdCiaCrea,IdUsuario)
 VALUES (@pmTipNota,@pmNumNota,@pmIdCia,@pmIdEmpleado,@pmNContrato,@pmIdPeriodo,@pmFechaInicial,@pmFechaFinal,@pmIdRegNom,@pmNumLiquida,@pmNumSemLiq,@pmIdNom,@pmTipoLiq,@pmObservacion,@pmNA_Estado,@pmNA_Prefijo,@pmNA_Numero,@pmNA_FecEnvio,@pmNA_CUNE,@pmNE_Prefijo,@pmNE_Numero,@pmNE_CUNE,@pmFechaCrea,@pmIdCiaCrea,@pmIdUsuario)
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paUpNomAjustes] @pmTipNota VARCHAR(3),@pmNumNota INT,@pmIdCia CHAR(2),@pmIdEmpleado VARCHAR(16),@pmNContrato INT,@pmIdPeriodo VARCHAR(8),@pmFechaInicial SMALLDATETIME,@pmFechaFinal SMALLDATETIME,@pmIdRegNom VARCHAR(30),@pmNumLiquida INT,@pmNumSemLiq INT,@pmIdNom VARCHAR(4),@pmTipoLiq CHAR(1),@pmObservacion VARCHAR(2000),@pmNA_Estado INT
,@pmNA_Prefijo VARCHAR(20),@pmNA_Numero INT,@pmNA_FecEnvio SMALLDATETIME,@pmNA_CUNE VARCHAR(500),@pmNE_Prefijo VARCHAR(20),@pmNE_Numero INT,@pmNE_CUNE VARCHAR(500)
 AS 
 UPDATE Trn_NomAjustes SET IdEmpleado=@pmIdEmpleado,NContrato=@pmNContrato,IdPeriodo=@pmIdPeriodo,FechaInicial=@pmFechaInicial,FechaFinal=@pmFechaFinal,IdRegNom=@pmIdRegNom,NumLiquida=@pmNumLiquida,NumSemLiq=@pmNumSemLiq,IdNom=@pmIdNom,TipoLiq=@pmTipoLiq
,Observacion=@pmObservacion,NA_Estado=@pmNA_Estado,NA_Prefijo=@pmNA_Prefijo,NA_Numero=@pmNA_Numero,NA_FecEnvio=@pmNA_FecEnvio,NA_CUNE=@pmNA_CUNE,NE_Prefijo=@pmNE_Prefijo,NE_Numero=@pmNE_Numero,NE_CUNE=@pmNE_CUNE
 WHERE TipNota=@pmTipNota AND NumNota=@pmNumNota AND IdCia=@pmIdCia
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryNomAjustes] @pmTipNota VARCHAR(3),@pmNumNota INT,@pmIdCia CHAR(2)
 AS 
 SELECT TipNota,NumNota,IdCia,IdEmpleado,NContrato,IdPeriodo,FechaInicial,FechaFinal,IdRegNom,NumLiquida,NumSemLiq,IdNom,TipoLiq,Observacion,NA_Estado,NA_Prefijo,NA_Numero,NA_FecEnvio,NA_CUNE,NA_QR,NE_Prefijo,NE_Numero,NE_CUNE,NE_QR,FechaCrea,IdCiaCrea,IdUsuario
 FROM Trn_NomAjustes
 WHERE TipNota=@pmTipNota AND NumNota=@pmNumNota AND IdCia=@pmIdCia
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paInsNomAjustesDet] @pmTipNota VARCHAR(3),@pmNumNota INT,@pmIdCia CHAR(2),@pmItem INT,@pmIdConcepto VARCHAR(4),@pmDetalle VARCHAR(250),@pmCantDevg DECIMAL(14,4),@pmCantDed DECIMAL(14,4),@pmVrUnitario MONEY,@pmVrTotDevg MONEY,@pmVrTotDed MONEY,@pmUnidad VARCHAR(10),@pmClaseLiq VARCHAR(10),@pmVrOrigen MONEY,@pmTarifa DECIMAL(14,4),@pmDiasCalc DECIMAL(14,4),@pmDiasNov DECIMAL(14,4)
,@pmFecInicial SMALLDATETIME,@pmFecFinal SMALLDATETIME,@pmCodFondo VARCHAR(8),@pmNPrestamo INT,@pmNCuota INT,@pmOrigCargue VARCHAR(10),@pmVrBaseLiq MONEY,@pmVrBasExceso MONEY,@pmNitTercero VARCHAR(16),@pmTipoIncLab VARCHAR(20)
 AS 
 INSERT INTO Trn_NomAjustesDet (TipNota,NumNota,IdCia,Item,IdConcepto,Detalle,CantDevg,CantDed,VrUnitario,VrTotDevg,VrTotDed,Unidad,ClaseLiq,VrOrigen,Tarifa,DiasCalc,DiasNov,FecInicial,FecFinal,CodFondo,NPrestamo,NCuota,OrigCargue,VrBaseLiq,VrBasExceso,NitTercero,TipoIncLab)
 VALUES (@pmTipNota,@pmNumNota,@pmIdCia,@pmItem,@pmIdConcepto,@pmDetalle,@pmCantDevg,@pmCantDed,@pmVrUnitario,@pmVrTotDevg,@pmVrTotDed,@pmUnidad,@pmClaseLiq,@pmVrOrigen,@pmTarifa,@pmDiasCalc,@pmDiasNov,@pmFecInicial,@pmFecFinal,@pmCodFondo,@pmNPrestamo,@pmNCuota,@pmOrigCargue,@pmVrBaseLiq,@pmVrBasExceso,@pmNitTercero,@pmTipoIncLab)
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[paQrytm_KdexCom] @pmtmNumero VARCHAR(5)
 AS 
 SELECT tmNumero,tmItem,tmIdProducto,tmIdBodega,tmCdTanque,tmEntradas,tmSalidas,tmIdUnd,tmVrUnitario,tmVrPrecio,tmVrCostProm
,tmVrBruto,tmVrDcto,tmReferencia,tmCdProdEquiv,tmUnidades,tmServcios,tmEsCombo,tmEsProdBase,tmItemCbo,tmCdSubgrupo
,tmListaPrec,tmVrBase,tmCdMoneda,tmVrTasaCamb,tmIdTercero,tmCdAgencia,tmCdCCosto,tmCdSubCos,tmCdLocal,tmCdSzona,tmpVehiculo,tmIdVend
,tmComision,tmCdOperario,tmComisnOper,tmCodTarCom,tmCodTarCmc,tmCantObseq
--variables de ProMcias
,DescripProd,TipoRef,IdSubgrupo,IdBodega,IdUbic,VrCostAnt,VrCosto,VrCostPmd,ExtciaMin,ExtciaMax,ExtciaAct
,Seriales,Lotes,Combo,Tanques,IvaDetCombo,LtaBaseIva
 FROM tm_Kdex AS K INNER JOIN ProdMcias AS P ON K.tmIdProducto=P.IdProducto
 WHERE tmNumero=@pmtmNumero AND Combo<>0 
 ORDER BY tmItem
GO