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;