建表语句.sql
5.03 KB
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
CREATE TABLE T_ICLEAR_API_APPINFO(
ID int IDENTITY(1,1) NOT NULL PRIMARY KEY
,APPID VARCHAR(40)
,APPKEY VARCHAR(40)
,SECURITY VARCHAR(16)
,CREATE_TIME DATE
,IS_ENABLE VARCHAR(1)
)
INSERT INTO T_ICLEAR_API_APPINFO VALUES('IDE','iClearExport','b92131f03f19a348',GETDATE(),'1');
INSERT INTO T_ICLEAR_API_APPINFO VALUES('BASF','iClearExportC','6db193cb4165a71b',GETDATE(),'1');
INSERT INTO T_ICLEAR_API_APPINFO VALUES('BASF','iClearExport','6db193cb4165a71b',GETDATE(),'1');
CREATE TABLE T_ICLEAR_API_LOG(
MSGID int identity(1,1) primary key --PK
,MSG_TYPE VARCHAR(20) --类型: C D
,APPID VARCHAR(20) --用户ID
,APPKEY VARCHAR(20) --用户key
,CREATE_TIME datetime --创建时间
,PUSH_TIME datetime --消息推送时间
,RECEIVE_RESULT VARCHAR(100) --记录接收后是否验证通过的结果
,ERROR_CODE VARCHAR(10) --错误代码
,RESPONSE_TIME datetime -- 响应时间
,SPEND_TIME numeric(5, 0) --消耗时间（ms）
,SENDTIMES int --更新次数
,BILLNO VARCHAR(20) --运单号
,CONTRNO VARCHAR(20) --合同号
)
CREATE TABLE T_ICLEAR_API_MAWB(
ID int identity(1,1) primary key --PK
,MSGID int
,APPID VARCHAR(20) --用户ID
,IEFlag varchar(1)
,TYPE VARCHAR(6)
,AGENTCODE VARCHAR(18)
,AGENTNAME VARCHAR(70)
,BILLNO VARCHAR(32)
,CONTRNO VARCHAR(32)
,CUSTOMMASTER VARCHAR(4)
,CUTMODE VARCHAR(3)
,DECLTRNREL VARCHAR(1)
,DISTINATEPORT VARCHAR(6)
,EDIID VARCHAR(3)
,ENTRYID VARCHAR(18)
,FEECURR VARCHAR(3)
,FEEMARK VARCHAR(1)
,FEERATE NUMERIC(19,5)
,CROSSWET NUMERIC(19,5)
,IEPORT VARCHAR(4)
,INPUTERNAME VARCHAR(32)
,INSURCURR VARCHAR(3)
,INSURMARK VARCHAR(1)
,INSURRATE NUMERIC(19,5)
,MANUALNO VARCHAR(12)
,NETWT NUMERIC(19,5)
,OTHERCURR VARCHAR(3)
,OTHERRATE NUMERIC(19,5)
,OTHERMARK VARCHAR(1)
,OWNERCODE VARCHAR(18)
,OWNERNAME VARCHAR(70)
,PACKNO NUMERIC(9)
,PREENTRYID VARCHAR(9)
,TRADECOUNTRY VARCHAR(3)
,TRADEMODE VARCHAR(4)
,TRADECODE VARCHAR(18)
,TRAFMODE VARCHAR(1)
,TRADENAME VARCHAR(70)
,TRANSMODE VARCHAR(1)
,WRAPTYPE VARCHAR(2)
,TRADECODESCC VARCHAR(18)
,OWNERCODESCC VARCHAR(18)
,TRADEAREACODE VARCHAR(3)
,MARKNO VARCHAR(400)
,ENTYPORTCODE VARCHAR(8)
,GOODSPLACE VARCHAR(100)
,DECLARENAME VARCHAR(50)
,OVERSEASCONSIGNORCODE VARCHAR(50)
,OVERSEASCONSIGNEECODE VARCHAR(50)
,OVERSEASCONSIGNEEENAME VARCHAR(400)
)
CREATE TABLE T_ICLEAR_API_MAWBDETAIL(
ID int identity(1,1) primary key --PK
,MSGID int
,MAWBID int
,DETAILID VARCHAR(50)
,CLASSMARK VARCHAR(1)
,CODETS VARCHAR(10)
,DECLPRICE NUMERIC(19,5)
,DECLTOTAL NUMERIC(19,5)
,DUTYMODE VARCHAR(1)
,EXGNO VARCHAR(30)
,EXGVERSION VARCHAR(8)
,FIRSTUNIT VARCHAR(3)
,FIRSTQTY NUMERIC(19,5)
,GUNIT VARCHAR(3)
,GMODE VARCHAR(255)
,GNAME VARCHAR(255)
,GNO NUMERIC(19)
,GQTY NUMERIC(19,5)
,ORIGINCURRENTY VARCHAR(3)
,SECONDUNIT VARCHAR(3)
,SECONDQTY NUMERIC(19,5)
,TRADECURR VARCHAR(3)
,USETO VARCHAR(2)
,WORKUSD NUMERIC(19,4)
,DESTINATIONCOUNTRY VARCHAR(3)
,CIQCODE VARCHAR(20)
,DECLGOODSENAME VARCHAR(100)
,DESTCODE VARCHAR(8)
,DISTRICTCODE VARCHAR(5)
)
---ALTER TABLE T_ICLEAR_API_MAWB ADD SOURCE_TYPE VARCHAR(10);
ALTER TABLE T_CHM_CONSIGNMENT_DETAIL ADD DETAILID VARCHAR(50);
--添加tsn口岸
INSERT into T_AU_ORGANIZATION(CODE,NAME,TYPE_ID,PARENT_ID,[LEVEL],CREATE_USER_ID,CREATE_TIME)VALUES('tsn','tsn口岸',84534,1,6,5503,GETDATE())
--添加tsn口岸操作员02
insert into T_AU_USER (LOGIN_NAME,USERNAME,PASSWORD,IS_VALID,VISIBLE,CREATE_TIME,VERSION,CHANGE_PASSWORD,ORG_ID,ORG_NAME,INIT_OWNER_NAME,TYPE_ID,TYPE_NAME)VALUES('tsnczy2','TSN操作员02','985111aed0bc0920dce9b362de2b1fa122a5ccdd6ac2a8e9b3ae5cfa58169f7725830de4566b7c43',1,1,GETDATE(),0,1,(select ID from T_AU_ORGANIZATION where code = 'tsn'),(select NAME from T_AU_ORGANIZATION where code = 'tsn'),1,(select ID from T_BI_DICTIONARY_ENTRIES where ID = 84534),(select CHINESE_NAME from T_BI_DICTIONARY_ENTRIES where ID = 84534))
--添加权限
insert into T_AU_USER_ROLES (USER_ID,ROLE_ID,VERSION)VALUES(53573,3910,0);
insert into T_AU_USER_ROLES (USER_ID,ROLE_ID,VERSION)VALUES(53574,3910,0);
--数据字段中添加天津口岸
insert INTO T_BI_DICTIONARY_ENTRIES(CODE,CHINESE_NAME,ENGLISH_NAME,DESCRIPTION,STATUS,DICT_ID,DICT_CODE,ORDINAL)VALUES('port_tsn','TSN','TSN','TSN',1,'50161','port',50)
CREATE TABLE T_ICLEAR_API_HSCODE_LOG(
ID int identity(1,1) primary key --PK
,APPID VARCHAR(20) --用户ID
,APPKEY VARCHAR(20) --用户key
,CODE VARCHAR(2000) --hscode编码
,CREATE_TIME datetime --创建时间
,RECEIVE_RESULT VARCHAR(100) --记录接收后是否验证通过的结果
,ERROR_CODE VARCHAR(10) --错误代码
,RESPONSE_TIME datetime -- 响应时间
,SPEND_TIME numeric(5, 0) --消耗时间（ms）
)