1
drop table if exists fin_influx_extension;
2
drop table if exists fin_influx_inst;
3
drop table if exists fin_settle_serial_map;
4
drop table if exists fin_influx_terminal;
5
drop table if exists fin_info_transaction;
6
drop table if exists fin_influx_terminal_unique;
7
drop table if exists fin_request_no_unique;
8
drop table if exists fin_settle_serial_unique;
9
drop table if exists fin_influx_transaction;
10
drop table if exists idx1_fin_influx_transaction;
11
drop table if exists fin_influx_payer;
12
drop table if exists idx1_fin_influx_payer;
13
drop table if exists idx2_fin_influx_payer;
14
create table fin_influx_extension(INFLUX_ID varchar(64) primary key, EXTENSION1 varchar(32), EXTENSION2 varchar(32), REQ_EXTENSION varchar(4000), RES_EXTENSION varchar(4000), GMT_CREATE datetime(6), GMT_MODIFIED datetime(6));
15
insert into fin_influx_extension(INFLUX_ID, REQ_EXTENSION, RES_EXTENSION, GMT_CREATE, GMT_MODIFIED) values('0001','req_1', 'res_1', '2012-12-20 12:00', '2013-1-20 13:00');16
select INFLUX_ID,EXTENSION1,EXTENSION2,REQ_EXTENSION,RES_EXTENSION,GMT_CREATE,GMT_MODIFIED from fin_influx_extension where INFLUX_ID = '0001';
17
INFLUX_ID EXTENSION1 EXTENSION2 REQ_EXTENSION RES_EXTENSION GMT_CREATE GMT_MODIFIED
18
0001 NULL NULL req_1 res_1 2012-12-20 12:00:00.000000 2013-01-20 13:00:00.000000
19
create table fin_influx_inst(INFLUX_ID varchar(64) primary key, INST_ID varchar(32), INST_SERIAL_NO varchar(32), INST_REF_NO varchar(32), RESP_AMOUNT int, RESP_CURRENCY char(3), INST_RESULT_CODE varchar(16), INST_RESULT_DESCRIPTION varchar(256), GMT_SETTLE datetime(6), GMT_RESP datetime(6), GMT_CREATE datetime(6), GMT_MODIFIED datetime(6));
20
insert into fin_influx_inst values('0001', '00001', 'serial_no', 'ref_no', 100, '123', 'result_code', 'result_des', '2013-1-20 13:00','2013-1-20 13:00','2013-1-20 13:00','2013-1-20 13:00');21
SELECT INFLUX_ID, INST_ID, INST_SERIAL_NO, INST_REF_NO, RESP_AMOUNT, RESP_CURRENCY, INST_RESULT_CODE, INST_RESULT_DESCRIPTION, GMT_SETTLE, GMT_RESP, GMT_CREATE, GMT_MODIFIED FROM fin_influx_inst WHERE INFLUX_ID = '0001';
22
INFLUX_ID INST_ID INST_SERIAL_NO INST_REF_NO RESP_AMOUNT RESP_CURRENCY INST_RESULT_CODE INST_RESULT_DESCRIPTION GMT_SETTLE GMT_RESP GMT_CREATE GMT_MODIFIED
23
0001 00001 serial_no ref_no 100 123 result_code result_des 2013-01-20 13:00:00.000000 2013-01-20 13:00:00.000000 2013-01-20 13:00:00.000000 2013-01-20 13:00:00.000000
24
create table fin_settle_serial_map(INFLUX_ID varchar(64) primary key, SETTLE_SERIAL_NO varchar(64), INST_ID varchar(32), FINANCE_EXCHANGE_CODE varchar(32), GMT_CREATE datetime(6), GMT_MODIFIED datetime(6));
25
SELECT FINANCE_EXCHANGE_CODE, SETTLE_SERIAL_NO, INST_ID, INFLUX_ID, GMT_CREATE, GMT_MODIFIED FROM fin_settle_serial_map WHERE SETTLE_SERIAL_NO = '0001' AND FINANCE_EXCHANGE_CODE = '0001';
26
FINANCE_EXCHANGE_CODE SETTLE_SERIAL_NO INST_ID INFLUX_ID GMT_CREATE GMT_MODIFIED
27
SELECT FINANCE_EXCHANGE_CODE, SETTLE_SERIAL_NO, INST_ID, INFLUX_ID, GMT_CREATE, GMT_MODIFIED FROM fin_settle_serial_map WHERE SETTLE_SERIAL_NO = '0001';
28
FINANCE_EXCHANGE_CODE SETTLE_SERIAL_NO INST_ID INFLUX_ID GMT_CREATE GMT_MODIFIED
29
SELECT INFLUX_ID FROM fin_settle_serial_map WHERE FINANCE_EXCHANGE_CODE IN ('EXCHANGECODES') AND SETTLE_SERIAL_NO = '0001';31
create table fin_influx_terminal(influx_id varchar(64) primary key, inst_id varchar(32), inst_merchant_no varchar(32), inst_terminal_no varchar(32), term_batch_no varchar(6), term_trace_no varchar(6), rrn varchar(12), auth_code varchar(6), gmt_create datetime, gmt_modified datetime, finance_exchange_code varchar(32), inst_account_no varchar(32), exchange_amount int, exchange_currency char(3), extension varchar(4000));
32
select influx_id, inst_id, inst_merchant_no, inst_terminal_no, term_batch_no, term_trace_no, rrn, auth_code, gmt_create, gmt_modified, finance_exchange_code, inst_account_no, exchange_amount, exchange_currency, extension from fin_influx_terminal where influx_id='0001' and term_trace_no = '123';
33
influx_id inst_id inst_merchant_no inst_terminal_no term_batch_no term_trace_no rrn auth_code gmt_create gmt_modified finance_exchange_code inst_account_no exchange_amount exchange_currency extension
34
create table fin_info_transaction(
35
info_id varchar(64) primary key,
37
business_code varchar(16),
38
sub_business_code varchar(16),
39
exchange_type varchar(32),
40
finance_exchange_code varchar(32),
41
exchange_status varchar(8),
42
request_info varchar(4000),
43
response_info varchar(4000),
48
result_code varchar(16),
49
result_description varchar(256),
50
inst_result_code varchar(16),
51
inst_result_description varchar(256)
53
create table fin_influx_terminal_unique(
55
inst_merchant_no varchar(32),
56
inst_terminal_no varchar(32),
57
term_batch_no varchar(6),
58
term_trace_no varchar(6),
61
primary key(inst_id, term_batch_no, inst_merchant_no, term_trace_no, inst_terminal_no)
63
create table fin_request_no_unique(
64
request_identify varchar(32),
65
request_biz_no varchar(64),
69
primary key(request_identify, request_biz_no)
71
create table fin_settle_serial_unique(
72
finance_exchange_code varchar(32),
73
settle_serial_no varchar(64),
76
primary key(finance_exchange_code, settle_serial_no)
78
create table fin_influx_transaction(
79
influx_id varchar(64) primary key,
80
org_influx_id varchar(64),
82
business_code varchar(16),
83
sub_business_code varchar(16),
84
exchange_type varchar(32),
85
finance_exchange_code varchar(32),
86
settle_serial_no varchar(64),
87
payer_account_no varchar(32),
89
exchange_currency char(3),
91
account_currency char(3),
93
settle_currency char(3),
94
settle_status varchar(8),
95
exchange_status varchar(8),
96
result_code varchar(16),
97
result_description varchar(256),
100
negative_flag char(1),
101
negative_exchange_type varchar(16),
102
request_identify varchar(32),
103
request_biz_no varchar(64),
104
pay_unique_no varchar(64),
105
pay_channel_api varchar(32),
106
inst_channel_api varchar(32),
107
clear_channel varchar(32),
108
biz_identity varchar(32),
115
select influx_id, org_influx_id, inst_id, business_code, sub_business_code, exchange_type, finance_exchange_code, settle_serial_no, payer_account_no, exchange_amount, exchange_currency, account_amount, account_currency, settle_amount, settle_currency, settle_status, exchange_status, result_code, result_description, recover_flag, recon_flag, negative_flag, negative_exchange_type, request_identify, request_biz_no, pay_unique_no, pay_channel_api, inst_channel_api, clear_channel, biz_identity, gmt_submit, gmt_resp, gmt_settle, gmt_create, gmt_modified from fin_influx_transaction where influx_id = '0001';
116
influx_id org_influx_id inst_id business_code sub_business_code exchange_type finance_exchange_code settle_serial_no payer_account_no exchange_amount exchange_currency account_amount account_currency settle_amount settle_currency settle_status exchange_status result_code result_description recover_flag recon_flag negative_flag negative_exchange_type request_identify request_biz_no pay_unique_no pay_channel_api inst_channel_api clear_channel biz_identity gmt_submit gmt_resp gmt_settle gmt_create gmt_modified
117
select influx_id, org_influx_id, inst_id, business_code, sub_business_code, exchange_type, finance_exchange_code, settle_serial_no, payer_account_no, exchange_amount, exchange_currency, account_amount, account_currency, settle_amount, settle_currency, settle_status, exchange_status, result_code, result_description, recover_flag, recon_flag, negative_flag, negative_exchange_type, request_identify, request_biz_no, pay_unique_no, pay_channel_api, inst_channel_api, clear_channel, biz_identity, gmt_submit, gmt_resp, gmt_settle, gmt_create, gmt_modified from fin_influx_transaction where finance_exchange_code = 'ss' and settle_serial_no = '00001';
118
influx_id org_influx_id inst_id business_code sub_business_code exchange_type finance_exchange_code settle_serial_no payer_account_no exchange_amount exchange_currency account_amount account_currency settle_amount settle_currency settle_status exchange_status result_code result_description recover_flag recon_flag negative_flag negative_exchange_type request_identify request_biz_no pay_unique_no pay_channel_api inst_channel_api clear_channel biz_identity gmt_submit gmt_resp gmt_settle gmt_create gmt_modified
119
select influx_id, org_influx_id, inst_id, business_code, sub_business_code, exchange_type, finance_exchange_code, settle_serial_no, payer_account_no, exchange_amount, exchange_currency, account_amount, account_currency, settle_amount, settle_currency, settle_status, exchange_status, result_code, result_description, recover_flag, recon_flag, negative_flag, negative_exchange_type, request_identify, request_biz_no, pay_unique_no, pay_channel_api, inst_channel_api, clear_channel, biz_identity, gmt_submit, gmt_resp, gmt_settle, gmt_create, gmt_modified from fin_influx_transaction where inst_channel_api = 'api' and settle_serial_no = '00001';
120
influx_id org_influx_id inst_id business_code sub_business_code exchange_type finance_exchange_code settle_serial_no payer_account_no exchange_amount exchange_currency account_amount account_currency settle_amount settle_currency settle_status exchange_status result_code result_description recover_flag recon_flag negative_flag negative_exchange_type request_identify request_biz_no pay_unique_no pay_channel_api inst_channel_api clear_channel biz_identity gmt_submit gmt_resp gmt_settle gmt_create gmt_modified
121
create table idx1_fin_influx_transaction(
122
settle_serial_no varchar(64),
123
finance_exchange_code varchar(32),
124
influx_id varchar(64),
125
inst_channel_api varchar(32),
126
primary key(settle_serial_no,finance_exchange_code,influx_id)
128
select * from idx1_fin_influx_transaction where finance_exchange_code = 'sss' and settle_serial_no = 'ss';
129
settle_serial_no finance_exchange_code influx_id inst_channel_api
130
select * from idx1_fin_influx_transaction where inst_channel_api = 'ss' and settle_serial_no = 'ss';
131
settle_serial_no finance_exchange_code influx_id inst_channel_api
132
create table fin_influx_payer(
133
influx_id varchar(64) ,
134
payer_account_no varchar(32),
135
payer_name varchar(32),
136
inst_account_no varchar(32),
137
inst_account_name varchar(128),
138
card_type varchar(16),
139
card_index varchar(32),
141
agreement_no varchar(100),
142
certificate_type varchar(64),
143
certificate_no varchar(32),
144
mobile_phone varchar(16),
149
gmt_modified datetime,
150
primary key(inst_account_no, gmt_create, influx_id)
152
select influx_id, payer_account_no, payer_name, inst_account_no, inst_account_name, card_type, card_index, issuer, agreement_no, certificate_type, certificate_no, mobile_phone, pay_tool, bill_no, bill_type, gmt_create, gmt_modified from fin_influx_payer where influx_id='0001';
153
influx_id payer_account_no payer_name inst_account_no inst_account_name card_type card_index issuer agreement_no certificate_type certificate_no mobile_phone pay_tool bill_no bill_type gmt_create gmt_modified
154
select influx_id, payer_account_no, payer_name, inst_account_no, inst_account_name, card_type, card_index, issuer, agreement_no, certificate_type, certificate_no, mobile_phone, pay_tool, bill_no, bill_type, gmt_create, gmt_modified from fin_influx_payer where bill_no='sss';
155
influx_id payer_account_no payer_name inst_account_no inst_account_name card_type card_index issuer agreement_no certificate_type certificate_no mobile_phone pay_tool bill_no bill_type gmt_create gmt_modified
156
select influx_id, payer_account_no, payer_name, inst_account_no, inst_account_name, card_type, card_index, issuer, agreement_no, certificate_type, certificate_no, mobile_phone, pay_tool, bill_no, bill_type, gmt_create, gmt_modified from fin_influx_payer where inst_account_no='0001' and gmt_create>='2012-10-1 23:00:00' and gmt_create<='2012-12-23 23:00:00';
157
influx_id payer_account_no payer_name inst_account_no inst_account_name card_type card_index issuer agreement_no certificate_type certificate_no mobile_phone pay_tool bill_no bill_type gmt_create gmt_modified
158
create table idx1_fin_influx_payer (
160
inst_account_no varchar(32),
162
influx_id varchar(64),
164
primary key(bill_no,inst_account_no,gmt_create,influx_id)
166
create table idx2_fin_influx_payer(
167
influx_id varchar(64),
168
inst_account_no varchar(32),
172
primary key(influx_id,inst_account_no,gmt_create,virtual_col1)
174
drop table fin_influx_extension;
175
drop table fin_influx_inst;
176
drop table fin_settle_serial_map;
177
drop table fin_influx_terminal;
178
drop table fin_info_transaction;
179
drop table fin_influx_terminal_unique;
180
drop table fin_request_no_unique;
181
drop table fin_settle_serial_unique;
182
drop table fin_influx_transaction;
183
drop table idx1_fin_influx_transaction;
184
drop table fin_influx_payer;
185
drop table idx1_fin_influx_payer;
186
drop table idx2_fin_influx_payer;