##01/ago/07 alter table prydetcobro add (colaborador varchar2(10)); UPDATE PRYDETCOBRO SET COLABORADOR='MVILLA'; ALTER TABLE PRYDETCOBRO MODIFY COLABORADOR NOT NULL; ALTER TABLE PRYDETCOBRO DROP CONSTRAINT PRYDETCOBRO_PK; ALTER TABLE PRYDETCOBRO ADD CONSTRAINT PRYDETCOBRO_PK PRIMARY KEY (DOCUMENTO, PROYECTO, TIPOLABOR, FECHA, COLABORADOR); ##03/OCT/07 alter table prydetcobro modify observaciones varchar2(400); alter table prydetallelabor add(laborcobroref number(2)); update prydetallelabor dl set laborcobroref = (select lc.labor from prylaborcobrable lc where lc.informe = dl.informe and lc.fecha = dl.fecha and lc.laborref = dl.labor); alter table prylaborcobrable drop column laborref; alter table prylaborcobrable add (control varchar2(1) default 'S' not null); ##24/oct/07 alter table prydetcobro modify observaciones varchar2(1000); --##21 Feb 08 GUV --Consecutivos de proyectos para envio de informes CREATE TABLE PRYCONSECUTIVO( Cia Char(3) Not Null, Clase Varchar2(1) Not Null Constraint PryConsecutivo_Clase_Ck Check (Clase In ('T','I','B', 'C', 'A')), Documento Number(10) Not Null, DocsBloque Number(5) Not Null, Constraint PryConsecutivo_Pk Primary Key (Cia, Clase) ) INITRANS 1 STORAGE ( INITIAL 10K NEXT 10K ); CREATE TABLE PRYBLOQUES ( Bloque Varchar2(10) Not Null, Cia Char(3) Not Null, Clase Varchar2(1) Not Null, Inicio Number(10) Not Null, Cantidad Number(5) Not Null, Actual Number(10) Not Null, Constraint PryBloques_Pk PRIMARY KEY (Bloque, Cia, Clase, Inicio) ) INITRANS 1 STORAGE ( INITIAL 10K NEXT 10K ); ALTER TABLE PRYBLOQUES ADD CONSTRAINT PRYCONSECUTIVO_BLOQUES_FK FOREIGN KEY (CIA, CLASE) REFERENCES PRYCONSECUTIVO (CIA, CLASE); Insert Into PryConsecutivo(Cia, Clase, Documento, DocsBloque) Select Cia, 'T', NumTar+1, 100 From PryParametros; Insert Into PryConsecutivo(Cia, Clase, Documento, DocsBloque) Select Cia, 'I', NumInforme+1, 100 From PryParametros; Insert Into PryConsecutivo(Cia, Clase, Documento, DocsBloque) Select Cia, 'B', NumBitacora+1, 1000 From PryParametros; Insert Into PryConsecutivo(Cia, Clase, Documento, DocsBloque) Select Cia, 'C', NumCobro+1, 100 From PryParametros; Insert Into PryConsecutivo(Cia, Clase, Documento, DocsBloque) Select Cia, 'A', 1, 1000 From PryParametros; Commit; alter table pryparametros add (Bloque Varchar2(10) Default 'CENTRAL' Not Null); alter table pryparametros drop column numtar; alter table pryparametros drop column numinforme; alter table pryparametros drop column numbitacora; alter table pryparametros drop column numcobro; --Ojo pq no tiene llave primaria alter table pryparametros add constraint pryparametros_pk primary key (cia); @proyectos\pryconsec #CR 27/2/08 alter table prycolaborador add (usuario varchar2(30) default 'X' not null); alter table prycolaborador add (estado char(1) default 'A' not null); alter table prycolaborador add CONSTRAINT PRYCOLABORADOR_ESTADO_CK CHECK (ESTADO IN ('A','I')); #CR 05/03/08 # crea tablas de act /*==============================================================*/ /* Table: PRYACT */ /*==============================================================*/ CREATE TABLE PRYACT ( ACTUALIZACION NUMBER(10) NOT NULL, FECHA DATE NOT NULL, COLABORADOR VARCHAR2(10) NOT NULL, SISTEMA VARCHAR2(10) NOT NULL, MODULO VARCHAR2(4) NOT NULL, PROPOSITO VARCHAR2(512) NOT NULL, RESTRICCIONES VARCHAR2(512), COMENTARIOS VARCHAR2(512), CONSTRAINT PRYACT_PK PRIMARY KEY (ACTUALIZACION) ); /*==============================================================*/ /* Table: PRYACTCLIENTE */ /*==============================================================*/ CREATE TABLE PRYACTCLIENTE ( SISTEMA VARCHAR2(10) NOT NULL, ACTUALIZACION NUMBER(10) NOT NULL, LINEA NUMBER(10) NOT NULL, CONSTRAINT PRYACTCLIENTE_PK PRIMARY KEY (SISTEMA) ); /*==============================================================*/ /* Table: PRYACTDET */ /*==============================================================*/ CREATE TABLE PRYACTDET ( ACTUALIZACION NUMBER(10) NOT NULL, LINEA NUMBER(10) NOT NULL, INSTRUCCION VARCHAR2(4000) NOT NULL, COMENTARIO VARCHAR2(512), CONDICION VARCHAR2(100), ERROR VARCHAR2(1000), CONSTRAINT PRYACTDET_PK PRIMARY KEY (ACTUALIZACION, LINEA) );