893 lines
28 KiB
SQL
893 lines
28 KiB
SQL
/*==============================================================*/
|
||
/* DBMS name: ORACLE Version 9i */
|
||
/* Created on: 2010/6/8 21:02:45 */
|
||
/*==============================================================*/
|
||
|
||
|
||
drop table MKMTACT cascade constraints;
|
||
|
||
drop index IDX_MKMTBAT1;
|
||
|
||
drop table MKMTBAT cascade constraints;
|
||
|
||
drop index IDX_MKMTBON3;
|
||
|
||
drop index IDX_MKMTBON2;
|
||
|
||
drop index IDX_MKMTBON1;
|
||
|
||
drop table MKMTBON cascade constraints;
|
||
|
||
drop table MKMTCHK cascade constraints;
|
||
|
||
drop index IDX_MKMTIAREA2;
|
||
|
||
drop index IDX_MKMTIAREA1;
|
||
|
||
drop table MKMTIAREA cascade constraints;
|
||
|
||
drop index IDX_MKMTIMER1;
|
||
|
||
drop table MKMTIMER cascade constraints;
|
||
|
||
drop index IDX_MKMTISS1;
|
||
|
||
drop table MKMTISS cascade constraints;
|
||
|
||
drop index IDX_MKMTIUSR2;
|
||
|
||
drop index IDX_MKMTIUSR1;
|
||
|
||
drop table MKMTIUSR cascade constraints;
|
||
|
||
drop index IDX_MKMTJNL3;
|
||
|
||
drop index IDX_MKMTJNL2;
|
||
|
||
drop index IDX_MKMTJNL1;
|
||
|
||
drop table MKMTJNL cascade constraints;
|
||
|
||
drop table MKMTMADV cascade constraints;
|
||
|
||
drop index IDX_MKMTMERC1;
|
||
|
||
drop table MKMTMERC cascade constraints;
|
||
|
||
drop index IDX_MKMTMSUM2;
|
||
|
||
drop index IDX_MKMTMSUM1;
|
||
|
||
drop table MKMTMSUM cascade constraints;
|
||
|
||
drop index IDX_MKMTPINF1;
|
||
|
||
drop table MKMTPINF cascade constraints;
|
||
|
||
drop table MKMTPUBPRA cascade constraints;
|
||
|
||
drop index IDX_MKMTSEARCH1;
|
||
|
||
drop table MKMTSEARCH cascade constraints;
|
||
|
||
/*==============================================================*/
|
||
/* Table: MKMTACT */
|
||
/*==============================================================*/
|
||
create table MKMTACT (
|
||
MKT_ID VARCHAR2(10) not null,
|
||
MKT_NM VARCHAR2(100) not null,
|
||
MKT_DESC2 VARCHAR2(50) not null,
|
||
CAP_TYP VARCHAR2(1) not null,
|
||
MKT_STS VARCHAR2(1) default 'U' not null,
|
||
ISS_TOT_AMT NUMBER(9,2) not null,
|
||
CAP_ID_TYP VARCHAR2(1),
|
||
CAP_ID VARCHAR2(15),
|
||
ISS_CUR_AMT NUMBER(9,2) default 0,
|
||
STL_PERD_FLG VARCHAR2(1),
|
||
STL_PRED NUMBER(9),
|
||
ISS_EFF_DT VARCHAR2(8),
|
||
ISS_EXP_DT VARCHAR2(8),
|
||
USE_TOT_AMT NUMBER(9,2),
|
||
USE_TOT_CNT NUMBER(8),
|
||
NULL_TOT_AMT NUMBER(9,2),
|
||
NULL_TOT_CNT NUMBER(8),
|
||
NEED_INV_FLG VARCHAR2(1) default 'N',
|
||
CRT_DT VARCHAR2(8),
|
||
CRT_TM VARCHAR2(6),
|
||
CRT_OPR_TYP VARCHAR2(1),
|
||
CRT_USR_OPR VARCHAR2(10),
|
||
UPD_USR_OPR VARCHAR2(10),
|
||
APPR_OPR VARCHAR2(10),
|
||
APPR_DT VARCHAR2(8),
|
||
APPR_TM VARCHAR2(6),
|
||
APPR_OPR2 VARCHAR2(10),
|
||
APPR_DT2 VARCHAR2(8),
|
||
APPR_TM2 VARCHAR2(6),
|
||
SMS_FLG VARCHAR2(1),
|
||
MKT_DESC VARCHAR2(300),
|
||
REJ_DESC VARCHAR2(400),
|
||
SMS_TMP_ID VARCHAR2(10),
|
||
TM_SMP VARCHAR2(26),
|
||
constraint PK_MKMTACT primary key (MKT_ID)
|
||
);
|
||
|
||
comment on column MKMTACT.MKT_STS is
|
||
'U: 登记未审核
|
||
A: 审核激活
|
||
P: 暂停
|
||
E: 过期，失效
|
||
R: 拒绝，可以修改，激活的不能拒绝';
|
||
|
||
comment on column MKMTACT.ISS_TOT_AMT is
|
||
'红包和代金券，采用总金额控制方式；折扣券，采用张数控制方式';
|
||
|
||
comment on column MKMTACT.STL_PERD_FLG is
|
||
'空：不结算
|
||
0：按月结算
|
||
1：指定结算周期，从活动生效日开始
|
||
1：等同现金或充值卡
|
||
2：活动结束后结算
|
||
（目前只支持空格和按月结算）
|
||
';
|
||
|
||
comment on column MKMTACT.USE_TOT_AMT is
|
||
'0: 不用
|
||
1: 需要，默认';
|
||
|
||
comment on column MKMTACT.NEED_INV_FLG is
|
||
'Y：需要开发票
|
||
N：不需要开发票
|
||
';
|
||
|
||
comment on column MKMTACT.SMS_FLG is
|
||
'0: 不用
|
||
1: 需要，默认
|
||
';
|
||
|
||
/*==============================================================*/
|
||
/* Table: MKMTBAT */
|
||
/*==============================================================*/
|
||
create table MKMTBAT (
|
||
SEQ_NO VARCHAR2(14) not null,
|
||
BAT_NO VARCHAR2(6),
|
||
ACT_ID VARCHAR2(10),
|
||
PRD_ID VARCHAR2(12),
|
||
SND_DT VARCHAR2(8),
|
||
MBL_NO VARCHAR2(11),
|
||
BON_AMT NUMBER(9,2),
|
||
STS VARCHAR2(1),
|
||
constraint PK_MKMTBAT primary key (SEQ_NO)
|
||
);
|
||
|
||
comment on column MKMTBAT.STS is
|
||
'0：未处理
|
||
1：成功
|
||
2：失败
|
||
';
|
||
|
||
/*==============================================================*/
|
||
/* Index: IDX_MKMTBAT1 */
|
||
/*==============================================================*/
|
||
create index IDX_MKMTBAT1 on MKMTBAT (
|
||
BAT_NO ASC,
|
||
STS ASC
|
||
);
|
||
|
||
/*==============================================================*/
|
||
/* Table: MKMTBON */
|
||
/*==============================================================*/
|
||
create table MKMTBON (
|
||
BON_ID VARCHAR2(20) not null,
|
||
MKT_ID VARCHAR2(10),
|
||
ISS_ID VARCHAR2(15),
|
||
PRD_ID VARCHAR2(12),
|
||
BON_NM VARCHAR2(200),
|
||
ISS_TYP VARCHAR2(1) default 'G' not null,
|
||
BON_STS VARCHAR2(1) not null,
|
||
SAV_TYP VARCHAR2(1) default '0',
|
||
PRD_TYP VARCHAR2(1) not null,
|
||
CAP_ID_TYP VARCHAR2(1),
|
||
CAP_ID VARCHAR2(15),
|
||
USE_ORD_RAT VARCHAR2(3),
|
||
USE_BEG_REF_AMT NUMBER(9,2),
|
||
USE_END_REF_AMT NUMBER(9,2),
|
||
USR_PROV VARCHAR2(2),
|
||
USR_CITY VARCHAR2(4),
|
||
USR_CITY_NM VARCHAR2(60),
|
||
USR_NO VARCHAR2(12) not null,
|
||
USR_MBL_NO VARCHAR2(11),
|
||
BON_AMT NUMBER(9,2),
|
||
LAST_AC_BAL NUMBER(9,2),
|
||
CUR_AC_BAL NUMBER(9,2),
|
||
UAVA_BAL NUMBER(9,2),
|
||
BON_RAT VARCHAR2(2),
|
||
EFF_DT VARCHAR2(8),
|
||
EXP_DT VARCHAR2(8) default '0',
|
||
ISS_DT VARCHAR2(8),
|
||
ISS_TM VARCHAR2(6),
|
||
USE_DT VARCHAR2(8),
|
||
USE_TM VARCHAR2(6),
|
||
CLR_DT VARCHAR2(8),
|
||
ISS_BUS_CNL VARCHAR2(10),
|
||
IS_TRANS VARCHAR2(1),
|
||
ORD_NO VARCHAR2(20),
|
||
LST_ORD_NO VARCHAR2(20),
|
||
REMARK VARCHAR2(200),
|
||
UPD_DT VARCHAR2(8),
|
||
UPD_JRN VARCHAR2(18),
|
||
TM_SMP VARCHAR2(26),
|
||
constraint PK_MKMTBON primary key (BON_ID)
|
||
);
|
||
|
||
comment on column MKMTBON.PRD_ID is
|
||
'针对点金券，记录领用商户';
|
||
|
||
comment on column MKMTBON.ISS_TYP is
|
||
'G: 已生成
|
||
P: 暂停使用
|
||
A: 已领用
|
||
R: 已回收
|
||
U: 已使用
|
||
E: 已过期';
|
||
|
||
comment on column MKMTBON.BON_STS is
|
||
'0 : 可拆分代金券(红包)
|
||
1: 整张使用代金券
|
||
2: 折扣券';
|
||
|
||
comment on column MKMTBON.SAV_TYP is
|
||
'0：远程
|
||
1：下载到手机（远程不能使用）
|
||
';
|
||
|
||
comment on column MKMTBON.PRD_TYP is
|
||
'0：红包
|
||
1：代金券
|
||
2：折扣券
|
||
';
|
||
|
||
comment on column MKMTBON.CAP_ID_TYP is
|
||
'0：省
|
||
1：商户
|
||
';
|
||
|
||
comment on column MKMTBON.CAP_ID is
|
||
'出资方省代码
|
||
如果出资方为商户，则不用结算
|
||
';
|
||
|
||
comment on column MKMTBON.USR_MBL_NO is
|
||
'0: 表示不打折';
|
||
|
||
comment on column MKMTBON.LAST_AC_BAL is
|
||
'失效日期：领用日期+清理标识';
|
||
|
||
comment on column MKMTBON.BON_RAT is
|
||
'清理的日期';
|
||
|
||
comment on column MKMTBON.EFF_DT is
|
||
'MPS: 手机支付系统发放
|
||
BOS: BOSS发放的
|
||
LPS: 省平台发放
|
||
其他待定';
|
||
|
||
comment on column MKMTBON.EXP_DT is
|
||
'0: 不是装让获得
|
||
1: 是装让获得';
|
||
|
||
comment on column MKMTBON.ISS_BUS_CNL is
|
||
'MPS: 手机支付系统发放
|
||
BOS: BOSS发放的
|
||
LPS: 省平台发放
|
||
其他待定
|
||
';
|
||
|
||
comment on column MKMTBON.IS_TRANS is
|
||
'0: 不是转让获得
|
||
1: 是转让获得
|
||
';
|
||
|
||
/*==============================================================*/
|
||
/* Index: IDX_MKMTBON1 */
|
||
/*==============================================================*/
|
||
create index IDX_MKMTBON1 on MKMTBON (
|
||
USR_NO ASC,
|
||
MKT_ID ASC
|
||
);
|
||
|
||
/*==============================================================*/
|
||
/* Index: IDX_MKMTBON2 */
|
||
/*==============================================================*/
|
||
create index IDX_MKMTBON2 on MKMTBON (
|
||
USR_MBL_NO ASC,
|
||
MKT_ID ASC
|
||
);
|
||
|
||
/*==============================================================*/
|
||
/* Index: IDX_MKMTBON3 */
|
||
/*==============================================================*/
|
||
create index IDX_MKMTBON3 on MKMTBON (
|
||
MKT_ID ASC,
|
||
USR_NO ASC
|
||
);
|
||
|
||
/*==============================================================*/
|
||
/* Table: MKMTCHK */
|
||
/*==============================================================*/
|
||
create table MKMTCHK (
|
||
OPR_JNL_NO VARCHAR2(18) not null,
|
||
TBL_NM VARCHAR2(500),
|
||
KEY_VAL VARCHAR2(400),
|
||
CNT1 VARCHAR2(4000),
|
||
CNT2 VARCHAR2(4000),
|
||
CNT3 VARCHAR2(4000),
|
||
CNT4 VARCHAR2(4000),
|
||
OPR_STS VARCHAR2(1),
|
||
CRE_OPR VARCHAR2(10),
|
||
CRE_DT VARCHAR2(8),
|
||
TM_SMP VARCHAR2(26),
|
||
constraint PK_MKMTCHK primary key (OPR_JNL_NO)
|
||
);
|
||
|
||
comment on column MKMTCHK.OPR_STS is
|
||
'0：未复核
|
||
1：一级复核
|
||
2：二级复核
|
||
9：拒绝
|
||
';
|
||
|
||
/*==============================================================*/
|
||
/* Table: MKMTIAREA */
|
||
/*==============================================================*/
|
||
create table MKMTIAREA (
|
||
MKT_ID VARCHAR2(10) not null,
|
||
ISS_ID VARCHAR2(15) not null,
|
||
USR_PROV VARCHAR2(2) not null,
|
||
USR_CITY VARCHAR2(4) not null,
|
||
USR_CITY_NM VARCHAR2(60),
|
||
TM_SMP VARCHAR2(26),
|
||
constraint PK_MKMTIAREA primary key (MKT_ID, ISS_ID, USR_PROV, USR_CITY)
|
||
);
|
||
|
||
/*==============================================================*/
|
||
/* Index: IDX_MKMTIAREA1 */
|
||
/*==============================================================*/
|
||
create index IDX_MKMTIAREA1 on MKMTIAREA (
|
||
USR_PROV ASC
|
||
);
|
||
|
||
/*==============================================================*/
|
||
/* Index: IDX_MKMTIAREA2 */
|
||
/*==============================================================*/
|
||
create index IDX_MKMTIAREA2 on MKMTIAREA (
|
||
USR_CITY ASC
|
||
);
|
||
|
||
/*==============================================================*/
|
||
/* Table: MKMTIMER */
|
||
/*==============================================================*/
|
||
create table MKMTIMER (
|
||
MKT_ID VARCHAR2(10) not null,
|
||
ISS_ID VARCHAR2(15) not null,
|
||
MERC_ID VARCHAR2(15) not null,
|
||
MAX_ISS_AMT NUMBER(9,2),
|
||
MAX_ISS_CNT NUMBER(9),
|
||
TOT_ISS_AMT NUMBER(9,2),
|
||
TOT_ISS_CNT NUMBER(9),
|
||
TM_SMP VARCHAR2(26),
|
||
constraint PK_MKMTIMER primary key (MKT_ID, ISS_ID, MERC_ID)
|
||
);
|
||
|
||
/*==============================================================*/
|
||
/* Index: IDX_MKMTIMER1 */
|
||
/*==============================================================*/
|
||
create index IDX_MKMTIMER1 on MKMTIMER (
|
||
MERC_ID ASC
|
||
);
|
||
|
||
/*==============================================================*/
|
||
/* Table: MKMTISS */
|
||
/*==============================================================*/
|
||
create table MKMTISS (
|
||
MKT_ID VARCHAR2(10) not null,
|
||
ISS_TOT_AMT NUMBER(9,2),
|
||
ISS_ID_TYP VARCHAR2(1),
|
||
ISS_ID VARCHAR2(15) not null,
|
||
ISS_ID_NM VARCHAR2(200),
|
||
ISS_CUR_AMT NUMBER(9,2),
|
||
USR_LMT_TYP VARCHAR2(1),
|
||
USR_REG_FLG VARCHAR2(1) default '0',
|
||
MERC_LMT_TYP VARCHAR2(1),
|
||
STL_TYP VARCHAR2(1) default '0',
|
||
STL_PERD_FLG VARCHAR2(1),
|
||
STL_PRED NUMBER(9),
|
||
STL_FLG VARCHAR2(1),
|
||
ISS_EFF_DT VARCHAR2(8),
|
||
ISS_EXP_DT VARCHAR2(8),
|
||
USE_TOT_AMT NUMBER(9,2),
|
||
USE_TOT_CNT NUMBER(8),
|
||
NULL_TOT_AMT NUMBER(9,2),
|
||
NULL_TOT_CNT NUMBER(8),
|
||
NEED_INV_FLG VARCHAR2(1) default 'N',
|
||
CRT_DT VARCHAR2(8),
|
||
CRT_TM VARCHAR2(6),
|
||
CRT_OPR_TYP VARCHAR2(1),
|
||
CRT_USR_OPR VARCHAR2(10),
|
||
UPD_USR_OPR VARCHAR2(10),
|
||
SMS_FLG VARCHAR2(1),
|
||
ISS_SMS_TMP_ID VARCHAR2(10),
|
||
EXP_SMS_TMP_ID VARCHAR2(10),
|
||
TM_SMP VARCHAR2(26),
|
||
constraint PK_MKMTISS primary key (MKT_ID, ISS_ID)
|
||
);
|
||
|
||
comment on column MKMTISS.ISS_ID_TYP is
|
||
'移动
|
||
商户，只能发代金券
|
||
';
|
||
|
||
comment on column MKMTISS.USR_LMT_TYP is
|
||
'0: 全网用户（所有用户）
|
||
1: 本省用户
|
||
2: 多省市用户
|
||
3: 指定用户群
|
||
（新增活动时移动基地发放的营销工具缺省为全网用户；省公司和商户发放的??销工具缺省为本省用户）
|
||
';
|
||
|
||
comment on column MKMTISS.USR_REG_FLG is
|
||
'0：必须为注册用户
|
||
1：必须为非注册用户
|
||
2：两者都可以
|
||
';
|
||
|
||
comment on column MKMTISS.MERC_LMT_TYP is
|
||
'0：不限制
|
||
1：全网商户
|
||
2：本省商户
|
||
3：指定商户列表
|
||
';
|
||
|
||
comment on column MKMTISS.STL_TYP is
|
||
'0：发行方
|
||
1：使用方不结算
|
||
';
|
||
|
||
comment on column MKMTISS.STL_PERD_FLG is
|
||
'空：不结算
|
||
0：按月结算
|
||
1：指定结算周期，从活动生效日开始
|
||
1：等同现金或充值卡
|
||
2：活动结束后结算
|
||
（目前只支持空格和按月结算）
|
||
';
|
||
|
||
comment on column MKMTISS.STL_FLG is
|
||
'0：未结
|
||
1：已结算
|
||
';
|
||
|
||
comment on column MKMTISS.NEED_INV_FLG is
|
||
'Y：需要开发票
|
||
N：不需要开发票
|
||
';
|
||
|
||
comment on column MKMTISS.SMS_FLG is
|
||
'0: 不用
|
||
1: 需要，默认
|
||
';
|
||
|
||
/*==============================================================*/
|
||
/* Index: IDX_MKMTISS1 */
|
||
/*==============================================================*/
|
||
create index IDX_MKMTISS1 on MKMTISS (
|
||
ISS_ID_TYP ASC,
|
||
ISS_ID ASC
|
||
);
|
||
|
||
/*==============================================================*/
|
||
/* Table: MKMTIUSR */
|
||
/*==============================================================*/
|
||
create table MKMTIUSR (
|
||
MKT_ID VARCHAR2(10) not null,
|
||
ISS_ID VARCHAR2(15) not null,
|
||
MBL_NO VARCHAR2(11) not null,
|
||
USR_NO VARCHAR2(12),
|
||
UPD_OPR VARCHAR2(11),
|
||
UPD_MERC_ID VARCHAR2(15),
|
||
TM_SMP VARCHAR2(26),
|
||
constraint PK_MKMTIUSR primary key (MKT_ID, ISS_ID, MBL_NO)
|
||
);
|
||
|
||
/*==============================================================*/
|
||
/* Index: IDX_MKMTIUSR1 */
|
||
/*==============================================================*/
|
||
create index IDX_MKMTIUSR1 on MKMTIUSR (
|
||
MBL_NO ASC
|
||
);
|
||
|
||
/*==============================================================*/
|
||
/* Index: IDX_MKMTIUSR2 */
|
||
/*==============================================================*/
|
||
create index IDX_MKMTIUSR2 on MKMTIUSR (
|
||
USR_NO ASC
|
||
);
|
||
|
||
/*==============================================================*/
|
||
/* Table: MKMTJNL */
|
||
/*==============================================================*/
|
||
create table MKMTJNL (
|
||
JRN_NO NUMBER(18) not null,
|
||
BON_ID VARCHAR2(20) not null,
|
||
TX_CD VARCHAR2(7) not null,
|
||
RSP_CD VARCHAR2(6),
|
||
RTN_MSC_TYP VARCHAR2(1),
|
||
ORD_TYP VARCHAR2(2) not null,
|
||
ORD_NO VARCHAR2(20),
|
||
TX_TYP VARCHAR2(2),
|
||
SUB_BUS_TYP VARCHAR2(3) not null,
|
||
BUS_ACP_CNL VARCHAR2(5) not null,
|
||
SYS_ACS_CNL VARCHAR2(5) not null,
|
||
TX_SYS_DT VARCHAR2(8),
|
||
TX_SYS_TM VARCHAR2(6),
|
||
TX_ACC_DT VARCHAR2(8),
|
||
RVS_TX_FLG VARCHAR2(1),
|
||
TX_STS VARCHAR2(2) not null,
|
||
MKT_ID VARCHAR2(10) default 'U' not null,
|
||
ISS_ID_TYP VARCHAR2(1),
|
||
ISS_ID VARCHAR2(15),
|
||
CAP_ID_TYP VARCHAR2(1),
|
||
CAP_ID VARCHAR2(15),
|
||
CAP_NM VARCHAR2(60),
|
||
BON_TYP VARCHAR2(1) default '0',
|
||
USE_TYP VARCHAR2(1),
|
||
BON_RAT VARCHAR2(2),
|
||
DR_AMT NUMBER(9,2),
|
||
CR_AMT NUMBER(9,2),
|
||
BON_BAL NUMBER(9,2),
|
||
MBL_NO VARCHAR2(11),
|
||
USR_NO VARCHAR2(12),
|
||
USR_PROV VARCHAR2(2),
|
||
USR_CITY VARCHAR2(4),
|
||
USR_CITY_NM VARCHAR2(60),
|
||
MERC_ID VARCHAR2(15),
|
||
MERC_ORD_NO VARCHAR2(20),
|
||
MERC_PROV VARCHAR2(2),
|
||
MERC_CITY VARCHAR2(4),
|
||
MERC_CITY_NM VARCHAR2(60),
|
||
CLR_MERC VARCHAR2(15),
|
||
THD_JRN NUMBER(18),
|
||
ORD_AMT NUMBER(9,2),
|
||
AC_DT VARCHAR2(8),
|
||
STL_DT VARCHAR2(8),
|
||
REAL_MERC_ID VARCHAR2(15),
|
||
REAL_MERC_NM VARCHAR2(60),
|
||
OLD_TX_DT VARCHAR2(8),
|
||
OLD_TX_TM VARCHAR2(6),
|
||
VCH_NO_TYP VARCHAR2(1),
|
||
VCH_NO VARCHAR2(20),
|
||
POS_TRM_SEQ VARCHAR2(8),
|
||
POS_TRM VARCHAR2(12),
|
||
POS_BAT_NO VARCHAR2(6),
|
||
POS_JRN VARCHAR2(6),
|
||
ORD_DT VARCHAR2(8),
|
||
ORD_TM VARCHAR2(6),
|
||
REMARK VARCHAR2(200),
|
||
REL_STL_FLG VARCHAR2(1) default '0',
|
||
TM_SMP VARCHAR2(26),
|
||
constraint PK_MKMTJNL primary key (JRN_NO, BON_ID)
|
||
);
|
||
|
||
comment on column MKMTJNL.ORD_TYP is
|
||
'00：远程消费
|
||
01：充值
|
||
02：提现
|
||
03：缴话费
|
||
04：缴公共事业费
|
||
05：现场联机
|
||
06：现场脱机
|
||
07：圈存
|
||
08：转账
|
||
';
|
||
|
||
comment on column MKMTJNL.TX_STS is
|
||
'S：交易成功
|
||
F：交易失败
|
||
C：被冲正
|
||
U：初始预记状态
|
||
T：发送超时
|
||
X：发送失败
|
||
D：交易撤消
|
||
E：其他错误
|
||
';
|
||
|
||
comment on column MKMTJNL.CAP_ID_TYP is
|
||
'0：移动
|
||
1：商户
|
||
';
|
||
|
||
comment on column MKMTJNL.USE_TYP is
|
||
'0:发放
|
||
1:使用
|
||
2:发放并使用
|
||
3:转让
|
||
';
|
||
|
||
comment on column MKMTJNL.VCH_NO_TYP is
|
||
'0:交易流水号
|
||
1：BSP批次号
|
||
2：日终批处理批次
|
||
（对于需要日终做清算的，白天统一计入日终批处理批次）
|
||
';
|
||
|
||
comment on column MKMTJNL.REL_STL_FLG is
|
||
'0：实时。默认
|
||
1：异步
|
||
';
|
||
|
||
/*==============================================================*/
|
||
/* Index: IDX_MKMTJNL1 */
|
||
/*==============================================================*/
|
||
create index IDX_MKMTJNL1 on MKMTJNL (
|
||
USR_NO ASC,
|
||
BON_ID ASC
|
||
);
|
||
|
||
/*==============================================================*/
|
||
/* Index: IDX_MKMTJNL2 */
|
||
/*==============================================================*/
|
||
create index IDX_MKMTJNL2 on MKMTJNL (
|
||
MKT_ID ASC,
|
||
MERC_ID ASC,
|
||
AC_DT ASC
|
||
);
|
||
|
||
/*==============================================================*/
|
||
/* Index: IDX_MKMTJNL3 */
|
||
/*==============================================================*/
|
||
create index IDX_MKMTJNL3 on MKMTJNL (
|
||
ISS_ID_TYP ASC,
|
||
ISS_ID ASC,
|
||
AC_DT ASC,
|
||
MKT_ID ASC
|
||
);
|
||
|
||
/*==============================================================*/
|
||
/* Table: MKMTMADV */
|
||
/*==============================================================*/
|
||
create table MKMTMADV (
|
||
MERC_ID VARCHAR2(15) not null,
|
||
ADV_DESC VARCHAR2(500) not null,
|
||
ADV_PIC VARCHAR2(200),
|
||
EFF_DT VARCHAR2(8) not null,
|
||
EXP_DT VARCHAR2(8) not null,
|
||
CRT_OPR VARCHAR2(10),
|
||
CRT_DT VARCHAR2(8),
|
||
CRT_TM VARCHAR2(6),
|
||
APPR_OPR VARCHAR2(10),
|
||
APPR_DT VARCHAR2(8),
|
||
APPR_TM VARCHAR2(6),
|
||
TM_SMP VARCHAR2(26),
|
||
constraint PK_MKMTMADV primary key (MERC_ID)
|
||
);
|
||
|
||
/*==============================================================*/
|
||
/* Table: MKMTMERC */
|
||
/*==============================================================*/
|
||
create table MKMTMERC (
|
||
MKT_ID VARCHAR2(10) not null,
|
||
ISS_ID VARCHAR2(15) not null,
|
||
PRD_ID VARCHAR2(12) not null,
|
||
MERC_ID VARCHAR2(15) not null,
|
||
MAX_USE_AMT NUMBER(9,2),
|
||
MAX_USE_CNT NUMBER(9),
|
||
USE_TOT_AMT NUMBER(9,2),
|
||
USE_TOT_CNT NUMBER(9),
|
||
TM_SMP VARCHAR2(26),
|
||
constraint PK_MKMTMERC primary key (MKT_ID, ISS_ID, PRD_ID, MERC_ID)
|
||
);
|
||
|
||
/*==============================================================*/
|
||
/* Index: IDX_MKMTMERC1 */
|
||
/*==============================================================*/
|
||
create index IDX_MKMTMERC1 on MKMTMERC (
|
||
MERC_ID ASC
|
||
);
|
||
|
||
/*==============================================================*/
|
||
/* Table: MKMTMSUM */
|
||
/*==============================================================*/
|
||
create table MKMTMSUM (
|
||
MERC_ID VARCHAR2(15) not null,
|
||
MKT_ID VARCHAR2(10),
|
||
CAP_TYP VARCHAR2(1) not null,
|
||
CAP_ID_TYP VARCHAR2(1) not null,
|
||
CAP_ID VARCHAR2(15) not null,
|
||
IS_INV_FLG VARCHAR2(1) default 'N' not null,
|
||
PERD_END_DT VARCHAR2(8) not null,
|
||
CAP_NM VARCHAR2(60),
|
||
MERC_PROV VARCHAR2(2),
|
||
MERC_CITY VARCHAR2(4),
|
||
INV_STS VARCHAR2(1),
|
||
PERD_BEG_DT VARCHAR2(8),
|
||
LAST_AC_BAL NUMBER(9,2),
|
||
CUR_AC_BAL NUMBER(9,2),
|
||
SND_NTC_DT VARCHAR2(8),
|
||
SND_NTC_MOD VARCHAR2(1),
|
||
INV_TIT VARCHAR2(60),
|
||
INV_CTT VARCHAR2(60),
|
||
MERC_INV_FLG VARCHAR2(1),
|
||
INV_POST_DT VARCHAR2(8),
|
||
INV_POST_WAY VARCHAR2(60),
|
||
INV_POST_ID VARCHAR2(60),
|
||
INV_CD VARCHAR2(60),
|
||
RCV_AMT_DT VARCHAR2(8),
|
||
RCV_INV_FLG VARCHAR2(1),
|
||
RCV_INV_OPR VARCHAR2(10),
|
||
TRSF_STS VARCHAR2(1),
|
||
TRSF_OPR_ID VARCHAR2(10),
|
||
TRSF_DT VARCHAR2(8),
|
||
UPD_DT VARCHAR2(8),
|
||
UPD_JRN NUMBER(18),
|
||
TM_SMP VARCHAR2(26),
|
||
constraint PK_MKMTMSUM primary key (MERC_ID, CAP_TYP, CAP_ID_TYP, CAP_ID, IS_INV_FLG, PERD_END_DT)
|
||
);
|
||
|
||
comment on column MKMTMSUM.MERC_ID is
|
||
'注意是清算商户编号';
|
||
|
||
comment on column MKMTMSUM.CAP_ID_TYP is
|
||
'0：省
|
||
1：商户
|
||
';
|
||
|
||
comment on column MKMTMSUM.IS_INV_FLG is
|
||
'Y：需要开发票
|
||
N：不需要开发票
|
||
';
|
||
|
||
comment on column MKMTMSUM.PERD_END_DT is
|
||
'指本发票截止交易的记账日期';
|
||
|
||
comment on column MKMTMSUM.INV_STS is
|
||
'0：正常
|
||
1：冻结
|
||
2：已结算
|
||
';
|
||
|
||
comment on column MKMTMSUM.MERC_INV_FLG is
|
||
'空格：未知状态
|
||
1：发票已寄出
|
||
2：款项已收到
|
||
';
|
||
|
||
/*==============================================================*/
|
||
/* Index: IDX_MKMTMSUM1 */
|
||
/*==============================================================*/
|
||
create index IDX_MKMTMSUM1 on MKMTMSUM (
|
||
MERC_ID ASC,
|
||
PERD_END_DT ASC,
|
||
CAP_TYP ASC,
|
||
CAP_ID_TYP ASC,
|
||
CAP_ID ASC
|
||
);
|
||
|
||
/*==============================================================*/
|
||
/* Index: IDX_MKMTMSUM2 */
|
||
/*==============================================================*/
|
||
create index IDX_MKMTMSUM2 on MKMTMSUM (
|
||
PERD_END_DT ASC,
|
||
CAP_ID_TYP ASC,
|
||
CAP_ID ASC
|
||
);
|
||
|
||
/*==============================================================*/
|
||
/* Table: MKMTPINF */
|
||
/*==============================================================*/
|
||
create table MKMTPINF (
|
||
PRD_ID VARCHAR2(12) not null,
|
||
MKT_ID VARCHAR2(10),
|
||
ISS_ID_TYP VARCHAR2(1),
|
||
ISS_ID VARCHAR2(15),
|
||
PRD_NM VARCHAR2(60) not null,
|
||
PRD_DESC VARCHAR2(200),
|
||
PRD_TYP VARCHAR2(1) not null,
|
||
TX_TYP VARCHAR2(2) not null,
|
||
ISS_BUS_TYP VARCHAR2(3),
|
||
ISS_ORD_CAP_TYP VARCHAR2(3),
|
||
ISS_BUS_CNL VARCHAR2(10),
|
||
BEG_REF_AMT NUMBER(9,2) default 0,
|
||
END_REF_AMT NUMBER(9,2),
|
||
DLY_TX_LMT NUMBER(9,2),
|
||
ISS_BILL_AMT NUMBER(9,2) default 0,
|
||
ISS_PCT VARCHAR2(3) default '0',
|
||
ISS_MAX_CNT NUMBER(10,0) default 0,
|
||
ISS_CUR_CNT NUMBER(10,0) default 0,
|
||
OMBL_MAX_CNT NUMBER(4,0) default 0 not null,
|
||
PERD_TYP VARCHAR2(1) default '0',
|
||
PRED NUMBER(4,0),
|
||
EFF_DT_CNT NUMBER(6) default 0,
|
||
CLR_TYP NUMBER(9,0) not null,
|
||
USE_TOT_AMT NUMBER(9,2),
|
||
USE_TOT_CNT NUMBER(9,0),
|
||
NULL_TOT_AMT NUMBER(9,2),
|
||
NULL_TOT_CNT NUMBER(9,0),
|
||
USE_TX_TYP VARCHAR2(2),
|
||
USE_BUS_TYP VARCHAR2(3),
|
||
USE_CNL_TYP VARCHAR2(10),
|
||
USE_ORD_CAP_TYP VARCHAR2(3),
|
||
USE_BEG_REF_AMT NUMBER(9,2),
|
||
USE_END_REF_AMT NUMBER(9,2),
|
||
USE_AMT NUMBER(9,2),
|
||
USE_PCT VARCHAR2(3),
|
||
TM_SMP VARCHAR2(26),
|
||
constraint PK_MKMTPINF primary key (PRD_ID)
|
||
);
|
||
|
||
comment on column MKMTPINF.PRD_ID is
|
||
'活动编号+4位顺序号';
|
||
|
||
comment on column MKMTPINF.PRD_TYP is
|
||
'0：红包
|
||
1：代金券
|
||
2：折扣券
|
||
目前支持红包、代金券
|
||
';
|
||
|
||
comment on column MKMTPINF.PERD_TYP is
|
||
'0: 默认不区分，整个活动有效期相同
|
||
1: 按日
|
||
2: 按月
|
||
4: 指定周期
|
||
';
|
||
|
||
comment on column MKMTPINF.EFF_DT_CNT is
|
||
'同领用日期+天数与使用日期的最大值';
|
||
|
||
/*==============================================================*/
|
||
/* Index: IDX_MKMTPINF1 */
|
||
/*==============================================================*/
|
||
create index IDX_MKMTPINF1 on MKMTPINF (
|
||
MKT_ID ASC,
|
||
ISS_ID ASC
|
||
);
|
||
|
||
/*==============================================================*/
|
||
/* Table: MKMTPUBPRA */
|
||
/*==============================================================*/
|
||
create table MKMTPUBPRA (
|
||
BON_USE_RAT VARCHAR2(50),
|
||
BON_USE_LMT VARCHAR2(15) default '0',
|
||
BON_USR_LOW VARCHAR2(15) default '0',
|
||
NTF_DAY VARCHAR2(3)
|
||
);
|
||
|
||
comment on column MKMTPUBPRA.BON_USE_LMT is
|
||
'默认0，没有上限';
|
||
|
||
comment on column MKMTPUBPRA.BON_USR_LOW is
|
||
'默认0，没有下限';
|
||
|
||
comment on column MKMTPUBPRA.NTF_DAY is
|
||
'0: 不通知
|
||
红包快到期的天数，短信通知用户';
|
||
|
||
/*==============================================================*/
|
||
/* Table: MKMTSEARCH */
|
||
/*==============================================================*/
|
||
create table MKMTSEARCH (
|
||
ACT_ID VARCHAR2(10) not null,
|
||
ARE_CD VARCHAR2(3) not null,
|
||
constraint PK_MKMTSEARCH primary key (ACT_ID, ARE_CD)
|
||
);
|
||
|
||
comment on column MKMTSEARCH.ARE_CD is
|
||
'999: 基地,适用全网';
|
||
|
||
/*==============================================================*/
|
||
/* Index: IDX_MKMTSEARCH1 */
|
||
/*==============================================================*/
|
||
create index IDX_MKMTSEARCH1 on MKMTSEARCH (
|
||
ARE_CD ASC
|
||
);
|
||
|