Procedure demo

--generate checksum for all records in transaction history
--remember to change checksumKey
DECLARE checksumKey varchar2(100) := 'Tcbs@20022020';

rawData varchar2(1000) := '';

newchecksum varchar2(1000);

BEGIN DBMS_OUTPUT.PUT_LINE('INFO: Workflow initiated');


FOR txh IN
  (SELECT *
   FROM IXU_TRANSACTION_HISTORY
   WHERE CAMPAIGN_ID =2006
   ORDER BY ID DESC) LOOP rawData := concat(':', txh.HISTORY_KEY);

IF txh.EXPIRED_DATE IS NULL THEN rawData := concat('', rawData);

rawData := concat(':', rawData);

ELSE rawData := concat(TO_CHAR(txh.EXPIRED_DATE, 'YYYY-MM-DD'), rawData);

rawData := concat(':', rawData);

END IF;

rawData := concat(to_char(txh.OUTSTANDING, 'FM99999999999999990.00'), rawData);

rawData := concat(':', rawData);

rawData := concat(to_char(txh.POINT, 'FM99999999999999990.00'), rawData);

rawData := concat(':', rawData);

--add campaignid
 rawData := concat(txh.CAMPAIGN_ID, rawData);

--rawData := concat('12', rawData);
 rawData := concat(':', rawData);

rawData := concat(txh.ACTION, rawData);

rawData := concat(':', rawData);

rawData := concat(txh.AWARD_TYPE, rawData);

rawData := concat(':', rawData);

IF txh.ROLE IS NULL THEN rawData := concat('', rawData);

rawData := concat(':', rawData);

ELSE rawData := concat(txh.ROLE, rawData);

rawData := concat(':', rawData);

END IF;

rawData := concat(txh.TCBSID, rawData);

rawData := concat(':', rawData);

rawData := concat(checksumKey, rawData);

-- 	rawData := fmt.Sprintf("%s:%s:%s:%s:%s:%d:%0.2f:%0.2f:%s:%s", checksumKey, glTx.TcbsID, glTx.Role, glTx.AwardType, glTx.Action, glTx.CampaignID, glTx.Point, newPointOutstanding, expireDateString, glTx.HistoryKey)
 dbms_output.put_line('rawData ' || rawData);


SELECT LOWER(RAWTOHEX(STANDARD_HASH(rawData, 'SHA256'))) INTO newchecksum
FROM dual;

dbms_output.put_line('newchecksum ' || newchecksum);

END LOOP;


COMMIT ;

END;

--------------------------UPDATE with campaign id = 12-------------------
 --generate checksum for all records in transaction history
--remember to change checksumKey
DECLARE checksumKey varchar2(100) := 'Tcbs@20022020';

rawData varchar2(1000) := '';

newchecksum varchar2(1000);

BEGIN DBMS_OUTPUT.PUT_LINE('INFO: Workflow initiated');


FOR txh IN
  (SELECT *
   FROM IXU_TRANSACTION_HISTORY
   WHERE CAMPAIGN_ID =2006
   ORDER BY ID DESC) LOOP rawData := concat(':', txh.HISTORY_KEY);

IF txh.EXPIRED_DATE IS NULL THEN rawData := concat('', rawData);

rawData := concat(':', rawData);

ELSE rawData := concat(TO_CHAR(txh.EXPIRED_DATE, 'YYYY-MM-DD'), rawData);

rawData := concat(':', rawData);

END IF;

rawData := concat(to_char(txh.OUTSTANDING, 'FM99999999999999990.00'), rawData);

rawData := concat(':', rawData);

rawData := concat(to_char(txh.POINT, 'FM99999999999999990.00'), rawData);

rawData := concat(':', rawData);

--add campaignid
 --	   	rawData := concat(txh.CAMPAIGN_ID, rawData);
rawData := concat('12', rawData);

rawData := concat(':', rawData);

rawData := concat(txh.ACTION, rawData);

rawData := concat(':', rawData);

rawData := concat(txh.AWARD_TYPE, rawData);

rawData := concat(':', rawData);

IF txh.ROLE IS NULL THEN rawData := concat('', rawData);

rawData := concat(':', rawData);

ELSE rawData := concat(txh.ROLE, rawData);

rawData := concat(':', rawData);

END IF;

rawData := concat(txh.TCBSID, rawData);

rawData := concat(':', rawData);

rawData := concat(checksumKey, rawData);

-- 	rawData := fmt.Sprintf("%s:%s:%s:%s:%s:%d:%0.2f:%0.2f:%s:%s", checksumKey, glTx.TcbsID, glTx.Role, glTx.AwardType, glTx.Action, glTx.CampaignID, glTx.Point, newPointOutstanding, expireDateString, glTx.HistoryKey)
 dbms_output.put_line('rawData ' || rawData);


SELECT LOWER(RAWTOHEX(STANDARD_HASH(rawData, 'SHA256'))) INTO newchecksum
FROM dual;

dbms_output.put_line('newchecksum ' || newchecksum);


UPDATE IXU_TRANSACTION_HISTORY
SET CHECKSUM = newchecksum,
    CAMPAIGN_ID = 12
WHERE ID = txh.ID ;

END LOOP;


COMMIT ;

END;