--Order 20014860.80

ALTER TABLE CREDITCARDTRANSACTION
   MODIFY(ARTRANSACTION_ID NUMBER NULL);


CREATE TABLE CREDITCARDTRANSACTIONREFUND
(
	CREDITCARDTRANSACTION_ID NUMBER NOT NULL ,
	ARTRANSACTION_ID     NUMBER NOT NULL ,
	SYSTEMUSER_ID        NUMBER NOT NULL ,
	TIMESTAMP            DATE NOT NULL 
);

ALTER TABLE CREDITCARDTRANSACTIONREFUND
ADD CONSTRAINT  XPKCREDITCARDTRANSACTIONREFUND PRIMARY KEY (CREDITCARDTRANSACTION_ID,ARTRANSACTION_ID);

ALTER TABLE CREDITCARDTRANSACTIONREFUND
ADD CONSTRAINT CCTREFUND_CCT FOREIGN KEY (CREDITCARDTRANSACTION_ID) REFERENCES CREDITCARDTRANSACTION (CREDITCARDTRANSACTION_ID) ON DELETE CASCADE;

ALTER TABLE CREDITCARDTRANSACTIONREFUND
ADD CONSTRAINT CCTREFUND_ARTRANSACTION FOREIGN KEY (ARTRANSACTION_ID) REFERENCES ARTRANSACTION (ARTRANSACTION_ID) ON DELETE CASCADE;

INSERT INTO CREDITCARDTRANSACTIONREFUND(CREDITCARDTRANSACTION_ID, ARTRANSACTION_ID, SYSTEMUSER_ID, TIMESTAMP)
SELECT CCT.CREDITCARDTRANSACTION_ID, CCT.PARTIALREFUNDARTRANSACTION_ID, 1, SYSDATE
  FROM CREDITCARDTRANSACTION CCT
  WHERE CCT.PARTIALREFUNDARTRANSACTION_ID IS NOT NULL;

COMMIT;

ALTER TABLE CREDITCARDTRANSACTION
  DROP COLUMN PARTIALREFUNDARTRANSACTION_ID CASCADE CONSTRAINTS;
