-- Create table
create table BODY_NO_MASTER
(
  code_control      VARCHAR2(1) default 'A' not null,
  date_entry        DATE not null,
  user_entry        VARCHAR2(7) not null,
  date_update       DATE,
  user_update       VARCHAR2(7),
  body_no           VARCHAR2(12) not null,
  code_amend        VARCHAR2(12),
  weight            NUMBER(5),
  layer_pcb         VARCHAR2(10),
  line_no           VARCHAR2(8) not null,
  pallet_date       VARCHAR2(10) not null,
  merchandise       VARCHAR2(12) not null,
  code_layer_pallet VARCHAR2(5),
  pallet_no         VARCHAR2(3) not null,
  quarter_id        VARCHAR2(2) not null,
  pallet_qt         VARCHAR2(5),
  date_input        DATE,
  pallet_status     VARCHAR2(3),
  slip_no           VARCHAR2(10)
)
partition by hash (BODY_NO)
(
  partition BNM1
    tablespace HKPDATA1,
  partition BNM2
    tablespace HKPDATA1,
  partition BNM3
    tablespace HKPDATA1,
  partition BNM4
    tablespace HKPDATA1,
  partition BNM5
    tablespace HKPDATA1,
  partition BNM6
    tablespace HKPDATA2,
  partition BNM7
    tablespace HKPDATA2,
  partition BNM8
    tablespace HKPDATA2,
  partition BNM9
    tablespace HKPDATA2,
  partition BNM10
    tablespace HKPDATA2
);
-- Create/Recreate indexes 
create index CODE_LAYER_PALLET_BNM_N on BODY_NO_MASTER (CODE_LAYER_PALLET)
  tablespace HKPDATA1
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
  );
create index LINE_NO_BNM_N on BODY_NO_MASTER (LINE_NO)
  tablespace HKPDATA1
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 128K
    next 1M
    minextents 1
    maxextents unlimited
  );
create index MERCHANDISE_BNM_N on BODY_NO_MASTER (MERCHANDISE)
  tablespace HKPDATA1
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 128K
    next 1M
    minextents 1
    maxextents unlimited
  );
create index PALLET_DATE_BNM_N on BODY_NO_MASTER (PALLET_DATE)
  tablespace HKPDATA1
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 128K
    next 1M
    minextents 1
    maxextents unlimited
  );
create index PALLET_NO_BNM_N on BODY_NO_MASTER (PALLET_NO)
  tablespace HKPDATA1
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 128K
    next 1M
    minextents 1
    maxextents unlimited
  );
create index PALLET_STATUS_BNM_N on BODY_NO_MASTER (PALLET_STATUS)
  tablespace HKPDATA1
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 128K
    next 1M
    minextents 1
    maxextents unlimited
  );
create index SLIP_NO_N on BODY_NO_MASTER (SLIP_NO)
  tablespace HKPDATA1
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
  );
-- Create/Recreate primary, unique and foreign key constraints 
alter table BODY_NO_MASTER
  add constraint BREGULAR_PALLET_BKL_FK primary key (BODY_NO, MERCHANDISE)
  using index 
  tablespace HKPDATA1
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
  );