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

-- ????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;