555 lines
28 KiB
SQL
555 lines
28 KiB
SQL
# CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
|
|
SET NAMES utf8mb4;
|
|
SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
|
|
SET time_zone = "+00:00";
|
|
|
|
DROP TABLE IF EXISTS t_title_daily;
|
|
CREATE TABLE t_title_daily
|
|
(
|
|
id INT(15) NOT NULL,
|
|
account_date CHAR(8) NOT NULL COMMENT '会计日',
|
|
title_code VARCHAR(16) NOT NULL COMMENT '科目代码',
|
|
balance_direction CHAR(1) NOT NULL COMMENT '余额方向',
|
|
debit_amount DECIMAL(19, 4) COMMENT '借方发生额',
|
|
credit_amount DECIMAL(19, 4) COMMENT '贷方发生额',
|
|
debit_count INT(15) COMMENT '借方发生笔数',
|
|
credit_count INT(15) COMMENT '贷方发生笔数',
|
|
debit_balance DECIMAL(19, 4) COMMENT '借方余额',
|
|
credit_balance DECIMAL(19, 4) COMMENT '贷方余额',
|
|
initial_debit_balance DECIMAL(19, 4),
|
|
initial_credit_balance DECIMAL(19, 4),
|
|
currency INT(3) NOT NULL COMMENT '币种',
|
|
gmt_create TIMESTAMP(6) NOT NULL COMMENT '创建时间',
|
|
gmt_modified TIMESTAMP(6) COMMENT '最后修改时间',
|
|
PRIMARY KEY (id),
|
|
INDEX IDX_TD_ACCOUNT_DATE (account_date),
|
|
UNIQUE INDEX U_TITLE_DAILY_DATE (title_code, account_date)
|
|
) ENGINE = InnoDB
|
|
DEFAULT CHARSET = utf8
|
|
ROW_FORMAT = COMPACT COMMENT ='科目每日信息';
|
|
|
|
|
|
DROP TABLE IF EXISTS t_title_daily_temp;
|
|
CREATE TABLE t_title_daily_temp
|
|
(
|
|
id BIGINT(15) NOT NULL COMMENT 'ID',
|
|
account_date CHAR(8) NOT NULL COMMENT '会计日',
|
|
title_code VARCHAR(16) NOT NULL COMMENT '科目代码',
|
|
balance_direction CHAR(1) NOT NULL COMMENT '余额方向',
|
|
debit_amount DECIMAL(19, 4) COMMENT '借方发生额',
|
|
credit_amount DECIMAL(19, 4) COMMENT '贷方发生额',
|
|
debit_count BIGINT(15) COMMENT '借方发生笔数',
|
|
credit_count BIGINT(15) COMMENT '贷方发生笔数',
|
|
debit_balance DECIMAL(19, 4) COMMENT '借方余额',
|
|
credit_balance DECIMAL(19, 4) COMMENT '贷方余额',
|
|
currency INT(3) NOT NULL COMMENT '币种',
|
|
gmt_create TIMESTAMP(6) NOT NULL COMMENT '创建时间',
|
|
gmt_modified TIMESTAMP(6) COMMENT '最后修改时间',
|
|
PRIMARY KEY (id),
|
|
INDEX IDX_TD_ACCOUNT_DATE_TEMP (account_date),
|
|
UNIQUE INDEX U_TITLE_DAILY_DATE_TEMP (title_code, account_date)
|
|
) ENGINE = InnoDB
|
|
DEFAULT CHARSET = utf8
|
|
ROW_FORMAT = COMPACT COMMENT ='科目每日信息-备份';
|
|
|
|
|
|
DROP TABLE IF EXISTS temp_trg_log;
|
|
CREATE TABLE temp_trg_log
|
|
(
|
|
account_no VARCHAR(100) COMMENT '账户号码',
|
|
addbalance DECIMAL(19, 4) COMMENT '增加的余额',
|
|
createtime DATE COMMENT '创建时间',
|
|
subbalance DECIMAL(19, 4) COMMENT '减少的余额',
|
|
mainbalance DECIMAL(19, 4) COMMENT '主余额'
|
|
) ENGINE = InnoDB
|
|
DEFAULT CHARSET = utf8
|
|
ROW_FORMAT = COMPACT COMMENT ='临时触发器日志表';
|
|
ALTER TABLE temp_trg_log
|
|
ADD INDEX idx_account_no (account_no);
|
|
|
|
DROP TABLE IF EXISTS t_account_title;
|
|
CREATE TABLE t_account_title
|
|
(
|
|
title_seq_no VARCHAR(16) NOT NULL COMMENT '科目序号',
|
|
title_code VARCHAR(16) NOT NULL COMMENT '科目代码',
|
|
title_name VARCHAR(64) NOT NULL COMMENT '科目名称',
|
|
title_level TINYINT COMMENT '科目级别',
|
|
parent_title_code VARCHAR(16) COMMENT '上级科目代码',
|
|
is_leaf CHAR(1) COMMENT '是否叶子节点，Y: 是 N: 否',
|
|
type CHAR(1) NOT NULL COMMENT '类型：1（资产类）；2（负债类）；3(所有者权益)；4（共同类）5(损益类)',
|
|
balance_direction TINYINT NOT NULL COMMENT '余额方向：1:借 2:贷 0:双向',
|
|
status CHAR(1) NOT NULL COMMENT '状态：Y（有效）；N（无效）',
|
|
gmt_create TIMESTAMP(6) NOT NULL COMMENT '创建时间',
|
|
gmt_modified TIMESTAMP(6) NOT NULL COMMENT '最后修改时间',
|
|
memo VARCHAR(128) COMMENT '备注',
|
|
title_range TINYINT NOT NULL COMMENT '1.内部科目;2,外部科目',
|
|
PRIMARY KEY (title_seq_no),
|
|
UNIQUE KEY AK_KEY_TITLE_CODE (title_code)
|
|
) ENGINE = InnoDB
|
|
DEFAULT CHARSET = utf8
|
|
ROW_FORMAT = COMPACT COMMENT ='会计科目表';
|
|
|
|
|
|
DROP TABLE IF EXISTS t_account_title_temp;
|
|
CREATE TABLE t_account_title_temp
|
|
(
|
|
title_seq_no VARCHAR(16) NOT NULL COMMENT '科目序号',
|
|
title_code VARCHAR(16) NOT NULL COMMENT '科目代码',
|
|
title_name VARCHAR(64) NOT NULL COMMENT '科目名称',
|
|
title_level INT(2) COMMENT '科目级别',
|
|
parent_title_code VARCHAR(16) COMMENT '上级科目代码',
|
|
is_leaf CHAR(1) COMMENT 'Y: 是 N: 否',
|
|
type CHAR(1) NOT NULL COMMENT '类型：1（资产类）；2（负债类）；4（共同类）',
|
|
balance_direction INT(1) NOT NULL COMMENT '余额方向：1:借 2:贷 0:双向',
|
|
status CHAR(1) NOT NULL COMMENT '状态：Y（有效）；N（无效）',
|
|
gmt_create TIMESTAMP(6) NOT NULL COMMENT '创建时间',
|
|
gmt_modified TIMESTAMP(6) NOT NULL COMMENT '最后修改时间',
|
|
memo VARCHAR(128) COMMENT '备注',
|
|
title_range INT(1) COMMENT '1.内部科目;2,外部科目',
|
|
PRIMARY KEY (title_seq_no)
|
|
) ENGINE = InnoDB
|
|
DEFAULT CHARSET = utf8
|
|
ROW_FORMAT = COMPACT COMMENT ='会计科目表-备份';
|
|
|
|
DROP TABLE IF EXISTS t_account_title_temp_temp;
|
|
CREATE TABLE IF NOT EXISTS t_account_title_temp_temp
|
|
(
|
|
title_seq_no VARCHAR(16) NOT NULL COMMENT '标题序列号',
|
|
title_code VARCHAR(16) NOT NULL COMMENT '标题编码',
|
|
title_name VARCHAR(64) NOT NULL COMMENT '标题名称',
|
|
title_level INT(2) COMMENT '标题级别',
|
|
parent_title_code VARCHAR(16) COMMENT '父级标题编码',
|
|
is_leaf CHAR(1) COMMENT '是否是叶子节点',
|
|
type CHAR(1) NOT NULL COMMENT '类型',
|
|
balance_direction INT(1) NOT NULL COMMENT '余额方向',
|
|
status CHAR(1) NOT NULL COMMENT '状态',
|
|
gmt_create TIMESTAMP(6) NOT NULL COMMENT '创建时间',
|
|
gmt_modified TIMESTAMP(6) NOT NULL COMMENT '修改时间',
|
|
memo VARCHAR(128) COMMENT '备注'
|
|
) ENGINE = InnoDB
|
|
DEFAULT CHARSET = utf8
|
|
ROW_FORMAT = COMPACT COMMENT ='临时账户标题表';
|
|
ALTER TABLE t_account_title_temp_temp
|
|
ADD CONSTRAINT pk_t_account_title_temp_t PRIMARY KEY (title_seq_no);
|
|
|
|
DROP TABLE IF EXISTS t_account_crl_def;
|
|
CREATE TABLE t_account_crl_def
|
|
(
|
|
account_title_id VARCHAR(32) COMMENT '账户标题ID',
|
|
account_attribute TINYINT(1) COMMENT '账户属性：1-对私，2-对公，9-内部',
|
|
account_type SMALLINT(4) COMMENT '账户类型：1-基本户，2-一般户，3-专用户，4-临时户，5-保证金户',
|
|
currency_code VARCHAR(3) COMMENT '货币代码',
|
|
bal_direction TINYINT(1) COMMENT '余额方向：1-借，2-贷，0-双向',
|
|
status TINYINT(1) COMMENT '状态：0-有效，1-无效',
|
|
remark VARCHAR(200) COMMENT '备注',
|
|
input_uid VARCHAR(32) COMMENT '录入人ID',
|
|
input_time TIMESTAMP(6) COMMENT '录入时间',
|
|
check_uid VARCHAR(32) COMMENT '审核人ID',
|
|
check_time TIMESTAMP(6) COMMENT '审核时间'
|
|
) ENGINE = InnoDB
|
|
DEFAULT CHARSET = utf8
|
|
ROW_FORMAT = COMPACT COMMENT ='DPM账户控制表';
|
|
CREATE UNIQUE INDEX ak_uk_tdacd_account_type ON t_account_crl_def (account_type, currency_code);
|
|
|
|
DROP TABLE IF EXISTS t_account_entry;
|
|
CREATE TABLE t_account_entry
|
|
(
|
|
entry_seq_no VARCHAR(32) not null,
|
|
transaction_no VARCHAR(32) not null,
|
|
suite_no VARCHAR(32) not null,
|
|
voucher_no VARCHAR(50),
|
|
sys_trace_no VARCHAR(32),
|
|
account_no VARCHAR(32) not null,
|
|
title_no VARCHAR(32),
|
|
entry_type TINYINT(1) comment '0:表内分录 1:表外分录',
|
|
is_manual TINYINT(1) comment '自动手工标志 0:自动 1:手工',
|
|
txn_currency VARCHAR(3),
|
|
amount DECIMAL(19, 4) not null,
|
|
txn_type TINYINT(1) not null comment '0-正常 1-红字 2-蓝字 9-抹帐 ',
|
|
direction TINYINT(1) not null comment '1:借 2:贷',
|
|
audit_status TINYINT(1) not null comment '0-未复核 1-已复核 2-不需事中复核 ',
|
|
status TINYINT(1) comment '0-未入账 2-已入账 3-已抹帐 9-无效',
|
|
accounting_date VARCHAR(8) not null,
|
|
create_time TIMESTAMP not null,
|
|
last_update_time TIMESTAMP,
|
|
bal_update_time TIMESTAMP,
|
|
remark VARCHAR(256),
|
|
is_buffer TINYINT(1) comment '0:未缓冲 1:缓冲',
|
|
old_entry_seq_no VARCHAR(32),
|
|
summary VARCHAR(256),
|
|
input_uid VARCHAR(32),
|
|
input_time TIMESTAMP,
|
|
check_uid VARCHAR(32),
|
|
check_time TIMESTAMP,
|
|
rsvd_amt1 DECIMAL(19, 4) default 0,
|
|
rsvd_amt2 DECIMAL(19, 4) default 0,
|
|
rsvd_text1 VARCHAR(50),
|
|
rsvd_text2 VARCHAR(50),
|
|
context_voucher_no VARCHAR(50) comment '关联结算流水号',
|
|
operater TINYINT(1),
|
|
clearing_code VARCHAR(16) comment '清算编码'
|
|
) ENGINE = InnoDB
|
|
DEFAULT CHARSET = utf8
|
|
ROW_FORMAT = COMPACT;
|
|
|
|
CREATE INDEX IDX_TD_ACCOUNTING_DATE ON t_account_entry (accounting_date);
|
|
CREATE INDEX I_ENTRY_ACCOUNTNO ON t_account_entry (account_no);
|
|
CREATE INDEX I_ENTRY_CREATE_TIME ON t_account_entry (create_time);
|
|
CREATE INDEX I_ENTRY_SUITENO ON t_account_entry (suite_no);
|
|
CREATE INDEX I_ENTRY_TRANSACTIONNO ON t_account_entry (transaction_no);
|
|
ALTER TABLE t_account_entry
|
|
ADD CONSTRAINT pk_t_account_entry PRIMARY KEY (entry_seq_no);
|
|
ALTER TABLE t_account_entry
|
|
ADD CONSTRAINT uk_entry_voucher_no UNIQUE (voucher_no);
|
|
|
|
|
|
|
|
DROP TABLE IF EXISTS `t_account_entry_temp`;
|
|
CREATE TABLE `t_account_entry_temp`
|
|
(
|
|
`entry_seq_no` varchar(32) NOT NULL,
|
|
`transaction_no` varchar(32) NOT NULL,
|
|
`suite_no` varchar(32) NOT NULL,
|
|
`voucher_no` varchar(50),
|
|
`sys_trace_no` varchar(32),
|
|
`account_no` varchar(32) NOT NULL,
|
|
`title_no` varchar(32),
|
|
`entry_type` tinyint(1) NOT NULL COMMENT '0:表内分录 1:表外分录',
|
|
`is_manual` tinyint(1) NOT NULL COMMENT '自动手工标志0:自动1:手工',
|
|
`txn_currency` varchar(3),
|
|
`amount` decimal(19, 4) NOT NULL,
|
|
`txn_type` tinyint(1) NOT NULL COMMENT '0-正常1-红字2-蓝字9-抹帐',
|
|
`direction` tinyint(1) NOT NULL COMMENT '1:借2:贷',
|
|
`audit_status` tinyint(1) NOT NULL COMMENT '0-未复核1-已复核2-不需事中复核',
|
|
`status` tinyint(1) COMMENT '0-未入账 2-已入账3-已抹帐9-无效',
|
|
`accounting_date` varchar(8) NOT NULL,
|
|
`create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
`last_update_time` timestamp,
|
|
`bal_update_time` timestamp,
|
|
`remark` varchar(256),
|
|
`is_buffer` tinyint(1) COMMENT '0:未缓冲1:缓冲',
|
|
`old_entry_seq_no` varchar(32),
|
|
`summary` varchar(256),
|
|
`input_uid` varchar(32),
|
|
`input_time` timestamp,
|
|
`check_uid` varchar(32),
|
|
`check_time` timestamp,
|
|
`rsvd_amt1` decimal(19, 4) DEFAULT 0,
|
|
`rsvd_amt2` decimal(19, 4) DEFAULT 0,
|
|
`rsvd_text1` varchar(50),
|
|
`rsvd_text2` varchar(50),
|
|
`context_voucher_no` varchar(50) COMMENT '关联结算流水号',
|
|
`operater` tinyint(1),
|
|
`clearing_code` varchar(16) COMMENT '清算编码'
|
|
) ENGINE = InnoDB
|
|
DEFAULT CHARSET = utf8
|
|
ROW_FORMAT = COMPACT;
|
|
ALTER TABLE `t_account_entry_temp`
|
|
ADD CONSTRAINT `pk_t_account_entry_temp` PRIMARY KEY (`ENTRY_SEQ_NO`);
|
|
|
|
DROP TABLE IF EXISTS t_buffer_detail;
|
|
CREATE TABLE t_buffer_detail
|
|
(
|
|
buffer_detail_id INT(32) NOT NULL COMMENT '待入账数据流水号',
|
|
account_no VARCHAR(32) NOT NULL COMMENT '账户号',
|
|
sys_trace_no VARCHAR(32) COMMENT '系统跟踪号',
|
|
voucher_no VARCHAR(50) NOT NULL COMMENT '凭证号',
|
|
transaction_no VARCHAR(32) NOT NULL COMMENT '交易号',
|
|
amount DECIMAL(19, 4) NOT NULL COMMENT '金额',
|
|
crdr INT(1) NOT NULL COMMENT '借贷方向 1:借 2:贷',
|
|
txn_type INT(1) NOT NULL COMMENT '交易类型 0:正常 1:红字 2:蓝字 9:抹帐',
|
|
accounting_rule VARCHAR(16) NOT NULL COMMENT '分户执行顺序 0.先贷后借(默认) 1.借记 2.贷记',
|
|
product_code VARCHAR(12) COMMENT 'PE产品编码',
|
|
pay_code VARCHAR(12) COMMENT 'PE支付编码',
|
|
status INT(1) NOT NULL COMMENT '状态 0.待入账 1.成功(不会存在,成功直接删除数据) 2.失败 3.处理中',
|
|
count INT(1) NOT NULL COMMENT '执行次数',
|
|
summary VARCHAR(256) COMMENT '摘要',
|
|
occupy_sign VARCHAR(32) COMMENT '时间戳',
|
|
remark VARCHAR(256) COMMENT '备注',
|
|
create_time TIMESTAMP(6) NOT NULL COMMENT '创建时间',
|
|
update_time TIMESTAMP(6) NOT NULL COMMENT '更新时间',
|
|
suite_no VARCHAR(32) NOT NULL COMMENT '套号',
|
|
context_voucher_no VARCHAR(32) COMMENT '关联凭证号',
|
|
accounting_date VARCHAR(8) COMMENT '会计日',
|
|
operation_type INT(1) COMMENT '操作类型',
|
|
balance_type INT(1) COMMENT '余额类型'
|
|
) ENGINE = InnoDB
|
|
DEFAULT CHARSET = utf8
|
|
ROW_FORMAT = COMPACT COMMENT ='待缓冲入账明细(缓冲池)';
|
|
ALTER TABLE t_buffer_detail
|
|
ADD CONSTRAINT pk_t_buffer_detail PRIMARY KEY (buffer_detail_id);
|
|
CREATE INDEX idx_bufferdetail_ct ON t_buffer_detail (create_time);
|
|
CREATE INDEX idx_dbd_os ON t_buffer_detail (occupy_sign);
|
|
|
|
DROP TABLE IF EXISTS t_buffer_detail_log;
|
|
CREATE TABLE t_buffer_detail_log
|
|
(
|
|
buffer_detail_id INT(32) UNSIGNED NOT NULL COMMENT '待入账数据流水号',
|
|
account_no VARCHAR(32) NOT NULL COMMENT '账户号',
|
|
sys_trace_no VARCHAR(32) COMMENT '系统跟踪号',
|
|
voucher_no VARCHAR(50) NOT NULL COMMENT '凭证号',
|
|
transaction_no VARCHAR(32) NOT NULL COMMENT '交易流水号',
|
|
amount DECIMAL(19, 4) NOT NULL COMMENT '金额',
|
|
crdr TINYINT(1) NOT NULL COMMENT '借贷方向\n1:借\n2:贷',
|
|
txn_type TINYINT(1) NOT NULL COMMENT '交易类型\n0:正常\n1:红字\n2:蓝字\n9:抹帐',
|
|
accounting_rule VARCHAR(16) NOT NULL COMMENT '分户执行顺序\n0.先贷后借(默认)\n1.借记\n2.贷记',
|
|
product_code VARCHAR(12) COMMENT 'PE产品编码',
|
|
pay_code VARCHAR(12) COMMENT 'PE支付编码',
|
|
summary VARCHAR(256) COMMENT '摘要',
|
|
remark VARCHAR(256) COMMENT '备注',
|
|
create_time TIMESTAMP(6) NOT NULL COMMENT '创建时间',
|
|
update_time TIMESTAMP(6) NOT NULL COMMENT '更新时间',
|
|
suite_no VARCHAR(32) NOT NULL COMMENT '套号',
|
|
context_voucher_no VARCHAR(32) COMMENT '关联凭证号',
|
|
accounting_date VARCHAR(8) COMMENT '会计日',
|
|
operation_type TINYINT(1) COMMENT '操作类型',
|
|
balance_type TINYINT(1) COMMENT '余额类型',
|
|
PRIMARY KEY (buffer_detail_id)
|
|
) ENGINE = InnoDB
|
|
DEFAULT CHARSET = utf8
|
|
ROW_FORMAT = COMPACT COMMENT ='待缓冲入账明细(缓冲池)日志';
|
|
|
|
DROP TABLE IF EXISTS t_buffer_rule;
|
|
CREATE TABLE t_buffer_rule
|
|
(
|
|
buffer_rule_id INT(11) NOT NULL,
|
|
product_code VARCHAR(12) NOT NULL COMMENT '产品编码',
|
|
pay_code VARCHAR(12) NOT NULL COMMENT 'PE支付编码',
|
|
account_no VARCHAR(32) COMMENT '账户号',
|
|
status INT(1) NOT NULL COMMENT '0.有效 1.无效(已删除)',
|
|
crdr INT(1) NOT NULL COMMENT '0:双向 1:借 2:贷',
|
|
remark VARCHAR(256) COMMENT '备注',
|
|
create_time TIMESTAMP(6) NOT NULL COMMENT '创建时间',
|
|
update_time TIMESTAMP(6) NOT NULL COMMENT '更新时间',
|
|
valid_time TIMESTAMP(6) NOT NULL COMMENT '生效时间',
|
|
unvalid_time TIMESTAMP(6) NOT NULL COMMENT '失效时间'
|
|
) ENGINE = InnoDB
|
|
DEFAULT CHARSET = utf8
|
|
ROW_FORMAT = COMPACT COMMENT ='缓冲入账规则表';
|
|
ALTER TABLE t_buffer_rule
|
|
ADD CONSTRAINT pk_t_buffer_rule PRIMARY KEY (buffer_rule_id);
|
|
CREATE UNIQUE INDEX uidx_buffer_rule ON t_buffer_rule (product_code, pay_code, account_no, crdr);
|
|
|
|
DROP TABLE IF EXISTS t_inner_account;
|
|
CREATE TABLE t_inner_account
|
|
(
|
|
account_no VARCHAR(32) NOT NULL COMMENT '帐号号',
|
|
account_title_no VARCHAR(32) COMMENT '科目号',
|
|
account_name VARCHAR(64) COMMENT '账户名称',
|
|
open_date DATE COMMENT '开户日期(创建时间)',
|
|
bal_direction TINYINT(1) COMMENT '1:借 2:贷 0:双向',
|
|
curr_bal_direction TINYINT(1) COMMENT '1:借 2:贷',
|
|
currency_code VARCHAR(3) COMMENT '货币类型',
|
|
balance DECIMAL(19, 4) DEFAULT 0 COMMENT '余额',
|
|
last_update_time TIMESTAMP(6) COMMENT '最后更新时间',
|
|
PRIMARY KEY (account_no)
|
|
) ENGINE = InnoDB
|
|
DEFAULT CHARSET = utf8
|
|
ROW_FORMAT = COMPACT;
|
|
|
|
|
|
DROP TABLE IF EXISTS t_inner_account_detail;
|
|
CREATE TABLE t_inner_account_detail
|
|
(
|
|
txn_seq_no VARCHAR(32) NOT NULL COMMENT '明细流水号',
|
|
sys_trace_no VARCHAR(32) COMMENT '系统跟踪号',
|
|
accounting_date VARCHAR(8) COMMENT '会计日',
|
|
txn_time TIMESTAMP(6) NOT NULL COMMENT '交易时间',
|
|
account_no VARCHAR(32) COMMENT '账户号',
|
|
txn_type TINYINT COMMENT '0:正常 1:红字 2:蓝字 9:抹帐 ',
|
|
txn_dscpt VARCHAR(256) COMMENT '摘要',
|
|
change_type TINYINT COMMENT '1: 借贷 2: 冻结解冻',
|
|
direction VARCHAR(1) COMMENT '1:加(收入) 2:减(支出)',
|
|
txn_amt DECIMAL(19, 4) COMMENT '交易金额',
|
|
before_amt DECIMAL(19, 4) COMMENT '交易前余额',
|
|
after_amt DECIMAL(19, 4) COMMENT '交易后余额',
|
|
entry_seq_no VARCHAR(32),
|
|
other_account_no VARCHAR(32),
|
|
old_txn_seq_no VARCHAR(32),
|
|
remark VARCHAR(256) COMMENT '备注',
|
|
crdr TINYINT COMMENT '借贷标志 1借2贷',
|
|
product_code VARCHAR(12) COMMENT 'PE产品编码',
|
|
pay_code VARCHAR(12) COMMENT 'PE支付编码',
|
|
operation_type TINYINT COMMENT '交易类型',
|
|
delete_sign TINYINT COMMENT '删除标记(冲正用)',
|
|
suite_no VARCHAR(32) COMMENT '套号',
|
|
context_voucher_no VARCHAR(32) COMMENT '关联结算流水号',
|
|
transaction_no VARCHAR(32),
|
|
voucher_no VARCHAR(50),
|
|
PRIMARY KEY (txn_seq_no),
|
|
UNIQUE KEY uidx_iad_vo (voucher_no),
|
|
KEY ak_inner_key_account_no (account_no),
|
|
KEY idx_diad_stn (sys_trace_no),
|
|
KEY idx_diad_txn_time (txn_time)
|
|
) ENGINE = InnoDB
|
|
DEFAULT CHARSET = utf8
|
|
ROW_FORMAT = COMPACT COMMENT ='内部账户余额明细';
|
|
|
|
|
|
DROP TABLE IF EXISTS t_outer_account_diary;
|
|
CREATE TABLE t_outer_account_diary
|
|
(
|
|
account_no VARCHAR(32) NOT NULL COMMENT '账户号码',
|
|
account_date VARCHAR(8) NOT NULL COMMENT '账户日期',
|
|
begin_dr_bal DECIMAL(19, 4) DEFAULT 0 COMMENT '期初借方余额',
|
|
begin_cr_bal DECIMAL(19, 4) DEFAULT 0 COMMENT '期初贷方余额',
|
|
begin_dr_avail_bal DECIMAL(19, 4) DEFAULT 0 COMMENT '期初可用借方余额',
|
|
begin_cr_avail_bal DECIMAL(19, 4) DEFAULT 0 COMMENT '期初可用贷方余额',
|
|
debit_amt DECIMAL(19, 4) DEFAULT 0 COMMENT '借方发生额',
|
|
credit_amt DECIMAL(19, 4) DEFAULT 0 COMMENT '贷方发生额',
|
|
debit_cnt INT(10) DEFAULT 0 COMMENT '借方发生笔数',
|
|
credit_cnt INT(10) DEFAULT 0 COMMENT '贷方发生笔数',
|
|
debit_frozen_amt DECIMAL(19, 4) DEFAULT 0 COMMENT '借方冻结金额',
|
|
credit_frozen_amt DECIMAL(19, 4) DEFAULT 0 COMMENT '贷方冻结金额',
|
|
debit_frozen_cnt INT(10) DEFAULT 0 COMMENT '借方冻结笔数',
|
|
credit_frozen_cnt INT(10) DEFAULT 0 COMMENT '贷方冻结笔数',
|
|
debit_unfrozen_amt DECIMAL(19, 4) DEFAULT 0 COMMENT '借方解冻金额',
|
|
credit_unfrozen_amt DECIMAL(19, 4) DEFAULT 0 COMMENT '贷方解冻金额',
|
|
debit_unfrozen_cnt INT(10) DEFAULT 0 COMMENT '借方解冻笔数',
|
|
credit_enfrozen_cnt INT(10) DEFAULT 0 COMMENT '贷方解冻笔数',
|
|
end_dr_bal DECIMAL(19, 4) DEFAULT 0 COMMENT '期末借方余额',
|
|
end_cr_bal DECIMAL(19, 4) DEFAULT 0 COMMENT '期末贷方余额',
|
|
end_dr_avail_bal DECIMAL(19, 4) DEFAULT 0 COMMENT '期末可用借方余额',
|
|
end_cr_avail_bal DECIMAL(19, 4) DEFAULT 0 COMMENT '期末可用贷方余额',
|
|
is_final INT(1) COMMENT '0:已经扎帐，1:未扎帐',
|
|
create_time TIMESTAMP(6) COMMENT '创建时间',
|
|
last_update_time TIMESTAMP(6) COMMENT '最后更新时间',
|
|
PRIMARY KEY (account_no, account_date)
|
|
) ENGINE = InnoDB
|
|
DEFAULT CHARSET = utf8
|
|
ROW_FORMAT = COMPACT;
|
|
|
|
|
|
DROP TABLE IF EXISTS t_outer_account_subset;
|
|
CREATE TABLE t_outer_account_subset
|
|
(
|
|
account_no VARCHAR(32) NOT NULL COMMENT '账户号码',
|
|
balance DECIMAL(19, 4) NOT NULL COMMENT '余额',
|
|
fund_type VARCHAR(16) NOT NULL COMMENT '资金类型：1-借记，2-贷记',
|
|
balance_type TINYINT(1) COMMENT '余额类型：1-普通，2-冻结',
|
|
remark VARCHAR(256) COMMENT '备注',
|
|
create_time TIMESTAMP(6) NOT NULL COMMENT '创建时间',
|
|
update_time TIMESTAMP(6) NOT NULL COMMENT '更新时间',
|
|
PRIMARY KEY (account_no, fund_type)
|
|
) ENGINE = InnoDB
|
|
DEFAULT CHARSET = utf8
|
|
ROW_FORMAT = COMPACT COMMENT ='外部账户分户表';
|
|
ALTER TABLE t_outer_account_subset
|
|
ADD CONSTRAINT UK_TDOAS_ACCOUNT_NO UNIQUE (account_no, fund_type);
|
|
|
|
DROP TABLE IF EXISTS t_pay_package;
|
|
CREATE TABLE t_pay_package
|
|
(
|
|
package_no VARCHAR(32) NOT NULL COMMENT '支付包编号',
|
|
app_id VARCHAR(10) COMMENT '应用ID',
|
|
accounting_model TINYINT(1) NOT NULL COMMENT '记账模式：0自动，1一个事务内完成，2逐笔完成',
|
|
asyn TINYINT(1) NOT NULL COMMENT '同步异步：0同步，1异步',
|
|
mac VARCHAR(32) COMMENT 'MAC地址',
|
|
total_cnt SMALLINT(6) COMMENT '总数量',
|
|
success_cnt SMALLINT(6) COMMENT '成功数量',
|
|
fail_cnt SMALLINT(6) COMMENT '失败数量',
|
|
status TINYINT(1) NOT NULL COMMENT '状态：0初始，1处理中，2入账完成(成功)，3入账失败，处理中，4入账完成(失败)',
|
|
accounting_desc VARCHAR(128) COMMENT '记账描述',
|
|
retry_cnt SMALLINT(6) COMMENT '重试次数',
|
|
notify_url VARCHAR(128) COMMENT '通知URL',
|
|
notify_status TINYINT(1) COMMENT '通知状态：0初始，1通知成功，2通知失败',
|
|
notify_desc VARCHAR(64) COMMENT '通知描述',
|
|
create_time TIMESTAMP(6) COMMENT '创建时间',
|
|
last_update_time TIMESTAMP(6) COMMENT '最后更新时间',
|
|
remark VARCHAR(256) COMMENT '备注'
|
|
) ENGINE = InnoDB
|
|
DEFAULT CHARSET = utf8
|
|
ROW_FORMAT = COMPACT COMMENT ='支付包表';
|
|
ALTER TABLE t_pay_package
|
|
ADD CONSTRAINT PK_t_pay_package PRIMARY KEY (package_no);
|
|
|
|
DROP TABLE IF EXISTS t_outer_account_sub_detail;
|
|
CREATE TABLE t_outer_account_sub_detail
|
|
(
|
|
account_subset_log_id int(32) NOT NULL,
|
|
account_no varchar(32) NOT NULL COMMENT '账户号',
|
|
fund_type varchar(16) NOT NULL COMMENT '资金类型 1:借记 2:贷记',
|
|
balance_type int(1) NOT NULL COMMENT '余额类型 1:普通 2:冻结',
|
|
sys_trace_no varchar(32) COMMENT '系统跟踪号',
|
|
voucher_no varchar(50) NOT NULL COMMENT '凭证号',
|
|
txn_amt decimal(19, 4) COMMENT '交易金额',
|
|
after_amt decimal(19, 4) COMMENT '交易后余额',
|
|
accounting_rule varchar(16) NOT NULL COMMENT '记账规则',
|
|
remark varchar(256) COMMENT '备注',
|
|
create_time timestamp(6) NOT NULL COMMENT '创建时间',
|
|
update_time timestamp(6) NOT NULL COMMENT '更新时间',
|
|
direction int(1) NOT NULL COMMENT '1:加(收入) 2:减(支出)',
|
|
PRIMARY KEY (account_subset_log_id)
|
|
) ENGINE = InnoDB
|
|
DEFAULT CHARSET = utf8
|
|
ROW_FORMAT = COMPACT COMMENT ='外部账户分户账户明细';
|
|
CREATE INDEX idx_account_sub_dtl_vouch ON t_outer_account_sub_detail (voucher_no) USING BTREE COMMENT '凭证号索引';
|
|
|
|
DROP TABLE IF EXISTS t_pay_transaction;
|
|
CREATE TABLE t_pay_transaction
|
|
(
|
|
transaction_id VARCHAR(32) NOT NULL COMMENT '事务ID',
|
|
package_no VARCHAR(32) COMMENT '包号，批量入帐时用',
|
|
transaction_no VARCHAR(32) NOT NULL COMMENT '事务流水号(PE支付流水号)',
|
|
app_id VARCHAR(10) COMMENT '应用ID',
|
|
sys_trace_no VARCHAR(32) COMMENT '入账请求流水号(CS清算ID)',
|
|
txn_type INT(1) NOT NULL COMMENT '0:正常 1:红字 2:蓝字 9:抹帐',
|
|
product_code VARCHAR(12) COMMENT 'PE产品编码',
|
|
pay_code VARCHAR(12) COMMENT 'PE支付编码',
|
|
status INT(1) NOT NULL COMMENT '0:初始 1:处理中 2:入账完成(成功) 3:入账失败,处理中 4:入账完成(失败)',
|
|
accounting_date VARCHAR(8) NOT NULL COMMENT '会计日',
|
|
create_time TIMESTAMP(6) NOT NULL COMMENT '插入时间',
|
|
last_update_time TIMESTAMP(6) COMMENT '更新时间',
|
|
remark VARCHAR(256) COMMENT '备注',
|
|
context_trans_no VARCHAR(32) COMMENT '关联事务号，回滚用',
|
|
operation_type INT(1) COMMENT '操作类型'
|
|
) ENGINE = InnoDB
|
|
DEFAULT CHARSET = utf8
|
|
ROW_FORMAT = COMPACT COMMENT ='PE支付交易表';
|
|
ALTER TABLE t_pay_transaction
|
|
ADD CONSTRAINT PK_t_pay_transaction PRIMARY KEY (transaction_id);
|
|
CREATE INDEX IDX_DPT_TO ON t_pay_transaction (sys_trace_no) COMMENT '根据入账请求流水号索引';
|
|
CREATE UNIQUE INDEX UDX_PAYSEQNO_CLEARINGNO ON t_pay_transaction (transaction_no) COMMENT '根据事务流水号(PE支付流水号)唯一索引';
|
|
|
|
DROP TABLE IF EXISTS t_subacc_fundattribute;
|
|
CREATE TABLE t_subacc_fundattribute
|
|
(
|
|
account_type INT(4) NOT NULL COMMENT '账户类型',
|
|
currency_code VARCHAR(3) NOT NULL COMMENT '币种',
|
|
fund_type VARCHAR(16) NOT NULL COMMENT '资金属性',
|
|
remark VARCHAR(200) COMMENT '备注',
|
|
gmt_created TIMESTAMP(6) NOT NULL COMMENT '创建时间',
|
|
gmt_modified TIMESTAMP(6) NOT NULL COMMENT '更新时间',
|
|
CONSTRAINT UK_TDAF_ACCOUNT_TYPE UNIQUE (account_type, currency_code, fund_type)
|
|
) ENGINE = InnoDB
|
|
DEFAULT CHARSET = utf8
|
|
ROW_FORMAT = COMPACT COMMENT ='子账户资金属性配置表';
|
|
|
|
|
|
DROP TABLE IF EXISTS t_outer_account;
|
|
CREATE TABLE t_outer_account
|
|
(
|
|
account_no VARCHAR(32) NOT NULL COMMENT '账户号',
|
|
# card_no VARCHAR(32) NOT NULL COMMENT '卡号',
|
|
account_title_no VARCHAR(32) COMMENT '科目号',
|
|
account_name VARCHAR(64) COMMENT '账户名称',
|
|
open_date DATE COMMENT '开户日期(创建时间)',
|
|
member_id VARCHAR(15) COMMENT '会员号',
|
|
status_map VARCHAR(6) COMMENT '第一位:激活状态 0:未激活 1:已激活 第二位:锁定状态 0:未锁定 1:已锁定 第三位:冻结状态 0:未冻结 1:借方冻结 2:贷方冻结 3:双向冻结 第四位:销户状态 0:未销户 1:已销户 2:已结转长期不动户',
|
|
account_attribute INT(1) COMMENT '账户属性 1:对私 2:对公',
|
|
account_type INT(4) COMMENT '',
|
|
curr_bal_direction INT(1) COMMENT '当前余额方向 1:借 2:贷',
|
|
bal_direction INT(1) COMMENT '账户余额方向 1:借 2:贷 0:双向',
|
|
currency_code VARCHAR(3) COMMENT '货币类型',
|
|
last_update_time TIMESTAMP(6) COMMENT '更新时间',
|
|
request_no VARCHAR(32) NOT NULL COMMENT '开外部户请求请求号:不可为空 需唯一',
|
|
PRIMARY KEY (account_no),
|
|
UNIQUE INDEX uidx_doao_ro (request_no),
|
|
INDEX idx_t_dpm_outer_account (open_date),
|
|
INDEX idx_oa_member_id (member_id)
|
|
)
|
|
ENGINE = InnoDB
|
|
DEFAULT CHARSET = utf8
|
|
ROW_FORMAT = COMPACT COMMENT ='T_DPM_OUTER_ACCOUNT';
|
|
|