if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInsNomNov]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paInsNomNov]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInsPresDetalle]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paInsPresDetalle]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInstm_DetCue_Pto]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paInstm_DetCue_Pto]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInstm_NiifCom]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paInstm_NiifCom]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryNomNov]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryNomNov]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryNomNov_Cr]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryNomNov_Cr]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryNomNovCon]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryNomNovCon]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryPresupuesto_Cr]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryPresupuesto_Cr]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryPresupuestoPer]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryPresupuestoPer]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQrytm_NiifCom]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQrytm_NiifCom]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paUpNomNov]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paUpNomNov]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInsMudOrdenConc]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paInsMudOrdenConc]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryMudInventario_Cr]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryMudInventario_Cr]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryMudOrdenOper_Cr]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryMudOrdenOper_Cr]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryMudOrdenOperRel]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryMudOrdenOperRel]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryMudOrdenServ_Cr]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryMudOrdenServ_Cr]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryMudOrdenServRel]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryMudOrdenServRel]
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[paQryNomNov_Cr] @pmIdPeriodo VARCHAR(8),@pmIdEmpleado VARCHAR(16)=Null
 AS 
 SELECT IdPeriodo,N.IdEmpleado AS IdEmpledo,Apellidos,Nombres,E.NContrato AS NumContrato,Item,N.IdConcepto AS IdConcept,Concepto,Detalle,Cantidad,VrUnitario,VrTotal,N.Tarifa AS TrfaBase,VrOrigen,ClaseCon,Unidad,TimeSys
,N.FecUpdate AS FecUpd,N.IdUsuario AS IdUsuari,Usuario,OrigCargue,Num_Contrato,Nit_Tercero,T.RazonSocial AS NomTercero
--Datos empleado
,E.Codigo,E.IdLugarCed,Localidad,E.Direccion AS Dirccion,E.Telefono,E.TelMovil,E.e_mail,E.IdProf AS CodProf,Profesion,E.IdClase AS CodClaCta,ClaseCuenta,NumCuenta,E.IdBanco AS CodBanco,Banco,IdFonPen,FP.Fondo AS FondoPen
,IdFonEps,FS.Fondo AS FondoEps,IdFonArp,FR.Fondo AS FondoArp,IdCajaCom,CC.Fondo AS CajaComp,EsCondtor,FecNacmto,Licencia,CatLicencia,VigLicencia,E.IdLugar AS CodLugar,LugarLic,TallaCam,TallaPan,TallaZap,E.Inactivo AS Emp_Inactvo
--datos del contrato
,C.IdTipCon AS CodTipCon,TipoContrato,FecIngreso,FecRetiro,Indefinido,C.IdCargo AS CodCargo,Cargo,C.IdArea AS CodArea,Area,C.IdDep AS CodDep,Dependencia,C.IdInstala AS CodInst,Instlacion,C.IdCCosto AS CodCcto,CCosto
,C.IdSubCos AS CodSubcen,SubCosto,C.IdNom AS CodNom,TipoNomina,C.TipoLiquida AS TipLiq,Asistencia,NoDevenga,C.IdClase AS CodClaRie,ClaseRiesgo,CR.Tarifa AS TarifaRie,VrSalario,VrAuxTrans,SalMinimo,SalIntegral,FPnomina,ATFinMes
,SegFinMes,DchDotacion,DchCafeteria,C.IdEstado AS IdEstdo,Estado,C.Inactivo AS Con_inactivo,C.IdJornada AS CodJnada,Jornada
 FROM Trn_NomNov AS N INNER JOIN Empleados AS E ON N.IdEmpleado=E.IdEmpleado  INNER JOIN  NomConceptos AS CN ON N.IdConcepto=CN.IdConcepto
 INNER JOIN adm_Usuarios AS U ON N.IdUsuario=U.IdUsuario 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
 LEFT JOIN  Emp_Contrato AS C ON E.NContrato=C.NContrato  LEFT JOIN TiposCon AS TC ON C.IdTipCon=TC.IdTipCon
 LEFT JOIN Dependencias AS DP ON C.IdDep=DP.IdDep  LEFT JOIN Instalaciones AS I ON C.IdInstala=I.IdInstala
 LEFT JOIN CentroCosto AS CO ON C.IdCCosto=CO.IdCCosto  LEFT JOIN SubCentros AS SC ON C.IdSubCos=SC.IdSubCos
 LEFT JOIN TiposNom AS TN ON C.IdNom=TN.IdNom LEFT JOIN EstadoDoc AS EN ON C.IdEstado=EN.IdEstado 
 LEFT JOIN Cargos AS CG ON C.IdCargo=CG.IdCargo LEFT JOIN Areas AS AR ON C.IdArea=AR.IdArea
 LEFT JOIN ClaseRie AS CR ON C.IdClase=CR.IdClase LEFT JOIN Jornadas AS J ON C.IdJornada=J.IdJornada
 LEFT JOIN Terceros AS T ON N.Nit_Tercero=T.IdTercero
 WHERE IdPeriodo=@pmIdPeriodo AND N.IdEmpleado LIKE ISNULL(@pmIdEmpleado,'%')
 ORDER BY  N.IdEmpleado,Item
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[paQryPresupuestoPer]  @pmnAnno INT,@pmnMesIni INT,@pmnMesFin INT,@pmIdCia CHAR(2)=Null
 AS 
 SELECT P.Numero AS NumPsto,P.IdCia AS CdCia,Compania,nAnno,nMes,Observacion,Item,D.IdCuenta AS CdCuenta,NomCuenta,D.IdCCosto AS CdCentro,CCosto
,D.IdSubCos AS CdSubcentro,SubCosto,VrPresupuesto,SaldoAnterior,TotalDebitos,TotalCreditos,Movimiento,CentroCosto,SubCentro,Naturaleza
,D.TipoNiif,D.CodCueNiif,D.VrPrespNiif,D.SAnteriorNiif,D.DebitosNiif,D.CreditosNiif,D.FecUpdate AS FecModifica,CdUsuario,UM.Usuario AS UsuarioModifica
,P.IdEstado AS CdEstado,Estado,TimeSys,P.FecUpdate AS Fec_Update,IdCiaCrea,P.IdUsuario AS Id_Usuario,U.Usuario AS UsuarioCrea
,TipoPsto,DetCentros
 FROM Trn_Presupuesto AS P INNER JOIN Trn_PresDetalle AS D ON P.Numero=D.Numero AND P.IdCia=D.IdCia
 INNER JOIN Companias AS CN ON P.IdCia=CN.IdCia INNER JOIN EstadoDoc AS ED ON P.IdEstado=ED.IdEstado
 INNER JOIN adm_Usuarios AS U ON P.IdUsuario=U.IdUsuario
 LEFT JOIN Puc AS PC ON D.IdCuenta=PC.IdCuenta LEFT JOIN CentroCosto AS CC ON D.IdCCosto=CC.IdCCosto
 LEFT JOIN SubCentros AS SC ON D.IdSubCos=SC.IdSubCos LEFT JOIN adm_Usuarios AS UM ON D.CdUsuario=UM.IdUsuario
 WHERE nAnno=@pmnAnno AND nMes BETWEEN @pmnMesIni AND @pmnMesFin
 AND P.IdCia LIKE ISNULL(@pmIdCia,'%%')
 ORDER BY nMes
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQrytm_NiifCom] @pmtmEst CHAR(2)
 AS 
 SELECT tmIdCia,Compania,BC.tmIdCuenta AS IdCuenta,NomCuenta,tmTipo,tmSaldoAnterior,tmTotalDebitos,tmTotalCreditos
,tmSaldoAnterior+tmTotalDebitos-tmTotalCreditos AS NuevoSaldo,tmSaldoAnterior2,tmTotalDebitos2,tmTotalCreditos2
,tmSaldoAnterior2+tmTotalDebitos2-tmTotalCreditos2 AS NuevoSaldo2,tmVrPresupto
,tmIdTercero,RazonSocial,tmCodAgncia,A.Agencia AS NomAgencia,tmIdVehiculo,tmIdCCosto,CCosto,tmIdSubCos,SubCosto
--información del tercero
,TipoId,Dv,Direccion,T.IdLocal AS CdCiudad,Localidad,L.IdDep AS CdDepto,Departamento,Telefono
,EsAccnista,EsCliente,EsPropietario,CodAgencia,Referencia 
,tmItem,tmNivel1,tmNivel2,tmNivel4,tmNivel6,tmNivel9,tmNivel12,P.Tercero,P.CentroCosto
 FROM tm_NiifCom AS BC INNER JOIN PucNiif AS P ON BC.tmIdCuenta=P.IdCuenta
 INNER JOIN Terceros AS T ON BC.tmIdTercero=T.IdTercero
 INNER JOIN Localidades AS L ON T.IdLocal=L.IdLocal
 INNER JOIN Departamentos AS D ON L.IdDep=D.IdDep
 INNER JOIN CentroCosto AS CC ON BC.tmIdCCosto=CC.IdCCosto
 LEFT JOIN SubCentros AS SC ON BC.tmIdSubCos=SC.IdSubCos
 LEFT JOIN tm_NivCue AS NV ON BC.tmIdCuenta=NV.tmIdCuenta
 LEFT JOIN Agencias AS A ON BC.tmCodAgncia=A.IdAgencia
 LEFT JOIN Companias AS CN ON BC.tmIdCia=CN.IdCia
 WHERE tmEst=@pmtmEst
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[paQryPresupuesto_Cr] @pmNumero INT,@pmIdCia CHAR(2)
 AS 
 SELECT P.Numero AS NumPsto,P.IdCia AS CdCia,Compania,nAnno,nMes,Observacion,Item,D.IdCuenta AS CdCuenta,PC.NomCuenta,D.IdCCosto AS CdCentro,CCosto
,D.IdSubCos AS CdSubcentro,SubCosto,VrPresupuesto,SaldoAnterior,TotalDebitos,TotalCreditos,PC.Movimiento,PC.CentroCosto,PC.SubCentro,PC.Naturaleza
,D.TipoNiif,D.CodCueNiif,D.VrPrespNiif,D.SAnteriorNiif,D.DebitosNiif,D.CreditosNiif,D.FecUpdate AS FecModifica,CdUsuario,UM.Usuario AS UsuarioModifica
,P.IdEstado AS CdEstado,Estado,TimeSys,P.FecUpdate AS Fec_Update,IdCiaCrea,P.IdUsuario AS Id_Usuario,U.Usuario AS UsuarioCrea
,TipoPsto,DetCentros,PN.NomCuenta AS NiifNomCuenta
 FROM Trn_Presupuesto AS P INNER JOIN Trn_PresDetalle AS D ON P.Numero=D.Numero AND P.IdCia=D.IdCia
 INNER JOIN Companias AS CN ON P.IdCia=CN.IdCia INNER JOIN EstadoDoc AS ED ON P.IdEstado=ED.IdEstado
 INNER JOIN adm_Usuarios AS U ON P.IdUsuario=U.IdUsuario
 LEFT JOIN Puc AS PC ON D.IdCuenta=PC.IdCuenta LEFT JOIN CentroCosto AS CC ON D.IdCCosto=CC.IdCCosto
 LEFT JOIN SubCentros AS SC ON D.IdSubCos=SC.IdSubCos LEFT JOIN adm_Usuarios AS UM ON D.CdUsuario=UM.IdUsuario
 LEFT JOIN PucNiif AS PN ON D.CodCueNiif=PN.IdCuenta
 WHERE P.Numero=@pmNumero AND P.IdCia=@pmIdCia
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paUpNomNov] @pmIdPeriodo VARCHAR(8),@pmIdEmpleado VARCHAR(16),@pmItem INT,@pmIdConcepto VARCHAR(4),@pmDetalle VARCHAR(100),@pmCantidad DECIMAL(10,4),@pmVrUnitario MONEY
,@pmVrTotal MONEY,@pmVrOrigen MONEY,@pmTarifa DECIMAL(10,4),@pmOrigCargue VARCHAR(10),@pmNumDocRef VARCHAR(20),@pmNum_Contrato INT,@pmNit_Tercero VARCHAR(16),@pmFecUpdate SMALLDATETIME
 AS 
 UPDATE Trn_NomNov SET IdConcepto=@pmIdConcepto,Detalle=@pmDetalle,Cantidad=@pmCantidad,VrUnitario=@pmVrUnitario,VrTotal=@pmVrTotal,VrOrigen=@pmVrOrigen
,Tarifa=@pmTarifa,OrigCargue=@pmOrigCargue,NumDocRef=@pmNumDocRef,Num_Contrato=@pmNum_Contrato,FecUpdate=@pmFecUpdate,Nit_Tercero=@pmNit_Tercero
 WHERE IdPeriodo=@pmIdPeriodo AND IdEmpleado=@pmIdEmpleado AND Item=@pmItem
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryNomNov] @pmIdPeriodo VARCHAR(8),@pmIdEmpleado VARCHAR(16),@pmItem INT
 AS 
 SELECT Item,IdConcepto,Detalle,Cantidad,VrUnitario,VrTotal,Tarifa,VrOrigen,IdPeriodo,IdEmpleado
,TimeSys,FecUpdate,IdUsuario,OrigCargue,NumDocRef,Num_Contrato,Nit_Tercero
 FROM Trn_NomNov
 WHERE IdPeriodo=@pmIdPeriodo AND IdEmpleado=@pmIdEmpleado
 AND (Item>=ISNULL(@pmItem,0)  AND Item<=ISNULL(@pmItem,2147483647))
 ORDER BY Item
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paInsNomNov] @pmIdPeriodo VARCHAR(8),@pmIdEmpleado VARCHAR(16),@pmItem INT,@pmIdConcepto VARCHAR(4),@pmDetalle VARCHAR(100),@pmCantidad DECIMAL(10,4),@pmVrUnitario MONEY
,@pmVrTotal MONEY,@pmVrOrigen MONEY,@pmTarifa DECIMAL(10,4),@pmOrigCargue VARCHAR(10),@pmNumDocRef VARCHAR(20),@pmNum_Contrato INT,@pmNit_Tercero VARCHAR(16),@pmTimeSys SMALLDATETIME,@pmIdUsuario VARCHAR(11)
 AS 
 INSERT INTO Trn_NomNov (IdPeriodo,IdEmpleado,Item,IdConcepto,Detalle,Cantidad,VrUnitario,VrTotal,VrOrigen,Tarifa,OrigCargue,TimeSys,IdUsuario,NumDocRef,Num_Contrato,Nit_Tercero)
 VALUES (@pmIdPeriodo,@pmIdEmpleado,@pmItem,@pmIdConcepto,@pmDetalle,@pmCantidad,@pmVrUnitario,@pmVrTotal,@pmVrOrigen,@pmTarifa,@pmOrigCargue, @pmTimeSys,@pmIdUsuario,@pmNumDocRef,@pmNum_Contrato,@pmNit_Tercero)
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[paQryNomNovCon] @pmIdPeriodo VARCHAR(8),@pmIdEmpleado VARCHAR(16) 
,@pmIdConcepto VARCHAR(4),@pmOrigCargue VARCHAR(10)=Null
 AS 
 SELECT Item,IdConcepto,Detalle,Cantidad,VrUnitario,VrTotal,Tarifa,VrOrigen,IdPeriodo,IdEmpleado
,TimeSys,FecUpdate,IdUsuario,OrigCargue,Num_Contrato,Nit_Tercero
 FROM Trn_NomNov
 WHERE IdPeriodo=@pmIdPeriodo AND IdEmpleado=@pmIdEmpleado
 AND IdConcepto=@pmIdConcepto
 AND OrigCargue LIKE ISNULL(@pmOrigCargue,'%')
 ORDER BY Item
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paInstm_DetCue_Pto] @pmtmNumero VARCHAR(5),@pmNumero INT,@pmIdCia CHAR(2)
 AS 
 INSERT INTO tm_DetCue (tmNumero,tmItem,tmConcepto,tmIdCuenta,tmValor,tmDetalle,tmIdTercero,tmCodAgncia,tmCdCCosto,tmCdSubCos
,tmpVehiculo,tmVrBase,tmTipFac,tmFactura,tmIdCiaFac,tmItemFac,tmFecVence,tmCheque,tmCiuOrigen,tmReferencia,tmTarifa,tmTipoTarif,tmCodConc)
 SELECT @pmtmNumero,Item,ISNULL(TipoNiif,'0'),IdCuenta,VrPresupuesto,'PRESUPUESTO','0','0',IdCCosto,IdSubCos
,'0',VrPrespNiif,'','','00',0,FecUpdate,ISNULL(CodCueNiif,''),'',CdUsuario,0,'$',''
 FROM Trn_PresDetalle
 WHERE Numero=@pmNumero AND IdCia=@pmIdCia
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[paInsPresDetalle] @pmNumero INT,@pmIdCia CHAR(2),@pmItem INT,@pmIdCuenta VARCHAR(16),@pmIdCCosto VARCHAR(16)
,@pmIdSubCos VARCHAR(16),@pmVrPresupuesto MONEY,@pmSaldoAnterior MONEY,@pmTotalDebitos MONEY,@pmTotalCreditos MONEY,@pmTipoNiif VARCHAR(10)
,@pmCodCueNiif VARCHAR(16),@pmVrPrespNiif MONEY,@pmSAnteriorNiif MONEY,@pmDebitosNiif MONEY,@pmCreditosNiif MONEY,@pmCdUsuario VARCHAR(11),@pmFecUpdate SMALLDATETIME
 AS 
 INSERT INTO Trn_PresDetalle (Numero,IdCia,Item,IdCuenta,IdCCosto,IdSubCos,VrPresupuesto,SaldoAnterior,TotalDebitos,TotalCreditos,TipoNiif,CodCueNiif,VrPrespNiif,SAnteriorNiif,DebitosNiif,CreditosNiif,CdUsuario,FecUpdate)
 VALUES (@pmNumero,@pmIdCia,@pmItem,@pmIdCuenta,@pmIdCCosto,@pmIdSubCos,@pmVrPresupuesto,@pmSaldoAnterior,@pmTotalDebitos,@pmTotalCreditos,@pmTipoNiif,@pmCodCueNiif,@pmVrPrespNiif,@pmSAnteriorNiif,@pmDebitosNiif,@pmCreditosNiif,@pmCdUsuario,@pmFecUpdate)
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paInstm_NiifCom] @pmtmEst CHAR(2),@pmtmIdCuenta VARCHAR(16),@pmtmIdCia CHAR(2),@pmtmItem INT,@pmtmIdTercero VARCHAR(16),@pmtmIdVehiculo VARCHAR(10),@pmtmIdCCosto VARCHAR(16)
,@pmtmIdSubCos VARCHAR(16),@pmtmCodAgncia VARCHAR(16),@pmtmTipo VARCHAR(4),@pmtmSaldoAnterior MONEY,@pmtmTotalDebitos MONEY,@pmtmTotalCreditos MONEY,@pmtmNuevoSaldo MONEY
,@pmtmSaldoAnterior2 MONEY,@pmtmTotalDebitos2 MONEY,@pmtmTotalCreditos2 MONEY,@pmtmNuevoSaldo2 MONEY,@pmtmVrPresupto MONEY
 AS 
 INSERT INTO tm_NiifCom (tmEst,tmIdCuenta,tmIdCia,tmItem,tmIdTercero,tmIdVehiculo,tmIdCCosto,tmIdSubCos,tmCodAgncia,tmTipo,tmSaldoAnterior,tmTotalDebitos,tmTotalCreditos
,tmNuevoSaldo,tmSaldoAnterior2,tmTotalDebitos2,tmTotalCreditos2,tmNuevoSaldo2,tmVrPresupto)
 VALUES (@pmtmEst,@pmtmIdCuenta,@pmtmIdCia,@pmtmItem,@pmtmIdTercero,@pmtmIdVehiculo,@pmtmIdCCosto,@pmtmIdSubCos,@pmtmCodAgncia,@pmtmTipo,@pmtmSaldoAnterior,@pmtmTotalDebitos
,@pmtmTotalCreditos,@pmtmNuevoSaldo,@pmtmSaldoAnterior2,@pmtmTotalDebitos2,@pmtmTotalCreditos2,@pmtmNuevoSaldo2,@pmtmVrPresupto)
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryMudOrdenServRel] @pmTipDoc VARCHAR(3),@pmFechaIni SMALLDATETIME,@pmFechaFin SMALLDATETIME,@pmIdCia CHAR(2)=Null
 AS 
 SELECT O.TipDoc,TipoDoc,O.NumOrden,O.IdCia AS CdCia,Compania,O.Fecha,O.FecDespacho,O.IdTipoServ AS CdTipoServ,TipoServicio,O.IdCliente,T.RazonSocial AS NomCliente,O.IdAgencia AS CdAgencia,Agencia
,O.IdOrigen,LO.Localidad AS CiuOrigen,LO.IdDep AS CdDepOrig,DPO.Departamento AS DptoOrigen,O.IdDestino,LD.Localidad AS CiuDestino,LD.IdDep AS CdDepDest,DPD.Departamento AS DptoDestino
,DirCargue,TipoInmRem,PisoCargue,TelsRemite,CelRemite,DirDescargue,TipoInmDest,PisoDescargue,TelsDest,CelDest,O.VrServicio,O.VrEmpaque,O.VrBodega,O.VrOtros,O.VrDescuento,O.VrImpuesto,O.VrNeto,O.VrDeclarado,O.VrSeguro
,O.IdVend,VN.RazonSocial AS NomVendedor,O.NomContacto,O.TelsContacto,O.emlContacto,O.ContactoDest,O.emlContDest,O.Servicios,O.FormaPago,O.Referencia,O.TipInv,O.NumInvent,O.IdCiaInv,O.TipRem,O.NumRemesa,O.IdCiaRem
,O.EstFactura,O.TipCausac,O.Causacion,O.CdCiaCau,O.EstCumplido,O.FechaCump,O.TipoRuta,O.Volumen,EstadoBod,O.CdBodega,B.Bodega AS NomBodega,FechaIngBod,FechaRetBod,O.Anulado,O.FecDev,O.Observacion AS Observ,O.IdEstado AS CdEstado,Estado
--Detalles de orden
,D.Item,D.IdConcepto AS CdConcepto,D.Descripcion,CD.Concepto,D.ValorTotal,D.TarifaIva,D.TipoConc,D.TipoRubro,D.CdCuenta,D.NitTercero,NT.RazonSocial AS NomTercero
,D.Referencia AS DetReferencia,D.NumDocRef,D.TipoReg,D.TarifSeguro,D.CodTarSeg,D.PlacaVehic,D.TarifaPago,D.NumDocFac,D.IndFactItem,D.FechaReg,D.CdUsuario AS DetCdUsuario,UC.Usuario AS DetUsuario,TS.Transporte,TS.Bodega,TS.Empaque,TS.Personal
--datos de facturas
,FC.FacNumero,FC.FacFecha,ISNULL(FC.FacValor,0) AS FacVrTotal,ISNULL(FC.FacCosto,0) AS FacVrCosto,ISNULL(FC.FacImpuesto,0) AS FacVrIva,ISNULL(FC.FacSeguro,0) AS FacVrSeguro
--datos del tercero
,T.Codigo AS TercCod,T.TipoId,T.Dv,T.Direccion,T.IdLocal AS TercCdLocal,L.Localidad AS TercLocalidad,T.Telefono AS TercTelefono,T.TelMovil AS TercTerCelular,T.e_mail AS TercEmail
,CLI.IdSzona AS CdSubzona,Subzona,SZ.IdZona AS CdZona,Zona,CLI.IdGrupo AS CdGrupo,GrupoClie,CLI.IdPlazo AS CdPlazo,Plazo
,V.NumVeh,V.TipoAfil,V.IdTipoVeh AS CdTipoVeh,TipoVehiculo,V.IdMarca AS CdMarca,Marca,V.IdColor AS CdColor,NomColor,V.Modelo,V.IdPoseedor AS NitPoseedor,NP.RazonSocial AS Poseedor
,V.IdConductor AS CedConductor,CDT.RazonSocial AS Conductor,V.NumSoat,V.VigSoat
,O.OrigenAdd,O.TimeSys AS FechaCrea,O.FecUpdate,O.IdCiaCrea,O.IdUsuario AS CdUsuario,U.Usuario
 FROM Trn_MudOrdenServ AS O INNER JOIN Companias AS CN ON O.IdCia=CN.IdCia INNER JOIN EstadoDoc AS ED ON O.IdEstado=ED.IdEstado
 INNER JOIN Terceros AS T ON O.IdCliente=T.IdTercero INNER JOIN TercCliente AS CLI ON O.IdCliente=CLI.IdClie
 INNER JOIN Terceros AS VN ON O.IdVend=VN.IdTercero INNER JOIN MudServicios AS TS ON O.IdTipoServ=TS.IdTipoServ
 INNER JOIN adm_Usuarios AS U ON O.IdUsuario=U.IdUsuario INNER JOIN Sys_TiposDoc AS TD ON O.TipDoc=TD.IdDoc
 INNER JOIN Localidades AS LO ON O.IdOrigen=LO.IdLocal INNER JOIN Localidades AS LD ON O.IdDestino=LD.IdLocal
 INNER JOIN Departamentos AS DPO ON LO.IdDep=DPO.IdDep INNER JOIN Departamentos AS DPD ON LD.IdDep=DPD.IdDep
 INNER JOIN Localidades AS L ON T.IdLocal=L.IdLocal
 INNER JOIN Trn_MudOrdenConc AS D ON O.TipDoc=D.TipDoc AND O.NumOrden=D.NumOrden AND O.IdCia=D.IdCia
 INNER JOIN ConcDiversos AS CD ON D.IdConcepto=CD.IdConcepto 
 INNER JOIN Subzonas AS SZ ON CLI.IdSzona=SZ.IdSzona INNER JOIN Zonas AS Z ON SZ.IdZona=Z.IdZona 
 LEFT JOIN GruposCli AS GC ON CLI.IdGrupo=GC.IdGrupo LEFT JOIN Plazos AS PZ ON CLI.IdPlazo=PZ.IdPlazo
 LEFT JOIN Terceros AS NT ON D.NitTercero=NT.IdTercero LEFT JOIN Agencias AS A ON O.IdAgencia=A.IdAgencia 
 LEFT JOIN AlmBodegas AS B ON O.CdBodega=B.IdBodega
 LEFT JOIN Vehiculos AS V ON D.PlacaVehic=V.IdVehiculo LEFT JOIN TiposVeh AS TV ON V.IdTipoVeh=TV.IdTipoVeh
 LEFT JOIN Marcas AS M ON V.IdMarca=M.IdMarca LEFT JOIN TiposCol AS CLR ON V.IdColor=CLR.IdColor
 LEFT JOIN Terceros AS NP ON V.IdPoseedor=NP.IdTercero LEFT JOIN Terceros AS CDT ON V.IdConductor=CDT.IdTercero
 LEFT JOIN adm_Usuarios AS UC ON D.CdUsuario=UC.IdUsuario
 --Facturas item por item
 LEFT JOIN (SELECT TipRem AS FacTipOrd,Remesa AS FacNumOrden,IdCiaRem AS FacIdCiaOrd,ItemRem AS FacItemOrd,MAX(FecRemesa) AS FacFecha
,SUM(CASE WHEN SUBSTRING(TipDoc,1,2)='DF' THEN (Cantidad*VrUnitario)*-1 ELSE Cantidad*VrUnitario END) AS FacValor
,SUM(CASE WHEN SUBSTRING(TipDoc,1,2)='DF' THEN (Cantidad*VrCosto)*-1 ELSE Cantidad*VrCosto END) AS FacCosto
,SUM(CASE WHEN SUBSTRING(TipDoc,1,2)='DF' THEN VrImpuesto*-1 ELSE VrImpuesto END) AS FacImpuesto
,SUM(CASE WHEN SUBSTRING(TipDoc,1,2)='DF' THEN VrSeguroRem*-1 ELSE VrSeguroRem END) AS FacSeguro
,SUM(CASE WHEN SUBSTRING(TipDoc,1,2)='FC' THEN VrDeclMcia ELSE 0 END) AS FacDeclarado
,MAX(CASE WHEN SUBSTRING(TipDoc,1,2)='FC' THEN Factura ELSE 0 END) AS FacNumero
 FROM Trn_TraFacRemesas WHERE TipRem='OSM' GROUP BY TipRem,Remesa,IdCiaRem,ItemRem) AS FC ON D.TipDoc=FC.FacTipOrd AND D.NumOrden=FC.FacNumOrden AND D.IdCia=FC.FacIdCiaOrd AND D.Item=FC.FacItemOrd
 WHERE O.TipDoc=@pmTipDoc AND O.Fecha BETWEEN @pmFechaIni AND @pmFechaFin AND O.IdCia LIKE ISNULL(@pmIdCia,'%%')
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryMudOrdenOperRel] @pmTipDoc VARCHAR(3),@pmFechaIni SMALLDATETIME,@pmFechaFin SMALLDATETIME,@pmIdCia CHAR(2)=Null
AS 
SELECT O.TipDoc,O.NumOrden,O.IdCia,O.FecDespacho,O.IdCliente,D.Item,D.IdOperario,NomOperario,D.IdTipoOper,TipoOperario
,D.CdConcepto,C.Concepto,C.IdCuenta,C.TipoConc,D.TarifaCargo,D.PlacaVehic,D.Descripcion,D.TipoReg,D.FechaReg,D.CdUsuario,Usuario
,V.NumVeh,V.IdMarca AS CdMarca,M.Marca,V.IdTipoVeh AS CdTipoVeh,TipoVehiculo,V.Modelo,V.IdColor AS CdColor,NomColor,V.Config
,V.IdPoseedor AS CdPoseedor,T.RazonSocial AS Poseedor,V.TipoAfil
FROM Trn_MudOrdenServ AS O INNER JOIN Trn_MudOrdenOper AS D ON O.TipDoc=D.TipDoc AND O.NumOrden=D.NumOrden AND O.IdCia=D.IdCia
INNER JOIN TiposOperarios AS TP ON D.IdTipoOper=TP.IdOper LEFT JOIN TraConcCausac AS C ON D.CdConcepto=C.IdConcepto
LEFT JOIN Vehiculos AS V ON D.PlacaVehic=V.IdVehiculo
LEFT JOIN Marcas AS M ON V.IdMarca=M.IdMarca LEFT JOIN TiposCol AS CL ON V.IdColor=CL.IdColor
LEFT JOIN TiposVeh AS TV ON V.IdTipoVeh=TV.IdTipoVeh LEFT JOIN Terceros AS T ON V.IdPoseedor=T.IdTercero
LEFT JOIN adm_Usuarios AS U ON D.CdUsuario=U.IdUsuario 
WHERE O.TipDoc=@pmTipDoc AND O.Fecha BETWEEN @pmFechaIni AND @pmFechaFin AND O.IdCia LIKE ISNULL(@pmIdCia,'%%')
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryMudOrdenServ_Cr] @pmTipDoc VARCHAR(3),@pmNumOrdenIni INT,@pmNumOrdenFin INT,@pmIdCia CHAR(2)
 AS 
 SELECT O.TipDoc,TipoDoc,O.NumOrden,O.IdCia AS CdCia,Compania,O.Fecha,O.FecDespacho,O.IdTipoServ AS CdTipoServ,TipoServicio,O.IdCliente,T.RazonSocial AS NomCliente,O.IdAgencia AS CdAgencia,Agencia
,O.IdOrigen,LO.Localidad AS CiuOrigen,O.IdDestino,LD.Localidad AS CiuDestino,DirCargue,TipoInmRem,PisoCargue,TelsRemite,CelRemite,DirDescargue,TipoInmDest,PisoDescargue,TelsDest,CelDest
,O.VrServicio,O.VrEmpaque,O.VrBodega,O.VrOtros,O.VrDescuento,O.VrImpuesto,O.VrNeto,O.VrDeclarado,O.VrSeguro,O.IdVend,VN.RazonSocial AS NomVendedor
,O.NomContacto,O.TelsContacto,O.emlContacto,O.ContactoDest,O.emlContDest,O.Servicios,O.FormaPago,O.Referencia,O.TipInv,O.NumInvent,O.IdCiaInv,O.TipRem,O.NumRemesa,O.IdCiaRem
,O.EstFactura,O.TipFact,O.NumFactura,O.CdCiaFact,O.TipCausac,O.Causacion,O.CdCiaCau,EstCumplido,FechaCump,TipoRuta,O.Volumen,EstadoBod,O.CdBodega,FechaIngBod,FechaRetBod,O.Anulado,O.FecDev,O.Observacion AS Observ,O.IdEstado AS CdEstado,Estado
,D.Item,D.IdConcepto AS CdConcepto,D.Descripcion,CD.Concepto,D.ValorTotal,D.TarifaIva,D.TipoConc,D.TipoRubro,D.CdCuenta,D.NitTercero,NT.RazonSocial AS NomTercero
,D.Referencia AS DetReferencia,D.NumDocRef,D.NumDocFac,D.IndFactItem,D.TipoReg,D.TarifSeguro,D.CodTarSeg,D.PlacaVehic,D.TarifaPago,D.FechaReg,D.CdUsuario AS DetCdUsuario,UC.Usuario AS DetUsuario,TS.Transporte,TS.Bodega,TS.Empaque,TS.Personal
,O.OrigenAdd,O.TimeSys AS FechaCrea,O.FecUpdate,O.IdCiaCrea,O.IdUsuario AS CdUsuario,U.Usuario
,T.Codigo AS TercCod,T.TipoId,T.Dv,T.Direccion,T.IdLocal AS TercCdLocal,L.Localidad AS TercLocalidad,T.Telefono AS TercTelefono,T.TelMovil AS TercTerCelular,T.e_mail AS TercEmail
 FROM Trn_MudOrdenServ AS O INNER JOIN Companias AS CN ON O.IdCia=CN.IdCia INNER JOIN EstadoDoc AS ED ON O.IdEstado=ED.IdEstado
 INNER JOIN Terceros AS T ON O.IdCliente=T.IdTercero INNER JOIN TercCliente AS CLI ON O.IdCliente=CLI.IdClie
 INNER JOIN Terceros AS VN ON O.IdVend=VN.IdTercero INNER JOIN MudServicios AS TS ON O.IdTipoServ=TS.IdTipoServ
 INNER JOIN adm_Usuarios AS U ON O.IdUsuario=U.IdUsuario INNER JOIN Sys_TiposDoc AS TD ON O.TipDoc=TD.IdDoc
 INNER JOIN Localidades AS LO ON O.IdOrigen=LO.IdLocal INNER JOIN Localidades AS LD ON O.IdDestino=LD.IdLocal
 INNER JOIN Localidades AS L ON T.IdLocal=L.IdLocal
 INNER JOIN Trn_MudOrdenConc AS D ON O.TipDoc=D.TipDoc AND O.NumOrden=D.NumOrden AND O.IdCia=D.IdCia
 INNER JOIN ConcDiversos AS CD ON D.IdConcepto=CD.IdConcepto 
 LEFT JOIN Terceros AS NT ON D.NitTercero=NT.IdTercero
 LEFT JOIN Agencias AS A ON O.IdAgencia=A.IdAgencia LEFT JOIN adm_Usuarios AS UC ON D.CdUsuario=UC.IdUsuario
 WHERE O.TipDoc=@pmTipDoc AND O.NumOrden BETWEEN @pmNumOrdenIni AND @pmNumOrdenFin AND O.IdCia=@pmIdCia
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryMudInventario_Cr] @pmTipDoc VARCHAR(3),@pmNumInventIni INT,@pmNumInventFin INT,@pmIdCia CHAR(2)
 AS 
 SELECT M.TipDoc,TipoDoc,M.NumInvent,M.IdCia AS CdCia,Compania,M.Fecha,M.FecDespacho,M.IdTipoServ AS CdTipoServ,TipoServicio,M.IdCliente,T.RazonSocial AS NomCliente,M.IdAgencia AS CdAgencia,Agencia
,DirCargue,TipoInmRem,PisoCargue,TelsRemite,CelRemite,DirDescargue,TipoInmDest,PisoDescargue,TelsDest,CelDest
,M.IdOrigen,LO.Localidad AS CiuOrigen,DPO.Departamento AS DptoOrigen,M.IdDestino,LD.Localidad AS CiuDestino,DPD.Departamento AS DptoDestino,M.pVehiculo,M.CdConductor,NC.RazonSocial AS Conductor
,M.nRemolque,M.TipoAfiVehic,M.NitEmpresa,M.NomEmpresa,CajasEmp,CajasClie,M.VrDeclarado,M.VrServicio,M.NumCotizac,M.NumPlanilla,NumActa,M.TipOsm,M.NumOrden,M.IdCiaOsm,M.NomContacto,M.TelsContacto
,M.emlContacto,M.Responsable,M.FormaPago,M.Remitente AS ContactoCargue,M.Destinatario AS ContactoDestino,M.Anulado,M.FecDev,M.Observacion AS Observ,M.IdEstado AS CdEstado,ED.Estado
,M.OrigenAdd,M.TimeSys AS FechaCrea,M.FecUpdate,M.IdCiaCrea AS CdCiaCrea,M.IdUsuario AS CdUsuario,U.Usuario
,D.Item,D.IdArticulo AS CdArticulo,D.Descripcion,D.Cantidad,D.CantSalida,D.IdEstado AS CdEstadoArt,EA.Estado AS EstadoArt,D.Observacion AS DetObserv,Articulo,MA.IdSeccion AS CdSeccion,Seccion,EA.NColor AS NColorEst
,D.TipoIngreso,D.FechaReg,D.CdUsuario AS DetCdUsuario,UD.Usuario AS DetUsuario
FROM Trn_MudInventario AS M INNER JOIN Companias AS CN ON M.IdCia=CN.IdCia INNER JOIN Terceros AS T ON M.IdCliente=T.IdTercero
INNER JOIN MudServicios AS TS ON M.IdTipoServ=TS.IdTipoServ INNER JOIN EstadoDoc AS ED ON M.IdEstado=ED.IdEstado
INNER JOIN adm_Usuarios AS U ON M.IdUsuario=U.IdUsuario INNER JOIN Sys_TiposDoc AS TD ON M.TipDoc=TD.IdDoc
INNER JOIN Localidades AS LO ON M.IdOrigen=LO.IdLocal INNER JOIN Departamentos AS DPO ON LO.IdDep=DPO.IdDep
INNER JOIN Localidades AS LD ON M.IdDestino=LD.IdLocal INNER JOIN Departamentos AS DPD ON LD.IdDep=DPD.IdDep
INNER JOIN Trn_MudInventDet AS D ON M.TipDoc=D.TipDoc AND M.NumInvent=D.NumInvent AND M.IdCia=D.IdCia
INNER JOIN MudArticulos AS MA ON D.IdArticulo=MA.IdArticulo INNER JOIN MudSecciones AS SC ON MA.IdSeccion=SC.IdSeccion
INNER JOIN MudEstados AS EA ON D.IdEstado=EA.IdEstado
LEFT JOIN Terceros AS NC ON M.CdConductor=NC.IdTercero LEFT JOIN Agencias AS A ON M.IdAgencia=A.IdAgencia 
LEFT JOIN adm_Usuarios AS UD ON D.CdUsuario=UD.IdUsuario 
WHERE M.TipDoc=@pmTipDoc AND M.NumInvent BETWEEN @pmNumInventIni AND @pmNumInventFin AND M.IdCia=@pmIdCia
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryMudOrdenOper_Cr] @pmTipDoc VARCHAR(3),@pmNumOrdenIni INT,@pmNumOrdenFin INT,@pmIdCia CHAR(2)
 AS 
 SELECT O.TipDoc,O.NumOrden,O.IdCia,O.Item,O.IdOperario,NomOperario,O.IdTipoOper,TipoOperario
,O.CdConcepto,C.Concepto,C.IdCuenta,C.TipoConc,TarifaCargo,O.PlacaVehic,O.Descripcion,O.TipoReg,O.FechaReg,O.CdUsuario,Usuario
 FROM Trn_MudOrdenOper AS O INNER JOIN TiposOperarios AS TP ON O.IdTipoOper=TP.IdOper
 LEFT JOIN Terceros AS T ON O.IdOperario=T.IdTercero
 LEFT JOIN TraConcCausac AS C ON O.CdConcepto=C.IdConcepto
 LEFT JOIN adm_Usuarios AS U ON O.CdUsuario=U.IdUsuario
 WHERE O.TipDoc=@pmTipDoc AND O.NumOrden BETWEEN @pmNumOrdenIni AND @pmNumOrdenFin AND O.IdCia=@pmIdCia
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paInsMudOrdenConc] @pmTipDoc VARCHAR(3),@pmNumOrden INT,@pmIdCia CHAR(2),@pmItem INT,@pmIdConcepto VARCHAR(4),@pmDescripcion VARCHAR(500),@pmValorTotal MONEY,@pmTarifaIva DECIMAL(14,4),@pmTipoConc VARCHAR(10),@pmTipoRubro VARCHAR(20)
,@pmTipoReg INT,@pmCdCuenta VARCHAR(16),@pmNitTercero VARCHAR(16),@pmReferencia VARCHAR(50),@pmNumDocRef VARCHAR(20),@pmCodTarIva VARCHAR(4),@pmIndFactItem INT,@pmNumDocFac VARCHAR(20),@pmTarifSeguro DECIMAL(14,4),@pmCodTarSeg VARCHAR(4),@pmPlacaVehic VARCHAR(10),@pmTarifaPago MONEY
,@pmFechaReg SMALLDATETIME,@pmCdUsuario VARCHAR(11)
 AS 
 INSERT INTO Trn_MudOrdenConc (TipDoc,NumOrden,IdCia,Item,IdConcepto,Descripcion,ValorTotal,TarifaIva,TipoConc,TipoRubro,TipoReg,CdCuenta,NitTercero,Referencia,NumDocRef,CodTarIva,IndFactItem,NumDocFac,TarifSeguro,CodTarSeg,PlacaVehic,TarifaPago,FechaReg,CdUsuario)
 VALUES (@pmTipDoc,@pmNumOrden,@pmIdCia,@pmItem,@pmIdConcepto,@pmDescripcion,@pmValorTotal,@pmTarifaIva,@pmTipoConc,@pmTipoRubro,@pmTipoReg,@pmCdCuenta,@pmNitTercero,@pmReferencia,@pmNumDocRef,@pmCodTarIva,@pmIndFactItem,@pmNumDocFac,@pmTarifSeguro,@pmCodTarSeg,@pmPlacaVehic,@pmTarifaPago,@pmFechaReg,@pmCdUsuario)
GO