Updating SSIs in bulk – How I did it

Practical case: today, someone asked me if I could help: some trades were imported and for these ones, everything has to be considered fine: even if SSIs are missing, future flows have to be ok and not appear as missing SSIs. New trades on the other hand (even if they are on the same counterpart) have to have proper SSIs or return a missing SSIs message.

Putting some dummy SSIs (catch’all) would not work as it would affect new trades as well. Even putting a validity up to yesterday would not work either as future payments from existing trades should not show any missing SSIs error.

So the only solution I thought of: specific SSIs for imported trades. I was not completely sure of the table holding SSIs (I had to confirm it), so I entered a trade with customized SSIs and I put in one of the fields a very specific string (if you’re curious ABCDE, yes I know, very very original). Then I searched through the DB trace file (see earlier posts for how to) to find the table.

The problem I then had was that the table had a field Trade number that was not in line with my trade number (even if I was sure it was the right trade). So I searched the trace file for the trade number that I did not know about and could find it within the transaction header of the transaction. So I then had everything: from my deal number to intermediary reference (in my case it was only 1 but there could have been multiple) to final data.

Turning that into a script was then a breeze (for people curious enough I’m using the same specific dummy SSIs for all the imported trades) and voila! you now have specific SSIs for all imported trades and new ones will fetch automatically from the SSIs assignments.

Is that a perfect solution? Of course not, you can have issues upon performing events but still, it a very valid one.