##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)
);