24 lines
1016 B
SQL
24 lines
1016 B
SQL
--drop sequence seq_trancoding_id;
|
|
|
|
create sequence seq_trancoding_id
|
|
increment by 1
|
|
start with 1
|
|
nomaxvalue
|
|
nocycle
|
|
nocache;
|
|
|
|
--alter table CAPCSWRULE drop column cswrule_Id;
|
|
alter table CAPCSWRULE add DST_TXN_STS VARCHAR2(2);
|
|
alter table CAPCSWRULE add CSWRULE_ID NUMBER ;
|
|
|
|
|
|
update CAPCSWRULE set CSWRULE_ID = seq_trancoding_id.nextval;
|
|
alter table CAPCSWRULE modify CSWRULE_ID NUMBER not null;
|
|
alter table CAPCSWRULE add constraint CAPCSWRULE_PK primary key(cswrule_Id);
|
|
|
|
|
|
UPDATE CAPCSWRULE SET DST_TXN_STS='S' WHERE RUL_TYP='BIS' AND CAP_TYP IN ('CAP_PAY_SIG','CAP_RCV_SIG') AND DST_RSP_CD='00';
|
|
UPDATE CAPCSWRULE SET DST_TXN_STS='P' WHERE RUL_TYP='BIS' AND CAP_TYP IN ('CAP_PAY_SIG','CAP_RCV_SIG') AND DST_RSP_CD='09';
|
|
UPDATE CAPCSWRULE SET DST_TXN_STS='T' WHERE RUL_TYP='BIS' AND CAP_TYP IN ('CAP_PAY_SIG','CAP_RCV_SIG') AND DST_RSP_CD='83';
|
|
UPDATE CAPCSWRULE SET DST_TXN_STS='F' WHERE RUL_TYP='BIS' AND CAP_TYP IN ('CAP_PAY_SIG','CAP_RCV_SIG') AND DST_RSP_CD NOT IN ('00','09','83');
|