Sophis Risque: how to add timezone on deals?
May
15
Written by:
5/15/2014 4:39 PM
Using Sophis Risque 5.3.7 and equivalent Value version or less, negotiation date of the deals done by the trader are stored into database using the local time zone of the machine on which Sophis Risque is launched. Unfortunately, on multisites/multi countries architecture, the timezone is not stored into the database, raising an issue on the last requirements for compliance and regulatory checks. The goal of this post is to explain how to add the required timezone.
First, fields to store the timezone (TMZ) have to be created on at least the following Oracle tables: HISTOMVTS, AUDIT_MVT, JO_HISTOMVTS. Other Oracle table linked to HISTOMVTS could be modified, but depending on the use done, it can be optional. The following script can create them:
alter table histomvts add bankname_tmz varchar2(6);
alter table audit_mvt add bankname_tmz varchar2(6);
alter table jo_histomvts add bankname_tmz varchar2(6);
Depending on the configuration of the database, it could be than an allocation of 7 is required (I don't know exactly why, since the timezone should be stored using the following format (+/-)XX:XX), but seems that the Oracle TZ_OFFSET used could return more thant 6 caracters...). Default settings can be done too by adding the default tz_offset(sessiontimezone) attribute. When adding default value, triggers using the tables have to be deactivated.
Once updated, at least the trigger AUDIT_MVT, which copies data between HISTOMVTS and the JO_HISTOMVTS and AUDIT_MVT table has to be modified: effectively, in this trigger the '*' tag is not used as it is done between TITRES and TITRES_HISTO. The code could look like this one:
create or replace TRIGGER "SOPHIS".AUDIT_MVT AFTER INSERT OR UPDATE OR DELETE ON HISTOMVTS
FOR EACH ROW
begin
if deleting then
begin
...
insert into JO_HISTOMVTS(...., bankname_tmz) VALUES(...., :old.bankname_tmz);
...
insert into AUDIT_MVT(...., bankname_tmz) VALUES(....,:old.bankname_tmz);
if updating
....
insert into JO_HISTOMVTS(...., bankname_tmz) VALUES(...., :old.bankname_tmz);
...
insert into AUDIT_MVT(...., bankname_tmz) VALUES(....,:old.bankname_tmz);
In order to display the timezone in the deal input dialog, the CSRTransactionDialog class has to be derivated and a CSRStaticText could be added. In the constructor of the derivated class, the following code could for example be added:
fElementList[i++] = new CSRStaticText(this, IDC_BANKNAME_TMZ-ID_ITEM_SHIFT,6,GetCurrentTMZ(), "BANKNAME_TMZ");
where the code of the GetCurrentTMZ() method calls the following Oracle query:
select tz_offset(sessiontimezone) from dual
The field cannot be edited at screen. It corresponds to the configuration of the machine.
During extraction, in order to compare more easily the different negotiation hours, the following SQL code can be used:
select SYS_EXTRACT_UTC(TO_TIMESTAMP_TZ(to_char(dateneg+heureneg/86400,'YYYY-MM-DD HH24:MI:SS') || ' ' || bankname_tmz,'YYYY-MM-DD HH24:MI:SS TZH:TZM')) from histomvts
That's all, folks!