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

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

drop table if exists DepartmentCostData cascade; 

CREATE TABLE DepartmentCostData( 

  DepartmentCode DECIMAL (5, 0) UNSIGNED NOT NULL COMMENT '?????R?[?h'

  , DataYear SMALLINT (5) UNSIGNED NOT NULL COMMENT '?H???????E?H???N??'

  , DataCount DECIMAL (5, 0) UNSIGNED NOT NULL COMMENT '?f?[?^???'

  , EntryPersonCode DECIMAL (8, 0) UNSIGNED NOT NULL COMMENT '????S????R?[?h'

  , SuppliersCode DECIMAL (5, 0) UNSIGNED NOT NULL COMMENT '?x????R?[?h'

  , SuppliersName VARCHAR (120) COMMENT '?x??????'

  , PaymentContent VARCHAR (60) COMMENT '?x?????e'

  , DateOfBilling DATE NOT NULL COMMENT '??????'

  , DateOfPayment DATE NOT NULL COMMENT '?x????'

  , EntryPrice DECIMAL (12, 0) NOT NULL COMMENT '???z?i????j'

  , EntryPriceInTax DECIMAL (12, 0) NOT NULL COMMENT '???z?i????j'

  , SlipNumber VARCHAR (12) COMMENT '?`?[???'

  , ComplateFlg DECIMAL (1, 0) NOT NULL COMMENT '???????t???O'

  , ApprovalFlg DECIMAL (1, 0) NOT NULL COMMENT '???F?t???O'

  , EntryDate DATETIME NOT NULL COMMENT '?o?^???t'

  , UpdateDate DATETIME NOT NULL COMMENT '?X?V???t'

  , CONSTRAINT DepartmentCostData_PKC PRIMARY KEY (DepartmentCode, DataYear, DataCount)

) COMMENT '???????o?[?f?[?^'; 

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

-- Date/Time    : 2018/04/12 9:06:32

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

  , constraint constructionprogressdate_PKC primary key (ConstructionCode,ChangeDate)

create index ConstrProg_Index1

  on constructionprogressdate(ConstructionCode);

create index ConstrProg_Index2

  on constructionprogressdate(ConstructionStatusFlg);

create index ConstrProg_Index3

  on constructionprogressdate(ChangeDate);

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

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

create index PersonSalaryMaster_Index3

  on personsalarymaster(MonthlySalary);