-- 工事担当変更履歴データ
drop table if exists constrchgcharge cascade;
drop table if exists ChgConstrCharge cascade;

create table ChgConstrCharge (
  ConstructionCode decimal(10,0) unsigned not null comment '工事コード'
  , Division decimal(1,0) not null comment '設定区分'
  , SeqNo smallint(5) unsigned not null comment '連番'
  , DepartmentCode decimal(5,0) unsigned not null comment '部署コード'
  , PersonCode decimal(8,0) not null comment '担当者コード'
  , StartDate date comment '担当開始年月日'
  , CompDate date comment '担当終了年月日'
  , EntryDate datetime not null comment '登録日付'
  , UpdateDate datetime not null comment '更新日付'
  , constraint ChgConstrCharge_PKC primary key (ConstructionCode,Division,SeqNo)
) comment '工事担当変更履歴データ' ;

-- 予算書集計コードマスタ
drop table if exists BudgetSumCode cascade; 

CREATE TABLE BudgetSumCode( 
  BlockCode DECIMAL (2, 0) UNSIGNED NOT NULL COMMENT 'ブロック番号'
  , GroupCode DECIMAL (2, 0) UNSIGNED NOT NULL COMMENT 'グループ番号'
  , ComponentCode DECIMAL (5, 0) UNSIGNED NOT NULL COMMENT '構成キー'
  , ItemCode DECIMAL (5, 0) UNSIGNED NOT NULL COMMENT '工種キー'
  , ElementsName VARCHAR (100) NOT NULL COMMENT '要素名称'
  , EntryDate DATETIME NOT NULL COMMENT '登録日付'
  , UpdateDate DATETIME NOT NULL COMMENT '更新日付'
  , CONSTRAINT BudgetSumCode_PKC PRIMARY KEY (BlockCode, GroupCode, ComponentCode, ItemCode)
) COMMENT '予算書集計コードマスタ'; 

-- 工事種別マスタ
DROP TABLE IF EXISTS constructiontypemaster_OLD; 
CREATE TABLE constructiontypemaster_OLD As 
Select
  * 
from
  constructiontypemaster; 

drop table if exists constructiontypemaster cascade;

create table constructiontypemaster (
  TypeCode decimal(5,0) unsigned not null comment '工事種別コード'
  , DisplayOrder decimal(5,0) unsigned not null comment '表示順'
  , NameString varchar(100) not null comment '名称'
  , PublicFlg decimal(1,0) not null comment '公共フラグ'
  , RepairFlg decimal(1,0) not null comment '新築・改修フラグ'
  , SecRank decimal(1,0) not null comment '保護区分ランク'
  , ExpensesLink decimal(1,0) not null comment '経費使用区分'
  , DeleteFlg decimal(1,0) not null comment '削除フラグ'
  , EntryDate datetime not null comment '登録日付'
  , UpdateDate datetime not null comment '更新日付'
  , constraint constructiontypemaster_PKC primary key (TypeCode)
) comment '工事種別マスタ' ;

Insert 
INTO constructiontypemaster 
Select
  TypeCode
  , DisplayOrder
  , NameString
  , PublicFlg
  , 0
  , SecRank
  , ExpensesLink
  , DeleteFlg
  , EntryDate
  , NOW()
from
  constructiontypemaster_OLD;