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

-- 資材管理、請求管理関係のテーブル作成

-- 銀行営業日マスタ

drop table if exists bankbusinessdaymaster cascade;

create table bankbusinessdaymaster (

  YEARMONTH decimal(6,0) default '0' not null comment '年月'

  , BANKBUSINESSDAY decimal(2,0) default '0' not null comment '銀行営業日'

  , NOTE varchar(120) comment '備考'

  , DELETEFLG decimal(1,0) comment '削除フラグ'

  , ENTRYDATE datetime comment '登録年月日'

  , UPDATEDATE datetime comment '更新年月日'

  , constraint bankbusinessdaymaster_PKC primary key (YEARMONTH,BANKBUSINESSDAY)

) comment '銀行営業日マスタ' ;

-- 業者請求データ

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 '対象年月'

  , 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,SEQNO)

) comment '業者請求データ' ;

-- 業者請求明細データ

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 '対象年月'

  , SEQNO decimal(3,0) default '0' not null comment '連番'

  , LINECOUNT decimal(3,0) default '0' not null comment '行番号'

  , CONSTRUCTIONCODE decimal(10,0) 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,SEQNO,LINECOUNT)

) comment '業者請求明細データ' ;

-- 業者請求支払サマリーデータ

drop table if exists billingpaymentsummary cascade;

create table billingpaymentsummary (

  COMPANYCODE decimal(8,0) default '0' not null comment '協力会社コード'

  , TARGETDATE decimal(6,0) default '0' not null comment '対象年月'

  , CHARGEPAYMENTKIND decimal(1,0) default '0' not null comment '請求支払区分'

  , PAYDAY date comment '支払日'

  , BILLPRICESTOTAL decimal(10,0) comment '請求金額小計'

  , CONSUMPTIONTAXRATE decimal(6,3) comment '消費税率'

  , CONSUMPTIONTAXPRICE decimal(10,0) comment '消費税額'

  , HIGHWPRICESTOTAL decimal(10,0) comment '高速代小計'

  , HARDWPRICESTOTAL decimal(10,0) comment '金物代小計'

  , INDSWASTETAXSTOTAL decimal(10,0) comment '産廃税小計'

  , CNSTRPRICERATE decimal(6,3) comment '協力金比率'

  , CNSTRPRICESTOTAL decimal(10,0) comment '協力金小計'

  , BILLINGPRICETOTAL decimal(10,0) comment '請求金額合計'

  , ENTRYDATE datetime comment '登録年月日'

  , UPDATEDATE datetime comment '更新年月日'

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

) comment '業者請求支払サマリーデータ' ;

-- 工事資材情報

drop table if exists constructionmaterialinfo cascade;

create table constructionmaterialinfo (

  CONSTRUCTIONCODE int(10) unsigned default 0 not null comment '工事番号'

  , MATERIALITEMCODE smallint(5) unsigned default 0 not null comment '資材品目コード'

  , RENTCOUNT smallint(5) unsigned comment '貸出数'

  , REPAYCOUNT smallint(5) unsigned comment '返却数'

  , COMPLETEFLG tinyint(3) unsigned comment '完了フラグ'

  , ENTRYDATE datetime comment '登録日付'

  , UPDATEDATE datetime comment '更新日付'

  , constraint constructionmaterialinfo_PKC primary key (CONSTRUCTIONCODE,MATERIALITEMCODE)

) comment '工事資材情報' ;

-- 締日マスタ

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 '支払日'

  , NOTE varchar(120) comment '備考'

  , DELETEFLG decimal(1,0) comment '削除フラグ'

  , ENTRYDATE datetime comment '登録日付'

  , UPDATEDATE datetime comment '更新日付'

  , constraint deadlinemaster_PKC primary key (LABOURKIND)

) comment '締日マスタ' ;

-- 入金確認承認データ

drop table if exists depositapprovalinfo cascade;

create table depositapprovalinfo (

  ORDERERSDIVISION decimal(5,0) not null comment '発注者区分'

  , ORDERERSCODE decimal(5,0) not null comment '発注者コード'

  , TARGETDATE decimal(6,0) not null comment '対象年月'

  , SEQNO decimal(3,0) not null comment '連番'

  , LINECOUNT decimal(3,0) not null comment '行番号'

  , APPROVALNO decimal(3,0) not null comment '承認順序'

  , APPROVALPERSON decimal(8,0) comment '承認者コード'

  , APPROVALPERSONNAME varchar(20) comment '承認者名'

  , APPROVALAUTHORITY decimal(1,0) comment '承認権限区分'

  , APPROVALDATE date comment '承認日付'

  , ENTRYDATE datetime comment '登録日付'

  , UPDATEDATE datetime comment '更新日付'

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

) comment '入金確認承認データ' ;

-- 入金データ

drop table if exists depositdata cascade;

create table depositdata (

  ORDERERSDIVISION decimal(5,0) not null comment '発注者区分'

  , ORDERERSCODE decimal(5,0) not null comment '発注者コード'

  , TARGETDATE decimal(6,0) not null comment '対象年月'

  , SEQNO decimal(3,0) not null comment '連番'

  , BusinessPeriod decimal(4,0) comment '営業期'

  , DEPOSITDATE date comment '入金日'

  , DepositAmount decimal(12,0) comment '入金金額'

  , DEPOSITAMOUNTCASH decimal(10,0) comment '入金金額（現金）'

  , DEPOSITAMOUNTBILL decimal(10,0) comment '入金金額（手形）'

  , ENTRYDATE datetime comment '登録日付'

  , UPDATEDATE datetime comment '更新日付'

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

) comment '入金データ' ;

-- 入金明細データ

drop table if exists depositdatadetail cascade;

create table depositdatadetail (

  ORDERERSDIVISION decimal(5,0) not null comment '発注者区分'

  , ORDERERSCODE decimal(5,0) not null comment '発注者コード'

  , TARGETDATE decimal(6,0) not null comment '対象年月'

  , SEQNO decimal(3,0) not null comment '連番'

  , LINECOUNT decimal(3,0) not null comment '行番号'

  , REQUESTNO decimal(10,0) comment '請求No'

  , ORDERNO decimal(2,0) comment '受付番号'

  , DEPOSITAMOUNT decimal(10,0) comment '入金金額'

  , DISCOUNTAMOUNT decimal(10,0) comment '値引き金額'

  , CNSTRPRICE decimal(10,0) comment '協力金'

  , FEES decimal(10,0) comment '手数料'

  , OTHERADJUSTMENTS decimal(10,0) comment 'その他調整'

  , CARRYAMOUNT decimal(10,0) comment '繰越金額'

  , APPROVALPERSONCODE decimal(8,0) comment '承認担当者コード'

  , APPROVALDATE date comment '担当者承認日付'

  , APPROVALENDFLG decimal(1,0) comment '承認完了フラグ'

  , ENTRYDATE datetime comment '登録日付'

  , UPDATEDATE datetime comment '更新日付'

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

) comment '入金明細データ' ;

-- 請求書データ

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 '発注者コード'

  , REQUESTMONTH decimal(6,0) not null comment '請求月'

  , SEQNO decimal(2,0) not null comment '連番'

  , CRETATEDATE date not null comment '作成日'

  , REQUESTDATE date not null comment '請求日'

  , REQUESTNAME varchar(120) comment '請求先名'

  , TOTALAMOUNT decimal(10,0) comment '税込み合計金額'

  , TAXAMOUNT decimal(10,0) comment '消費税金額'

  , COMMENT1 varchar(60) comment 'コメント1'

  , COMMENT2 varchar(60) comment 'コメント2'

  , COMMENT3 varchar(60) not null comment 'コメント3'

  , COMMENT4 varchar(60) not null comment 'コメント4'

  , COMMENT5 varchar(60) not null comment 'コメント5'

  , COMPLETEFLG decimal(1,0) default '0' not null comment '完了フラグ'

  , ENTRYDATE datetime not null comment '登録年月日'

  , UPDATEDATE datetime not null comment '更新年月日'

  , constraint invoicedata_PKC primary key (INVOICENO)

) comment '請求書データ' ;

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 '資材品目コード'

  , MATERIALCOUNT smallint(5) unsigned comment '資材数'

  , RENTCOUNT smallint(5) unsigned comment '貸出可能数'

  , DELETEFLG smallint(5) unsigned comment '削除フラグ'

  , ENTRYDATE datetime comment '登録日付'

  , UPDATEDATE datetime comment '更新日付'

  , constraint materialinfo_PKC primary key (MATERIALITEMCODE)

) comment '資材情報' ;

-- 資材品目マスタ

drop table if exists materialitemmaster cascade;

create table materialitemmaster (

  MATERIALITEMCODE smallint(5) unsigned default 0 not null comment '資材品目コード'

  , MATERIALKINDCODE smallint(5) unsigned comment '資材種類コード'

  , MATERIALITEMNAME varchar(40) comment '資材品目名称'

  , DISPLAYORDER smallint(5) unsigned comment '表示順'

  , DELETEFLG tinyint(3) unsigned comment '削除フラグ'

  , ENTRYDATE datetime comment '登録日付'

  , UPDATEDATE datetime comment '更新日付'

  , VERSIONNO decimal(8,0) not null default 0 comment 'バージョン番号'

  , constraint materialitemmaster_PKC primary key (MATERIALITEMCODE)

) comment '資材品目マスタ' ;

-- 資材種類マスタ

drop table if exists materialkindmaster cascade;

create table materialkindmaster (

  MATERIALKINDCODE smallint(5) unsigned default 0 not null comment '資材種類コード'

  , MATERIALKINDNAME varchar(40) comment '資材種類名称'

  , DISPLAYORDER smallint(5) unsigned comment '表示順'

  , DELETEFLG tinyint(3) unsigned comment '削除フラグ'

  , ENTRYDATE datetime comment '登録日付'

  , UPDATEDATE datetime comment '更新日付'

  , constraint materialkindmaster_PKC primary key (MATERIALKINDCODE)

) comment '資材種類マスタ' ;

-- 資材履歴情報

drop table if exists materialrecordinfo cascade;

create table materialrecordinfo (

  MATERIALITEMCODE smallint(5) unsigned default 0 not null comment '資材品目コード'

  , PROCESSDATE date default '0000-00-00' not null comment '処理年月日'

  , SEQNO smallint(5) unsigned default 0 not null comment '連番'

  , RECKIND tinyint(3) unsigned comment 'レコード区分'

  , CONSTRUCTIONCODE int(10) unsigned comment '工事番号'

  , PERSONCODE int(10) unsigned comment '担当者コード'

  , MATERIALCOUNT smallint(5) unsigned comment '資材数'

  , REPAYPLANDATE date comment '返却予定年月日'

  , COMMENTTEXT varchar(80) comment 'コメント'

  , ENTRYDATE datetime comment '登録日付'

  , UPDATEDATE datetime comment '更新日付'

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

) comment '資材履歴情報' ;

-- 支払承認情報データ

drop table if exists paymentapprovalinfo cascade;

create table paymentapprovalinfo (

  COMPANYCODE decimal(8,0) default '0' not null comment '協力会社コード'

  , TARGETDATE decimal(6,0) default '0' not null comment '対象年月'

  , SEQNO decimal(3,0) default '0' not null comment '連番'

  , LINECOUNT decimal(3,0) default '0' not null comment '行番号'

  , APPROVALNO decimal(3,0) default '0' not null comment '承認順序'

  , APPROVALPERSON decimal(8,0) comment '承認者コード'

  , APPROVALPERSONNAME varchar(20) comment '承認者名'

  , APPROVALAUTHORITY decimal(1,0) comment '承認権限区分'

  , APPROVALDATE date comment '承認日付'

  , ENTRYDATE datetime comment '登録日付'

  , UPDATEDATE datetime comment '更新日付'

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

) comment '支払承認情報データ' ;

-- 支払明細データ

drop table if exists paymentdatadetail cascade;

create table paymentdatadetail (

  COMPANYCODE decimal(8,0) default '0' not null comment '協力会社コード'

  , TARGETDATE decimal(6,0) default '0' not null comment '対象年月'

  , SEQNO decimal(3,0) default '0' not null comment '連番'

  , LINECOUNT decimal(3,0) default '0' not null comment '行番号'

  , BILLPRICE decimal(10,0) comment '請求金額'

  , DISCOUNTPRICE decimal(10,0) comment '値引き金額'

  , OFFSETPRICE decimal(10,0) comment '相殺金額'

  , NEXTCOPRICE decimal(10,0) comment '次回繰越'

  , HIGHWPRICE decimal(10,0) comment '高速代'

  , HARDWPRICE decimal(10,0) comment '金物代'

  , INDSWASTETAX decimal(10,0) comment '産廃税'

  , CNSTRPRICE decimal(10,0) comment '協力金'

  , CNSTRPRICEEXIST decimal(1,0) comment '協力金有無'

  , APPROVALPERSONCODE decimal(8,0) comment '承認担当者コード'

  , APPROVALDATE date comment '担当者承認日付'

  , APPROVALENDFLG decimal(1,0) comment '承認完了フラグ'

  , ENTRYDATE datetime comment '登録日付'

  , UPDATEDATE datetime comment '更新日付'

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

) comment '支払明細データ' ;

-- 請求データ

drop table if exists requestdata cascade;

create table requestdata (

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

  , MAINCONSTRUCTIONCODE decimal(10,0) not null comment '本工事番号'

  , CONSTRUCTIONNAME varchar(120) not null comment '工事件名'

  , CONTRACTAMOUNT decimal(10,0) not null comment '請負金額'

  , PAIDAMOUNT decimal(10,0) not null comment '請求済金額'

  , REQUESTAMOUNT0 decimal(10,0) not null comment '請求金額０'

  , REQUESTAMOUNT1 decimal(10,0) not null comment '請求金額１'

  , REQUESTAMOUNT2 decimal(10,0) comment '請求金額２'

  , REQUESTAMOUNT3 decimal(10,0) comment '請求金額３'

  , REQUESTAMOUNT4 decimal(10,0) comment '請求金額４'

  , REQUESTAMOUNT5 decimal(10,0) comment '請求金額５'

  , REQUESTAMOUNT6 decimal(10,0) comment '請求金額６'

  , UNCLAIMEDAMOUNT decimal(10,0) not null comment '請求残金額'

  , UNPAIDAMOUNT decimal(10,0) not null comment '未入金金額'

  , TAXAMOUNT decimal(10,0) not null comment '消費税'

  , NOTE varchar(120) not null comment '備考'

  , ENTRYDATE datetime not null comment '登録年月日'

  , UPDATEDATE datetime not null comment '更新年月日'

  , constraint requestdata_PKC primary key (REQUESTNO,MAINCONSTRUCTIONCODE)

) comment '請求データ' ;

-- 請求データ明細

drop table if exists requestdatadetail cascade;

create table requestdatadetail (

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

  , MAINCONSTRUCTIONCODE decimal(10,0) not null comment '本工事番号'

  , CONSTRUCTIONCODE decimal(10,0) not null comment '工事番号'

  , CONSTRUCTIONKIND decimal(1,0) not null comment '工事区分'

  , REQUESTAMOUNT decimal(10,0) not null comment '請求金額'

  , TAXAMOUNT decimal(10,0) not null comment '消費税'

  , ENTRYDATE datetime not null comment '登録年月日'

  , UPDATEDATE datetime not null comment '更新年月日'

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

) comment '請求データ明細' ;

-- 請求ヘッダ

drop table if exists requesthead cascade;

create table requesthead (

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

  , REQCONSTRUCTIONCODE decimal(10,0) not null comment '請求工事番号'

  , ORDERNO decimal(2,0) not null comment '受付番号'

  , REQUESTMONTH decimal(6,0) not null comment '請求月'

  , ORDERERSDIVISION decimal(5,0) not null comment '発注者区分'

  , ORDERERSCODE decimal(5,0) not null comment '発注者コード'

  , ORDERERSNAME varchar(120) not null comment '発注者先名'

  , REQCONSTRUCTIONNAME varchar(120) not null comment '請求工事件名'

  , REQUESTTOTALAMOUNT decimal(10,0) not null comment '請求金額合計'

  , REQUESTAMOUNT decimal(10,0) not null comment '請求金額'

  , TAXAMOUNT decimal(10,0) not null comment '消費税'

  , UNPAIDAMOUNT decimal(10,0) comment '未入金'

  , NOTE varchar(120) comment '備考'

  , ASSIGNEDFLG decimal(1,0) comment '割当済フラグ'

  , INVOICENO decimal(9,0) not null comment '請求書No'

  , ENTRYDATE datetime not null comment '登録年月日'

  , UPDATEDATE datetime not null comment '更新年月日'

  , constraint requesthead_PKC primary key (REQUESTNO)

) comment '請求ヘッダ' ;

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