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

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

DROP TABLE IF EXISTS departmentmaster_OLD;

CREATE TABLE departmentmaster_OLD As Select * from departmentmaster;

drop table if exists departmentmaster cascade;

create table departmentmaster (

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

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

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

  , ActionScheduleFlg decimal(1,0) not null comment '?s???\??\???t???O'

  , StaffAssignFlg decimal(1,0) not null comment '?l???z?u?\???t???O'

  , StartDate datetime not null comment '?J?n???t'

  , CompDate datetime not null comment '?I?????t'

  , 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 departmentmaster_PKC primary key (DepartmentCode)

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

create index DepartmentMaster_index1

  on departmentmaster(DisplayOrder);

Insert INTO departmentmaster

SELECT

  DepartmentCode

  , DisplayOrder

  , DepartmentString

  , ActionScheduleFlg

  , StaffAssignFlg

  , NOW()

  , DATE('0001/01/01')

  , DeleteFlg

  , EntryDate

  , UpdateDate

From departmentmaster_OLD

;

commit;

create index constructionledger_Index1

  on constructionledger(ConstructionStart);

create index constructionledger_Index2

  on constructionledger(ConstructionEnd);

create index LedgerDetail_Index06

  on constructionledgerdetail(SalaryFlg);

create index LedgerDetail_Index07

  on constructionledgerdetail(CompanyCode);

-- ?H????{???

DROP TABLE IF EXISTS constructionbaseinfo_OLD;

CREATE TABLE constructionbaseinfo_OLD As Select * from constructionbaseinfo;

drop table if exists constructionbaseinfo cascade;

create table constructionbaseinfo (

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

  , TyingFlg decimal(1,0) not null comment '?R?t?f?[?^?t???O'

  , JoinFlg decimal(1,0) not null comment '?????_??H???t???O'

  , ConstructionYear smallint(5) unsigned not null comment '?H???N?x'

  , ConstructionPeriod smallint(5) unsigned not null comment '?H??????'

  , RequestedDate date comment '??????'

  , EstimatesSubmitDeadline date comment '?????o????'

  , EstimatesSubmittedDate date comment '?????o??'

  , ProvisionalOrderDate date comment '?????'

  , OrderDate date comment '???'

  , OrderStartingDate date comment '?J?n?\???'

  , OrderCompletionDate date comment '?????\???'

  , PreparationStartDate date comment '?{?H?????J?n??'

  , ConstructionStartingDate date comment '?{?H?J?n??'

  , ConstructionCompletionDate date comment '?{?H??????'

  , NonOrderDate date comment '????'

  , OrdersDecisionPrice decimal(12,0) not null comment '??????????z'

  , OrdersDecisionPriceInTax decimal(12,0) not null comment '??????????z'

  , SalesDepCode decimal(5,0) unsigned not null comment '?c??S????????R?[?h'

  , SalesPersonCode decimal(8,0) unsigned not null comment '?c??S????R?[?h'

  , SalesSubDepCode decimal(5,0) unsigned not null comment '?c????S????????R?[?h'

  , SalesSubPersonCode decimal(8,0) unsigned not null comment '?c????S????R?[?h'

  , ConstrDepCode decimal(5,0) unsigned not null comment '?H???S????????R?[?h'

  , ConstructionPersonCode decimal(8,0) not null comment '?H???S????R?[?h'

  , ConstrSubDepCode decimal(5,0) unsigned not null comment '?H?????S????????R?[?h'

  , ConstrSubPersonCode decimal(8,0) not null comment '?H?????S????R?[?h'

  , ConstrInstrDepCode decimal(5,0) unsigned not null comment '?H???w?????????R?[?h'

  , ConstructionInstructor decimal(8,0) not null comment '?H???w?????R?[?h'

  , TransferConstruction decimal(8,0) not null comment '?H??????R?[?h'

  , TransferConstructionDate date comment '?H??????'

  , OrderersDivision decimal(5,0) not null comment '????????R?[?h'

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

  , EstimatesExpirationDate date comment '????L??????'

  , ConstructionPeriodStart date comment '?_??H???J?n??'

  , ConstructionPeriodEnd date comment '?_??H??????'

  , ConstructionPeriodStart2 date comment '?_??H???J?n?i???????j'

  , ConstructionPeriodEnd2 date comment '?_??H???????i???????j'

  , StartDate date comment '?H???J?n??'

  , EndDate date comment '?H???I????'

  , PurchaseOrderMailingDate date comment '???????X????'

  , PurchaseOrderReturnDate date comment '???????????'

  , PurchaseOrderReturnCheckDate date comment '??????????m?F??'

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

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

  , SalesCostFlg decimal(1,0) not null comment '?c??o?????t???O'

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

  , ConstructionType decimal(5,0) not null comment '?H?????R?[?h'

  , EstimateType decimal(1,0) not null comment '??????'

  , BillingStartDate date not null comment '?????????J?n??'

  , BillingDate date not null comment '??????'

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

  , BillingSendDate date comment '???????X??/??n????'

  , LedgerComplateDate date not null comment '?H????????????????t'

  , InspectPlanningDate date not null comment '?????\???'

  , InspectExecuteDate date not null comment '???????s??'

  , RevCompleteDate date not null comment '??????????????'

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

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

  , constraint constructionbaseinfo_PKC primary key (ConstructionCode)

) comment '?H????{???' ;

create index BaseInfo_Index1

  on constructionbaseinfo(ConstructionStatusFlg);

create index BaseInfo_Index2

  on constructionbaseinfo(EstimateType);

create index BaseInfo_Index3

  on constructionbaseinfo(SalesPersonCode);

create index BaseInfo_Index4

  on constructionbaseinfo(ConstructionPersonCode);

create index BaseInfo_Index5

  on constructionbaseinfo(ConstructionInstructor);

create index BaseInfo_Index6

  on constructionbaseinfo(ConstructionPeriod);

Insert INTO constructionbaseinfo

Select 

  ConstructionCode

  , TyingFlg

  , JoinFlg

  , ConstructionYear

  , ConstructionPeriod

  , RequestedDate

  , EstimatesSubmitDeadline

  , EstimatesSubmittedDate

  , ProvisionalOrderDate

  , OrderDate

  , OrderStartingDate

  , OrderCompletionDate

  , PreparationStartDate

  , ConstructionStartingDate

  , ConstructionCompletionDate

  , NonOrderDate

  , OrdersDecisionPrice

  , OrdersDecisionPriceInTax

  , SalesDepCode

  , SalesPersonCode

  , 0

  , 0

  , ConstrDepCode

  , ConstructionPersonCode

  , ConstrSubDepCode

  , ConstrSubPersonCode

  , ConstrInstrDepCode

  , ConstructionInstructor

  , TransferConstruction

  , TransferConstructionDate

  , OrderersDivision

  , OrderersCode

  , EstimatesExpirationDate

  , ConstructionPeriodStart

  , ConstructionPeriodEnd

  , ConstructionPeriodStart2

  , ConstructionPeriodEnd2

  , StartDate

  , EndDate

  , PurchaseOrderMailingDate

  , PurchaseOrderReturnDate

  , PurchaseOrderReturnCheckDate

  , ConsumptionTaxFlg

  , PrimeContractorFlg

  , SalesCostFlg

  , ConstructionStatusFlg

  , ConstructionType

  , EstimateType

  , BillingStartDate

  , BillingDate

  , BillingSplitFlg

  , BillingSendDate

  , LedgerComplateDate

  , InspectPlanningDate

  , InspectExecuteDate

  , RevCompleteDate

  , EntryDate

  , UpdateDate

From constructionbaseinfo_OLD;

-- ?N?}?X?^

DROP TABLE IF EXISTS yearmaster_OLD;

CREATE TABLE yearmaster_OLD As Select * from yearmaster;

drop table if exists yearmaster cascade;

create table yearmaster (

  years smallint(6) not null comment '?N'

  , constraint yearmaster_PKC primary key (years)

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

Insert INTO yearmaster

Select * From yearmaster_OLD;

-- ???}?X?^

DROP TABLE IF EXISTS monthmaster_OLD;

CREATE TABLE monthmaster_OLD As Select * from monthmaster;

drop table if exists monthmaster cascade;

create table monthmaster (

  month smallint(6) not null comment '??'

  , constraint monthmaster_PKC primary key (month)

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

Insert INTO monthmaster

Select * From monthmaster_OLD;

-- ???}?X?^

DROP TABLE IF EXISTS daymaster_OLD;

CREATE TABLE daymaster_OLD As Select * from daymaster;

drop table if exists daymaster cascade;

create table daymaster (

  days smallint(6) not null comment '??'

  , constraint daymaster_PKC primary key (days)

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

Insert INTO daymaster

Select * From daymaster_OLD;

commit;

-- Date/Time    : 2018/04/06 11:54:53

  , SalesSubDepCode decimal(5,0) unsigned not null comment '?c????S????????R?[?h'

  , SalesSubPersonCode decimal(8,0) unsigned not null comment '?c????S????R?[?h'

create index constructionledger_Index1

  on constructionledger(ConstructionStart);

create index constructionledger_Index2

  on constructionledger(ConstructionEnd);

create index LedgerDetail_Index06

  on constructionledgerdetail(SalaryFlg);

create index LedgerDetail_Index07

  on constructionledgerdetail(CompanyCode);

  , constraint daymaster_PKC primary key (days)

  , StartDate datetime not null comment '?J?n???t'

  , CompDate datetime not null comment '?I?????t'

  , constraint monthmaster_PKC primary key (month)

  , constraint yearmaster_PKC primary key (years)