oracle OPERTION

alter table T_GATEWAY_SET add (feeset_type varchar2(50),pay_type varchar2(50),switch_type varchar2(50),card_type varchar2(50));
COMMENT ON COLUMN T_GATEWAY_SET.feeset_type IS '计费类型';
COMMENT ON COLUMN T_GATEWAY_SET.period IS '结算周期';

alter table T_FEE_SET drop column FEESET_TYPE;

alter table T_ADDR modify (LINK_MAN varchar2(50));

drop index IDX_PAYCH_CODE;
/*==============================================================*/
/* Index: IDX_PAYCH_CODE */
/*==============================================================*/
create unique index IDX_PAYCH_CODE on T_PAYCHANNEL (
CODE ASC,
FEE_TYPE ASC,
MAX_LIM ASC,
MIN_LIM ASC,
CARD_TYPE ASC,
GATEWAY_TYPE ASC
)
tablespace KMPAY_IDX_TS1;


declare
cursor jiesuan is
select t1.*
FROM (SELECT PAYMENT_ID,
BUYER_ID,
ORDER_ID,
CREATE_DATE,
AMOUNT,
STATUS STATUS,
TRANSFER_STATUS TRANSFERSTATUS,
'WITHDRAW' BUSINESSTYPE,
NVL(FEE, 0) FEE
FROM T_WITHDRAW
UNION ALL
SELECT PAYMENT_ID,
BUYER_ID,
T.ORDER_ID,
CREATE_DATE,
AMOUNT,
STATUS STATUS,
TRANSFER_STATUS TRANSFERSTATUS,
'BANKTRANS' BUSINESSTYPE,
NVL(F.FEE, 0) FEE
FROM T_TRADE_ORDER T
LEFT JOIN T_FEE F
ON T.ORDER_ID = F.ORDER_ID
WHERE T.TRADE_CATEGORY = 'BANKTRANS') t1 where t1.status='PROCESSING' and t1.transferstatus='INIT';
begin
for js in jiesuan
loop
--dbms_output.put_line('订单号：'||js.order_id||'订单状态：'||js.status||'打款状态：'||js.TRANSFERSTATUS);
INSERT INTO t_trial VALUES (getId_by_seq('SEQ_TRIAL'), '提现审核', 'WITHDRAW_CONFIRM', js.order_id, 'UNCHECK', 'UNCHECK', '结算列表审核老数据人工处理', '', sysdate);
end loop;
commit;
end;
-------------------------------------------------特别注意：请以此为分割线，上面部分与下面分开执行-----------------------------------------------------
delete FROM T_TRIAL E WHERE (e.belong_type='WITHDRAW_CONFIRM' or e.belong_type='BANKTRANS_CONFIRM') and E.ROWID < (SELECT max(X.ROWID) FROM T_TRIAL X WHERE X.belong_obj_id = E.belong_obj_id);
commit;


/*==============================================================*/
/* Table: T_AUBJECT_ADJUST */
/*==============================================================*/
create table T_AUBJECT_ADJUST
(
AUBJECT_ID NUMBER(16) not null,
PAYMENT_ORDER_NO VARCHAR2(30),
DR_SUBJECTNO VARCHAR2(20),
DR_SUBJECTNAME VARCHAR2(256),
CR_SUBJECTNO VARCHAR2(20),
CR_SUBJECTNAME VARCHAR2(256),
AMT NUMBER(16),
APPLY_TIME TIMESTAMP,
APPLYER_ID VARCHAR2(200),
AUTHO_TIME TIMESTAMP,
AUTHO_ID VARCHAR2(200),
STATE VARCHAR2(20),
REMARK VARCHAR2(500)
)
tablespace KMPAY_DAT_TS1;

comment on table T_AUBJECT_ADJUST is
'科目调账记录表';

comment on column T_AUBJECT_ADJUST.AUBJECT_ID is
'调账流水号';

comment on column T_AUBJECT_ADJUST.PAYMENT_ORDER_NO is
'关联支付流水号';

comment on column T_AUBJECT_ADJUST.DR_SUBJECTNO is
'借方科目编号';

comment on column T_AUBJECT_ADJUST.DR_SUBJECTNAME is
'借方科目名词';

comment on column T_AUBJECT_ADJUST.CR_SUBJECTNO is
'贷方科目编号';

comment on column T_AUBJECT_ADJUST.CR_SUBJECTNAME is
'贷方科目名词';

comment on column T_AUBJECT_ADJUST.AMT is
'调账金额';

comment on column T_AUBJECT_ADJUST. APPLY_TIME is
'申请时间';

comment on column T_AUBJECT_ADJUST.APPLYER_ID is
'申请人';

comment on column T_AUBJECT_ADJUST.AUTHO_TIME is
'审核时间';

comment on column T_AUBJECT_ADJUST.AUTHO_ID is
'审核人';

comment on column T_AUBJECT_ADJUST.STATE is
'调账状态';

comment on column T_AUBJECT_ADJUST.REMARK is
'调账备注';

alter table T_AUBJECT_ADJUST add constraint PK_T_AUBJECT_ADJUST primary key (AUBJECT_ID);

drop sequence SEQ_AUBJECTADJUST;

create sequence SEQ_AUBJECTADJUST
increment by 1
start with 10001
maxvalue 99999999999999
minvalue 10001
cache 20;