INSERT INTO adm_Opciones (IdOpc,Opcion,TipoDato,Valor,NivUp,nModulo) VALUES ('VVC','VALIDAR PLACA DE VEHICULO EN ORDEN DE COMPRA (MODULO TRANSPORTE Y BUSES)','BOOLEAN','0',5,'MAIN') GO INSERT INTO Sys_ObjetosApp (IdObj,IdGrupo,SubModulo,IndObj,Nombre,Formulario,Permisos,NivelMinimo,NomArchivo) VALUES ('FRMDCPP','MAIDOC','PRO',1,'Consolidado de Pedidos','FRMDCPP','SNNNNNNNNNNNN',0,'') GO INSERT INTO Sys_Roles (IdObj,IdRole,Funcion) VALUES ('FRMDCPP','FEC','Habilitar Fecha Abierta') INSERT INTO Sys_Roles (IdObj,IdRole,Funcion) VALUES ('FRMDCPP','MOD','Modificar Documento') INSERT INTO Sys_Roles (IdObj,IdRole,Funcion) VALUES ('FRMDCPP','CIA','Cambiar de Compañía') INSERT INTO Sys_Roles (IdObj,IdRole,Funcion) VALUES ('FRMDCPP','ADC','Modificar o Anular documento creado en otra compañía') INSERT INTO Sys_Roles (IdObj,IdRole,Funcion) VALUES ('FRMDCPP','REF','Editar items de Productos') INSERT INTO Sys_Roles (IdObj,IdRole,Funcion) VALUES ('FRMDCPP','ERF','Eliminar items de Productos') INSERT INTO Sys_Roles (IdObj,IdRole,Funcion) VALUES ('FRMDCPP','PRO','Editar items de Procesos') INSERT INTO Sys_Roles (IdObj,IdRole,Funcion) VALUES ('FRMDCPP','EPC','Eliminar items de Procesos') INSERT INTO Sys_Roles (IdObj,IdRole,Funcion) VALUES ('FRMDCPP','APC','Agregar items de procesos') INSERT INTO Sys_Roles (IdObj,IdRole,Funcion) VALUES ('FRMDCPP','INS','Editar Items de Insumos') INSERT INTO Sys_Roles (IdObj,IdRole,Funcion) VALUES ('FRMDCPP','EIN','Eliminar Items de Insumos') INSERT INTO Sys_Roles (IdObj,IdRole,Funcion) VALUES ('FRMDCPP','AIN','Agregar Items de Insumos') INSERT INTO Sys_Roles (IdObj,IdRole,Funcion) VALUES ('FRMDCPP','BOD','Permitir Cambiar Bodega de Productos') INSERT INTO Sys_Roles (IdObj,IdRole,Funcion) VALUES ('FRMDCPP','BIN','Permitir Cambiar Bodega de Insumos') INSERT INTO Sys_Roles (IdObj,IdRole,Funcion) VALUES ('FRMDCPP','BDP','Permitir Cambiar Bodega Reserva de Productos') INSERT INTO Sys_Roles (IdObj,IdRole,Funcion) VALUES ('FRMDCPP','BDI','Permitir Cambiar Bodega Reserva de Insumos') INSERT INTO Sys_Roles (IdObj,IdRole,Funcion) VALUES ('FRMDCPP','APR','Habilitar Opción de Aprobados') INSERT INTO Sys_Roles (IdObj,IdRole,Funcion) VALUES ('FRMDENT','OSQ','Permitir incluir unidades obsequiadas') INSERT INTO Sys_Roles (IdObj,IdRole,Funcion) VALUES ('FRMDCOM','OSQ','Permitir incluir unidades obsequiadas') GO INSERT INTO Sys_TiposDoc (IdDoc,TipoDoc,ConsUnico,Leyenda,Inactivo,IntegraCias) VALUES ('CPP','CONSOLIDADO DE PEDIDOS DE PRODUCCION',0,'',0,0) GO ALTER TABLE Trn_TraOrdenAnt ADD Cantidad DECIMAL(14,4) DEFAULT(0) NOT NULL GO ALTER TABLE Trn_Face ADD FechaValidacion DATETIME GO CREATE TABLE Trn_ProdConsolida ( TipDoc VARCHAR(3) DEFAULT ('CPP') NOT NULL, NumCons INT DEFAULT ((0)) NOT NULL, IdCia CHAR(2) NOT NULL, Fecha SMALLDATETIME NOT NULL, IdBodega VARCHAR(4) NOT NULL, CdBodDest VARCHAR(4), IdBodIns VARCHAR(4) NOT NULL, CdBodInsDest VARCHAR(4), OrigenAdd VARCHAR(10) NOT NULL, Anulado BIT DEFAULT ((0)) NOT NULL, FecDev SMALLDATETIME, CritPedidos VARCHAR(500), Observacion VARCHAR(2000), IdEstado VARCHAR(4) NOT NULL, TimeSys SMALLDATETIME NOT NULL, FecUpdate SMALLDATETIME, IdCiaCrea CHAR(2) DEFAULT ('01') NOT NULL, IdUsuario VARCHAR(11) NOT NULL CONSTRAINT PK_Trn_ProdConsolida PRIMARY KEY CLUSTERED (TipDoc,NumCons,IdCia), CONSTRAINT CK_Trn_ProdConsolidaIdBodega CHECK ((len([IdBodega])>(0))), CONSTRAINT CK_Trn_ProdConsolidaIdBodIns CHECK ((len([IdBodIns])>(0))), CONSTRAINT CK_Trn_ProdConsolidaIdCia CHECK ((len([IdCia])>(0))), CONSTRAINT CK_Trn_ProdConsolidaIdCiaCrea CHECK ((len([IdCiaCrea])>(0))), CONSTRAINT CK_Trn_ProdConsolidaIdEstado CHECK ((len([IdEstado])>(0))), CONSTRAINT CK_Trn_ProdConsolidaIdUsuario CHECK ((len([IdUsuario])>(0))), CONSTRAINT CK_Trn_ProdConsolidaOrigenAdd CHECK ((len([OrigenAdd])>(0))), CONSTRAINT CK_Trn_ProdConsolidaTipDoc CHECK ((len([TipDoc])>(0)))) GO CREATE TABLE Trn_ProdConsDet ( TipDoc VARCHAR(3) DEFAULT ('CPP') NOT NULL, NumCons INT DEFAULT ((0)) NOT NULL, IdCia CHAR(2) NOT NULL, Item INT DEFAULT ((0)) NOT NULL, TipPed VARCHAR(3) DEFAULT ('PED') NOT NULL, Pedido INT DEFAULT ((0)) NOT NULL, IdCiaPed CHAR(2) DEFAULT ('00') NOT NULL, ItemPed INT DEFAULT ((0)) NOT NULL, IdProducto VARCHAR(16) NOT NULL, IdBodega VARCHAR(4) NOT NULL, CdUbic VARCHAR(10), NumTalla VARCHAR(30) NOT NULL, CantPedido DECIMAL(14,4) DEFAULT ((0)) NOT NULL, CantAprob DECIMAL(14,4) DEFAULT ((0)) NOT NULL, Existencias DECIMAL(14,4) DEFAULT ((0)) NOT NULL, CantSaldo DECIMAL(14,4) DEFAULT ((0)) NOT NULL, CantTdo DECIMAL(14,4) DEFAULT ((0)) NOT NULL, CdBodTdo VARCHAR(4) NOT NULL, Tallas BIT DEFAULT ((0)) NOT NULL, IdUnd VARCHAR(4) NOT NULL CONSTRAINT PK_Trn_ProdConsDet PRIMARY KEY CLUSTERED (TipDoc,NumCons,IdCia,Item), CONSTRAINT CK_Trn_ProdConsDetCdBodTdo CHECK ((len([CdBodTdo])>(0))), CONSTRAINT CK_Trn_ProdConsDetIdBodega CHECK ((len([IdBodega])>(0))), CONSTRAINT CK_Trn_ProdConsDetIdCia CHECK ((len([IdCia])>(0))), CONSTRAINT CK_Trn_ProdConsDetIdCiaPed CHECK ((len([IdCiaPed])>(0))), CONSTRAINT CK_Trn_ProdConsDetIdProducto CHECK ((len([IdProducto])>(0))), CONSTRAINT CK_Trn_ProdConsDetIdUnd CHECK ((len([IdUnd])>(0))), CONSTRAINT CK_Trn_ProdConsDetNumTalla CHECK ((len([NumTalla])>(0))), CONSTRAINT CK_Trn_ProdConsDetTipDoc CHECK ((len([TipDoc])>(0))), CONSTRAINT CK_Trn_ProdConsDetTipPed CHECK ((len([TipPed])>(0)))) GO CREATE TABLE Trn_ProdConsInsu ( TipDoc VARCHAR(3) DEFAULT ('CPP') NOT NULL, NumCons INT DEFAULT ((0)) NOT NULL, IdCia CHAR(2) NOT NULL, Item INT DEFAULT ((0)) NOT NULL, TipPed VARCHAR(3) DEFAULT ('PED') NOT NULL, Pedido INT DEFAULT ((0)) NOT NULL, IdCiaPed CHAR(2) DEFAULT ('00') NOT NULL, ItemPed INT DEFAULT ((0)) NOT NULL, IdProducto VARCHAR(16) NOT NULL, IdInsumo VARCHAR(16) NOT NULL, IdTipProc VARCHAR(4) NOT NULL, IdBodega VARCHAR(4) NOT NULL, Cantidad DECIMAL(14,4) DEFAULT ((0)) NOT NULL, Existencias DECIMAL(14,4) DEFAULT ((0)) NOT NULL, CantSaldo DECIMAL(14,4) DEFAULT ((0)) NOT NULL, CantTdo DECIMAL(14,4) DEFAULT ((0)) NOT NULL, CantOrden DECIMAL(14,4) DEFAULT ((0)) NOT NULL, CdBodTdo VARCHAR(4) NOT NULL, CantPed DECIMAL(14,4) DEFAULT ((0)) NOT NULL CONSTRAINT PK_Trn_ProdConsInsu PRIMARY KEY CLUSTERED (TipDoc,NumCons,IdCia,Item), CONSTRAINT CK_Trn_ProdConsInsuCdBodTdo CHECK ((len([CdBodTdo])>(0))), CONSTRAINT CK_Trn_ProdConsInsuIdBodega CHECK ((len([IdBodega])>(0))), CONSTRAINT CK_Trn_ProdConsInsuIdCia CHECK ((len([IdCia])>(0))), CONSTRAINT CK_Trn_ProdConsInsuIdCiaPed CHECK ((len([IdCiaPed])>(0))), CONSTRAINT CK_Trn_ProdConsInsuIdInsumo CHECK ((len([IdInsumo])>(0))), CONSTRAINT CK_Trn_ProdConsInsuIdProducto CHECK ((len([IdProducto])>(0))), CONSTRAINT CK_Trn_ProdConsInsuIdTipProc CHECK ((len([IdTipProc])>(0))), CONSTRAINT CK_Trn_ProdConsInsuTipDoc CHECK ((len([TipDoc])>(0))), CONSTRAINT CK_Trn_ProdConsInsuTipPed CHECK ((len([TipPed])>(0)))) GO CREATE TABLE Trn_ProdConsPed ( TipDoc VARCHAR(3) DEFAULT ('CPP') NOT NULL, NumCons INT DEFAULT ((0)) NOT NULL, IdCia CHAR(2) NOT NULL, Item INT DEFAULT ((0)) NOT NULL, TipPed VARCHAR(3) DEFAULT ('PED') NOT NULL, Pedido INT DEFAULT ((0)) NOT NULL, IdCiaPed CHAR(2) DEFAULT ('00') NOT NULL, TipTdo VARCHAR(3), Traslado INT DEFAULT ((0)) NOT NULL, IdCiaTdo CHAR(2), NumTdoIns INT DEFAULT ((0)) NOT NULL, CdCiaIns CHAR(2) CONSTRAINT PK_Trn_ProdConsPed PRIMARY KEY CLUSTERED (TipDoc,NumCons,IdCia,Item), CONSTRAINT CK_Trn_ProdConsPedIdCia CHECK ((len([IdCia])>(0))), CONSTRAINT CK_Trn_ProdConsPedIdCiaPed CHECK ((len([IdCiaPed])>(0))), CONSTRAINT CK_Trn_ProdConsPedTipDoc CHECK ((len([TipDoc])>(0))), CONSTRAINT CK_Trn_ProdConsPedTipPed CHECK ((len([TipPed])>(0)))) GO CREATE TABLE Trn_ProdConsProc ( TipDoc VARCHAR(3) DEFAULT ('CPP') NOT NULL, NumCons INT DEFAULT ((0)) NOT NULL, IdCia CHAR(2) NOT NULL, Item INT DEFAULT ((0)) NOT NULL, TipPed VARCHAR(3) DEFAULT ('PED') NOT NULL, Pedido INT DEFAULT ((0)) NOT NULL, IdCiaPed CHAR(2) DEFAULT ('00') NOT NULL, ItemPed INT DEFAULT ((0)) NOT NULL, IdProducto VARCHAR(16) NOT NULL, IdBodega VARCHAR(4) NOT NULL, CdUbic VARCHAR(10), NumTalla VARCHAR(30) NOT NULL, CantAprob DECIMAL(14,4) DEFAULT ((0)) NOT NULL, Existencias DECIMAL(14,4) DEFAULT ((0)) NOT NULL, CantSaldo DECIMAL(14,4) DEFAULT ((0)) NOT NULL, CantTdo DECIMAL(14,4) DEFAULT ((0)) NOT NULL, IdTipProc VARCHAR(4) NOT NULL, Cantidad DECIMAL(14,4) DEFAULT ((0)) NOT NULL, Tallas BIT DEFAULT ((0)) NOT NULL, ProcFinal BIT DEFAULT ((0)) NOT NULL CONSTRAINT PK_Trn_ProdConsProc PRIMARY KEY CLUSTERED (TipDoc,NumCons,IdCia,Item), CONSTRAINT CK_Trn_ProdConsProcIdBodega CHECK ((len([IdBodega])>(0))), CONSTRAINT CK_Trn_ProdConsProcIdCia CHECK ((len([IdCia])>(0))), CONSTRAINT CK_Trn_ProdConsProcIdCiaPed CHECK ((len([IdCiaPed])>(0))), CONSTRAINT CK_Trn_ProdConsProcIdProducto CHECK ((len([IdProducto])>(0))), CONSTRAINT CK_Trn_ProdConsProcIdTipProc CHECK ((len([IdTipProc])>(0))), CONSTRAINT CK_Trn_ProdConsProcNumTalla CHECK ((len([NumTalla])>(0))), CONSTRAINT CK_Trn_ProdConsProcTipDoc CHECK ((len([TipDoc])>(0))), CONSTRAINT CK_Trn_ProdConsProcTipPed CHECK ((len([TipPed])>(0)))) GO CREATE TABLE Trn_ProdConsTdo ( TipDoc VARCHAR(3) DEFAULT ('CPP') NOT NULL, NumCons INT DEFAULT ((0)) NOT NULL, IdCia CHAR(2) NOT NULL, Item INT DEFAULT ((0)) NOT NULL, IdProducto VARCHAR(16) NOT NULL, IdBodega VARCHAR(4) NOT NULL, CdUbic VARCHAR(10), NumTalla VARCHAR(30) NOT NULL, CantPedido DECIMAL(14,4) DEFAULT ((0)) NOT NULL, CantAprob DECIMAL(14,4) DEFAULT ((0)) NOT NULL, Existencias DECIMAL(14,4) DEFAULT ((0)) NOT NULL, CantTdo DECIMAL(14,4) DEFAULT ((0)) NOT NULL, Tallas BIT DEFAULT ((0)) NOT NULL, IdUnd VARCHAR(4) NOT NULL CONSTRAINT PK_Trn_ProdConsTdo PRIMARY KEY CLUSTERED (TipDoc,NumCons,IdCia,Item), CONSTRAINT CK_Trn_ProdConsTdoIdBodega CHECK ((len([IdBodega])>(0))), CONSTRAINT CK_Trn_ProdConsTdoIdCia CHECK ((len([IdCia])>(0))), CONSTRAINT CK_Trn_ProdConsTdoIdProducto CHECK ((len([IdProducto])>(0))), CONSTRAINT CK_Trn_ProdConsTdoIdUnd CHECK ((len([IdUnd])>(0))), CONSTRAINT CK_Trn_ProdConsTdoNumTalla CHECK ((len([NumTalla])>(0))), CONSTRAINT CK_Trn_ProdConsTdoTipDoc CHECK ((len([TipDoc])>(0)))) GO ALTER TABLE Trn_ProdConsolida ADD CONSTRAINT FK_Trn_ProdConsolida_adm_Usuarios FOREIGN KEY (IdUsuario) REFERENCES [dbo].[adm_Usuarios] (IdUsuario), CONSTRAINT FK_Trn_ProdConsolida_Bodegas FOREIGN KEY (IdBodega) REFERENCES [dbo].[Bodegas] (IdBodega), CONSTRAINT FK_Trn_ProdConsolida_Bodegas1 FOREIGN KEY (IdBodIns) REFERENCES [dbo].[Bodegas] (IdBodega), CONSTRAINT FK_Trn_ProdConsolida_Companias FOREIGN KEY (IdCia) REFERENCES [dbo].[Companias] (IdCia), CONSTRAINT FK_Trn_ProdConsolida_EstadoDoc FOREIGN KEY (IdEstado) REFERENCES [dbo].[EstadoDoc] (IdEstado), CONSTRAINT FK_Trn_ProdConsolida_Sys_TiposDoc FOREIGN KEY (TipDoc) REFERENCES [dbo].[Sys_TiposDoc] (IdDoc) CREATE NONCLUSTERED INDEX IX_Trn_ProdConsolidaFecha ON Trn_ProdConsolida(Fecha) ALTER TABLE Trn_ProdConsDet ADD CONSTRAINT FK_Trn_ProdConsDet_Bodegas FOREIGN KEY (IdBodega) REFERENCES [dbo].[Bodegas] (IdBodega), CONSTRAINT FK_Trn_ProdConsDet_ProdMcias FOREIGN KEY (IdProducto) REFERENCES [dbo].[ProdMcias] (IdProducto), CONSTRAINT FK_Trn_ProdConsDet_Trn_ProdConsolida FOREIGN KEY (TipDoc,NumCons,IdCia) REFERENCES [dbo].[Trn_ProdConsolida] (TipDoc,NumCons,IdCia) ALTER TABLE Trn_ProdConsInsu ADD CONSTRAINT FK_Trn_ProdConsInsu_Bodegas FOREIGN KEY (IdBodega) REFERENCES [dbo].[Bodegas] (IdBodega), CONSTRAINT FK_Trn_ProdConsInsu_ProdMcias FOREIGN KEY (IdProducto) REFERENCES [dbo].[ProdMcias] (IdProducto), CONSTRAINT FK_Trn_ProdConsInsu_ProdMcias1 FOREIGN KEY (IdInsumo) REFERENCES [dbo].[ProdMcias] (IdProducto), CONSTRAINT FK_Trn_ProdConsInsu_TiposProceso FOREIGN KEY (IdTipProc) REFERENCES [dbo].[TiposProceso] (IdTipProc), CONSTRAINT FK_Trn_ProdConsInsu_Trn_ProdConsolida FOREIGN KEY (TipDoc,NumCons,IdCia) REFERENCES [dbo].[Trn_ProdConsolida] (TipDoc,NumCons,IdCia) ALTER TABLE Trn_ProdConsPed ADD CONSTRAINT FK_Trn_ProdConsPed_Trn_ProdConsolida FOREIGN KEY (TipDoc,NumCons,IdCia) REFERENCES [dbo].[Trn_ProdConsolida] (TipDoc,NumCons,IdCia) ALTER TABLE Trn_ProdConsProc ADD CONSTRAINT FK_Trn_ProdConsProc_Bodegas FOREIGN KEY (IdBodega) REFERENCES [dbo].[Bodegas] (IdBodega), CONSTRAINT FK_Trn_ProdConsProc_ProdMcias FOREIGN KEY (IdProducto) REFERENCES [dbo].[ProdMcias] (IdProducto), CONSTRAINT FK_Trn_ProdConsProc_TiposProceso FOREIGN KEY (IdTipProc) REFERENCES [dbo].[TiposProceso] (IdTipProc), CONSTRAINT FK_Trn_ProdConsProc_Trn_ProdConsolida FOREIGN KEY (TipDoc,NumCons,IdCia) REFERENCES [dbo].[Trn_ProdConsolida] (TipDoc,NumCons,IdCia) ALTER TABLE Trn_ProdConsTdo ADD CONSTRAINT FK_Trn_ProdConsTdo_Bodegas FOREIGN KEY (IdBodega) REFERENCES [dbo].[Bodegas] (IdBodega), CONSTRAINT FK_Trn_ProdConsTdo_ProdMcias FOREIGN KEY (IdProducto) REFERENCES [dbo].[ProdMcias] (IdProducto), CONSTRAINT FK_Trn_ProdConsTdo_Trn_ProdConsolida FOREIGN KEY (TipDoc,NumCons,IdCia) REFERENCES [dbo].[Trn_ProdConsolida] (TipDoc,NumCons,IdCia)