-- 業者請求データ
DROP TABLE IF EXISTS billingdata_OLD;
CREATE TABLE billingdata_OLD As Select * from billingdata;

drop table if exists billingdata cascade;

create table billingdata (
  COMPANYCODE decimal(8,0) default '0' not null comment '協力会社コード'
  , TARGETDATE decimal(6,0) default '0' not null comment '対象年月'
  , PAYMENTKIND decimal(1,0) default '0' not null comment '支払区分'
  , SEQNO decimal(3,0) default '0' not null comment '連番'
  , BILLPRICE decimal(10,0) comment '請求金額'
  , ENTRYDATE datetime comment '登録年月日'
  , UPDATEDATE datetime comment '更新年月日'
  , constraint billingdata_PKC primary key (COMPANYCODE,TARGETDATE,PAYMENTKIND,SEQNO)
) comment '業者請求データ' ;

Insert INTO billingdata
SELECT
  COMPANYCODE
  , TARGETDATE
  , 0
  , SEQNO
  , BILLPRICE
  , ENTRYDATE
  , UPDATEDATE
FROM billingdata_OLD
;
commit;


-- 業者請求明細データ
DROP TABLE IF EXISTS billingdatadetail_OLD;
CREATE TABLE billingdatadetail_OLD As Select * from billingdatadetail;

drop table if exists billingdatadetail cascade;

create table billingdatadetail (
  COMPANYCODE decimal(8,0) default '0' not null comment '協力会社コード'
  , TARGETDATE decimal(6,0) default '0' not null comment '対象年月'
  , PAYMENTKIND decimal(1,0) default '0' not null comment '支払区分'
  , SEQNO decimal(3,0) default '0' not null comment '連番'
  , LINECOUNT decimal(3,0) default '0' not null comment '行番号'
  , CONSTRUCTIONCODE decimal(10,0) comment '工事番号'
  , CONSTRUCTIONROWCNT decimal(3,0) default '0' not null comment '行番号'
  , CONSTRUCTIONCOLCNT decimal(3,0) default '0' not null comment '列番号'
  , FIELDNAME varchar(120) comment '現場名'
  , BILLPRICE decimal(10,0) comment '請求金額'
  , HIGHWPRICE decimal(10,0) comment '高速代'
  , HARDWPRICE decimal(10,0) comment '金物代'
  , INDSWASTETAX decimal(10,0) comment '産廃税'
  , NOTE varchar(120) comment '備考'
  , ENTRYDATE datetime comment '登録年月日'
  , UPDATEDATE datetime comment '更新年月日'
  , constraint billingdatadetail_PKC primary key (COMPANYCODE,TARGETDATE,PAYMENTKIND,SEQNO,LINECOUNT)
) comment '業者請求明細データ' ;

Insert INTO billingdatadetail
SELECT
  COMPANYCODE
  , TARGETDATE
  , 0
  , SEQNO
  , LINECOUNT
  , CONSTRUCTIONCODE
  , CONSTRUCTIONROWCNT
  , CONSTRUCTIONCOLCNT
  , FIELDNAME
  , BILLPRICE
  , HIGHWPRICE
  , HARDWPRICE
  , INDSWASTETAX
  , NOTE
  , ENTRYDATE
  , UPDATEDATE
FROM billingdatadetail_OLD
;
commit;