Manual alerts contain an incorrect LST_TRANSACTION_TIMESTAMP value


You can create a manual alert on a transaction in SAS® Fraud Management Analyst Workstation. In the FCM_ALERT table, the LST_TRANSACTION_TIMESTAMP value of this manual alert should be the timestamp of the transaction for which the alert was created.

In SAS® Fraud Management 5, SAS® Fraud Management 6.1, and SAS® Fraud Management 6.2 Hot Fix 3, the LST_TRANSACTION_TIMESTAMP value of alert manually created from a transaction is incorrect.

Here is an example: The SAS Fraud Management SAS® Web Application Server is in Coordinated Universal Time (UTC). If you create a manual alert on a transaction with RQO_TRAN_DATE = 11-APR-17 and RQO_TRAN_TIME = 01-JAN-70 08.52.57 PM, the alert contains LST_TRANSACTION_TIMESTAMP 11-APR-17 08.52.57 AM.

Workaround

  1. Set the SAS Fraud Management SAS Web Application Server host time zone to UTC using following command, or consult your UNIX administrator:

sudo timedatectl set-timezone UTC

  1. Extract the code of the SOR/FCMCORE RECLASSIFY_ALERT stored procedure using the SQL query below.

SELECT DBMS_METADATA.GET_DDL('PROCEDURE', 'RECLASSIFY_ALERT') FROM DUAL;

  1. In the extracted SOR/FCMCORE RECLASSIFY_ALERT stored procedure code, change the timestamp format from hh to hh24 for both the Insert and Update sections. 

Here is an example for SAS Fraud Management 5 and 6.1:

Before:

TO_TIMESTAMP(to_char(i_rqo_tran_date)

             || ' '

             ||(to_char(i_rqo_tran_time, 'hh:mi:ss')))

After:

TO_TIMESTAMP(to_char(i_rqo_tran_date)

             || ' '

             ||(to_char(i_rqo_tran_time, 'hh24:mi:ss')))

Here is an example for SAS Fraud Management 6.2:

Before:

TO_TIMESTAMP (
         TO_CHAR (i_rqo_tran_date)
         || ' '
         || (TO_CHAR (i_rqo_tran_time, 'HH:mi:ss')),'YYYY-MM-DD HH:MI:SS')

After:

TO_TIMESTAMP (
         TO_CHAR (i_rqo_tran_date)
         || ' '
         || (TO_CHAR (i_rqo_tran_time, 'HH24:mi:ss')),'YYYY-MM-DD HH24:MI:SS')

  1. Save the updated SOR/FCMCORE RECLASSIFY_ALERT stored procedure body or code in Step 3 as a .sql file
  2. Execute the .sql file with the SOR/FCMCORE schema owner.

Note: For SAS Fraud Management 6.2, SAS plans to fix this problem in a future hot fix.