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

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