Z-Archive » Maybank Projects » M2U UPASS Project

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

Rem   NAME

Rem     <ib_bank_branch.sql>

Rem   DESCRIPTION

Rem     <IB Bank Branch Content 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_BANK_BRANCH

(

  OID                  NUMBER                   NOT NULL,

  CONTENT_CATEGORY_ID  NUMBER                   NOT NULL,

  BRANCH_NAME          VARCHAR2(64 BYTE),

  BRANCH_CODE          VARCHAR2(8 BYTE),

  BRANCH_ADDR1         VARCHAR2(64 BYTE),

  BRANCH_ADDR2         VARCHAR2(64 BYTE),

  BRANCH_ADDR3         VARCHAR2(64 BYTE),

  BRANCH_ADDR4         VARCHAR2(64 BYTE),

  BRANCH_ADDR5         VARCHAR2(64 BYTE),

  BRANCH_POSTCODE      VARCHAR2(16 BYTE),

  BRANCH_CONTACT       VARCHAR2(20 BYTE),

  BRANCH_FAX           VARCHAR2(20 BYTE),

  AREA_CODE            VARCHAR2(16 BYTE),

  STATUS               VARCHAR2(1 BYTE),

  DELETED              VARCHAR2(1 BYTE),

  CREATION_DATE        TIMESTAMP(2)             DEFAULT SYSDATE               NOT NULL,

  LAST_MOD_DATE        TIMESTAMP(2)

)

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_BANK_BRANCH_PK ON IB_BANK_BRANCH

(OID)

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;

CREATE UNIQUE INDEX IB_BANK_BRANCH_R02 ON IB_BANK_BRANCH

(BRANCH_CODE)

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_BANK_BRANCH ADD (

  CONSTRAINT IB_BANK_BRANCH_PK

 PRIMARY KEY

 (OID)

    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

               ),

  CONSTRAINT IB_BANK_BRANCH_R02

 UNIQUE (BRANCH_CODE)

    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_BANK_BRANCH ADD (

  CONSTRAINT IB_BANK_BRANCH_R01

 FOREIGN KEY (CONTENT_CATEGORY_ID)

 REFERENCES IB_CONTENT_CATEGORY (OID)

    ON DELETE CASCADE);