工事管理システム製品化対応

-- ????i?????t?f?[?^

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 '?H?????'

  , ConstructionStatusFlg decimal (2, 0) not null comment '???H?????'

  , PreviousStatusFlg decimal (2, 0) comment '??X?O???'

  , ChangeDate datetime not null comment '?????t'

  , ChangePersonCode decimal (8, 0) not null comment '???S????'

  , EntryDate datetime not null comment '?o?^???t'

  , UpdateDate datetime not null comment '?X?V???t'

  , constraint constructionprogressdate_PKC primary key (ConstructionCode, ChangeDate)

) comment '????i?????t?f?[?^'; 

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; 

-- ?H???????o??f?[?^

drop table if exists ConstrLedgerExpenses cascade;

create table ConstrLedgerExpenses (

  ConstructionCode decimal(10,0) unsigned not null comment '?H???R?[?h'

  , SeqNo decimal(5,0) not null comment '?}??'

  , NameCode decimal(5,0) unsigned not null comment '?o??R?[?h'

  , ExpensesRaito decimal(4,2) not null comment '?o??'

  , ExpensesValue decimal(10,0) not null comment '?o????z'

  , EntryDate datetime not null comment '?o?^???t'

  , UpdateDate datetime not null comment '?X?V???t'

  , constraint ConstrLedgerExpenses_PKC primary key (ConstructionCode, SeqNo, NameCode)

) comment '?H???????o??f?[?^' ;

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; 

-- ?????o???}?X?^

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 '?????R?[?h'

  , ExpensesPeriod smallint(6) not null comment '?????'

  , NameCode decimal(5,0) unsigned not null comment '?o??R?[?h'

  , NameString varchar(100) not null comment '????'

  , DisplayOrder decimal(5,0) unsigned not null comment '?\????'

  , ExpensesRaito decimal(4,2) not null comment '?o??'

  , DeleteFlg decimal(1,0) not null comment '???t???O'

  , EntryDate datetime not null comment '?o?^???t'

  , UpdateDate datetime not null comment '?X?V???t'

  , constraint departmentexpensesmaster_PKC primary key (DepartmentCode,ExpensesPeriod,NameCode)

) comment '?????o???}?X?^' ;

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;