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

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

  , SalesPersonCode decimal(8,0) comment '?c??S?????'

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

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

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

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

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

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

  , 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 

  , SalesPersonCode 

  , ConstructionPersonCode 

  , ConstrSubPersonCode 

  , 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 

  , STR_TO_DATE('0001/01/01', '%Y/%m/%d')

  , STR_TO_DATE('0001/01/01', '%Y/%m/%d')

  , STR_TO_DATE('0001/01/01', '%Y/%m/%d')

  , EntryDate

  , UpdateDate

FROM constructionbaseinfo_OLD

;

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

ALTER TABLE constructionbaseinfodetail

CHANGE DetailString DetailString 

varchar(300)

comment '??????e';

commit;