if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paDelEstadoGuia]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paDelEstadoGuia]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paDelTraGuias]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paDelTraGuias]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInsEstadoGuia]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paInsEstadoGuia]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInstm_TraDetalle_Gtc]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paInstm_TraDetalle_Gtc]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInsTraGuiaMcia]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paInsTraGuiaMcia]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInsTraGuias]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paInsTraGuias]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryEstadoGuia]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryEstadoGuia]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryTraGuias]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryTraGuias]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryTraGuiasFco]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryTraGuiasFco]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryTraGuiasFmt]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryTraGuiasFmt]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paUpEstadoGuia]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paUpEstadoGuia]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paUpTraGuias]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paUpTraGuias]
GO


SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paInsTraGuiaMcia] @pmTipDoc VARCHAR(3),@pmNumGuia INT,@pmIdCia CHAR(2),@pmItem INT,@pmIdMercancia VARCHAR(16),@pmDescripcionMcia VARCHAR(500),@pmCantidad DECIMAL(14,4),@pmPesoNeto DECIMAL(14,4),@pmUndMed VARCHAR(10),@pmdmsAlto DECIMAL(14,4),@pmdmsAncho DECIMAL(14,4),@pmdmsLargo DECIMAL(14,4),@pmVolumen DECIMAL(14,4),@pmUndVol VARCHAR(10),@pmIdUnd VARCHAR(4)
,@pmIdEmp VARCHAR(4),@pmIdNat VARCHAR(4),@pmIdTmcia VARCHAR(4),@pmIdMnjo VARCHAR(4),@pmVrUnitario MONEY,@pmVrDeclarado MONEY,@pmVrSeguro MONEY,@pmTarifSeguro DECIMAL(14,4),@pmUndTarifa VARCHAR(10),@pmRemision DECIMAL(14,4),@pmDocCliente VARCHAR(30),@pmReferencia1 VARCHAR(50),@pmReferencia2 VARCHAR(50),@pmReferencia3 VARCHAR(50),@pmCantFactura DECIMAL(14,4),@pmVrFactura MONEY,@pmCdConcepto VARCHAR(4)
 AS 
 INSERT INTO Trn_TraGuiaMcia (TipDoc,NumGuia,IdCia,Item,IdMercancia,DescripcionMcia,Cantidad,PesoNeto,UndMed,dmsAlto,dmsAncho,dmsLargo,Volumen,UndVol,IdUnd,IdEmp,IdNat,IdTmcia,IdMnjo,VrUnitario,VrDeclarado,VrSeguro,TarifSeguro,UndTarifa,Remision,DocCliente,Referencia1,Referencia2,Referencia3,CantFactura,VrFactura,CdConcepto)
 VALUES (@pmTipDoc,@pmNumGuia,@pmIdCia,@pmItem,@pmIdMercancia,@pmDescripcionMcia,@pmCantidad,@pmPesoNeto,@pmUndMed,@pmdmsAlto,@pmdmsAncho,@pmdmsLargo,@pmVolumen,@pmUndVol,@pmIdUnd,@pmIdEmp,@pmIdNat,@pmIdTmcia,@pmIdMnjo,@pmVrUnitario,@pmVrDeclarado,@pmVrSeguro,@pmTarifSeguro,@pmUndTarifa,@pmRemision,@pmDocCliente,@pmReferencia1,@pmReferencia2,@pmReferencia3,@pmCantFactura,@pmVrFactura,@pmCdConcepto)
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryTraGuiasFco] @pmTipDoc VARCHAR(3),@pmNumGuiaIni INT,@pmNumGuiaFin INT,@pmIdCia CHAR(2)
 AS 
 SELECT G.TipDoc,TipoDoc,G.NumGuia,G.IdCia,Compania,G.Fecha,G.FecDespacho,G.FecVigencia,G.Modalidad,G.IdCliente,T.RazonSocial AS Nom_Cliente,G.IdAgencia,A.Agencia AS NomAgencia
,G.IdRemitente,G.NomRemitente,G.DirOrigen,G.TelRemitente,G.emlRemitente,G.IdDestinatario,G.NomDestinatario,G.DirDestino,G.TelDestinatario,G.emlDestinatario
,G.IdLocOrigen,LO.Localidad AS CiudadOrigen,DP.Departamento AS DptoOrigen,G.IdLocDestino,LD.Localidad AS CiudadDestino,DD.Departamento AS DptoDestino,G.NomContacto,G.TelContacto,G.emlContacto
,G.IdResponsable,G.NomResponsable,G.VrTotal,G.VrDeclarado AS VrTotDeclarado,G.VrSeguro AS VrTotSeguro,G.VrCargos,G.Cantidad AS Cant_Total,G.PesoTotal,G.TipRem,G.NumRemesa,G.IdCiaRem,G.EstFactura,G.TipFac,G.NumFactura,G.IdCiaFac,G.FechaFac
,G.CdConcepto,C.Concepto,G.CdForma,FormaPago,G.TipCom,TipoCom,G.Comprobante,G.IdCiaCom,G.Anulado,G.FecDev,G.Observacion,G.IdEstado,ED.Estado
,G.FechaCrea,G.FechaModif,G.IdCiaCrea,G.IdUsuario,Usuario,G.OrigenAdd
--Detalle
,D.Item,D.IdMercancia,D.DescripcionMcia,D.Cantidad,D.PesoNeto,D.UndMed,UMP.Unidad AS UmPeso,D.dmsAlto,D.dmsAncho,D.dmsLargo,D.Volumen,D.UndVol,D.IdUnd,UM.Unidad AS UndMedida,D.IdEmp,EP.Empaque
,D.VrUnitario,D.VrDeclarado,D.VrSeguro,D.TarifSeguro,D.UndTarifa,D.Remision,D.DocCliente,D.Referencia1,D.Referencia2,D.Referencia3,D.CantFactura,D.VrFactura,D.CdConcepto AS CdConcFactura,DC.Concepto AS ConcFactura
--datos del tercero
,T.TipoId AS TercTipId,T.Dv AS TercDv,T.Codigo AS TercCodigo,T.NomCial AS TercNomCial,T.Direccion AS TercDireccion,T.IdLocal AS TercCdCiudad,L.Localidad AS NomCiudad,T.Telefono AS TercTelefono,T.e_mail AS TercEmail
,A.DirAgncia AS AgeDireccion,A.IdLocal AS AgeIdCiudad,LA.Localidad AS AgeCiudad,A.TelAgncia AS AgeTelefono,A.Referencia AS AgeReferencia
--enc fact cont
,FE.EstadoFE,FE.Prefijo,FE.NumFace,FE.CUFE,FE.Resolucion,FE.RangoNum,FE.FecVigencia AS FecVigNum,FE.CUFE_QR AS QR,FE.FechaValidacion
 FROM Trn_TraGuias AS G INNER JOIN Companias AS CN ON G.IdCia=CN.IdCia INNER JOIN Sys_TiposDoc AS TD ON G.TipDoc=TD.IdDoc
 INNER JOIN EstadoGuia AS ED ON G.IdEstado=ED.IdEstado INNER JOIN adm_Usuarios AS U ON G.IdUsuario=U.IdUsuario
 INNER JOIN Trn_TraGuiaMcia AS D ON G.TipDoc=D.TipDoc AND G.NumGuia=D.NumGuia AND G.IdCia=D.IdCia
 INNER JOIN Terceros AS T ON G.IdCliente=T.IdTercero INNER JOIN Localidades AS L ON T.IdLocal=L.IdLocal
 INNER JOIN Localidades AS LO ON G.IdLocOrigen=LO.IdLocal INNER JOIN Departamentos AS DP ON LO.IdDep=DP.IdDep
 INNER JOIN Localidades AS LD ON G.IdLocDestino=LD.IdLocal INNER JOIN Departamentos AS DD ON LD.IdDep=DD.IdDep
 LEFT JOIN Conceptos AS C ON G.CdConcepto=C.IdConcepto LEFT JOIN TiposCom AS TC ON G.TipCom=TC.IdCom
 LEFT JOIN Agencias AS A ON G.IdAgencia=A.IdAgencia LEFT JOIN Localidades AS LA ON A.IdLocal=LA.IdLocal
 LEFT JOIN Formaspago AS F ON G.CdForma=F.IdForma LEFT JOIN Sys_Um AS UMP ON D.UndMed=UMP.UndMed
 LEFT JOIN UndMed AS UM ON D.IdUnd=UM.IdUnd LEFT JOIN Empaques AS EP ON D.IdEmp=EP.IdEmp
 LEFT JOIN ConcDiversos AS DC ON D.CdConcepto=DC.IdConcepto 
 LEFT JOIN Trn_Face AS FE ON G.TipFac=FE.TipDoc AND G.NumFactura=FE.Documento AND G.IdCiaFac=FE.IdCia
 WHERE G.TipDoc=@pmTipDoc AND G.NumGuia BETWEEN @pmNumGuiaIni AND @pmNumGuiaFin AND G.IdCia=@pmIdCia
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryTraGuiasFmt] @pmTipDoc VARCHAR(3),@pmNumGuiaIni INT,@pmNumGuiaFin INT,@pmIdCia CHAR(2)
 AS 
 SELECT G.TipDoc,TipoDoc,G.NumGuia,G.IdCia,Compania,G.Fecha,G.FecDespacho,G.FecVigencia,G.Modalidad,G.IdCliente,T.RazonSocial AS Nom_Cliente,G.IdAgencia,A.Agencia AS NomAgencia
,G.IdRemitente,G.NomRemitente,G.DirOrigen,G.TelRemitente,G.emlRemitente,G.IdDestinatario,G.NomDestinatario,G.DirDestino,G.TelDestinatario,G.emlDestinatario
,G.IdLocOrigen,LO.Localidad AS CiudadOrigen,DP.Departamento AS DptoOrigen,G.IdLocDestino,LD.Localidad AS CiudadDestino,DD.Departamento AS DptoDestino,G.NomContacto,G.TelContacto,G.emlContacto
,G.IdResponsable,G.NomResponsable,G.VrTotal,G.VrDeclarado AS VrTotDeclarado,G.VrSeguro AS VrTotSeguro,G.VrCargos,G.Cantidad AS Cant_Total,G.PesoTotal,G.TipRem,G.NumRemesa,G.IdCiaRem,G.EstFactura,G.TipFac,G.NumFactura,G.IdCiaFac,G.FechaFac
,G.CdConcepto,C.Concepto,G.CdForma,FormaPago,G.TipCom,TipoCom,G.Comprobante,G.IdCiaCom,G.Anulado,G.FecDev,G.Observacion,G.IdEstado,ED.Estado
,G.FechaCrea,G.FechaModif,G.IdCiaCrea,G.IdUsuario,Usuario,G.OrigenAdd
--Detalle
,D.Item,D.IdMercancia,D.DescripcionMcia,D.Cantidad,D.PesoNeto,D.UndMed,UMP.Unidad AS UmPeso,D.dmsAlto,D.dmsAncho,D.dmsLargo,D.Volumen,D.UndVol,D.IdUnd,UM.Unidad AS UndMedida,D.IdEmp,EP.Empaque--,D.IdNat,D.IdTmcia,D.IdMnjo
,D.VrUnitario,D.VrDeclarado,D.VrSeguro,D.TarifSeguro,D.UndTarifa,D.Remision,D.DocCliente,D.Referencia1,D.Referencia2,D.Referencia3,D.CantFactura,D.VrFactura,D.CdConcepto AS CdConcFactura,DC.Concepto AS ConcFactura
--datos del tercero
,T.TipoId AS TercTipId,T.Dv AS TercDv,T.Codigo AS TercCodigo,T.NomCial AS TercNomCial,T.Direccion AS TercDireccion,T.IdLocal AS TercCdCiudad,L.Localidad AS NomCiudad,T.Telefono AS TercTelefono,T.e_mail AS TercEmail
,A.DirAgncia AS AgeDireccion,A.IdLocal AS AgeIdCiudad,LA.Localidad AS AgeCiudad,A.TelAgncia AS AgeTelefono,A.Referencia AS AgeReferencia
 FROM Trn_TraGuias AS G INNER JOIN Companias AS CN ON G.IdCia=CN.IdCia INNER JOIN Sys_TiposDoc AS TD ON G.TipDoc=TD.IdDoc
 INNER JOIN EstadoGuia AS ED ON G.IdEstado=ED.IdEstado INNER JOIN adm_Usuarios AS U ON G.IdUsuario=U.IdUsuario
 INNER JOIN Trn_TraGuiaMcia AS D ON G.TipDoc=D.TipDoc AND G.NumGuia=D.NumGuia AND G.IdCia=D.IdCia
 INNER JOIN Terceros AS T ON G.IdCliente=T.IdTercero INNER JOIN Localidades AS L ON T.IdLocal=L.IdLocal
 INNER JOIN Localidades AS LO ON G.IdLocOrigen=LO.IdLocal INNER JOIN Departamentos AS DP ON LO.IdDep=DP.IdDep
 INNER JOIN Localidades AS LD ON G.IdLocDestino=LD.IdLocal INNER JOIN Departamentos AS DD ON LD.IdDep=DD.IdDep
 LEFT JOIN Conceptos AS C ON G.CdConcepto=C.IdConcepto LEFT JOIN TiposCom AS TC ON G.TipCom=TC.IdCom
 LEFT JOIN Agencias AS A ON G.IdAgencia=A.IdAgencia LEFT JOIN Localidades AS LA ON A.IdLocal=LA.IdLocal
 LEFT JOIN Formaspago AS F ON G.CdForma=F.IdForma LEFT JOIN Sys_Um AS UMP ON D.UndMed=UMP.UndMed
 LEFT JOIN UndMed AS UM ON D.IdUnd=UM.IdUnd LEFT JOIN Empaques AS EP ON D.IdEmp=EP.IdEmp
 LEFT JOIN ConcDiversos AS DC ON D.CdConcepto=DC.IdConcepto 
 WHERE G.TipDoc=@pmTipDoc AND G.NumGuia BETWEEN @pmNumGuiaIni AND @pmNumGuiaFin AND G.IdCia=@pmIdCia
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryEstadoGuia] @pmIdEstado VARCHAR(4)
 AS 
 IF @pmIdEstado IS NULL
  BEGIN
   SELECT IdEstado,Estado,NColor
   FROM EstadoGuia
   WHERE Inactivo=0
  END
 ELSE
  BEGIN
   SELECT IdEstado,Estado,NColor,Inactivo
   FROM EstadoGuia
   WHERE IdEstado=@pmIdEstado
  END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paDelEstadoGuia] @pmIdEstado VARCHAR(4)
 AS 
 DELETE FROM EstadoGuia WHERE IdEstado=@pmIdEstado
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paInsEstadoGuia] @pmIdEstado VARCHAR(4),@pmEstado VARCHAR(50),@pmNColor INT,@pmInactivo BIT
 AS 
 INSERT INTO EstadoGuia (IdEstado,Estado,NColor,Inactivo)
 VALUES (@pmIdEstado,@pmEstado,@pmNColor,@pmInactivo)
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paUpEstadoGuia] @pmIdEstado VARCHAR(4),@pmEstado VARCHAR(50),@pmNColor INT,@pmInactivo BIT
 AS 
 UPDATE EstadoGuia SET Estado=@pmEstado,NColor=@pmNColor,Inactivo=@pmInactivo
 WHERE IdEstado=@pmIdEstado
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paDelTraGuias] @pmTipDoc VARCHAR(3),@pmNumGuia INT,@pmIdCia CHAR(2)
 AS 
 DELETE FROM Trn_TraGuias
 WHERE TipDoc=@pmTipDoc AND NumGuia=@pmNumGuia AND IdCia=@pmIdCia
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paInsTraGuias] @pmTipDoc VARCHAR(3),@pmNumGuia INT,@pmIdCia CHAR(2),@pmFecha SMALLDATETIME,@pmFecDespacho SMALLDATETIME,@pmFecVigencia SMALLDATETIME,@pmModalidad VARCHAR(10),@pmIdCliente VARCHAR(16),@pmIdAgencia VARCHAR(16),@pmIdRemitente VARCHAR(16),@pmNomRemitente VARCHAR(150),@pmIdDestinatario VARCHAR(16),@pmNomDestinatario VARCHAR(150),@pmDirOrigen VARCHAR(250),@pmIdLocOrigen VARCHAR(8),@pmDirDestino VARCHAR(250),@pmIdLocDestino VARCHAR(8),@pmNomContacto VARCHAR(150),@pmTelContacto VARCHAR(30),@pmemlContacto VARCHAR(150),@pmTelRemitente VARCHAR(30),@pmemlRemitente VARCHAR(150),@pmTelDestinatario VARCHAR(30),@pmemlDestinatario VARCHAR(150),@pmIdResponsable VARCHAR(16),@pmNomResponsable VARCHAR(150),@pmVrTotal MONEY,@pmVrDeclarado MONEY,@pmVrSeguro MONEY,@pmVrCargos MONEY,@pmCantidad DECIMAL(14,4),@pmPesoTotal DECIMAL(14,4),@pmTipRem VARCHAR(3)
,@pmNumRemesa INT,@pmIdCiaRem CHAR(2),@pmEstFactura INT,@pmTipFac VARCHAR(3),@pmNumFactura INT,@pmIdCiaFac CHAR(2),@pmFechaFac SMALLDATETIME,@pmCdConcepto VARCHAR(4),@pmCdForma VARCHAR(4),@pmTipCom VARCHAR(3),@pmComprobante INT,@pmIdCiaCom CHAR(2),@pmAnulado BIT,@pmFecDev SMALLDATETIME,@pmObservacion VARCHAR(2000),@pmIdEstado VARCHAR(4),@pmOrigenAdd VARCHAR(10),@pmFechaCrea SMALLDATETIME,@pmIdCiaCrea CHAR(2),@pmIdUsuario VARCHAR(11)
 AS 
 INSERT INTO Trn_TraGuias (TipDoc,NumGuia,IdCia,Fecha,FecDespacho,FecVigencia,Modalidad,IdCliente,IdAgencia,IdRemitente,NomRemitente,IdDestinatario,NomDestinatario,DirOrigen,IdLocOrigen,DirDestino,IdLocDestino,NomContacto,TelContacto,emlContacto,TelRemitente,emlRemitente,TelDestinatario,emlDestinatario,IdResponsable,NomResponsable,VrTotal,VrDeclarado,VrSeguro,VrCargos,Cantidad,PesoTotal,TipRem,NumRemesa,IdCiaRem,EstFactura,TipFac,NumFactura,IdCiaFac,FechaFac,CdConcepto,CdForma,TipCom,Comprobante,IdCiaCom,OrigenAdd,Anulado,FecDev,Observacion,IdEstado,FechaCrea,IdCiaCrea,IdUsuario)
 VALUES (@pmTipDoc,@pmNumGuia,@pmIdCia,@pmFecha,@pmFecDespacho,@pmFecVigencia,@pmModalidad,@pmIdCliente,@pmIdAgencia,@pmIdRemitente,@pmNomRemitente,@pmIdDestinatario,@pmNomDestinatario,@pmDirOrigen,@pmIdLocOrigen,@pmDirDestino,@pmIdLocDestino,@pmNomContacto,@pmTelContacto,@pmemlContacto,@pmTelRemitente,@pmemlRemitente,@pmTelDestinatario,@pmemlDestinatario,@pmIdResponsable,@pmNomResponsable,@pmVrTotal,@pmVrDeclarado,@pmVrSeguro,@pmVrCargos,@pmCantidad,@pmPesoTotal,@pmTipRem,@pmNumRemesa,@pmIdCiaRem,@pmEstFactura,@pmTipFac,@pmNumFactura,@pmIdCiaFac,@pmFechaFac,@pmCdConcepto,@pmCdForma,@pmTipCom,@pmComprobante,@pmIdCiaCom,@pmOrigenAdd,@pmAnulado,@pmFecDev,@pmObservacion,@pmIdEstado,@pmFechaCrea,@pmIdCiaCrea,@pmIdUsuario)
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryTraGuias] @pmTipDoc VARCHAR(3),@pmNumGuia INT,@pmIdCia CHAR(2)
 AS 
 SELECT TipDoc,NumGuia,IdCia,Fecha,FecDespacho,FecVigencia,Modalidad,IdCliente,IdAgencia,IdRemitente,NomRemitente,IdDestinatario,NomDestinatario,DirOrigen,IdLocOrigen,DirDestino,IdLocDestino,NomContacto,TelContacto,emlContacto,TelRemitente,emlRemitente,TelDestinatario,emlDestinatario
,IdResponsable,NomResponsable,VrTotal,VrDeclarado,VrSeguro,VrCargos,Cantidad,PesoTotal,TipRem,NumRemesa,IdCiaRem,EstFactura,TipFac,NumFactura,IdCiaFac,FechaFac,CdConcepto,CdForma,TipCom,Comprobante,IdCiaCom,OrigenAdd,Anulado,FecDev,Observacion,IdEstado,FechaCrea,FechaModif,IdCiaCrea,IdUsuario
 FROM Trn_TraGuias
 WHERE TipDoc=@pmTipDoc AND NumGuia=@pmNumGuia AND IdCia=@pmIdCia
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paUpTraGuias] @pmTipDoc VARCHAR(3),@pmNumGuia INT,@pmIdCia CHAR(2),@pmFecha SMALLDATETIME,@pmFecDespacho SMALLDATETIME,@pmFecVigencia SMALLDATETIME,@pmModalidad VARCHAR(10),@pmIdCliente VARCHAR(16),@pmIdAgencia VARCHAR(16),@pmIdRemitente VARCHAR(16),@pmNomRemitente VARCHAR(150),@pmIdDestinatario VARCHAR(16),@pmNomDestinatario VARCHAR(150),@pmDirOrigen VARCHAR(250),@pmIdLocOrigen VARCHAR(8),@pmDirDestino VARCHAR(250),@pmIdLocDestino VARCHAR(8),@pmNomContacto VARCHAR(150),@pmTelContacto VARCHAR(30),@pmemlContacto VARCHAR(150),@pmTelRemitente VARCHAR(30),@pmemlRemitente VARCHAR(150),@pmTelDestinatario VARCHAR(30),@pmemlDestinatario VARCHAR(150),@pmIdResponsable VARCHAR(16),@pmNomResponsable VARCHAR(150),@pmVrTotal MONEY,@pmVrDeclarado MONEY,@pmVrSeguro MONEY,@pmVrCargos MONEY,@pmCantidad DECIMAL(14,4),@pmPesoTotal DECIMAL(14,4),@pmTipRem VARCHAR(3)
,@pmNumRemesa INT,@pmIdCiaRem CHAR(2),@pmEstFactura INT,@pmTipFac VARCHAR(3),@pmNumFactura INT,@pmIdCiaFac CHAR(2),@pmFechaFac SMALLDATETIME,@pmCdConcepto VARCHAR(4),@pmCdForma VARCHAR(4),@pmTipCom VARCHAR(3),@pmComprobante INT,@pmIdCiaCom CHAR(2),@pmAnulado BIT,@pmFecDev SMALLDATETIME,@pmObservacion VARCHAR(2000),@pmIdEstado VARCHAR(4),@pmFechaModif SMALLDATETIME
 AS 
 UPDATE Trn_TraGuias SET Fecha=@pmFecha,FecDespacho=@pmFecDespacho,FecVigencia=@pmFecVigencia,Modalidad=@pmModalidad,IdCliente=@pmIdCliente,IdAgencia=@pmIdAgencia,IdRemitente=@pmIdRemitente,NomRemitente=@pmNomRemitente,IdDestinatario=@pmIdDestinatario,NomDestinatario=@pmNomDestinatario,DirOrigen=@pmDirOrigen,IdLocOrigen=@pmIdLocOrigen,DirDestino=@pmDirDestino,IdLocDestino=@pmIdLocDestino,NomContacto=@pmNomContacto,TelContacto=@pmTelContacto,emlContacto=@pmemlContacto,TelRemitente=@pmTelRemitente,emlRemitente=@pmemlRemitente,TelDestinatario=@pmTelDestinatario,emlDestinatario=@pmemlDestinatario,IdResponsable=@pmIdResponsable,NomResponsable=@pmNomResponsable,VrTotal=@pmVrTotal,VrDeclarado=@pmVrDeclarado,VrSeguro=@pmVrSeguro,VrCargos=@pmVrCargos,Cantidad=@pmCantidad,PesoTotal=@pmPesoTotal,TipRem=@pmTipRem,NumRemesa=@pmNumRemesa,IdCiaRem=@pmIdCiaRem,EstFactura=@pmEstFactura,TipFac=@pmTipFac,NumFactura=@pmNumFactura,IdCiaFac=@pmIdCiaFac,FechaFac=@pmFechaFac
,CdConcepto=@pmCdConcepto,CdForma=@pmCdForma,TipCom=@pmTipCom,Comprobante=@pmComprobante,IdCiaCom=@pmIdCiaCom,Anulado=@pmAnulado,FecDev=@pmFecDev,Observacion=@pmObservacion,IdEstado=@pmIdEstado,FechaModif=@pmFechaModif
 WHERE TipDoc=@pmTipDoc AND NumGuia=@pmNumGuia AND IdCia=@pmIdCia
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paInstm_TraDetalle_Gtc] @pmTipDoc VARCHAR(3),@pmNumGuia INT,@pmIdCia CHAR(2),@pmtmNumero VARCHAR(5)
 AS 
 INSERT INTO tm_TraDetalle (tmNumero,tmItem,tmIdMercancia,tmDescripMcias,tmCantidad,tmPesoNeto,tmUndMed,tmdmsAlto,tmdmsAncho,tmdmsLargo,tmVolumen,tmUndVol,tmIdUnd,tmIdEmp,tmIdNat
,tmIdMnjo,tmIdTmcia,tmCdRango,tmCases,tmCajas,tmPalets,tmRemision,tmNumOrden,tmNitRemite,tmRemitente,tmDirOrigen,tmIdOrigen,tmNitDestntario,tmDestinatario,tmDirDestino,tmIdDestino
,tmCdAgencia,tmTipoVehic,tmTarifClie,tmTarifPago,tmTarifTabla,tmVrDeclarado,tmVrSeguro,tmTarifSeguro,tmDocCliente,tmReferencia1,tmReferencia2,tmReferencia3,tmContenedor1,tmContenedor2,tmUndTarifa
,tmTipRem,tmRemesa,tmIdCiaRem,tmItemRem,tmNumMintrans,tmPesoCont,tmUndTarPago,tmCodBodega,tmTipoTraslado,tmTieCargue,tmTieDesc,tmFecIniCargue,tmFecFinCargue,tmFecInidesc,tmFecFindesc,tmCdTipoVeh
,tmTipoServicio,tmSedeRem,tmSedeDest,tmFecLlegaCargue,tmFecLlegaDesc,tmMinCargue,tmMinDescargue)
SELECT @pmtmNumero,D.Item,D.IdMercancia,D.DescripcionMcia,D.Cantidad,D.PesoNeto,D.UndMed,D.dmsAlto,D.dmsAncho,D.dmsLargo,D.Volumen,D.UndVol,D.IdUnd,D.IdEmp,D.IdNat
,D.IdMnjo,D.IdTmcia,D.CdConcepto,0,0,0,D.Remision,G.NumRemesa,G.IdRemitente,G.NomRemitente,G.DirOrigen,G.IdLocOrigen,G.IdDestinatario,G.NomDestinatario,G.DirDestino,G.IdLocDestino
,G.IdAgencia,'TERCEROS',D.VrUnitario,0,0,D.VrDeclarado,D.VrSeguro,D.TarifSeguro,D.DocCliente,D.Referencia1,D.Referencia2,D.Referencia3,'','',D.UndTarifa
,G.TipRem,G.NumRemesa,G.IdCiaRem,0,0,0,D.UndTarifa,'0',0,0,0,Null,Null,Null,Null,'0','0','','',Null,Null,0,0
 FROM Trn_TraGuias AS G INNER JOIN Trn_TraGuiaMcia AS D ON G.TipDoc=D.TipDoc AND G.NumGuia=D.NumGuia AND G.IdCia=D.IdCia
 WHERE G.TipDoc=@pmTipDoc AND G.NumGuia=@pmNumGuia AND G.IdCia=@pmIdCia
GO
--Marzo 3

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInstm_Kdex_Ord]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paInstm_Kdex_Ord]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInstm_Kdex_Sel]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paInstm_Kdex_Sel]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInstm_Kdex_SelDev]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paInstm_Kdex_SelDev]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryCotizacionDet]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryCotizacionDet]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryOpedidoDet]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryOpedidoDet]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryRemisionDet]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryRemisionDet]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQrytm_KdexPdc]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQrytm_KdexPdc]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQrytm_KdexPed]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQrytm_KdexPed]
GO

SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[paQryCotizacionDet]  @pmCotizacion INT,@pmIdCia CHAR(2)
 AS 
 SELECT Item,K.IdProducto AS CdProducto,Descripcion,K.IdBodega AS CdBodega,Bodega,Salidas,VrPrecio,Salidas*VrPrecio AS VrSubTotal
,TarifaDct,VrDctoSal,TarifaIva,VrIvaSal,((Salidas*VrPrecio)-VrDctoSal)+VrIvaSal AS VrTotal,VrBruto,K.ListaPrec AS LtaPrec,VrBase,K.Referencia AS Referncia,Referencia2,Unidades
,CodTarDct,CodTarIva,K.IdVend AS NitVend,V.RazonSocial AS Vendedor,Comision,K.CodTarCom AS CdTarifCom,DescripProd,Servcios,Tanques,CdTanque,CdMoneda,VrTasaCamb
,C.Fecha AS FecPedido,FechaVence,IdCliente,T.RazonSocial AS Cliente,IdAgencia,Modalidad,C.Factura AS NumFactura,IdCiaFac,FechaFact,CantObseq,VrIvaObseq
 FROM  Trn_Cotizacion AS C INNER JOIN Trn_Kardex AS K ON C.TipDoc=K.TipDoc AND C.Cotizacion=K.Documento AND C.IdCia=K.IdCia
 INNER JOIN ProdMcias AS P ON K.IdProducto=P.IdProducto
 INNER JOIN Bodegas AS B ON K.IdBodega=B.IdBodega
 INNER JOIN Terceros AS T ON C.IdCliente=T.IdTercero
 INNER JOIN Terceros AS  V ON K.IdVend=V.IdTercero
 WHERE C.Cotizacion=@pmCotizacion  AND C.IdCia=@pmIdCia
 ORDER BY  Item
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[paQryRemisionDet]  @pmRemision INT,@pmIdCia CHAR(2)
 AS 
 SELECT Item,K.IdProducto AS CdProducto,Descripcion,K.IdBodega AS CdBodega,Bodega,Salidas,VrPrecio,Salidas*VrPrecio AS VrSubTotal
,TarifaDct,VrDctoSal,TarifaIva,VrIvaSal,((Salidas*VrPrecio)-VrDctoSal)+VrIvaSal AS VrTotal,VrBruto,K.ListaPrec AS LtaPrec,VrBase,K.Referencia AS Referncia,Referencia2,K.Unidades AS Undades
,CodTarDct,CodTarIva,K.IdVend AS NitVend,V.RazonSocial AS Vendedor,Comision,K.CodTarCom AS CdTarifCom,DescripProd,Servcios,Tanques,CdTanque,CdMoneda,VrTasaCamb
,R.Fecha AS FecRemision,FechaDesp,DiasEntraga,FechaVence,IdCliente,T.RazonSocial AS Cliente,IdAgencia,Modalidad,Cantidad,R.Unidades AS TotalUnd
,DirEnvio,IdLocEnv,Localidad,NitContac,NomContac,TelContac,emlContac,CargoContac,NitEmpTrans,EmpTrans
,R.pVehiculo AS PlacaVeh,DetalleEnvio,TipPed,Pedido,IdCiaPed,FechaPed,CantObseq,VrIvaObseq
 FROM  Trn_Remision AS R INNER JOIN Trn_Kardex AS K ON R.TipDoc=K.TipDoc AND R.Remision=K.Documento AND R.IdCia=K.IdCia
 INNER JOIN ProdMcias AS P ON K.IdProducto=P.IdProducto
 INNER JOIN Bodegas AS B ON K.IdBodega=B.IdBodega
 INNER JOIN Terceros AS T ON R.IdCliente=T.IdTercero
 INNER JOIN Terceros AS  V ON K.IdVend=V.IdTercero
 INNER JOIN Localidades AS L ON R.IdLocEnv=L.IdLocal
 WHERE R.Remision=@pmRemision AND R.IdCia=@pmIdCia
 ORDER BY  Item
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[paQrytm_KdexPed] @pmtmNumero VARCHAR(5)
 AS 
 SELECT tmItem,tmIdProducto,DescripProd,tmIdBodega,Bodega,tmCdTanque,tmSalidas,tmVrPrecio,tmTarifaDct,tmVrDcto,(tmSalidas*tmVrPrecio)-tmVrDcto AS VrSubTotal
,tmTarifaIva,tmVrIva,((tmSalidas*tmVrPrecio)-tmVrDcto)+tmVrIva AS VrTotal,tmComptmntos,tmSobretasa,tmImpGlobal,tmSoldicom,tmTasaNac,tmTasaDep,tmTasaMun
,tmVrBruto,tmListaPrec,tmUnidades,tmReferencia,tmDescripcion,tmCodTarDct
,tmIdVend,tmComision,tmCodTarCom,tmServcios,Tanques,tmVrUnitario,tmVrBase,tmCdMoneda,tmVrTasaCamb,tmTipDoc,tmDocumento,tmIdCia,tmRec_Costo,tmMgenCont
,tmIvaComb,tmImpCarb,tmCdOperario,tmComisnOper,tmCodTarCmc,tmpVehiculo,tmBaseIvp,tmTarifaIvp,tmIvaIngProd,tmItemCbo,tmCantObseq,tmIvaObseq
 FROM  tm_Kdex AS K INNER JOIN ProdMcias AS P ON K.tmIdProducto=P.IdProducto 
 INNER JOIN Bodegas AS B ON K.tmIdBodega=B.IdBodega
 WHERE  tmNumero=@pmtmNumero 
 ORDER BY tmItem
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[paQryOpedidoDet] @pmPedido INT,@pmIdCia CHAR(2)
 AS 
 SELECT Item,K.IdProducto AS CdProducto,Descripcion,K.IdBodega AS CdBodega,Bodega
,CASE WHEN O.Modalidad='PRODUCCION' THEN (CASE WHEN K.Unidades>0 THEN (K.Unidades-K.galsneto) ELSE (K.Salidas-K.galsneto) END) ELSE Salidas END AS Cantidad
,VrPrecio,Salidas*VrPrecio AS VrSubTotal,TarifaDct,VrDctoSal,TarifaIva,VrIvaSal,((Salidas*VrPrecio)-VrDctoSal)+VrIvaSal AS VrTotal,VrBruto,K.ListaPrec AS LtaPrec,VrBase,K.Referencia AS Referncia,Referencia2
,Salidas,Unidades,galsneto,CodTarDct,CodTarIva,K.IdVend AS NitVend,V.RazonSocial AS Vendedor,Comision,K.CodTarCom AS CdTarifCom,DescripProd,Servcios,Tanques,CdTanque,CdMoneda,VrTasaCamb
,O.Fecha AS FecPedido,FechaVence,DiasEntraga,IdCliente,T.RazonSocial AS Cliente,IdAgencia,Modalidad,Vigencia,O.Factura AS NumFactura,IdCiaFac,FechaFact
,O.Remision AS NumRemision,O.IdCiaRem AS CiaRem,FechaRem,CantObseq,VrIvaObseq
 FROM Trn_Opedido AS O INNER JOIN Trn_Kardex AS K ON O.TipDoc=K.TipDoc AND O.Pedido=K.Documento AND O.IdCia=K.IdCia
 INNER JOIN ProdMcias AS P ON K.IdProducto=P.IdProducto
 INNER JOIN Bodegas AS B ON K.IdBodega=B.IdBodega
 INNER JOIN Terceros AS T ON O.IdCliente=T.IdTercero
 INNER JOIN Terceros AS  V ON K.IdVend=V.IdTercero
 WHERE Pedido=@pmPedido AND O.IdCia=@pmIdCia
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[paInstm_Kdex_SelDev] @pmTipDoc VARCHAR(3),@pmDocumento INT,@pmIdCia CHAR(2),@pmtmNumero VARCHAR(5)
 AS 
 INSERT INTO tm_Kdex (tmNumero,tmItem,tmIdProducto,tmIdBodega,tmCdTanque,tmEntradas,tmSalidas,tmIdUnd,tmVrUnitario,tmVrPrecio,tmVrCostProm,tmVrProm,tmTarifaIva,tmVrIva,tmTarifaDct,tmVrDcto,tmTarifaRet,tmVrRete
,tmTarifaIca,tmVrIca,tmVrBruto,tmCdUbic,tmNumLote,tmFechLote,tmFecVceLote,tmIdTercero,tmCdAgencia,tmCdCCosto,tmCdSubCos,tmCdLocal,tmCdSzona,tmpVehiculo,tmIdVend
,tmComision,tmCdOperario,tmComisnOper,tmReferencia,tmDescripcion,tmComptmntos,tmCdProdEquiv,tmCdMngra,tmNumInicial,tmNumFinal,tmSobretasa,tmTasaNac,tmTasaDep,tmTasaMun,tmSoldicom,tmImpGlobal,tmOtroImpto
,tmUnidades,tmServcios,tmNoVentas,tmEsCombo,tmEsProdBase,tmItemCbo,tmCodTarDct,tmCodTarIva,tmCodTarIca,tmCodTarRet,tmCodTarCom,tmCodTarCmc,tmCdSubgrupo
,tmListaPrec,tmTipDoc,tmDocumento,tmIdCia,tmVrBase,tmCdMoneda,tmVrTasaCamb,tmTipRem,tmRemision,tmIdCiaRem,tmReferencia2,tmFecOrden,tmgalsbruto,tmgalsneto,tmTemperatura,tmUmTemp,tmDensidad,tmRec_Costo,tmMgenCont
,tmCotizacion,tmCiaCotiza,tmVrImvCosto,tmTarifaIco,tmVrImpCon,tmCantObseq,tmIvaObseq,tmIvaComb,tmImpCarb,tmIngCombo,tmTarifaStc,tmSobtasaCons,tmCodTarIco,tmBaseIvp,tmTarifaIvp,tmIvaIngProd)
SELECT @pmtmNumero,Item,K.IdProducto,K.IdBodega,CdTanque,CASE WHEN CantObseq>0 AND (TipDoc='DVE' OR TipDoc='DEI' OR SUBSTRING(TipDoc,1,2)='FC' OR SUBSTRING(TipDoc,1,2)='FO' OR TipDoc='PED' OR TipDoc='COT' OR TipDoc='REM') THEN Salidas-CantObseq ELSE Salidas END
,CASE WHEN CantObseq>0 AND (TipDoc='ENT' OR TipDoc='COM' OR SUBSTRING(TipDoc,1,2)='DF' OR SUBSTRING(TipDoc,1,2)='DO') THEN Entradas-CantObseq ELSE Entradas END
,K.IdUnd,VrUnitario,VrPrecio,VrCostProm,0,TarifaIva,CASE WHEN CantObseq>0 AND VrIvaObseq>0 THEN (VrIvaEnt+VrIvaSal)-VrIvaObseq ELSE VrIvaEnt+VrIvaSal END,TarifaDct,VrDctoEnt+VrDctoSal,TarifaRet,VrReteEnt+VrReteSal
,TarifaIca,VrIcaEnt+VrIcaSal,VrBruto,CdUbic,NumLote,FechLote,FechLote,IdTercero,CdAgencia,CdCCosto,CdSubCos,CdLocal,CdSzona,pVehiculo,IdVend
,Comision,CdOperario,ComisnOper,K.Referencia,Descripcion,Comptmntos,CdProdEquiv,CdMngra,NumInicial,NumFinal,Sobretasa,TasaNac,TasaDep,TasaMun,Soldicom,ImpGlobal,OtroImpto
,Unidades,Servcios,NoVentas,EsCombo,EsProdBase,ItemCombo,CodTarDct,CodTarIva,CodTarIca,CodTarRet,CodTarCom,CodTarCmc,IdSubgrupo,ListaPrec,TipOrd,NumOrden,IdCiaOrd,VrBase,CdMoneda,VrTasaCamb
,CASE WHEN TipOrd='CPP' AND NumOrden>0 THEN 'OPP' ELSE 'REM' END
,Remision,IdCiaRem,Referencia2,FecOrden,galsbruto,galsneto,Temperatura,UmTemp,Densidad,Rec_Costo,MgenCont,Cotizacion,IdCiaCot,VrImvCosto,TarifaIco,VrImpCon,CantObseq,VrIvaObseq
,BaseIvaCom,ImpCarbono,IngBaseCom,TarifaStc,SobtasaCons,CodTarIco,BaseIvp,TarifaIvp,IvaIngProd
 FROM Trn_Kardex AS K INNER JOIN ProdMcias AS P ON K.IdProducto=P.IdProducto
 WHERE TipDoc=@pmTipDoc AND Documento=@pmDocumento AND IdCia=@pmIdCia  AND  EsProdBase<=1
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[paQrytm_KdexPdc] @pmtmNumero VARCHAR(5)
 AS 
 SELECT tmItem,tmIdProducto,DescripProd,tmCdTanque,tmSalidas,tmVrPrecio,tmTarifaDct,tmVrDcto,(tmSalidas*tmVrPrecio)-tmVrDcto AS VrSubTotal
,tmIvaComb*tmSalidas AS BaseIva,tmTarifaIva,tmVrIva,tmBaseIvp*tmSalidas AS BaseIngProd,tmTarifaIvp,tmIvaIngProd,((tmSalidas*tmVrPrecio)-tmVrDcto)+tmVrIva+tmIvaIngProd AS VrTotal
,tmComptmntos,tmSobretasa,tmImpGlobal,tmSoldicom,tmTasaNac,tmTasaDep,tmTasaMun,tmImpCarb,tmRec_Costo,tmMgenCont,tmVrBruto,tmListaPrec
,tmUnidades,tmReferencia,tmDescripcion,tmCodTarDct,tmIdVend,tmComision,tmCodTarCom,tmCdOperario,tmComisnOper,tmCodTarCmc,tmServcios,Tanques,tmVrUnitario,tmVrBase
,tmCdMoneda,tmVrTasaCamb,tmTipDoc,tmDocumento,tmIdCia,tmIdBodega,tmpVehiculo,tmIvaComb,tmBaseIvp,tmCantObseq,tmIvaObseq
 FROM tm_Kdex AS K INNER JOIN ProdMcias AS P ON K.tmIdProducto=P.IdProducto 
 --INNER JOIN Bodegas AS B ON K.tmIdBodega=B.IdBodega
 WHERE tmNumero=@pmtmNumero 
 ORDER BY tmItem
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[paInstm_Kdex_Ord] @pmTipDoc VARCHAR(3),@pmDocumento INT,@pmIdCia CHAR(2),@pmtmNumero VARCHAR(5)
 AS 
 INSERT INTO tm_Kdex (tmNumero,tmItem,tmIdProducto,tmIdBodega,tmCdTanque,tmEntradas,tmSalidas,tmIdUnd,tmVrUnitario,tmVrPrecio,tmVrCostProm,tmVrProm,tmTarifaIva,tmVrIva,tmTarifaDct,tmVrDcto,tmTarifaRet,tmVrRete
,tmTarifaIca,tmVrIca,tmVrBruto,tmCdUbic,tmNumLote,tmFechLote,tmFecVceLote,tmIdTercero,tmCdAgencia,tmCdCCosto,tmCdSubCos,tmCdLocal,tmCdSzona,tmpVehiculo,tmIdVend
,tmComision,tmCdOperario,tmComisnOper,tmReferencia,tmDescripcion,tmComptmntos,tmCdProdEquiv,tmCdMngra,tmNumInicial,tmNumFinal,tmSobretasa,tmTasaNac,tmTasaDep,tmTasaMun,tmSoldicom,tmImpGlobal,tmOtroImpto
,tmUnidades,tmServcios,tmNoVentas,tmEsCombo,tmEsProdBase,tmItemCbo,tmCodTarDct,tmCodTarIva,tmCodTarIca,tmCodTarRet,tmCodTarCom,tmCodTarCmc,tmCdSubgrupo
,tmListaPrec,tmTipDoc,tmDocumento,tmIdCia,tmVrBase,tmCdMoneda,tmVrTasaCamb,tmTipRem,tmRemision,tmIdCiaRem,tmReferencia2,tmFecOrden,tmgalsbruto,tmgalsneto,tmTemperatura,tmUmTemp,tmDensidad
,tmRec_Costo,tmMgenCont,tmCotizacion,tmCiaCotiza,tmVrImvCosto,tmTarifaIco,tmVrImpCon,tmCantObseq,tmIvaObseq,tmIvaComb,tmImpCarb,tmIngCombo,tmTarifaStc,tmSobtasaCons,tmCodTarIco,tmBaseIvp,tmTarifaIvp,tmIvaIngProd)
SELECT @pmtmNumero,Item,K.IdProducto,K.IdBodega,CdTanque,CASE WHEN CantObseq>0 AND (TipDoc='ENT' OR TipDoc='COM' OR SUBSTRING(TipDoc,1,2)='DF' OR SUBSTRING(TipDoc,1,2)='DO') THEN Entradas-CantObseq ELSE Entradas END
,CASE WHEN CantObseq>0 AND (TipDoc='DVE' OR TipDoc='DEI' OR SUBSTRING(TipDoc,1,2)='FC' OR SUBSTRING(TipDoc,1,2)='FO') THEN Salidas-CantObseq 
 WHEN (TipDoc='PED' AND TipDocDev='PPD' AND Unidades>0) THEN Unidades WHEN (TipDoc='PED' OR TipDoc='COT' OR TipDoc='REM') AND CantObseq>0 THEN Salidas-CantObseq ELSE Salidas END
,K.IdUnd,VrUnitario,VrPrecio,VrCostProm,0,TarifaIva,CASE WHEN CantObseq>0 AND VrIvaObseq>0 THEN (VrIvaEnt+VrIvaSal)-VrIvaObseq ELSE VrIvaEnt+VrIvaSal END
,TarifaDct,VrDctoEnt+VrDctoSal,TarifaRet,VrReteEnt+VrReteSal
,TarifaIca,VrIcaEnt+VrIcaSal,VrBruto,CdUbic,NumLote,FechLote,FechLote,IdTercero,CdAgencia,CdCCosto,CdSubCos,CdLocal,CdSzona,pVehiculo,IdVend
,Comision,CdOperario,ComisnOper,K.Referencia,Descripcion,Comptmntos,CdProdEquiv,CdMngra,NumInicial,NumFinal,Sobretasa,TasaNac,TasaDep,TasaMun,Soldicom,ImpGlobal,OtroImpto
,Unidades,Servcios,NoVentas,EsCombo,EsProdBase,ItemCombo,CodTarDct,CodTarIva,CodTarIca,CodTarRet,CodTarCom,CodTarCmc,IdSubgrupo,ListaPrec,TipOrd,NumOrden,IdCiaOrd,VrBase,CdMoneda,VrTasaCamb
,'REM',Remision,IdCiaRem,Referencia2,FecOrden,galsbruto,galsneto,Temperatura,UmTemp,Densidad,Rec_Costo,MgenCont,Cotizacion,IdCiaCot,VrImvCosto,TarifaIco,VrImpCon,CantObseq,VrIvaObseq
,BaseIvaCom,ImpCarbono,IngBaseCom,TarifaStc,SobtasaCons,CodTarIco,BaseIvp,TarifaIvp,IvaIngProd
 FROM Trn_Kardex AS K INNER JOIN ProdMcias AS P ON K.IdProducto=P.IdProducto
 WHERE TipDoc=@pmTipDoc AND Documento=@pmDocumento AND IdCia=@pmIdCia AND EsProdBase=0
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[paInstm_Kdex_Sel] @pmTipDoc VARCHAR(3),@pmDocumento INT,@pmIdCia CHAR(2),@pmtmNumero VARCHAR(5)
 AS 
 INSERT INTO tm_Kdex (tmNumero,tmItem,tmIdProducto,tmIdBodega,tmCdTanque,tmEntradas,tmSalidas,tmIdUnd,tmVrUnitario,tmVrPrecio,tmVrCostProm,tmVrProm,tmTarifaIva,tmVrIva,tmTarifaDct,tmVrDcto,tmTarifaRet,tmVrRete
,tmTarifaIca,tmVrIca,tmVrBruto,tmCdUbic,tmNumLote,tmFechLote,tmFecVceLote,tmIdTercero,tmCdAgencia,tmCdCCosto,tmCdSubCos,tmCdLocal,tmCdSzona,tmpVehiculo,tmIdVend
,tmComision,tmCdOperario,tmComisnOper,tmReferencia,tmDescripcion,tmComptmntos,tmCdProdEquiv,tmCdMngra,tmNumInicial,tmNumFinal,tmSobretasa,tmTasaNac,tmTasaDep,tmTasaMun,tmSoldicom,tmImpGlobal,tmOtroImpto
,tmUnidades,tmServcios,tmNoVentas,tmEsCombo,tmEsProdBase,tmItemCbo,tmCodTarDct,tmCodTarIva,tmCodTarIca,tmCodTarRet,tmCodTarCom,tmCodTarCmc,tmCdSubgrupo
,tmListaPrec,tmTipDoc,tmDocumento,tmIdCia,tmVrBase,tmCdMoneda,tmVrTasaCamb,tmTipRem,tmRemision,tmIdCiaRem,tmReferencia2,tmFecOrden,tmgalsbruto,tmgalsneto,tmTemperatura,tmUmTemp,tmDensidad
,tmRec_Costo,tmMgenCont,tmCotizacion,tmCiaCotiza,tmVrImvCosto,tmTarifaIco,tmVrImpCon,tmCantObseq,tmIvaObseq,tmIvaComb,tmImpCarb,tmIngCombo,tmTarifaStc,tmSobtasaCons,tmCodTarIco,tmBaseIvp,tmTarifaIvp,tmIvaIngProd)
SELECT @pmtmNumero,Item,K.IdProducto,K.IdBodega,CdTanque,CASE WHEN CantObseq>0 AND (TipDoc='ENT' OR TipDoc='COM' OR SUBSTRING(TipDoc,1,2)='DF' OR SUBSTRING(TipDoc,1,2)='DO') THEN Entradas-CantObseq ELSE Entradas END
,CASE WHEN CantObseq>0 AND (TipDoc='DVE' OR TipDoc='DEI' OR SUBSTRING(TipDoc,1,2)='FC' OR SUBSTRING(TipDoc,1,2)='FO' OR TipDoc='PED' OR TipDoc='COT' OR TipDoc='REM') THEN Salidas-CantObseq ELSE Salidas END
,K.IdUnd,VrUnitario,VrPrecio,VrCostProm,0,TarifaIva,CASE WHEN CantObseq>0 AND VrIvaObseq>0 THEN (VrIvaEnt+VrIvaSal)-VrIvaObseq ELSE VrIvaEnt+VrIvaSal END
,TarifaDct,VrDctoEnt+VrDctoSal,TarifaRet,VrReteEnt+VrReteSal
,TarifaIca,VrIcaEnt+VrIcaSal,VrBruto,CdUbic,NumLote,FechLote,FechLote,IdTercero,CdAgencia,CdCCosto,CdSubCos,CdLocal,CdSzona,pVehiculo,IdVend
,Comision,CdOperario,ComisnOper,K.Referencia,Descripcion,Comptmntos,CdProdEquiv,CdMngra,NumInicial,NumFinal,Sobretasa,TasaNac,TasaDep,TasaMun,Soldicom,ImpGlobal,OtroImpto
,Unidades,Servcios,NoVentas,EsCombo,EsProdBase,ItemCombo,CodTarDct,CodTarIva,CodTarIca,CodTarRet,CodTarCom,CodTarCmc,IdSubgrupo,ListaPrec,TipOrd,NumOrden,IdCiaOrd,VrBase,CdMoneda,VrTasaCamb
,'REM',Remision,IdCiaRem,Referencia2,FecOrden,galsbruto,galsneto,Temperatura,UmTemp,Densidad,Rec_Costo,MgenCont,Cotizacion,IdCiaCot,VrImvCosto,TarifaIco,VrImpCon,CantObseq,VrIvaObseq
,BaseIvaCom,ImpCarbono,IngBaseCom,TarifaStc,SobtasaCons,CodTarIco,BaseIvp,TarifaIvp,IvaIngProd
 FROM Trn_Kardex AS K INNER JOIN ProdMcias AS P ON K.IdProducto=P.IdProducto
 WHERE TipDoc=@pmTipDoc AND Documento=@pmDocumento AND IdCia=@pmIdCia AND EsProdBase=0
GO
--MARZO 7
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryTraGuiasLta]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryTraGuiasLta]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryTraGuiasLta] @pmTipDoc VARCHAR(3),@pmFechaIni SMALLDATETIME,@pmFechaFin SMALLDATETIME,@pmIdCia CHAR(2)=Null
 AS 
SELECT G.TipDoc,G.NumGuia,G.IdCia,Compania,G.Fecha,G.FecDespacho,G.FecVigencia,G.Modalidad,T.TipoId,G.IdCliente,T.Dv,T.RazonSocial AS Nom_Cliente,G.IdAgencia,A.Agencia AS NomAgencia
,G.IdRemitente,G.NomRemitente,G.DirOrigen,G.TelRemitente,G.emlRemitente,G.IdDestinatario,G.NomDestinatario,G.DirDestino,G.TelDestinatario,G.emlDestinatario
,G.IdLocOrigen,LO.Localidad AS CiudadOrigen,DP.Departamento AS DptoOrigen,G.IdLocDestino,LD.Localidad AS CiudadDestino,DD.Departamento AS DptoDestino,G.NomContacto,G.TelContacto,G.emlContacto
,G.IdResponsable,G.NomResponsable,G.VrTotal,G.VrDeclarado AS VrTotDeclarado,G.VrSeguro AS VrTotSeguro,G.VrCargos,G.Cantidad AS Cant_Total,G.PesoTotal,G.TipRem,G.NumRemesa,G.IdCiaRem,G.EstFactura,G.TipFac,G.NumFactura,G.IdCiaFac,G.FechaFac
,G.EstLiquida,G.TipLiq,G.NumLiquida,G.IdCiaLiq,G.CdConcepto,C.Concepto,G.CdForma,FormaPago,G.TipCom,TC.TipoCom,G.Comprobante,G.IdCiaCom,G.Anulado,G.FecDev,G.Observacion,G.IdEstado,ED.Estado,G.FechaCrea,G.IdUsuario,Usuario
--datos de factura
,GF.FacCantidad,GF.FacValorTotal,GF.FacVrSeguro,GF.FacVrCargos,GF.FacUnidades,GF.FacPesoNeto,GF.FacVolumen
,GM.NumManif,GM.CiaManif,MC.IdVehiculo,MC.TipoAfiVehic,MC.IdConductor,NC.RazonSocial AS NomConductor,MC.IdPoseedor,NP.RazonSocial AS NomPoseedor,MC.EstOrden,MC.TipOdp,MC.OrdPago,MC.IdCiaOdp
,LG.LiqVrGuias,LG.LiqVrSeguro,LG.LiqVrCargos,LG.LiqCantidad,LG.LiqPesoTotal
 FROM Trn_TraGuias AS G INNER JOIN Companias AS CN ON G.IdCia=CN.IdCia INNER JOIN EstadoGuia AS ED ON G.IdEstado=ED.IdEstado
 INNER JOIN adm_Usuarios AS U ON G.IdUsuario=U.IdUsuario INNER JOIN Terceros AS T ON G.IdCliente=T.IdTercero
 INNER JOIN Localidades AS LO ON G.IdLocOrigen=LO.IdLocal INNER JOIN Departamentos AS DP ON LO.IdDep=DP.IdDep
 INNER JOIN Localidades AS LD ON G.IdLocDestino=LD.IdLocal INNER JOIN Departamentos AS DD ON LD.IdDep=DD.IdDep
 LEFT JOIN Conceptos AS C ON G.CdConcepto=C.IdConcepto LEFT JOIN Formaspago AS F ON G.CdForma=F.IdForma
 LEFT JOIN Agencias AS A ON G.IdAgencia=A.IdAgencia LEFT JOIN TiposCom AS TC ON G.TipCom=TC.IdCom
--subconsulta facturacion
 LEFT JOIN (SELECT TipRem,Remesa,IdCiaRem,SUM(CASE WHEN SUBSTRING(TipDoc,1,2)='DF' THEN Cantidad*-1 ELSE Cantidad END) AS FacCantidad
,SUM(CASE WHEN SUBSTRING(TipDoc,1,2)='DF' THEN Unidades*-1 ELSE Unidades END) AS FacUnidades
,SUM(CASE WHEN SUBSTRING(TipDoc,1,2)='DF' THEN PesoNeto*-1 ELSE PesoNeto END) AS FacPesoNeto
,SUM(CASE WHEN SUBSTRING(TipDoc,1,2)='DF' THEN Volumen*-1 ELSE Volumen END) AS FacVolumen
,SUM(CASE WHEN SUBSTRING(TipDoc,1,2)='DF' THEN (Cantidad*VrUnitario)*-1 ELSE Cantidad*VrUnitario END) AS FacValorTotal
,SUM(CASE WHEN SUBSTRING(TipDoc,1,2)='DF' THEN VrSeguroRem*-1 ELSE VrSeguroRem END) AS FacVrSeguro
,SUM(CASE WHEN SUBSTRING(TipDoc,1,2)='DF' THEN CostoAdicTbdo*-1 ELSE CostoAdicTbdo END) AS FacVrCargos
,MAX(CASE WHEN SUBSTRING(TipDoc,1,2)='DF' THEN 0 ELSE Factura END) AS NumFactura
,MAX(CASE WHEN SUBSTRING(TipDoc,1,2)='DF' THEN Factura ELSE 0 END) AS NumDevFact
FROM Trn_TraFacRemesas WHERE TipRem='GTC' GROUP BY TipRem,Remesa,IdCiaRem) AS GF ON G.TipDoc=GF.TipRem AND G.NumGuia=GF.Remesa AND G.IdCia=GF.IdCiaRem
--subconsulta No. manifiesto
LEFT JOIN (SELECT RG.TipDoc,RG.NumGuia,RG.IdCiaGuia,MAX(MR.Manifiesto) AS NumManif,MAX(MR.IdCia) AS CiaManif
 FROM Trn_TraRemGuias AS RG INNER JOIN Trn_TraManifRem AS MR ON RG.TipRem=MR.TipRem AND RG.Remesa=MR.Remesa AND RG.IdCia=MR.IdCiaRem
 INNER JOIN Trn_TraManifiesto AS M ON MR.TipDoc=M.TipDoc AND MR.Manifiesto=M.Manifiesto AND MR.IdCia=M.IdCia WHERE M.Anulado=0 GROUP BY RG.TipDoc,RG.NumGuia,RG.IdCiaGuia) AS GM ON G.TipDoc=GM.TipDoc AND G.NumGuia=GM.NumGuia AND G.IdCia=GM.IdCiaGuia
--subconsulta manifiestos
LEFT JOIN (SELECT M.TipDoc,M.Manifiesto,M.IdCia,M.Fecha,M.IdOrigen,M.IdDestino,M.IdVehiculo,M.TipoAfiVehic,M.IdConductor,M.IdPropietario,M.IdPoseedor,M.EstCumplido,M.Cumplido,M.IdCiaCump,M.EstOrden,M.TipOdp,M.OrdPago,M.IdCiaOdp,M.Observacion
,O.Fecha AS FecOPago,O.TipCom,O.Comprobante,O.IdCiaCom,O.Anulado AS OdpAnulado,OM.VrTotalFletes,OM.VrRetencion,OM.VrReteIca,OM.VrAnticipos,OM.VrFaltantes,OM.VrSeguros,OM.VrFondos,OM.VrAportes,OM.VrOtrosDctos,OM.VrImpuestos,OM.VrOtrosPagos
,OM.VrNeto,OM.TarifaTabla,OM.TarifaFlete,OM.VrRemesas,OM.VrRecCaja,OM.VrSeguroRc
FROM Trn_TraManifiesto AS M LEFT JOIN Trn_TraOrdenPago AS O ON M.TipOdp=O.TipDoc AND M.OrdPago=O.OrdPago AND M.IdCiaOdp=O.IdCia
LEFT JOIN Trn_TraOrdenManif AS OM ON M.TipOdp=OM.TipDoc AND M.OrdPago=OM.OrdPago AND M.IdCiaOdp=OM.IdCia WHERE M.Anulado=0) AS MC ON GM.NumManif=MC.Manifiesto AND GM.CiaManif=MC.IdCia
--subconsulta Liq. contraentrega
LEFT JOIN (SELECT TipGui,NumGuia,IdCiaGuia,SUM(CASE WHEN (TipDoc='ODP' OR TipDoc='LGA') THEN VrLiquidado WHEN (TipDoc='DOP' OR TipDoc='DLG') THEN VrLiquidado*-1 ELSE 0 END) AS LiqVrGuias
,SUM(CASE WHEN (TipDoc='ODP' OR TipDoc='LGA') THEN VrSeguro WHEN (TipDoc='DOP' OR TipDoc='DLG') THEN VrSeguro*-1 ELSE 0 END) AS LiqVrSeguro
,SUM(CASE WHEN (TipDoc='ODP' OR TipDoc='LGA') THEN VrCargos WHEN (TipDoc='DOP' OR TipDoc='DLG') THEN VrCargos*-1 ELSE 0 END) AS LiqVrCargos
,SUM(CASE WHEN (TipDoc='ODP' OR TipDoc='LGA') THEN Cantidad WHEN (TipDoc='DOP' OR TipDoc='DLG') THEN Cantidad*-1 ELSE 0 END) AS LiqCantidad
,SUM(CASE WHEN (TipDoc='ODP' OR TipDoc='LGA') THEN PesoTotal WHEN (TipDoc='DOP' OR TipDoc='DLG') THEN PesoTotal*-1 ELSE 0 END) AS LiqPesoTotal
FROM Trn_TraOrdenGuias GROUP BY TipGui,NumGuia,IdCiaGuia) AS LG ON G.TipDoc=LG.TipGui AND G.NumGuia=LG.NumGuia AND G.IdCia=LG.IdCiaGuia
LEFT JOIN Terceros AS NP ON MC.IdPoseedor=NP.IdTercero LEFT JOIN Terceros AS NC ON MC.IdConductor=NC.IdTercero
WHERE G.TipDoc=@pmTipDoc AND G.Fecha BETWEEN @pmFechaIni AND @pmFechaFin
AND (G.IdCia=@pmIdCia OR @pmIdCia IS NULL)
GO
--NOMINA 14/03/2023

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInsNomDetalle]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paInsNomDetalle]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInsNomDetalle_Sel]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paInsNomDetalle_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].[paQryNomDetalle]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryNomDetalle]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryNomDetallePer]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryNomDetallePer]
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].[paQryNomDetalleResN]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryNomDetalleResN]
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_Nomina_Cr]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQrytm_Nomina_Cr]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paUpNomDetalle]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paUpNomDetalle]
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

SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[paInsNomDetalle_Sel] @pmIdEmpleado VARCHAR(16),@pmNewCedula VARCHAR(16)
 AS 
 INSERT INTO Trn_NomDetalle (IdPeriodo,KeyRegistro,IdEmpleado,Item,IdConcepto,Detalle,CantDevg,CantDed,VrUnitario,VrTotDevg,VrTotDed,Unidad,ClaseLiq,VrOrigen
,Tarifa,DiasCalc,DiasNov,CodFondo,NPrestamo,NCuota,OrigCargue,VrBaseLiq,VrBasExceso,NitTercero,HoraInicial,HoraFinal,TipoIncLab,VrIngNoSal)
 SELECT D.IdPeriodo,@pmNewCedula +CAST(NContrato AS VARCHAR(10)) +CASE EsRetiro WHEN 1 THEN '_1' ELSE '_0'  END
,@pmNewCedula,Item,IdConcepto,Detalle,CantDevg,CantDed,VrUnitario,VrTotDevg,VrTotDed,Unidad,ClaseLiq,VrOrigen
,Tarifa,DiasCalc,DiasNov,CodFondo,NPrestamo,NCuota,OrigCargue,VrBaseLiq,VrBasExceso,NitTercero,HoraInicial,HoraFinal,TipoIncLab,VrIngNoSal
 FROM Trn_NomDetalle AS D INNER JOIN Trn_Nomina AS N ON D.IdPeriodo=N.IdPeriodo AND D.KeyRegistro=N.KeyRegistro
 WHERE D.IdEmpleado=@pmIdEmpleado
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[paInsNomDetalle] @pmIdPeriodo VARCHAR(8),@pmKeyRegistro VARCHAR(30),@pmIdEmpleado VARCHAR(16),@pmItem INT,@pmIdConcepto VARCHAR(4),@pmDetalle VARCHAR(100),@pmCantDevg DECIMAL(10,4),@pmCantDed DECIMAL(10,4),@pmVrUnitario MONEY,@pmVrTotDevg MONEY,@pmVrTotDed MONEY
,@pmUnidad VARCHAR(10),@pmClaseLiq VARCHAR(10),@pmVrOrigen MONEY,@pmTarifa DECIMAL(10,4),@pmDiasCalc DECIMAL(10,4),@pmDiasNov DECIMAL(10,4),@pmCodFondo VARCHAR(8),@pmNPrestamo INT,@pmNCuota INT,@pmOrigCargue VARCHAR(10),@pmVrBaseLiq MONEY,@pmVrBasExceso MONEY,@pmNitTercero VARCHAR(16)
,@pmHoraInicial DATETIME,@pmHoraFinal DATETIME,@pmTipoIncLab VARCHAR(20),@pmVrIngNoSal MONEY
 AS 
 INSERT INTO Trn_NomDetalle (IdPeriodo,KeyRegistro,IdEmpleado,Item,IdConcepto,Detalle,CantDevg,CantDed,VrUnitario,VrTotDevg,VrTotDed,Unidad,ClaseLiq,VrOrigen,Tarifa,DiasCalc,DiasNov,CodFondo,NPrestamo,NCuota,OrigCargue,VrBaseLiq,VrBasExceso,NitTercero,HoraInicial,HoraFinal,TipoIncLab,VrIngNoSal)
 VALUES (@pmIdPeriodo,@pmKeyRegistro,@pmIdEmpleado,@pmItem,@pmIdConcepto,@pmDetalle,@pmCantDevg,@pmCantDed,@pmVrUnitario,@pmVrTotDevg,@pmVrTotDed,@pmUnidad,@pmClaseLiq,@pmVrOrigen,@pmTarifa
,@pmDiasCalc,@pmDiasNov,@pmCodFondo,@pmNPrestamo,@pmNCuota,@pmOrigCargue,@pmVrBaseLiq,@pmVrBasExceso,@pmNitTercero,@pmHoraInicial,@pmHoraFinal,@pmTipoIncLab,@pmVrIngNoSal)
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[paUpNomDetalle] @pmIdPeriodo VARCHAR(8),@pmKeyRegistro VARCHAR(30),@pmItem INT,@pmIdEmpleado VARCHAR(16),@pmIdConcepto VARCHAR(4),@pmDetalle VARCHAR(100),@pmCantDevg DECIMAL(10,4),@pmCantDed DECIMAL(10,4),@pmVrUnitario MONEY,@pmVrTotDevg MONEY
,@pmVrTotDed MONEY,@pmUnidad VARCHAR(10),@pmClaseLiq VARCHAR(10),@pmVrOrigen MONEY,@pmTarifa DECIMAL(10,4),@pmDiasCalc DECIMAL(10,4),@pmDiasNov DECIMAL(10,4),@pmCodFondo VARCHAR(8),@pmNPrestamo INT,@pmNCuota INT,@pmOrigCargue VARCHAR(10),@pmVrBaseLiq MONEY,@pmVrBasExceso MONEY,@pmNitTercero VARCHAR(16)
,@pmHoraInicial DATETIME,@pmHoraFinal DATETIME,@pmTipoIncLab VARCHAR(20),@pmVrIngNoSal MONEY
 AS 
 UPDATE Trn_NomDetalle SET IdEmpleado=@pmIdEmpleado,IdConcepto=@pmIdConcepto,Detalle=@pmDetalle,CantDevg=@pmCantDevg,CantDed=@pmCantDed,VrUnitario=@pmVrUnitario,VrTotDevg=@pmVrTotDevg,VrTotDed=@pmVrTotDed,Unidad=@pmUnidad,ClaseLiq=@pmClaseLiq
,VrOrigen=@pmVrOrigen,Tarifa=@pmTarifa,DiasCalc=@pmDiasCalc,DiasNov=@pmDiasNov,CodFondo=@pmCodFondo,NPrestamo=@pmNPrestamo,NCuota=@pmNCuota,OrigCargue=@pmOrigCargue,VrBaseLiq=@pmVrBaseLiq,VrBasExceso=@pmVrBasExceso,NitTercero=@pmNitTercero,HoraInicial=@pmHoraInicial,HoraFinal=@pmHoraFinal,TipoIncLab=@pmTipoIncLab,VrIngNoSal=@pmVrIngNoSal
 WHERE IdPeriodo=@pmIdPeriodo AND KeyRegistro=@pmKeyRegistro AND Item=@pmItem
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryNomDetalle] @pmIdPeriodo VARCHAR(8),@pmKeyRegistro VARCHAR(30),@pmItem INT
 AS 
 SELECT IdPeriodo,KeyRegistro,Item,IdEmpleado,IdConcepto,Detalle,CantDevg,CantDed,VrUnitario,VrTotDevg,VrTotDed,Unidad,ClaseLiq,VrOrigen,Tarifa,DiasCalc
,DiasNov,CodFondo,NPrestamo,NCuota,OrigCargue,VrBaseLiq,VrBasExceso,NitTercero,HoraInicial,HoraFinal,TipoIncLab,VrIngNoSal
 FROM Trn_NomDetalle
 WHERE IdPeriodo=@pmIdPeriodo AND KeyRegistro=@pmKeyRegistro 
  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].[paQryNomDetallePer] @pmIdPeriodo VARCHAR(8),@pmKeyRegistro VARCHAR(30)=Null,@pmIdNom VARCHAR(4)=Null
 AS 
 SELECT D.IdEmpleado AS CedEmpleado,Apellidos,Nombres,Item,D.IdConcepto AS IdConcept,Detalle,ClaseCon,CantDevg,VrUnitario,VrTotDevg,CantDed,VrTotDed
,D.Unidad AS Undad,VrOrigen,D.Tarifa AS Tarfa,VrBaseLiq,DiasCalc,DiasNov,DiasCalc-DiasNov AS DiasNeto,ClaseLiq,NPrestamo,NCuota,OrigCargue,CodFondo,D.KeyRegistro AS Kyregistro
,D.TipoIncLab,D.HoraInicial,D.HoraFinal,D.VrIngNoSal
 FROM Trn_NomDetalle AS D INNER JOIN Empleados AS E ON D.IdEmpleado=E.IdEmpleado
 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 LIKE ISNULL(@pmKeyRegistro,'%') AND IdNom like ISNULL(@pmIdNom,'%')
 ORDER BY Apellidos,Nombres,Item
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[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,tmBasExceso,tmNitTercero,tmHoraInicial,tmHoraFinal,tmTipoInc,tmVrIngNoSal)
 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,VrBasExceso,NitTercero,HoraInicial,HoraFinal,TipoIncLab,VrIngNoSal
 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 ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[paQryNomDetalleResN] @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,@pmEsRetiro BIT=Null
,@pmInactivo BIT=Null,@pmEmp_Inactivo BIT=Null,@pmIdConcepto VARCHAR(4)=Null,@pmClaseLiq VARCHAR(10)=Null,@pmClaseCon VARCHAR(10)=Null
 AS 
SELECT N.IdEmpleado AS IdEmple,N.NContrato AS NumContrato,FecInicial,FecFinal,N.IdPeriodo AS IdPerdo,IdArea,N.IdDep AS nDepe,N.IdInstala AS nInstala
,N.IdCCosto AS CodCcosto,N.IdSubCos AS CodSubcentro,N.IdNom AS CdNom,N.VrSalario AS vSalario,VrATransMes,N.pVehiculo AS PlacaVeh,TipCom,Comprobante,IdCiaCom
,N.Observacion AS Observ,EsRetiro,NLiqRet,TipoLiq
--Detales
,D.IdConcepto AS CdConcepto,Item,Detalle,CantDevg,CantDed,VrUnitario,VrTotDevg,VrTotDed,D.Unidad AS Und,ClaseLiq,VrOrigen,D.Tarifa AS Tarif,DiasCalc,DiasNov,CodFondo
,NPrestamo,NCuota,VrBaseLiq,HoraInicial,HoraFinal,TipoIncLab,VrIngNoSal
 FROM Trn_Nomina AS N INNER JOIN Empleados AS E ON N.IdEmpleado=E.IdEmpleado
 INNER JOIN Emp_Contrato AS C ON N.NContrato=C.NContrato
 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
 WHERE FecInicial>=@pmFecIni  AND FecFinal<=@pmFecFin AND N.IdEmpleado LIKE ISNULL(@pmIdEmpleado,'%')  AND  N.IdPeriodo LIKE ISNULL(@pmIdPeriodo,'%')
 AND IdArea LIKE ISNULL(@pmIdArea,'%') AND N.IdDep LIKE ISNULL(@pmIdDep,'%')
 AND N.IdNom LIKE ISNULL(@pmIdNom,'%')  AND N.IdInstala LIKE ISNULL(@pmIdInstala,'%')
 AND N.IdCCosto LIKE ISNULL(@pmIdCCosto,'%')
 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 (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))
 ORDER BY  FecInicial, N.IdEmpleado,Item
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
,D.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,D.VrIngNoSal
--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,'',0
,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 OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[paQrytm_Nomina_Cr] @pmtmNumero VARCHAR(5),@pmtmIdEmpleado VARCHAR(16)=Null
 AS 
 SELECT tmIdEmpleado,Apellidos,Nombres,tmItem,tmIdConcepto,tmDetalle,Concepto,tmClaseCon,tmCantDevg,tmUnidad,tmVrUnitario,tmVrTotDevg,tmCantDed,tmVrUnitario AS VrUndDed
,tmVrTotDed,tmNPrestamo,tmNCuota,tmVrOrigen,tmTarifa,tmDiasCalc,tmDiasNov,tmDiasCalc-tmDiasNov AS Dias,tmCodFondo,FD.Fondo AS tm_Fondo,tmVrBaseLiq,tmFecIni,tmFecFin,tmIdPeriodo
,tmFecUltLiq,tmFecIniPmd,tmFecFinPmd,tmLogPmdio,tmClaseLiq,tmOrigCargue,tmBasExceso,tmNitTercero,tmHoraInicial,tmHoraFinal,tmTipoInc,tmVrIngNoSal
--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,CC.Fondo AS CajaComp,FecNacmto,EsCondtor,Licencia,CatLicencia,VigLicencia,E.IdLugar AS CodLugar,LugarLic,TallaCam,TallaPan,TallaZap,E.Inactivo AS Emp_Inactvo
--datos del contrato
,E.NContrato AS NumContrato,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,VrSalario,VrAuxTrans,SalMinimo,SalIntegral,FPnomina,ATFinMes
,SegFinMes,DchDotacion,DchCafeteria,ModoRet,C.IdEstado AS IdEstdo,Estado,C.Inactivo AS Con_inactivo,C.IdJornada AS CodJnada,Jornada,C.IdUsuario AS Con_IdUsuari,Usuario
,pVehiculo,NumVeh,TipoAdmon,VehPropio
 FROM tm_Nomina AS N INNER JOIN Empleados AS E ON N.tmIdEmpleado=E.IdEmpleado
 INNER JOIN NomConceptos AS CN ON N.tmIdConcepto=CN.IdConcepto
 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 Localidades AS L ON E.IdLugarCed=L.IdLocal
 INNER JOIN  Emp_Contrato AS C ON E.NContrato=C.NContrato  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 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 EN ON C.IdEstado=EN.IdEstado
 INNER JOIN adm_Usuarios AS U ON C.IdUsuario=U.IdUsuario  INNER JOIN ClaseRie AS CR ON C.IdClase=CR.IdClase 
 INNER JOIN Jornadas AS J ON C.IdJornada=J.IdJornada  LEFT JOIN Fondos AS FD ON N.tmCodFondo=FD.IdFondo
 LEFT JOIN NomVehiculos AS VH ON E.pVehiculo=VH.IdVehiculo
 WHERE tmNumero=@pmtmNumero  AND tmIdEmpleado like ISNULL(@pmtmIdEmpleado,'%')
 ORDER BY Apellidos,Nombres,tmItem
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[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,tmBasExceso,tmNitTercero,tmHoraInicial,tmHoraFinal,tmTipoInc,tmVrIngNoSal)
 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,0,'',Null,Null,'',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 ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[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,@pmtmBasExceso MONEY,@pmtmNitTercero VARCHAR(16),@pmtmHoraInicial DATETIME,@pmtmHoraFinal DATETIME,@pmtmTipoInc VARCHAR(20),@pmtmVrIngNoSal 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,tmBasExceso,tmNitTercero,tmHoraInicial,tmHoraFinal,tmTipoInc,tmVrIngNoSal)
 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,@pmtmBasExceso,@pmtmNitTercero,@pmtmHoraInicial,@pmtmHoraFinal,@pmtmTipoInc,@pmtmVrIngNoSal)
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[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,@pmtmBasExceso MONEY,@pmtmNitTercero VARCHAR(16),@pmtmHoraInicial DATETIME,@pmtmHoraFinal DATETIME,@pmtmTipoInc VARCHAR(20),@pmtmVrIngNoSal 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,tmBasExceso=@pmtmBasExceso,tmNitTercero=@pmtmNitTercero
,tmHoraInicial=@pmtmHoraInicial,tmHoraFinal=@pmtmHoraFinal,tmTipoInc=@pmtmTipoInc,tmVrIngNoSal=@pmtmVrIngNoSal
 WHERE tmNumero=@pmtmNumero AND tmIdEmpleado=@pmtmIdEmpleado AND tmItem=@pmtmItem
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[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,tmBasExceso,tmNitTercero,tmHoraInicial,tmHoraFinal,tmTipoInc,tmVrIngNoSal
 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 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,tmVrIngNoSal)
 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,'',0
 FROM Trn_NomVacDet  AS D INNER JOIN NomConceptos AS C ON D.IdConcepto=C.IdConcepto
 WHERE IdEmpleado=@pmIdEmpleado AND Numero=@pmNumero
GO