MrTech/ctg-app-cap
5
0
Fork 0
Code Releases Activity
Files
dev
ctg-app-cap/cap/sql/cre_cap.sql
T
Alawn 82e386afda 大区初始化数据sql
2021-07-29 14:46:37 +08:00

1388 lines
38 KiB
SQL
Raw Permalink Blame History

create table CAPTXNJNL
(
JRN_NO VARCHAR2(40) not null constraint CAPTXNJNL_PK primary key,
TXN_CD VARCHAR2(32) default ' ' not null,
TXN_TYP VARCHAR2(2) default '0' not null,
AC_DT VARCHAR2(8) not null,
CORG_TXN_MOD VARCHAR2(2) default ' ',
TXN_TM VARCHAR2(14) default ' ',
BUS_TYP VARCHAR2(5) default ' ',
MERC_ID VARCHAR2(15) not null,
MERC_TYP VARCHAR2(4) default ' ',
MERC_TRD_CLS VARCHAR2(4) default ' ',
MERC_NAM VARCHAR2(120) default ' ',
PAY_TYP VARCHAR2(2) default ' ',
ORD_TYP VARCHAR2(2) default ' ',
ORD_DT VARCHAR2(8) default ' ',
ORD_TM VARCHAR2(6) default ' ',
ORD_DT_TM VARCHAR2(14) default ' ',
MERC_ORD_NO VARCHAR2(64) default ' ',
MERC_LOG_NO VARCHAR2(32) default ' ',
ID_TYP VARCHAR2(2) default ' ',
ID_NO VARCHAR2(30) default ' ',
CYC_CD VARCHAR2(3) default ' ',
AC_TYP VARCHAR2(2) default ' ',
AC_NO VARCHAR2(30) default ' ',
CRD_NO VARCHAR2(30) default ' ',
BNK_TYP VARCHAR2(4) default ' ',
CUS_NAM VARCHAR2(350) default ' ',
CVV2 VARCHAR2(6) default ' ',
CRD_EXP_DT VARCHAR2(10) default ' ',
CRD_FLG VARCHAR2(2) default ' ',
ISS_ORG_NO VARCHAR2(15) default ' ',
AC_BNK_CD VARCHAR2(10) default ' ',
CD_FLG VARCHAR2(1) default ' ',
BUS_INF VARCHAR2(64) default ' ',
RMK VARCHAR2(120) default ' ',
TXN_AMT NUMBER(13,2) default 0,
REF_AMT NUMBER(13,2) default 0,
DSK_NO VARCHAR2(48) default ' ',
DSK_NAM VARCHAR2(100) default ' ',
DSK_NAM_STS VARCHAR2(1) default '0',
LBNK_NO VARCHAR2(32) default ' ',
LBNK_NAM VARCHAR2(300) default ' ',
TAC_TYP VARCHAR2(4) default ' ',
TAC_NO VARCHAR2(32) default ' ',
CORG_MERC_ID VARCHAR2(32) default ' ',
CORG_NO VARCHAR2(20) default ' ',
COP_BUS_TYP VARCHAR2(2) default ' ',
CORG_NO_NM VARCHAR2(120) default ' ',
CORG_TRM_NO VARCHAR2(32) default ' ',
RTR_SVR VARCHAR2(30) default ' ',
RTR_COD VARCHAR2(30) default ' ',
ORG_NO VARCHAR2(10) default ' ',
CORG_DSK_NO VARCHAR2(60) default '0',
TTXN_CD VARCHAR2(30) default ' ',
TACC_DT VARCHAR2(8) default ' ',
TACC_TM VARCHAR2(14) default ' ',
CAP_ORD_NO VARCHAR2(32) default ' ',
OCAP_ORD_NO VARCHAR2(32) default ' ',
OTACC_DT VARCHAR2(8) default ' ',
TRSP_CD VARCHAR2(200) default ' ',
TRSP_MSG VARCHAR2(500) default ' ',
TTXN_ST VARCHAR2(1) default 'U',
CHK_STS VARCHAR2(1) default '0',
CHK_DT VARCHAR2(8) default ' ',
CHK_BAT_NO VARCHAR2(20) default ' ',
TSTL_DT VARCHAR2(8) default ' ',
TSTL_CYC_CD VARCHAR2(3) default ' ',
TSTL_AMT NUMBER(13,2) default 0,
RSP_CD VARCHAR2(200) default ' ',
RSP_MSG VARCHAR2(500) default ' ',
TXN_STS VARCHAR2(1) default 'U',
TRM_NO VARCHAR2(8) default ' ',
MOBILE VARCHAR2(20) default ' ',
QUERY_ID VARCHAR2(30) default ' ',
SREF_NO VARCHAR2(12) default ' ',
AUT_CD VARCHAR2(6) default ' ',
BAT_NO VARCHAR2(36) default ' ',
BILL_NO VARCHAR2(6) default ' ',
CSEQ_NO VARCHAR2(6) default ' ',
OCSEQ_NO VARCHAR2(6) default ' ',
ACQ_NO VARCHAR2(11) default ' ',
FOR_COD VARCHAR2(11) default ' ',
ORN_JRN_NO NUMBER(18) default 0 not null,
MERC_FEE_CLR_FLG VARCHAR2(1) default '0' not null,
MERC_FEE_CLR_DT VARCHAR2(8) default ' ' not null,
MERC_CLR_FLG VARCHAR2(1) default '0' not null,
MERC_CLR_DT VARCHAR2(8) default ' ' not null,
MERC_FEE_MOD VARCHAR2(1) default '0' not null,
MERC_FEE_CAL_MTH VARCHAR2(1) default ' ',
MERC_FEE_DT VARCHAR2(8) default ' ',
MERC_FEE_AMT NUMBER(13,2) default 0 not null,
MERC_STL_AMT NUMBER(13,2) default 0 not null,
CORG_FEE_CLR_FLG VARCHAR2(1) default '0' not null,
CORG_FEE_CLR_DT VARCHAR2(8) default ' ' not null,
CORG_CLR_FLG VARCHAR2(1) default '0' not null,
CORG_CLR_DT VARCHAR2(8) default ' ' not null,
CORG_FEE_MOD VARCHAR2(1) default '0' not null,
CORG_FEE_CAL_MTH VARCHAR2(1) default ' ',
CORG_FEE_DT VARCHAR2(8) default ' ',
CORG_FEE_AMT NUMBER(13,2) default 0 not null,
CORG_STL_AMT NUMBER(13,2) default 0 not null,
PRO_FEE NUMBER(13,2) default 0 not null,
STL_STS VARCHAR2(1) default '0' not null,
STL_DT VARCHAR2(8) default ' ' not null,
TM_SMP VARCHAR2(26) default SYSDATE not null,
RSV_FLD1 VARCHAR2(100) default ' ',
RSV_FLD2 VARCHAR2(100) default ' ',
RSV_FLD3 VARCHAR2(100) default ' ',
CAP_TYP VARCHAR2(50),
TIM_IVL VARCHAR2(50),
HRSP_CD VARCHAR2(300),
HTXN_ST VARCHAR2(2),
HRSP_MSG VARCHAR2(300),
HLOG_NO VARCHAR2(100),
TXN_CNL VARCHAR2(30),
ACT_PTY VARCHAR2(30),
JRN_INF VARCHAR2(255) default ' ' not null,
RES_FLG VARCHAR2(1) default '0' not null,
RSD_NUM VARCHAR2(4) default '0' not null,
OCORG_DSK_NO VARCHAR2(60),
AMOUNTFLAG VARCHAR2(1),
UNIONFLAG VARCHAR2(1) default '0',
URGENCYFLAG VARCHAR2(1),
BAT_SEQ NUMBER(8),
STATUS CHAR default 'U',
DO_FLG CHAR default 'Y',
MSG_CD CHAR(8),
PROVICE VARCHAR2(10),
CITY VARCHAR2(100),
BANK_NAM VARCHAR2(100),
MER_RSV VARCHAR2(100),
BNK_RSV VARCHAR2(100),
SUMMARY VARCHAR2(50),
APLFLG CHAR default '1',
BANK_CODE VARCHAR2(6),
USR_FEE_DDT_FLG CHAR,
HLD_NO VARCHAR2(20),
TX_FEE NUMBER(13,2),
ACC_VER_STS VARCHAR2(2) default 'U',
ACC_VER_NO VARCHAR2(16) default '',
TXN_MOD VARCHAR2(2) default ' ' not null,
ROUT_FLG VARCHAR2(5),
BR_NO VARCHAR2(20),
POSTSCRIPT VARCHAR2(100),
TRX_SEQ VARCHAR2(8) default ' ' not null,
TAC_NAM VARCHAR2(100),
CORG_JRN_NO VARCHAR2(32) default ' ' not null,
CORG_BUS_TYP VARCHAR2(5) default ' ',
BNK_FLG VARCHAR2(1)
)
/
comment on column CAPTXNJNL.ROUT_FLG is '汇路 SUPER:超网 LARGE:大额 SMALL:小额'
/
comment on column CAPTXNJNL.BR_NO is '分行号'
/
comment on column CAPTXNJNL.POSTSCRIPT is '银行附言'
/
comment on column CAPTXNJNL.TRX_SEQ is '序号'
/
comment on column CAPTXNJNL.TAC_NAM is '付款账户名称'
/
comment on column CAPTXNJNL.CORG_JRN_NO is '银行流水号'
/
comment on column CAPTXNJNL.CORG_BUS_TYP is '第三方业务种类 用于合并记账 1-999'
/
comment on column CAPTXNJNL.BNK_FLG is 'Y:本行 N:跨行'
/
create unique index CAPTXNJNL_UNI1 on CAPTXNJNL (MERC_ORD_NO)
/
create index CAPTXNJNL_NI1 on CAPTXNJNL (ORD_DT,DSK_NAM,MERC_ORD_NO,TXN_STS)
/
create index CAPTXNJNL_NI2 on CAPTXNJNL (ORD_DT,CORG_NO,CORG_DSK_NO)
/
create index CAPTXNJNL_NI3 on CAPTXNJNL (ORD_DT,DSK_NAM)
/
create table CAPDSKINF
(
MERC_ID VARCHAR2(15),
MERC_TYP VARCHAR2(4) default ' ',
MERC_TRD_CLS VARCHAR2(4) default ' ',
MERC_NAM VARCHAR2(120) default ' ',
SYS_ID VARCHAR2(12) default ' ',
BNK_CD VARCHAR2(10),
ARE_CD VARCHAR2(8),
DSK_NO VARCHAR2(48) not null,
DSK_NAM VARCHAR2(50) not null constraint CAPDSKINF_PK primary key,
DSK_DAT VARCHAR2(8) not null,
AC_DT VARCHAR2(8) not null,
FIL_TYP VARCHAR2(8),
TXN_MOD VARCHAR2(2) not null,
ORD_DT VARCHAR2(8) not null,
RCV_DT VARCHAR2(8) not null,
RCV_TM VARCHAR2(14) default ' ' not null,
END_DT VARCHAR2(8) default ' ' not null,
END_TM VARCHAR2(14) default ' ' not null,
ORN_CNT VARCHAR2(5) default '0' not null,
ORN_AMT NUMBER(13,2) default 0 not null,
SUC_CNT VARCHAR2(5) default '0' not null,
SUC_AMT NUMBER(13,2) default 0 not null,
FAL_CNT VARCHAR2(5) default '0' not null,
FAL_AMT NUMBER(13,2) default 0 not null,
DAT_REC VARCHAR2(3) default '0' not null,
DSK_STS VARCHAR2(1) default '0' not null,
TM_SMP VARCHAR2(26) default SYSDATE not null,
RSV_FLD VARCHAR2(100) default ' ' not null,
VER_NO VARCHAR2(16),
CUSTOMER_ID VARCHAR2(48),
TITLE_ID VARCHAR2(48),
MD5_APPLY VARCHAR2(32),
MD5_RESULT VARCHAR2(32),
FILE_ID VARCHAR2(32),
FILE_ID_RESULT VARCHAR2(32),
DISK_DESC VARCHAR2(100),
POSTSCRIPT VARCHAR2(100)
)
/
comment on column CAPDSKINF.DSK_STS is '0-初始，1-批次处理中 9-批次接受失败 4-批次处理成功'
/
comment on column CAPDSKINF.CUSTOMER_ID is '客户编号'
/
comment on column CAPDSKINF.TITLE_ID is '支付地抬头'
/
comment on column CAPDSKINF.MD5_APPLY is '批量代付申请MD5值'
/
comment on column CAPDSKINF.MD5_RESULT is '批量代付结果MD5值'
/
comment on column CAPDSKINF.FILE_ID is '送盘文件ID'
/
comment on column CAPDSKINF.FILE_ID_RESULT is '回盘文件ID'
/
comment on column CAPDSKINF.DISK_DESC is ' 磁盘名称'
/
comment on column CAPDSKINF.POSTSCRIPT is '附言'
/
create index CAPDSKINF_NI1
on CAPDSKINF (ORD_DT,DSK_NAM,DSK_STS)
/
create table CAPBATINF
(
MERC_ID VARCHAR2(15) not null,
CORG_MERC_ID VARCHAR2(32) default ' ' not null,
ORD_DT VARCHAR2(8) not null,
DSK_NAM VARCHAR2(50) not null,
AC_DT VARCHAR2(8) not null,
BAT_TYP VARCHAR2(8) not null,
CORG_NO VARCHAR2(20) not null,
CORG_NO_NM VARCHAR2(120) default ' ' not null,
CORG_DSK_NO VARCHAR2(60) default '0' not null,
CUPS_FIL_NAM VARCHAR2(120) default ' ' not null,
SND_DT VARCHAR2(8) default ' ' not null,
SND_TM VARCHAR2(14) default ' ' not null,
END_DT VARCHAR2(8) default ' ' not null,
END_TM VARCHAR2(14) default ' ' not null,
BAT_ORN_CNT VARCHAR2(5) default '0' not null,
BAT_ORN_AMT NUMBER(13,2) default 0 not null,
BAT_SUC_CNT VARCHAR2(5) default '0' not null,
BAT_SUC_AMT NUMBER(13,2) default 0 not null,
BAT_FAL_CNT VARCHAR2(5) default '0' not null,
BAT_FAL_AMT NUMBER(13,2) default 0 not null,
BAT_STS VARCHAR2(1) default '0' not null,
TM_SMP VARCHAR2(26) default SYSDATE not null,
RSV_FLD VARCHAR2(100) default ' ' not null,
HRSP_CD VARCHAR2(300),
HTXN_ST VARCHAR2(2),
HRSP_MSG VARCHAR2(300),
HTXN_MSG VARCHAR2(300),
HLOG_NO VARCHAR2(100),
RES_TYP VARCHAR2(1) default ' ',
RES_FLG VARCHAR2(1) default '0',
RES_INF VARCHAR2(255) default ' ' not null,
RES_OPR VARCHAR2(30) default ' ' not null,
RSD_NUM VARCHAR2(4) default '0' not null,
OCORG_DSK_NO VARCHAR2(60),
CAP_TYP VARCHAR2(50),
TXN_CNL VARCHAR2(30),
ACT_PTY VARCHAR2(30),
TCORG_DSK_NO VARCHAR2(500),
REG_ID VARCHAR2(30) default ' ' not null,
NOD_ID VARCHAR2(30) default ' ' not null,
PRO_JRL VARCHAR2(20) default '0' not null,
SND_TIME VARCHAR2(30),
CORG_SND_DT VARCHAR2(8) default ' ' not null,
GET_TIME VARCHAR2(30),
BAT_VER_STS VARCHAR2(2) default 'U',
RSP_CD VARCHAR2(300),
RSP_MSG VARCHAR2(300),
CORG_FIL_NAM VARCHAR2(120),
CORG_JRN_NO VARCHAR2(32),
CORG_BUS_TYP VARCHAR2(5) default ' ',
constraint CAPBATINF_PK primary key (CORG_NO, CORG_DSK_NO, AC_DT)
)
/
comment on column CAPBATINF.CORG_JRN_NO is '银行流水号'
/
comment on column CAPBATINF.CORG_BUS_TYP is '第三方业务种类 用于合并记账 1-999'
/
create index CAPBATINF_NI1 on CAPBATINF (ORD_DT,DSK_NAM,BAT_STS)
/
create index CAPBATINF_NI2 on CAPBATINF (ORD_DT,CORG_NO,CORG_DSK_NO,BAT_STS)
/
create index CAPBATINF_NI3 on CAPBATINF (ORD_DT,CORG_NO,BAT_STS,PRO_JRL)
/
create table CAPDAYCTL
(
PSYS_ID VARCHAR2(6) not null,
WRK_DT VARCHAR2(8) not null,
STR_TM VARCHAR2(6) default ' ' not null,
END_TM VARCHAR2(6) default ' ' not null,
CLR_STS VARCHAR2(1) default '0' not null,
MERC_STR_TM VARCHAR2(6) default ' ' not null,
MERC_END_TM VARCHAR2(6) default ' ' not null,
MERC_CLR_STS VARCHAR2(1) default '0' not null,
ORG_STR_TM VARCHAR2(6) default ' ' not null,
ORG_END_TM VARCHAR2(6) default ' ' not null,
ORG_CLR_STS VARCHAR2(1) default '0' not null,
TM_SMP VARCHAR2(26) default SYSDATE not null,
constraint SYS_C0011853
primary key (PSYS_ID, WRK_DT)
)
/
create index CAPDAYCTL_NI1
on CAPDAYCTL (CLR_STS)
/
create index CAPDAYCTL_NI2
on CAPDAYCTL (MERC_CLR_STS)
/
create table CAPSTLDTL
(
JRN_NO NUMBER(18) not null
constraint CAPSTLDTL_PK
primary key,
TXN_CD VARCHAR2(10) not null,
TXN_TYP VARCHAR2(2) default 'N' not null,
AC_DT VARCHAR2(8) not null,
TXN_MOD VARCHAR2(2) not null,
CORG_TXN_MOD VARCHAR2(2) default ' ' not null,
TXN_TM VARCHAR2(14) default ' ' not null,
BUS_TYP VARCHAR2(5) default ' ' not null,
MERC_ID VARCHAR2(15) not null,
MERC_TYP VARCHAR2(4) default ' ' not null,
MERC_TRD_CLS VARCHAR2(4) default ' ' not null,
MERC_NAM VARCHAR2(120) default ' ' not null,
PAY_TYP VARCHAR2(2) default ' ' not null,
ORD_TYP VARCHAR2(2) default ' ' not null,
ORD_DT VARCHAR2(8) default ' ' not null,
ORD_TM VARCHAR2(6) default ' ' not null,
ORD_DT_TM VARCHAR2(14) default ' ' not null,
CYC_CD VARCHAR2(3) default ' ' not null,
AC_TYP VARCHAR2(2) default ' ' not null,
AC_NO VARCHAR2(30) default ' ' not null,
CRD_NO VARCHAR2(30) default ' ' not null,
BNK_TYP VARCHAR2(4) default ' ' not null,
CRD_FLG VARCHAR2(2) default ' ' not null,
ISS_ORG_NO VARCHAR2(15) default ' ' not null,
TXN_AMT NUMBER(13,2) default 0 not null,
REF_AMT NUMBER(13,2) default 0 not null,
LBNK_NO VARCHAR2(32) default ' ' not null,
TAC_TYP VARCHAR2(4) default ' ' not null,
TAC_NO VARCHAR2(32) default ' ' not null,
CORG_MERC_ID VARCHAR2(32) default ' ' not null,
CORG_NO VARCHAR2(20) default ' ' not null,
COP_BUS_TYP VARCHAR2(2) default ' ' not null,
CORG_NO_NM VARCHAR2(120) default ' ' not null,
ORG_NO VARCHAR2(10) default ' ' not null,
TTXN_CD VARCHAR2(10) default ' ' not null,
TACC_DT VARCHAR2(8) default ' ' not null,
TACC_TM VARCHAR2(14) default ' ' not null,
OTACC_DT VARCHAR2(8) default ' ' not null,
TSTL_DT VARCHAR2(8) default ' ' not null,
TSTL_CYC_CD VARCHAR2(3) default ' ' not null,
TSTL_AMT NUMBER(13,2) default 0 not null,
TXN_STS VARCHAR2(1) default 'U' not null,
ORN_JRN_NO NUMBER(18) default 0 not null,
MERC_CLR_FLG VARCHAR2(1) default '0' not null,
MERC_CLR_DT VARCHAR2(8) default ' ' not null,
MERC_FEE_MOD VARCHAR2(1) default '0' not null,
MERC_FEE_CAL_MTH VARCHAR2(1) default ' ' not null,
MERC_FEE_DT VARCHAR2(8) default ' ' not null,
MERC_FEE_AMT NUMBER(13,2) default 0 not null,
MERC_STL_AMT NUMBER(13,2) default 0 not null,
CORG_CLR_FLG VARCHAR2(1) default '0' not null,
CORG_CLR_DT VARCHAR2(8) default ' ' not null,
CORG_FEE_MOD VARCHAR2(1) default '0' not null,
CORG_FEE_CAL_MTH VARCHAR2(1) default ' ' not null,
CORG_FEE_DT VARCHAR2(8) default ' ' not null,
CORG_FEE_AMT NUMBER(13,2) default 0 not null,
CORG_STL_AMT NUMBER(13,2) default 0 not null,
PRO_FEE NUMBER(13,2) default 0 not null,
STL_STS VARCHAR2(1) default '0' not null,
STL_DT VARCHAR2(8) default ' ' not null,
TM_SMP VARCHAR2(26) default SYSDATE not null,
RSV_FLD1 VARCHAR2(100) default ' ' not null,
RSV_FLD2 VARCHAR2(100) default ' ' not null,
RSV_FLD3 VARCHAR2(100) default ' ' not null,
ACC_VER_STS VARCHAR2(2) default 'U',
STL_VER VARCHAR2(1) default '0',
VER_FEE_AMT NUMBER(13,2) default 0,
CORG_FEE_CLR_DT VARCHAR2(8),
CORG_FEE_CLR_FLG VARCHAR2(1),
MERC_FEE_CLR_DT VARCHAR2(8),
MERC_FEE_CLR_FLG VARCHAR2(1)
)
/
create index CAPSTLDTL_NI1
on CAPSTLDTL (MERC_ID, TXN_AMT, MERC_CLR_FLG, TXN_TYP, TXN_STS)
/
create index CAPSTLDTL_NI2
on CAPSTLDTL (CORG_NO, TXN_AMT, CORG_CLR_FLG, TXN_TYP, TXN_STS)
/
create table CAPSTLFEE
(
MERC_ID VARCHAR2(15) not null,
MERC_TYP VARCHAR2(4) default ' ' not null,
MERC_TRD_CLS VARCHAR2(4) default ' ' not null,
MERC_NAM VARCHAR2(120) default ' ' not null,
REC_MERC_FEE_AMT NUMBER(13,2) default 0 not null,
PAY_MERC_FEE_AMT NUMBER(13,2) default 0 not null,
BUS_TYP VARCHAR2(5) default ' ' not null,
TO_TXN_AMT NUMBER(13,2) default 0 not null,
FEE_STL_DT VARCHAR2(8) not null,
FEE_STL_CYC_CD VARCHAR2(3) default ' ' not null,
TO_MERC_SUCC_CNT VARCHAR2(10) default ' ' not null,
TO_MERC_FEE_AMT NUMBER(13,2) default 0 not null,
FEE_PERD_UNIT VARCHAR2(1) default ' ' not null,
FEE_PERD_NUM VARCHAR2(1) default ' ' not null,
MERC_FEE_MOD VARCHAR2(1) default ' ' not null,
MERC_FEE_CAL_MTH VARCHAR2(1) default ' ' not null,
FEE_STL_STT_DT VARCHAR2(8) default ' ' not null,
FEE_STL_END_DT VARCHAR2(8) default ' ' not null,
STL_MOD VARCHAR2(1) default ' ' not null,
FEE_MERC_STS VARCHAR2(1) default 'U' not null,
TM_SMP VARCHAR2(26) default SYSDATE not null,
RSV_FLD1 VARCHAR2(100) default ' ' not null,
RSV_FLD2 VARCHAR2(100) default ' ' not null,
RSV_FLD3 VARCHAR2(100) default ' ' not null,
TO_MERC_AMT NUMBER(13,2) not null,
constraint CAPSTLFEE_PK
primary key (MERC_ID, FEE_STL_DT)
)
/
create index CAPSTLFEE_NI1
on CAPSTLFEE (MERC_ID, FEE_STL_DT, TO_TXN_AMT)
/
create index CAPSTLFEE_NI2
on CAPSTLFEE (MERC_ID, FEE_STL_DT, TO_MERC_FEE_AMT)
/
create table CAPSTLREG
(
MERC_ID VARCHAR2(15) not null,
MERC_TYP VARCHAR2(4) default ' ' not null,
MERC_TRD_CLS VARCHAR2(4) default ' ' not null,
MERC_NAM VARCHAR2(120) default ' ' not null,
REC_MERC_FEE_AMT NUMBER(13,2) default 0 not null,
PAY_MERC_FEE_AMT NUMBER(13,2) default 0 not null,
TO_REC_MERC_AMT NUMBER(13,2) default 0 not null,
TO_PAY_MERC_AMT NUMBER(13,2) default 0 not null,
BUS_TYP VARCHAR2(5) default ' ' not null,
TO_TXN_AMT NUMBER(13,2) default 0 not null,
STL_DT VARCHAR2(8) not null,
STL_CYC_CD VARCHAR2(3) default ' ' not null,
TO_MERC_SUCC_CNT VARCHAR2(10) default ' ' not null,
TO_MERC_FEE_AMT NUMBER(13,2) default 0 not null,
TO_MERC_STL_AMT NUMBER(13,2) default 0 not null,
MERC_FEE_MOD VARCHAR2(1) default ' ' not null,
MERC_FEE_CAL_MTH VARCHAR2(1) default ' ' not null,
STL_STT_DT VARCHAR2(8) default ' ' not null,
STL_END_DT VARCHAR2(8) default ' ' not null,
STL_MOD VARCHAR2(1) not null,
STL_PERD VARCHAR2(1) default ' ' not null,
STL_DAY VARCHAR2(3) default ' ' not null,
STL_TRF_DAYS VARCHAR2(3) default ' ' not null,
MERC_STS VARCHAR2(20) default 'U' not null,
TM_SMP VARCHAR2(26) default SYSDATE not null,
RSV_FLD1 VARCHAR2(100) default ' ' not null,
RSV_FLD2 VARCHAR2(100) default ' ' not null,
RSV_FLD3 VARCHAR2(100) default ' ' not null,
constraint CAPSTLREG_PK
primary key (MERC_ID, STL_DT)
)
/
create index CAPSTLREG_NI1
on CAPSTLREG (MERC_ID, STL_DT, TO_TXN_AMT)
/
create index CAPSTLREG_NI2
on CAPSTLREG (MERC_ID, STL_DT, TO_MERC_FEE_AMT, TO_MERC_STL_AMT)
/
create table CAPAGTFEE
(
CORG_NO VARCHAR2(20) not null,
CORG_NO_NM VARCHAR2(120) default ' ' not null,
REC_CORG_FEE_AMT NUMBER(13,2) default 0 not null,
PAY_CORG_FEE_AMT NUMBER(13,2) default 0 not null,
BUS_TYP VARCHAR2(5) default ' ' not null,
TO_TXN_AMT NUMBER(13,2) default 0 not null,
FEE_STL_DT VARCHAR2(8) not null,
FEE_STL_CYC_CD VARCHAR2(3) default ' ' not null,
TO_CORG_SUCC_CNT VARCHAR2(10) default ' ' not null,
TO_CORG_FEE_AMT NUMBER(13,2) default 0 not null,
FEE_PERD_UNIT VARCHAR2(1) default ' ' not null,
FEE_PERD_NUM VARCHAR2(1) default ' ' not null,
CORG_FEE_MOD VARCHAR2(1) default ' ' not null,
CORG_FEE_CAL_MTH VARCHAR2(1) default ' ' not null,
FEE_STL_STT_DT VARCHAR2(8) default ' ' not null,
FEE_STL_END_DT VARCHAR2(8) default ' ' not null,
STL_MOD VARCHAR2(1) default ' ' not null,
FEE_CORG_STS VARCHAR2(1) default 'U' not null,
TM_SMP VARCHAR2(26) default SYSDATE not null,
RSV_FLD1 VARCHAR2(100) default ' ' not null,
RSV_FLD2 VARCHAR2(100) default ' ' not null,
RSV_FLD3 VARCHAR2(100) default ' ' not null,
CORG_NM VARCHAR2(120),
TO_CORG_AMT VARCHAR2(18) default '0',
ORD_TYP VARCHAR2(10) default ' ',
constraint CAPAGTFEE_PK
primary key (CORG_NO, FEE_STL_DT)
)
/
create index CAPAGTFEE_NI1
on CAPAGTFEE (CORG_NO, FEE_STL_DT, TO_TXN_AMT)
/
create index CAPAGTFEE_NI2
on CAPAGTFEE (CORG_NO, FEE_STL_DT, TO_CORG_FEE_AMT)
/
create table CAPSTLAGT
(
CORG_NO VARCHAR2(20) not null,
CORG_NO_NM VARCHAR2(120) default ' ' not null,
REC_CORG_FEE_AMT NUMBER(13,2) default 0 not null,
PAY_CORGFEE_AMT NUMBER(13,2) default 0 not null,
TO_REC_CORG_AMT NUMBER(13,2) default 0 not null,
TO_PAY_CORG_AMT NUMBER(13,2) default 0 not null,
BUS_TYP VARCHAR2(5) default ' ' not null,
TO_TXN_AMT NUMBER(13,2) default 0 not null,
STL_DT VARCHAR2(8) not null,
STL_CYC_CD VARCHAR2(3) default ' ' not null,
TO_CORGSUCC_CNT VARCHAR2(10) default ' ' not null,
TO_CORG_FEE_AMT NUMBER(13,2) default 0 not null,
TO_CORG_STL_AMT NUMBER(13,2) default 0 not null,
CORG_FEE_MOD VARCHAR2(1) default '0' not null,
CORG_FEE_CAL_MTH VARCHAR2(1) default ' ' not null,
STL_STT_DT VARCHAR2(8) default ' ' not null,
STL_END_DT VARCHAR2(8) default ' ' not null,
STL_MOD VARCHAR2(1) default ' ' not null,
STL_PERD VARCHAR2(1) default ' ' not null,
STL_DAY VARCHAR2(3) default ' ' not null,
STL_TRF_DAYS VARCHAR2(3) default ' ' not null,
CORG_STS VARCHAR2(20) default 'U' not null,
TM_SMP VARCHAR2(26) default SYSDATE not null,
RSV_FLD1 VARCHAR2(100) default ' ' not null,
RSV_FLD2 VARCHAR2(100) default ' ' not null,
RSV_FLD3 VARCHAR2(100) default ' ' not null,
constraint CAPSTLAGT_PK
primary key (CORG_NO, STL_DT)
)
/
create index CAPSTLAGT_NI1
on CAPSTLAGT (CORG_NO, STL_DT, TO_TXN_AMT)
/
create index CAPSTLAGT_NI2
on CAPSTLAGT (CORG_NO, STL_DT, TO_CORG_FEE_AMT, TO_CORG_STL_AMT)
/
create table CAPERRCSW
(
CORP_NO VARCHAR2(12) not null,
TRSP_CD VARCHAR2(8) not null,
RSP_CD VARCHAR2(8) not null,
ERR_RMK VARCHAR2(200) default ' ' not null,
SHOW_TEXT VARCHAR2(200) default ' ' not null,
constraint CAPERRCSW_PK
primary key (CORP_NO, TRSP_CD)
)
/
create index CAPERRCSW_NI1
on CAPERRCSW (RSP_CD, ERR_RMK, SHOW_TEXT)
/
create table CAPDAYPROFIT
(
STL_DT VARCHAR2(8) not null
constraint CAPDAYPROFIT_PK
primary key,
TO_REC_MERC_FEE_AMT NUMBER(13,2) default 0 not null,
TO_PAY_MERC_FEE_AMT NUMBER(13,2) default 0 not null,
TO_ALL_MERC_FEE_AMT NUMBER(13,2) default 0 not null,
TO_REC_CORG_FEE_AMT NUMBER(13,2) default 0 not null,
TO_PAY_CORG_FEE_AMT NUMBER(13,2) default 0 not null,
TO_ALL_CORG_FEE_AMT NUMBER(13,2) default 0 not null,
TO_REC_DAY_PROFIT NUMBER(13,2) default 0 not null,
TO_PAY_DAY_PROFIT NUMBER(13,2) default 0 not null,
TO_DAY_PROFIT NUMBER(13,2) default 0 not null,
TM_SMP VARCHAR2(26) default SYSDATE not null,
RSV_FLD1 VARCHAR2(100) default ' ' not null,
RSV_FLD2 VARCHAR2(100) default ' ' not null,
RSV_FLD3 VARCHAR2(100) default ' ' not null
)
/
create table CAPSMSLST
(
USR_ID VARCHAR2(50) not null constraint CAPSMSLST_PK primary key,
EVT_TYP VARCHAR2(30) not null,
ROL_TYP VARCHAR2(10) not null,
MERC_ID VARCHAR2(10),
ROL_NAM VARCHAR2(100) default '',
MOB_TEL VARCHAR2(30) default '',
MAL_ADR VARCHAR2(100) default '',
RLR_STS VARCHAR2(40) default '0',
TM_SMP VARCHAR2(26) default SYSDATE not null
)
/
create index CAPSMSLST_NI1
on CAPSMSLST (EVT_TYP,ROL_TYP)
/
create table CAPEVTRLR
(
EVT_TYP VARCHAR2(30) not null
constraint CAPEVTRLR_PK
primary key,
EVT_DSC VARCHAR2(60),
EVT_PTY VARCHAR2(30) default '',
IS_SND_MERC VARCHAR2(2) default 'Y',
IS_SND_DEV VARCHAR2(2) default 'Y',
IS_SND_BUS VARCHAR2(2) default 'Y',
IS_SND_OPR VARCHAR2(2) default 'Y',
IS_SND_OTH VARCHAR2(2) default 'Y',
IS_SND_SMS VARCHAR2(2) default 'Y',
IS_SND_MAL VARCHAR2(2) default 'Y',
SMS_TPL VARCHAR2(100) default '',
MAL_TPL VARCHAR2(100) default '',
RLR_STS VARCHAR2(40) default '0',
TM_SMP VARCHAR2(26) default SYSDATE not null
)
/
create table CAPTFUNCROLE
(
FUNCROLE_ID NUMBER,
FUNC_NO VARCHAR2(10),
ROLE_ID NUMBER,
STS CHAR default '1',
TM_SMP CHAR(14),
UPD_OPER VARCHAR2(10),
ORG_CD VARCHAR2(10)
)
/
comment on table CAPTFUNCROLE is '操作员角色权限表'
/
comment on column CAPTFUNCROLE.FUNCROLE_ID is '角色关系ID'
/
comment on column CAPTFUNCROLE.FUNC_NO is '功能编号'
/
comment on column CAPTFUNCROLE.ROLE_ID is '角色ID'
/
comment on column CAPTFUNCROLE.STS is '生效状态'
/
comment on column CAPTFUNCROLE.TM_SMP is '时间戳'
/
comment on column CAPTFUNCROLE.UPD_OPER is '最后更新操作员'
/
comment on column CAPTFUNCROLE.ORG_CD is '归属机构'
/
create table CAPTFUNC
(
FUNC_ID VARCHAR2(10),
FUNC_NO VARCHAR2(10) default ' ',
FUNC_NM VARCHAR2(60) default ' ',
FUNC_TYP VARCHAR2(1) default ' ',
FUNC_DESC VARCHAR2(120) default ' ',
ICON_NM VARCHAR2(10) default ' ',
URL VARCHAR2(50) default ' ',
MODULE_TYP VARCHAR2(10) default ' ',
PFUNC_NO VARCHAR2(10) default ' ',
ORG_NO VARCHAR2(10) default ' ',
STS VARCHAR2(1) default '0',
UPD_OPR VARCHAR2(10) default ' ',
TM_SMP VARCHAR2(26) default ' '
)
/
create unique index CAPTFUNC_PK
on CAPTFUNC (FUNC_ID)
/
create table CAPCSWRULE
(
RUL_TYP VARCHAR2(20) not null,
CAP_TYP VARCHAR2(20) not null,
CORG_NO VARCHAR2(20) not null,
SRC_RSP_CD VARCHAR2(8),
SRC_RSP_MSG VARCHAR2(300),
DST_RSP_CD VARCHAR2(8),
DST_RSP_MSG VARCHAR2(300),
CSW_TYP VARCHAR2(2),
RUL_STS VARCHAR2(2) default '0',
TM_SMP VARCHAR2(26) default SYSDATE not null,
DST_TXN_STS VARCHAR2(2),
CSWRULE_ID NUMBER default '0'
)
/
create index CAPCSWRULE_NI1
on CAPCSWRULE (RUL_TYP, CAP_TYP, CORG_NO, SRC_RSP_CD)
/
create table CAPCODEINF
(
RUL_TYP VARCHAR2(20) not null,
CAP_TYP VARCHAR2(20) not null,
RSP_CD VARCHAR2(8),
RSP_MSG VARCHAR2(300),
IS_DFT VARCHAR2(8) default '0',
TM_SMP VARCHAR2(26) default SYSDATE not null
)
/
create table CAPCNLINF
(
SYS_ID VARCHAR2(30) not null,
TXN_CNL VARCHAR2(30) not null,
TXN_DES VARCHAR2(500) not null,
IS_ACC VARCHAR2(2) default 'Y',
IS_SIG VARCHAR2(2) default 'Y',
IS_STL VARCHAR2(2) default 'Y',
RLR_STS VARCHAR2(3) default '0',
TM_SMP VARCHAR2(26) default SYSDATE not null,
constraint CAPCNLINF_PK
primary key (SYS_ID, TXN_CNL)
)
/
create table CAPCOMPTY
(
PTY_KND VARCHAR2(10) default 'PBC',
PTY_ID VARCHAR2(35) not null
constraint CAPCOMPTY_PK
primary key,
PTY_NM VARCHAR2(240) default ' ',
PTYALS_NM VARCHAR2(240) default ' ',
BKCTGY_CD VARCHAR2(20) default ' ',
PTY_TP VARCHAR2(10) default ' ',
STTLMAGTID VARCHAR2(35) default ' ',
RMKINF VARCHAR2(240) default ' ',
TM_SMP VARCHAR2(26) default SYSDATE not null,
RSV_FLD VARCHAR2(100) default ' '
)
/
comment on column CAPCOMPTY.PTY_ID is '联行号'
/
comment on column CAPCOMPTY.PTY_NM is '联行名称'
/
create index CAPCOMPTY_NI1
on CAPCOMPTY (PTY_NM)
/
create table CAPSOFTROUTE
(
BANKCODE VARCHAR2(10) not null,
FRSPRNO VARCHAR2(10) not null,
OBJSVR VARCHAR2(30),
HTXNCD VARCHAR2(30),
HEADPARAM VARCHAR2(255) not null,
BODYPARAM VARCHAR2(255) not null,
ORTSTS VARCHAR2(5) not null,
HBOBJSVR VARCHAR2(30),
HBTXNCD VARCHAR2(30),
FTSSTS VARCHAR2(20),
SIGNURL VARCHAR2(100),
TM_SMP VARCHAR2(20) default SYSDATE not null
)
/
comment on table CAPSOFTROUTE is '前置机信息表'
/
comment on column CAPSOFTROUTE.BANKCODE is '银行代码'
/
comment on column CAPSOFTROUTE.FRSPRNO is '前置机编号'
/
comment on column CAPSOFTROUTE.OBJSVR is '目标服务SCA'
/
comment on column CAPSOFTROUTE.HTXNCD is '目标交易码SCA'
/
comment on column CAPSOFTROUTE.HEADPARAM is '头参数IP端口'
/
comment on column CAPSOFTROUTE.BODYPARAM is '银行业务参数'
/
comment on column CAPSOFTROUTE.ORTSTS is '状态标识S有效，F无效'
/
comment on column CAPSOFTROUTE.HBOBJSVR is '心跳服务码'
/
comment on column CAPSOFTROUTE.HBTXNCD is '心跳交易码'
/
comment on column CAPSOFTROUTE.FTSSTS is '无效标识修改时间'
/
comment on column CAPSOFTROUTE.SIGNURL is '目标签名服务器信息'
/
create global temporary table CAPICBCPERD
(
ISEQNO VARCHAR2(35) not null,
QRYISEQNO VARCHAR2(35) not null,
QRYORDERNO VARCHAR2(6) not null,
PORTNO VARCHAR2(30) not null,
OPTYPE VARCHAR2(1) not null,
AUTCOD VARCHAR2(15) not null,
AC_NO VARCHAR2(34) not null,
CUS_NAM VARCHAR2(60) not null,
PAYACCNAMEEN VARCHAR2(60),
ISS_CD VARCHAR2(60) not null,
TXN_AMT VARCHAR2(17) not null,
USECODE VARCHAR2(3),
USECN VARCHAR2(20),
RMK VARCHAR2(40),
POSTSCRIPT VARCHAR2(100),
SUMMARY VARCHAR2(20),
SREF_NO VARCHAR2(20) not null,
OREF VARCHAR2(20),
ORD_NO VARCHAR2(20) not null,
BUS_TYP VARCHAR2(5) not null,
ERPCHECKNO VARCHAR2(8),
CRVOUHTYPE VARCHAR2(3),
CRVOUHNAME VARCHAR2(30),
CRVOUHNO VARCHAR2(20),
RESULT VARCHAR2(2) not null,
BANKREM VARCHAR2(100) not null,
BANKRETIME VARCHAR2(14) not null,
IRETCODE VARCHAR2(5) not null,
IRETMSG VARCHAR2(100) not null,
REQRESERVED3 VARCHAR2(100),
REQRESERVED4 VARCHAR2(100),
TM_SMP VARCHAR2(26) default SYSDATE not null,
TRSP_MSG VARCHAR2(160),
TTXN_ST VARCHAR2(10)
)
on commit preserve rows
/
create table CAPCUSINF
(
CUSTOMER_ID VARCHAR2(48) not null
constraint PK_CAPCUSINF
primary key,
CUSTOMER_NAME VARCHAR2(100) not null,
CUSTOMER_CODE VARCHAR2(48) not null,
RANK VARCHAR2(100) not null,
ENABLED VARCHAR2(1) not null,
CRE_DT VARCHAR2(8) default ' ',
CRE_TM VARCHAR2(6) default ' ',
UPD_DT VARCHAR2(8) default ' ',
UPD_TM VARCHAR2(6) default ' ',
TM_SMP VARCHAR2(26) default ' ',
LMT_NM VARCHAR2(60) default 'DEFAULT'
)
/
comment on table CAPCUSINF is '客户信息表'
/
comment on column CAPCUSINF.CUSTOMER_ID is '客户编号'
/
comment on column CAPCUSINF.CUSTOMER_NAME is '客户名称'
/
comment on column CAPCUSINF.CUSTOMER_CODE is '客户代码'
/
comment on column CAPCUSINF.RANK is '等级'
/
comment on column CAPCUSINF.ENABLED is '是否启用'
/
comment on column CAPCUSINF.CRE_DT is '创建日期'
/
comment on column CAPCUSINF.CRE_TM is '创建时间'
/
comment on column CAPCUSINF.UPD_DT is '更新日期'
/
comment on column CAPCUSINF.UPD_TM is '更新时间'
/
comment on column CAPCUSINF.LMT_NM is '风控编号'
/
create index NI1_CAPCUSINF
on CAPCUSINF (LMT_NM)
/
create table CAPACCOUNTINF
(
TITLE_ID VARCHAR2(48) not null,
TITLE_NAME VARCHAR2(100) not null,
BANK_CODE VARCHAR2(20) not null,
BR_NO VARCHAR2(100) not null,
ACCT_NO VARCHAR2(30) not null,
ACCT_NAME VARCHAR2(100) not null,
ENABLED VARCHAR2(1) not null,
CRE_DT VARCHAR2(8) default ' ',
CRE_TM VARCHAR2(6) default ' ',
UPD_DT VARCHAR2(8) default ' ',
UPD_TM VARCHAR2(6) default ' ',
TM_SMP VARCHAR2(26) default ' ',
ACCT_TYPE VARCHAR2(1) not null,
constraint PK_CAPACCOUNTINF
primary key (TITLE_ID, BANK_CODE)
)
/
comment on table CAPACCOUNTINF is '支付地账户表'
/
comment on column CAPACCOUNTINF.TITLE_ID is '支付地抬头ID'
/
comment on column CAPACCOUNTINF.TITLE_NAME is '支付地抬头名称'
/
comment on column CAPACCOUNTINF.BANK_CODE is '银行代码'
/
comment on column CAPACCOUNTINF.BR_NO is '分行号'
/
comment on column CAPACCOUNTINF.ACCT_NO is '账号'
/
comment on column CAPACCOUNTINF.ACCT_NAME is '户名'
/
comment on column CAPACCOUNTINF.ENABLED is '是否启用'
/
comment on column CAPACCOUNTINF.CRE_DT is '创建日期'
/
comment on column CAPACCOUNTINF.CRE_TM is '创建时间'
/
comment on column CAPACCOUNTINF.UPD_DT is '更新日期'
/
comment on column CAPACCOUNTINF.UPD_TM is '更新时间'
/
comment on column CAPACCOUNTINF.ACCT_TYPE is '账户类型 B:基本户 N:一般户'
/
create index CAPACCOUNTINF_TITLE_ID_INDEX
on CAPACCOUNTINF (TITLE_ID)
/
create global temporary table CAPCMBPERD
(
REQ_NBR VARCHAR2(64) default ' ' not null,
YUR_REF VARCHAR2(64) default ' ' not null,
TRX_SEQ VARCHAR2(8) default ' ' not null,
ACC_NBR VARCHAR2(64) default ' ' not null,
ACC_NAM VARCHAR2(64) default ' ' not null,
TRS_AMT VARCHAR2(16) default ' ' not null,
ERR_COD VARCHAR2(16) default ' ' not null,
ERR_TXT VARCHAR2(128) default ' ' not null,
BNK_FLG VARCHAR2(16) default ' ' not null,
TM_SMP VARCHAR2(32) default ' ' not null,
TRSP_MSG VARCHAR2(256) default ' ' not null,
TTXN_ST VARCHAR2(10) default ' ' not null,
STS_COD VARCHAR2(20) default ' ' not null
)
on commit preserve rows
/
create table CAPCMBRFD
(
REQ_NBR VARCHAR2(16) default ' ' not null,
YUR_REF VARCHAR2(32) default ' ' not null,
BTH_DTL VARCHAR2(16) default ' ' not null,
EAC_NBR VARCHAR2(48) default ' ' not null,
EAC_NAM VARCHAR2(16) default ' ' not null,
TRS_AMT VARCHAR2(16) default ' ' not null,
CPR_ACT VARCHAR2(32) default ' ' not null,
TXT_CLT VARCHAR2(64) default ' ' not null,
EAC_BNM VARCHAR2(64) default ' ' not null,
EAC_CTY VARCHAR2(64) default ' ' not null,
CPR_CNV VARCHAR2(16) default ' ' not null,
TRS_DAT VARCHAR2(32) default ' ' not null,
STS_COD VARCHAR2(16) default ' ' not null,
RTN_COD VARCHAR2(8) default ' ' not null,
RTN_TXT VARCHAR2(128) default ' ' not null,
RSV VARCHAR2(32) default ' ' not null,
DSK_NAM VARCHAR2(64) default ' ' not null,
MERC_ORD_NO VARCHAR2(64) default ' ',
CAP_ORD_NO VARCHAR2(32) default ' ',
TAC_NO VARCHAR2(30) default ' ',
TAC_NAM VARCHAR2(100) default ' '
)
/
comment on column CAPCMBRFD.REQ_NBR is '流程实例号'
/
comment on column CAPCMBRFD.YUR_REF is '业务参考号'
/
comment on column CAPCMBRFD.BTH_DTL is '明细序号'
/
comment on column CAPCMBRFD.EAC_NBR is '明细账户'
/
comment on column CAPCMBRFD.EAC_NAM is '明细账户名称'
/
comment on column CAPCMBRFD.TRS_AMT is '交易金额'
/
comment on column CAPCMBRFD.CPR_ACT is '客户在商户账号'
/
comment on column CAPCMBRFD.TXT_CLT is '客户摘要'
/
comment on column CAPCMBRFD.EAC_BNM is '明细账户开户行'
/
comment on column CAPCMBRFD.EAC_CTY is '明细账户开户地'
/
comment on column CAPCMBRFD.CPR_CNV is '商户协议号'
/
comment on column CAPCMBRFD.TRS_DAT is '交易日期'
/
comment on column CAPCMBRFD.STS_COD is '交易状态'
/
comment on column CAPCMBRFD.RTN_COD is '退票失败代码'
/
comment on column CAPCMBRFD.RTN_TXT is '退票附言'
/
comment on column CAPCMBRFD.RSV is '保留字30'
/
comment on column CAPCMBRFD.DSK_NAM is '批次号'
/
comment on column CAPCMBRFD.TAC_NO is '付款行号'
/
comment on column CAPCMBRFD.TAC_NAM is '付款账户名'
/
create index NI1_CAPCMBRFD
on CAPCMBRFD (TRS_DAT)
/
create index NI2_CAPCMBRFD
on CAPCMBRFD (YUR_REF,BTH_DTL)
/
create table CAPSMSMALINF
(
JRN_NO VARCHAR2(64) not null
constraint CAPSMSMALINF_PK
primary key,
SEND_MSG VARCHAR2(2048),
RECEIVE_NM VARCHAR2(128),
SEND_NM VARCHAR2(128),
TM_SMP VARCHAR2(32),
MSG_FLG VARCHAR2(1)
)
/
comment on column CAPSMSMALINF.JRN_NO is '流水号'
/
comment on column CAPSMSMALINF.SEND_MSG is '发送内容'
/
comment on column CAPSMSMALINF.RECEIVE_NM is '收件人'
/
comment on column CAPSMSMALINF.SEND_NM is '发送者'
/
comment on column CAPSMSMALINF.MSG_FLG is '1：邮件 2：短信'
/
create table CAPPARAMTER
(
P_KEY VARCHAR2(20) not null,
P_VALUES VARCHAR2(1000) not null,
P_DESC VARCHAR2(100) not null,
STS VARCHAR2(1) default '0' not null,
TM_SMP VARCHAR2(14) default ' ' not null,
RMK VARCHAR2(100) default ' ' not null,
constraint CAPPARAMTER_PK
primary key (P_KEY, P_VALUES)
)
/
comment on table CAPPARAMTER is '参数信息表'
/
comment on column CAPPARAMTER.P_KEY is '参数key'
/
comment on column CAPPARAMTER.P_VALUES is '参数值'
/
comment on column CAPPARAMTER.P_DESC is '参数描述'
/
comment on column CAPPARAMTER.STS is '状态 0生效 1无效'
/
create table CAPHROCHK
(
CHK_BAT_NO NUMBER(18) not null,
RUT_CORG VARCHAR2(12) default ' ' not null,
COP_BUS_TYP VARCHAR2(2) default ' ' not null,
CHK_FIL_DT VARCHAR2(8) default ' ' not null,
CHK_FIL_NM VARCHAR2(100) default ' ' not null,
FILE_RCV_DT VARCHAR2(8) default ' ' not null,
CHK_TYP VARCHAR2(1) default ' ' not null,
CHK_BEG_TM VARCHAR2(6) default ' ' not null,
CHK_END_TM VARCHAR2(6) default ' ' not null,
RMK VARCHAR2(60) default ' ' not null,
AC_DT VARCHAR2(8) default ' ' not null,
CHK_DT VARCHAR2(8) default ' ' not null,
FIL_TOT_AMT NUMBER(18,2) default 0 not null,
FIL_TOT_CNT NUMBER(13) default 0 not null,
CHK_TX_CD VARCHAR2(20) default ' ' not null,
TM_SMP VARCHAR2(26) default ' ' not null,
FIL_SUCC_CNT NUMBER(13) default 0 not null,
FIL_SUCC_AMT NUMBER(18,2) default 0 not null,
FIL_FAIL_CNT NUMBER(13) default 0 not null,
FIL_FAIL_AMT NUMBER(18,2) default 0 not null,
MD5 VARCHAR2(32),
CHK_SRV VARCHAR2(20) default ' ' not null,
CHK_FIL_STS VARCHAR2(1) default ' ' not null
)
/
create table CAPOPRLOG
(
TXN_CD VARCHAR2(20) not null,
TOKEN VARCHAR2(512) default ' ',
FUNCID VARCHAR2(20) default ' ',
MSG_TYP VARCHAR2(2) default ' ',
MSG_CD VARCHAR2(20) default ' ',
UPD_DT VARCHAR2(8) default ' ',
UPD_TM VARCHAR2(6) default ' ',
TM_SMP VARCHAR2(26) default ' '
)
/
create index NI1_CAPOPRLOG
on CAPOPRLOG (TXN_CD, UPD_DT)
/
create global temporary table CAPICBCPERDTMP
(
I_SEQ_NO VARCHAR2(35) not null,
QRY_I_SEQ_NO VARCHAR2(35) not null,
QRY_F_SEQ_NO VARCHAR2(35) not null,
PAY_TYPE VARCHAR2(2) not null,
PAYER_ACCOUNT VARCHAR2(34),
PAYER_CNNAME VARCHAR2(100),
PAYER_ENNAME VARCHAR2(100),
PAYEE_ACCOUNT VARCHAR2(34),
PAYEE_CNNAME VARCHAR2(100),
PAYEE_ENNAME VARCHAR2(100),
IO_FLAG VARCHAR2(2),
SAME_CITY_FLAG VARCHAR2(2),
PAYEE_AREA_CODE VARCHAR2(5),
PAYEE_CITY VARCHAR2(60),
PAYEE_BANK_NO VARCHAR2(13),
PAYEE_BANK_NAME VARCHAR2(60),
CURRENCY VARCHAR2(3),
AMOUNT VARCHAR2(17),
PURPOSE_CODE VARCHAR2(3),
PURPOSE VARCHAR2(20),
SUMMARY VARCHAR2(20),
SUMMARY_EN VARCHAR2(40),
POSTSCRIPT VARCHAR2(100),
REF_NO VARCHAR2(20),
RELATED_REF_NO VARCHAR2(20),
ERP_SERIAL_NO VARCHAR2(20),
ERP_CHECK_NO VARCHAR2(8),
BUSI_CODE VARCHAR2(5),
ORI_VOUCHER_TYPE VARCHAR2(3),
ORI_VOUCHER_NO VARCHAR2(20),
ORI_VOUCHER_NAME VARCHAR2(30),
RESULT VARCHAR2(2) not null,
INSTR_RET_CODE VARCHAR2(5) not null,
INSTR_RET_MSG VARCHAR2(300),
MANAGER_CARD_NO VARCHAR2(19),
MANAGER_CARD_NAME VARCHAR2(60),
TM_SMP VARCHAR2(26) default SYSDATE,
TRSP_CD VARCHAR2(30),
TRSP_MSG VARCHAR2(300),
TTXN_ST VARCHAR2(10)
)
on commit preserve rows
/
create global temporary table CAPHROTMP
(
DSK_NAM VARCHAR2(100) not null,
ACCT_NO VARCHAR2(48) not null,
ACCT_NAME VARCHAR2(150) not null,
REF_ID VARCHAR2(48) not null,
AMOUNT VARCHAR2(18) not null,
BANK_CODE VARCHAR2(10) default ' ',
BANK_CITY VARCHAR2(100) default ' ',
BANK_NAME VARCHAR2(100) default ' ',
BANK_NO VARCHAR2(14),
REMARK VARCHAR2(100) default ' ',
TM_SMP VARCHAR2(26) default SYSDATE not null
)
on commit preserve rows
/
comment on column CAPHROTMP.DSK_NAM is '批次文件名'
/
comment on column CAPHROTMP.ACCT_NO is '账号'
/
comment on column CAPHROTMP.ACCT_NAME is '账户名称'
/
comment on column CAPHROTMP.REF_ID is '参考号，HRO流水号'
/
comment on column CAPHROTMP.AMOUNT is '交易金额，以分为单位'
/
comment on column CAPHROTMP.BANK_CODE is '银行类型'
/
comment on column CAPHROTMP.BANK_CITY is '开户行所在城市'
/
comment on column CAPHROTMP.BANK_NAME is '开户银行网点'
/
comment on column CAPHROTMP.BANK_NO is '联行号'
/
comment on column CAPHROTMP.REMARK is '备注'
/
comment on column CAPHROTMP.TM_SMP is '时间戳'
/
alter table CAPBATINF add S_RSP_CD VARCHAR2(60) DEFAULT ' ';
alter table CAPBATINF add S_RSP_MSG VARCHAR2(256) DEFAULT ' ';
COMMENT ON COLUMN CAPBATINF.S_RSP_CD IS '批次发送银行返回错误码';
COMMENT ON COLUMN CAPBATINF.S_RSP_MSG IS '批次发送银行返回信息';
alter table CAPCMBPERD add ERR_DSP VARCHAR2(256) DEFAULT ' ';
COMMENT ON COLUMN CAPCMBPERD.ERR_DSP IS '错误描述';
alter table CAPCMBRFD add TM_SMP VARCHAR2(26) DEFAULT SYSDATE;
COMMENT ON COLUMN CAPCMBPERD.TM_SMP IS '时间戳';
--大区表
create table CAPZONEINFO
(
ZONE_ID VARCHAR2(16) not null constraint CAPZONEINFO_PK primary key,
ZONE_NM VARCHAR2(64) default ' ' not null,
TM_SMP VARCHAR2(26) default SYSDATE not null
)
/
comment on column CAPZONEINFO.ZONE_ID is '大区ID'
/
comment on column CAPZONEINFO.ZONE_NM is '大区名称'
/
comment on column CAPZONEINFO.TM_SMP is '时间戳'
/
--分公司表
create table CAPCOMPANY
(
COMPANY_ID VARCHAR2(16) not null constraint CAPCOMPANY_PK primary key,
COMPANY_NM VARCHAR2(300) default ' ' not null,
TM_SMP VARCHAR2(26) default SYSDATE not null
)
/
comment on column CAPCOMPANY.COMPANY_ID is '分公司ID'
/
comment on column CAPCOMPANY.COMPANY_NM is '分公司名称'
/
comment on column CAPCOMPANY.TM_SMP is '时间戳'
/
View Git Blame Copy Permalink