INSERT INTO Sys_ObjetosApp (IdObj,IdGrupo,SubModulo,IndObj,Nombre,Formulario,Permisos,NivelMinimo,NomArchivo)
 VALUES ('FRMIMPCAT','MAIPRO','INV',5,'Importar Catálogo','FRMIMPCAT','SNNNNNNNNNNNN',0,'')
INSERT INTO Sys_ObjetosApp (IdObj,IdGrupo,SubModulo,IndObj,Nombre,Formulario,Permisos,NivelMinimo,NomArchivo)
 VALUES ('FRMLAUBI','MAIINF','INV',18,'Auxiliar de Inventarios/Ubicaciones','FRMLAUBI','SNNNNNNNNNNNN',0,'')
INSERT INTO Sys_Report (TipoRep,IdRep,Descripcion,Formato,TipoPapel,Orientacion,VistaPrevia,VerSetup,NumCopias,sp_Nombre,Ind_Location,NomRep)
 VALUES ('AIU','01','Análisis de Existencias/Ubicaciones','CrAIubi.rpt',1,1,1,0,1,'paQrytm_AuxInvUbic','','Auxiliar de Inventarios/Ubicaciones')
GO
CREATE TABLE tm_AuxInvUbic ( 
tmId INT IDENTITY ( 1,1 ) NOT NULL,
tmEst CHAR(2) NOT NULL,
tmIdProducto VARCHAR(16) NOT NULL,
tmIdBodega VARCHAR(4) NOT NULL,
tmIdUbic VARCHAR(10) NOT NULL,
tmIdCia CHAR(2) NOT NULL,
tmSaldoAnt DECIMAL(14,4) DEFAULT ((0)) NOT NULL,
tmEntradas DECIMAL(14,4) DEFAULT ((0)) NOT NULL,
tmSalidas DECIMAL(14,4) DEFAULT ((0)) NOT NULL,
tmCostoAnt MONEY DEFAULT ((0)) NOT NULL,
tmCostoEnt MONEY DEFAULT ((0)) NOT NULL,
tmCostoSal MONEY DEFAULT ((0)) NOT NULL,
tmCostoProm MONEY DEFAULT ((0)) NOT NULL
 CONSTRAINT PK_tm_AuxInvUbic PRIMARY KEY NONCLUSTERED (tmId),
 CONSTRAINT CK_tm_AuxInvUbictmEst CHECK ((len([tmEst])>(0))),
 CONSTRAINT CK_tm_AuxInvUbictmIdBodega CHECK ((len([tmIdBodega])>(0))),
 CONSTRAINT CK_tm_AuxInvUbictmIdCia CHECK ((len([tmIdCia])>(0))),
 CONSTRAINT CK_tm_AuxInvUbictmIdProducto CHECK ((len([tmIdProducto])>(0))),
 CONSTRAINT CK_tm_AuxInvUbictmIdUbic CHECK ((len([tmIdUbic])>(0))))
GO
CREATE CLUSTERED INDEX IX_tm_AuxInvUbictmIdProducto ON tm_AuxInvUbic(tmEst,tmIdProducto,tmIdBodega,tmIdCia)

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInstm_Caja_Aos]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paInstm_Caja_Aos]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInstm_Caja_Dao]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paInstm_Caja_Dao]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQrytm_AuxInvUbic]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQrytm_AuxInvUbic]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryTraOrdenAntTotCaj]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryTraOrdenAntTotCaj]
GO

SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[paQrytm_AuxInvUbic] @pmtmEst CHAR(2)
 AS 
 SELECT tmIdProducto,DescripProd,tmIdCia,Compania,tmIdBodega,Bodega,tmIdUbic,Ubicacion
,tmSaldoAnt,tmCostoAnt,tmEntradas,tmCostoEnt,tmSalidas,tmCostoSal
,CASE  WHEN TipoRef IN ('PRODUCTO','INSUMO') THEN tmSaldoAnt+tmEntradas-tmSalidas ELSE tmSalidas-tmEntradas  END AS NuevoSaldo
,CASE  WHEN TipoRef IN ('PRODUCTO','INSUMO') THEN tmCostoAnt+tmCostoEnt-tmCostoSal ELSE 0  END AS CostoTotal
,CASE  WHEN TipoRef IN ('PRODUCTO','INSUMO') THEN tmCostoProm ELSE 0  END AS CostoPromActual
,CASE  WHEN TipoRef IN ('PRODUCTO','INSUMO') THEN tmCostoProm*(tmSaldoAnt+tmEntradas-tmSalidas) ELSE 0  END AS  CostoTotalPromedio
,TipoRef,PM.IdUnd AS CdUnid,UM.Unidad AS UnidadPres,PM.UndMed AS CdUndMed,SU.Unidad AS UnidadMed
,PM.IdSubgrupo AS CdSubgrupo,Subgrupo,S.IdGrupo AS CdGrupo,Grupo,G.IdLinea AS CdLinea,Linea,PM.IdMarca AS CdMarca,Marca
,Tamano,Color,Referencia,VrCosto AS ProdUltCosto,VrCostPmd AS ProdCostoProm,IdTarIva,Tarifa,Simbolo,IvaInc
,Precio1,Precio2,Precio3,Precio4,Precio5,IdProv,PV.RazonSocial AS NomProveedor,DescripLong,DescripAbrv,ExtciaMin,ExtciaMax,ExtciaAct
,Factor1,Factor2,Factor3,Factor4,Factor5,PM.Inactivo AS ProdInactivo,Pasillo,Nivel,Profdad,Notas
 FROM tm_AuxInvUbic AS A INNER JOIN ProdMcias AS PM ON A.tmIdProducto=PM.IdProducto
 INNER JOIN UndMed AS UM ON PM.IdUnd=UM.IdUnd
 INNER JOIN SubGrupos AS S ON PM.IdSubgrupo=S.IdSubgrupo INNER JOIN Grupos AS G ON S.IdGrupo=G.IdGrupo 
 INNER JOIN Lineas AS LN ON G.IdLinea=LN.IdLinea INNER JOIN Sys_Um AS SU ON PM.UndMed=SU.UndMed
 INNER JOIN Terceros AS PV ON PM.IdProv=PV.IdTercero
 LEFT JOIN Companias AS CN ON A.tmIdCia=CN.IdCia LEFT JOIN Bodegas AS B ON A.tmIdBodega=B.IdBodega
 LEFT JOIN Tablapor AS TI ON PM.IdTarIva=TI.IdTarifa LEFT JOIN Marcas AS M ON PM.IdMarca=M.IdMarca
 LEFT JOIN BodUbic AS U ON A.tmIdUbic=U.IdUbic
 WHERE tmEst=@pmtmEst
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paInstm_Caja_Aos] @pmtmEst CHAR(2),@pmFechaIni SMALLDATETIME,@pmFechaFin SMALLDATETIME,@pmIdCia CHAR(2)=Null
,@pmIdCajero VARCHAR(11)=Null
 AS 
 INSERT INTO tm_Caja (tmEst,tmItem,TipDoc,Numero,IdCia,Fecha,IdConcepto,IdTercero,IdAgencia,VrSubTotal,VrDescuento,VrImpuesto,VrRetencion,VrOtros,VCargos,VrOtrosDctos,VrImpGlobal,VrSoldicom,VrNeto,VrEfectivo
,VrPagosOtr,VrCreditos,VrGastComb,VrGastOtros,Cantidad,CantPuntos,PuntosAcum,EnEfectivo,CdForma,NumForma,CdBanco,DetallePagos,CdCta,FecCheque,IdVend,TarifaCom,nClieCon,IdCajero,IdLocal,Referencia,Referencia2
,Modalidad,pVehiculo,TipRef,DocRef,IdCiaRef,TipCom,Comprobante,IdCiaCom,Anulado,NumDev,FecDev,Observacion,TimeSys)
SELECT @pmtmEst,7,A.TipDoc,A.Anticipo,A.IdCia,A.Fecha,A.IdConcepto,O.IdPoseedor,O.IdAgencia,A.VrAnticipo,0,0,0,0,0,0,0,0,A.VrAnticipo AS ValNeto,CASE A.TipoPago WHEN 'EFECTIVO' THEN A.VrAnticipo ELSE 0 END
,0,0,0,0,0,0,0,CASE A.TipoPago WHEN 'EFECTIVO' THEN 1 ELSE 0 END,CASE A.TipoPago WHEN 'EFECTIVO' THEN 'EFE' ELSE 'CHE' END,A.NumCheque,IdBanco,SUBSTRING(A.Beneficiario,1,150),A.IdCta,A.FecCheque,ISNULL(A.CedBenef,'0'),0,'0',A.IdUsuario,'0','ANTICIPOS',''
,'EGRESO',O.IdVehiculo,A.TipOds,A.NumOrden,A.IdCiaOds,A.TipCom,A.Comprobante,A.IdCiaCom,A.Anulado,A.NumDev,A.FecDev,A.Observacion,A.TimeSys
 FROM Trn_TraOrdenAnt AS A INNER JOIN Trn_TraOrdenServ AS O ON A.TipOds=O.TipDoc AND A.NumOrden=O.NumOrden AND A.IdCiaOds=O.IdCia
 LEFT JOIN CtasCorrientes AS CT ON A.IdCta=CT.IdCta
 WHERE A.Fecha BETWEEN @pmFechaIni AND @pmFechaFin AND A.TipoPago='EFECTIVO'
 AND A.IdCia LIKE ISNULL(@pmIdCia,'%%') AND A.IdUsuario LIKE ISNULL(@pmIdCajero,'%')
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paInstm_Caja_Dao] @pmtmEst CHAR(2),@pmFechaIni SMALLDATETIME,@pmFechaFin SMALLDATETIME,@pmIdCia CHAR(2)=Null
,@pmIdCajero VARCHAR(11)=Null
 AS 
 INSERT INTO tm_Caja (tmEst,tmItem,TipDoc,Numero,IdCia,Fecha,IdConcepto,IdTercero,IdAgencia,VrSubTotal,VrDescuento,VrImpuesto,VrRetencion,VrOtros,VCargos,VrOtrosDctos,VrImpGlobal,VrSoldicom,VrNeto,VrEfectivo
,VrPagosOtr,VrCreditos,VrGastComb,VrGastOtros,Cantidad,CantPuntos,PuntosAcum,EnEfectivo,CdForma,NumForma,CdBanco,DetallePagos,CdCta,FecCheque,IdVend,TarifaCom,nClieCon,IdCajero,IdLocal,Referencia,Referencia2
,Modalidad,pVehiculo,TipRef,DocRef,IdCiaRef,TipCom,Comprobante,IdCiaCom,Anulado,NumDev,FecDev,Observacion,TimeSys)
SELECT @pmtmEst,7,D.TipDev,D.Devolucion,D.IdCia,D.Fecha,D.IdConcepto,O.IdPoseedor,O.IdAgencia,D.VrDevolucion,0,0,0,0,0,0,0,0,D.VrDevolucion AS ValNeto,CASE A.TipoPago WHEN 'EFECTIVO' THEN D.VrDevolucion ELSE 0 END
,0,0,0,0,0,0,0,CASE A.TipoPago WHEN 'EFECTIVO' THEN 1 ELSE 0 END,CASE A.TipoPago WHEN 'EFECTIVO' THEN 'EFE' ELSE 'CHE' END,A.NumCheque,IdBanco,SUBSTRING(A.Beneficiario,1,150),D.CdCta,A.FecCheque,ISNULL(A.CedBenef,'0'),0,'0',D.IdUsuario,'0','ANTICIPOS',''
,'EGRESO',O.IdVehiculo,A.TipOds,A.NumOrden,A.IdCiaOds,D.TipCom,D.Comprobante,D.IdCiaCom,0,D.Anticipo,D.FecDoc,D.Observacion,D.TimeSys
 FROM Trn_TraDevAos AS D INNER JOIN Trn_TraOrdenAnt AS A ON D.TipDoc=A.TipDoc AND D.Anticipo=A.Anticipo AND D.IdCiaDoc=A.IdCia
 INNER JOIN Trn_TraOrdenServ AS O ON A.TipOds=O.TipDoc AND A.NumOrden=O.NumOrden AND A.IdCiaOds=O.IdCia
 LEFT JOIN CtasCorrientes AS CT ON D.CdCta=CT.IdCta
 WHERE D.Fecha BETWEEN @pmFechaIni AND @pmFechaFin AND A.TipoPago='EFECTIVO'
 AND D.IdCia LIKE ISNULL(@pmIdCia,'%%') AND D.IdUsuario LIKE ISNULL(@pmIdCajero,'%')
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryTraOrdenAntTotCaj]  @pmFechaIni SMALLDATETIME,@pmFechaFin SMALLDATETIME,@pmIdCia CHAR(2)=Null
,@pmIdUsuario VARCHAR(11)=Null
AS
SELECT TipDoc,SUM(VrAnticipo) AS TotalAnt
FROM Trn_TraOrdenAnt 
WHERE Fecha BETWEEN @pmFechaIni AND @pmFechaFin AND TipoPago='EFECTIVO'
AND IdCia LIKE ISNULL(@pmIdCia,'%%') AND IdUsuario LIKE ISNULL(@pmIdUsuario,'%')
GROUP BY TipDoc
UNION ALL
SELECT D.TipDev,SUM(D.VrDevolucion)
FROM Trn_TraDevAos AS D INNER JOIN Trn_TraOrdenAnt AS A ON D.TipDoc=A.TipDoc AND D.Anticipo=A.Anticipo AND D.IdCiaDoc=A.IdCia
WHERE D.Fecha BETWEEN @pmFechaIni AND @pmFechaFin AND A.TipoPago='EFECTIVO'
AND D.IdCia LIKE ISNULL(@pmIdCia,'%%') AND D.IdUsuario LIKE ISNULL(@pmIdUsuario,'%')
GROUP BY D.TipDev
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryTraTurnLta] @pmFechaIni SMALLDATETIME,@pmFechaFin SMALLDATETIME,@pmIdVehiculo VARCHAR(10)=Null 
 AS  
SELECT T.Numero,T.IdCia AS CdCia,Compania,T.IdVehiculo AS PlacaVeh,V.NumVeh AS NumVehiculo,T.nRemolque
,T.IndTurno AS Ind_Turno,T.Fecha,T.IdConductor AS CedConductor,NC.RazonSocial AS NomConductor,T.Estado
,CASE T.Estado WHEN 1 THEN 'EN ESPERA' WHEN 2 THEN 'EN TRANSITO' ELSE 'INACTIVO' END AS DescEstado
,TipOrd,NumOrden,IdCiaOrd,FecOrden,T.IdUsuario AS CdUsuario,Usuario
,V.IdTipoVeh AS CdTipoVeh,TipoVehiculo,V.TipoAfil,V.IdMarca AS CdMarca,M.Marca AS MarcaVeh,V.IdColor AS CdColor,NomColor,V.Modelo
,Config,V.IdCat AS CdCateg,Catpeaje,CarrCapac,UndCapc,V.IdPoseedor AS NitPoseedor,NP.RazonSocial AS NomPoseedor,NumSoat,FecSoat,VigSoat,KmActual
,CdLocal,CU.Localidad,V.Ubicacion
,NC.TelMovil AS Cond_Celular,NC.Telefono AS Cond_Telefono,NC.e_mail AS Cond_Email,CND.Licencia AS NumLicencia,CND.CatLicencia,VigLicencia
FROM Trn_TraTurn AS T INNER JOIN Vehiculos AS V ON T.IdVehiculo=V.IdVehiculo INNER JOIN adm_Usuarios AS U ON T.IdUsuario=U.IdUsuario
INNER JOIN Terceros AS NC ON T.IdConductor=NC.IdTercero INNER JOIN Companias AS CN ON T.IdCia=CN.IdCia
INNER JOIN TiposVeh AS TV ON V.IdTipoVeh=TV.IdTipoVeh INNER JOIN Marcas AS M ON V.IdMarca=M.IdMarca INNER JOIN TiposCol AS CL ON V.IdColor=CL.IdColor
INNER JOIN Terceros AS NP ON V.IdPoseedor=NP.IdTercero
LEFT JOIN PeajesCat AS CP ON V.IdCat=CP.IdCat LEFT JOIN Localidades AS CU ON V.CdLocal=CU.IdLocal
LEFT JOIN TercCndtores AS CND ON T.IdConductor=CND.IdConductor
WHERE T.Fecha BETWEEN @pmFechaIni AND @pmFechaFin AND T.IdVehiculo LIKE ISNULL(@pmIdVehiculo,'%')
GO