create table CONSUMER
(
  CNSMR_ID                      NUMBER(14) not null,
  CNSMR_LAST_NAME               VARCHAR2(40),
  CNSMR_FRST_NAME               VARCHAR2(40),
  CNSMR_MATRNL_LAST_NAME        VARCHAR2(40),
  CNSMR_MID_NAME                VARCHAR2(40),
  CNSMR_BRTH_DATE               DATE,
  CNSMR_ADDR_LINE1_TEXT         VARCHAR2(80),
  CNSMR_ADDR_LINE2_TEXT         VARCHAR2(80),
  CNSMR_ADDR_LINE3_TEXT         VARCHAR2(80),
  CNSMR_ADDR_CITY_NAME          VARCHAR2(60),
  CNSMR_ADDR_STATE_NAME         VARCHAR2(40),
  CNSMR_ADDR_POSTAL_CODE        VARCHAR2(14),
  CNSMR_ADDR_ISO_CNTRY_CODE     VARCHAR2(3) not null,
  CNSMR_ADDR_LATITUDE_NBR       NUMBER(15,9),
  CNSMR_ADDR_LONGITUDE_NBR      NUMBER(15,9),
  CNSMR_ADDR_GEOCODE_MATCH_CODE CHAR(1),
  EPSILON_SYNC_FLAG             CHAR(1) not null,
  EPSILON_SYNC_DATE             DATE,
  CNSMR_OCUPN_TEXT              VARCHAR2(40),
  CNSMR_PGM_STAT_CODE           CHAR(1) default 'E',
  NCOA_FLAG                     CHAR(1),
  LACS_FLAG                     CHAR(1),
  NCOA_RETURN_CODE              VARCHAR2(2),
  CREATE_DATE                   DATE default sysdate not null,
  CREATE_USERID                 VARCHAR2(30) default SUBSTR(SYS_CONTEXT('USERENV','SESSION_USER') || '-' || SYS_CONTEXT('USERENV','OS_USER'),1,30) not null,
  LAST_UPDATE_DATE              DATE default sysdate not null,
  LAST_UPDATE_USERID            VARCHAR2(30) default SUBSTR(SYS_CONTEXT('USERENV','SESSION_USER') || '-' || SYS_CONTEXT('USERENV','OS_USER'),1,30) not null,
  MF_LAST_UPDATE_DATE           DATE,
  EDIR_GU_ID                    VARCHAR2(38),
  CNSMR_RESET_PSWD_ID           VARCHAR2(36),
  LAST_TRAN_DATE                DATE,
  PRM_CNSMR_ID                  NUMBER(14),
  CNSMR_ADDR_LINE4_TEXT         VARCHAR2(80),
  CRTSY_TITLE_CODE              VARCHAR2(6),
  OLD_LAST_NAME                 VARCHAR2(20),
  LANG_TAG_ID                   NUMBER(21),
  LAST_UPDATE_SRC_SYS_ID        VARCHAR2(3)
)



create index FK1_CONSUMER on CONSUMER (LANG_TAG_ID)
create index FK2_CONSUMER on CONSUMER (PRM_CNSMR_ID)
create index FK3_CONSUMER on CONSUMER (CRTSY_TITLE_CODE)
create index FK4_CONSUMER on CONSUMER (LAST_UPDATE_SRC_SYS_ID)
create index IDX_CNSMR_ADDR on CONSUMER (CNSMR_ADDR_POSTAL_CODE)
create index IX1_CONSUMER on CONSUMER (CNSMR_LAST_NAME, CNSMR_FRST_NAME, CNSMR_BRTH_DATE)




create table CONSUMER_TRAN
(
  TRAN_REF_ID                  VARCHAR2(8) not null,
  SND_TRAN_DATE                DATE not null,
  TRAN_STAT_CODE               CHAR(3),
  TRAN_STAT_DATE               DATE,
  SND_CNSMR_PH_NBR             VARCHAR2(20),
  PRODUCT_ID                   NUMBER(21) not null,
  DLVR_OPTN_ID                 NUMBER(4) not null,
  LYLTY_PGM_TYPE_CODE          VARCHAR2(3),
  LYLTY_PGM_MBSHP_ID           VARCHAR2(20),
  PROMO_ID                     VARCHAR2(8),
  SND_CNSMR_ID                 NUMBER(14),
  SND_AGENT_ID                 NUMBER(8) not null,
  SND_MAINOFFICE_ID            NUMBER(8) not null,
  SND_ISO_CNTRY_CODE           VARCHAR2(3) not null,
  SND_ISO_CRNCY_CODE           VARCHAR2(3) not null,
  SND_FACE_AMT                 NUMBER(17,6) not null,
  SND_FEE_AMT                  NUMBER(15,3) not null,
  SND_FEE_DCNT_AMT             NUMBER(15,3) default 0,
  SND_ACCT_MASK_NBR            VARCHAR2(4),
  INTND_RCV_AGENT_ID           NUMBER(8),
  RCV_ISO_CNTRY_CODE           VARCHAR2(3),
  RCV_ISO_CRNCY_CODE           VARCHAR2(3),
  LAST_UPDATE_DATE             DATE default sysdate not null,
  LAST_UPDATE_USERID           VARCHAR2(30) default    SUBSTR(SYS_CONTEXT('USERENV','SESSION_USER') || '-' || SYS_CONTEXT('USERENV','OS_USER'),1,30) not null,
  RCV_CUST_FIRST_NAME          VARCHAR2(40),
  RCV_CUST_MID_NAME            VARCHAR2(40),
  RCV_CUST_LAST_NAME           VARCHAR2(40),
  RCV_CUST_MATRNL_LAST_NAME    VARCHAR2(40),
  RCV_CUST_ADDR_LINE1_TEXT     VARCHAR2(60),
  RCV_CUST_ADDR_LINE2_TEXT     VARCHAR2(60),
  RCV_CUST_ADDR_LINE3_TEXT     VARCHAR2(60),
  RCV_CUST_ADDR_CITY_NAME      VARCHAR2(60),
  RCV_CUST_ADDR_STATE_NAME     VARCHAR2(40),
  RCV_CUST_ADDR_POSTAL_CODE    VARCHAR2(14),
  RCV_CUST_ADDR_ISO_CNTRY_CODE VARCHAR2(3),
  RCV_CUST_MUNICIPIO           VARCHAR2(60),
  RCV_CUST_COLONIA             VARCHAR2(60),
  RCV_DLVR_INSTR_1_TEXT        VARCHAR2(40),
  RCV_DLVR_INSTR_2_TEXT        VARCHAR2(40),
  RCV_DLVR_INSTR_3_TEXT        VARCHAR2(40),
  RCV_CUST_PH_NBR              VARCHAR2(20),
  RCV_ACCT_MASK_NBR            VARCHAR2(4),
  RCV_AGCY_CODE                VARCHAR2(30),
  CUST_RCV_NBR                 VARCHAR2(10),
  RCV_AGENT_CUST_ID            VARCHAR2(20),
  PREV_TOT_CNSMR_TALLY_CNT     NUMBER(7),
  SND_FEE_DCNT_TYPE_CODE       VARCHAR2(1)
)




create table LOYALTY_PROGRAM_MBSHP
(
  LYLTY_PGM_MBSHP_ID       VARCHAR2(20) not null,
  LYLTY_PGM_TYPE_CODE      VARCHAR2(3) not null,
  CNSMR_ID                 NUMBER(14),
  MBSHP_BEG_DATE           DATE not null,
  MBSHP_SRC_CODE           VARCHAR2(6) not null,
  CARD_TYPE_CODE           CHAR(1) default 'T' not null,
  CARD_FULFILL_STAT_CODE   CHAR(1),
  CARD_FULFILL_STAT_DATE   DATE,
  CREATE_DATE              DATE default sysdate not null,
  CREATE_USERID            VARCHAR2(30) default SUBSTR(SYS_CONTEXT('USERENV','SESSION_USER') || '-' || SYS_CONTEXT('USERENV','OS_USER'),1,30) not null,
  LAST_UPDATE_DATE         DATE default sysdate not null,
  LAST_UPDATE_USERID       VARCHAR2(30) default SUBSTR(SYS_CONTEXT('USERENV','SESSION_USER') || '-' || SYS_CONTEXT('USERENV','OS_USER'),1,30) not null,
  MBSHP_END_DATE           DATE,
  LAST_TRAN_DATE           DATE,
  MBR_FRST_NAME            VARCHAR2(40),
  MBR_LAST_NAME            VARCHAR2(40),
  MBR_MID_NAME             VARCHAR2(40),
  MBR_CNSMR_VAL_LVL_CODE   VARCHAR2(1),
  MBR_MATRNL_LAST_NAME     VARCHAR2(40),
  WEB_TRMS_CNDTNS_ACKNW_TS DATE,
  OLD_MBR_LAST_NAME        VARCHAR2(20)
)