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

-- Project Name : noname

-- Date/Time    : 2017/04/28 18:49:40

-- Author       : yamauchi

-- RDBMS Type   : MySQL

-- Application  : A5:SQL Mk-2

-- ??s?c????}?X?^

drop table if exists bankbusinessdaymaster cascade;

create table bankbusinessdaymaster (

  YEARMONTH decimal(6,0) default '0' not null comment '?N??'

  , BANKBUSINESSDAY decimal(2,0) default '0' not null comment '??s?c???'

  , NOTE varchar(120) comment '???l'

  , DELETEFLG decimal(1,0) comment '???t???O'

  , ENTRYDATE datetime comment '?o?^?N????'

  , UPDATEDATE datetime comment '?X?V?N????'

  , constraint bankbusinessdaymaster_PKC primary key (YEARMONTH,BANKBUSINESSDAY)

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

-- ???????f?[?^

drop table if exists billingdata cascade;

create table billingdata (

  COMPANYCODE decimal(8,0) default '0' not null comment '??????R?[?h'

  , TARGETDATE decimal(6,0) default '0' not null comment '???N??'

  , SEQNO decimal(3,0) default '0' not null comment '?A??'

  , BILLPRICE decimal(10,0) comment '???????z'

  , ENTRYDATE datetime comment '?o?^?N????'

  , UPDATEDATE datetime comment '?X?V?N????'

  , constraint billingdata_PKC primary key (COMPANYCODE,TARGETDATE,SEQNO)

) comment '???????f?[?^' ;

-- ??????????f?[?^

drop table if exists billingdatadetail cascade;

create table billingdatadetail (

  COMPANYCODE decimal(8,0) default '0' not null comment '??????R?[?h'

  , TARGETDATE decimal(6,0) default '0' not null comment '???N??'

  , SEQNO decimal(3,0) default '0' not null comment '?A??'

  , LINECOUNT decimal(3,0) default '0' not null comment '?s???'

  , CONSTRUCTIONCODE decimal(10,0) comment '?H?????'

  , FIELDNAME varchar(120) comment '????'

  , BILLPRICE decimal(10,0) comment '???????z'

  , HIGHWPRICE decimal(10,0) comment '??????'

  , HARDWPRICE decimal(10,0) comment '??????'

  , INDSWASTETAX decimal(10,0) comment '?Y?p??'

  , NOTE varchar(120) comment '???l'

  , ENTRYDATE datetime comment '?o?^?N????'

  , UPDATEDATE datetime comment '?X?V?N????'

  , constraint billingdatadetail_PKC primary key (COMPANYCODE,TARGETDATE,SEQNO,LINECOUNT)

) comment '??????????f?[?^' ;

-- ???????x???T?}???[?f?[?^

drop table if exists billingpaymentsummary cascade;

create table billingpaymentsummary (

  COMPANYCODE decimal(8,0) default '0' not null comment '??????R?[?h'

  , TARGETDATE decimal(6,0) default '0' not null comment '???N??'

  , CHARGEPAYMENTKIND decimal(1,0) default '0' not null comment '?????x????'

  , PAYDAY date comment '?x????'

  , BILLPRICESTOTAL decimal(10,0) comment '???????z???v'

  , CONSUMPTIONTAXRATE decimal(6,3) comment '??????'

  , CONSUMPTIONTAXPRICE decimal(10,0) comment '?????z'

  , HIGHWPRICESTOTAL decimal(10,0) comment '???????v'

  , HARDWPRICESTOTAL decimal(10,0) comment '???????v'

  , INDSWASTETAXSTOTAL decimal(10,0) comment '?Y?p????v'

  , CNSTRPRICERATE decimal(6,3) comment '???????'

  , CNSTRPRICESTOTAL decimal(10,0) comment '????????v'

  , BILLINGPRICETOTAL decimal(10,0) comment '???????z???v'

  , ENTRYDATE datetime comment '?o?^?N????'

  , UPDATEDATE datetime comment '?X?V?N????'

  , constraint billingpaymentsummary_PKC primary key (COMPANYCODE,TARGETDATE,CHARGEPAYMENTKIND)

) comment '???????x???T?}???[?f?[?^' ;

-- ?H????????

drop table if exists constructionmaterialinfo cascade;

create table constructionmaterialinfo (

  CONSTRUCTIONCODE int(10) unsigned default 0 not null comment '?H?????'

  , MATERIALITEMCODE smallint(5) unsigned default 0 not null comment '????i??R?[?h'

  , RENTCOUNT smallint(5) unsigned comment '??o??'

  , REPAYCOUNT smallint(5) unsigned comment '??p??'

  , COMPLETEFLG tinyint(3) unsigned comment '?????t???O'

  , ENTRYDATE datetime comment '?o?^???t'

  , UPDATEDATE datetime comment '?X?V???t'

  , constraint constructionmaterialinfo_PKC primary key (CONSTRUCTIONCODE,MATERIALITEMCODE)

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

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

drop table if exists deadlinemaster cascade;

create table deadlinemaster (

  LABOURKIND decimal(1,0) default '0' not null comment '??????'

  , DEADLINE decimal(2,0) comment '????'

  , PAYDAY decimal(2,0) comment '?x????'

  , NOTE varchar(120) comment '???l'

  , DELETEFLG decimal(1,0) comment '???t???O'

  , ENTRYDATE datetime comment '?o?^???t'

  , UPDATEDATE datetime comment '?X?V???t'

  , constraint deadlinemaster_PKC primary key (LABOURKIND)

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

-- ?????m?F???F?f?[?^

drop table if exists depositapprovalinfo cascade;

create table depositapprovalinfo (

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

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

  , TARGETDATE decimal(6,0) not null comment '???N??'

  , SEQNO decimal(3,0) not null comment '?A??'

  , LINECOUNT decimal(3,0) not null comment '?s???'

  , APPROVALNO decimal(3,0) not null comment '???F????'

  , APPROVALPERSON decimal(8,0) comment '???F??R?[?h'

  , APPROVALPERSONNAME varchar(20) comment '???F???'

  , APPROVALAUTHORITY decimal(1,0) comment '???F??????'

  , APPROVALDATE date comment '???F???t'

  , ENTRYDATE datetime comment '?o?^???t'

  , UPDATEDATE datetime comment '?X?V???t'

  , constraint depositapprovalinfo_PKC primary key (ORDERERSDIVISION,ORDERERSCODE,TARGETDATE,SEQNO,LINECOUNT,APPROVALNO)

) comment '?????m?F???F?f?[?^' ;

-- ?????f?[?^

drop table if exists depositdata cascade;

create table depositdata (

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

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

  , TARGETDATE decimal(6,0) not null comment '???N??'

  , SEQNO decimal(3,0) not null comment '?A??'

  , BusinessPeriod decimal(4,0) comment '?c???'

  , DEPOSITDATE date comment '??????'

  , DepositAmount decimal(12,0) comment '???????z'

  , DEPOSITAMOUNTCASH decimal(10,0) comment '???????z?i?????j'

  , DEPOSITAMOUNTBILL decimal(10,0) comment '???????z?i??`?j'

  , ENTRYDATE datetime comment '?o?^???t'

  , UPDATEDATE datetime comment '?X?V???t'

  , constraint depositdata_PKC primary key (ORDERERSDIVISION,ORDERERSCODE,TARGETDATE,SEQNO)

) comment '?????f?[?^' ;

-- ????????f?[?^

drop table if exists depositdatadetail cascade;

create table depositdatadetail (

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

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

  , TARGETDATE decimal(6,0) not null comment '???N??'

  , SEQNO decimal(3,0) not null comment '?A??'

  , LINECOUNT decimal(3,0) not null comment '?s???'

  , REQUESTNO decimal(10,0) comment '????No'

  , ORDERNO decimal(2,0) comment '??t???'

  , DEPOSITAMOUNT decimal(10,0) comment '???????z'

  , DISCOUNTAMOUNT decimal(10,0) comment '?l???????z'

  , CNSTRPRICE decimal(10,0) comment '?????'

  , FEES decimal(10,0) comment '????'

  , OTHERADJUSTMENTS decimal(10,0) comment '?????????'

  , CARRYAMOUNT decimal(10,0) comment '?J?z???z'

  , APPROVALPERSONCODE decimal(8,0) comment '???F?S????R?[?h'

  , APPROVALDATE date comment '?S??????F???t'

  , APPROVALENDFLG decimal(1,0) comment '???F?????t???O'

  , ENTRYDATE datetime comment '?o?^???t'

  , UPDATEDATE datetime comment '?X?V???t'

  , constraint depositdatadetail_PKC primary key (ORDERERSDIVISION,ORDERERSCODE,TARGETDATE,SEQNO,LINECOUNT)

) comment '????????f?[?^' ;

-- ???????f?[?^

drop table if exists invoicedata cascade;

create table invoicedata (

  INVOICENO decimal(9,0) not null comment '??????No'

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

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

  , REQUESTMONTH decimal(6,0) not null comment '??????'

  , SEQNO decimal(2,0) not null comment '?A??'

  , CRETATEDATE date not null comment '????'

  , REQUESTDATE date not null comment '??????'

  , REQUESTNAME varchar(120) comment '??????'

  , TOTALAMOUNT decimal(10,0) comment '???????v???z'

  , TAXAMOUNT decimal(10,0) comment '???????z'

  , COMMENT1 varchar(60) comment '?R?????g1'

  , COMMENT2 varchar(60) comment '?R?????g2'

  , COMMENT3 varchar(60) not null comment '?R?????g3'

  , COMMENT4 varchar(60) not null comment '?R?????g4'

  , COMMENT5 varchar(60) not null comment '?R?????g5'

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

  , ENTRYDATE datetime not null comment '?o?^?N????'

  , UPDATEDATE datetime not null comment '?X?V?N????'

  , constraint invoicedata_PKC primary key (INVOICENO)

) comment '???????f?[?^' ;

alter table invoicedata add unique ORDERERSDIVISION (ORDERERSDIVISION,ORDERERSCODE,REQUESTMONTH,SEQNO) ;

-- ??????

drop table if exists materialinfo cascade;

create table materialinfo (

  MATERIALITEMCODE smallint(5) unsigned default 0 not null comment '????i??R?[?h'

  , MATERIALCOUNT smallint(5) unsigned comment '?????'

  , RENTCOUNT smallint(5) unsigned comment '??o??\??'

  , DELETEFLG smallint(5) unsigned comment '???t???O'

  , ENTRYDATE datetime comment '?o?^???t'

  , UPDATEDATE datetime comment '?X?V???t'

  , constraint materialinfo_PKC primary key (MATERIALITEMCODE)

) comment '??????' ;

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

drop table if exists materialitemmaster cascade;

create table materialitemmaster (

  MATERIALITEMCODE smallint(5) unsigned default 0 not null comment '????i??R?[?h'

  , MATERIALKINDCODE smallint(5) unsigned comment '??????R?[?h'

  , MATERIALITEMNAME varchar(40) comment '????i?????'

  , DISPLAYORDER smallint(5) unsigned comment '?\????'

  , DELETEFLG tinyint(3) unsigned comment '???t???O'

  , ENTRYDATE datetime comment '?o?^???t'

  , UPDATEDATE datetime comment '?X?V???t'

  , constraint materialitemmaster_PKC primary key (MATERIALITEMCODE)

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

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

drop table if exists materialkindmaster cascade;

create table materialkindmaster (

  MATERIALKINDCODE smallint(5) unsigned default 0 not null comment '??????R?[?h'

  , MATERIALKINDNAME varchar(40) comment '?????????'

  , DISPLAYORDER smallint(5) unsigned comment '?\????'

  , DELETEFLG tinyint(3) unsigned comment '???t???O'

  , ENTRYDATE datetime comment '?o?^???t'

  , UPDATEDATE datetime comment '?X?V???t'

  , constraint materialkindmaster_PKC primary key (MATERIALKINDCODE)

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

-- ??????????

drop table if exists materialrecordinfo cascade;

create table materialrecordinfo (

  MATERIALITEMCODE smallint(5) unsigned default 0 not null comment '????i??R?[?h'

  , PROCESSDATE date default '0000-00-00' not null comment '?????N????'

  , SEQNO smallint(5) unsigned default 0 not null comment '?A??'

  , RECKIND tinyint(3) unsigned comment '???R?[?h??'

  , CONSTRUCTIONCODE int(10) unsigned comment '?H?????'

  , PERSONCODE int(10) unsigned comment '?S????R?[?h'

  , MATERIALCOUNT smallint(5) unsigned comment '?????'

  , REPAYPLANDATE date comment '??p?\??N????'

  , COMMENTTEXT varchar(80) comment '?R?????g'

  , ENTRYDATE datetime comment '?o?^???t'

  , UPDATEDATE datetime comment '?X?V???t'

  , constraint materialrecordinfo_PKC primary key (MATERIALITEMCODE,PROCESSDATE,SEQNO)

) comment '??????????' ;

-- ?x?????F???f?[?^

drop table if exists paymentapprovalinfo cascade;

create table paymentapprovalinfo (

  COMPANYCODE decimal(8,0) default '0' not null comment '??????R?[?h'

  , TARGETDATE decimal(6,0) default '0' not null comment '???N??'

  , SEQNO decimal(3,0) default '0' not null comment '?A??'

  , LINECOUNT decimal(3,0) default '0' not null comment '?s???'

  , APPROVALNO decimal(3,0) default '0' not null comment '???F????'

  , APPROVALPERSON decimal(8,0) comment '???F??R?[?h'

  , APPROVALPERSONNAME varchar(20) comment '???F???'

  , APPROVALAUTHORITY decimal(1,0) comment '???F??????'

  , APPROVALDATE date comment '???F???t'

  , ENTRYDATE datetime comment '?o?^???t'

  , UPDATEDATE datetime comment '?X?V???t'

  , constraint paymentapprovalinfo_PKC primary key (COMPANYCODE,TARGETDATE,SEQNO,LINECOUNT,APPROVALNO)

) comment '?x?????F???f?[?^' ;

-- ?x??????f?[?^

drop table if exists paymentdatadetail cascade;

create table paymentdatadetail (

  COMPANYCODE decimal(8,0) default '0' not null comment '??????R?[?h'

  , TARGETDATE decimal(6,0) default '0' not null comment '???N??'

  , SEQNO decimal(3,0) default '0' not null comment '?A??'

  , LINECOUNT decimal(3,0) default '0' not null comment '?s???'

  , BILLPRICE decimal(10,0) comment '???????z'

  , DISCOUNTPRICE decimal(10,0) comment '?l???????z'

  , OFFSETPRICE decimal(10,0) comment '???E???z'

  , NEXTCOPRICE decimal(10,0) comment '????J?z'

  , HIGHWPRICE decimal(10,0) comment '??????'

  , HARDWPRICE decimal(10,0) comment '??????'

  , INDSWASTETAX decimal(10,0) comment '?Y?p??'

  , CNSTRPRICE decimal(10,0) comment '?????'

  , CNSTRPRICEEXIST decimal(1,0) comment '??????L??'

  , APPROVALPERSONCODE decimal(8,0) comment '???F?S????R?[?h'

  , APPROVALDATE date comment '?S??????F???t'

  , APPROVALENDFLG decimal(1,0) comment '???F?????t???O'

  , ENTRYDATE datetime comment '?o?^???t'

  , UPDATEDATE datetime comment '?X?V???t'

  , constraint paymentdatadetail_PKC primary key (COMPANYCODE,TARGETDATE,SEQNO,LINECOUNT)

) comment '?x??????f?[?^' ;

-- ?????f?[?^

drop table if exists requestdata cascade;

create table requestdata (

  REQUESTNO decimal(9,0) not null comment '????No'

  , MAINCONSTRUCTIONCODE decimal(10,0) not null comment '?{?H?????'

  , CONSTRUCTIONNAME varchar(120) not null comment '?H??????'

  , CONTRACTAMOUNT decimal(10,0) not null comment '???????z'

  , PAIDAMOUNT decimal(10,0) not null comment '????????z'

  , REQUESTAMOUNT0 decimal(10,0) not null comment '???????z?O'

  , REQUESTAMOUNT1 decimal(10,0) not null comment '???????z?P'

  , REQUESTAMOUNT2 decimal(10,0) comment '???????z?Q'

  , REQUESTAMOUNT3 decimal(10,0) comment '???????z?R'

  , REQUESTAMOUNT4 decimal(10,0) comment '???????z?S'

  , REQUESTAMOUNT5 decimal(10,0) comment '???????z?T'

  , REQUESTAMOUNT6 decimal(10,0) comment '???????z?U'

  , UNCLAIMEDAMOUNT decimal(10,0) not null comment '?????c???z'

  , UNPAIDAMOUNT decimal(10,0) not null comment '?????????z'

  , TAXAMOUNT decimal(10,0) not null comment '?????'

  , NOTE varchar(120) not null comment '???l'

  , ENTRYDATE datetime not null comment '?o?^?N????'

  , UPDATEDATE datetime not null comment '?X?V?N????'

  , constraint requestdata_PKC primary key (REQUESTNO,MAINCONSTRUCTIONCODE)

) comment '?????f?[?^' ;

-- ?????f?[?^????

drop table if exists requestdatadetail cascade;

create table requestdatadetail (

  REQUESTNO decimal(9,0) not null comment '????No'

  , MAINCONSTRUCTIONCODE decimal(10,0) not null comment '?{?H?????'

  , CONSTRUCTIONCODE decimal(10,0) not null comment '?H?????'

  , CONSTRUCTIONKIND decimal(1,0) not null comment '?H????'

  , REQUESTAMOUNT decimal(10,0) not null comment '???????z'

  , TAXAMOUNT decimal(10,0) not null comment '?????'

  , ENTRYDATE datetime not null comment '?o?^?N????'

  , UPDATEDATE datetime not null comment '?X?V?N????'

  , constraint requestdatadetail_PKC primary key (REQUESTNO,MAINCONSTRUCTIONCODE,CONSTRUCTIONCODE,CONSTRUCTIONKIND)

) comment '?????f?[?^????' ;

-- ?????w?b?_

drop table if exists requesthead cascade;

create table requesthead (

  REQUESTNO decimal(9,0) not null comment '????No'

  , REQCONSTRUCTIONCODE decimal(10,0) not null comment '?????H?????'

  , ORDERNO decimal(2,0) not null comment '??t???'

  , REQUESTMONTH decimal(6,0) not null comment '??????'

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

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

  , ORDERERSNAME varchar(120) not null comment '???????'

  , REQCONSTRUCTIONNAME varchar(120) not null comment '?????H??????'

  , REQUESTTOTALAMOUNT decimal(10,0) not null comment '???????z???v'

  , REQUESTAMOUNT decimal(10,0) not null comment '???????z'

  , TAXAMOUNT decimal(10,0) not null comment '?????'

  , UNPAIDAMOUNT decimal(10,0) comment '??????'

  , NOTE varchar(120) comment '???l'

  , ASSIGNEDFLG decimal(1,0) comment '??????t???O'

  , INVOICENO decimal(9,0) not null comment '??????No'

  , ENTRYDATE datetime not null comment '?o?^?N????'

  , UPDATEDATE datetime not null comment '?X?V?N????'

  , constraint requesthead_PKC primary key (REQUESTNO)

) comment '?????w?b?_' ;

alter table requesthead add unique REQCONSTRUCTIONCODE (REQCONSTRUCTIONCODE,ORDERNO) ;