[ 사업장 ] 테이블 생성을 아래와 같이 진행합니다.

-- ------------------------------------------------------------------------------------------------
-- [ 인사관리 ] 사업장 테이블 생성
conn system/built123$@ndb;
-- ------------------------------------------------------------------------------------------------
-- 테이블 생성
drop   table bhrm.hrm_business_place cascade constraints;
create table bhrm.hrm_business_place
(
     corp_id                 number         not null
    ,biz_id                  number         not null
    ,biz_cd                  varchar2(50)   not null
    ,biz_name                varchar2(200)  not null
    ,biz_short_name          varchar2(100)  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_business_place                         is '[인사관리] 사업장';
comment on column bhrm.hrm_business_place.corp_id                 is '법인ID';
comment on column bhrm.hrm_business_place.biz_id                  is '사업장ID';
comment on column bhrm.hrm_business_place.biz_cd                  is '사업장코드';
comment on column bhrm.hrm_business_place.biz_name                is '사업장명';
comment on column bhrm.hrm_business_place.biz_short_name          is '사업장명(약칭)';
comment on column bhrm.hrm_business_place.biz_num                 is '사업자번호';
comment on column bhrm.hrm_business_place.repre_ssn1              is '대표자주민번호1';
comment on column bhrm.hrm_business_place.repre_name1             is '대표자명1';
comment on column bhrm.hrm_business_place.repre_ssn2              is '대표자주민번호2';
comment on column bhrm.hrm_business_place.repre_name2             is '대표자명2';
comment on column bhrm.hrm_business_place.estab_date              is '설립일자';
comment on column bhrm.hrm_business_place.start_date              is '시작일자';
comment on column bhrm.hrm_business_place.end_date                is '종료일자';
comment on column bhrm.hrm_business_place.tel_num                 is '전화번호';
comment on column bhrm.hrm_business_place.fax_num                 is '팩스번호';
comment on column bhrm.hrm_business_place.address1                is '주소';
comment on column bhrm.hrm_business_place.address2                is '상세주소';
comment on column bhrm.hrm_business_place.postal_code             is '우편번호';
comment on column bhrm.hrm_business_place.home_tax_id             is '홈덱스id';
comment on column bhrm.hrm_business_place.region_cd               is '지역코드';
comment on column bhrm.hrm_business_place.tax_office_cd           is '세무서코드';
comment on column bhrm.hrm_business_place.health_ins_num          is '건강보험번호';
comment on column bhrm.hrm_business_place.nat_pen_num             is '국민보험번호';
comment on column bhrm.hrm_business_place.emp_ins_num             is '고용보험번호';
comment on column bhrm.hrm_business_place.ind_acc_num             is '산재보험번호';
comment on column bhrhttp://m.hrm_business_place.remark is '비고';

comment on column bhrm.hrm_business_place.create_date             is '생성일시';
comment on column bhrm.hrm_business_place.create_by               is '생성자';
comment on column bhrm.hrm_business_place.update_date             is '수정일시';
comment on column bhrm.hrm_business_place.update_by               is '수정자';

-- primary 인덱스
alter table bhrm.hrm_business_place add constraint hrm_business_place_pk primary key (biz_id) using index tablespace bdb_idx;

-- unique 인덱스
create unique index bhrm.hrm_business_place_uk01 on bhrm.hrm_business_place (corp_id, biz_cd) tablespace bdb_idx;
create unique index bhrm.hrm_business_place_uk02 on bhrm.hrm_business_place (biz_num) tablespace bdb_idx;

-- normal 인덱스
create index bhrm.hrm_business_place_ix01 on bhrm.hrm_business_place (biz_name) tablespace bdb_idx;
create index bhrm.hrm_business_place_ix02 on bhrm.hrm_business_place (biz_short_name) tablespace bdb_idx;

-- 권한부여
grant select, insert, update, delete on bhrm.hrm_business_place to bsys with grant option;
grant select, insert, update, delete on bhrm.hrm_business_place to bcom with grant option;

-- 트리거 생성
create or replace trigger bhrm.hrm_business_place_trg
    before insert or update on bhrm.hrm_business_place
    for each row
begin
    if :new.biz_id is null then    
        :new.biz_id  := hrm_business_place_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_business_place for bhrm.hrm_business_place;

conn bcom/1234@ndb;
create or replace synonym hrm_business_place for bhrm.hrm_business_place;