1652 lines
58 KiB
SQL
1652 lines
58 KiB
SQL
create table DBAJOBCONF
|
|
(
|
|
jobname VARCHAR2(50),
|
|
jobstatus VARCHAR2(20),
|
|
comments VARCHAR2(50)
|
|
)
|
|
;
|
|
comment on column DBAJOBCONF.jobname
|
|
is '作业名字';
|
|
comment on column DBAJOBCONF.jobstatus
|
|
is '作业进度';
|
|
grant select, insert, update, delete on DBAJOBCONF to TESTRW;
|
|
|
|
|
|
create table DBAJOBLOG
|
|
(
|
|
jobid NUMBER not null,
|
|
jobname VARCHAR2(50),
|
|
startdate DATE,
|
|
enddate DATE,
|
|
jobstatus VARCHAR2(20),
|
|
comments VARCHAR2(100),
|
|
parameter VARCHAR2(100)
|
|
)
|
|
;
|
|
alter table DBAJOBLOG
|
|
add constraint PK_DBA_JOBLOG primary key (JOBID);
|
|
grant select, insert, update, delete on DBAJOBLOG to TESTRW;
|
|
|
|
|
|
create table TB_TITLE_DAILY
|
|
(
|
|
id NUMBER(15) not null,
|
|
account_date CHAR(8) not null,
|
|
title_code VARCHAR2(16) not null,
|
|
balance_direction CHAR(1) not null,
|
|
debit_amount NUMBER(19,4),
|
|
credit_amount NUMBER(19,4),
|
|
debit_count NUMBER(15),
|
|
credit_count NUMBER(15),
|
|
debit_balance NUMBER(19,4),
|
|
credit_balance NUMBER(19,4),
|
|
currency NUMBER(3) not null,
|
|
gmt_create TIMESTAMP(6) not null,
|
|
gmt_modified TIMESTAMP(6)
|
|
)
|
|
;
|
|
comment on table TB_TITLE_DAILY
|
|
is '科目每日信息';
|
|
comment on column TB_TITLE_DAILY.account_date
|
|
is '会计日';
|
|
comment on column TB_TITLE_DAILY.title_code
|
|
is '科目代码';
|
|
comment on column TB_TITLE_DAILY.balance_direction
|
|
is '余额方向';
|
|
comment on column TB_TITLE_DAILY.debit_amount
|
|
is '借方发生额';
|
|
comment on column TB_TITLE_DAILY.credit_amount
|
|
is '贷方发生额';
|
|
comment on column TB_TITLE_DAILY.debit_count
|
|
is '借方发生笔数';
|
|
comment on column TB_TITLE_DAILY.credit_count
|
|
is '贷方发生笔数';
|
|
comment on column TB_TITLE_DAILY.debit_balance
|
|
is '借方余额';
|
|
comment on column TB_TITLE_DAILY.credit_balance
|
|
is '贷方余额';
|
|
comment on column TB_TITLE_DAILY.currency
|
|
is '币种';
|
|
comment on column TB_TITLE_DAILY.gmt_create
|
|
is '创建时间';
|
|
comment on column TB_TITLE_DAILY.gmt_modified
|
|
is '最后修改时间';
|
|
create index IDX_TD_ACCOUNT_DATE on TB_TITLE_DAILY (ACCOUNT_DATE);
|
|
create unique index U_TITLE_DAILY_DATE on TB_TITLE_DAILY (TITLE_CODE, ACCOUNT_DATE);
|
|
alter table TB_TITLE_DAILY
|
|
add constraint PK_TB_TITLE_DAILY primary key (ID);
|
|
grant select, insert, update, delete on TB_TITLE_DAILY to DPMUSER;
|
|
grant select, insert, update, delete on TB_TITLE_DAILY to TESTRW;
|
|
|
|
create table TB_TITLE_DAILY_TEMP
|
|
(
|
|
id NUMBER(15) not null,
|
|
account_date CHAR(8) not null,
|
|
title_code VARCHAR2(16) not null,
|
|
balance_direction CHAR(1) not null,
|
|
debit_amount NUMBER(19,4),
|
|
credit_amount NUMBER(19,4),
|
|
debit_count NUMBER(15),
|
|
credit_count NUMBER(15),
|
|
debit_balance NUMBER(19,4),
|
|
credit_balance NUMBER(19,4),
|
|
currency NUMBER(3) not null,
|
|
gmt_create TIMESTAMP(6) not null,
|
|
gmt_modified TIMESTAMP(6)
|
|
)
|
|
;
|
|
comment on table TB_TITLE_DAILY_TEMP
|
|
is '科目每日信息-备份';
|
|
comment on column TB_TITLE_DAILY_TEMP.account_date
|
|
is '会计日';
|
|
comment on column TB_TITLE_DAILY_TEMP.title_code
|
|
is '科目代码';
|
|
comment on column TB_TITLE_DAILY_TEMP.balance_direction
|
|
is '余额方向';
|
|
comment on column TB_TITLE_DAILY_TEMP.debit_amount
|
|
is '借方发生额';
|
|
comment on column TB_TITLE_DAILY_TEMP.credit_amount
|
|
is '贷方发生额';
|
|
comment on column TB_TITLE_DAILY_TEMP.debit_count
|
|
is '借方发生笔数';
|
|
comment on column TB_TITLE_DAILY_TEMP.credit_count
|
|
is '贷方发生笔数';
|
|
comment on column TB_TITLE_DAILY_TEMP.debit_balance
|
|
is '借方余额';
|
|
comment on column TB_TITLE_DAILY_TEMP.credit_balance
|
|
is '贷方余额';
|
|
comment on column TB_TITLE_DAILY_TEMP.currency
|
|
is '币种';
|
|
comment on column TB_TITLE_DAILY_TEMP.gmt_create
|
|
is '创建时间';
|
|
comment on column TB_TITLE_DAILY_TEMP.gmt_modified
|
|
is '最后修改时间';
|
|
create index IDX_TD_ACCOUNT_DATE_TEMP on TB_TITLE_DAILY_TEMP (ACCOUNT_DATE);
|
|
create unique index U_TITLE_DAILY_DATE_TEMP on TB_TITLE_DAILY_TEMP (TITLE_CODE, ACCOUNT_DATE);
|
|
alter table TB_TITLE_DAILY_TEMP
|
|
add constraint PK_TB_TITLE_DAILY_TEMP primary key (ID);
|
|
grant select, insert, update, delete on TB_TITLE_DAILY_TEMP to TESTRW;
|
|
|
|
|
|
create table TEMP_TRG_LOG
|
|
(
|
|
account_no VARCHAR2(100),
|
|
addbalance NUMBER(19,4),
|
|
createtime DATE,
|
|
subbalance NUMBER(19,4),
|
|
mainbalance NUMBER(19,4)
|
|
)
|
|
;
|
|
grant select, insert, update, delete on TEMP_TRG_LOG to TESTRW;
|
|
|
|
|
|
create table T_ACT_ACCOUNT_TITILE
|
|
(
|
|
title_seq_no VARCHAR2(16) not null,
|
|
title_code VARCHAR2(16) not null,
|
|
title_name VARCHAR2(64) not null,
|
|
title_level NUMBER(2),
|
|
parent_title_code VARCHAR2(16),
|
|
is_leaf CHAR(1),
|
|
type CHAR(1) not null,
|
|
balance_direction NUMBER(1) not null,
|
|
status CHAR(1) not null,
|
|
gmt_create TIMESTAMP(6) not null,
|
|
gmt_modified TIMESTAMP(6) not null,
|
|
memo VARCHAR2(128),
|
|
title_range NUMBER(1) not null
|
|
)
|
|
;
|
|
comment on table T_ACT_ACCOUNT_TITILE
|
|
is '会计科目表';
|
|
comment on column T_ACT_ACCOUNT_TITILE.title_seq_no
|
|
is '科目序号';
|
|
comment on column T_ACT_ACCOUNT_TITILE.title_code
|
|
is '科目代码';
|
|
comment on column T_ACT_ACCOUNT_TITILE.title_name
|
|
is '科目名称';
|
|
comment on column T_ACT_ACCOUNT_TITILE.is_leaf
|
|
is 'Y: 是 N: 否';
|
|
comment on column T_ACT_ACCOUNT_TITILE.type
|
|
is '类型：1（资产类）；2（负债类）；3(所有者权益)；4（共同类）5(损益类)';
|
|
comment on column T_ACT_ACCOUNT_TITILE.balance_direction
|
|
is '余额方向：
|
|
1:借
|
|
2:贷
|
|
0:双向';
|
|
comment on column T_ACT_ACCOUNT_TITILE.status
|
|
is '状态：Y（有效）；N（无效）';
|
|
comment on column T_ACT_ACCOUNT_TITILE.gmt_create
|
|
is '创建时间';
|
|
comment on column T_ACT_ACCOUNT_TITILE.gmt_modified
|
|
is '最后修改时间';
|
|
comment on column T_ACT_ACCOUNT_TITILE.memo
|
|
is '备注';
|
|
comment on column T_ACT_ACCOUNT_TITILE.title_range
|
|
is '1.内部科目;2,外部科目';
|
|
create unique index AK_KEY_TITLE_CODE on T_ACT_ACCOUNT_TITILE (TITLE_CODE);
|
|
alter table T_ACT_ACCOUNT_TITILE
|
|
add constraint PK_T_ACT_ACCOUNT_TITILE primary key (TITLE_SEQ_NO);
|
|
grant select, insert, update, delete on T_ACT_ACCOUNT_TITILE to DPMUSER;
|
|
grant select, insert, update, delete on T_ACT_ACCOUNT_TITILE to TESTRW;
|
|
|
|
|
|
create table T_ACT_ACCOUNT_TITILE_TEMP
|
|
(
|
|
title_seq_no VARCHAR2(16) not null,
|
|
title_code VARCHAR2(16) not null,
|
|
title_name VARCHAR2(64) not null,
|
|
title_level NUMBER(2),
|
|
parent_title_code VARCHAR2(16),
|
|
is_leaf CHAR(1),
|
|
type CHAR(1) not null,
|
|
balance_direction NUMBER(1) not null,
|
|
status CHAR(1) not null,
|
|
gmt_create TIMESTAMP(6) not null,
|
|
gmt_modified TIMESTAMP(6) not null,
|
|
memo VARCHAR2(128),
|
|
title_range NUMBER(1)
|
|
)
|
|
;
|
|
comment on table T_ACT_ACCOUNT_TITILE_TEMP
|
|
is '会计科目表-备份';
|
|
comment on column T_ACT_ACCOUNT_TITILE_TEMP.title_seq_no
|
|
is '科目序号';
|
|
comment on column T_ACT_ACCOUNT_TITILE_TEMP.title_code
|
|
is '科目代码';
|
|
comment on column T_ACT_ACCOUNT_TITILE_TEMP.title_name
|
|
is '科目名称';
|
|
comment on column T_ACT_ACCOUNT_TITILE_TEMP.is_leaf
|
|
is 'Y: 是 N: 否';
|
|
comment on column T_ACT_ACCOUNT_TITILE_TEMP.type
|
|
is '类型：1（资产类）；2（负债类）；4（共同类）';
|
|
comment on column T_ACT_ACCOUNT_TITILE_TEMP.balance_direction
|
|
is '余额方向：
|
|
1:借
|
|
2:贷
|
|
0:双向';
|
|
comment on column T_ACT_ACCOUNT_TITILE_TEMP.status
|
|
is '状态：Y（有效）；N（无效）';
|
|
comment on column T_ACT_ACCOUNT_TITILE_TEMP.gmt_create
|
|
is '创建时间';
|
|
comment on column T_ACT_ACCOUNT_TITILE_TEMP.gmt_modified
|
|
is '最后修改时间';
|
|
comment on column T_ACT_ACCOUNT_TITILE_TEMP.memo
|
|
is '备注';
|
|
comment on column T_ACT_ACCOUNT_TITILE_TEMP.title_range
|
|
is '1.内部科目;2,外部科目';
|
|
alter table T_ACT_ACCOUNT_TITILE_TEMP
|
|
add constraint PK_T_ACT_ACCOUNT_TITILE_TEMP primary key (TITLE_SEQ_NO);
|
|
grant select, insert, update, delete on T_ACT_ACCOUNT_TITILE_TEMP to TESTRW;
|
|
|
|
create table T_ACT_ACCOUNT_TITILE_TEMP_TEMP
|
|
(
|
|
title_seq_no VARCHAR2(16) not null,
|
|
title_code VARCHAR2(16) not null,
|
|
title_name VARCHAR2(64) not null,
|
|
title_level NUMBER(2),
|
|
parent_title_code VARCHAR2(16),
|
|
is_leaf CHAR(1),
|
|
type CHAR(1) not null,
|
|
balance_direction NUMBER(1) not null,
|
|
status CHAR(1) not null,
|
|
gmt_create TIMESTAMP(6) not null,
|
|
gmt_modified TIMESTAMP(6) not null,
|
|
memo VARCHAR2(128)
|
|
)
|
|
;
|
|
alter table T_ACT_ACCOUNT_TITILE_TEMP_TEMP
|
|
add constraint PK_T_ACT_ACCOUNT_TITILE_TEMP_T primary key (TITLE_SEQ_NO);
|
|
grant select, insert, update, delete on T_ACT_ACCOUNT_TITILE_TEMP_TEMP to TESTRW;
|
|
|
|
prompt Creating T_DPM_ACCOUNT_CRL_DEF...
|
|
create table T_DPM_ACCOUNT_CRL_DEF
|
|
(
|
|
account_title_id VARCHAR2(32),
|
|
account_attribute NUMBER(1),
|
|
account_type NUMBER(4),
|
|
currency_code VARCHAR2(3),
|
|
bal_direction NUMBER(1),
|
|
status NUMBER(1),
|
|
remark VARCHAR2(200),
|
|
input_uid VARCHAR2(32),
|
|
input_time TIMESTAMP(6),
|
|
check_uid VARCHAR2(32),
|
|
check_time TIMESTAMP(6)
|
|
)
|
|
;
|
|
comment on column T_DPM_ACCOUNT_CRL_DEF.account_attribute
|
|
is '1:对私
|
|
2:对公
|
|
9:内部';
|
|
comment on column T_DPM_ACCOUNT_CRL_DEF.account_type
|
|
is '1:基本户
|
|
2:一般户
|
|
3:专用户
|
|
4:临时户
|
|
5:保证金户
|
|
';
|
|
comment on column T_DPM_ACCOUNT_CRL_DEF.bal_direction
|
|
is '1:借
|
|
2:贷
|
|
0:双向';
|
|
comment on column T_DPM_ACCOUNT_CRL_DEF.status
|
|
is '0:有效
|
|
1:无效';
|
|
create unique index AK_UK_TDACD_ACCOUNT_TYPE on T_DPM_ACCOUNT_CRL_DEF (ACCOUNT_TYPE, CURRENCY_CODE);
|
|
grant select, insert, update, delete on T_DPM_ACCOUNT_CRL_DEF to DPMUSER;
|
|
grant select, insert, update, delete on T_DPM_ACCOUNT_CRL_DEF to TESTRW;
|
|
|
|
|
|
create table T_DPM_ACCOUNT_ENTRY
|
|
(
|
|
entry_seq_no VARCHAR2(32) not null,
|
|
transaction_no VARCHAR2(32) not null,
|
|
suite_no VARCHAR2(32) not null,
|
|
voucher_no VARCHAR2(50),
|
|
sys_trace_no VARCHAR2(32),
|
|
account_no VARCHAR2(32) not null,
|
|
title_no VARCHAR2(32),
|
|
entry_type NUMBER(1),
|
|
is_manual NUMBER(1),
|
|
txn_currency VARCHAR2(3),
|
|
amount NUMBER(19,4) not null,
|
|
txn_type NUMBER(1) not null,
|
|
direction NUMBER(1) not null,
|
|
audit_status NUMBER(1) not null,
|
|
status NUMBER(1),
|
|
accounting_date VARCHAR2(8) not null,
|
|
create_time TIMESTAMP(6) not null,
|
|
last_update_time TIMESTAMP(6),
|
|
bal_update_time TIMESTAMP(6),
|
|
remark VARCHAR2(256),
|
|
is_buffer NUMBER(1),
|
|
old_entry_seq_no VARCHAR2(32),
|
|
summary VARCHAR2(256),
|
|
input_uid VARCHAR2(32),
|
|
input_time TIMESTAMP(6),
|
|
check_uid VARCHAR2(32),
|
|
check_time TIMESTAMP(6),
|
|
rsvd_amt1 NUMBER(19,4) default 0,
|
|
rsvd_amt2 NUMBER(19,4) default 0,
|
|
rsvd_text1 VARCHAR2(50),
|
|
rsvd_text2 VARCHAR2(50),
|
|
context_voucher_no VARCHAR2(50),
|
|
operater NUMBER(1),
|
|
clearing_code VARCHAR2(16)
|
|
)
|
|
partition by range (CREATE_TIME) interval (NUMTOYMINTERVAL(1,'MONTH'))
|
|
(
|
|
partition PA_201505 values less than (TIMESTAMP' 2015-06-01 00:00:00')
|
|
tablespace DPM,
|
|
partition PA_201506 values less than (TIMESTAMP' 2015-07-01 00:00:00')
|
|
tablespace DPM,
|
|
partition PA_201507 values less than (TIMESTAMP' 2015-08-01 00:00:00')
|
|
tablespace DPM,
|
|
partition PA_201508 values less than (TIMESTAMP' 2015-09-01 00:00:00')
|
|
tablespace DPM,
|
|
partition PA_201509 values less than (TIMESTAMP' 2015-10-01 00:00:00')
|
|
tablespace DPM,
|
|
partition PA_201510 values less than (TIMESTAMP' 2015-11-01 00:00:00')
|
|
tablespace DPM,
|
|
partition PA_201511 values less than (TIMESTAMP' 2015-12-01 00:00:00')
|
|
tablespace DPM,
|
|
partition PA_201512 values less than (TIMESTAMP' 2016-01-01 00:00:00')
|
|
tablespace DPM,
|
|
partition PA_201601 values less than (TIMESTAMP' 2016-02-01 00:00:00')
|
|
tablespace DPM,
|
|
partition PA_201602 values less than (TIMESTAMP' 2016-03-01 00:00:00')
|
|
tablespace DPM,
|
|
partition PA_201603 values less than (TIMESTAMP' 2016-04-01 00:00:00')
|
|
tablespace DPM,
|
|
partition PA_201604 values less than (TIMESTAMP' 2016-05-01 00:00:00')
|
|
tablespace DPM,
|
|
partition PA_201605 values less than (TIMESTAMP' 2016-06-01 00:00:00')
|
|
tablespace DPM,
|
|
partition SYS_P156 values less than (TIMESTAMP' 2016-07-01 00:00:00')
|
|
tablespace DPM,
|
|
partition SYS_P176 values less than (TIMESTAMP' 2016-08-01 00:00:00')
|
|
tablespace DPM,
|
|
partition SYS_P196 values less than (TIMESTAMP' 2016-09-01 00:00:00')
|
|
tablespace DPM,
|
|
partition SYS_P232 values less than (TIMESTAMP' 2016-10-01 00:00:00')
|
|
tablespace DPM,
|
|
partition SYS_P274 values less than (TIMESTAMP' 2016-11-01 00:00:00')
|
|
tablespace DPM,
|
|
partition SYS_P314 values less than (TIMESTAMP' 2016-12-01 00:00:00')
|
|
tablespace DPM,
|
|
partition SYS_P335 values less than (TIMESTAMP' 2017-01-01 00:00:00')
|
|
tablespace DPM,
|
|
partition SYS_P355 values less than (TIMESTAMP' 2017-02-01 00:00:00')
|
|
tablespace DPM,
|
|
partition SYS_P370 values less than (TIMESTAMP' 2017-03-01 00:00:00')
|
|
tablespace DPM,
|
|
partition SYS_P410 values less than (TIMESTAMP' 2017-04-01 00:00:00')
|
|
tablespace DPM,
|
|
partition SYS_P430 values less than (TIMESTAMP' 2017-05-01 00:00:00')
|
|
tablespace DPM,
|
|
partition SYS_P450 values less than (TIMESTAMP' 2017-06-01 00:00:00')
|
|
tablespace DPM,
|
|
partition SYS_P470 values less than (TIMESTAMP' 2017-07-01 00:00:00')
|
|
tablespace DPM,
|
|
partition SYS_P510 values less than (TIMESTAMP' 2017-08-01 00:00:00')
|
|
tablespace DPM,
|
|
partition SYS_P550 values less than (TIMESTAMP' 2017-09-01 00:00:00')
|
|
tablespace DPM,
|
|
partition SYS_P570 values less than (TIMESTAMP' 2017-10-01 00:00:00')
|
|
tablespace DPM,
|
|
partition SYS_P591 values less than (TIMESTAMP' 2017-11-01 00:00:00')
|
|
tablespace DPM,
|
|
partition SYS_P631 values less than (TIMESTAMP' 2017-12-01 00:00:00')
|
|
tablespace DPM,
|
|
partition SYS_P655 values less than (TIMESTAMP' 2018-01-01 00:00:00')
|
|
tablespace DPM
|
|
);
|
|
comment on column T_DPM_ACCOUNT_ENTRY.entry_type
|
|
is '0:表内分录
|
|
1:表外分录';
|
|
comment on column T_DPM_ACCOUNT_ENTRY.is_manual
|
|
is '自动手工标志
|
|
0:自动
|
|
1:手工';
|
|
comment on column T_DPM_ACCOUNT_ENTRY.txn_type
|
|
is '0-正常
|
|
1-红字
|
|
2-蓝字
|
|
9-抹帐
|
|
';
|
|
comment on column T_DPM_ACCOUNT_ENTRY.direction
|
|
is '1:借
|
|
2:贷';
|
|
comment on column T_DPM_ACCOUNT_ENTRY.audit_status
|
|
is '0-未复核
|
|
1-已复核
|
|
2-不需事中复核
|
|
';
|
|
comment on column T_DPM_ACCOUNT_ENTRY.status
|
|
is '0-未入账
|
|
2-已入账
|
|
3-已抹帐
|
|
9-无效';
|
|
comment on column T_DPM_ACCOUNT_ENTRY.is_buffer
|
|
is '0:未缓冲
|
|
1:缓冲';
|
|
comment on column T_DPM_ACCOUNT_ENTRY.context_voucher_no
|
|
is '关联结算流水号';
|
|
comment on column T_DPM_ACCOUNT_ENTRY.clearing_code
|
|
is '清算编码';
|
|
create index IDX_TD_ACCOUNTING_DATE on T_DPM_ACCOUNT_ENTRY (ACCOUNTING_DATE)
|
|
local;
|
|
create index I_ENTRY_ACCOUNTNO on T_DPM_ACCOUNT_ENTRY (ACCOUNT_NO)
|
|
local;
|
|
create index I_ENTRY_CREATE_TIME on T_DPM_ACCOUNT_ENTRY (CREATE_TIME);
|
|
create index I_ENTRY_SUITENO on T_DPM_ACCOUNT_ENTRY (SUITE_NO)
|
|
local;
|
|
create index I_ENTRY_TRANSACTIONNO on T_DPM_ACCOUNT_ENTRY (TRANSACTION_NO)
|
|
local;
|
|
alter table T_DPM_ACCOUNT_ENTRY
|
|
add constraint PK_T_DPM_ACCOUNT_ENTRY primary key (ENTRY_SEQ_NO);
|
|
alter table T_DPM_ACCOUNT_ENTRY
|
|
add constraint UK_ENTRY_VOUCHER_NO unique (VOUCHER_NO);
|
|
grant select, insert, update, delete on T_DPM_ACCOUNT_ENTRY to DPMUSER;
|
|
grant select, insert, update, delete on T_DPM_ACCOUNT_ENTRY to TESTRW;
|
|
|
|
|
|
create table T_DPM_ACCOUNT_ENTRY_TEMP
|
|
(
|
|
entry_seq_no VARCHAR2(32) not null,
|
|
transaction_no VARCHAR2(32) not null,
|
|
suite_no VARCHAR2(32) not null,
|
|
voucher_no VARCHAR2(50),
|
|
sys_trace_no VARCHAR2(32),
|
|
account_no VARCHAR2(32) not null,
|
|
title_no VARCHAR2(32),
|
|
entry_type NUMBER(1),
|
|
is_manual NUMBER(1),
|
|
txn_currency VARCHAR2(3),
|
|
amount NUMBER(19,4) not null,
|
|
txn_type NUMBER(1) not null,
|
|
direction NUMBER(1) not null,
|
|
audit_status NUMBER(1) not null,
|
|
status NUMBER(1),
|
|
accounting_date VARCHAR2(8) not null,
|
|
create_time TIMESTAMP(6) not null,
|
|
last_update_time TIMESTAMP(6),
|
|
bal_update_time TIMESTAMP(6),
|
|
remark VARCHAR2(256),
|
|
is_buffer NUMBER(1),
|
|
old_entry_seq_no VARCHAR2(32),
|
|
summary VARCHAR2(256),
|
|
input_uid VARCHAR2(32),
|
|
input_time TIMESTAMP(6),
|
|
check_uid VARCHAR2(32),
|
|
check_time TIMESTAMP(6),
|
|
rsvd_amt1 NUMBER(19,4) default 0,
|
|
rsvd_amt2 NUMBER(19,4) default 0,
|
|
rsvd_text1 VARCHAR2(50),
|
|
rsvd_text2 VARCHAR2(50),
|
|
context_voucher_no VARCHAR2(50),
|
|
operater NUMBER(1),
|
|
clearing_code VARCHAR2(16)
|
|
)
|
|
;
|
|
comment on column T_DPM_ACCOUNT_ENTRY_TEMP.entry_type
|
|
is '0:表内分录
|
|
1:表外分录';
|
|
comment on column T_DPM_ACCOUNT_ENTRY_TEMP.is_manual
|
|
is '自动手工标志
|
|
0:自动
|
|
1:手工';
|
|
comment on column T_DPM_ACCOUNT_ENTRY_TEMP.txn_type
|
|
is '0-正常
|
|
1-红字
|
|
2-蓝字
|
|
9-抹帐
|
|
';
|
|
comment on column T_DPM_ACCOUNT_ENTRY_TEMP.direction
|
|
is '1:借
|
|
2:贷';
|
|
comment on column T_DPM_ACCOUNT_ENTRY_TEMP.audit_status
|
|
is '0-未复核
|
|
1-已复核
|
|
2-不需事中复核
|
|
';
|
|
comment on column T_DPM_ACCOUNT_ENTRY_TEMP.status
|
|
is '0-未入账
|
|
2-已入账
|
|
3-已抹帐
|
|
9-无效';
|
|
comment on column T_DPM_ACCOUNT_ENTRY_TEMP.is_buffer
|
|
is '0:未缓冲
|
|
1:缓冲';
|
|
comment on column T_DPM_ACCOUNT_ENTRY_TEMP.context_voucher_no
|
|
is '关联结算流水号';
|
|
comment on column T_DPM_ACCOUNT_ENTRY_TEMP.clearing_code
|
|
is '清算编码';
|
|
alter table T_DPM_ACCOUNT_ENTRY_TEMP
|
|
add constraint PK_T_DPM_ACCOUNT_ENTRY_TEMP primary key (ENTRY_SEQ_NO);
|
|
grant select, insert, update, delete on T_DPM_ACCOUNT_ENTRY_TEMP to DPMUSER;
|
|
grant select, insert, update, delete on T_DPM_ACCOUNT_ENTRY_TEMP to TESTRW;
|
|
|
|
|
|
create table T_DPM_BUFFER_DETAIL
|
|
(
|
|
buffer_detail_id NUMBER(32) not null,
|
|
account_no VARCHAR2(32) not null,
|
|
sys_trace_no VARCHAR2(32),
|
|
voucher_no VARCHAR2(50) not null,
|
|
transaction_no VARCHAR2(32) not null,
|
|
amount NUMBER(19,4) not null,
|
|
crdr NUMBER(1) not null,
|
|
txn_type NUMBER(1) not null,
|
|
accounting_rule VARCHAR2(16) not null,
|
|
product_code VARCHAR2(12),
|
|
pay_code VARCHAR2(12),
|
|
status NUMBER(1) not null,
|
|
count NUMBER(1) not null,
|
|
summary VARCHAR2(256),
|
|
occupy_sign VARCHAR2(32),
|
|
remark VARCHAR2(256),
|
|
create_time TIMESTAMP(6) not null,
|
|
update_time TIMESTAMP(6) not null,
|
|
suite_no VARCHAR2(32) not null,
|
|
context_voucher_no VARCHAR2(32),
|
|
accounting_date VARCHAR2(8),
|
|
operation_type NUMBER(1),
|
|
balance_type NUMBER(1)
|
|
)
|
|
;
|
|
comment on table T_DPM_BUFFER_DETAIL
|
|
is '待缓冲入账明细(缓冲池)';
|
|
comment on column T_DPM_BUFFER_DETAIL.buffer_detail_id
|
|
is '待入账数据流水号';
|
|
comment on column T_DPM_BUFFER_DETAIL.account_no
|
|
is '账户号';
|
|
comment on column T_DPM_BUFFER_DETAIL.sys_trace_no
|
|
is '系统跟踪号';
|
|
comment on column T_DPM_BUFFER_DETAIL.amount
|
|
is '金额';
|
|
comment on column T_DPM_BUFFER_DETAIL.crdr
|
|
is '借贷方向
|
|
1:借
|
|
2:贷';
|
|
comment on column T_DPM_BUFFER_DETAIL.txn_type
|
|
is '交易类型
|
|
0:正常
|
|
1:红字
|
|
2:蓝字
|
|
9:抹帐
|
|
';
|
|
comment on column T_DPM_BUFFER_DETAIL.accounting_rule
|
|
is '分户执行顺序
|
|
0.先贷后借(默认)
|
|
1.借记
|
|
2.贷记
|
|
';
|
|
comment on column T_DPM_BUFFER_DETAIL.product_code
|
|
is 'PE产品编码';
|
|
comment on column T_DPM_BUFFER_DETAIL.pay_code
|
|
is 'PE支付编码';
|
|
comment on column T_DPM_BUFFER_DETAIL.status
|
|
is '状态
|
|
0.待入账
|
|
1.成功(不会存在,成功直接删除数据)
|
|
2.失败
|
|
3.处理中';
|
|
comment on column T_DPM_BUFFER_DETAIL.count
|
|
is '执行次数';
|
|
comment on column T_DPM_BUFFER_DETAIL.summary
|
|
is '摘要';
|
|
comment on column T_DPM_BUFFER_DETAIL.occupy_sign
|
|
is '时间戳';
|
|
comment on column T_DPM_BUFFER_DETAIL.remark
|
|
is '备注';
|
|
comment on column T_DPM_BUFFER_DETAIL.create_time
|
|
is '创建时间';
|
|
comment on column T_DPM_BUFFER_DETAIL.update_time
|
|
is '更新时间';
|
|
comment on column T_DPM_BUFFER_DETAIL.suite_no
|
|
is '套号';
|
|
comment on column T_DPM_BUFFER_DETAIL.context_voucher_no
|
|
is '关联凭证号';
|
|
comment on column T_DPM_BUFFER_DETAIL.accounting_date
|
|
is '会计日';
|
|
create index IDX_BUFFERDETAIL_CT on T_DPM_BUFFER_DETAIL (CREATE_TIME);
|
|
create index IDX_DBD_OS on T_DPM_BUFFER_DETAIL (OCCUPY_SIGN);
|
|
alter table T_DPM_BUFFER_DETAIL
|
|
add constraint PK_T_DPM_BUFFER_DETAIL primary key (BUFFER_DETAIL_ID);
|
|
grant select, insert, update, delete on T_DPM_BUFFER_DETAIL to DPMUSER;
|
|
grant select, insert, update, delete on T_DPM_BUFFER_DETAIL to TESTRW;
|
|
|
|
|
|
create table T_DPM_BUFFER_DETAIL_LOG
|
|
(
|
|
buffer_detail_id NUMBER(32) not null,
|
|
account_no VARCHAR2(32) not null,
|
|
sys_trace_no VARCHAR2(32),
|
|
voucher_no VARCHAR2(50) not null,
|
|
transaction_no VARCHAR2(32) not null,
|
|
amount NUMBER(19,4) not null,
|
|
crdr NUMBER(1) not null,
|
|
txn_type NUMBER(1) not null,
|
|
accounting_rule VARCHAR2(16) not null,
|
|
product_code VARCHAR2(12),
|
|
pay_code VARCHAR2(12),
|
|
summary VARCHAR2(256),
|
|
remark VARCHAR2(256),
|
|
create_time TIMESTAMP(6) not null,
|
|
update_time TIMESTAMP(6) not null,
|
|
suite_no VARCHAR2(32) not null,
|
|
context_voucher_no VARCHAR2(32),
|
|
accounting_date VARCHAR2(8),
|
|
operation_type NUMBER(1),
|
|
balance_type NUMBER(1)
|
|
)
|
|
;
|
|
comment on table T_DPM_BUFFER_DETAIL_LOG
|
|
is '待缓冲入账明细(缓冲池)日志';
|
|
comment on column T_DPM_BUFFER_DETAIL_LOG.buffer_detail_id
|
|
is '待入账数据流水号';
|
|
comment on column T_DPM_BUFFER_DETAIL_LOG.account_no
|
|
is '账户号';
|
|
comment on column T_DPM_BUFFER_DETAIL_LOG.sys_trace_no
|
|
is '系统跟踪号';
|
|
comment on column T_DPM_BUFFER_DETAIL_LOG.amount
|
|
is '金额';
|
|
comment on column T_DPM_BUFFER_DETAIL_LOG.crdr
|
|
is '借贷方向
|
|
1:借
|
|
2:贷';
|
|
comment on column T_DPM_BUFFER_DETAIL_LOG.txn_type
|
|
is '交易类型
|
|
0:正常
|
|
1:红字
|
|
2:蓝字
|
|
9:抹帐
|
|
';
|
|
comment on column T_DPM_BUFFER_DETAIL_LOG.accounting_rule
|
|
is '分户执行顺序
|
|
0.先贷后借(默认)
|
|
1.借记
|
|
2.贷记';
|
|
comment on column T_DPM_BUFFER_DETAIL_LOG.product_code
|
|
is 'PE产品编码';
|
|
comment on column T_DPM_BUFFER_DETAIL_LOG.pay_code
|
|
is 'PE支付编码';
|
|
comment on column T_DPM_BUFFER_DETAIL_LOG.summary
|
|
is '摘要';
|
|
comment on column T_DPM_BUFFER_DETAIL_LOG.remark
|
|
is '备注';
|
|
comment on column T_DPM_BUFFER_DETAIL_LOG.create_time
|
|
is '创建时间';
|
|
comment on column T_DPM_BUFFER_DETAIL_LOG.update_time
|
|
is '更新时间';
|
|
comment on column T_DPM_BUFFER_DETAIL_LOG.suite_no
|
|
is '套号';
|
|
comment on column T_DPM_BUFFER_DETAIL_LOG.context_voucher_no
|
|
is '关联凭证号';
|
|
comment on column T_DPM_BUFFER_DETAIL_LOG.accounting_date
|
|
is '会计日';
|
|
alter table T_DPM_BUFFER_DETAIL_LOG
|
|
add constraint PK_T_DPM_BUFFER_DETAIL_LOG primary key (BUFFER_DETAIL_ID);
|
|
grant select, insert, update on T_DPM_BUFFER_DETAIL_LOG to DPMUSER;
|
|
grant select, insert, update, delete on T_DPM_BUFFER_DETAIL_LOG to TESTRW;
|
|
|
|
|
|
create table T_DPM_BUFFER_RULE
|
|
(
|
|
buffer_rule_id NUMBER(32) not null,
|
|
product_code VARCHAR2(12) not null,
|
|
pay_code VARCHAR2(12) not null,
|
|
account_no VARCHAR2(32),
|
|
status NUMBER(1) not null,
|
|
crdr NUMBER(1) not null,
|
|
remark VARCHAR2(256),
|
|
create_time TIMESTAMP(6) not null,
|
|
update_time TIMESTAMP(6) not null,
|
|
valid_time TIMESTAMP(6) not null,
|
|
unvalid_time TIMESTAMP(6) not null
|
|
)
|
|
;
|
|
comment on table T_DPM_BUFFER_RULE
|
|
is '缓冲入账规则表';
|
|
comment on column T_DPM_BUFFER_RULE.product_code
|
|
is '产品编码';
|
|
comment on column T_DPM_BUFFER_RULE.pay_code
|
|
is 'PE支付编码';
|
|
comment on column T_DPM_BUFFER_RULE.account_no
|
|
is '账户号';
|
|
comment on column T_DPM_BUFFER_RULE.status
|
|
is '0.有效
|
|
1.无效(已删除)';
|
|
comment on column T_DPM_BUFFER_RULE.crdr
|
|
is '0:双向
|
|
1:借
|
|
2:贷
|
|
';
|
|
comment on column T_DPM_BUFFER_RULE.remark
|
|
is '备注';
|
|
comment on column T_DPM_BUFFER_RULE.create_time
|
|
is '创建时间';
|
|
comment on column T_DPM_BUFFER_RULE.update_time
|
|
is '更新时间';
|
|
comment on column T_DPM_BUFFER_RULE.valid_time
|
|
is '生效时间';
|
|
comment on column T_DPM_BUFFER_RULE.unvalid_time
|
|
is '失效时间';
|
|
create unique index UIDX_BUFFER_RULE on T_DPM_BUFFER_RULE (PRODUCT_CODE, PAY_CODE, ACCOUNT_NO, CRDR);
|
|
alter table T_DPM_BUFFER_RULE
|
|
add constraint PK_T_DPM_BUFFER_RULE primary key (BUFFER_RULE_ID);
|
|
grant select, insert, update on T_DPM_BUFFER_RULE to DPMUSER;
|
|
grant select, insert, update, delete on T_DPM_BUFFER_RULE to TESTRW;
|
|
|
|
|
|
create table T_DPM_INNER_ACCOUNT
|
|
(
|
|
account_no VARCHAR2(32) not null,
|
|
account_title_no VARCHAR2(32),
|
|
account_name VARCHAR2(64),
|
|
open_date DATE,
|
|
bal_direction NUMBER(1),
|
|
curr_bal_direction NUMBER(1),
|
|
currency_code VARCHAR2(3),
|
|
balance NUMBER(19,4) default 0,
|
|
last_update_time TIMESTAMP(6)
|
|
)
|
|
;
|
|
comment on column T_DPM_INNER_ACCOUNT.account_no
|
|
is '帐号号';
|
|
comment on column T_DPM_INNER_ACCOUNT.account_title_no
|
|
is '科目号';
|
|
comment on column T_DPM_INNER_ACCOUNT.account_name
|
|
is '账户名称';
|
|
comment on column T_DPM_INNER_ACCOUNT.open_date
|
|
is '开户日期(创建时间)';
|
|
comment on column T_DPM_INNER_ACCOUNT.bal_direction
|
|
is '1:借
|
|
2:贷
|
|
0:双向';
|
|
comment on column T_DPM_INNER_ACCOUNT.curr_bal_direction
|
|
is '1:借
|
|
2:贷
|
|
';
|
|
comment on column T_DPM_INNER_ACCOUNT.currency_code
|
|
is '货币类型';
|
|
comment on column T_DPM_INNER_ACCOUNT.balance
|
|
is '余额';
|
|
comment on column T_DPM_INNER_ACCOUNT.last_update_time
|
|
is '最后更新时间';
|
|
alter table T_DPM_INNER_ACCOUNT
|
|
add constraint PK_T_DPM_INNER_ACCOUNT primary key (ACCOUNT_NO);
|
|
grant select, insert, update, delete on T_DPM_INNER_ACCOUNT to DPMUSER;
|
|
grant select, insert, update, delete on T_DPM_INNER_ACCOUNT to TESTRW;
|
|
|
|
|
|
create table T_DPM_INNER_ACCOUNT_DETAIL
|
|
(
|
|
txn_seq_no VARCHAR2(32) not null,
|
|
sys_trace_no VARCHAR2(32),
|
|
accounting_date VARCHAR2(8),
|
|
txn_time TIMESTAMP(6) not null,
|
|
account_no VARCHAR2(32),
|
|
txn_type NUMBER(1),
|
|
txn_dscpt VARCHAR2(256),
|
|
change_type NUMBER(1),
|
|
direction VARCHAR2(1),
|
|
txn_amt NUMBER(19,4),
|
|
before_amt NUMBER(19,4),
|
|
after_amt NUMBER(19,4),
|
|
entry_seq_no VARCHAR2(32),
|
|
other_account_no VARCHAR2(32),
|
|
old_txn_seq_no VARCHAR2(32),
|
|
remark VARCHAR2(256),
|
|
crdr NUMBER(1),
|
|
product_code VARCHAR2(12),
|
|
pay_code VARCHAR2(12),
|
|
operation_type NUMBER(1),
|
|
delete_sign NUMBER(1),
|
|
suite_no VARCHAR2(32),
|
|
context_voucher_no VARCHAR2(32),
|
|
transaction_no VARCHAR2(32),
|
|
voucher_no VARCHAR2(50)
|
|
)
|
|
partition by range (TXN_TIME) interval (NUMTOYMINTERVAL(1,'MONTH'))
|
|
(
|
|
partition PA_201505 values less than (TIMESTAMP' 2015-06-01 00:00:00')
|
|
tablespace DPM,
|
|
partition PA_201506 values less than (TIMESTAMP' 2015-07-01 00:00:00')
|
|
tablespace DPM,
|
|
partition PA_201507 values less than (TIMESTAMP' 2015-08-01 00:00:00')
|
|
tablespace DPM,
|
|
partition PA_201508 values less than (TIMESTAMP' 2015-09-01 00:00:00')
|
|
tablespace DPM,
|
|
partition PA_201509 values less than (TIMESTAMP' 2015-10-01 00:00:00')
|
|
tablespace DPM,
|
|
partition PA_201510 values less than (TIMESTAMP' 2015-11-01 00:00:00')
|
|
tablespace DPM,
|
|
partition PA_201511 values less than (TIMESTAMP' 2015-12-01 00:00:00')
|
|
tablespace DPM,
|
|
partition PA_201512 values less than (TIMESTAMP' 2016-01-01 00:00:00')
|
|
tablespace DPM,
|
|
partition PA_201601 values less than (TIMESTAMP' 2016-02-01 00:00:00')
|
|
tablespace DPM,
|
|
partition PA_201602 values less than (TIMESTAMP' 2016-03-01 00:00:00')
|
|
tablespace DPM,
|
|
partition PA_201603 values less than (TIMESTAMP' 2016-04-01 00:00:00')
|
|
tablespace DPM,
|
|
partition PA_201604 values less than (TIMESTAMP' 2016-05-01 00:00:00')
|
|
tablespace DPM,
|
|
partition PA_201605 values less than (TIMESTAMP' 2016-06-01 00:00:00')
|
|
tablespace DPM,
|
|
partition SYS_P155 values less than (TIMESTAMP' 2016-07-01 00:00:00')
|
|
tablespace DPM,
|
|
partition SYS_P175 values less than (TIMESTAMP' 2016-08-01 00:00:00')
|
|
tablespace DPM,
|
|
partition SYS_P195 values less than (TIMESTAMP' 2016-09-01 00:00:00')
|
|
tablespace DPM,
|
|
partition SYS_P229 values less than (TIMESTAMP' 2016-10-01 00:00:00')
|
|
tablespace DPM,
|
|
partition SYS_P273 values less than (TIMESTAMP' 2016-11-01 00:00:00')
|
|
tablespace DPM,
|
|
partition SYS_P313 values less than (TIMESTAMP' 2016-12-01 00:00:00')
|
|
tablespace DPM,
|
|
partition SYS_P334 values less than (TIMESTAMP' 2017-01-01 00:00:00')
|
|
tablespace DPM,
|
|
partition SYS_P354 values less than (TIMESTAMP' 2017-02-01 00:00:00')
|
|
tablespace DPM,
|
|
partition SYS_P376 values less than (TIMESTAMP' 2017-03-01 00:00:00')
|
|
tablespace DPM,
|
|
partition SYS_P416 values less than (TIMESTAMP' 2017-04-01 00:00:00')
|
|
tablespace DPM,
|
|
partition SYS_P436 values less than (TIMESTAMP' 2017-05-01 00:00:00')
|
|
tablespace DPM,
|
|
partition SYS_P456 values less than (TIMESTAMP' 2017-06-01 00:00:00')
|
|
tablespace DPM,
|
|
partition SYS_P476 values less than (TIMESTAMP' 2017-07-01 00:00:00')
|
|
tablespace DPM,
|
|
partition SYS_P516 values less than (TIMESTAMP' 2017-08-01 00:00:00')
|
|
tablespace DPM,
|
|
partition SYS_P553 values less than (TIMESTAMP' 2017-09-01 00:00:00')
|
|
tablespace DPM,
|
|
partition SYS_P576 values less than (TIMESTAMP' 2017-10-01 00:00:00')
|
|
tablespace DPM,
|
|
partition SYS_P597 values less than (TIMESTAMP' 2017-11-01 00:00:00')
|
|
tablespace DPM,
|
|
partition SYS_P637 values less than (TIMESTAMP' 2017-12-01 00:00:00')
|
|
tablespace DPM,
|
|
partition SYS_P654 values less than (TIMESTAMP' 2018-01-01 00:00:00')
|
|
tablespace DPM
|
|
);
|
|
comment on table T_DPM_INNER_ACCOUNT_DETAIL
|
|
is '内部账户余额明细';
|
|
comment on column T_DPM_INNER_ACCOUNT_DETAIL.txn_seq_no
|
|
is '明细流水号';
|
|
comment on column T_DPM_INNER_ACCOUNT_DETAIL.sys_trace_no
|
|
is '系统跟踪号';
|
|
comment on column T_DPM_INNER_ACCOUNT_DETAIL.accounting_date
|
|
is '会计日';
|
|
comment on column T_DPM_INNER_ACCOUNT_DETAIL.account_no
|
|
is '账户号';
|
|
comment on column T_DPM_INNER_ACCOUNT_DETAIL.txn_type
|
|
is '0:正常
|
|
1:红字
|
|
2:蓝字
|
|
9:抹帐
|
|
';
|
|
comment on column T_DPM_INNER_ACCOUNT_DETAIL.txn_dscpt
|
|
is '摘要';
|
|
comment on column T_DPM_INNER_ACCOUNT_DETAIL.change_type
|
|
is '1: 借贷
|
|
2: 冻结解冻';
|
|
comment on column T_DPM_INNER_ACCOUNT_DETAIL.direction
|
|
is '1:加(收入)
|
|
2:减(支出)';
|
|
comment on column T_DPM_INNER_ACCOUNT_DETAIL.txn_amt
|
|
is '交易金额';
|
|
comment on column T_DPM_INNER_ACCOUNT_DETAIL.before_amt
|
|
is '交易前余额';
|
|
comment on column T_DPM_INNER_ACCOUNT_DETAIL.after_amt
|
|
is '交易后余额';
|
|
comment on column T_DPM_INNER_ACCOUNT_DETAIL.remark
|
|
is '备注';
|
|
comment on column T_DPM_INNER_ACCOUNT_DETAIL.crdr
|
|
is '借贷标志 1借2贷';
|
|
comment on column T_DPM_INNER_ACCOUNT_DETAIL.product_code
|
|
is 'PE产品编码';
|
|
comment on column T_DPM_INNER_ACCOUNT_DETAIL.pay_code
|
|
is 'PE支付编码';
|
|
comment on column T_DPM_INNER_ACCOUNT_DETAIL.operation_type
|
|
is '交易类型';
|
|
comment on column T_DPM_INNER_ACCOUNT_DETAIL.delete_sign
|
|
is '删除标记(冲正用)';
|
|
comment on column T_DPM_INNER_ACCOUNT_DETAIL.suite_no
|
|
is '套号';
|
|
comment on column T_DPM_INNER_ACCOUNT_DETAIL.context_voucher_no
|
|
is '关联结算流水号';
|
|
create index AK_INNER_KEY_ACCOUNT_NO on T_DPM_INNER_ACCOUNT_DETAIL (ACCOUNT_NO)
|
|
local;
|
|
create index IDX_DIAD_STN on T_DPM_INNER_ACCOUNT_DETAIL (SYS_TRACE_NO)
|
|
local;
|
|
create index IDX_DIAD_TXN_TIME on T_DPM_INNER_ACCOUNT_DETAIL (TXN_TIME);
|
|
create unique index UIDX_IAD_VO on T_DPM_INNER_ACCOUNT_DETAIL (VOUCHER_NO);
|
|
alter table T_DPM_INNER_ACCOUNT_DETAIL
|
|
add constraint PK_T_DPM_INNER_ACCOUNT_DETAIL primary key (TXN_SEQ_NO);
|
|
grant select, insert, update, delete on T_DPM_INNER_ACCOUNT_DETAIL to DPMUSER;
|
|
grant select, insert, update, delete on T_DPM_INNER_ACCOUNT_DETAIL to TESTRW;
|
|
|
|
|
|
create table T_DPM_INNER_ACCOUNT_DIARY
|
|
(
|
|
account_no VARCHAR2(32) not null,
|
|
account_date VARCHAR2(8) not null,
|
|
begin_dr_bal NUMBER(19,4) default 0,
|
|
begin_cr_bal NUMBER(19,4) default 0,
|
|
begin_dr_avail_bal NUMBER(19,4) default 0,
|
|
begin_cr_avail_bal NUMBER(19,4) default 0,
|
|
debit_amt NUMBER(19,4) default 0,
|
|
credit_amt NUMBER(19,4) default 0,
|
|
debit_cnt NUMBER(10) default 0,
|
|
credit_cnt NUMBER(10) default 0,
|
|
debit_frozen_amt NUMBER(19,4) default 0,
|
|
credit_frozen_amt NUMBER(19,4) default 0,
|
|
debit_frozen_cnt NUMBER(10) default 0,
|
|
credit_frozen_cnt NUMBER(10) default 0,
|
|
debit_unfrozen_amt NUMBER(19,4) default 0,
|
|
credit_unfrozen_amt NUMBER(19,4) default 0,
|
|
debit_unfrozen_cnt NUMBER(10) default 0,
|
|
credit_enfrozen_cnt NUMBER(10) default 0,
|
|
end_dr_bal NUMBER(19,4) default 0,
|
|
end_cr_bal NUMBER(19,4) default 0,
|
|
end_dr_avail_bal NUMBER(19,4) default 0,
|
|
end_cr_avail_bal NUMBER(19,4) default 0,
|
|
is_final NUMBER(1),
|
|
create_time TIMESTAMP(6),
|
|
last_update_time TIMESTAMP(6)
|
|
)
|
|
;
|
|
comment on column T_DPM_INNER_ACCOUNT_DIARY.is_final
|
|
is '0:已经扎帐
|
|
1:未扎帐';
|
|
alter table T_DPM_INNER_ACCOUNT_DIARY
|
|
add constraint PK_T_DPM_INNER_ACCOUNT_DIARY primary key (ACCOUNT_NO, ACCOUNT_DATE);
|
|
grant select, insert, update, delete on T_DPM_INNER_ACCOUNT_DIARY to DPMUSER;
|
|
grant select, insert, update, delete on T_DPM_INNER_ACCOUNT_DIARY to TESTRW;
|
|
|
|
create table T_DPM_MANAGEMENT_LOG
|
|
(
|
|
txn_seq_no VARCHAR2(32) not null,
|
|
sys_trace_no VARCHAR2(32),
|
|
account_id VARCHAR2(32),
|
|
txn_type VARCHAR2(6),
|
|
txn_dscpt VARCHAR2(256),
|
|
before_status VARCHAR2(6),
|
|
after_status VARCHAR2(6),
|
|
remark VARCHAR2(256),
|
|
input_uid VARCHAR2(32),
|
|
input_time TIMESTAMP(6),
|
|
check_uid VARCHAR2(32),
|
|
check_time TIMESTAMP(6),
|
|
resv_fld1 VARCHAR2(50),
|
|
resv_fld2 VARCHAR2(50),
|
|
resv_fld3 VARCHAR2(50)
|
|
)
|
|
;
|
|
alter table T_DPM_MANAGEMENT_LOG
|
|
add constraint PK_T_DPM_MANAGEMENT_LOG primary key (TXN_SEQ_NO);
|
|
grant select, insert, update, delete on T_DPM_MANAGEMENT_LOG to DPMUSER;
|
|
grant select, insert, update, delete on T_DPM_MANAGEMENT_LOG to TESTRW;
|
|
|
|
|
|
create table T_DPM_OUTER_ACCOUNT
|
|
(
|
|
account_no VARCHAR2(32) not null,
|
|
account_title_no VARCHAR2(32),
|
|
account_name VARCHAR2(64),
|
|
open_date DATE,
|
|
member_id VARCHAR2(15),
|
|
status_map VARCHAR2(6),
|
|
account_attribute NUMBER(1),
|
|
account_type NUMBER(4),
|
|
curr_bal_direction NUMBER(1),
|
|
bal_direction NUMBER(1),
|
|
currency_code VARCHAR2(3),
|
|
last_update_time TIMESTAMP(6),
|
|
request_no VARCHAR2(32) not null
|
|
)
|
|
;
|
|
comment on column T_DPM_OUTER_ACCOUNT.account_no
|
|
is '账户号';
|
|
comment on column T_DPM_OUTER_ACCOUNT.account_title_no
|
|
is '科目号';
|
|
comment on column T_DPM_OUTER_ACCOUNT.account_name
|
|
is '账户名称';
|
|
comment on column T_DPM_OUTER_ACCOUNT.open_date
|
|
is '开户日期(创建时间)';
|
|
comment on column T_DPM_OUTER_ACCOUNT.member_id
|
|
is '会员号';
|
|
comment on column T_DPM_OUTER_ACCOUNT.status_map
|
|
is '第一位:激活状态
|
|
0:未激活
|
|
1:已激活
|
|
第二位:锁定状态
|
|
0:未锁定
|
|
1:已锁定
|
|
第三位:冻结状态
|
|
0:未冻结
|
|
1:借方冻结
|
|
2:贷方冻结
|
|
3:双向冻结
|
|
第四位:销户状态
|
|
0:未销户
|
|
1:已销户
|
|
2:已结转长期不动户
|
|
';
|
|
comment on column T_DPM_OUTER_ACCOUNT.account_attribute
|
|
is '账户属性 1:对私
|
|
2:对公';
|
|
comment on column T_DPM_OUTER_ACCOUNT.curr_bal_direction
|
|
is '当前余额方向 1:借
|
|
2:贷';
|
|
comment on column T_DPM_OUTER_ACCOUNT.bal_direction
|
|
is '账户余额方向 1:借
|
|
2:贷
|
|
0:双向';
|
|
comment on column T_DPM_OUTER_ACCOUNT.currency_code
|
|
is '货币类型';
|
|
comment on column T_DPM_OUTER_ACCOUNT.last_update_time
|
|
is '更新时间';
|
|
comment on column T_DPM_OUTER_ACCOUNT.request_no
|
|
is '开外部户请求请求号:不可为空 需唯一';
|
|
create index IDX_OA_MEMBER_ID on T_DPM_OUTER_ACCOUNT (MEMBER_ID);
|
|
create index IDX_T_DPM_OUTER_ACCOUNT on T_DPM_OUTER_ACCOUNT (OPEN_DATE);
|
|
create unique index UIDX_DOAO_RO on T_DPM_OUTER_ACCOUNT (REQUEST_NO);
|
|
alter table T_DPM_OUTER_ACCOUNT
|
|
add constraint PK_T_DPM_OUTER_ACCOUNT primary key (ACCOUNT_NO);
|
|
grant select, insert, update, delete on T_DPM_OUTER_ACCOUNT to DPMUSER;
|
|
grant select, insert, update, delete on T_DPM_OUTER_ACCOUNT to TESTRW;
|
|
|
|
|
|
create table T_DPM_OUTER_ACCOUNT_CTRL
|
|
(
|
|
request_no VARCHAR2(32) not null,
|
|
member_id VARCHAR2(32) not null,
|
|
gmt_create TIMESTAMP(6) not null,
|
|
gmt_modified TIMESTAMP(6) not null
|
|
)
|
|
;
|
|
comment on table T_DPM_OUTER_ACCOUNT_CTRL
|
|
is '外部开户约束表';
|
|
comment on column T_DPM_OUTER_ACCOUNT_CTRL.request_no
|
|
is '开外部户请求请求号:不可为空 需唯一';
|
|
comment on column T_DPM_OUTER_ACCOUNT_CTRL.member_id
|
|
is '会员ID';
|
|
comment on column T_DPM_OUTER_ACCOUNT_CTRL.gmt_create
|
|
is '创建时间';
|
|
comment on column T_DPM_OUTER_ACCOUNT_CTRL.gmt_modified
|
|
is '最后修改时间';
|
|
create unique index UIDX_DOA_RO on T_DPM_OUTER_ACCOUNT_CTRL (MEMBER_ID);
|
|
alter table T_DPM_OUTER_ACCOUNT_CTRL
|
|
add constraint PK_T_DPM_OUTER_ACCOUNT_CTRL primary key (REQUEST_NO);
|
|
grant select, insert, update on T_DPM_OUTER_ACCOUNT_CTRL to DPMUSER;
|
|
grant select, insert, update, delete on T_DPM_OUTER_ACCOUNT_CTRL to TESTRW;
|
|
|
|
|
|
create table T_DPM_OUTER_ACCOUNT_DETAIL
|
|
(
|
|
txn_seq_no VARCHAR2(32) not null,
|
|
sys_trace_no VARCHAR2(32),
|
|
accounting_date VARCHAR2(8),
|
|
txn_time TIMESTAMP(6) not null,
|
|
account_no VARCHAR2(32),
|
|
txn_type NUMBER(1),
|
|
txn_dscpt VARCHAR2(256),
|
|
change_type NUMBER(1),
|
|
direction NUMBER(1),
|
|
frozen_flag VARCHAR2(1),
|
|
txn_amt NUMBER(19,4),
|
|
before_amt NUMBER(19,4),
|
|
after_amt NUMBER(19,4),
|
|
entry_seq_no VARCHAR2(32),
|
|
other_account_no VARCHAR2(32),
|
|
old_txn_seq_no VARCHAR2(32),
|
|
remark VARCHAR2(256),
|
|
crdr NUMBER(1),
|
|
product_code VARCHAR2(12),
|
|
pay_code VARCHAR2(12),
|
|
operation_type NUMBER(1),
|
|
delete_sign NUMBER(1),
|
|
suite_no VARCHAR2(32),
|
|
context_voucher_no VARCHAR2(32),
|
|
accounting_rule VARCHAR2(16),
|
|
transaction_no VARCHAR2(32),
|
|
voucher_no VARCHAR2(50)
|
|
)
|
|
partition by range (TXN_TIME) interval (NUMTOYMINTERVAL(1,'MONTH'))
|
|
(
|
|
partition PA_201505 values less than (TIMESTAMP' 2015-06-01 00:00:00')
|
|
tablespace DPM,
|
|
partition PA_201506 values less than (TIMESTAMP' 2015-07-01 00:00:00')
|
|
tablespace DPM,
|
|
partition PA_201507 values less than (TIMESTAMP' 2015-08-01 00:00:00')
|
|
tablespace DPM,
|
|
partition PA_201508 values less than (TIMESTAMP' 2015-09-01 00:00:00')
|
|
tablespace DPM,
|
|
partition PA_201509 values less than (TIMESTAMP' 2015-10-01 00:00:00')
|
|
tablespace DPM,
|
|
partition PA_201510 values less than (TIMESTAMP' 2015-11-01 00:00:00')
|
|
tablespace DPM,
|
|
partition PA_201511 values less than (TIMESTAMP' 2015-12-01 00:00:00')
|
|
tablespace DPM,
|
|
partition PA_201512 values less than (TIMESTAMP' 2016-01-01 00:00:00')
|
|
tablespace DPM,
|
|
partition PA_201601 values less than (TIMESTAMP' 2016-02-01 00:00:00')
|
|
tablespace DPM,
|
|
partition PA_201602 values less than (TIMESTAMP' 2016-03-01 00:00:00')
|
|
tablespace DPM,
|
|
partition PA_201603 values less than (TIMESTAMP' 2016-04-01 00:00:00')
|
|
tablespace DPM,
|
|
partition PA_201604 values less than (TIMESTAMP' 2016-05-01 00:00:00')
|
|
tablespace DPM,
|
|
partition PA_201605 values less than (TIMESTAMP' 2016-06-01 00:00:00')
|
|
tablespace DPM,
|
|
partition SYS_P157 values less than (TIMESTAMP' 2016-07-01 00:00:00')
|
|
tablespace DPM,
|
|
partition SYS_P177 values less than (TIMESTAMP' 2016-08-01 00:00:00')
|
|
tablespace DPM,
|
|
partition SYS_P197 values less than (TIMESTAMP' 2016-09-01 00:00:00')
|
|
tablespace DPM,
|
|
partition SYS_P230 values less than (TIMESTAMP' 2016-10-01 00:00:00')
|
|
tablespace DPM,
|
|
partition SYS_P275 values less than (TIMESTAMP' 2016-11-01 00:00:00')
|
|
tablespace DPM,
|
|
partition SYS_P315 values less than (TIMESTAMP' 2016-12-01 00:00:00')
|
|
tablespace DPM,
|
|
partition SYS_P336 values less than (TIMESTAMP' 2017-01-01 00:00:00')
|
|
tablespace DPM,
|
|
partition SYS_P356 values less than (TIMESTAMP' 2017-02-01 00:00:00')
|
|
tablespace DPM,
|
|
partition SYS_P368 values less than (TIMESTAMP' 2017-03-01 00:00:00')
|
|
tablespace DPM,
|
|
partition SYS_P408 values less than (TIMESTAMP' 2017-04-01 00:00:00')
|
|
tablespace DPM,
|
|
partition SYS_P428 values less than (TIMESTAMP' 2017-05-01 00:00:00')
|
|
tablespace DPM,
|
|
partition SYS_P448 values less than (TIMESTAMP' 2017-06-01 00:00:00')
|
|
tablespace DPM,
|
|
partition SYS_P468 values less than (TIMESTAMP' 2017-07-01 00:00:00')
|
|
tablespace DPM,
|
|
partition SYS_P508 values less than (TIMESTAMP' 2017-08-01 00:00:00')
|
|
tablespace DPM,
|
|
partition SYS_P548 values less than (TIMESTAMP' 2017-09-01 00:00:00')
|
|
tablespace DPM,
|
|
partition SYS_P568 values less than (TIMESTAMP' 2017-10-01 00:00:00')
|
|
tablespace DPM,
|
|
partition SYS_P589 values less than (TIMESTAMP' 2017-11-01 00:00:00')
|
|
tablespace DPM,
|
|
partition SYS_P629 values less than (TIMESTAMP' 2017-12-01 00:00:00')
|
|
tablespace DPM,
|
|
partition SYS_P656 values less than (TIMESTAMP' 2018-01-01 00:00:00')
|
|
tablespace DPM
|
|
);
|
|
comment on table T_DPM_OUTER_ACCOUNT_DETAIL
|
|
is '客户分户账余额明细';
|
|
comment on column T_DPM_OUTER_ACCOUNT_DETAIL.txn_seq_no
|
|
is '明细流水号';
|
|
comment on column T_DPM_OUTER_ACCOUNT_DETAIL.sys_trace_no
|
|
is '系统跟踪号';
|
|
comment on column T_DPM_OUTER_ACCOUNT_DETAIL.accounting_date
|
|
is '会计日';
|
|
comment on column T_DPM_OUTER_ACCOUNT_DETAIL.account_no
|
|
is '账户号';
|
|
comment on column T_DPM_OUTER_ACCOUNT_DETAIL.txn_type
|
|
is '0:正常
|
|
1:红字
|
|
2:蓝字
|
|
9:抹帐
|
|
';
|
|
comment on column T_DPM_OUTER_ACCOUNT_DETAIL.txn_dscpt
|
|
is '摘要';
|
|
comment on column T_DPM_OUTER_ACCOUNT_DETAIL.change_type
|
|
is '1: 借贷
|
|
2: 冻结解冻';
|
|
comment on column T_DPM_OUTER_ACCOUNT_DETAIL.direction
|
|
is ' 1:加(收入)
|
|
2:减(支出)';
|
|
comment on column T_DPM_OUTER_ACCOUNT_DETAIL.frozen_flag
|
|
is '1:冻结
|
|
2:解冻';
|
|
comment on column T_DPM_OUTER_ACCOUNT_DETAIL.txn_amt
|
|
is '交易金额';
|
|
comment on column T_DPM_OUTER_ACCOUNT_DETAIL.before_amt
|
|
is '交易前余额';
|
|
comment on column T_DPM_OUTER_ACCOUNT_DETAIL.after_amt
|
|
is '交易后余额';
|
|
comment on column T_DPM_OUTER_ACCOUNT_DETAIL.entry_seq_no
|
|
is '分录号';
|
|
comment on column T_DPM_OUTER_ACCOUNT_DETAIL.remark
|
|
is '备注';
|
|
comment on column T_DPM_OUTER_ACCOUNT_DETAIL.crdr
|
|
is '借贷标志 1借2贷';
|
|
comment on column T_DPM_OUTER_ACCOUNT_DETAIL.product_code
|
|
is 'PE产品编码';
|
|
comment on column T_DPM_OUTER_ACCOUNT_DETAIL.pay_code
|
|
is 'PE支付编码';
|
|
comment on column T_DPM_OUTER_ACCOUNT_DETAIL.operation_type
|
|
is '操作类型';
|
|
comment on column T_DPM_OUTER_ACCOUNT_DETAIL.delete_sign
|
|
is '删除标记(冲正用)';
|
|
comment on column T_DPM_OUTER_ACCOUNT_DETAIL.suite_no
|
|
is '套号';
|
|
comment on column T_DPM_OUTER_ACCOUNT_DETAIL.context_voucher_no
|
|
is '关联结算流水号';
|
|
comment on column T_DPM_OUTER_ACCOUNT_DETAIL.accounting_rule
|
|
is ' DEFAULT(0, "减钱(付款方):先贷记账户后借记账户,加钱(收款方):借记账户"),
|
|
DR(1, "只借记子账户"),
|
|
CR(2, "只贷记子账户"),
|
|
CRDR(3, "减钱(付款方):先贷记账户后借记账户,加钱(收款方):出款方借记到收款方借记,出款方贷记到收款方贷记")';
|
|
create index IDX_DOAD_AN on T_DPM_OUTER_ACCOUNT_DETAIL (ACCOUNT_NO)
|
|
local;
|
|
create index IDX_DOAD_STN on T_DPM_OUTER_ACCOUNT_DETAIL (SYS_TRACE_NO)
|
|
local;
|
|
create index IDX_DOA_AN_TT on T_DPM_OUTER_ACCOUNT_DETAIL (ACCOUNT_NO, TXN_TIME)
|
|
local;
|
|
create index IDX_DOA_TT on T_DPM_OUTER_ACCOUNT_DETAIL (TXN_TIME);
|
|
create unique index UIDX_OAD_VO on T_DPM_OUTER_ACCOUNT_DETAIL (VOUCHER_NO);
|
|
alter table T_DPM_OUTER_ACCOUNT_DETAIL
|
|
add constraint PK_T_DPM_OUTER_ACCOUNT_DETAIL primary key (TXN_SEQ_NO);
|
|
grant select, insert, update, delete on T_DPM_OUTER_ACCOUNT_DETAIL to DPMUSER;
|
|
grant select, insert, update, delete on T_DPM_OUTER_ACCOUNT_DETAIL to TESTRW;
|
|
|
|
|
|
create table T_DPM_OUTER_ACCOUNT_DIARY
|
|
(
|
|
account_no VARCHAR2(32) not null,
|
|
account_date VARCHAR2(8) not null,
|
|
begin_dr_bal NUMBER(19,4) default 0,
|
|
begin_cr_bal NUMBER(19,4) default 0,
|
|
begin_dr_avail_bal NUMBER(19,4) default 0,
|
|
begin_cr_avail_bal NUMBER(19,4) default 0,
|
|
debit_amt NUMBER(19,4) default 0,
|
|
credit_amt NUMBER(19,4) default 0,
|
|
debit_cnt NUMBER(10) default 0,
|
|
credit_cnt NUMBER(10) default 0,
|
|
debit_frozen_amt NUMBER(19,4) default 0,
|
|
credit_frozen_amt NUMBER(19,4) default 0,
|
|
debit_frozen_cnt NUMBER(10) default 0,
|
|
credit_frozen_cnt NUMBER(10) default 0,
|
|
debit_unfrozen_amt NUMBER(19,4) default 0,
|
|
credit_unfrozen_amt NUMBER(19,4) default 0,
|
|
debit_unfrozen_cnt NUMBER(10) default 0,
|
|
credit_enfrozen_cnt NUMBER(10) default 0,
|
|
end_dr_bal NUMBER(19,4) default 0,
|
|
end_cr_bal NUMBER(19,4) default 0,
|
|
end_dr_avail_bal NUMBER(19,4) default 0,
|
|
end_cr_avail_bal NUMBER(19,4) default 0,
|
|
is_final NUMBER(1),
|
|
create_time TIMESTAMP(6),
|
|
last_update_time TIMESTAMP(6)
|
|
)
|
|
;
|
|
comment on column T_DPM_OUTER_ACCOUNT_DIARY.is_final
|
|
is '0:已经扎帐
|
|
1:未扎帐';
|
|
alter table T_DPM_OUTER_ACCOUNT_DIARY
|
|
add constraint PK_T_DPM_OUTER_ACCOUNT_DIARY primary key (ACCOUNT_NO, ACCOUNT_DATE);
|
|
grant select, insert, update, delete on T_DPM_OUTER_ACCOUNT_DIARY to DPMUSER;
|
|
grant select, insert, update, delete on T_DPM_OUTER_ACCOUNT_DIARY to TESTRW;
|
|
|
|
create table T_DPM_OUTER_ACCOUNT_SUBSET
|
|
(
|
|
account_no VARCHAR2(32) not null,
|
|
balance NUMBER(19,4) not null,
|
|
fund_type VARCHAR2(16) not null,
|
|
balance_type NUMBER(1),
|
|
remark VARCHAR2(256),
|
|
create_time TIMESTAMP(6) not null,
|
|
update_time TIMESTAMP(6) not null
|
|
)
|
|
;
|
|
comment on table T_DPM_OUTER_ACCOUNT_SUBSET
|
|
is '外部账户分户表';
|
|
comment on column T_DPM_OUTER_ACCOUNT_SUBSET.balance
|
|
is '余额';
|
|
comment on column T_DPM_OUTER_ACCOUNT_SUBSET.fund_type
|
|
is '资金类型
|
|
1,借记
|
|
2.贷记
|
|
';
|
|
comment on column T_DPM_OUTER_ACCOUNT_SUBSET.balance_type
|
|
is '余额类型
|
|
1.普通
|
|
2.冻结';
|
|
comment on column T_DPM_OUTER_ACCOUNT_SUBSET.remark
|
|
is '备注';
|
|
comment on column T_DPM_OUTER_ACCOUNT_SUBSET.create_time
|
|
is '创建时间';
|
|
comment on column T_DPM_OUTER_ACCOUNT_SUBSET.update_time
|
|
is '更新时间';
|
|
alter table T_DPM_OUTER_ACCOUNT_SUBSET
|
|
add constraint UK_TDOAS_ACCOUNT_NO primary key (ACCOUNT_NO, FUND_TYPE);
|
|
grant select, insert, update on T_DPM_OUTER_ACCOUNT_SUBSET to DPMUSER;
|
|
grant select, insert, update, delete on T_DPM_OUTER_ACCOUNT_SUBSET to TESTRW;
|
|
|
|
|
|
create table T_DPM_OUTER_ACCOUNT_SUB_DETAIL
|
|
(
|
|
account_subset_log_id NUMBER(32) not null,
|
|
account_no VARCHAR2(32) not null,
|
|
fund_type VARCHAR2(16) not null,
|
|
balance_type NUMBER(1) not null,
|
|
sys_trace_no VARCHAR2(32),
|
|
voucher_no VARCHAR2(50) not null,
|
|
txn_amt NUMBER(19,4),
|
|
after_amt NUMBER(19,4),
|
|
accounting_rule VARCHAR2(16) not null,
|
|
remark VARCHAR2(256),
|
|
create_time TIMESTAMP(6) not null,
|
|
update_time TIMESTAMP(6) not null,
|
|
direction NUMBER(1) not null
|
|
)
|
|
partition by range (CREATE_TIME) interval (NUMTOYMINTERVAL(1,'MONTH'))
|
|
(
|
|
partition PA_201505 values less than (TIMESTAMP' 2015-06-01 00:00:00')
|
|
tablespace DPM,
|
|
partition PA_201506 values less than (TIMESTAMP' 2015-07-01 00:00:00')
|
|
tablespace DPM,
|
|
partition PA_201507 values less than (TIMESTAMP' 2015-08-01 00:00:00')
|
|
tablespace DPM,
|
|
partition PA_201508 values less than (TIMESTAMP' 2015-09-01 00:00:00')
|
|
tablespace DPM,
|
|
partition PA_201509 values less than (TIMESTAMP' 2015-10-01 00:00:00')
|
|
tablespace DPM,
|
|
partition PA_201510 values less than (TIMESTAMP' 2015-11-01 00:00:00')
|
|
tablespace DPM,
|
|
partition PA_201511 values less than (TIMESTAMP' 2015-12-01 00:00:00')
|
|
tablespace DPM,
|
|
partition PA_201512 values less than (TIMESTAMP' 2016-01-01 00:00:00')
|
|
tablespace DPM,
|
|
partition PA_201601 values less than (TIMESTAMP' 2016-02-01 00:00:00')
|
|
tablespace DPM,
|
|
partition PA_201602 values less than (TIMESTAMP' 2016-03-01 00:00:00')
|
|
tablespace DPM,
|
|
partition PA_201603 values less than (TIMESTAMP' 2016-04-01 00:00:00')
|
|
tablespace DPM,
|
|
partition PA_201604 values less than (TIMESTAMP' 2016-05-01 00:00:00')
|
|
tablespace DPM,
|
|
partition PA_201605 values less than (TIMESTAMP' 2016-06-01 00:00:00')
|
|
tablespace DPM,
|
|
partition SYS_P158 values less than (TIMESTAMP' 2016-07-01 00:00:00')
|
|
tablespace DPM,
|
|
partition SYS_P178 values less than (TIMESTAMP' 2016-08-01 00:00:00')
|
|
tablespace DPM,
|
|
partition SYS_P198 values less than (TIMESTAMP' 2016-09-01 00:00:00')
|
|
tablespace DPM,
|
|
partition SYS_P231 values less than (TIMESTAMP' 2016-10-01 00:00:00')
|
|
tablespace DPM,
|
|
partition SYS_P276 values less than (TIMESTAMP' 2016-11-01 00:00:00')
|
|
tablespace DPM,
|
|
partition SYS_P316 values less than (TIMESTAMP' 2016-12-01 00:00:00')
|
|
tablespace DPM,
|
|
partition SYS_P337 values less than (TIMESTAMP' 2017-01-01 00:00:00')
|
|
tablespace DPM,
|
|
partition SYS_P357 values less than (TIMESTAMP' 2017-02-01 00:00:00')
|
|
tablespace DPM,
|
|
partition SYS_P369 values less than (TIMESTAMP' 2017-03-01 00:00:00')
|
|
tablespace DPM,
|
|
partition SYS_P409 values less than (TIMESTAMP' 2017-04-01 00:00:00')
|
|
tablespace DPM,
|
|
partition SYS_P429 values less than (TIMESTAMP' 2017-05-01 00:00:00')
|
|
tablespace DPM,
|
|
partition SYS_P449 values less than (TIMESTAMP' 2017-06-01 00:00:00')
|
|
tablespace DPM,
|
|
partition SYS_P469 values less than (TIMESTAMP' 2017-07-01 00:00:00')
|
|
tablespace DPM,
|
|
partition SYS_P509 values less than (TIMESTAMP' 2017-08-01 00:00:00')
|
|
tablespace DPM,
|
|
partition SYS_P549 values less than (TIMESTAMP' 2017-09-01 00:00:00')
|
|
tablespace DPM,
|
|
partition SYS_P569 values less than (TIMESTAMP' 2017-10-01 00:00:00')
|
|
tablespace DPM,
|
|
partition SYS_P590 values less than (TIMESTAMP' 2017-11-01 00:00:00')
|
|
tablespace DPM,
|
|
partition SYS_P630 values less than (TIMESTAMP' 2017-12-01 00:00:00')
|
|
tablespace DPM,
|
|
partition SYS_P657 values less than (TIMESTAMP' 2018-01-01 00:00:00')
|
|
tablespace DPM
|
|
);
|
|
comment on table T_DPM_OUTER_ACCOUNT_SUB_DETAIL
|
|
is '外部账户分户账户明细';
|
|
comment on column T_DPM_OUTER_ACCOUNT_SUB_DETAIL.account_subset_log_id
|
|
is '分户账户余额明细';
|
|
comment on column T_DPM_OUTER_ACCOUNT_SUB_DETAIL.account_no
|
|
is '账户号';
|
|
comment on column T_DPM_OUTER_ACCOUNT_SUB_DETAIL.fund_type
|
|
is '资金类型
|
|
1,借记
|
|
2.贷记
|
|
';
|
|
comment on column T_DPM_OUTER_ACCOUNT_SUB_DETAIL.balance_type
|
|
is '余额类型
|
|
1.普通
|
|
2.冻结';
|
|
comment on column T_DPM_OUTER_ACCOUNT_SUB_DETAIL.sys_trace_no
|
|
is '系统跟踪号';
|
|
comment on column T_DPM_OUTER_ACCOUNT_SUB_DETAIL.txn_amt
|
|
is '交易金额';
|
|
comment on column T_DPM_OUTER_ACCOUNT_SUB_DETAIL.after_amt
|
|
is '交易后余额';
|
|
comment on column T_DPM_OUTER_ACCOUNT_SUB_DETAIL.remark
|
|
is '备注';
|
|
comment on column T_DPM_OUTER_ACCOUNT_SUB_DETAIL.create_time
|
|
is '创建时间';
|
|
comment on column T_DPM_OUTER_ACCOUNT_SUB_DETAIL.update_time
|
|
is '更新时间';
|
|
comment on column T_DPM_OUTER_ACCOUNT_SUB_DETAIL.direction
|
|
is ' 1:加(收入)
|
|
2:减(支出)';
|
|
create index IDX_ACCOUNT_SUB_DTL_VOUCH on T_DPM_OUTER_ACCOUNT_SUB_DETAIL (VOUCHER_NO)
|
|
local;
|
|
alter table T_DPM_OUTER_ACCOUNT_SUB_DETAIL
|
|
add constraint ACCOUNT_SUBSET_LOG_ID_PK primary key (ACCOUNT_SUBSET_LOG_ID);
|
|
grant select, insert, update on T_DPM_OUTER_ACCOUNT_SUB_DETAIL to DPMUSER;
|
|
grant select, insert, update, delete on T_DPM_OUTER_ACCOUNT_SUB_DETAIL to TESTRW;
|
|
|
|
create table T_DPM_PAY_PACKAGE
|
|
(
|
|
package_no VARCHAR2(32) not null,
|
|
app_id VARCHAR2(10),
|
|
accounting_model NUMBER(1) not null,
|
|
asyn NUMBER(1) not null,
|
|
mac VARCHAR2(32),
|
|
total_cnt NUMBER(6),
|
|
success_cnt NUMBER(6),
|
|
fail_cnt NUMBER(6),
|
|
status NUMBER(1) not null,
|
|
accounting_desc VARCHAR2(128),
|
|
retry_cnt NUMBER(6),
|
|
notify_url VARCHAR2(128),
|
|
notify_status NUMBER(1),
|
|
notify_desc VARCHAR2(64),
|
|
create_time TIMESTAMP(6),
|
|
last_update_time TIMESTAMP(6),
|
|
remark VARCHAR2(256)
|
|
)
|
|
;
|
|
comment on column T_DPM_PAY_PACKAGE.accounting_model
|
|
is '0:自动
|
|
1:一个事务内完成
|
|
2：逐笔完成';
|
|
comment on column T_DPM_PAY_PACKAGE.asyn
|
|
is '0:同步
|
|
1:异步';
|
|
comment on column T_DPM_PAY_PACKAGE.status
|
|
is '0:初始
|
|
1：处理中
|
|
2:入账完成(成功)
|
|
3:入账失败,处理中
|
|
4:入账完成(失败)';
|
|
comment on column T_DPM_PAY_PACKAGE.notify_status
|
|
is '0:初始
|
|
1:通知成功
|
|
2:通知失败';
|
|
comment on column T_DPM_PAY_PACKAGE.remark
|
|
is '备注';
|
|
alter table T_DPM_PAY_PACKAGE
|
|
add constraint PK_T_DPM_PAY_PACKAGE primary key (PACKAGE_NO);
|
|
grant select, insert, update, delete on T_DPM_PAY_PACKAGE to DPMUSER;
|
|
grant select, insert, update, delete on T_DPM_PAY_PACKAGE to TESTRW;
|
|
|
|
create table T_DPM_PAY_TRANSACTION
|
|
(
|
|
transaction_id VARCHAR2(32) not null,
|
|
package_no VARCHAR2(32),
|
|
transaction_no VARCHAR2(32) not null,
|
|
app_id VARCHAR2(10),
|
|
sys_trace_no VARCHAR2(32),
|
|
txn_type NUMBER(1) not null,
|
|
product_code VARCHAR2(12),
|
|
pay_code VARCHAR2(12),
|
|
status NUMBER(1) not null,
|
|
accounting_date VARCHAR2(8) not null,
|
|
create_time TIMESTAMP(6) not null,
|
|
last_update_time TIMESTAMP(6),
|
|
remark VARCHAR2(256),
|
|
context_trans_no VARCHAR2(32)
|
|
)
|
|
partition by range (CREATE_TIME) interval (NUMTOYMINTERVAL(1,'MONTH'))
|
|
(
|
|
partition PA_201505 values less than (TIMESTAMP' 2015-06-01 00:00:00')
|
|
tablespace DPM,
|
|
partition PA_201506 values less than (TIMESTAMP' 2015-07-01 00:00:00')
|
|
tablespace DPM,
|
|
partition PA_201507 values less than (TIMESTAMP' 2015-08-01 00:00:00')
|
|
tablespace DPM,
|
|
partition PA_201508 values less than (TIMESTAMP' 2015-09-01 00:00:00')
|
|
tablespace DPM,
|
|
partition PA_201509 values less than (TIMESTAMP' 2015-10-01 00:00:00')
|
|
tablespace DPM,
|
|
partition PA_201510 values less than (TIMESTAMP' 2015-11-01 00:00:00')
|
|
tablespace DPM,
|
|
partition PA_201511 values less than (TIMESTAMP' 2015-12-01 00:00:00')
|
|
tablespace DPM,
|
|
partition PA_201512 values less than (TIMESTAMP' 2016-01-01 00:00:00')
|
|
tablespace DPM,
|
|
partition PA_201601 values less than (TIMESTAMP' 2016-02-01 00:00:00')
|
|
tablespace DPM,
|
|
partition PA_201602 values less than (TIMESTAMP' 2016-03-01 00:00:00')
|
|
tablespace DPM,
|
|
partition PA_201603 values less than (TIMESTAMP' 2016-04-01 00:00:00')
|
|
tablespace DPM,
|
|
partition PA_201604 values less than (TIMESTAMP' 2016-05-01 00:00:00')
|
|
tablespace DPM,
|
|
partition PA_201605 values less than (TIMESTAMP' 2016-06-01 00:00:00')
|
|
tablespace DPM,
|
|
partition SYS_P154 values less than (TIMESTAMP' 2016-07-01 00:00:00')
|
|
tablespace DPM,
|
|
partition SYS_P174 values less than (TIMESTAMP' 2016-08-01 00:00:00')
|
|
tablespace DPM,
|
|
partition SYS_P194 values less than (TIMESTAMP' 2016-09-01 00:00:00')
|
|
tablespace DPM,
|
|
partition SYS_P228 values less than (TIMESTAMP' 2016-10-01 00:00:00')
|
|
tablespace DPM,
|
|
partition SYS_P272 values less than (TIMESTAMP' 2016-11-01 00:00:00')
|
|
tablespace DPM,
|
|
partition SYS_P312 values less than (TIMESTAMP' 2016-12-01 00:00:00')
|
|
tablespace DPM,
|
|
partition SYS_P333 values less than (TIMESTAMP' 2017-01-01 00:00:00')
|
|
tablespace DPM,
|
|
partition SYS_P353 values less than (TIMESTAMP' 2017-02-01 00:00:00')
|
|
tablespace DPM,
|
|
partition SYS_P367 values less than (TIMESTAMP' 2017-03-01 00:00:00')
|
|
tablespace DPM,
|
|
partition SYS_P407 values less than (TIMESTAMP' 2017-04-01 00:00:00')
|
|
tablespace DPM,
|
|
partition SYS_P427 values less than (TIMESTAMP' 2017-05-01 00:00:00')
|
|
tablespace DPM,
|
|
partition SYS_P447 values less than (TIMESTAMP' 2017-06-01 00:00:00')
|
|
tablespace DPM,
|
|
partition SYS_P467 values less than (TIMESTAMP' 2017-07-01 00:00:00')
|
|
tablespace DPM,
|
|
partition SYS_P507 values less than (TIMESTAMP' 2017-08-01 00:00:00')
|
|
tablespace DPM,
|
|
partition SYS_P547 values less than (TIMESTAMP' 2017-09-01 00:00:00')
|
|
tablespace DPM,
|
|
partition SYS_P567 values less than (TIMESTAMP' 2017-10-01 00:00:00')
|
|
tablespace DPM,
|
|
partition SYS_P588 values less than (TIMESTAMP' 2017-11-01 00:00:00')
|
|
tablespace DPM,
|
|
partition SYS_P628 values less than (TIMESTAMP' 2017-12-01 00:00:00')
|
|
tablespace DPM,
|
|
partition SYS_P653 values less than (TIMESTAMP' 2018-01-01 00:00:00')
|
|
tablespace DPM
|
|
);
|
|
comment on column T_DPM_PAY_TRANSACTION.package_no
|
|
is '包号，批量入帐时用';
|
|
comment on column T_DPM_PAY_TRANSACTION.transaction_no
|
|
is '事务流水号(PE支付流水号)';
|
|
comment on column T_DPM_PAY_TRANSACTION.app_id
|
|
is '应用ID';
|
|
comment on column T_DPM_PAY_TRANSACTION.sys_trace_no
|
|
is '入账请求流水号(CS清算ID)';
|
|
comment on column T_DPM_PAY_TRANSACTION.txn_type
|
|
is '0:正常
|
|
1:红字
|
|
2:蓝字
|
|
9:抹帐';
|
|
comment on column T_DPM_PAY_TRANSACTION.product_code
|
|
is 'PE产品编码';
|
|
comment on column T_DPM_PAY_TRANSACTION.pay_code
|
|
is 'PE支付编码';
|
|
comment on column T_DPM_PAY_TRANSACTION.status
|
|
is '0:初始
|
|
1：处理中
|
|
2:入账完成(成功)
|
|
3:入账失败,处理中
|
|
4:入账完成(失败)';
|
|
comment on column T_DPM_PAY_TRANSACTION.accounting_date
|
|
is '会计日';
|
|
comment on column T_DPM_PAY_TRANSACTION.create_time
|
|
is '插入时间';
|
|
comment on column T_DPM_PAY_TRANSACTION.last_update_time
|
|
is '更新时间';
|
|
comment on column T_DPM_PAY_TRANSACTION.remark
|
|
is '备注';
|
|
comment on column T_DPM_PAY_TRANSACTION.context_trans_no
|
|
is '关联事务号，回滚用?';
|
|
create index IDX_DPT_TO on T_DPM_PAY_TRANSACTION (SYS_TRACE_NO)
|
|
local;
|
|
create unique index UDX_PAYSEQNO_CLEARINGNO on T_DPM_PAY_TRANSACTION (TRANSACTION_NO);
|
|
alter table T_DPM_PAY_TRANSACTION
|
|
add constraint PK_T_DPM_PAY_TRANSACTION primary key (TRANSACTION_ID);
|
|
grant select, insert, update, delete on T_DPM_PAY_TRANSACTION to DPMUSER;
|
|
grant select, insert, update, delete on T_DPM_PAY_TRANSACTION to TESTRW;
|
|
|
|
prompt Creating T_DPM_SUBACC_FUNDATTRIBUTE...
|
|
create table T_DPM_SUBACC_FUNDATTRIBUTE
|
|
(
|
|
account_type NUMBER(4) not null,
|
|
currency_code VARCHAR2(3) not null,
|
|
fund_type VARCHAR2(16) not null,
|
|
remark VARCHAR2(200),
|
|
gmt_created TIMESTAMP(6) not null,
|
|
gmt_modified TIMESTAMP(6) not null
|
|
)
|
|
;
|
|
comment on table T_DPM_SUBACC_FUNDATTRIBUTE
|
|
is '子账户资金属性配置表';
|
|
comment on column T_DPM_SUBACC_FUNDATTRIBUTE.account_type
|
|
is '账户类型';
|
|
comment on column T_DPM_SUBACC_FUNDATTRIBUTE.currency_code
|
|
is '币种';
|
|
comment on column T_DPM_SUBACC_FUNDATTRIBUTE.fund_type
|
|
is '资金属性';
|
|
comment on column T_DPM_SUBACC_FUNDATTRIBUTE.remark
|
|
is '备注';
|
|
comment on column T_DPM_SUBACC_FUNDATTRIBUTE.gmt_created
|
|
is '创建时间';
|
|
comment on column T_DPM_SUBACC_FUNDATTRIBUTE.gmt_modified
|
|
is '更新时间';
|
|
alter table T_DPM_SUBACC_FUNDATTRIBUTE
|
|
add constraint UK_TDAF_ACCOUNT_TYPE unique (ACCOUNT_TYPE, CURRENCY_CODE, FUND_TYPE);
|
|
grant select, insert, update, delete on T_DPM_SUBACC_FUNDATTRIBUTE to DPMUSER;
|
|
grant select, insert, update, delete on T_DPM_SUBACC_FUNDATTRIBUTE to TESTRW; |