/*==============================================================*/
/* Database name:  FITBANK PHYSICAL DATA MODEL                  */
/* DBMS name:      IBM DB2 UDB 5.x Common Server                */
/* Created on:     26/07/2011  15:06:12 PM                      */
/*==============================================================*/
-- 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


-- fitbank201067019.log


/*==============================================================*/
/* Table: TPRODUCTODEFINICIONPLAZOS                             */
/*==============================================================*/
alter table TPRODUCTODEFINICIONPLAZOS add   ASIGNACIONES         INTEGER;
alter table TPRODUCTODEFINICIONPLAZOS add   MARGENASIGNACION     DECIMAL(10,6);

comment on table TPRODUCTODEFINICIONPLAZOS is
'Definicion de los plazos para el calculo del vencimiento de una cuenta de captaciones a la vista y el tarifario de tasas asignado, se utiliza en colocaciones para indicar el numero de asignaciones del saldo de la aportacion que determinan el tarifario del producto.';

comment on column TPRODUCTODEFINICIONPLAZOS.ASIGNACIONES is
'Numero de asignaciones del saldo de la aportacion que determinan el tarifario del producto de colocaciones.';

comment on column TPRODUCTODEFINICIONPLAZOS.MARGENASIGNACION is
'Margen a agregar al tarifario de acuerdo al plazo y a la asignacion';


alter table TPROSPECTONATURALES
   drop constraint FKPROSNATACT;

alter table TPROSPECTONATURALES
   drop constraint FKPROSNATCIU;

alter table TPROSPECTONATURALES
   drop constraint FKPROSNATMON;

alter table TPROSPECTONATURALES
   drop constraint FKPROSNATOPEESP;

alter table TPROSPECTONATURALES
   drop constraint FKPROSNATPAR;

alter table TPROSPECTONATURALES
   drop constraint FKPROSNATRAZAPE;

alter table TPROSPECTONATURALES
   drop constraint FKPROSNATTIPCIU;

alter table TPROSPECTONATURALES
   drop constraint FKPROSNATUSU;

alter table TPROSPECTONATURALES
   drop primary key;

drop table TPROSPECTONATURALES;

/*==============================================================*/
/* Table: TPROSPECTONATURALES                                   */
/*==============================================================*/
create table TPROSPECTONATURALES  (
   IDENTIFICACION       VARCHAR(19)                    not null,
   FHASTA               TIMESTAMP                       not null,
   VERSIONCONTROL       BIGINT                     default 0,
   FDESDE               TIMESTAMP                       not null,
   PROSPECTO            VARCHAR(1)                    default '1' not null,
   NOMBRES              VARCHAR(250)                   not null,
   APELLIDOS            VARCHAR(250)                   not null,
   FECHANACIMIENTO      DATE                            not null,
   GENERO               VARCHAR(1)                     not null,
   CTIPOCIUDADANO       VARCHAR(30),
   CTIPOPERSONA         VARCHAR(3),
   CACTIVIDAD           VARCHAR(6),
   CPAIS                VARCHAR(3),
   CPROVINCIA           VARCHAR(3),
   CCANTON              VARCHAR(6),
   CPARROQUIA           VARCHAR(6),
   CCIUDAD              VARCHAR(6),
   CTIPOOPERACIONESPERADA INTEGER,
   CRAZONAPERTURA       VARCHAR(3),
   CMONEDA              VARCHAR(3)                     not null,
   MONTOTOTALESPERADO   DECIMAL(19,6),
   CUSUARIO             VARCHAR(12),
   ANTECEDENTESFINANCIEROS VARCHAR(250),
   VINCULACION          VARCHAR(1),
   BOLETINADA           VARCHAR(1),
   EXPOSICIONPUBLICA    VARCHAR(1),
   PUNTAJETOTAL         INTEGER,
   RESULTADOCALIFICACION VARCHAR(1),
   PERFIL               VARCHAR(10),
   ESTRATO              INTEGER,
   FAPROBACION          DATE,
   ESTADOAUTORIZACION   VARCHAR(3),
   OBSERVACIONES        VARCHAR(200)
);

comment on table TPROSPECTONATURALES is
'Datos de las persona naturales que son prospectos de cliente';

comment on column TPROSPECTONATURALES.IDENTIFICACION is
'Identificacion de la persona natural prospecto ';

comment on column TPROSPECTONATURALES.FHASTA is
'Fecha hasta la que estan vigentes los datos de precalificacion de un prospecto persnoa natural.';

comment on column TPROSPECTONATURALES.VERSIONCONTROL is
'Manejo de optimistic locking.';

comment on column TPROSPECTONATURALES.FDESDE is
'Fecha desde la que estan vigentes los datos de precalificacion de un prospecto persnoa natural.';

comment on column TPROSPECTONATURALES.PROSPECTO is
'Indica si el registro de calificacion corresponde a una persona prospecto o a una persona ya existente';

comment on column TPROSPECTONATURALES.NOMBRES is
'Nombre';

comment on column TPROSPECTONATURALES.APELLIDOS is
'Apellidos';

comment on column TPROSPECTONATURALES.FECHANACIMIENTO is
'Fecha de nacimiento';

comment on column TPROSPECTONATURALES.GENERO is
'Genero de la persona natural';

comment on column TPROSPECTONATURALES.CTIPOCIUDADANO is
'Tipo de ciudadano';

comment on column TPROSPECTONATURALES.CTIPOPERSONA is
'Codigo del tipo de persona';

comment on column TPROSPECTONATURALES.CACTIVIDAD is
'Codigo de actividad';

comment on column TPROSPECTONATURALES.CPAIS is
'Codigo del pais';

comment on column TPROSPECTONATURALES.CPROVINCIA is
'Codigo de la provincia';

comment on column TPROSPECTONATURALES.CCANTON is
'Codigo de canton';

comment on column TPROSPECTONATURALES.CPARROQUIA is
'Codigo de parroquia';

comment on column TPROSPECTONATURALES.CCIUDAD is
'Codigo de ciudad';

comment on column TPROSPECTONATURALES.CTIPOOPERACIONESPERADA is
'Codigo del tipo de operacion de valores esperados que realizara la persona natural o juridica';

comment on column TPROSPECTONATURALES.CRAZONAPERTURA is
'Codigo de la razon de apertura de una persona natural o juridica';

comment on column TPROSPECTONATURALES.CMONEDA is
'Codigo de moneda del monto esperado';

comment on column TPROSPECTONATURALES.MONTOTOTALESPERADO is
'Monto total esperado para el movimiento';

comment on column TPROSPECTONATURALES.CUSUARIO is
'Codigo del usuario que realiza el ingreso de los datos';

comment on column TPROSPECTONATURALES.ANTECEDENTESFINANCIEROS is
'Comentarios sobre los antecedentes financieros de la persona';

comment on column TPROSPECTONATURALES.VINCULACION is
'Indica si la persona tiene vinculacion con la institucion';

comment on column TPROSPECTONATURALES.BOLETINADA is
'Indica si la persona esta boletinada en alguna lista de control';

comment on column TPROSPECTONATURALES.EXPOSICIONPUBLICA is
'Indica si la persona tiene cargo publico';

comment on column TPROSPECTONATURALES.PUNTAJETOTAL is
'Total puntaje obtenido de la calificacion para preseleccion';

comment on column TPROSPECTONATURALES.RESULTADOCALIFICACION is
'Resultado de la precalificacion H = Alto, M = Medio, L = Bajo.';

comment on column TPROSPECTONATURALES.PERFIL is
'Perfil economico de la persona para determinar el limite operacional.';

comment on column TPROSPECTONATURALES.ESTRATO is
'Estrato socioeconomico de la persona para determinar el limite operacional.';

comment on column TPROSPECTONATURALES.FAPROBACION is
'Fecha de aprobacion de la precalificacion';

comment on column TPROSPECTONATURALES.ESTADOAUTORIZACION is
'Estado de la autorizacion de precalificacion: APR = Aprobada, NEG = Negada';

comment on column TPROSPECTONATURALES.OBSERVACIONES is
'Texto de observaciones sobre la precalificacion de la persona prospecto';

alter table TPROSPECTONATURALES
   add constraint CKPROSPECTO check (PROSPECTO = upper(PROSPECTO) and PROSPECTO in ('1','0'));

alter table TPROSPECTONATURALES
   add constraint CKGENEROPROS check (GENERO = upper(GENERO) and GENERO in ('F','M'));

alter table TPROSPECTONATURALES
   add constraint CKRESULTADOCALIFICACION check (RESULTADOCALIFICACION is null or (RESULTADOCALIFICACION = upper(RESULTADOCALIFICACION) and RESULTADOCALIFICACION in ('H','M','L')));

alter table TPROSPECTONATURALES
   add constraint CKESTADOAUTORIZACIONPROS check (ESTADOAUTORIZACION is null or (ESTADOAUTORIZACION = upper(ESTADOAUTORIZACION) and ESTADOAUTORIZACION in ('APR','NEG')));

alter table TPROSPECTONATURALES
   add constraint IPKPROSNATUR primary key (IDENTIFICACION, FHASTA);

alter table TPROSPECTONATURALES
   add constraint FKPROSNATACT foreign key (CTIPOPERSONA, CACTIVIDAD)
      references TACTIVIDADESID (CTIPOPERSONA, CACTIVIDAD);

alter table TPROSPECTONATURALES
   add constraint FKPROSNATCIU foreign key (CPAIS, CPROVINCIA, CCIUDAD)
      references TCIUDADESID (CPAIS, CPROVINCIA, CCIUDAD);

alter table TPROSPECTONATURALES
   add constraint FKPROSNATMON foreign key (CMONEDA)
      references TMONEDASID (CMONEDA);

alter table TPROSPECTONATURALES
   add constraint FKPROSNATOPEESP foreign key (CTIPOOPERACIONESPERADA)
      references TTIPOSOPERACIONESPERADAID (CTIPOOPERACIONESPERADA);

alter table TPROSPECTONATURALES
   add constraint FKPROSNATPAR foreign key (CPARROQUIA, CPAIS, CPROVINCIA, CCANTON)
      references TPARROQUIASID (CPARROQUIA, CPAIS, CPROVINCIA, CCANTON);

alter table TPROSPECTONATURALES
   add constraint FKPROSNATRAZAPE foreign key (CRAZONAPERTURA)
      references TRAZONESAPERTURAID (CRAZONAPERTURA);

alter table TPROSPECTONATURALES
   add constraint FKPROSNATTIPCIU foreign key (CTIPOCIUDADANO)
      references TTIPOCIUDADANOID (CTIPOCIUDADANO);

alter table TPROSPECTONATURALES
   add constraint FKPROSNATUSU foreign key (CUSUARIO)
      references TUSUARIOSID (CUSUARIO);


alter table TCUENTACOLOCACIONESOPERACION
   drop constraint FKCTACOLOPER;

alter table TCUENTACOLOCACIONESOPERACION
   drop constraint FKCTACOLOPERCTA;

alter table TCUENTACOLOCACIONESOPERACION
   drop constraint FKCTACOLOPERUSUING;

alter table TCUENTACOLOCACIONESOPERACION
   drop constraint FKCTACOLOPERUSUMOD;

alter table TCUENTACOLOCACIONESOPERACION
   drop primary key;

drop table TCUENTACOLOCACIONESOPERACION;

/*==============================================================*/
/* Table: TCUENTACOLOCACIONESOPERACION                          */
/*==============================================================*/
create table TCUENTACOLOCACIONESOPERACION  (
   CCUENTA              VARCHAR(19)                    not null,
   CPERSONA_COMPANIA    INTEGER                       not null,
   SCUENTAOPERACION     INTEGER                       not null,
   FHASTA               TIMESTAMP(6)                    not null,
   VERSIONCONTROL       BIGINT                     default 0,
   FDESDE               TIMESTAMP(6)                    not null,
   CCUENTA_OPERACION    VARCHAR(19)                    not null,
   COMENTARIO           VARCHAR(2000),
   CUSUARIO_INGRESO     VARCHAR(12),
   CUSUARIO_MODIFICA    VARCHAR(12),
   VALORASIGNADO        DECIMAL(19,6)
);

comment on table TCUENTACOLOCACIONESOPERACION is
'Cuentas de credito del pasivo relacionadas a Operaciones de colocaciones.';

comment on column TCUENTACOLOCACIONESOPERACION.CCUENTA is
'Numero de cuenta de la operacion de credito del pasivo';

comment on column TCUENTACOLOCACIONESOPERACION.CPERSONA_COMPANIA is
'Codigo de la compania a la que pertenece la cuenta, puede ser externa o compania licenciada';

comment on column TCUENTACOLOCACIONESOPERACION.SCUENTAOPERACION is
'Numero secuencial de la definicion de cuentas de credito del pasivo relacionadas a la Operacion';

comment on column TCUENTACOLOCACIONESOPERACION.FHASTA is
'Fecha hasta la que esta vigente los datos de cuentas de credito del pasivo relacionadas a la Operacion';

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

comment on column TCUENTACOLOCACIONESOPERACION.FDESDE is
'Fecha desde la que esta vigente los datos de cuentas de credito del pasivo relacionadas a la Operacion';

comment on column TCUENTACOLOCACIONESOPERACION.CCUENTA_OPERACION is
'Numero de cuenta de la operacion de colocaciones asociada';

comment on column TCUENTACOLOCACIONESOPERACION.COMENTARIO is
'Comentarios sobre la garantia relacionada a la Operacion de Credito';

comment on column TCUENTACOLOCACIONESOPERACION.CUSUARIO_INGRESO is
'Codigo de usuario que ingresa la operacion garantizada';

comment on column TCUENTACOLOCACIONESOPERACION.CUSUARIO_MODIFICA is
'Codigo de usuario que modifica la operacion garantizada';

comment on column TCUENTACOLOCACIONESOPERACION.VALORASIGNADO is
'Valor asignado para la operacion de credito';

alter table TCUENTACOLOCACIONESOPERACION
   add constraint IPKCTACOLOPER primary key (CCUENTA, FHASTA, SCUENTAOPERACION, CPERSONA_COMPANIA);

alter table TCUENTACOLOCACIONESOPERACION
   add constraint FKCTACOLOPER foreign key (CCUENTA, CPERSONA_COMPANIA)
      references TCUENTAID (CCUENTA, CPERSONA_COMPANIA);

alter table TCUENTACOLOCACIONESOPERACION
   add constraint FKCTACOLOPERCTA foreign key (CCUENTA_OPERACION, CPERSONA_COMPANIA)
      references TCUENTAID (CCUENTA, CPERSONA_COMPANIA);

alter table TCUENTACOLOCACIONESOPERACION
   add constraint FKCTACOLOPERUSUING foreign key (CUSUARIO_INGRESO)
      references TUSUARIOSID (CUSUARIO);

alter table TCUENTACOLOCACIONESOPERACION
   add constraint FKCTACOLOPERUSUMOD foreign key (CUSUARIO_MODIFICA)
      references TUSUARIOSID (CUSUARIO);