-- 案件進捗日付データ
DROP TABLE IF EXISTS constructionprogressdate_OLD; 

CREATE TABLE constructionprogressdate_OLD As 
Select
  * 
from
  constructionprogressdate; 

drop table if exists constructionprogressdate cascade; 

create table constructionprogressdate( 
  ConstructionCode decimal (10, 0) not null comment '工事番号'
  , ConstructionStatusFlg decimal (2, 0) not null comment '設定工事状態'
  , PreviousStatusFlg decimal (2, 0) comment '変更前状態'
  , ChangeDate datetime not null comment '設定日付'
  , ChangePersonCode decimal (8, 0) not null comment '設定担当者'
  , EntryDate datetime not null comment '登録日付'
  , UpdateDate datetime not null comment '更新日付'
  , constraint constructionprogressdate_PKC primary key (ConstructionCode, ChangeDate)
) comment '案件進捗日付データ'; 

create index ConstrProg_Index1 
  on constructionprogressdate(ConstructionCode); 

create index ConstrProg_Index2 
  on constructionprogressdate(ConstructionStatusFlg); 

create index ConstrProg_Index3 
  on constructionprogressdate(ChangeDate); 

Insert 
INTO constructionprogressdate 
Select
  * 
from
  constructionprogressdate_OLD; 

-- 工事詳細台帳経費データ
drop table if exists ConstrLedgerExpenses cascade;

create table ConstrLedgerExpenses (
  ConstructionCode decimal(10,0) unsigned not null comment '工事コード'
  , SeqNo decimal(5,0) not null comment '枝番'
  , NameCode decimal(5,0) unsigned not null comment '経費コード'
  , ExpensesRaito decimal(4,2) not null comment '経費率'
  , ExpensesValue decimal(10,0) not null comment '経費金額'
  , EntryDate datetime not null comment '登録日付'
  , UpdateDate datetime not null comment '更新日付'
  , constraint ConstrLedgerExpenses_PKC primary key (ConstructionCode, SeqNo, NameCode)
) comment '工事詳細台帳経費データ' ;

create index ConstrLedgerExpenses_Index1
  on ConstrLedgerExpenses(ConstructionCode);

create index ConstrLedgerExpenses_Index2
  on ConstrLedgerExpenses(SeqNo);

create index ConstrLedgerExpenses_Index3
  on ConstrLedgerExpenses(NameCode);

Insert 
INTO constrledgerexpenses 
select
  A.ConstructionCode
  , 1
  , A.ComponentCode
  , A.AmountConfigRate
  , A.ExecutionAmount 
  , A.EntryDate
  , A.UpdateDate
from
  constructionledgerdetail As A 
where
  A.GroupCount = 1 
  And A.ComponentCode = 3; 

Insert 
INTO constrledgerexpenses 
select
  A.ConstructionCode
  , 2
  , A.ComponentCode
  , A.AmountConfigRate
  , A.ExecutionAmount 
  , A.EntryDate
  , A.UpdateDate
from
  constructionledgerdetail As A 
where
  A.GroupCount = 1 
  And A.ComponentCode = 1; 

Insert 
INTO constrledgerexpenses 
select
  A.ConstructionCode
  , 3
  , A.ComponentCode
  , A.AmountConfigRate
  , A.ExecutionAmount 
  , A.EntryDate
  , A.UpdateDate
from
  constructionledgerdetail As A 
where
  A.GroupCount = 1 
  And A.ComponentCode = 2; 


-- 部署経費率マスタ
DROP TABLE IF EXISTS departmentexpensesmaster_OLD; 

CREATE TABLE departmentexpensesmaster_OLD As 
Select
  * 
from
  departmentexpensesmaster; 

drop table if exists departmentexpensesmaster cascade;

create table departmentexpensesmaster (
  DepartmentCode decimal(5,0) unsigned not null comment '部署コード'
  , ExpensesPeriod smallint(6) not null comment '事業期'
  , NameCode decimal(5,0) unsigned not null comment '経費コード'
  , NameString varchar(100) not null comment '名称'
  , DisplayOrder decimal(5,0) unsigned not null comment '表示順'
  , ExpensesRaito decimal(4,2) not null comment '経費率'
  , DeleteFlg decimal(1,0) not null comment '削除フラグ'
  , EntryDate datetime not null comment '登録日付'
  , UpdateDate datetime not null comment '更新日付'
  , constraint departmentexpensesmaster_PKC primary key (DepartmentCode,ExpensesPeriod,NameCode)
) comment '部署経費率マスタ' ;

Insert 
INTO departmentexpensesmaster 
select
  A.DepartmentCode
  , A.ExpensesPeriod
  , A.NameCode
  , B.NameString
  , A.DisplayOrder
  , A.ExpensesRaito
  , A.DeleteFlg
  , A.EntryDate
  , A.UpdateDate 
from
  departmentexpensesmaster_OLD As A 
  LEFT JOIN divisionmaster AS B 
    ON B.DivisionCode = 3 
    And B.NameCode = A.NameCode; 

commit;