ALTER TABLE TarifasFletes ADD CdAgencia VARCHAR(16) DEFAULT('0')
GO
ALTER TABLE Subzonas ADD CdRuta VARCHAR(4)
GO
ALTER TABLE tm_TraLiqCausac ADD tmTipoFac VARCHAR(3)
GO
INSERT INTO adm_Opciones (IdOpc,Opcion,TipoDato,Valor,NivUp,nModulo)
 VALUES ('ABS','FORMATO DE ARCHIVO RECAUDO EMPRESARIAL ASOBANCARIA SICOM','BOOLEAN','0',4,'MAIN')
INSERT INTO adm_Opciones (IdOpc,Opcion,TipoDato,Valor,NivUp,nModulo)
 VALUES ('TMD','PORCENTAJE MAXIMO DE DEDUCCIONES SOBRE EL TOTAL DEVENGADO (SIN APORTES SALUD Y PENSION)','DECIMAL','-1',5,'NOMINA')
GO
INSERT INTO Sys_ObjetosApp (IdObj,IdGrupo,SubModulo,IndObj,Nombre,Formulario,Permisos,NivelMinimo,NomArchivo)
 VALUES ('FRMENTINV','MAIPRO','INV',6,'Importar Entradas a Inventario','FRMENTINV','SNNNNNNNNNNNN',0,'')
GO
INSERT INTO Sys_Roles (IdObj,IdRole,Funcion)
 VALUES ('FRMTCL','RUT','Permitir Seleccionar Ruta')
INSERT INTO Sys_Roles (IdObj,IdRole,Funcion)
 VALUES ('FRMAGE','RUT','Permitir Seleccionar Ruta')
INSERT INTO Sys_Roles (IdObj,IdRole,Funcion)
 VALUES ('FRMTCLTRA','RUT','Permitir Seleccionar Ruta')
INSERT INTO Sys_Roles (IdObj,IdRole,Funcion)
 VALUES ('FRMAGETRA','RUT','Permitir Seleccionar Ruta')
INSERT INTO Sys_Roles (IdObj,IdRole,Funcion)
 VALUES ('FRMNCA','DED','Permitir Crear Nómina descartando empleados con deducciones mayor que devengados')
INSERT INTO Sys_Roles (IdObj,IdRole,Funcion)
 VALUES ('FRMNCA','DEO','Permitir Exceso en margen de otras deducciones')
INSERT INTO Sys_Roles  (IdObj,IdRole,Funcion)
 VALUES ('FRMDODC','REQ','Permitir Agregar Requisiciones incluidas en otra orden de compra')
INSERT INTO Sys_Roles  (IdObj,IdRole,Funcion)
 VALUES ('FRMENTINV','FEC','Permitir Modificar fecha de Entrada')
GO
INSERT INTO  Sys_Report (TipoRep,IdRep,Descripcion,Formato,TipoPapel,Orientacion,VistaPrevia,VerSetup,NumCopias,sp_Nombre,Ind_Location,NomRep)
 VALUES ('EIM','01','Predeterminado (Hz)','CrEntInv.rpt',1,2,1,0,1,'LogEntInventario','L','Entradas a Inventario')
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInsSubzonas]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paInsSubzonas]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInsTarifasFletes]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paInsTarifasFletes]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryProdMciasDes]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryProdMciasDes]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQrySubzonas]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQrySubzonas]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQrySubzonasLta]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQrySubzonasLta]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryTarifasFletes]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryTarifasFletes]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryTarifasFletesLta]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryTarifasFletesLta]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paUpSubzonas]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paUpSubzonas]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paUpTarifasFletes]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paUpTarifasFletes]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryRequisicionDet]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryRequisicionDet]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInstm_TraLiqCausac]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paInstm_TraLiqCausac]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInstm_TraLiqCausac_Sel]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paInstm_TraLiqCausac_Sel]
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[paQryProdMciasDes] @pmIdProducto VARCHAR(16)
 AS 
 SELECT IdProducto,DescripProd,DescripAbrv,CodBarras,Referencia,TipoRef,Linea,Grupo,Subgrupo,Marca,Color,Tamano,P.UndMed AS UndVolm
,UM.Unidad AS UndadMed,UP.Unidad AS EmpPrim,Empaque,Bodega,DescripLong,Estado,RazonSocial,Tarifa,Simbolo
,S.IdGrupo AS CdGrupo,G.IdLinea AS CdLinea
 FROM ProdMcias AS P INNER JOIN SubGrupos AS S ON P.IdSubgrupo=S.IdSubgrupo INNER JOIN Grupos AS G ON S.IdGrupo=G.IdGrupo 
 INNER JOIN Lineas AS L ON G.IdLinea=L.IdLinea INNER JOIN Marcas AS M ON P.IdMarca=M.IdMarca
 INNER JOIN UndMed AS UM ON P.IdUnd=UM.IdUnd  INNER JOIN UndMed AS UP ON P.IdUndP=UP.IdUnd
 INNER JOIN Empaques AS E ON P.IdEmp=E.IdEmp  INNER JOIN Bodegas AS B ON P.IdBodega=B.IdBodega
 INNER JOIN Terceros AS TP ON P.IdProv=TP.IdTercero
 INNER JOIN Tablapor AS TI ON P.IdTarIva=TI.IdTarifa INNER JOIN EstadoPro AS EP ON P.IdEstado=EP.IdEstado
 WHERE IdProducto=@pmIdProducto
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[paInsSubzonas] @pmIdSzona VARCHAR(4),@pmSubzona VARCHAR(50),@pmIdZona VARCHAR(4),@pmCdRuta VARCHAR(4),@pmInactivo BIT
 AS 
 INSERT INTO Subzonas (IdSzona,Subzona,IdZona,Inactivo,CdRuta)
 VALUES (@pmIdSzona,@pmSubzona,@pmIdZona,@pmInactivo,@pmCdRuta)
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[paUpSubzonas] @pmIdSzona VARCHAR(4),@pmSubzona VARCHAR(50),@pmIdZona VARCHAR(4),@pmCdRuta VARCHAR(4),@pmInactivo BIT
 AS 
 UPDATE Subzonas SET Subzona=@pmSubzona,IdZona=@pmIdZona,Inactivo=@pmInactivo,CdRuta=@pmCdRuta
 WHERE IdSzona=@pmIdSzona
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[paQrySubzonas] @pmIdSzona VARCHAR(4)
 AS 
 SELECT IdSzona,Subzona,IdZona,CdRuta,Inactivo
 FROM Subzonas
 WHERE IdSzona=@pmIdSzona
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[paQrySubzonasLta] @pmIdZona VARCHAR(4)=Null
 AS 
  SELECT IdSzona,Subzona,S.IdZona AS CdZona,Zona,CdRuta,Ruta
  FROM Subzonas AS S INNER JOIN Zonas AS Z ON S.IdZona=Z.IdZona
  LEFT JOIN Rutas AS R ON S.CdRuta=R.IdRuta
  WHERE S.Inactivo=0 AND S.IdZona LIKE ISNULL(@pmIdZona,'%')
  ORDER BY Zona,Subzona
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryTarifasFletesLta] @pmTipoTarifa VARCHAR(10)=Null,@pmIdOrigen VARCHAR(8)=Null,@pmIdDestino VARCHAR(8)=Null
,@pmNitCliente VARCHAR(16)=Null,@pmInactivo BIT=Null,@pmCdRuta VARCHAR(4)=Null
 AS 
 SELECT Numero,IdOrigen,O.Localidad AS CiuOrigen,IdDestino,D.Localidad AS CiuDestino,CdRuta,TarifClie,TarifPago,TarifTabla,IdMneda,T.UndMed AS Und_Med,Unidad
,NitCliente,RazonSocial,CdNat,Natlzaprod,CdTipoVeh,TipoVehiculo,CdRango,DescripRango,T.Inactivo AS Inactvo,UndTarifClie,UndTarifPago,CdMercancia,TipoAfiVehic
,TarifAfiliado,TarifTercero,TarifPropio,TipoOper,CdGrupo,GrupoProp,MargenMin,CdAgencia,FechaCrea,T.IdUsuario AS CdUsuario,Usuario
,O.IdDep AS CdDep,DPO.Departamento AS DptoOrigen,D.IdDep AS CdDepDest,DPD.Departamento AS DptoDestino,TipoTarifa
 FROM TarifasFletes AS T INNER JOIN Localidades AS O ON T.IdOrigen=O.IdLocal INNER JOIN Localidades AS D ON T.IdDestino=D.IdLocal
 INNER JOIN Departamentos AS DPO ON O.IdDep=DPO.IdDep INNER JOIN Departamentos AS DPD ON D.IdDep=DPD.IdDep
 INNER JOIN adm_Usuarios AS U ON T.IdUsuario=U.IdUsuario
 LEFT JOIN Terceros AS TC ON T.NitCliente=TC.IdTercero LEFT JOIN Sys_Um AS UM ON T.UndMed=UM.UndMed 
 LEFT JOIN TiposNat AS N ON T.CdNat=N.IdNat LEFT JOIN TiposVeh AS TV ON T.CdTipoVeh=TV.IdTipoVeh
 LEFT JOIN RangosPeso AS R ON T.CdRango=R.IdRango LEFT JOIN GruposPro AS G ON T.CdGrupo=G.IdGrupo
 WHERE TipoTarifa LIKE ISNULL(@pmTipoTarifa,'%') AND  IdOrigen LIKE ISNULL(@pmIdOrigen,'%')
 AND IdDestino LIKE ISNULL(@pmIdDestino,'%') AND NitCliente LIKE ISNULL(@pmNitCliente,'%')
 AND CdRuta LIKE ISNULL(@pmCdRuta,'%')
 AND  (T.Inactivo=ISNULL(@pmInactivo,0) or T.Inactivo=ISNULL(@pmInactivo,1))
 ORDER BY O.Localidad,D.Localidad,Numero
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paInsTarifasFletes] @pmIdOrigen VARCHAR(8),@pmIdDestino VARCHAR(8),@pmNumero INT,@pmTipoTarifa VARCHAR(10),@pmTarifClie MONEY,@pmTarifPago MONEY
,@pmTarifTabla MONEY,@pmIdMneda VARCHAR(5),@pmUndMed VARCHAR(10),@pmNitCliente VARCHAR(16),@pmCdNat VARCHAR(4),@pmCdTipoVeh VARCHAR(4)
,@pmCdRango VARCHAR(4),@pmUndTarifClie VARCHAR(10),@pmUndTarifPago VARCHAR(10),@pmInactivo BIT,@pmCdRuta VARCHAR(4),@pmCdMercancia VARCHAR(16)
,@pmTipoAfiVehic VARCHAR(10),@pmTarifAfiliado DECIMAL(16,6),@pmTarifTercero DECIMAL(16,6),@pmTarifPropio DECIMAL(16,6),@pmTipoOper VARCHAR(10)
,@pmCdGrupo VARCHAR(4),@pmMargenMin DECIMAL(14,4),@pmCdAgencia VARCHAR(16),@pmFechaCrea SMALLDATETIME,@pmIdUsuario VARCHAR(11)
 AS 
 INSERT INTO TarifasFletes (Numero,IdOrigen,IdDestino,CdRuta,TipoTarifa,TarifClie,TarifPago,TarifTabla,IdMneda,UndMed,NitCliente,CdNat,CdTipoVeh,CdRango
,UndTarifClie,UndTarifPago,TipoAfiVehic,TarifAfiliado,TarifTercero,TarifPropio,Inactivo,CdMercancia,FechaCrea,IdUsuario,TipoOper,CdGrupo,MargenMin,CdAgencia)
 VALUES (@pmNumero,@pmIdOrigen,@pmIdDestino,@pmCdRuta,@pmTipoTarifa,@pmTarifClie,@pmTarifPago,@pmTarifTabla,@pmIdMneda,@pmUndMed,@pmNitCliente
,@pmCdNat,@pmCdTipoVeh,@pmCdRango,@pmUndTarifClie,@pmUndTarifPago,@pmTipoAfiVehic,@pmTarifAfiliado,@pmTarifTercero,@pmTarifPropio,@pmInactivo,@pmCdMercancia,@pmFechaCrea,@pmIdUsuario,@pmTipoOper,@pmCdGrupo,@pmMargenMin,@pmCdAgencia)
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paUpTarifasFletes] @pmIdOrigen VARCHAR(8),@pmIdDestino VARCHAR(8),@pmNumero INT,@pmTipoTarifa VARCHAR(10),@pmTarifClie MONEY,@pmTarifPago MONEY,@pmTarifTabla MONEY,@pmIdMneda VARCHAR(5)
,@pmUndMed VARCHAR(10),@pmNitCliente VARCHAR(16),@pmCdNat VARCHAR(4),@pmCdTipoVeh VARCHAR(4),@pmCdRango VARCHAR(4)
,@pmUndTarifClie VARCHAR(10),@pmUndTarifPago VARCHAR(10),@pmInactivo BIT,@pmCdRuta VARCHAR(4),@pmCdMercancia VARCHAR(16),@pmTipoAfiVehic VARCHAR(10)
,@pmTarifAfiliado DECIMAL(16,6),@pmTarifTercero DECIMAL(16,6),@pmTarifPropio DECIMAL(16,6),@pmTipoOper VARCHAR(10),@pmCdGrupo VARCHAR(4),@pmMargenMin DECIMAL(14,4),@pmCdAgencia VARCHAR(16)
 AS 
 UPDATE TarifasFletes SET TipoTarifa=@pmTipoTarifa,TarifClie=@pmTarifClie,TarifPago=@pmTarifPago,TarifTabla=@pmTarifTabla,IdMneda=@pmIdMneda,UndMed=@pmUndMed,NitCliente=@pmNitCliente
,CdNat=@pmCdNat,CdTipoVeh=@pmCdTipoVeh,CdRango=@pmCdRango,Inactivo=@pmInactivo,UndTarifClie=@pmUndTarifClie,UndTarifPago=@pmUndTarifPago,CdRuta=@pmCdRuta
,IdOrigen=@pmIdOrigen,IdDestino=@pmIdDestino,CdMercancia=@pmCdMercancia,TipoAfiVehic=@pmTipoAfiVehic
,TarifAfiliado=@pmTarifAfiliado,TarifTercero=@pmTarifTercero,TarifPropio=@pmTarifPropio,TipoOper=@pmTipoOper,CdGrupo=@pmCdGrupo,MargenMin=@pmMargenMin,CdAgencia=@pmCdAgencia
 WHERE  Numero=@pmNumero
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryTarifasFletes] @pmNumero INT
 AS 
 SELECT Numero,IdOrigen,IdDestino,CdRuta,TipoTarifa,TarifClie,TarifPago,TarifTabla,IdMneda,UndMed
,NitCliente,CdNat,CdTipoVeh,CdRango,UndTarifClie,UndTarifPago
,Inactivo,CdMercancia,TipoAfiVehic,TarifAfiliado,TarifTercero,TarifPropio,TipoOper,CdGrupo,MargenMin,CdAgencia,FechaCrea,IdUsuario
 FROM TarifasFletes
 WHERE  Numero=@pmNumero
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[paQryRequisicionDet] @pmTipDoc VARCHAR(3),@pmRequisicion INT,@pmIdCia CHAR(2)
 AS 
 SELECT Fecha,IdRespons,IdCCosto,IdSubCos,IdDep,NContrato,IdCiaCont,NitCliente,CdAgencia,Modalidad
,DirEntrega,IdLocEnt,NumAprob,FecAprob,CdUsuAprob,OrigenAdd,Anulado,FecDev,Observacion,IdEstado
,Item,CdProducto,Descripcion,IdSubgrupo,CdBodega,D.Cantidad AS CantArt,IdUnd,VrUnitario,D.Referencia
,D.TipOrd,D.NumOrden,D.IdCiaOrd
 FROM Trn_Requisicion AS R INNER JOIN  Trn_ReqDetalle AS D ON R.TipDoc=D.TipDoc AND R.Requisicion=D.Requisicion
 AND R.IdCia=D.IdCia
 WHERE R.TipDoc=@pmTipDoc AND R.Requisicion=@pmRequisicion AND R.IdCia=@pmIdCia
 ORDER BY Item
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE  [dbo].[paInstm_TraLiqCausac_Sel] @pmtmNumero VARCHAR(5),@pmTipDoc VARCHAR(3),@pmLiquidacion INT,@pmIdCia CHAR(2)
 AS 
 INSERT INTO tm_TraLiqCausac (tmNumero,tmItem,tmTipCau,tmCausacion,tmIdCiaCau,tmItemCau,tmFecha,tmIdConcepto,tmDescripcion,tmVrAbono,tmNit,tmVehiculo,tmTipOdp,tmOrdenPago,tmIdCiaOdp
,tmTipMuc,tmManifiesto,tmIdCiaMuc,tmItemFact,tmVrSaldo,tmEsVence,tmCdCuenta,tmCuePagar,tmFactura,tmConcValor,tmConcAbono,tmCdCCosto,tmCdSubCos,tmTipoFac)
 SELECT @pmtmNumero,Item,TipCau,Causacion,IdCiaCau,ItemCau,FechaCau,IdConcepto,Descripcion,VrAbono,NitPoseedor,pVehiculo,TipOdp,OrdenPago,IdCiaOdp
,TipMuc,Manifiesto,IdCiaMuc,1,VrSaldoOrden,EsVence,CdCuenta,CdCuePagar,NumFactura,0,0,CdCCosto,CdSubCos,TipOdp
 FROM Trn_TraLiquidaDed
 WHERE TipDoc=@pmTipDoc AND Liquidacion=@pmLiquidacion AND IdCia=@pmIdCia
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paInstm_TraLiqCausac] @pmtmNumero VARCHAR(5),@pmtmItem INT,@pmtmTipCau VARCHAR(3),@pmtmCausacion INT,@pmtmIdCiaCau CHAR(2),@pmtmItemCau INT,@pmtmFecha SMALLDATETIME,@pmtmIdConcepto VARCHAR(4),@pmtmDescripcion VARCHAR(250)
,@pmtmVrAbono MONEY,@pmtmNit VARCHAR(16),@pmtmVehiculo VARCHAR(10),@pmtmTipOdp VARCHAR(3),@pmtmOrdenPago INT,@pmtmIdCiaOdp CHAR(2),@pmtmTipMuc VARCHAR(3),@pmtmManifiesto INT,@pmtmIdCiaMuc CHAR(2),@pmtmItemFact INT,@pmtmVrSaldo MONEY
,@pmtmEsVence BIT,@pmtmCdCuenta VARCHAR(16),@pmtmCuePagar VARCHAR(16),@pmtmFactura VARCHAR(20),@pmtmConcValor MONEY,@pmtmConcAbono MONEY,@pmtmCdCCosto VARCHAR(16),@pmtmCdSubCos VARCHAR(16),@pmtmTipoFac VARCHAR(3)
 AS 
 INSERT INTO tm_TraLiqCausac (tmNumero,tmItem,tmTipCau,tmCausacion,tmIdCiaCau,tmItemCau,tmFecha,tmIdConcepto,tmDescripcion,tmVrAbono,tmNit,tmVehiculo,tmTipOdp,tmOrdenPago,tmIdCiaOdp,tmTipMuc,tmManifiesto,tmIdCiaMuc
,tmItemFact,tmVrSaldo,tmEsVence,tmCdCuenta,tmCuePagar,tmFactura,tmConcValor,tmConcAbono,tmCdCCosto,tmCdSubCos,tmTipoFac)
 VALUES (@pmtmNumero,@pmtmItem,@pmtmTipCau,@pmtmCausacion,@pmtmIdCiaCau,@pmtmItemCau,@pmtmFecha,@pmtmIdConcepto,@pmtmDescripcion,@pmtmVrAbono,@pmtmNit,@pmtmVehiculo,@pmtmTipOdp,@pmtmOrdenPago,@pmtmIdCiaOdp
,@pmtmTipMuc,@pmtmManifiesto,@pmtmIdCiaMuc,@pmtmItemFact,@pmtmVrSaldo,@pmtmEsVence,@pmtmCdCuenta,@pmtmCuePagar,@pmtmFactura,@pmtmConcValor,@pmtmConcAbono,@pmtmCdCCosto,@pmtmCdSubCos,@pmtmTipoFac)
GO