Z-Archive » Maybank Projects » M2U UPASS Project

Rem =====================================================================

Rem   NAME

Rem     <ib_cust_profile.sql>

Rem   DESCRIPTION

Rem     <IB Customer Profile Table> 

Rem   MODULE

Rem     <IB Module>

Rem   MODIFIED  (MM/DD/YYYY)

Rem        Danniell   03/12/2010 - Creation

Rem        xxxxxx           mm/dd/yyyy - Modify

Rem =====================================================================

CREATE TABLE IB_CUST_PROFILE

(

  USER_ID            NUMBER                     NOT NULL,

  ROLE_ID            NUMBER                     NOT NULL,

  GROUP_ID           NUMBER                     NOT NULL,

  ORG_ID             NUMBER,

  USER_NAME          VARCHAR2(64 BYTE),

  USER_DOB_BRD       VARCHAR2(8 BYTE),

  USER_EMAIL         VARCHAR2(64 BYTE),

  USER_GENDER        VARCHAR2(1 BYTE),

  USER_RACE          VARCHAR2(4 BYTE),

  USER_HOME_PHONE    VARCHAR2(20 BYTE),

  USER_OFFICE_PHONE  VARCHAR2(20 BYTE),

  USER_MOBILE_PHONE  VARCHAR2(20 BYTE),

  USER_TAC_PHONE     VARCHAR2(20 BYTE),

  USER_ADDR1         VARCHAR2(64 BYTE),

  USER_ADDR2         VARCHAR2(64 BYTE),

  USER_ADDR3         VARCHAR2(64 BYTE),

  USER_ADDR4         VARCHAR2(64 BYTE),

  USER_ADDR5         VARCHAR2(64 BYTE),

  USER_ACCESS_NO     VARCHAR2(19 BYTE),

  USER_TYPE          VARCHAR2(2 BYTE),

  LAST_LOGIN_DATE    TIMESTAMP(2),

  UPDATED_BY         NUMBER,

  UPDATED_DATE       TIMESTAMP(2),

  IMAGE_LINK         VARCHAR2(60 BYTE),

  IMAGE_TEXT         VARCHAR2(60 BYTE)

)

TABLESPACE USERS

PCTUSED    0

PCTFREE    10

INITRANS   1

MAXTRANS   255

STORAGE    (

            INITIAL          64K

            NEXT             128K

            MINEXTENTS       1

            MAXEXTENTS       UNLIMITED

            PCTINCREASE      0

            FREELISTS        1

            FREELIST GROUPS  1

            BUFFER_POOL      DEFAULT

           )

LOGGING 

NOCOMPRESS 

NOCACHE

NOPARALLEL

MONITORING;

CREATE UNIQUE INDEX IB_CUST_PROFILE_PK ON IB_CUST_PROFILE

(USER_ID)

LOGGING

TABLESPACE USERS

PCTFREE    10

INITRANS   2

MAXTRANS   255

STORAGE    (

            INITIAL          64K

            NEXT             128K

            MINEXTENTS       1

            MAXEXTENTS       UNLIMITED

            PCTINCREASE      0

            FREELISTS        1

            FREELIST GROUPS  1

            BUFFER_POOL      DEFAULT

           )

NOPARALLEL;

ALTER TABLE IB_CUST_PROFILE ADD (

  CONSTRAINT IB_CUST_PROFILE_PK

 PRIMARY KEY

 (USER_ID)

    USING INDEX 

    TABLESPACE USERS

    PCTFREE    10

    INITRANS   2

    MAXTRANS   255

    STORAGE    (

                INITIAL          64K

                NEXT             128K

                MINEXTENTS       1

                MAXEXTENTS       UNLIMITED

                PCTINCREASE      0

                FREELISTS        1

                FREELIST GROUPS  1

               ));

ALTER TABLE IB_CUST_PROFILE ADD (

  CONSTRAINT IB_CUST_PROFILE_R01 

 FOREIGN KEY (ROLE_ID) 

 REFERENCES IB_ROLE (ROLE_ID),

  CONSTRAINT IB_CUST_PROFILE_R02 

 FOREIGN KEY (GROUP_ID) 

 REFERENCES IB_GROUP (GROUP_ID),

  CONSTRAINT IB_CUST_PROFILE_R04 

 FOREIGN KEY (ORG_ID) 

 REFERENCES IB_ORGANIZATION (ORG_ID));