-- 注文書明細データ
DROP TABLE IF EXISTS purchaseorderdetail_OLD;
CREATE TABLE purchaseorderdetail_OLD As Select * from purchaseorderdetail;

drop table if exists purchaseorderdetail cascade;

create table purchaseorderdetail (
  ConstructionCode decimal(10,0) unsigned not null comment '工事コード'
  , SeqNo decimal(3,0) unsigned not null comment '注文書枝番'
  , GroupCount decimal(1,0) not null comment 'グループ番号'
  , LineCount decimal(3,0) unsigned not null comment '行番号'
  , ComponentCode decimal(5,0) unsigned not null comment '構成キー'
  , ItemCode decimal(5,0) unsigned not null comment '工種キー'
  , FirstString varchar(120) comment '工種・品名'
  , SecondString varchar(120) comment '内容内訳'
  , UnitCount decimal(8,2) not null comment '数量'
  , UnitName varchar(30) comment '単位'
  , UnitPrice decimal(10,0) not null comment '単価'
  , OrdersLinePrice decimal(12,0) comment '金額'
  , SourceCode decimal(10,0) unsigned not null comment '予算作成時工事番号'
  , SourceLineCnt decimal(3,0) unsigned not null comment '注文書作成時台帳行番号'
  , DataTypeFlag decimal(1,0) unsigned not null comment 'データタイプフラグ'
  , EntryDate datetime not null comment '登録日付'
  , UpdateDate datetime not null comment '更新日付'
  , constraint purchaseorderdetail_PKC primary key (ConstructionCode,SeqNo,GroupCount,LineCount)
) comment '注文書明細データ' ;

create index POrderDetail_Index1
  on purchaseorderdetail(SourceCode);

Insert INTO purchaseorderdetail
SELECT
  ConstructionCode
  , SeqNo
  , GroupCount
  , LineCount
  , ComponentCode
  , ItemCode
  , FirstString
  , SecondString
  , UnitCount
  , UnitName
  , UnitPrice
  , OrdersLinePrice
  , SourceCode
  , SourceLineCnt
  , 0
  , EntryDate
  , UpdateDate
FROM purchaseorderdetail_OLD
;
update purchaseorderdetail set DataTypeFlag = 1 where FirstString like '%法定%';

commit;