alter session set "_ORACLE_SCRIPT"=true;  

CREATE USER CPDB identified by CPDB default tablespace users  temporary tablespace temp;
GRANT CONNECT , RESOURCE  TO CPDB;
connect cpdb/CPDB@orcl12 
/

CREATE TABLE REF_CITY$
(
	PROVINCE_C  VARCHAR2(3)  NOT NULL ,
	CITY_C  VARCHAR2(2)  NOT NULL ,
	CITY  VARCHAR2(30)  NULL ,
	MOI_C  VARCHAR2(4)  NULL ,
	OLD_PROVINCE_C  VARCHAR2(3)  NULL ,
	OLD_CITY_C  VARCHAR2(2)  NULL 
);


CREATE  UNIQUE INDEX SYS_C0064989 ON REF_CITY$
(
	PROVINCE_C  ASC,
	CITY_C  ASC
);


ALTER TABLE REF_CITY$
	ADD  PRIMARY KEY (PROVINCE_C,CITY_C);


CREATE TABLE REF_ENGINEER_BRANCH$
(
	BRANCH_C  VARCHAR2(4)  NOT NULL ,
	BRANCH_DESC  VARCHAR2(50)  NULL 
);


CREATE  UNIQUE INDEX XPKREF_ENGINEER_BRANCH$ ON REF_ENGINEER_BRANCH$
(
	BRANCH_C  ASC
);


ALTER TABLE REF_ENGINEER_BRANCH$
	ADD  PRIMARY KEY (BRANCH_C);


CREATE TABLE REF_PRECINCT$
(
	PROVINCE_C  VARCHAR2(3)  NOT NULL ,
	CITY_C  VARCHAR2(2)  NOT NULL ,
	PRECINCT_C  VARCHAR2(2)  NOT NULL ,
	PRECINCT  VARCHAR2(30)  NULL ,
	MOI_C  VARCHAR2(6)  NULL ,
	POST_CODE  VARCHAR2(5)  NULL ,
	OLD_PROVINCE_C  VARCHAR2(3)  NULL ,
	OLD_CITY_C  VARCHAR2(2)  NULL ,
	OLD_PRECINCT_C  VARCHAR2(2)  NULL 
);


CREATE  UNIQUE INDEX SYS_C0065021 ON REF_PRECINCT$
(
	PROVINCE_C  ASC,
	CITY_C  ASC,
	PRECINCT_C  ASC
);


ALTER TABLE REF_PRECINCT$
	ADD  PRIMARY KEY (PROVINCE_C,CITY_C,PRECINCT_C);


CREATE TABLE REF_PRENAME$
(
	PRENAME_C  VARCHAR2(4)  NOT NULL ,
	PRENAME_TH  VARCHAR2(40)  NULL ,
	PRENAME_EN  VARCHAR2(40)  NULL ,
	PREABR_TH  VARCHAR2(30)  NULL ,
	PREABR_EN  VARCHAR2(30)  NULL ,
	PRIOR_NO  NUMBER(5)  NULL ,
	DEFAULT_FLAG  VARCHAR2(1)  NULL 
);


CREATE  UNIQUE INDEX XPKREF_PRENAME$ ON REF_PRENAME$
(
	PRENAME_C  ASC
);


ALTER TABLE REF_PRENAME$
	ADD  PRIMARY KEY (PRENAME_C);


CREATE TABLE REF_PROVINCE$
(
	PROVINCE_C  VARCHAR2(3)  NOT NULL ,
	PROVINCE  VARCHAR2(40)  NULL ,
	REGION_C  VARCHAR2(2)  NULL ,
	ZONE_C  VARCHAR2(2)  NULL ,
	DEDE_ZONE_C  VARCHAR2(2)  NULL ,
	MOI_C  VARCHAR2(2)  NULL ,
	MOI_ZONE  VARCHAR2(2)  NULL ,
	OLD_PROVINCE_C  VARCHAR2(3)  NULL 
);


CREATE  UNIQUE INDEX SYS_C0065024 ON REF_PROVINCE$
(
	PROVINCE_C  ASC
);


ALTER TABLE REF_PROVINCE$
	ADD  PRIMARY KEY (PROVINCE_C);


CREATE INDEX XIF283ENDBREF_PROVINCE$ ON REF_PROVINCE$
(
	DEDE_ZONE_C  ASC
);


CREATE TABLE TBL_E_PK_INSPECTOR$
(
	REQ_ID  VARCHAR2(9)  NOT NULL ,
	PRENAME_C  VARCHAR2(20)  NULL ,
	NAME  VARCHAR2(30)  NULL ,
	SURNAME  VARCHAR2(30)  NULL ,
	ID_CARD  VARCHAR2(13)  NULL ,
	CONTACT_ADDR_NO  VARCHAR2(20)  NULL ,
	CONTACT_ADDR_NAME  VARCHAR2(100)  NULL ,
	CONTACT_SOI  VARCHAR2(50)  NULL ,
	CONTACT_MOO  VARCHAR2(10)  NULL ,
	CONTACT_STREET  VARCHAR2(50)  NULL ,
	CONTACT_PROVINCE_C  VARCHAR2(3)  NULL ,
	CONTACT_CITY_C  VARCHAR2(2)  NULL ,
	CONTACT_PRECINCT_C  VARCHAR2(2)  NULL ,
	CONTACT_POSTCODE  VARCHAR2(5)  NULL ,
	EMAIL  VARCHAR2(40)  NULL ,
	TELEPHONE  VARCHAR2(40)  NULL ,
	WEBSITE  VARCHAR2(40)  NULL ,
	MOBILE  VARCHAR2(25)  NULL ,
	FAX  VARCHAR2(15)  NULL ,
	ADDR_NAME  VARCHAR2(100)  NULL ,
	MOO  VARCHAR2(10)  NULL ,
	SOI  VARCHAR2(50)  NULL ,
	STREET  VARCHAR2(50)  NULL ,
	PROVINCE_C  VARCHAR2(3)  NULL ,
	CITY_C  VARCHAR2(2)  NULL ,
	PRECINCT_C  VARCHAR2(2)  NULL ,
	ENGINEER_ID  VARCHAR2(20)  NULL ,
	ENGINEER_TYPE  VARCHAR2(1)  NULL ,
	START_D  VARCHAR2(10)  NULL ,
	END_D  VARCHAR2(10)  NULL ,
	BRANCH_C  VARCHAR2(4)  NULL ,
	LEVEL_C  VARCHAR2(1)  NULL ,
	CREATED_AT  TIMESTAMP  NULL ,
	UPDATED_AT  TIMESTAMP  NULL ,
	MODIFIED_BY  VARCHAR2(50)  NULL 
);


CREATE  UNIQUE INDEX XPKTBL_E_PK_INSPECTOR$ ON TBL_E_PK_INSPECTOR$
(
	REQ_ID  ASC
);


ALTER TABLE TBL_E_PK_INSPECTOR$
	ADD  PRIMARY KEY (REQ_ID);


CREATE INDEX XIF1TBL_E_PK_INSPECTOR$ ON TBL_E_PK_INSPECTOR$
(
	BRANCH_C  ASC
);


CREATE INDEX XIF2TBL_E_PK_INSPECTOR$ ON TBL_E_PK_INSPECTOR$
(
	PRENAME_C  ASC
);


CREATE INDEX XIF3TBL_E_PK_INSPECTOR$ ON TBL_E_PK_INSPECTOR$
(
	PROVINCE_C  ASC,
	CITY_C  ASC,
	PRECINCT_C  ASC
);