-- �HŽ–’S“–—š—ðƒf�[ƒ^
drop table if exists ConstrChgCharge cascade;

create table ConstrChgCharge (
  ConstructionCode decimal(10,0) unsigned not null comment '�HŽ–ƒR�[ƒh'
  , Division decimal(1,0) not null comment '�Ý’è‹æ•ª'
  , SeqNo smallint(5) unsigned not null comment '˜A”Ô'
  , DepartmentCode decimal(5,0) unsigned not null comment '•”��ƒR�[ƒh'
  , PersonCode  decimal(8,0) not null comment '’S“–ŽÒƒR�[ƒh'
  , StartDate  date comment '’S“–ŠJŽn”NŒŽ“ú'
  , CompDate  date comment '’S“–�I—¹”NŒŽ“ú'
  , EntryDate datetime not null comment '“o˜^“ú•t'
  , UpdateDate datetime not null comment '�X�V“ú•t'
  , constraint ConstrChgCharge_PKC primary key (ConstructionCode, Division, SeqNo)
) comment '�HŽ–’S“–—š—ðƒf�[ƒ^' ;

-- �HŽ–Šî–{�î•ñ
DROP TABLE IF EXISTS constructionbaseinfo_OLD;
CREATE TABLE constructionbaseinfo_OLD As Select * from constructionbaseinfo;

drop table if exists constructionbaseinfo cascade;

create table constructionbaseinfo (
  ConstructionCode decimal(10,0) unsigned not null comment '�HŽ–ƒR�[ƒh'
  , TyingFlg decimal(1,0) not null comment '•R•tƒf�[ƒ^ƒtƒ‰ƒO'
  , JoinFlg decimal(1,0) not null comment 'Œ‹�‡Œ_–ñ�HŽ–ƒtƒ‰ƒO'
  , ConstructionYear smallint(5) unsigned not null comment '�HŽ–”N“x'
  , ConstructionPeriod smallint(5) unsigned not null comment '�HŽ–Šú�”'
  , RequestedDate date comment 'ˆË—ŠŽó‚¯“ú'
  , EstimatesSubmitDeadline date comment 'Œ©�Ï’ñ�oŠúŒÀ'
  , EstimatesSubmittedDate date comment 'Œ©�Ï’ñ�o“ú'
  , ProvisionalOrderDate date comment '‰¼Žó’�“ú'
  , OrderDate date comment 'Žó’�“ú'
  , OrderStartingDate date comment 'ŠJŽn—\’è“ú'
  , OrderCompletionDate date comment 'Š®—¹—\’è“ú'
  , PreparationStartDate date comment 'Ž{�H�€”õŠJŽn“ú'
  , ConstructionStartingDate date comment 'Ž{�HŠJŽn“ú'
  , ConstructionCompletionDate date comment 'Ž{�HŠ®—¹“ú'
  , NonOrderDate date comment '”ñŽó’�“ú'
  , OrdersDecisionPrice decimal(12,0) not null comment '�Å”²Žó’�Œˆ’è‹àŠz'
  , OrdersDecisionPriceInTax decimal(12,0) not null comment '�Å�žŽó’�Œˆ’è‹àŠz'
  , SalesDepCode decimal(5,0) unsigned not null comment '‰c‹Æ’S“–ŽÒ•”��ƒR�[ƒh'
  , SalesPersonCode decimal(8,0) unsigned not null comment '‰c‹Æ’S“–ŽÒƒR�[ƒh'
  , ConstrDepCode decimal(5,0) unsigned not null comment '�HŽ–’S“–ŽÒ•”��ƒR�[ƒh'
  , ConstructionPersonCode decimal(8,0) not null comment '�HŽ–’S“–ŽÒƒR�[ƒh'
  , ConstrSubDepCode decimal(5,0) unsigned not null comment '�HŽ–•›’S“–ŽÒ•”��ƒR�[ƒh'
  , ConstrSubPersonCode decimal(8,0) not null comment '�HŽ–•›’S“–ŽÒƒR�[ƒh'
  , ConstrInstrDepCode decimal(5,0) unsigned not null comment '�HŽ–Žw“±ˆõ•”��ƒR�[ƒh'
  , ConstructionInstructor decimal(8,0) not null comment '�HŽ–Žw“±ˆõƒR�[ƒh'
  , TransferConstruction decimal(8,0) not null comment '�HŽ–ˆÚŠÇ�æƒR�[ƒh'
  , TransferConstructionDate date comment '�HŽ–ˆÚŠÇ“ú'
  , OrderersDivision decimal(5,0) not null comment '”­’�ŽÒ‹æ•ªƒR�[ƒh'
  , OrderersCode decimal(5,0) unsigned not null comment '”­’�ŽÒƒR�[ƒh'
  , EstimatesExpirationDate date comment 'Œ©�Ï—LŒøŠúŒÀ'
  , ConstructionPeriodStart date comment 'Œ_–ñ�HŠúŠJŽn“ú'
  , ConstructionPeriodEnd date comment 'Œ_–ñ�HŠúŠ®—¹'
  , ConstructionPeriodStart2 date comment 'Œ_–ñ�HŠúŠJŽn�i‰„’·Žž�j'
  , ConstructionPeriodEnd2 date comment 'Œ_–ñ�HŠúŠ®—¹�i‰„’·Žž�j'
  , StartDate date comment '�HŽ–ŠJŽn“ú'
  , EndDate date comment '�HŽ–�I—¹“ú'
  , PurchaseOrderMailingDate date comment '’�•¶�‘—X‘—“ú'
  , PurchaseOrderReturnDate date comment '’�•¶�‘•Ô‘—“ú'
  , PurchaseOrderReturnCheckDate date comment '’�•¶�‘•Ô‘—Šm”F“ú'
  , ConsumptionTaxFlg decimal(1,0) not null comment '�Á”ï�Ńtƒ‰ƒO'
  , PrimeContractorFlg decimal(1,0) not null comment 'Œ³�¿ƒtƒ‰ƒO'
  , SalesCostFlg decimal(1,0) not null comment '‰c‹ÆŒo”ï”­�¶ƒtƒ‰ƒO'
  , ConstructionStatusFlg decimal(2,0) not null comment '�HŽ–�ó‘Ôƒtƒ‰ƒO'
  , ConstructionType decimal(5,0) not null comment '�HŽ–Ží•ÊƒR�[ƒh'
  , EstimateType decimal(1,0) not null comment 'Œ©�ÏŽí•Ê'
  , BillingStartDate date not null comment '�¿‹��€”õŠJŽn“ú'
  , BillingDate date not null comment '�¿‹�“ú'
  , BillingSplitFlg decimal(1,0) not null comment '�¿‹�‰ñ�”ƒtƒ‰ƒO'
  , BillingSendDate date comment '�¿‹��‘—X‘—/Žè“n‚µ“ú'
  , LedgerComplateDate date not null comment '�HŽ–�Ú�ב䒠“ü—ÍŠ®—¹“ú•t'
  , InspectPlanningDate date not null comment 'ŒŸ�¸—\’è“ú'
  , InspectExecuteDate date not null comment 'ŒŸ�¸ŽÀ�s“ú'
  , RevCompleteDate date not null comment 'ŒŸ�¸�¥�³Š®—¹“ú'
  , EntryDate datetime not null comment '“o˜^“ú•t'
  , UpdateDate datetime not null comment '�X�V“ú•t'
  , constraint constructionbaseinfo_PKC primary key (ConstructionCode)
) comment '�HŽ–Šî–{�î•ñ' ;

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);

Insert INTO constructionbaseinfo
SELECT
  A.ConstructionCode
  , A.TyingFlg
  , A.JoinFlg
  , A.ConstructionYear
  , A.ConstructionPeriod
  , A.RequestedDate
  , A.EstimatesSubmitDeadline
  , A.EstimatesSubmittedDate
  , A.ProvisionalOrderDate
  , A.OrderDate
  , A.OrderStartingDate
  , A.OrderCompletionDate
  , A.PreparationStartDate
  , A.ConstructionStartingDate
  , A.ConstructionCompletionDate
  , A.NonOrderDate
  , A.OrdersDecisionPrice
  , A.OrdersDecisionPriceInTax
  , B.DepartmentCode
  , A.SalesPersonCode
  , ifnull(C.DepartmentCode, 0)
  , A.ConstructionPersonCode
  , ifnull(D.DepartmentCode, 0)
  , A.ConstrSubPersonCode
  , ifnull(E.DepartmentCode, 0)
  , A.ConstructionInstructor
  , A.TransferConstruction
  , A.TransferConstructionDate
  , A.OrderersDivision
  , A.OrderersCode
  , A.EstimatesExpirationDate
  , A.ConstructionPeriodStart
  , A.ConstructionPeriodEnd
  , A.ConstructionPeriodStart2
  , A.ConstructionPeriodEnd2
  , A.StartDate
  , A.EndDate
  , A.PurchaseOrderMailingDate
  , A.PurchaseOrderReturnDate
  , A.PurchaseOrderReturnCheckDate
  , A.ConsumptionTaxFlg
  , A.PrimeContractorFlg
  , A.SalesCostFlg
  , A.ConstructionStatusFlg
  , A.ConstructionType
  , A.EstimateType
  , A.BillingStartDate
  , A.BillingDate
  , A.BillingSplitFlg
  , A.BillingSendDate
  , A.LedgerComplateDate
  , InspectPlanningDate
  , InspectExecuteDate
  , RevCompleteDate
  , A.EntryDate
  , A.UpdateDate 
FROM
  constructionbaseinfo_OLD As A 
  Left Join chgchargedep As B 
    On B.PersonCode = A.SalesPersonCode
    AND ( 
      DATE (B.StartDate) <= DATE ('2016/07/31') 
      AND ( 
        DATE ('2015/08/01') <= DATE (B.CompDate) 
        OR DATE ('0001/01/01') = DATE (B.CompDate)
      )
    ) 
  Left Join chgchargedep As C 
    On C.PersonCode = A.ConstructionPersonCode 
    AND ( 
      DATE (C.StartDate) <= DATE ('2016/07/31') 
      AND ( 
        DATE ('2015/08/01') <= DATE (C.CompDate) 
        OR DATE ('0001/01/01') = DATE (C.CompDate)
      )
    ) 
  Left Join chgchargedep As D 
    On D.PersonCode = A.ConstrSubPersonCode 
    AND ( 
      DATE (D.StartDate) <= DATE ('2016/07/31') 
      AND ( 
        DATE ('2015/08/01') <= DATE (D.CompDate) 
        OR DATE ('0001/01/01') = DATE (D.CompDate)
      )
    ) 
  Left Join chgchargedep As E 
    On E.PersonCode = A.ConstructionInstructor
    AND ( 
      DATE (D.StartDate) <= DATE ('2016/07/31') 
      AND ( 
        DATE ('2015/08/01') <= DATE (D.CompDate) 
        OR DATE ('0001/01/01') = DATE (D.CompDate)
      )
    ) 
Where A.ConstructionPeriod = 15
;

Insert INTO constructionbaseinfo
SELECT
  A.ConstructionCode
  , A.TyingFlg
  , A.JoinFlg
  , A.ConstructionYear
  , A.ConstructionPeriod
  , A.RequestedDate
  , A.EstimatesSubmitDeadline
  , A.EstimatesSubmittedDate
  , A.ProvisionalOrderDate
  , A.OrderDate
  , A.OrderStartingDate
  , A.OrderCompletionDate
  , A.PreparationStartDate
  , A.ConstructionStartingDate
  , A.ConstructionCompletionDate
  , A.NonOrderDate
  , A.OrdersDecisionPrice
  , A.OrdersDecisionPriceInTax
  , B.DepartmentCode
  , A.SalesPersonCode
  , ifnull(C.DepartmentCode, 0)
  , A.ConstructionPersonCode
  , ifnull(D.DepartmentCode, 0)
  , A.ConstrSubPersonCode
  , ifnull(E.DepartmentCode, 0)
  , A.ConstructionInstructor
  , A.TransferConstruction
  , A.TransferConstructionDate
  , A.OrderersDivision
  , A.OrderersCode
  , A.EstimatesExpirationDate
  , A.ConstructionPeriodStart
  , A.ConstructionPeriodEnd
  , A.ConstructionPeriodStart2
  , A.ConstructionPeriodEnd2
  , A.StartDate
  , A.EndDate
  , A.PurchaseOrderMailingDate
  , A.PurchaseOrderReturnDate
  , A.PurchaseOrderReturnCheckDate
  , A.ConsumptionTaxFlg
  , A.PrimeContractorFlg
  , A.SalesCostFlg
  , A.ConstructionStatusFlg
  , A.ConstructionType
  , A.EstimateType
  , A.BillingStartDate
  , A.BillingDate
  , A.BillingSplitFlg
  , A.BillingSendDate
  , A.LedgerComplateDate
  , InspectPlanningDate
  , InspectExecuteDate
  , RevCompleteDate
  , A.EntryDate
  , A.UpdateDate 
FROM
  constructionbaseinfo_OLD As A 
  Left Join chgchargedep As B 
    On B.PersonCode = A.SalesPersonCode
    AND ( 
      DATE (B.StartDate) <= DATE ('2017/07/31') 
      AND ( 
        DATE ('2016/08/01') <= DATE (B.CompDate) 
        OR DATE ('0001/01/01') = DATE (B.CompDate)
      )
    ) 
  Left Join chgchargedep As C 
    On C.PersonCode = A.ConstructionPersonCode 
    AND ( 
      DATE (C.StartDate) <= DATE ('2017/07/31') 
      AND ( 
        DATE ('2016/08/01') <= DATE (C.CompDate) 
        OR DATE ('0001/01/01') = DATE (C.CompDate)
      )
    ) 
  Left Join chgchargedep As D 
    On D.PersonCode = A.ConstrSubPersonCode 
    AND ( 
      DATE (D.StartDate) <= DATE ('2017/07/31') 
      AND ( 
        DATE ('2016/08/01') <= DATE (D.CompDate) 
        OR DATE ('0001/01/01') = DATE (D.CompDate)
      )
    ) 
  Left Join chgchargedep As E 
    On E.PersonCode = A.ConstructionInstructor
    AND ( 
      DATE (E.StartDate) <= DATE ('2017/07/31') 
      AND ( 
        DATE ('2016/08/01') <= DATE (E.CompDate) 
        OR DATE ('0001/01/01') = DATE (E.CompDate)
      )
    ) 
Where A.ConstructionPeriod = 16
;

Insert INTO constructionbaseinfo
SELECT
  A.ConstructionCode
  , A.TyingFlg
  , A.JoinFlg
  , A.ConstructionYear
  , A.ConstructionPeriod
  , A.RequestedDate
  , A.EstimatesSubmitDeadline
  , A.EstimatesSubmittedDate
  , A.ProvisionalOrderDate
  , A.OrderDate
  , A.OrderStartingDate
  , A.OrderCompletionDate
  , A.PreparationStartDate
  , A.ConstructionStartingDate
  , A.ConstructionCompletionDate
  , A.NonOrderDate
  , A.OrdersDecisionPrice
  , A.OrdersDecisionPriceInTax
  , B.DepartmentCode
  , A.SalesPersonCode
  , ifnull(C.DepartmentCode, 0)
  , A.ConstructionPersonCode
  , ifnull(D.DepartmentCode, 0)
  , A.ConstrSubPersonCode
  , ifnull(E.DepartmentCode, 0)
  , A.ConstructionInstructor
  , A.TransferConstruction
  , A.TransferConstructionDate
  , A.OrderersDivision
  , A.OrderersCode
  , A.EstimatesExpirationDate
  , A.ConstructionPeriodStart
  , A.ConstructionPeriodEnd
  , A.ConstructionPeriodStart2
  , A.ConstructionPeriodEnd2
  , A.StartDate
  , A.EndDate
  , A.PurchaseOrderMailingDate
  , A.PurchaseOrderReturnDate
  , A.PurchaseOrderReturnCheckDate
  , A.ConsumptionTaxFlg
  , A.PrimeContractorFlg
  , A.SalesCostFlg
  , A.ConstructionStatusFlg
  , A.ConstructionType
  , A.EstimateType
  , A.BillingStartDate
  , A.BillingDate
  , A.BillingSplitFlg
  , A.BillingSendDate
  , A.LedgerComplateDate
  , InspectPlanningDate
  , InspectExecuteDate
  , RevCompleteDate
  , A.EntryDate
  , A.UpdateDate 
FROM
  constructionbaseinfo_OLD As A 
  Left Join chgchargedep As B 
    On B.PersonCode = A.SalesPersonCode
    AND ( 
      DATE (B.StartDate) <= DATE ('2018/03/31') 
      AND ( 
        DATE ('2017/08/01') <= DATE (B.CompDate) 
        OR DATE ('0001/01/01') = DATE (B.CompDate)
      )
    ) 
  Left Join chgchargedep As C 
    On C.PersonCode = A.ConstructionPersonCode 
    AND ( 
      DATE (C.StartDate) <= DATE ('2018/03/31') 
      AND ( 
        DATE ('2017/08/01') <= DATE (C.CompDate) 
        OR DATE ('0001/01/01') = DATE (C.CompDate)
      )
    ) 
  Left Join chgchargedep As D 
    On D.PersonCode = A.ConstrSubPersonCode 
    AND ( 
      DATE (D.StartDate) <= DATE ('2018/03/31') 
      AND ( 
        DATE ('2017/08/01') <= DATE (D.CompDate) 
        OR DATE ('0001/01/01') = DATE (D.CompDate)
      )
    ) 
  Left Join chgchargedep As E 
    On E.PersonCode = A.ConstructionInstructor
    AND ( 
      DATE (E.StartDate) <= DATE ('2018/03/31') 
      AND ( 
        DATE ('2017/08/01') <= DATE (E.CompDate) 
        OR DATE ('0001/01/01') = DATE (E.CompDate)
      )
    ) 
Where A.ConstructionPeriod = 17
;

Insert INTO constructionbaseinfo
SELECT
  A.ConstructionCode
  , A.TyingFlg
  , A.JoinFlg
  , A.ConstructionYear
  , A.ConstructionPeriod
  , A.RequestedDate
  , A.EstimatesSubmitDeadline
  , A.EstimatesSubmittedDate
  , A.ProvisionalOrderDate
  , A.OrderDate
  , A.OrderStartingDate
  , A.OrderCompletionDate
  , A.PreparationStartDate
  , A.ConstructionStartingDate
  , A.ConstructionCompletionDate
  , A.NonOrderDate
  , A.OrdersDecisionPrice
  , A.OrdersDecisionPriceInTax
  , B.DepartmentCode
  , A.SalesPersonCode
  , ifnull(C.DepartmentCode, 0)
  , A.ConstructionPersonCode
  , ifnull(D.DepartmentCode, 0)
  , A.ConstrSubPersonCode
  , ifnull(E.DepartmentCode, 0)
  , A.ConstructionInstructor
  , A.TransferConstruction
  , A.TransferConstructionDate
  , A.OrderersDivision
  , A.OrderersCode
  , A.EstimatesExpirationDate
  , A.ConstructionPeriodStart
  , A.ConstructionPeriodEnd
  , A.ConstructionPeriodStart2
  , A.ConstructionPeriodEnd2
  , A.StartDate
  , A.EndDate
  , A.PurchaseOrderMailingDate
  , A.PurchaseOrderReturnDate
  , A.PurchaseOrderReturnCheckDate
  , A.ConsumptionTaxFlg
  , A.PrimeContractorFlg
  , A.SalesCostFlg
  , A.ConstructionStatusFlg
  , A.ConstructionType
  , A.EstimateType
  , A.BillingStartDate
  , A.BillingDate
  , A.BillingSplitFlg
  , A.BillingSendDate
  , A.LedgerComplateDate
  , InspectPlanningDate
  , InspectExecuteDate
  , RevCompleteDate
  , A.EntryDate
  , A.UpdateDate 
FROM
  constructionbaseinfo_OLD As A 
  Left Join chgchargedep As B 
    On B.PersonCode = A.SalesPersonCode
    AND ( 
      DATE (B.StartDate) <= DATE ('2019/03/31') 
      AND ( 
        DATE ('2018/04/01') <= DATE (B.CompDate) 
        OR DATE ('0001/01/01') = DATE (B.CompDate)
      )
    ) 
  Left Join chgchargedep As C 
    On C.PersonCode = A.ConstructionPersonCode 
    AND ( 
      DATE (C.StartDate) <= DATE ('2019/03/31') 
      AND ( 
        DATE ('2018/04/01') <= DATE (C.CompDate) 
        OR DATE ('0001/01/01') = DATE (C.CompDate)
      )
    ) 
  Left Join chgchargedep As D 
    On D.PersonCode = A.ConstrSubPersonCode 
    AND ( 
      DATE (D.StartDate) <= DATE ('2019/03/31') 
      AND ( 
        DATE ('2018/04/01') <= DATE (D.CompDate) 
        OR DATE ('0001/01/01') = DATE (D.CompDate)
      )
    ) 
  Left Join chgchargedep As E 
    On E.PersonCode = A.ConstructionInstructor
    AND ( 
      DATE (E.StartDate) <= DATE ('2019/03/31') 
      AND ( 
        DATE ('2018/04/01') <= DATE (E.CompDate) 
        OR DATE ('0001/01/01') = DATE (E.CompDate)
      )
    ) 
Where A.ConstructionPeriod = 18
;

-- �HŽ–Šî–{�î•ñ–¾�×
DROP TABLE IF EXISTS constructionbaseinfodetail_OLD;

CREATE TABLE constructionbaseinfodetail_OLD As Select * from constructionbaseinfodetail;

drop table if exists constructionbaseinfodetail cascade;

create table constructionbaseinfodetail (
  ConstructionCode decimal(10,0) unsigned not null comment '�HŽ–ƒR�[ƒh'
  , DetailNo decimal(3,0) not null comment '–¾�×”Ô�†'
  , DetailString varchar(300) comment '–¾�דà—e'
  , EntryDate datetime not null comment '“o˜^“ú•t'
  , UpdateDate datetime not null comment '�X�V“ú•t'
  , constraint constructionbaseinfodetail_PKC primary key (ConstructionCode,DetailNo)
) comment '�HŽ–Šî–{�î•ñ–¾�×' ;

create index BaseInfoDetail_Idx1
  on constructionbaseinfodetail(ConstructionCode);

create index BaseInfoDetail_Idx2
  on constructionbaseinfodetail(DetailNo);

Insert INTO constructionbaseinfodetail
SELECT * From constructionbaseinfodetail_OLD;

Insert 
INTO constructionbaseinfodetail 
SELECT
  Base.ConstructionCode
  , 101
  , Dep.DepartmentString
  , Now()
  , Now() 
From
  constructionbaseinfo As Base 
  Inner Join departmentmaster as Dep 
    On Dep.DepartmentCode = Base.SalesDepCode; 

Insert 
INTO constructionbaseinfodetail 
SELECT
  Base.ConstructionCode
  , 102
  , ifnull(Dep.DepartmentString,'')
  , Now()
  , Now() 
From
  constructionbaseinfo As Base 
  Inner Join departmentmaster as Dep 
    On Dep.DepartmentCode = Base.ConstrDepCode; 

Insert 
INTO constructionbaseinfodetail 
SELECT
  Base.ConstructionCode
  , 103
  , ifnull(Dep.DepartmentString,'')
  , Now()
  , Now() 
From
  constructionbaseinfo As Base 
  Inner Join departmentmaster as Dep 
    On Dep.DepartmentCode = Base.ConstrSubDepCode; 

Insert 
INTO constructionbaseinfodetail 
SELECT
  Base.ConstructionCode
  , 104
  , ifnull(Dep.DepartmentString,'')
  , Now()
  , Now() 
From
  constructionbaseinfo As Base 
  Inner Join departmentmaster as Dep 
    On Dep.DepartmentCode = Base.ConstrInstrDepCode; 

UPDATE constructionbaseinfodetail AS BD 
SET 
    BD.DetailString = 'ŽR–{‘g'
WHERE
    BD.ConstructionCode IN (SELECT 
            ConstructionCode
        FROM
            constructionbaseinfo AS B
        WHERE
            ConstructionPeriod < 17)
        AND BD.DetailNo > 100
        AND DetailString = 'ŽÐ’·Žº';

-- ”­’�ŽÒ“o˜^�\�¿ƒf�[ƒ^
drop table if exists costomerregist cascade;

create table costomerregist (
  CreatePersonCode decimal(8,0) unsigned not null comment '�\�¿ŽÒ”Ô�†'
  , CreateDate date not null comment '�ì�¬“ú'
  , SeqNo decimal(2,0) not null comment 'ƒL�[˜A”Ô'
  , DataMode decimal(1,0) not null comment 'ƒf�[ƒ^‹æ•ª'
  , SourceCotegory decimal(5,0) unsigned not null comment '”­’�ŽÒ‹æ•ª'
  , SourceCode decimal(5,0) unsigned not null comment '”­’�ŽÒƒR�[ƒh'
  , PetitionPeriod decimal(4,0) unsigned not null comment 'Ž–‹ÆŠú'
  , OrderFlg decimal(1,0) not null comment '–@�l’c‘Ì�EŒÂ�lƒtƒ‰ƒO'
  , CorporateStatusName varchar(100) comment '–@�lŠi–¼�Ì'
  , CorporateStatusPoint decimal(1,0) comment '–@�lŠi–¼�̈ʒu'
  , OrderersName1 varchar(60) not null comment '”­’�ŽÒ–¼1'
  , OrderersName2 varchar(60) comment '”­’�ŽÒ–¼‚Q'
  , DepartmentName varchar(60) comment '•”��–¼'
  , PersonName varchar(60) comment '’S“–ŽÒ–¼'
  , ZipCode varchar(8) comment '—X•Ö”Ô�†'
  , Address1 varchar(60) comment '�Z�Š‚P'
  , Address2 varchar(60) comment '�Z�Š2'
  , Address3 varchar(60) comment '�Z�Š3'
  , PhoneNumber varchar(13) comment '“d˜b”Ô�†'
  , FaxNumber varchar(13) comment 'FAX”Ô�†'
  , MailAddress varchar(257) comment 'ƒ��[ƒ‹ƒAƒhƒŒƒX'
  , Note varchar(300) comment '”õ�l'
  , OrderCotegory decimal(5,0) unsigned not null comment '”­’�ŽÒ‹æ•ª'
  , OrderDate date not null comment '�\�¿“ú'
  , OrderNo decimal(2,0) unsigned not null comment '�\�¿Žó•t”Ô�†'
  , CreateDepartmentCode decimal(5,0) unsigned not null comment '�\�¿•”��ƒR�[ƒh'
  , CreateDepartmentName varchar(60) comment '�\�¿•”��–¼'
  , EntryDate datetime not null comment '“o˜^“ú•t'
  , UpdateDate datetime not null comment '�X�V“ú•t'
  , constraint costomerregist_PKC primary key (CreatePersonCode,CreateDate,SeqNo)
) comment '”­’�ŽÒ“o˜^�\�¿ƒf�[ƒ^' ;

create index CostomerRegist_Index1
  on costomerregist(PetitionPeriod);

create index CostomerRegist_Index2
  on costomerregist(CreatePersonCode);

create index CostomerRegist_Index3
  on costomerregist(CreateDepartmentCode);

commit;