# 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';