/*==============================================================*/
/* Database name:  FITBANK PHYSICAL DATA MODEL                  */
/* DBMS name:      IBM DB2 UDB 5.x Common Server                */
/* Created on:     28/10/2009 17:38:17 AM                       */
/*==============================================================*/
-- SQL script that update fitBank squema

-- To run this script from the SQL Plus, perform the following steps:
-- 1. connect to the database
-- 2. set define character to !
--      "set define !"
-- 3. issue the command "@ <script-name>"
--    where <script-name> represents the name of this script


-- fitbank201062001.log


alter table TCODIGOSCOMENTARIOID
   drop constraint FKTIPCOMENCOD;

alter table TTIPOSCOMENTARIO
   drop constraint FKTIPCOMENID;

alter table TTIPOSCOMENTARIOID
   drop primary key cascade;

drop table TTIPOSCOMENTARIOID cascade constraints;

/*==============================================================*/
/* Table: TTIPOSCOMENTARIOID                                    */
/*==============================================================*/
create table TTIPOSCOMENTARIOID  (
   CTIPOCOMENTARIO      VARCHAR(3)                     not null
);

comment on table TTIPOSCOMENTARIOID is
'Id de los Tipos de comentario que clasifican a los codigos de comentario en Internos, Internos, Externos, Reclamos, Mercadeo.';

comment on column TTIPOSCOMENTARIOID.CTIPOCOMENTARIO is
'Tipos de comentario que clasifican a los codigos de comentario en Internos, Externos, Reclamos, Mercadeo.';

alter table TTIPOSCOMENTARIOID
   add constraint IPKTIPCOMENID primary key (CTIPOCOMENTARIO);


alter table TTIPOSCOMENTARIO
   drop constraint FKIDITIPCOMEN;

alter table TTIPOSCOMENTARIO
   drop constraint FKTIPCOMENID;

alter table TTIPOSCOMENTARIO
   drop primary key cascade;

drop table TTIPOSCOMENTARIO cascade constraints;

/*==============================================================*/
/* Table: TTIPOSCOMENTARIO                                      */
/*==============================================================*/
create table TTIPOSCOMENTARIO  (
   CIDIOMA              VARCHAR(3)                     not null,
   CTIPOCOMENTARIO      VARCHAR(3)                     not null,
   FHASTA               TIMESTAMP                       not null,
   VERSIONCONTROL       INTEGER                         default 0,
   FDESDE               TIMESTAMP                       not null,
   DESCRIPCION          VARCHAR(30)                    not null
);

comment on table TTIPOSCOMENTARIO is
'Descripcion de los tipos de comentario que clasifican a los codigos de comentario en Internos, Externos, Reclamos, Cobranzas, Mercadeo.';

comment on column TTIPOSCOMENTARIO.CIDIOMA is
'Codigo de idioma';

comment on column TTIPOSCOMENTARIO.CTIPOCOMENTARIO is
'Tipos de comentario que clasifican a los codigos de comentario en Internos, Externos, Reclamos, Cobranzas, Mercadeo.';

comment on column TTIPOSCOMENTARIO.FHASTA is
'Fecha hasta la que esta vigente la descripcion del codigo identificativo de comentarios';

comment on column TTIPOSCOMENTARIO.VERSIONCONTROL is
'Manejo de optimistic loking';

comment on column TTIPOSCOMENTARIO.FDESDE is
'Fecha desde la que esta vigente la descripcion del codigo identificativo de comentarios';

comment on column TTIPOSCOMENTARIO.DESCRIPCION is
'Descripcion del codigo identificativo de comentarios';

alter table TTIPOSCOMENTARIO
   add constraint IPKTIPCOMEN primary key (CIDIOMA, CTIPOCOMENTARIO, FHASTA);

alter table TTIPOSCOMENTARIO
   add constraint FKIDITIPCOMEN foreign key (CIDIOMA)
      references TIDIOMASID (CIDIOMA);

alter table TTIPOSCOMENTARIO
   add constraint FKTIPCOMENID foreign key (CTIPOCOMENTARIO)
      references TTIPOSCOMENTARIOID (CTIPOCOMENTARIO);


alter table TCODIGOSCOMENTARIO
   drop constraint FKCODCOMENID;

alter table TCODIGOSCOMENTARIOID
   drop constraint FKTIPCOMENCOD;

alter table TCUENTACOMENTARIOS
   drop constraint FKCTACOMEN;

alter table TPERSONACOMENTARIOS
   drop constraint FKPERCOMENCOD;

alter table TCODIGOSCOMENTARIOID
   drop primary key cascade;

drop table TCODIGOSCOMENTARIOID cascade constraints;

/*==============================================================*/
/* Table: TCODIGOSCOMENTARIOID                                  */
/*==============================================================*/
create table TCODIGOSCOMENTARIOID  (
   CCODIGOCOMENTARIO    VARCHAR(4)                     not null,
   CTIPOCOMENTARIO      VARCHAR(3)                     not null
);

comment on table TCODIGOSCOMENTARIOID is
'Codigo identificativo del comentario';

comment on column TCODIGOSCOMENTARIOID.CCODIGOCOMENTARIO is
'Codigo identificativo del comentario';

comment on column TCODIGOSCOMENTARIOID.CTIPOCOMENTARIO is
'Tipos de comentario que clasifican a los codigos de comentario en Internos, Externos, Reclamos, Mercadeo.';

alter table TCODIGOSCOMENTARIOID
   add constraint IPKCODCOMENID primary key (CCODIGOCOMENTARIO, CTIPOCOMENTARIO);

alter table TCODIGOSCOMENTARIOID
   add constraint FKTIPCOMENCOD foreign key (CTIPOCOMENTARIO)
      references TTIPOSCOMENTARIOID (CTIPOCOMENTARIO);


alter table TCODIGOSCOMENTARIO
   drop constraint FKCODCOMENID;

alter table TCODIGOSCOMENTARIO
   drop constraint FKIDICODCOMEN;

alter table TCODIGOSCOMENTARIO
   drop primary key cascade;

drop table TCODIGOSCOMENTARIO cascade constraints;

/*==============================================================*/
/* Table: TCODIGOSCOMENTARIO                                    */
/*==============================================================*/
create table TCODIGOSCOMENTARIO  (
   CIDIOMA              VARCHAR(3)                     not null,
   CCODIGOCOMENTARIO    VARCHAR(4)                     not null,
   CTIPOCOMENTARIO      VARCHAR(3)                     not null,
   FHASTA               TIMESTAMP                       not null,
   VERSIONCONTROL       INTEGER                         default 0,
   FDESDE               TIMESTAMP                       not null,
   DESCRIPCION          VARCHAR(30)                    not null,
   TEXTOCOMENTARIO      VARCHAR(2000)
);

comment on table TCODIGOSCOMENTARIO is
'Descripcion por idioma de los codigos identificativos de comentarios de personas y operaciones';

comment on column TCODIGOSCOMENTARIO.CIDIOMA is
'Codigo del idioma';

comment on column TCODIGOSCOMENTARIO.CCODIGOCOMENTARIO is
'Codigo identificativo del comentario de personas y operaciones';

comment on column TCODIGOSCOMENTARIO.CTIPOCOMENTARIO is
'Tipos de comentario que clasifican a los codigos de comentario en Internos, Externos, Reclamos, Mercadeo.';

comment on column TCODIGOSCOMENTARIO.FHASTA is
'Fecha hasta la que esta vigente la descripcion del codigo identificativo de comentarios';

comment on column TCODIGOSCOMENTARIO.VERSIONCONTROL is
'Manejo de optimistic loking';

comment on column TCODIGOSCOMENTARIO.FDESDE is
'Fecha desde la que esta vigente la descripcion del codigo identificativo de comentarios';

comment on column TCODIGOSCOMENTARIO.DESCRIPCION is
'Descripcion del codigo identificativo de comentarios';

comment on column TCODIGOSCOMENTARIO.TEXTOCOMENTARIO is
'Texto asociado al codigo de comentarios';

alter table TCODIGOSCOMENTARIO
   add constraint IPKCODCOMEN primary key (CIDIOMA, CCODIGOCOMENTARIO, CTIPOCOMENTARIO, FHASTA);

alter table TCODIGOSCOMENTARIO
   add constraint FKCODCOMENID foreign key (CCODIGOCOMENTARIO, CTIPOCOMENTARIO)
      references TCODIGOSCOMENTARIOID (CCODIGOCOMENTARIO, CTIPOCOMENTARIO);

alter table TCODIGOSCOMENTARIO
   add constraint FKIDICODCOMEN foreign key (CIDIOMA)
      references TIDIOMASID (CIDIOMA);


alter table TPERSONACOMENTARIOS
   drop constraint FKPERCOMEN;

alter table TPERSONACOMENTARIOS
   drop constraint FKPERCOMENCOD;

alter table TPERSONACOMENTARIOS
   drop constraint FKPERCOMENUSU;

drop index IPERCOMTIPCOM;

alter table TPERSONACOMENTARIOS
   drop primary key cascade;

drop table TPERSONACOMENTARIOS cascade constraints;

/*==============================================================*/
/* Table: TPERSONACOMENTARIOS                                   */
/*==============================================================*/
create table TPERSONACOMENTARIOS  (
   CPERSONA             INTEGER                       not null,
   SCOMENTARIO          INTEGER                       not null,
   FHASTA               TIMESTAMP                       not null,
   VERSIONCONTROL       INTEGER                         default 0,
   FDESDE               TIMESTAMP                       not null,
   CCODIGOCOMENTARIO    VARCHAR(4)                     not null,
   CTIPOCOMENTARIO      VARCHAR(3)                     not null,
   CUSUARIO             VARCHAR(12),
   COMENTARIO           VARCHAR(2000),
   FINGRESO             DATE                            not null,
   FCADUCIDAD           DATE,
   ESTADOCOMENTARIO     VARCHAR(3)                    default 'ING',
   CONTADORALERTA       INTEGER,
   ACUMULADOALERTA      DECIMAL(19,6)
);

comment on table TPERSONACOMENTARIOS is
'Comentarios sobre la persona natural o juridica';

comment on column TPERSONACOMENTARIOS.CPERSONA is
'Codigo de la persona';

comment on column TPERSONACOMENTARIOS.SCOMENTARIO is
'Secuencia del comentario sobre la persona natural o juridica';

comment on column TPERSONACOMENTARIOS.FHASTA is
'Fecha hasta la que estan vigentes el comentario sobre la persona';

comment on column TPERSONACOMENTARIOS.VERSIONCONTROL is
'Manejo de optimistic loking';

comment on column TPERSONACOMENTARIOS.FDESDE is
'Fecha desde la que estan vigentes el comentario sobre la persona';

comment on column TPERSONACOMENTARIOS.CCODIGOCOMENTARIO is
'Codigo identificativo del comentario';

comment on column TPERSONACOMENTARIOS.CTIPOCOMENTARIO is
'Tipos de comentario que clasifican a los codigos de comentario en Internos, Externos, Mercadeo.';

comment on column TPERSONACOMENTARIOS.CUSUARIO is
'Codigo de usuario';

comment on column TPERSONACOMENTARIOS.COMENTARIO is
'Comentario sobre la persona';

comment on column TPERSONACOMENTARIOS.FINGRESO is
'Fecha de ingreso del comentario';

comment on column TPERSONACOMENTARIOS.FCADUCIDAD is
'Fecha de caducidad del comentario';

comment on column TPERSONACOMENTARIOS.ESTADOCOMENTARIO is
'Estado del Comentario ING=Ingresado,ANU=Anulado,IMP=Impreso';

comment on column TPERSONACOMENTARIOS.CONTADORALERTA is
'Contador de alertas cuando el mensaje es repetitivo';

comment on column TPERSONACOMENTARIOS.ACUMULADOALERTA is
'Valor acumulado al presentar la alerta';

alter table TPERSONACOMENTARIOS
   add constraint CKESTADOCOMENTARIOPER check (ESTADOCOMENTARIO is null or (ESTADOCOMENTARIO = upper(ESTADOCOMENTARIO) and ESTADOCOMENTARIO in ('ING','IMP','ANU')));

alter table TPERSONACOMENTARIOS
   add constraint IPKPERCOMEN primary key (CPERSONA, SCOMENTARIO, FHASTA);

/*==============================================================*/
/* Index: IPERCOMTIPCOM                                         */
/*==============================================================*/
create index IPERCOMTIPCOM on TPERSONACOMENTARIOS (
   FCADUCIDAD ASC,
   CTIPOCOMENTARIO ASC,
   FHASTA ASC
);

alter table TPERSONACOMENTARIOS
   add constraint FKPERCOMEN foreign key (CPERSONA)
      references TPERSONAID (CPERSONA);

alter table TPERSONACOMENTARIOS
   add constraint FKPERCOMENCOD foreign key (CCODIGOCOMENTARIO, CTIPOCOMENTARIO)
      references TCODIGOSCOMENTARIOID (CCODIGOCOMENTARIO, CTIPOCOMENTARIO);

alter table TPERSONACOMENTARIOS
   add constraint FKPERCOMENUSU foreign key (CUSUARIO)
      references TUSUARIOSID (CUSUARIO);


alter table TCUENTACOMENTARIOS
   drop constraint FKCTACOMEN;

alter table TCUENTACOMENTARIOS
   drop constraint FKCTACOMENCOD;

alter table TCUENTACOMENTARIOS
   drop constraint FKCTACOMENUSU;

drop index ICTACOMTIPCOM;

alter table TCUENTACOMENTARIOS
   drop primary key cascade;

drop table TCUENTACOMENTARIOS cascade constraints;

/*==============================================================*/
/* Table: TCUENTACOMENTARIOS                                    */
/*==============================================================*/
create table TCUENTACOMENTARIOS  (
   CCUENTA              VARCHAR(19)                    not null,
   FHASTA               TIMESTAMP                       not null,
   CPERSONA_COMPANIA    INTEGER                       not null,
   SCOMENTARIO          INTEGER                       not null,
   VERSIONCONTROL       INTEGER                         default 0,
   FDESDE               TIMESTAMP                       not null,
   CCODIGOCOMENTARIO    VARCHAR(4)                     not null,
   CTIPOCOMENTARIO      VARCHAR(3),
   COMENTARIO           VARCHAR(2000),
   CUSUARIO             VARCHAR(12)                    not null,
   FINGRESO             DATE                            not null,
   FCADUCIDAD           DATE,
   ESTADOCOMENTARIO     VARCHAR(3)                    default 'ING'
);

comment on table TCUENTACOMENTARIOS is
'Comentarios ingresados para la cuenta';

comment on column TCUENTACOMENTARIOS.CCUENTA is
'Numero de cuenta';

comment on column TCUENTACOMENTARIOS.FHASTA is
'Fecha hasta la que estan vigentes el comentario sobre la cuenta';

comment on column TCUENTACOMENTARIOS.CPERSONA_COMPANIA is
'Codigo de persona de la compania';

comment on column TCUENTACOMENTARIOS.SCOMENTARIO is
'Secuencia del comentario sobre la cuenta';

comment on column TCUENTACOMENTARIOS.VERSIONCONTROL is
'Manejo de optimistic loking';

comment on column TCUENTACOMENTARIOS.FDESDE is
'Fecha desde la que estan vigentes el comentario sobre la cuenta';

comment on column TCUENTACOMENTARIOS.CCODIGOCOMENTARIO is
'Codigo identificativo del comentario';

comment on column TCUENTACOMENTARIOS.CTIPOCOMENTARIO is
'Tipos de comentario que clasifican a los codigos de comentario en Internos, Externos, Reclamos, Mercadeo.';

comment on column TCUENTACOMENTARIOS.COMENTARIO is
'Comentario sobre la cuenta';

comment on column TCUENTACOMENTARIOS.CUSUARIO is
'Codigo del usuario que ingresa o modifica el comentario';

comment on column TCUENTACOMENTARIOS.FINGRESO is
'Fecha de ingreso del comentario';

comment on column TCUENTACOMENTARIOS.FCADUCIDAD is
'Fecha de caducidad del comentario';

comment on column TCUENTACOMENTARIOS.ESTADOCOMENTARIO is
'Estado del Comentario ING=Ingresado,ANU=Anulado,IMP=Impreso';

alter table TCUENTACOMENTARIOS
   add constraint CKESTADOCOMENTARIOCTA check (ESTADOCOMENTARIO is null or (ESTADOCOMENTARIO = upper(ESTADOCOMENTARIO) and ESTADOCOMENTARIO in ('ING','IMP','ANU')));

alter table TCUENTACOMENTARIOS
   add constraint IPKCTACOMEN primary key (CCUENTA, FHASTA, CPERSONA_COMPANIA, SCOMENTARIO);

/*==============================================================*/
/* Index: ICTACOMTIPCOM                                         */
/*==============================================================*/
create index ICTACOMTIPCOM on TCUENTACOMENTARIOS (
   FHASTA ASC,
   FCADUCIDAD ASC
);

alter table TCUENTACOMENTARIOS
   add constraint FKCTACOMEN foreign key (CCUENTA, CPERSONA_COMPANIA)
      references TCUENTAID (CCUENTA, CPERSONA_COMPANIA);

alter table TCUENTACOMENTARIOS
   add constraint FKCTACOMENCOD foreign key (CCODIGOCOMENTARIO, CTIPOCOMENTARIO)
      references TCODIGOSCOMENTARIOID (CCODIGOCOMENTARIO, CTIPOCOMENTARIO);

alter table TCUENTACOMENTARIOS
   add constraint FKCTACOMENUSU foreign key (CUSUARIO)
      references TUSUARIOSID (CUSUARIO);


/*==============================================================*/
/* View: VPERSONACOMENTARIOS                                    */
/*==============================================================*/
create or replace view VPERSONACOMENTARIOS as
select
   a.CPERSONA,
   b.CCODIGOCOMENTARIO,
   b.DESCRIPCION DESCRIPCIONCOMENTARIO,
   COALESCE(a.COMENTARIO,b.TEXTOCOMENTARIO) TEXTOCOMENTARIO,
   a.CUSUARIO,
   a.FINGRESO,
   a.FHASTA,
   a.FCADUCIDAD,
   b.CIDIOMA,
   ' ' CCUENTA,
   ESTADOCOMENTARIO,
   rownum SECUENCIA
from
   TPERSONACOMENTARIOS a,
   TCODIGOSCOMENTARIO b
where a.ccodigocomentario != '9997'
  and a.ccodigocomentario = b.ccodigocomentario
  and a.fCaducidad >= trunc(sysdate)
  and a.fHasta = to_timestamp('2999-12-31','yyyy-mm-dd')
  and b.fHasta = to_timestamp('2999-12-31','yyyy-mm-dd');

comment on table VPERSONACOMENTARIOS is
'Vista de los comentarios ingresados para personas y sus cuentas';

comment on column VPERSONACOMENTARIOS.CPERSONA is
'Codigo de la persona';

comment on column VPERSONACOMENTARIOS.CCODIGOCOMENTARIO is
'Codigo identificativo del comentario de personas y operaciones';

comment on column VPERSONACOMENTARIOS.DESCRIPCIONCOMENTARIO is
'Descripcion del codigo identificativo de comentarios';

comment on column VPERSONACOMENTARIOS.TEXTOCOMENTARIO is
'Texto del comentario';

comment on column VPERSONACOMENTARIOS.CUSUARIO is
'Codigo de usuario';

comment on column VPERSONACOMENTARIOS.FINGRESO is
'Fecha de ingreso del comentario';

comment on column VPERSONACOMENTARIOS.FHASTA is
'Fecha hasta la que estan vigentes el comentario sobre la persona';

comment on column VPERSONACOMENTARIOS.FCADUCIDAD is
'Fecha de caducidad del comentario';

comment on column VPERSONACOMENTARIOS.CIDIOMA is
'Codigo del idioma';

comment on column VPERSONACOMENTARIOS.CCUENTA is
'Codigo de cuente del comentario';

comment on column VPERSONACOMENTARIOS.ESTADOCOMENTARIO is
'Estado del Comentario ING=Ingresado,ANU=Anulado,IMP=Impreso';

comment on column VPERSONACOMENTARIOS.SECUENCIA is
'Numero consecutivo del registro';