-- ------------------------------------------------------------------------------------------------
-- [ 인사관리 ] 법인 테이블 생성
conn system/built123$@ndb;
-- ------------------------------------------------------------------------------------------------
-- 테이블 생성
drop table bhrm.hrm_corporation cascade constraints;
create table bhrm.hrm_corporation
(
corp_id number not null
,corp_cd varchar2(50) not null
,corp_name varchar2(200) not null
,corp_short_name varchar2(100) not null
,corp_num varchar2(2000) not null
,biz_num varchar2(2000) not null
,repre_ssn1 varchar2(2000) not null
,repre_name1 varchar2(50) not null
,repre_ssn2 varchar2(2000)
,repre_name2 varchar2(50)
,estab_date date not null
,start_date date default trunc(sysdate) not null
,end_date date
,tel_num varchar2(20)
,fax_num varchar2(20)
,address1 varchar2(300)
,address2 varchar2(300)
,postal_code varchar2(10)
,home_tax_id varchar2(20)
,region_cd varchar2(50) not null
,tax_office_cd varchar2(30) not null
,health_ins_num varchar2(30) not null
,nat_pen_num varchar2(30) not null
,emp_ins_num varchar2(30) not null
,ind_acc_num varchar2(30) not null
,remark varchar2(4000)
,create_date timestamp default systimestamp
,create_by varchar2(50) default '-1'
,update_date timestamp default systimestamp
,update_by varchar2(50) default '-1'
) tablespace bdb_data;
-- 테이블 설명
comment on table bhrm.hrm_corporation is '[인사관리] 법인';
comment on column bhrm.hrm_corporation.corp_id is '법인ID';
comment on column bhrm.hrm_corporation.corp_cd is '법인코드';
comment on column bhrm.hrm_corporation.corp_name is '법인명';
comment on column bhrm.hrm_corporation.corp_short_name is '법인명(약칭)';
comment on column bhrm.hrm_corporation.corp_num is '법인번호';
comment on column bhrm.hrm_corporation.biz_num is '사업자번호';
comment on column bhrm.hrm_corporation.repre_ssn1 is '대표자주민번호1';
comment on column bhrm.hrm_corporation.repre_name1 is '대표자명1';
comment on column bhrm.hrm_corporation.repre_ssn2 is '대표자주민번호2';
comment on column bhrm.hrm_corporation.repre_name2 is '대표자명2';
comment on column bhrm.hrm_corporation.estab_date is '설립일자';
comment on column bhrm.hrm_corporation.start_date is '시작일자';
comment on column bhrm.hrm_corporation.end_date is '종료일자';
comment on column bhrm.hrm_corporation.tel_num is '전화번호';
comment on column bhrm.hrm_corporation.fax_num is '팩스번호';
comment on column bhrm.hrm_corporation.address1 is '주소';
comment on column bhrm.hrm_corporation.address2 is '상세주소';
comment on column bhrm.hrm_corporation.postal_code is '우편번호';
comment on column bhrm.hrm_corporation.home_tax_id is '홈덱스id';
comment on column bhrm.hrm_corporation.region_cd is '지역코드';
comment on column bhrm.hrm_corporation.tax_office_cd is '세무서코드';
comment on column bhrm.hrm_corporation.health_ins_num is '건강보험번호';
comment on column bhrm.hrm_corporation.nat_pen_num is '국민보험번호';
comment on column bhrm.hrm_corporation.emp_ins_num is '고용보험번호';
comment on column bhrm.hrm_corporation.ind_acc_num is '산재보험번호';
comment on column bhrhttp://m.hrm_corporation.remark is '비고';
comment on column bhrm.hrm_corporation.create_date is '생성일시';
comment on column bhrm.hrm_corporation.create_by is '생성자';
comment on column bhrm.hrm_corporation.update_date is '수정일시';
comment on column bhrm.hrm_corporation.update_by is '수정자';
-- primary 인덱스
alter table bhrm.hrm_corporation add constraint hrm_corporation_pk primary key (corp_id) using index tablespace bdb_idx;
-- unique 인덱스
create unique index bhrm.hrm_corporation_uk01 on bhrm.hrm_corporation (corp_cd) tablespace bdb_idx;
create unique index bhrm.hrm_corporation_uk02 on bhrm.hrm_corporation (corp_num) tablespace bdb_idx;
-- normal 인덱스
create index bhrm.hrm_corporation_ix01 on bhrm.hrm_corporation (corp_name) tablespace bdb_idx;
create index bhrm.hrm_corporation_ix02 on bhrm.hrm_corporation (corp_short_name) tablespace bdb_idx;
-- 권한부여
grant select, insert, update, delete on bhrm.hrm_corporation to bsys with grant option;
grant select, insert, update, delete on bhrm.hrm_corporation to bcom with grant option;
-- 트리거 생성
create or replace trigger bhrm.hrm_corporation_trg
before insert or update on bhrm.hrm_corporation
for each row
begin
if :new.corp_id is null then
:new.corp_id := hrm_corporation_s.nextval;
end if;
if inserting then
:new.create_date := systimestamp;
:new.create_by := nvl(wwv_flow.g_user, user);
end if;
:new.update_date := systimestamp;
:new.update_by := nvl(wwv_flow.g_user, user);
end;
/
-- 시노님 생성
conn bsys/1234@ndb;
create or replace synonym hrm_corporation for bhrm.hrm_corporation;
conn bcom/1234@ndb;
create or replace synonym hrm_corporation for bhrm.hrm_corporation;
'오라클 > 테이블(인사관리)' 카테고리의 다른 글
[ 임직원 ] 테이블 생성을 아래와 같이 진행합니다. (0) | 2024.04.12 |
---|---|
[ 조직도상세 ] 테이블 생성을 아래와 같이 진행합니다. (0) | 2024.04.12 |
[ 조직도 ] 테이블 생성을 아래와 같이 진행합니다. (0) | 2024.04.12 |
[ 사업장 ] 테이블 생성을 아래와 같이 진행합니다. (0) | 2024.04.12 |
[ 사업장 ] 테이블 생성을 아래와 같이 진행합니다. (0) | 2024.04.12 |