/branches/ddl/ProcessManagement.sql - 工事管理システム製品化対応 - Redmine

h-you / branches / ddl / ProcessManagement.sql @ 263

       -- Project Name : noname
       -- Date/Time    : 2017/07/20 9:53:08
       -- Author       : Horiuchi
       -- RDBMS Type   : MySQL
       -- Application  : A5:SQL Mk-2
       -- 担当者行動予定データ
       drop table if exists actionscheduledata cascade;
       create table actionscheduledata (
         TargetDate date not null comment '対象日'
         , PersonCode decimal(8,0) not null comment '担当者コード'
         , ActionSchedule varchar(30) comment '行動予定'
         , TargetYear year(4) not null comment '該当年'
         , TargetMonth year(4) not null comment '該当月'
         , TargetDay year(4) not null comment '該当日'
         , EntryDate datetime not null comment '登録日付'
         , UpdateDate datetime not null comment '更新日付'
         , constraint actionscheduledata_PKC primary key (TargetDate,PersonCode)
       ) comment '担当者行動予定データ' ;
       create index ActionScheduleData_Index1
         on actionscheduledata(TargetYear);
       create index ActionScheduleData_Index2
         on actionscheduledata(TargetMonth);
       create index ActionScheduleData_Index3
         on actionscheduledata(TargetDay);
       -- 承認経路マスタ
       drop table if exists approvalmaster cascade;
       create table approvalmaster (
         ApprovalCode decimal(3,0) unsigned not null comment '承認機能番号'
         , DepartmentCode decimal(5,0) unsigned not null comment '部署コード'
         , SeqNo decimal(2,0) not null comment '枝番'
         , DisplayOrder decimal(2,0) not null comment '表示順'
         , ApprovalPerson decimal(8,0) unsigned not null comment '承認者コード'
         , ApprovalAuthority decimal(1,0) not null comment '承認権限区分'
         , EntryDate datetime not null comment '登録日付'
         , UpdateDate datetime not null comment '更新日付'
         , constraint approvalmaster_PKC primary key (ApprovalCode,DepartmentCode,SeqNo)
       ) comment '承認経路マスタ' ;
       -- 出勤日報データ
       drop table if exists attendancedailydata cascade;
       create table attendancedailydata (
         PersonCode decimal(8,0) unsigned not null comment '作成者コード'
         , AttendanceDate date not null comment '日報作成日'
         , SeqNo decimal(3,0) unsigned not null comment '明細行番号'
         , ActionResult varchar(30) not null comment '行動実績'
         , OvertimeHours decimal(4,1) not null comment '残業時間'
         , WorkingComment varchar(120) comment 'コメント'
         , EntryDate datetime not null comment '登録日付'
         , UpdateDate datetime not null comment '更新日付'
         , constraint attendancedailydata_PKC primary key (PersonCode,AttendanceDate,SeqNo)
       ) comment '出勤日報データ' ;
       create index AttendDaily_Index1
         on attendancedailydata(ActionResult);
       create index AttendDaily_Index2
         on attendancedailydata(AttendanceDate);
       -- 出勤管理データ
       drop table if exists attendancedata cascade;
       create table attendancedata (
         PersonCode decimal(8,0) unsigned not null comment '作成者コード'
         , AttendanceMonth date not null comment '出勤管理年月'
         , LastDays decimal(2,0) unsigned not null comment '当月最終日'
         , AttendDayCount decimal(2,0) unsigned not null comment '出勤日数'
         , TotalOverTimes decimal(4,1) not null comment '残業時間数'
         , OrderDate date not null comment '申請日'
         , OrderNo decimal(2,0) unsigned not null comment '申請受付番号'
         , EntryDate datetime not null comment '登録日付'
         , UpdateDate datetime not null comment '更新日付'
         , constraint attendancedata_PKC primary key (PersonCode,AttendanceMonth)
       ) comment '出勤管理データ' ;
       -- 銀行営業日マスタ
       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 bk_constructionledger cascade;
       create table bk_constructionledger (
         ConstructionCode decimal(10,0) unsigned not null comment '工事コード'
         , ConstructionPeriod decimal(4,0) unsigned not null comment '事業期'
         , CreatorCode decimal(8,0) comment '作成者コード'
         , CreatorName varchar(60) comment '作成者名'
         , CreateDate date not null comment '作成日'
         , ConstructionTimes decimal(5,2) not null comment '工期（単位・月）'
         , ConstructionStart date not null comment '契約工期開始'
         , ConstructionEnd date not null comment '契約工期完了'
         , OrdersDecisionPrice decimal(14,0) not null comment '税抜受注決定金額'
         , CompanyExpenses decimal(10,0) not null comment '会社経費合計'
         , DepartmentExpenses decimal(10,0) not null comment '部署経費合計'
         , SalesExpenses decimal(10,0) not null comment '営業経費合計'
         , TotalPayment decimal(10,0) not null comment '総支払額'
         , GrossProfit decimal(10,0) not null comment '粗利'
         , Allowance decimal(10,0) not null comment '給与'
         , NetProfit decimal(10,0) not null comment '純利益'
         , ComplateFlg decimal(1,0) not null comment '入力完了フラグ'
         , IntegrationFlg decimal(1,0) not null comment '受注金額積算フラグ'
         , EntryDate datetime not null comment '登録日付'
         , UpdateDate datetime not null comment '更新日付'
         , constraint bk_constructionledger_PKC primary key (ConstructionCode)
       ) comment '工事詳細台帳データ【退避用】' ;
       -- 工事詳細台帳明細データ【退避用】
       drop table if exists bk_constructionledgerdetail cascade;
       create table bk_constructionledgerdetail (
         ConstructionCode decimal(10,0) unsigned not null comment '工事コード'
         , GroupCount decimal(2,0) unsigned not null comment 'グループ番号'
         , LineCount decimal(5,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 '工事内容'
         , CompanyType decimal(1,0) not null comment '協力会社コードタイプ'
         , CompanyCode decimal(8,0) not null comment '協力会社コード'
         , CompanyName varchar(120) comment '協力会社名称'
         , EstimatePrice decimal(12,0) not null comment '予算（見積）金額'
         , ExecutionAmount decimal(12,0) not null comment '実行金額'
         , AmountConfigRate decimal(5,2) not null comment '金額構成率'
         , PaymentBurden decimal(12,0) not null comment '支払補填額'
         , FixDataFlg decimal(1,0) not null comment '固定データフラグ'
         , IndependentFlg decimal(1,0) not null comment '独立データフラグ'
         , FluctuationFlg decimal(1,0) not null comment '増減データフラグ'
         , SalaryFlg decimal(1,0) not null comment '給与振分区分'
         , SalaryDays decimal(4,0) not null comment '給与振分日数'
         , OperatingFlg decimal(1,0) not null comment '担当中フラグ'
         , SourceCode decimal(10,0) unsigned not null comment '元工事番号'
         , JoinTitleFlg decimal(1,0) not null comment '工事名称タイトル'
         , SalaryOnRegist decimal(12,0) not null comment '登録時月額給与'
         , PurchaseOrderFlg decimal(2,0) not null comment '注文書発行フラグ'
         , EntryDate datetime not null comment '登録日付'
         , UpdateDate datetime not null comment '更新日付'
         , constraint bk_constructionledgerdetail_PKC primary key (ConstructionCode,GroupCount,LineCount)
       ) comment '工事詳細台帳明細データ【退避用】' ;
       -- 工事詳細台帳実行データ【退避用】
       drop table if exists bk_constructionledgerexcute cascade;
       create table bk_constructionledgerexcute (
         ConstructionCode decimal(10,0) unsigned not null comment '工事コード'
         , GroupCount decimal(2,0) unsigned not null comment 'グループ番号'
         , LineCount decimal(5,0) unsigned not null comment '行番号'
         , ColumnCount decimal(3,0) not null comment '列番号'
         , PaymentAmount decimal(12,0) not null comment '支払金額'
         , TargetMonth date not null comment '対象年月'
         , EntryDate datetime not null comment '登録日付'
         , UpdateDate datetime not null comment '更新日付'
         , constraint bk_constructionledgerexcute_PKC primary key (ConstructionCode,GroupCount,LineCount,ColumnCount)
       ) comment '工事詳細台帳実行データ【退避用】' ;
       -- 積算見積データ【退避用】
       drop table if exists bk_estimatedata cascade;
       create table bk_estimatedata (
         SaveDate datetime not null comment '退避日付'
         , ConstructionCode decimal(10,0) unsigned not null comment '工事コード'
         , LineCount decimal(3,0) unsigned not null comment '行番号'
         , ComponentCode decimal(5,0) unsigned not null comment '構成キー'
         , FixedItemCode varchar(1) comment '固定項目キー'
         , ItemName varchar(120) comment '作業名称'
         , SpecName varchar(120) comment '作業／品質・形状・寸法'
         , PriceValue decimal(14,0) not null comment '金額'
         , note varchar(60) comment '備考'
         , MyExpensesFlg decimal(1,0) not null comment '自社経費フラグ'
         , InputFlg decimal(1,0) not null comment '入力フラグ'
         , EntryDate datetime not null comment '登録日付'
         , UpdateDate datetime not null comment '更新日付'
         , constraint bk_estimatedata_PKC primary key (SaveDate,ConstructionCode,LineCount)
       ) comment '積算見積データ【退避用】' ;
       -- 積算見積ページデータ【退避用】
       drop table if exists bk_estimatedatabody cascade;
       create table bk_estimatedatabody (
         SaveDate datetime not null comment '退避日付'
         , ConstructionCode decimal(10,0) unsigned not null comment '工事コード'
         , PageCount decimal(4,0) unsigned not null comment 'ページ番号'
         , Category decimal(1,0) not null comment 'ページ区分'
         , UnionComponentCode decimal(5,0) unsigned not null comment '所属構成キー'
         , ComponentCode decimal(5,0) unsigned not null comment '構成キー'
         , ItemCode decimal(5,0) unsigned not null comment '工種キー'
         , SelectComponent decimal(5,0) unsigned not null comment '選択構成キー'
         , PageTitle varchar(120) not null comment 'ページ表示名'
         , DetailLineCount decimal(3,0) unsigned not null comment '明細行番号'
         , EntryDate datetime not null comment '登録日付'
         , UpdateDate datetime not null comment '更新日付'
         , constraint bk_estimatedatabody_PKC primary key (SaveDate,ConstructionCode,PageCount)
       ) comment '積算見積ページデータ【退避用】' ;
       -- 積算見積データ明細【退避用】
       drop table if exists bk_estimatedatadetail cascade;
       create table bk_estimatedatadetail (
         SaveDate datetime not null comment '退避日付'
         , ConstructionCode decimal(10,0) unsigned not null comment '工事コード'
         , PageCount decimal(4,0) unsigned not null comment 'ページ番号'
         , LineCount decimal(3,0) unsigned not null comment '行番号'
         , DataType decimal(1,0) not null comment 'データ種別'
         , ComponentCode decimal(5,0) unsigned not null comment '構成キー'
         , ItemCode decimal(5,0) unsigned not null comment '工種キー'
         , SpecCode decimal(5,0) unsigned not null comment '作業キー'
         , ItemName varchar(120) comment '作業名称'
         , SpecName varchar(120) comment '作業／品質・形状・寸法'
         , Unitcount decimal(8,2) not null comment '数量'
         , UnitName varchar(30) comment '単位名称'
         , UnitPrice decimal(9,2) not null comment '単価'
         , LineTotal decimal(12,0) not null comment '金額'
         , note varchar(60) comment '備考'
         , CompanyCode decimal(8,0) not null comment '協力会社コード'
         , MyExpensesFlg decimal(1,0) not null comment '自社経費フラグ'
         , InputFlg decimal(1,0) comment '入力フラグ'
         , EntryDate datetime not null comment '登録日付'
         , UpdateDate datetime not null comment '更新日付'
         , constraint bk_estimatedatadetail_PKC primary key (SaveDate,ConstructionCode,PageCount,LineCount)
       ) comment '積算見積データ明細【退避用】' ;
       -- 法人格マスタ
       drop table if exists businesstypemaster cascade;
       create table businesstypemaster (
         BusinessTypeCode decimal(5,0) unsigned not null comment '法人格コード'
         , DisplayOrder decimal(5,0) unsigned not null comment '表示順'
         , BusinessTypeName varchar(100) not null comment '業種名'
         , EntryDate datetime not null comment '登録日付'
         , UpdateDate datetime not null comment '更新日付'
         , constraint businesstypemaster_PKC primary key (BusinessTypeCode)
       ) comment '法人格マスタ' ;
       create index BusinessTypeMaster_Index1
         on businesstypemaster(DisplayOrder);
       -- 共通費大項目マスタ
       drop table if exists commoncostlarge cascade;
       create table commoncostlarge (
         LargeCode decimal(5,0) unsigned not null comment '大項目キー'
         , DisplayOrder decimal(5,0) unsigned not null comment '表示順'
         , LargeName varchar(50) not null comment '大項目名称'
         , CommentText varchar(120) comment '説明書き'
         , DeleteFlg decimal(1,0) not null comment '削除フラグ'
         , EntryDate datetime not null comment '登録日付'
         , UpdateDate datetime not null comment '更新日付'
         , constraint commoncostlarge_PKC primary key (LargeCode)
       ) comment '共通費大項目マスタ' ;
       -- 共通費工種関連マスタ
       drop table if exists commoncostlinkms cascade;
       create table commoncostlinkms (
         LargeCode decimal(5,0) unsigned not null comment '大項目キー'
         , MiddleCode decimal(5,0) unsigned not null comment '中項目キー'
         , ItemCode decimal(5,0) unsigned not null comment '工種キー'
         , EntryDate datetime comment '登録日付'
         , UpdateDate datetime comment '更新日付'
         , constraint commoncostlinkms_PKC primary key (LargeCode,MiddleCode,ItemCode)
       ) comment '共通費工種関連マスタ' ;
       -- 共通費中項目マスタ
       drop table if exists commoncostmiddle cascade;
       create table commoncostmiddle (
         LargeCode decimal(5,0) unsigned not null comment '大項目キー'
         , MiddleCode decimal(5,0) unsigned not null comment '中項目キー'
         , DisplayOrder decimal(5,0) unsigned not null comment '表示順'
         , MiddleName varchar(50) not null comment '中項目名称'
         , CommentText varchar(120) comment '説明書き'
         , DeleteFlg decimal(1,0) not null comment '削除フラグ'
         , EntryDate datetime not null comment '登録日付'
         , UpdateDate datetime not null comment '更新日付'
         , constraint commoncostmiddle_PKC primary key (LargeCode,MiddleCode)
       ) comment '共通費中項目マスタ' ;
       -- 共通費中項目キーマスタ
       drop table if exists commoncostmiddlesearchword cascade;
       create table commoncostmiddlesearchword (
         LargeCode decimal(5,0) unsigned not null comment '大項目キー'
         , MiddleCode decimal(5,0) unsigned not null comment '中項目キー'
         , SeqNo decimal(5,0) unsigned not null comment '順序'
         , SearchWord varchar(50) not null comment '検索項目'
         , EntryDate datetime not null comment '登録日付'
         , UpdateDate datetime not null comment '更新日付'
         , constraint commoncostmiddlesearchword_PKC primary key (LargeCode,MiddleCode,SeqNo)
       ) comment '共通費中項目キーマスタ' ;
       -- 構成工種関連マスタ
       drop table if exists componentlinkmaster cascade;
       create table componentlinkmaster (
         ComponentCode decimal(5,0) unsigned not null comment '構成キー'
         , ItemCode decimal(5,0) unsigned not null comment '工種キー'
         , DisplayOrder decimal(5,0) unsigned comment '表示順'
         , EntryDate datetime not null comment '登録日付'
         , UpdateDate datetime not null comment '更新日付'
         , constraint componentlinkmaster_PKC primary key (ComponentCode,ItemCode)
       ) comment '構成工種関連マスタ' ;
       create index ComponentLinkMaster_Index1
         on componentlinkmaster(ComponentCode);
       create index ComponentLinkMaster_Index2
         on componentlinkmaster(ItemCode);
       -- 構成マスタ
       drop table if exists componentmaster cascade;
       create table componentmaster (
         ComponentCode decimal(5,0) unsigned not null comment '構成キー'
         , DisplayOrder decimal(5,0) unsigned not null comment '表示順'
         , ComponentName varchar(120) not null comment '構成名称'
         , SubItemsFlg decimal(1,0) comment '下位項目フラグ'
         , DirectInputFlg decimal(1,0) comment '直接入力フラグ'
         , MyExpensesFlg decimal(1,0) comment '自社経費フラグ'
         , DeleteFlg decimal(1,0) not null comment '削除フラグ'
         , EntryDate datetime not null comment '登録日付'
         , UpdateDate datetime not null comment '更新日付'
         , constraint componentmaster_PKC primary key (ComponentCode)
       ) comment '構成マスタ' ;
       create index ComponentMaster_Index1
         on componentmaster(DisplayOrder);
       -- 構成工事種別関連マスタ
       drop table if exists componenttotypemaster cascade;
       create table componenttotypemaster (
         ComponentCode decimal(5,0) unsigned not null comment '構成キー'
         , TypeCode decimal(5,0) unsigned not null comment '工事種別コード'
         , Dummy decimal(1,0) not null comment 'ダミー項目'
         , EntryDate datetime not null comment '登録日付'
         , UpdateDate datetime not null comment '更新日付'
         , constraint componenttotypemaster_PKC primary key (ComponentCode,TypeCode)
       ) comment '構成工事種別関連マスタ' ;
       create index ComponentToTypeMaster_Index1
         on componenttotypemaster(ComponentCode);
       create index ComponentToTypeMaster_Index2
         on componenttotypemaster(TypeCode);
       -- 工事基本情報
       drop table if exists constructionbaseinfo cascade;
       create table constructionbaseinfo (
         ConstructionCode decimal(10,0) unsigned not null comment '工事コード'
         , TyingFlg decimal(1,0) not null comment '紐付データフラグ'
         , JoinFlg decimal(1,0) not null comment '結合契約工事フラグ'
         , ConstructionYear smallint(5) unsigned not null comment '工事年度'
         , ConstructionPeriod smallint(5) unsigned not null comment '工事期数'
         , RequestedDate date comment '依頼受け日'
         , EstimatesSubmitDeadline date comment '見積提出期限'
         , EstimatesSubmittedDate date comment '見積提出日'
         , ProvisionalOrderDate date comment '仮受注日'
         , OrderDate date comment '受注日'
         , OrderStartingDate date comment '開始予定日'
         , OrderCompletionDate date comment '完了予定日'
         , PreparationStartDate date comment '施工準備開始日'
         , ConstructionStartingDate date comment '施工開始日'
         , ConstructionCompletionDate date comment '施工完了日'
         , NonOrderDate date comment '非受注日'
         , OrdersDecisionPrice decimal(12,0) not null comment '税抜受注決定金額'
         , OrdersDecisionPriceInTax decimal(12,0) not null comment '税込受注決定金額'
         , SalesPersonCode decimal(8,0) comment '営業担当者名'
         , ConstructionPersonCode decimal(8,0) not null comment '工事担当者'
         , ConstrSubPersonCode decimal(8,0) not null comment '工事副担当者'
         , ConstructionInstructor decimal(8,0) not null comment '工事指導員'
         , TransferConstruction decimal(8,0) not null comment '工事移管先コード'
         , TransferConstructionDate date comment '工事移管日'
         , OrderersDivision decimal(5,0) not null comment '発注者区分'
         , OrderersCode decimal(5,0) unsigned not null comment '発注者コード'
         , EstimatesExpirationDate date comment '見積有効期限'
         , ConstructionPeriodStart date comment '契約工期開始日'
         , ConstructionPeriodEnd date comment '契約工期完了'
         , ConstructionPeriodStart2 date comment '契約工期開始（延長時）'
         , ConstructionPeriodEnd2 date comment '契約工期完了（延長時）'
         , StartDate date comment '工事開始日'
         , EndDate date comment '工事終了日'
         , PurchaseOrderMailingDate date comment '注文書郵送日'
         , PurchaseOrderReturnDate date comment '注文書返送日'
         , PurchaseOrderReturnCheckDate date comment '注文書返送確認日'
         , ConsumptionTaxFlg decimal(1,0) not null comment '消費税フラグ'
         , PrimeContractorFlg decimal(1,0) not null comment '元請フラグ'
         , SalesCostFlg decimal(1,0) not null comment '営業経費発生フラグ'
         , ConstructionStatusFlg decimal(2,0) not null comment '工事状態フラグ'
         , ConstructionType decimal(5,0) not null comment '工事種別コード'
         , EstimateType decimal(1,0) not null comment '見積種別'
         , BillingStartDate date not null comment '請求準備開始日'
         , BillingDate date not null comment '請求日'
         , BillingSplitFlg decimal(1,0) not null comment '請求回数フラグ'
         , BillingSendDate date comment '請求書郵送/手渡し日'
         , LedgerComplateDate date not null comment '工事詳細台帳入力完了日付'
         , EntryDate datetime not null comment '登録日付'
         , UpdateDate datetime not null comment '更新日付'
         , constraint constructionbaseinfo_PKC primary key (ConstructionCode)
       ) comment '工事基本情報' ;
       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);
       -- 工事基本情報明細
       drop table if exists constructionbaseinfodetail cascade;
       create table constructionbaseinfodetail (
         ConstructionCode decimal(10,0) unsigned not null comment '工事コード'
         , DetailNo decimal(2,0) not null comment '明細番号'
         , DetailString varchar(120) comment '明細内容'
         , EntryDate datetime not null comment '登録日付'
         , UpdateDate datetime not null comment '更新日付'
         , constraint constructionbaseinfodetail_PKC primary key (ConstructionCode,DetailNo)
       ) comment '工事基本情報明細' ;
       create index BaseInfoDetail_Idx1
         on constructionbaseinfodetail(ConstructionCode);
       create index BaseInfoDetail_Idx2
         on constructionbaseinfodetail(DetailNo);
       -- 工事施工予算データ
       drop table if exists constructionbudget cascade;
       create table constructionbudget (
         ConstructionCode decimal(10,0) unsigned not null comment '工事コード'
         , CreatorCode decimal(8,0) not null comment '作成者コード'
         , CreatorName varchar(60) comment '作成者名'
         , CreatorCosts decimal(11,0) not null comment '作成者給与'
         , AssistantCode decimal(8,0) not null comment '副担当者コード'
         , AssistantName varchar(60) comment '副担当者名'
         , AssistantCosts decimal(11,0) not null comment '副担当者給与'
         , InstructorCode decimal(8,0) not null comment '工事指導員コード'
         , InstructorName varchar(60) comment '工事指導員名'
         , InstructorCosts decimal(11,0) not null comment '工事指導員給与'
         , CreateDate date not null comment '作成日'
         , ConstructionTimes decimal(5,2) not null comment '工期（単位・月）'
         , ConstructionStart date not null comment '契約工期開始'
         , ConstructionEnd date not null comment '契約工期完了'
         , InstructorTimes decimal(5,2) not null comment '指導員稼働月数'
         , SalaryFlg decimal(1,0) not null comment '給与振分区分'
         , SalaryDays decimal(4,0) not null comment '振分日数'
         , A_SalaryFlg decimal(1,0) not null comment '副担当者給与振分区分'
         , A_SalaryDays decimal(4,0) not null comment '副担当者振分日数'
         , I_SalaryFlg decimal(1,0) not null comment '指導員給与振分区分'
         , I_SalaryDays decimal(4,0) not null comment '指導員振分日数'
         , EntryDate datetime not null comment '登録日付'
         , UpdateDate datetime not null comment '更新日付'
         , constraint constructionbudget_PKC primary key (ConstructionCode)
       ) comment '工事施工予算データ' ;
       -- 工事施工予算データ明細
       drop table if exists constructionbudgetdetail cascade;
       create table constructionbudgetdetail (
         ConstructionCode decimal(10,0) unsigned not null comment '工事コード'
         , GroupCount decimal(2,0) unsigned 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 '工事内容'
         , CompanyType decimal(1,0) not null comment '協力会社コードタイプ'
         , CompanyCode decimal(8,0) not null comment '協力会社コード'
         , CompanyName varchar(120) comment '協力会社名称'
         , EstimatePrice decimal(12,0) not null comment '積算時見積金額'
         , NegotiationPrice decimal(12,0) not null comment '積算時交渉金額'
         , OrderDesiredAmount decimal(12,0) not null comment '発注希望金額'
         , ExecutionAmount decimal(12,0) not null comment '実行金額'
         , AmountConfigRate decimal(5,2) not null comment '金額構成率'
         , NegotiateFlg decimal(1,0) not null comment '協力業者交渉フラグ'
         , EntryDate datetime not null comment '登録日付'
         , UpdateDate datetime not null comment '更新日付'
         , constraint constructionbudgetdetail_PKC primary key (ConstructionCode,GroupCount,LineCount)
       ) comment '工事施工予算データ明細' ;
       -- 工種マスタ
       drop table if exists constructionitemmaster cascade;
       create table constructionitemmaster (
         ItemCode decimal(5,0) unsigned not null comment '工種キー'
         , DisplayOrder decimal(5,0) unsigned not null comment '表示順'
         , ItemName varchar(120) not null comment '工種名称'
         , SubItemsFlg decimal(1,0) comment '下位項目フラグ'
         , MyExpensesFlg decimal(1,0) not null comment '自社経費フラグ'
         , DeleteFlg decimal(1,0) not null comment '削除フラグ'
         , EntryDate datetime not null comment '登録日付'
         , UpdateDate datetime not null comment '更新日付'
         , constraint constructionitemmaster_PKC primary key (ItemCode)
       ) comment '工種マスタ' ;
       -- 工事詳細台帳データ
       drop table if exists constructionledger cascade;
       create table constructionledger (
         ConstructionCode decimal(10,0) unsigned not null comment '工事コード'
         , ConstructionPeriod decimal(4,0) unsigned not null comment '事業期'
         , CreatorCode decimal(8,0) comment '作成者コード'
         , CreatorName varchar(60) comment '作成者名'
         , CreateDate date not null comment '作成日'
         , ConstructionTimes decimal(5,2) not null comment '工期（単位・月）'
         , ConstructionStart date not null comment '契約工期開始'
         , ConstructionEnd date not null comment '契約工期完了'
         , OrdersDecisionPrice decimal(14,0) not null comment '税抜受注決定金額'
         , CompanyExpenses decimal(10,0) not null comment '会社経費合計'
         , DepartmentExpenses decimal(10,0) not null comment '部署経費合計'
         , SalesExpenses decimal(10,0) not null comment '営業経費合計'
         , TotalPayment decimal(10,0) not null comment '総支払額'
         , GrossProfit decimal(10,0) not null comment '粗利'
         , Allowance decimal(10,0) not null comment '給与'
         , NetProfit decimal(10,0) not null comment '純利益'
         , ComplateFlg decimal(1,0) not null comment '入力完了フラグ'
         , IntegrationFlg decimal(1,0) not null comment '受注金額積算フラグ'
         , EntryDate datetime not null comment '登録日付'
         , UpdateDate datetime not null comment '更新日付'
         , constraint constructionledger_PKC primary key (ConstructionCode)
       ) comment '工事詳細台帳データ' ;
       -- 工事詳細台帳明細データ
       drop table if exists constructionledgerdetail cascade;
       create table constructionledgerdetail (
         ConstructionCode decimal(10,0) unsigned not null comment '工事コード'
         , GroupCount decimal(2,0) unsigned 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 '工事内容'
         , CompanyType decimal(1,0) not null comment '協力会社コードタイプ'
         , CompanyCode decimal(8,0) not null comment '協力会社コード'
         , CompanyName varchar(120) comment '協力会社名称'
         , EstimatePrice decimal(12,0) not null comment '予算（見積）金額'
         , ExecutionAmount decimal(12,0) not null comment '実行金額'
         , AmountConfigRate decimal(5,2) not null comment '金額構成率'
         , PaymentBurden decimal(12,0) not null comment '支払補填額'
         , FixDataFlg decimal(1,0) not null comment '固定データフラグ'
         , IndependentFlg decimal(1,0) not null comment '独立データフラグ'
         , FluctuationFlg decimal(1,0) not null comment '増減データフラグ'
         , SalaryFlg decimal(1,0) not null comment '給与振分区分'
         , SalaryDays decimal(4,0) not null comment '給与振分日数'
         , OperatingFlg decimal(1,0) not null comment '担当中フラグ'
         , SourceCode decimal(10,0) unsigned not null comment '元工事番号'
         , JoinTitleFlg decimal(1,0) not null comment '工事名称タイトル'
         , SalaryOnRegist decimal(12,0) not null comment '登録時月額給与'
         , PurchaseOrderFlg decimal(2,0) not null comment '注文書発行フラグ'
         , EntryDate datetime not null comment '登録日付'
         , UpdateDate datetime not null comment '更新日付'
         , constraint constructionledgerdetail_PKC primary key (ConstructionCode,GroupCount,LineCount)
       ) comment '工事詳細台帳明細データ' ;
       create index LedgerDetail_Index01
         on constructionledgerdetail(ConstructionCode);
       create index LedgerDetail_Index02
         on constructionledgerdetail(GroupCount);
       create index LedgerDetail_Index03
         on constructionledgerdetail(SourceCode);
       -- 工事詳細台帳実行データ
       drop table if exists constructionledgerexcute cascade;
       create table constructionledgerexcute (
         ConstructionCode decimal(10,0) unsigned not null comment '工事コード'
         , GroupCount decimal(2,0) unsigned not null comment 'グループ番号'
         , LineCount decimal(3,0) unsigned not null comment '行番号'
         , ColumnCount decimal(3,0) not null comment '列番号'
         , PaymentAmount decimal(12,0) not null comment '支払金額'
         , TargetMonth date not null comment '対象年月'
         , EntryDate datetime not null comment '登録日付'
         , UpdateDate datetime not null comment '更新日付'
         , constraint constructionledgerexcute_PKC primary key (ConstructionCode,GroupCount,LineCount,ColumnCount)
       ) comment '工事詳細台帳実行データ' ;
       create index LedgerExcute_Index1
         on constructionledgerexcute(ConstructionCode,TargetMonth,GroupCount,LineCount);
       -- 工事リンク情報
       drop table if exists constructionlink cascade;
       create table constructionlink (
         ConstructionCode decimal(10,0) unsigned not null comment '工事コード'
         , FluctuationCode decimal(10,0) not null comment '子工事番号'
         , LinkType decimal(1,0) not null comment 'リンク種別'
         , EntryDate datetime not null comment '登録日付'
         , UpdateDate datetime not null comment '更新日付'
         , constraint constructionlink_PKC primary key (ConstructionCode,FluctuationCode)
       ) comment '工事リンク情報' ;
       create index ConstructionLink_Index1
         on constructionlink(ConstructionCode);
       create index ConstructionLink_Index2
         on constructionlink(FluctuationCode);
       create index ConstructionLink_Index3
         on constructionlink(LinkType);
       -- 工事資材情報
       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 constructionprogressdate cascade;
       create table constructionprogressdate (
         ConstructionCode decimal(10,0) not null comment '工事番号'
         , ConstructionStatusFlg decimal(2,0) not null comment '設定工事状態'
         , PreviousStatusFlg decimal(2,0) comment '変更前状態'
         , ChangeDate datetime not null comment '設定日付'
         , ChangePersonCode decimal(8,0) not null comment '設定担当者'
         , EntryDate datetime not null comment '登録日付'
         , UpdateDate datetime not null comment '更新日付'
         , constraint constructionprogressdate_PKC primary key (ConstructionCode,ConstructionStatusFlg)
       ) comment '案件進捗日付データ' ;
       -- 作業マスタ
       drop table if exists constructionspecmaster cascade;
       create table constructionspecmaster (
         ItemCode decimal(5,0) unsigned not null comment '工種キー'
         , SpecCode decimal(5,0) unsigned not null comment '作業キー'
         , DisplayOrder decimal(5,0) unsigned not null comment '表示順'
         , ItemName varchar(120) comment '作業名称'
         , SpecName varchar(120) comment '作業名称'
         , UnitName varchar(30) comment '単位名称'
         , UnitPrice decimal(9,2) not null comment '単価'
         , DeleteFlg decimal(1,0) not null comment '削除フラグ'
         , EntryDate datetime not null comment '登録日付'
         , UpdateDate datetime not null comment '更新日付'
         , constraint constructionspecmaster_PKC primary key (ItemCode,SpecCode)
       ) comment '作業マスタ' ;
       create index ConstructionSpecMaster_Index1
         on constructionspecmaster(DisplayOrder);
       -- 作業単価マスタ
       drop table if exists constructionspecunitprice cascade;
       create table constructionspecunitprice (
         ComponentCode decimal(5,0) unsigned not null comment '構成キー'
         , ItemCode decimal(5,0) unsigned not null comment '工種キー'
         , SpecCode decimal(5,0) unsigned not null comment '作業キー'
         , ConstructionTypeCode mediumint(8) unsigned not null comment '工事種別コード'
         , UnitPrice decimal(9,2) not null comment '単価'
         , EntryDate datetime not null comment '登録日付'
         , UpdateDate datetime not null comment '更新日付'
         , constraint constructionspecunitprice_PKC primary key (ComponentCode,ItemCode,SpecCode,ConstructionTypeCode)
       ) comment '作業単価マスタ' ;
       -- 工事種別マスタ
       drop table if exists constructiontypemaster cascade;
       create table constructiontypemaster (
         TypeCode decimal(5,0) unsigned not null comment '工事種別コード'
         , DisplayOrder decimal(5,0) unsigned not null comment '表示順'
         , NameString varchar(100) not null comment '名称'
         , PublicFlg decimal(1,0) not null comment '工事種別フラグ'
         , SecRank decimal(1,0) not null comment '保護区分ランク'
         , ExpensesLink decimal(1,0) not null comment '経費使用区分'
         , DeleteFlg decimal(1,0) not null comment '削除フラグ'
         , EntryDate datetime not null comment '登録日付'
         , UpdateDate datetime not null comment '更新日付'
         , constraint constructiontypemaster_PKC primary key (TypeCode)
       ) comment '工事種別マスタ' ;
       -- 担当者毎経費データ
       drop table if exists costdataofperson cascade;
       create table costdataofperson (
         PersonCode decimal(8,0) unsigned not null comment '担当者コード'
         , ActionDate date not null comment '対象日付'
         , DataType decimal(1,0) not null comment 'データ区分'
         , DataAddCount decimal(5,0) unsigned not null comment 'データ追加番号'
         , TargetMonth varchar(7) not null comment '対象年月'
         , ConstructionCode decimal(10,0) unsigned comment '工事コード'
         , SuppliersCode decimal(5,0) unsigned not null comment '支払先コード'
         , SuppliersName varchar(120) comment '支払先名称'
         , PaymentContent varchar(60) comment '支払内容'
         , PaymentType decimal(1,0) not null comment '支払方法フラグ'
         , EntryPrice decimal(12,0) not null comment '金額（税抜）'
         , EntryPriceInTax decimal(12,0) not null comment '金額（税込）'
         , SlipNumber varchar(12) comment '伝票番号'
         , ComplateFlg decimal(1,0) not null comment '引込済みフラグ'
         , ApprovalFlg decimal(1,0) not null comment '承認フラグ'
         , EntryDate datetime not null comment '登録日付'
         , UpdateDate datetime not null comment '更新日付'
         , constraint costdataofperson_PKC primary key (PersonCode,ActionDate,DataType,DataAddCount)
       ) comment '担当者毎経費データ' ;
       create index CostDataOfPerson_Index1
         on costdataofperson(TargetMonth);
       create index CostDataOfPerson_Index2
         on costdataofperson(ConstructionCode);
       -- 発注者登録申請データ
       drop table if exists costomerregist cascade;
       create table costomerregist (
         PersonCode decimal(8,0) unsigned not null comment '申請者番号'
         , CreateDate date not null comment '作成日'
         , SeqNo decimal(2,0) not null comment 'キー連番'
         , DataMode decimal(1,0) not null comment 'データ区分'
         , SourceCotegory decimal(5,0) unsigned not null comment '発注者区分'
         , SourceCode decimal(5,0) unsigned not null comment '発注者コード'
         , PetitionPeriod decimal(4,0) unsigned not null comment '事業期'
         , OrderFlg decimal(1,0) not null comment '法人団体・個人フラグ'
         , CorporateStatusName varchar(100) comment '法人格名称'
         , CorporateStatusPoint decimal(1,0) comment '法人格名称位置'
         , OrderersName1 varchar(60) not null comment '発注者名1'
         , OrderersName2 varchar(60) comment '発注者名２'
         , DepartmentName varchar(60) comment '部署名'
         , ChargePersonName varchar(60) comment '担当者名'
         , ZipCode varchar(8) comment '郵便番号'
         , Address1 varchar(60) comment '住所１'
         , Address2 varchar(60) comment '住所2'
         , Address3 varchar(60) comment '住所3'
         , PhoneNumber varchar(13) comment '電話番号'
         , FaxNumber varchar(13) comment 'FAX番号'
         , MailAddress varchar(257) comment 'メールアドレス'
         , Note varchar(300) comment '備考'
         , OrderCotegory decimal(5,0) unsigned not null comment '発注者区分'
         , OrderDate date not null comment '申請日'
         , OrderNo decimal(2,0) unsigned not null comment '申請受付番号'
         , EntryDate datetime not null comment '登録日付'
         , UpdateDate datetime not null comment '更新日付'
         , constraint costomerregist_PKC primary key (PersonCode,CreateDate,SeqNo)
       ) comment '発注者登録申請データ' ;
       create index CostomerRegist_Index1
         on costomerregist(PetitionPeriod);
       -- 日報データ
       drop table if exists dailydataconstruction cascade;
       create table dailydataconstruction (
         PersonCode decimal(8,0) unsigned not null comment '担当者コード'
         , DailyDataDate date not null comment '日報作成日'
         , ConstructionCode decimal(10,0) unsigned not null comment '工事コード'
         , Weather varchar(60) comment '天気'
         , EntryDate datetime not null comment '登録日付'
         , UpdateDate datetime not null comment '更新日付'
         , constraint dailydataconstruction_PKC primary key (PersonCode,DailyDataDate,ConstructionCode)
       ) comment '日報データ' ;
       create index DailyDataConstruction_Index1
         on dailydataconstruction(PersonCode);
       create index DailyDataConstruction_Index2
         on dailydataconstruction(DailyDataDate);
       create index DailyDataConstruction_Index3
         on dailydataconstruction(ConstructionCode);
       -- 日報データ (明細)
       drop table if exists dailydatadetail cascade;
       create table dailydatadetail (
         PersonCode decimal(8,0) unsigned not null comment '担当者コード'
         , DailyDataDate date not null comment '日報作成日'
         , ConstructionCode decimal(10,0) unsigned not null comment '工事コード'
         , SeqNo decimal(3,0) unsigned not null comment '明細行番号'
         , JobCategoryCode decimal(5,0) not null comment '職種キー'
         , CompanyCode decimal(8,0) not null comment '協力会社コード'
         , TodayHeadCount decimal(4,1) not null comment '当日人員数'
         , TotalHeadCount decimal(5,1) not null comment '累計人員数'
         , TodayWork varchar(100) comment '当日作業内容'
         , NextdayWork varchar(100) comment '翌日作業内容'
         , NextdayHeadCount decimal(4,1) not null comment '翌日人員数'
         , EntryDate datetime not null comment '登録日付'
         , UpdateDate datetime not null comment '更新日付'
         , constraint dailydatadetail_PKC primary key (PersonCode,DailyDataDate,ConstructionCode,SeqNo)
       ) comment '日報データ (明細)' ;
       create index DailyDataDetail_Index
         on dailydatadetail(PersonCode,DailyDataDate,ConstructionCode);
       -- 日報データ (現場巡回)
       drop table if exists dailydatafield cascade;
       create table dailydatafield (
         PersonCode decimal(8,0) unsigned not null comment '作成者コード'
         , DailyDataDate date not null comment '日報作成日'
         , ConstructionCode decimal(10,0) unsigned not null comment '工事コード'
         , SeqNo decimal(3,0) unsigned not null comment '明細行番号'
         , PatrolTime datetime not null comment '巡回時間'
         , ContentsText varchar(200) comment '内容'
         , EntryDate datetime not null comment '登録日付'
         , UpdateDate datetime not null comment '更新日付'
         , constraint dailydatafield_PKC primary key (PersonCode,DailyDataDate,ConstructionCode,SeqNo)
       ) comment '日報データ (現場巡回)' ;
       create index DailyDataField_Index
         on dailydatafield(PersonCode,DailyDataDate,ConstructionCode);
       -- 日報データ (資材)
       drop table if exists dailydatamaterials cascade;
       create table dailydatamaterials (
         PersonCode decimal(8,0) unsigned not null comment '作成者コード'
         , DailyDataDate date not null comment '日報作成日'
         , ConstructionCode decimal(10,0) unsigned not null comment '工事コード'
         , SeqNo decimal(3,0) unsigned not null comment '明細行番号'
         , Materials varchar(100) comment '資材名'
         , Quantity varchar(100) comment '数量'
         , Production varchar(100) comment '制作会社'
         , Delivery varchar(100) comment '納入業者'
         , QualityControl varchar(100) comment '品質管理事項'
         , EntryDate datetime not null comment '登録日付'
         , UpdateDate datetime not null comment '更新日付'
         , constraint dailydatamaterials_PKC primary key (PersonCode,DailyDataDate,ConstructionCode,SeqNo)
       ) comment '日報データ (資材)' ;
       create index DailyDataMaterials_Index
         on dailydatamaterials(PersonCode,DailyDataDate,ConstructionCode);
       -- 日報データ (協力業者指示)
       drop table if exists dailydatasubcontractors cascade;
       create table dailydatasubcontractors (
         PersonCode decimal(8,0) unsigned not null comment '作成者コード'
         , DailyDataDate date not null comment '日報作成日'
         , ConstructionCode decimal(10,0) unsigned not null comment '工事コード'
         , SeqNo decimal(3,0) unsigned not null comment '明細行番号'
         , Attendee varchar(100) comment '出席者'
         , ContentsText varchar(200) comment '内容'
         , EntryDate datetime not null comment '登録日付'
         , UpdateDate datetime not null comment '更新日付'
         , constraint dailydatasubcontractors_PKC primary key (PersonCode,DailyDataDate,ConstructionCode,SeqNo)
       ) comment '日報データ (協力業者指示)' ;
       create index DailyDataSubcontractors_Index
         on dailydatasubcontractors(PersonCode,DailyDataDate,ConstructionCode);
       -- 日報データ (車両)
       drop table if exists dailydatavehicles cascade;
       create table dailydatavehicles (
         PersonCode decimal(8,0) unsigned not null comment '作成者コード'
         , DailyDataDate date not null comment '日報作成日'
         , ConstructionCode decimal(10,0) unsigned not null comment '工事コード'
         , SeqNo decimal(3,0) unsigned not null comment '明細行番号'
         , Model varchar(100) comment '機種'
         , Performance varchar(100) comment '性能'
         , Owner varchar(100) comment '所有会社'
         , Driver varchar(100) comment '当日運転者'
         , StartWorkingTime datetime not null comment '作業時間開始'
         , EndWorkingTime datetime not null comment '作業時間終了'
         , EntryDate datetime not null comment '登録日付'
         , UpdateDate datetime not null comment '更新日付'
         , constraint dailydatavehicles_PKC primary key (PersonCode,DailyDataDate,ConstructionCode,SeqNo)
       ) comment '日報データ (車両)' ;
       create index DailyDataVehicles_Index
         on dailydatavehicles(PersonCode,DailyDataDate,ConstructionCode);
       -- 日マスタ
       drop table if exists daymaster cascade;
       create table daymaster (
         days smallint(6) not null comment '日'
       ) comment '日マスタ' ;
       create index day_Index1
         on daymaster(days);
       -- 締日マスタ
       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 departmentexpensesmaster cascade;
       create table departmentexpensesmaster (
         DepartmentCode decimal(5,0) unsigned not null comment '部署コード'
         , ExpensesPeriod smallint(6) not null comment '事業期'
         , NameCode decimal(5,0) unsigned not null comment '経費名称コード'
         , DisplayOrder decimal(5,0) unsigned not null comment '表示順'
         , ExpensesRaito decimal(4,2) not null comment '経費率'
         , DeleteFlg decimal(1,0) not null comment '削除フラグ'
         , EntryDate datetime not null comment '登録日付'
         , UpdateDate datetime not null comment '更新日付'
         , constraint departmentexpensesmaster_PKC primary key (DepartmentCode,ExpensesPeriod,NameCode)
       ) comment '部署経費率マスタ' ;
       -- 部署マスタ
       drop table if exists departmentmaster cascade;
       create table departmentmaster (
         DepartmentCode decimal(5,0) unsigned not null comment '部署コード'
         , DisplayOrder decimal(5,0) unsigned not null comment '表示順'
         , DepartmentString varchar(100) not null comment '部署名'
         , ActionScheduleFlg decimal(1,0) not null comment '行動予定表示フラグ'
         , StaffAssignFlg decimal(1,0) not null comment '人員配置表示フラグ'
         , DeleteFlg decimal(1,0) not null comment '削除フラグ'
         , EntryDate datetime not null comment '登録日付'
         , UpdateDate datetime not null comment '更新日付'
         , constraint departmentmaster_PKC primary key (DepartmentCode)
       ) comment '部署マスタ' ;
       create index DepartmentMaster_index1
         on departmentmaster(DisplayOrder);
       -- 入金確認承認データ
       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 '入金金額（手形）'
         , DEPOSITPERSONCODE decimal(8,0) not null comment '入金担当者コード'
         , NOTE varchar(120) 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 '対象年月'
         , REQUESTNO decimal(10,0) not null 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 'その他'
         , DIFFERENCEAMOUNT decimal(10,0) comment '差分'
         , CONFIRMATIONPERSONCODE decimal(8,0) comment '確認担当者コード'
         , CONFIRMATIONDATE date comment '担当者確認日付'
         , CONFIRMATIONENDFLG decimal(1,0) comment '確認完了フラグ'
         , NOTE varchar(120) comment '備考'
         , ENTRYDATE datetime comment '登録日付'
         , UPDATEDATE datetime comment '更新日付'
         , constraint depositdatadetail_PKC primary key (ORDERERSDIVISION,ORDERERSCODE,TARGETDATE,REQUESTNO)
       ) comment '入金明細データ' ;
       -- 区分マスタ
       drop table if exists divisionmaster cascade;
       create table divisionmaster (
         DivisionCode decimal(5,0) unsigned not null comment '区分コード'
         , NameCode decimal(5,0) unsigned not null comment '名称コード'
         , DisplayOrder decimal(5,0) unsigned not null comment '表示順'
         , NameString varchar(100) not null comment '名称'
         , DeleteFlg decimal(1,0) not null comment '削除フラグ'
         , EntryDate datetime not null comment '登録日付'
         , UpdateDate datetime not null comment '更新日付'
         , constraint divisionmaster_PKC primary key (DivisionCode,NameCode)
       ) comment '区分マスタ' ;
       -- 積算予算書データ
       drop table if exists estimatebudget cascade;
       create table estimatebudget (
         ConstructionCode decimal(10,0) unsigned not null comment '工事コード'
         , ConstructionTime decimal(5,2) unsigned not null comment '工期'
         , IntegratedCode decimal(8,0) not null comment '積算者'
         , DataCreateDate datetime not null comment 'データ引込日'
         , RetValue1 decimal(12,0) not null comment '予定価格'
         , RetValue2 decimal(12,0) not null comment '直接工事費'
         , RetValue3 decimal(12,0) not null comment '積み上げ共通仮設費'
         , RetValue4 decimal(12,0) not null comment '積み上げ現場管理費'
         , RetPercent decimal(5,2) not null comment '一般管理費率'
         , EntryDate datetime not null comment '登録日付'
         , UpdateDate datetime not null comment '更新日付'
         , constraint estimatebudget_PKC primary key (ConstructionCode)
       ) comment '積算予算書データ' ;
       -- 積算予算内訳データ明細
       drop table if exists estimatebudgetdetail cascade;
       create table estimatebudgetdetail (
         ConstructionCode decimal(10,0) unsigned not null comment '工事コード'
         , GroupCount decimal(2,0) unsigned not null comment 'グループ番号'
         , LineCount decimal(3,0) unsigned not null comment '行番号'
         , ComponentCode decimal(5,0) unsigned not null comment '構成キー'
         , ItemCode decimal(5,0) unsigned comment '工種キー'
         , CompanyCode decimal(8,0) not null comment '協力会社コード'
         , FirstString varchar(120) comment '大項目名称'
         , SecondString varchar(120) comment '工種名称・協力会社名'
         , Content varchar(120) comment '内容'
         , EstimatePrice decimal(12,0) not null comment '見積時金額'
         , NegotiationPrice decimal(12,0) not null comment '交渉時金額'
         , Notes varchar(120) comment '注意事項'
         , EntryDate datetime not null comment '登録日付'
         , UpdateDate datetime not null comment '更新日付'
         , constraint estimatebudgetdetail_PKC primary key (ConstructionCode,GroupCount,LineCount)
       ) comment '積算予算内訳データ明細' ;
       -- 積算見積データ
       drop table if exists estimatedata cascade;
       create table estimatedata (
         ConstructionCode decimal(10,0) unsigned not null comment '工事コード'
         , LineCount decimal(3,0) unsigned not null comment '行番号'
         , ComponentCode decimal(5,0) unsigned not null comment '構成キー'
         , FixedItemCode varchar(1) comment '固定項目キー'
         , ItemName varchar(120) comment '作業名称'
         , SpecName varchar(120) comment '作業／品質・形状・寸法'
         , PriceValue decimal(14,0) not null comment '金額'
         , note varchar(60) comment '備考'
         , MyExpensesFlg decimal(1,0) not null comment '自社経費フラグ'
         , InputFlg decimal(1,0) not null comment '入力フラグ'
         , EntryDate datetime not null comment '登録日付'
         , UpdateDate datetime not null comment '更新日付'
         , constraint estimatedata_PKC primary key (ConstructionCode,LineCount)
       ) comment '積算見積データ' ;
       create index EstimateData_Index1
         on estimatedata(ConstructionCode,FixedItemCode);
       -- 積算見積ページデータ
       drop table if exists estimatedatabody cascade;
       create table estimatedatabody (
         ConstructionCode decimal(10,0) unsigned not null comment '工事コード'
         , PageCount decimal(4,0) unsigned not null comment 'ページ番号'
         , Category decimal(1,0) not null comment 'ページ区分'
         , UnionComponentCode decimal(5,0) unsigned not null comment '所属構成キー'
         , ComponentCode decimal(5,0) unsigned not null comment '構成キー'
         , ItemCode decimal(5,0) unsigned not null comment '工種キー'
         , SelectComponent decimal(5,0) unsigned not null comment '選択構成キー'
         , PageTitle varchar(120) not null comment 'ページ表示名'
         , DetailLineCount decimal(3,0) unsigned not null comment '明細行番号'
         , EntryDate datetime not null comment '登録日付'
         , UpdateDate datetime not null comment '更新日付'
         , constraint estimatedatabody_PKC primary key (ConstructionCode,PageCount)
       ) comment '積算見積ページデータ' ;
       create index EstimateDataBody_Index1
         on estimatedatabody(ConstructionCode,ComponentCode,ItemCode);
       -- 積算見積データ明細
       drop table if exists estimatedatadetail cascade;
       create table estimatedatadetail (
         ConstructionCode decimal(10,0) unsigned not null comment '工事コード'
         , PageCount decimal(4,0) unsigned not null comment 'ページ番号'
         , LineCount decimal(3,0) unsigned not null comment '行番号'
         , DataType decimal(1,0) not null comment 'データ種別'
         , ComponentCode decimal(5,0) unsigned not null comment '構成キー'
         , ItemCode decimal(5,0) unsigned not null comment '工種キー'
         , SpecCode decimal(5,0) unsigned not null comment '作業キー'
         , ItemName varchar(120) comment '作業名称'
         , SpecName varchar(120) comment '作業／品質・形状・寸法'
         , Unitcount decimal(8,2) not null comment '数量'
         , UnitName varchar(30) comment '単位名称'
         , UnitPrice decimal(9,2) not null comment '単価'
         , LineTotal decimal(12,0) not null comment '金額'
         , note varchar(60) comment '備考'
         , CompanyCode decimal(8,0) not null comment '協力会社コード'
         , MyExpensesFlg decimal(1,0) not null comment '自社経費フラグ'
         , InputFlg decimal(1,0) comment '入力フラグ'
         , EntryDate datetime not null comment '登録日付'
         , UpdateDate datetime not null comment '更新日付'
         , constraint estimatedatadetail_PKC primary key (ConstructionCode,PageCount,LineCount)
       ) comment '積算見積データ明細' ;
       create index EstimateDataDetail_Index1
         on estimatedatadetail(ConstructionCode,PageCount);
       -- 経費率マスタ
       drop table if exists expensesmaster cascade;
       create table expensesmaster (
         DivisionCode smallint(6) not null comment '工事種別コード'
         , NameCode smallint(6) not null comment '経費名称コード'
         , DisplayOrder smallint(6) not null comment '表示順'
         , ExpensesRatio decimal(4,2) not null comment '経費率'
         , DeleteFlg decimal(1,0) not null comment '削除フラグ'
         , EntryDate datetime not null comment '登録日付'
         , UpdateDate datetime not null comment '更新日付'
         , constraint expensesmaster_PKC primary key (DivisionCode,NameCode)
       ) comment '経費率マスタ' ;
       -- 会社休日マスタ
       drop table if exists holidaycalendermaster cascade;
       create table holidaycalendermaster (
         Holiday date not null comment '休日'
         , BusinessYear smallint(5) unsigned not null comment '営業年度'
         , TargetMonth tinyint(3) unsigned not null comment '該当月'
         , TargetDay tinyint(3) unsigned not null comment '該当日'
         , constraint holidaycalendermaster_PKC primary key (Holiday)
       ) comment '会社休日マスタ' ;
       create index HolidayCalender_Index1
         on holidaycalendermaster(BusinessYear);
       create index HolidayCalenderMaster_Index2
         on holidaycalendermaster(TargetMonth);
       create index HolidayCalenderMaster_Index3
         on holidaycalendermaster(TargetDay);
       -- 入力時検索履歴
       drop table if exists inputsearchlogdata cascade;
       create table inputsearchlogdata (
         ConstructionCode decimal(10,0) not null comment '工事コード'
         , UsedProcessNo decimal(4,0) not null comment '使用機能番号'
         , DisplayOrder decimal(4,0) unsigned not null comment '表示順'
         , DataType decimal(1,0) not null comment 'データ種別'
         , InputCode decimal(10,0) comment '入力コード'
         , constraint inputsearchlogdata_PKC primary key (ConstructionCode,UsedProcessNo,DisplayOrder)
       ) comment '入力時検索履歴' ;
       create index InputSearchLogData_Index1
         on inputsearchlogdata(ConstructionCode,UsedProcessNo,InputCode);
       -- 請求書データ
       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) default '0' not null 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 messageboarddata cascade;
       create table messageboarddata (
         RecordNumber decimal(6,0) unsigned not null comment 'レコード番号'
         , BranchNumber decimal(3,0) unsigned not null comment 'レコード枝番'
         , FromCode decimal(8,0) unsigned not null comment '書込み者コード'
         , FromName varchar(60) not null comment '書込み者'
         , MessageTitile varchar(100) comment '伝言タイトル'
         , MessageContent varchar(1333) not null comment '伝言内容'
         , LinkType decimal(2,0) not null comment 'リンク動作タイプ'
         , LinkMessage varchar(200) comment 'リンク文字列'
         , LinkCode varchar(30) comment 'リンクキー'
         , WritingDate datetime not null comment '書込み日時'
         , PersonCode decimal(8,0) not null comment '書込み担当者コード'
         , ShareFlag decimal(2,0) not null comment '管理者対象フラグ'
         , MessageColor varchar(8) comment '文字色'
         , BackColor varchar(8) comment 'バックカラー'
         , MessageFlag decimal(1,0) not null comment 'メッセージフラグ'
         , EntryDate datetime not null comment '登録日付'
         , UpdateDate datetime not null comment '更新日付'
         , constraint messageboarddata_PKC primary key (RecordNumber,BranchNumber)
       ) comment '掲示板メッセージ' ;
       create index MessageBoardData_Index1
         on messageboarddata(WritingDate);
       create index MessageBoardData_Index2
         on messageboarddata(MessageFlag);
       -- 掲示板対象者テーブル
       drop table if exists messageboardterget cascade;
       create table messageboardterget (
         RecordNumber decimal(6,0) unsigned not null comment 'レコード番号'
         , BranchNumber decimal(3,0) unsigned not null comment 'レコード枝番'
         , SeqNum decimal(3,0) unsigned not null comment '順番'
         , ToCode decimal(8,0) not null comment '対象者コード'
         , ToName varchar(60) not null comment '対象者'
         , EntryDate datetime not null comment '登録日付'
         , UpdateDate datetime not null comment '更新日付'
         , constraint messageboardterget_PKC primary key (RecordNumber,BranchNumber,SeqNum)
       ) comment '掲示板対象者テーブル' ;
       create index MessageBoardTerget_Index1
         on messageboardterget(RecordNumber,BranchNumber);
       -- 掲示板メッセージ閲覧履歴
       drop table if exists messagebrowsinghistory cascade;
       create table messagebrowsinghistory (
         RecordNumber decimal(6,0) unsigned not null comment 'レコード番号'
         , BranchNumber decimal(3,0) unsigned not null comment 'レコード枝番'
         , SeqNum decimal(3,0) unsigned not null comment '順番'
         , BrowsingCode decimal(8,0) not null comment '閲覧者コード'
         , BrowsingDate datetime not null comment '閲覧日付'
         , EntryDate datetime not null comment '登録日付'
         , UpdateDate datetime not null comment '更新日付'
         , constraint messagebrowsinghistory_PKC primary key (RecordNumber,BranchNumber,SeqNum)
       ) comment '掲示板メッセージ閲覧履歴' ;
       -- 月マスタ
       drop table if exists monthmaster cascade;
       create table monthmaster (
         month smallint(6) not null comment '月'
       ) comment '月マスタ' ;
       create index month_Index1
         on monthmaster(month);
       -- 発注者マスタ
       drop table if exists orderersmaster cascade;
       create table orderersmaster (
         OrderCotegory decimal(5,0) unsigned not null comment '発注者区分'
         , OrderersCode decimal(5,0) unsigned not null comment '発注者コード'
         , DisplayOrder decimal(5,0) unsigned not null comment '表示順'
         , OrderFlg decimal(1,0) not null comment '法人団体・個人フラグ'
         , CorporateStatusName varchar(100) comment '法人格名称'
         , CorporateStatusPoint decimal(1,0) comment '法人格名称位置'
         , OrderersName1 varchar(60) not null comment '発注者名1'
         , OrderersName2 varchar(60) comment '発注者名２'
         , DepartmentName varchar(60) comment '部署名'
         , ChargePersonName varchar(60) comment '担当者名'
         , ZipCode varchar(8) comment '郵便番号'
         , Address1 varchar(60) comment '住所１'
         , Address2 varchar(60) comment '住所2'
         , Address3 varchar(60) comment '住所3'
         , PhoneNumber varchar(13) comment '電話番号'
         , FaxNumber varchar(13) comment 'FAX番号'
         , MailAddress varchar(257) comment 'メールアドレス'
         , Note varchar(300) comment '備考'
         , DeleteFlg decimal(1,0) not null comment '削除フラグ'
         , EntryDate datetime not null comment '登録日付'
         , UpdateDate datetime not null comment '更新日付'
         , constraint orderersmaster_PKC primary key (OrderCotegory,OrderersCode)
       ) comment '発注者マスタ' ;
       -- 受注金額データ
       drop table if exists orderspricedata cascade;
       create table orderspricedata (
         ConstructionCode decimal(10,0) unsigned not null comment '工事コード'
         , ChangeNo decimal(3,0) unsigned not null comment '変更番号'
         , ChangeDate date not null comment '変更日'
         , ChangePersonCode decimal(8,0) unsigned not null comment '変更者コード'
         , ChangePersonName varchar(60) comment '変更者名'
         , BeforeValue decimal(14,0) not null comment '変更前税抜受注金額'
         , BeforeValueInTax decimal(14,0) not null comment '変更前税込受注金額'
         , AfterValue decimal(14,0) not null comment '変更後税抜受注金額'
         , AfterValueInTax decimal(14,0) not null comment '変更後税込受注金額'
         , ChangeComment varchar(200) comment '変更理由'
         , EntryDate datetime not null comment '登録日付'
         , UpdateDate datetime not null comment '更新日付'
         , constraint orderspricedata_PKC primary key (ConstructionCode,ChangeNo)
       ) 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 personapproval cascade;
       create table personapproval (
         PersonCode decimal(8,0) unsigned not null comment '担当者コード'
         , ApprovalCode decimal(3,0) unsigned not null comment '承認機能番号'
         , OrderDate date not null comment '受付日'
         , OrderNo decimal(2,0) unsigned not null comment '受付番号'
         , SeqNo decimal(2,0) not null comment '枝番'
         , PersonCodeApproval decimal(8,0) unsigned not null comment '申請・承認者コード'
         , PetitionApprovalDate datetime not null comment '申請・承認日付'
         , ApprovalLimitDates datetime not null comment '承認希望日'
         , ApprovalStatus decimal(1,0) not null comment '承認状態'
         , InputComment varchar(300) comment 'コメント'
         , EntryDate datetime not null comment '登録日付'
         , UpdateDate datetime not null comment '更新日付'
         , constraint personapproval_PKC primary key (PersonCode,ApprovalCode,OrderDate,OrderNo,SeqNo)
       ) comment '担当者承認データ' ;
       -- 担当者承認コメントデータ
       drop table if exists personapprovalcomment cascade;
       create table personapprovalcomment (
         PersonCode decimal(8,0) unsigned not null comment '担当者コード'
         , ApprovalCode decimal(3,0) unsigned not null comment '承認機能番号'
         , OrderDate date not null comment '受付日'
         , OrderNo decimal(2,0) unsigned not null comment '受付番号'
         , CommentNo decimal(2,0) not null comment 'コメント番号'
         , ParentNo decimal(2,0) not null comment '親コメント番号'
         , ApproValComment varchar(300) comment '指示・連絡コメント'
         , EntryDate datetime not null comment '登録日付'
         , UpdateDate datetime not null comment '更新日付'
         , constraint personapprovalcomment_PKC primary key (PersonCode,ApprovalCode,OrderDate,OrderNo,CommentNo)
       ) comment '担当者承認コメントデータ' ;
       -- 担当者対象部署マスタ
       drop table if exists persondepartmentmaster cascade;
       create table persondepartmentmaster (
         PersonCode decimal(8,0) unsigned not null comment '担当者コード'
         , DepartmentCode decimal(5,0) unsigned not null comment '部署コード'
         , AffiliationFlg decimal(1,0) not null comment '所属フラグ'
         , EntryDate datetime not null comment '登録日付'
         , UpdateDate datetime not null comment '更新日付'
         , constraint persondepartmentmaster_PKC primary key (PersonCode,DepartmentCode)
       ) comment '担当者対象部署マスタ' ;
       create index PersonDepartmentMaster_Index1
         on persondepartmentmaster(PersonCode);
       create index PersonDepartmentMaster_Index2
         on persondepartmentmaster(DepartmentCode);
       -- 担当者マスタ
       drop table if exists personinchargemaster cascade;
       create table personinchargemaster (
         PersonCode decimal(8,0) unsigned not null comment '担当者コード'
         , DisplayOrder decimal(8,0) unsigned not null comment '表示順'
         , PersonName varchar(60) not null comment '担当者名'
         , StartDate date not null comment '使用開始年月日'
         , EndDate date comment '使用終了年月日'
         , PassWord varchar(16) comment 'パスワード'
         , SecurityManagement decimal(1,0) not null comment '機密区分'
         , SecCode decimal(2,0) unsigned not null comment '保護区分番号'
         , MsgBackColor varchar(8) comment 'メッセージ背景色'
         , DisplayString varchar(20) comment '表示・印刷名称'
         , DepartmentCode decimal(5,0) unsigned comment '部署コード'
         , MonthlySalary decimal(11,0) not null comment '月給金額'
         , YearSalary decimal(12,0) not null comment '年俸金額'
         , Qualification varchar(120) comment '資格入力欄'
         , SealPrintName varchar(5) comment '印鑑表示名'
         , EmployeeClassFlg decimal(1,0) not null comment '社員区分'
         , LedgerFlg decimal(1,0) not null comment '台帳計算対象フラグ'
         , CommutingDistance decimal(5,2) not null comment '通勤距離'
         , CommuteCosts decimal(5,0) not null comment '通勤交通費'
         , DeleteFlg decimal(1,0) not null comment '削除フラグ'
         , EntryDate datetime not null comment '登録日付'
         , UpdateDate datetime not null comment '更新日付'
         , constraint personinchargemaster_PKC primary key (PersonCode)
       ) comment '担当者マスタ' ;
       create index PersonInChargeMaster_Index1
         on personinchargemaster(StartDate);
       create index PersonInChargeMaster_Index2
         on personinchargemaster(DepartmentCode);
       -- 担当者給与マスタ
       drop table if exists personsalarymaster cascade;
       create table personsalarymaster (
         PersonCode decimal(8,0) unsigned not null comment '担当者コード'
         , StartDate date not null comment '使用開始年月日'
         , MonthlySalary decimal(11,0) not null comment '月給金額'
         , YearSalary decimal(12,0) not null comment '年俸金額'
         , EntryDate datetime not null comment '登録日付'
         , UpdateDate datetime not null comment '更新日付'
         , constraint personsalarymaster_PKC primary key (PersonCode,StartDate)
       ) comment '担当者給与マスタ' ;
       create index PersonSalaryMaster_Index1
         on personsalarymaster(PersonCode);
       create index PersonSalaryMaster_Index2
         on personsalarymaster(StartDate);
       -- 指示事項データ
       drop table if exists pointingoutcomment cascade;
       create table pointingoutcomment (
         ConstructionCode decimal(10,0) not null comment '工事コード'
         , ProcessNo smallint(6) not null comment '処理番号（画面番号）'
         , SeqNo smallint(6) not null comment '順序'
         , PageCount smallint(6) not null comment 'ページ番号'
         , PersonCode decimal(8,0) not null comment '記入者コード'
         , DrowColor varchar(8) comment '描画色'
         , CommentMessage varchar(1000) comment 'メッセージ'
         , StartPointX smallint(6) not null comment '指摘項目位置Ｘ'
         , StartPointY smallint(6) not null comment '指摘項目位置Ｙ'
         , CurrentPointX smallint(6) not null comment '初期位置Ｘ'
         , CurrentPointY smallint(6) not null comment '初期位置Ｙ'
         , CurrentWidth smallint(6) not null comment '表示サイズWidth'
         , CurrentHeight smallint(6) not null comment '表示サイズHeigth'
         , EntryDate datetime not null comment '登録日付'
         , UpdateDate datetime not null comment '更新日付'
         , constraint pointingoutcomment_PKC primary key (ConstructionCode,ProcessNo,SeqNo)
       ) comment '指示事項データ' ;
       -- 発注者打ち合わせ議事録データ
       drop table if exists proceedingsdata cascade;
       create table proceedingsdata (
         ConstructionCode decimal(10,0) unsigned not null comment '工事コード'
         , ProceedingsDataDate date not null comment '議事録作成日'
         , StartMeetingTime decimal(4,0) unsigned not null comment '会議時間開始'
         , EndMeetingTime decimal(4,0) unsigned not null comment '会議時間終了'
         , CreatorCode decimal(8,0) not null comment '作成者コード'
         , PalceFlag decimal(1,0) not null comment '現地フラグ'
         , MeetingPalce varchar(120) comment '打合せ場所'
         , EntryDate datetime not null comment '登録日付'
         , UpdateDate datetime not null comment '更新日付'
         , constraint proceedingsdata_PKC primary key (ConstructionCode,ProceedingsDataDate,StartMeetingTime)
       ) comment '発注者打ち合わせ議事録データ' ;
       -- 発注者打ち合わせ議事録データ（出席者）
       drop table if exists proceedingsdataattend cascade;
       create table proceedingsdataattend (
         ConstructionCode decimal(10,0) unsigned not null comment '工事コード'
         , ProceedingsDataDate date not null comment '議事録作成日'
         , StartMeetingTime decimal(4,0) unsigned not null comment '会議時間開始'
         , SeqNo decimal(3,0) unsigned not null comment '明細行番号'
         , Department varchar(20) comment '出席者所属'
         , AttendName varchar(10) comment '出席者氏名'
         , EntryDate datetime not null comment '登録日付'
         , UpdateDate datetime not null comment '更新日付'
         , constraint proceedingsdataattend_PKC primary key (ConstructionCode,ProceedingsDataDate,StartMeetingTime,SeqNo)
       ) comment '発注者打ち合わせ議事録データ（出席者）' ;
       -- 発注者打ち合わせ議事録データ（出席者）
       drop table if exists proceedingsdataattendee cascade;
       create table proceedingsdataattendee (
         PersonCode decimal(8,0) not null comment '作成者コード'
         , ProceedingsDataDate date not null comment '議事録作成日'
         , ConstructionCode int(11) not null comment '工事番号'
         , SeqNo smallint(6) not null comment 'データ行番号'
         , Department varchar(100) comment '出席者所属'
         , AttendeeName varchar(100) comment '出席者氏名'
         , EntryDate datetime not null comment '登録年月日'
         , UpdateDate datetime not null comment '更新年月日'
         , constraint proceedingsdataattendee_PKC primary key (PersonCode,ProceedingsDataDate,ConstructionCode,SeqNo)
       ) comment '発注者打ち合わせ議事録データ（出席者）' ;
       -- 発注者打ち合わせ議事録データ（明細）
       drop table if exists proceedingsdatadetail cascade;
       create table proceedingsdatadetail (
         ConstructionCode decimal(10,0) unsigned not null comment '工事コード'
         , ProceedingsDataDate date not null comment '議事録作成日'
         , StartMeetingTime decimal(4,0) unsigned not null comment '会議時間開始'
         , SeqNo decimal(3,0) unsigned not null comment '明細行番号'
         , TitleNo varchar(5) comment '議題番号'
         , Title varchar(20) comment '議題'
         , ContentsText varchar(50) comment '内容'
         , EntryDate datetime not null comment '登録日付'
         , UpdateDate datetime not null comment '更新日付'
         , constraint proceedingsdatadetail_PKC primary key (ConstructionCode,ProceedingsDataDate,StartMeetingTime,SeqNo)
       ) comment '発注者打ち合わせ議事録データ（明細）' ;
       -- 承認データ
       drop table if exists processapproval cascade;
       create table processapproval (
         ConstructionCode decimal(10,0) unsigned not null comment '工事コード'
         , ApprovalCode decimal(3,0) unsigned not null comment '承認機能番号'
         , OrderNo decimal(2,0) unsigned not null comment '受付番号'
         , SeqNo decimal(2,0) not null comment '枝番'
         , PersonCode decimal(8,0) unsigned not null comment '申請・承認者コード'
         , PetitionApprovalDate datetime not null comment '申請・承認日付'
         , ApprovalLimitDates datetime not null comment '承認希望日'
         , ApprovalStatus decimal(1,0) not null comment '承認状態'
         , InputComment varchar(300) comment 'コメント'
         , EntryDate datetime not null comment '登録日付'
         , UpdateDate datetime not null comment '更新日付'
         , constraint processapproval_PKC primary key (ConstructionCode,ApprovalCode,OrderNo,SeqNo)
       ) comment '承認データ' ;
       -- 承認コメントデータ
       drop table if exists processapprovalcomment cascade;
       create table processapprovalcomment (
         ConstructionCode decimal(10,0) unsigned not null comment '工事コード'
         , ApprovalCode decimal(3,0) unsigned not null comment '承認機能番号'
         , OrderNo decimal(2,0) unsigned not null comment '受付番号'
         , CommentNo decimal(2,0) not null comment 'コメント番号'
         , ParentNo decimal(2,0) not null comment '親コメント番号'
         , ApproValComment varchar(300) comment '指示・連絡コメント'
         , EntryDate datetime not null comment '登録日付'
         , UpdateDate datetime not null comment '更新日付'
         , constraint processapprovalcomment_PKC primary key (ConstructionCode,ApprovalCode,OrderNo,CommentNo)
       ) comment '承認コメントデータ' ;
       -- 承認明細データ
       drop table if exists processapprovaldetail cascade;
       create table processapprovaldetail (
         ConstructionCode decimal(10,0) unsigned not null comment '工事コード'
         , ApprovalCode decimal(3,0) unsigned not null comment '承認機能番号'
         , OrderNo decimal(2,0) unsigned not null comment '受付番号'
         , SerialNo decimal(2,0) unsigned not null comment '枝番'
         , LinkCode decimal(10,0) not null comment 'リンク情報'
         , EntryDate datetime not null comment '登録日付'
         , UpdateDate datetime not null comment '更新日付'
         , constraint processapprovaldetail_PKC primary key (ConstructionCode,ApprovalCode,OrderNo,SerialNo)
       ) comment '承認明細データ' ;
       -- 起動対象マスタ
       drop table if exists processexcutemaster cascade;
       create table processexcutemaster (
         SecCode decimal(2,0) unsigned not null comment '保護区分番号'
         , ExecCode decimal(4,0) unsigned not null comment '起動処理番号'
         , EditFlg decimal(1,0) not null comment '編集フラグ'
         , EntryDate datetime not null comment '登録日付'
         , UpdateDate datetime not null comment '更新日付'
         , constraint processexcutemaster_PKC primary key (SecCode,ExecCode)
       ) comment '起動対象マスタ' ;
       create index ProcessExcuteMaster_Index1
         on processexcutemaster(SecCode);
       create index ProcessExcuteMaster_Index2
         on processexcutemaster(ExecCode);
       -- 注文書データ
       drop table if exists purchaseorder cascade;
       create table purchaseorder (
         ConstructionCode decimal(10,0) unsigned not null comment '工事コード'
         , SeqNo decimal(3,0) unsigned not null comment '注文書枝番'
         , CompanyCode decimal(8,0) not null comment '協力会社コード'
         , CreateDate date comment '作成日付'
         , OrderPrice decimal(12,0) not null comment '発注金額'
         , LastRowStringDate date not null comment '最終行表示日付'
         , OrderSheetType decimal(1,0) not null comment '簡易・本式フラグ'
         , PaymentTerms decimal(1,0) not null comment '支払い条件'
         , CashRatio decimal(4,1) not null comment '現金割合（％）'
         , BillPeriod decimal(4,0) not null comment '手形期間'
         , PrePayments decimal(1,0) not null comment '前金払'
         , PrePaymentsDays decimal(3,0) not null comment '前金払日数'
         , PrePaymentsPrice decimal(12,0) not null comment '前金払金額'
         , PartialPayment decimal(1,0) not null comment '部分払'
         , PartialPaymentMounth decimal(2,0) unsigned not null comment '部分払締切月'
         , PartialPaymentDay1 decimal(2,0) unsigned not null comment '部分払締切日'
         , PartialPaymentDay2 decimal(2,0) unsigned not null comment '部分払支払日'
         , PartialPaymentPrice decimal(12,0) not null comment '部分払金額'
         , CompletionPayment decimal(1,0) not null comment '引渡し時の支払'
         , CompletionPaymentDays decimal(3,0) unsigned not null comment '引渡し時の支払日数'
         , CompletionPaymentPrice decimal(12,0) not null comment '引渡し時の支払金額'
         , LeadEngineerCode decimal(8,0) comment '主任技術者コード'
         , LeadEngineerName varchar(60) comment 'LeadEngineerName'
         , SafetyOfficerCode decimal(8,0) comment '安全管理者コード'
         , SafetyOfficerName varchar(60) comment '安全管理者名'
         , WorkersCount decimal(3,0) unsigned comment '作業員数'
         , PrintoutDate date not null comment '印刷年月日'
         , EntryDate datetime not null comment '登録日付'
         , UpdateDate datetime not null comment '更新日付'
         , constraint purchaseorder_PKC primary key (ConstructionCode,SeqNo)
       ) comment '注文書データ' ;
       -- 注文書日付データ
       drop table if exists purchaseorderdate cascade;
       create table purchaseorderdate (
         ConstructionCode decimal(10,0) unsigned not null comment '工事コード'
         , SeqNo decimal(3,0) unsigned not null comment '注文書枝番'
         , CompanyCode decimal(8,0) not null comment '協力会社コード'
         , SendDate date not null comment '注文書郵送日'
         , ReturnDate date not null comment '注文書返送日'
         , ReturnCheckDate date not null comment '注文書返送確認日'
         , EntryDate datetime not null comment '登録日付'
         , UpdateDate datetime not null comment '更新日付'
         , constraint purchaseorderdate_PKC primary key (ConstructionCode,SeqNo)
       ) comment '注文書日付データ' ;
       -- 注文書明細データ
       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 '注文書作成時台帳行番号'
         , 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);
       -- 請求データ
       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) ;
       -- 請求書日付データ
       drop table if exists requestorderdate cascade;
       create table requestorderdate (
         ConstructionCode decimal(10,0) unsigned not null comment '工事コード'
         , InvoiceNo decimal(9,0) not null comment '請求書No'
         , SendDate date not null comment '請求書発給郵送日'
         , EntryDate datetime not null comment '登録日付'
         , UpdateDate datetime not null comment '更新日付'
         , constraint requestorderdate_PKC primary key (ConstructionCode,InvoiceNo)
       ) comment '請求書日付データ' ;
       -- 売上グラフデータ
       drop table if exists salesgraphdata cascade;
       create table salesgraphdata (
         GraphDataCode decimal(8,0) not null comment 'グラフデータコード'
         , ConstructionPeriod decimal(4,0) unsigned not null comment '事業期'
         , SalesDataDays date not null comment '売上年月'
         , DisplayOrder decimal(8,0) unsigned not null comment '表示順'
         , DataName varchar(100) not null comment 'データ表示名称'
         , SalesAmount decimal(10,0) not null comment '売上データ'
         , AdministrativeExpense decimal(10,0) not null comment '販管・部署経費'
         , GrossProfit decimal(10,0) not null comment '粗利益'
         , NetIncome decimal(10,0) not null comment '純利益(損益)'
         , EntryDate datetime not null comment '登録日付'
         , UpdateDate datetime not null comment '更新日付'
         , constraint salesgraphdata_PKC primary key (GraphDataCode,ConstructionPeriod,SalesDataDays)
       ) comment '売上グラフデータ' ;
       create index SalesGraphData_Idx1
         on salesgraphdata(DisplayOrder);
       create index SalesGraphData_idx2
         on salesgraphdata(ConstructionPeriod);
       -- 保護区分マスタ
       drop table if exists securitymaster cascade;
       create table securitymaster (
         SecCode decimal(2,0) unsigned not null comment '保護区分番号'
         , DisplayOrder decimal(2,0) not null comment '表示順'
         , SecName varchar(60) not null comment '保護区分名称'
         , SecRank decimal(1,0) not null comment '保護区分ランク'
         , SecRange decimal(1,0) not null comment '保護区分範囲'
         , GeneralAffairs decimal(1,0) not null comment '総務業務フラグ'
         , SelectBackColor varchar(8) not null comment '選択背景色'
         , EntryDate datetime not null comment '登録日付'
         , UpdateDate datetime not null comment '更新日付'
         , constraint securitymaster_PKC primary key (SecCode)
       ) comment '保護区分マスタ' ;
       create index SecurityMaster_Index1
         on securitymaster(DisplayOrder);
       create index SecurityMaster_Index2
         on securitymaster(SecRank);
       create index SecurityMaster_Index3
         on securitymaster(SecRange);
       -- 安全パトロールデータ
       drop table if exists sfpdata cascade;
       create table sfpdata (
         ConstructionCode decimal(10,0) unsigned not null comment '工事コード'
         , DataCount decimal(2,0) unsigned not null comment 'パトロール回数'
         , RequestDate datetime not null comment 'パトロール要請日時'
         , PersonCode decimal(8,0) unsigned not null comment '申請者コード'
         , StringValue varchar(300) comment 'コメント'
         , OrderNo decimal(2,0) unsigned not null comment '申請受付番号'
         , EntryDate datetime not null comment '登録日付'
         , UpdateDate datetime not null comment '更新日付'
         , constraint sfpdata_PKC primary key (ConstructionCode,DataCount)
       ) comment '安全パトロールデータ' ;
       create index SFPData_Index1
         on sfpdata(RequestDate);
       -- 安全パトロール明細データ
       drop table if exists sfpdatadetail cascade;
       create table sfpdatadetail (
         ConstructionCode decimal(10,0) unsigned not null comment '工事コード'
         , DataCount decimal(2,0) unsigned not null comment 'パトロール回数'
         , SeqNo decimal(2,0) unsigned not null comment 'データ枝番'
         , CompanyCode decimal(8,0) unsigned not null comment '協力会社コード'
         , EntryDate datetime not null comment '登録日付'
         , UpdateDate datetime not null comment '更新日付'
         , constraint sfpdatadetail_PKC primary key (ConstructionCode,DataCount,SeqNo)
       ) comment '安全パトロール明細データ' ;
       -- 協力業者施工工種登録申請データ
       drop table if exists subconstrjobitemregist cascade;
       create table subconstrjobitemregist (
         PersonCode decimal(8,0) unsigned not null comment '申請者番号'
         , CreateDate date not null comment '作成日'
         , SeqNo decimal(2,0) unsigned not null comment '申請連番'
         , DisplayOrder decimal(8,0) unsigned not null comment '表示順'
         , ItemCode decimal(5,0) unsigned not null comment '工種キー'
         , EntryDate datetime not null comment '登録日付'
         , UpdateDate datetime not null comment '更新日付'
         , constraint subconstrjobitemregist_PKC primary key (PersonCode,CreateDate,SeqNo,DisplayOrder)
       ) comment '協力業者施工工種登録申請データ' ;
       -- 協力業者職種リンク登録申請データ
       drop table if exists subconstrjoblinkregist cascade;
       create table subconstrjoblinkregist (
         PersonCode decimal(8,0) unsigned not null comment '申請者番号'
         , CreateDate date not null comment '作成日'
         , SeqNo decimal(2,0) unsigned not null comment '申請連番'
         , DisplayOrder decimal(8,0) unsigned not null comment '表示順'
         , JobCategoryCode decimal(5,0) not null comment '職種キー'
         , EntryDate datetime not null comment '登録日付'
         , UpdateDate datetime not null comment '更新日付'
         , constraint subconstrjoblinkregist_PKC primary key (PersonCode,CreateDate,SeqNo,DisplayOrder)
       ) comment '協力業者職種リンク登録申請データ' ;
       -- 協力業者登録申請データ
       drop table if exists subconstrregist cascade;
       create table subconstrregist (
         PersonCode decimal(8,0) unsigned not null comment '申請者番号'
         , CreateDate date not null comment '作成日'
         , SeqNo decimal(2,0) unsigned not null comment '申請連番'
         , DataMode decimal(1,0) comment 'データ区分'
         , SourceCode decimal(8,0) not null comment '協力会社コード'
         , PetitionPeriod decimal(4,0) unsigned not null comment '申請年度'
         , CorporateStatusName varchar(40) comment '法人格名称'
         , CorporateStatusPoint decimal(1,0) not null comment '法人格名称位置'
         , CompanyName varchar(100) not null comment '協力会社名'
         , CEOPositionName varchar(60) comment '代表者役職名'
         , CEOName varchar(60) comment '代表者氏名'
         , ContactPersonName varchar(60) comment '担当者氏名'
         , ZipCode varchar(8) comment '郵便番号'
         , Address1 varchar(60) comment '住所１'
         , Address2 varchar(60) comment '住所2'
         , Address3 varchar(60) comment '住所3'
         , CellPhoneNumber varchar(13) comment '携帯電話番号'
         , MailAddress varchar(257) comment 'メールアドレス'
         , PhoneNumber varchar(13) comment '電話番号'
         , FaxNumber varchar(13) comment 'FAX番号'
         , JapaneseSyllabary varchar(2) comment '50音読み'
         , StartDate date not null comment '取引開始年月日'
         , Note varchar(300) comment '備考'
         , labourKind decimal(1,0) not null comment '支払い通常・手間区分'
         , OrderDate date comment '申請日'
         , OrderNo decimal(2,0) unsigned not null comment '申請受付番号'
         , EntryDate datetime not null comment '登録日付'
         , UpdateDate datetime not null comment '更新日付'
         , constraint subconstrregist_PKC primary key (PersonCode,CreateDate,SeqNo)
       ) comment '協力業者登録申請データ' ;
       -- 協力会社工種マスタ
       drop table if exists subcontractoritemmaster cascade;
       create table subcontractoritemmaster (
         ItemCode decimal(5,0) unsigned not null comment '工種キー'
         , DisplayOrder decimal(5,0) unsigned not null comment '表示順'
         , ItemName varchar(120) not null comment '工種名称'
         , DeleteFlg decimal(1,0) not null comment '削除フラグ'
         , EntryDate datetime not null comment '登録日付'
         , UpdateDate datetime not null comment '更新日付'
         , constraint subcontractoritemmaster_PKC primary key (ItemCode)
       ) comment '協力会社工種マスタ' ;
       -- 協力会社職種マスタ
       drop table if exists subcontractorjobcategory cascade;
       create table subcontractorjobcategory (
         JobCategoryCode decimal(5,0) not null comment '職種キー'
         , DisplayOrder decimal(5,0) unsigned not null comment '表示順'
         , JobCategoryName varchar(120) not null comment '職種名称'
         , DeleteFlg decimal(1,0) not null comment '削除フラグ'
         , EntryDate datetime not null comment '登録日付'
         , UpdateDate datetime not null comment '更新日付'
         , constraint subcontractorjobcategory_PKC primary key (JobCategoryCode)
       ) comment '協力会社職種マスタ' ;
       -- 協力会社施工工種データ
       drop table if exists subcontractorjobitem cascade;
       create table subcontractorjobitem (
         CompanyCode decimal(8,0) not null comment '協力会社コード'
         , ItemCode decimal(5,0) unsigned not null comment '工種キー'
         , DisplayOrder decimal(5,0) unsigned not null comment '表示順'
         , EntryDate datetime not null comment '登録日付'
         , UpdateDate datetime not null comment '更新日付'
         , constraint subcontractorjobitem_PKC primary key (CompanyCode,ItemCode)
       ) comment '協力会社施工工種データ' ;
       create index SubContractorJobItem_Index1
         on subcontractorjobitem(CompanyCode);
       create index SubContractorJobItem_Index2
         on subcontractorjobitem(ItemCode);
       -- 協力会社職種リンクマスタ
       drop table if exists subcontractorjoblink cascade;
       create table subcontractorjoblink (
         CompanyCode decimal(8,0) not null comment '協力会社コード'
         , JobCategoryCode decimal(5,0) not null comment '職種キー'
         , DisplayOrder decimal(5,0) unsigned not null comment '表示順'
         , EntryDate datetime not null comment '登録日付'
         , UpdateDate datetime not null comment '更新日付'
         , constraint subcontractorjoblink_PKC primary key (CompanyCode,JobCategoryCode)
       ) comment '協力会社職種リンクマスタ' ;
       create index SubContractorJobLink_Index1
         on subcontractorjoblink(CompanyCode);
       create index SubContractorJobLink_Index2
         on subcontractorjoblink(JobCategoryCode);
       -- 協力会社マスタ
       drop table if exists subcontractormaster cascade;
       create table subcontractormaster (
         CompanyCode decimal(8,0) not null comment '協力会社コード'
         , DisplayOrder decimal(8,0) unsigned not null comment '表示順'
         , CorporateStatusName varchar(40) comment '法人格名称'
         , CorporateStatusPoint decimal(1,0) not null comment '法人格名称位置'
         , CompanyName varchar(100) not null comment '協力会社名'
         , CEOPositionName varchar(60) comment '代表者役職名'
         , CEOName varchar(60) comment '代表者氏名'
         , ContactPersonName varchar(60) comment '担当者氏名'
         , ZipCode varchar(8) comment '郵便番号'
         , Address1 varchar(60) comment '住所１'
         , Address2 varchar(60) comment '住所2'
         , Address3 varchar(60) comment '住所3'
         , CellPhoneNumber varchar(13) comment '携帯電話番号'
         , MailAddress varchar(257) comment 'メールアドレス'
         , PhoneNumber varchar(13) comment '電話番号'
         , FaxNumber varchar(13) comment 'FAX番号'
         , JapaneseSyllabary varchar(2) comment '50音読み'
         , StartDate date not null comment '取引開始年月日'
         , Note varchar(300) comment '備考'
         , labourKind decimal(1,0) not null comment '支払い通常・手間区分'
         , DeleteFlg decimal(1,0) default '0' not null comment '削除フラグ'
         , EntryDate datetime not null comment '登録日付'
         , UpdateDate datetime not null comment '更新日付'
         , constraint subcontractormaster_PKC primary key (CompanyCode)
       ) comment '協力会社マスタ' ;
       create index SubContractorMaster_Index1
         on subcontractormaster(JapaneseSyllabary);
       -- 仕入先マスタ
       drop table if exists suppliersmaster cascade;
       create table suppliersmaster (
         SuppliersCode decimal(5,0) unsigned not null comment '仕入先コード'
         , DisplayOrder decimal(5,0) unsigned not null comment '表示順'
         , SuppliersName1 varchar(60) not null comment '仕入先名1'
         , SuppliersName2 varchar(60) comment '仕入先名２'
         , ZipCode varchar(8) comment '郵便番号'
         , Address1 varchar(60) comment '住所１'
         , Address2 varchar(60) comment '住所2'
         , Address3 varchar(60) comment '住所3'
         , PhoneNumber varchar(13) comment '電話番号'
         , FaxNumber varchar(13) comment 'FAX番号'
         , Note varchar(300) comment '備考'
         , DeleteFlg decimal(1,0) not null comment '削除フラグ'
         , EntryDate datetime not null comment '登録日付'
         , UpdateDate datetime not null comment '更新日付'
         , constraint suppliersmaster_PKC primary key (SuppliersCode)
       ) comment '仕入先マスタ' ;
       -- システム起動確認データ
       drop table if exists systemexecute cascade;
       create table systemexecute (
         PrimaryCode tinyint(4) not null comment 'キー項目'
         , TargetDate date not null comment '起動日付'
         , SystemVersion int(11) not null comment '本体・環境バージョン'
         , UpDateCopyVersion int(11) not null comment 'コピーバージョン'
         , EntryDate datetime not null comment '登録日付'
         , UpdateDate datetime not null comment '更新日付'
         , constraint systemexecute_PKC primary key (PrimaryCode)
       ) comment 'システム起動確認データ' ;
       -- 管理マスタ
       drop table if exists systemmaster cascade;
       create table systemmaster (
         SystemCode decimal(3,0) unsigned not null comment '会社コード'
         , CompanyName1 varchar(60) not null comment '会社名１'
         , CompanyName2 varchar(60) comment '会社名２'
         , CEOName varchar(60) not null comment '代表者氏名'
         , CEOPositionName varchar(60) comment '代表者役職名'
         , ZipCode varchar(8) not null comment '郵便番号'
         , Address1 varchar(60) not null comment '住所１'
         , Address2 varchar(60) comment '住所2'
         , Address3 varchar(60) comment '住所3'
         , PhoneNumber varchar(13) not null comment '電話番号'
         , FaxNumber varchar(13) comment 'FAX番号'
         , HomePageURL varchar(120) comment 'ホームページURL'
         , ConstructionExpenses decimal(4,2) not null comment '工事部経費'
         , OfficeExpenses decimal(4,2) not null comment '事務所経費'
         , OperatingExpenses decimal(4,2) not null comment '営業経費'
         , ConsumptionTax decimal(4,2) not null comment '消費税'
         , CooperationRate decimal(4,2) not null comment '協力金比率'
         , StatutoryWelfareRate decimal(4,2) not null comment '法定福利費比率'
         , FuelPrice decimal(5,2) not null comment '燃料キロ単価'
         , AreaDistance decimal(5,2) not null comment '資材置場移動距離'
         , ExcelSavePath varchar(120) comment 'Excel保存パス'
         , BusinessPeriod smallint(6) not null comment '現在営業期数'
         , ConstructionYear smallint(6) not null comment '現在工事年度'
         , BusinessBeginningDate varchar(5) not null comment '営業期期首日'
         , ConstructionBeginningDate varchar(5) not null comment '工事年度期首日'
         , ConstructionNoBase decimal(1,0) not null comment '工事番号基準値'
         , SloganString1 varchar(100) comment '標語1'
         , SloganString2 varchar(100) comment '標語2'
         , SloganString3 varchar(100) comment '標語3'
         , EntryDate datetime not null comment '登録日付'
         , UpdateDate datetime not null comment '更新日付'
         , constraint systemmaster_PKC primary key (SystemCode)
       ) comment '管理マスタ' ;
       -- 期限マスタ
       drop table if exists termmaster cascade;
       create table termmaster (
         ConstructionStatusFlg decimal(2,0) not null comment '工事状態フラグ'
         , FieldNo decimal(2,0) not null comment 'チェック対象フィールド番号'
         , SeqNo decimal(2,0) not null comment '枝番'
         , DisplayOrder decimal(2,0) not null comment '表示順'
         , CheckSchdule decimal(1,0) not null comment 'チェックタイミング'
         , TermDays decimal(3,0) not null comment '経過日'
         , SendTitile varchar(100) comment 'タイトル'
         , SendMessage varchar(100) comment 'メッセージ'
         , BackColor varchar(8) comment '背景色'
         , ForeColor varchar(8) comment '文字色'
         , EntryDate datetime not null comment '登録日付'
         , UpdateDate datetime not null comment '更新日付'
         , constraint termmaster_PKC primary key (ConstructionStatusFlg,FieldNo,SeqNo)
       ) comment '期限マスタ' ;
       create index TermMaster_Index1
         on termmaster(DisplayOrder);
       -- 出勤交通費データ
       drop table if exists tranceportdailydata cascade;
       create table tranceportdailydata (
         PersonCode decimal(8,0) unsigned not null comment '作成者コード'
         , AttendanceDate date not null comment '日報作成日'
         , StartDistance decimal(7,1) not null comment '当日出発キロ数'
         , ComplateDistance decimal(7,1) not null comment '当日終了キロ数'
         , TotalDistance decimal(6,1) not null comment '当日合計キロ数'
         , OfficeGoFlg decimal(1,0) not null comment '事務所出勤フラグ'
         , OfficeOutFlg decimal(1,0) not null comment '事務所帰宅フラグ'
         , TrancePayGoFlg decimal(1,0) not null comment '有料通勤出勤フラグ'
         , TrancePayOutFlg decimal(1,0) not null comment '有料通勤帰宅フラグ'
         , WareHouseFlg decimal(1,0) not null comment '資材置場フラグ'
         , EntryDate datetime not null comment '登録日付'
         , UpdateDate datetime not null comment '更新日付'
         , constraint tranceportdailydata_PKC primary key (PersonCode,AttendanceDate)
       ) comment '出勤交通費データ' ;
       create index TranceportDailyData_Index1
         on tranceportdailydata(AttendanceDate);
       -- 工事種別経費率マスタ
       drop table if exists typeexpensesmaster cascade;
       create table typeexpensesmaster (
         TypeCode decimal(5,0) unsigned not null comment '工事種別コード'
         , ExpensesPeriod smallint(6) not null comment '事業期'
         , NameCode decimal(5,0) unsigned not null comment '経費名称コード'
         , ExpensesRaito decimal(4,2) not null comment '経費率'
         , DeleteFlg decimal(1,0) not null comment '削除フラグ'
         , EntryDate datetime not null comment '登録日付'
         , UpdateDate datetime not null comment '更新日付'
         , constraint typeexpensesmaster_PKC primary key (TypeCode,ExpensesPeriod,NameCode)
       ) comment '工事種別経費率マスタ' ;
       -- 単位マスタ
       drop table if exists unitmaster cascade;
       create table unitmaster (
         UnitKey decimal(5,0) unsigned not null comment '単位キー'
         , DisplayOrder decimal(5,0) unsigned not null comment '表示順'
         , UnitName varchar(30) not null comment '単位名称'
         , EntryDate datetime not null comment '登録日付'
         , UpdateDate datetime not null comment '更新日付'
         , constraint unitmaster_PKC primary key (UnitKey)
       ) comment '単位マスタ' ;
       create index UnitMaster_Index1
         on unitmaster(DisplayOrder);
       -- 車両マスタ
       drop table if exists vehiclemaster cascade;
       create table vehiclemaster (
         VehicleCode decimal(5,0) unsigned not null comment '車両コード'
         , DisplayOrder decimal(5,0) unsigned not null comment '表示順'
         , VehicleName varchar(100) not null comment '車両名称'
         , RegistrationNumber varchar(30) comment '登録番号'
         , IdentificationNumber varchar(30) comment '車体番号'
         , ModelCode varchar(30) comment '型式'
         , DepartmentCode decimal(5,0) unsigned not null comment '管理部署'
         , VehicleScheduleFlg decimal(1,0) not null comment '車両予約表示フラグ'
         , PurchaseDate date comment '購入日'
         , PurchaseName varchar(30) comment '購入先'
         , PurchaseContactPersons varchar(30) comment '購入先担当者'
         , PurchasePhone varchar(13) comment '購入先電話番号'
         , LeaseDate date comment 'リース開始日'
         , LeaseDestination varchar(30) comment 'リース先'
         , LeaseContactPersons varchar(30) comment 'リース先担当者'
         , LeasePhone varchar(13) comment 'リース先電話番号'
         , InsuranceCompany varchar(30) comment '任意保険会社'
         , InsuranceContactPersons varchar(30) comment '任意保険会社担当者'
         , InsurancePhone varchar(13) comment '任意保険会社電話番号'
         , Note varchar(300) comment '備考'
         , EntryDate datetime comment '登録日付'
         , UpdateDate datetime comment '更新日付'
         , constraint vehiclemaster_PKC primary key (VehicleCode)
       ) comment '車両マスタ' ;
       -- 車両記録データ
       drop table if exists vehiclerecorddata cascade;
       create table vehiclerecorddata (
         VehicleCode decimal(5,0) unsigned not null comment '車両コード'
         , DivisionCode decimal(2,0) unsigned not null comment 'データ区分'
         , SeqNo decimal(2,0) not null comment '枝番'
         , RecordDate date comment '実地日'
         , Mileage mediumint(9) comment '走行距離'
         , Content varchar(100) comment '内容'
         , PaymentCost decimal(10,0) comment '費用'
         , EntryDate datetime not null comment '登録日付'
         , UpdateDate datetime not null comment '更新日付'
         , constraint vehiclerecorddata_PKC primary key (VehicleCode,DivisionCode,SeqNo)
       ) comment '車両記録データ' ;
       -- 車両予約データ
       drop table if exists vehiclescheduledata cascade;
       create table vehiclescheduledata (
         TargetDate date not null comment '対象日'
         , VehicleCode decimal(5,0) unsigned not null comment '車両コード'
         , MorningAfternoon decimal(1,0) not null comment '午前・午後'
         , PersonCode decimal(8,0) unsigned not null comment '担当者コード'
         , TargetYear year(4) not null comment '該当年'
         , TargetMonth tinyint(3) unsigned not null comment '該当月'
         , TargetDay year(4) not null comment '該当日'
         , EntryDate datetime not null comment '登録日付'
         , UpdateDate datetime not null comment '更新日付'
         , constraint vehiclescheduledata_PKC primary key (TargetDate,VehicleCode,MorningAfternoon)
       ) comment '車両予約データ' ;
       create index VehicleScheduleData_Index1
         on vehiclescheduledata(TargetYear);
       create index VehicleScheduleData_Index2
         on vehiclescheduledata(TargetMonth);
       create index VehicleScheduleData_Index3
         on vehiclescheduledata(TargetDay);
       -- 年マスタ
       drop table if exists yearmaster cascade;
       create table yearmaster (
         years smallint(6) not null comment '年'
       ) comment '年マスタ' ;
       create index year_Index1
         on yearmaster(years);

プロジェクト

全般

プロフィール

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

h-you / branches / ddl / ProcessManagement.sql @ 263