--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');