h-you / branches / ddl / 20171211_SQL.txt @ 329
履歴 | 表示 | アノテート | ダウンロード (3.56 KB)
1 |
-- ?H??????????f?[?^ |
---|---|
2 |
DROP TABLE IF EXISTS constructionledgerdetail_OLD; |
3 |
CREATE TABLE constructionledgerdetail_OLD As Select * from constructionledgerdetail; |
4 |
|
5 |
drop table if exists constructionledgerdetail cascade; |
6 |
|
7 |
create table constructionledgerdetail ( |
8 |
ConstructionCode decimal(10,0) unsigned not null comment '?H???R?[?h' |
9 |
, GroupCount decimal(2,0) unsigned not null comment '?O???[?v???' |
10 |
, LineCount decimal(4,0) unsigned not null comment '?s???' |
11 |
, ComponentCode decimal(5,0) unsigned not null comment '?\???L?[' |
12 |
, ItemCode decimal(5,0) unsigned not null comment '?H??L?[' |
13 |
, FirstString varchar(120) comment '???????' |
14 |
, SecondString varchar(120) comment '?H?????e' |
15 |
, CompanyType decimal(1,0) not null comment '??????R?[?h?^?C?v' |
16 |
, CompanyCode decimal(8,0) not null comment '??????R?[?h' |
17 |
, CompanyName varchar(120) comment '?????????' |
18 |
, EstimatePrice decimal(12,0) not null comment '?\?Z?i????j???z' |
19 |
, ExecutionAmount decimal(12,0) not null comment '???s???z' |
20 |
, AmountConfigRate decimal(5,2) not null comment '???z?\????' |
21 |
, PaymentBurden decimal(12,0) not null comment '?x????U?z' |
22 |
, FixDataFlg decimal(1,0) not null comment '???f?[?^?t???O' |
23 |
, IndependentFlg decimal(1,0) not null comment '????f?[?^?t???O' |
24 |
, FluctuationFlg decimal(1,0) not null comment '?????f?[?^?t???O' |
25 |
, SalaryFlg decimal(1,0) not null comment '???^?U????' |
26 |
, SalaryDays decimal(4,0) not null comment '???^?U??????' |
27 |
, OperatingFlg decimal(1,0) not null comment '?S?????t???O' |
28 |
, SourceCode decimal(10,0) unsigned not null comment '???H?????' |
29 |
, JoinTitleFlg decimal(1,0) not null comment '?H??????^?C?g??' |
30 |
, SalaryOnRegist decimal(12,0) not null comment '?o?^?????z???^' |
31 |
, PurchaseOrderFlg decimal(2,0) not null comment '?????????s?t???O' |
32 |
, DetailCount decimal(4,0) unsigned not null comment '??????' |
33 |
, EntryDate datetime not null comment '?o?^???t' |
34 |
, UpdateDate datetime not null comment '?X?V???t' |
35 |
, constraint constructionledgerdetail_PKC primary key (ConstructionCode,GroupCount,LineCount) |
36 |
) comment '?H??????????f?[?^' ; |
37 |
|
38 |
create index LedgerDetail_Index01 |
39 |
on constructionledgerdetail(ConstructionCode); |
40 |
|
41 |
create index LedgerDetail_Index02 |
42 |
on constructionledgerdetail(GroupCount); |
43 |
|
44 |
create index LedgerDetail_Index03 |
45 |
on constructionledgerdetail(LineCount); |
46 |
|
47 |
create index LedgerDetail_Index04 |
48 |
on constructionledgerdetail(SourceCode); |
49 |
|
50 |
create index LedgerDetail_Index05 |
51 |
on constructionledgerdetail(DetailCount); |
52 |
|
53 |
Insert INTO constructionledgerdetail |
54 |
SELECT |
55 |
ConstructionCode |
56 |
, GroupCount |
57 |
, LineCount |
58 |
, ComponentCode |
59 |
, ItemCode |
60 |
, FirstString |
61 |
, SecondString |
62 |
, CompanyType |
63 |
, CompanyCode |
64 |
, CompanyName |
65 |
, EstimatePrice |
66 |
, ExecutionAmount |
67 |
, AmountConfigRate |
68 |
, PaymentBurden |
69 |
, FixDataFlg |
70 |
, IndependentFlg |
71 |
, FluctuationFlg |
72 |
, SalaryFlg |
73 |
, SalaryDays |
74 |
, OperatingFlg |
75 |
, SourceCode |
76 |
, JoinTitleFlg |
77 |
, SalaryOnRegist |
78 |
, PurchaseOrderFlg |
79 |
, 0 |
80 |
, EntryDate |
81 |
, UpdateDate |
82 |
FROM constructionledgerdetail_OLD |
83 |
; |
84 |
|
85 |
ALTER TABLE purchaseorderdetail |
86 |
CHANGE SourceCode SourceCode decimal(10,0) unsigned not null |
87 |
COMMENT '???H?????'; |
88 |
ALTER TABLE purchaseorderdetail |
89 |
CHANGE SourceLineCnt SourceDetailCnt decimal(4,0) unsigned not null |
90 |
COMMENT '????????'; |
91 |
|
92 |
ALTER TABLE constructionledgerexcute |
93 |
CHANGE LineCount LineCount decimal(4,0) unsigned not null |
94 |
comment '?s???'; |
95 |
|
96 |
Truncate table paymentapprovalinfo; |
97 |
Truncate table paymentdatadetail; |
98 |
Truncate table billingdata; |
99 |
Truncate table billingdatadetail; |
100 |
Truncate table billingpaymentsummary; |
101 |
commit; |
102 |
|