OFAC Compliance queries

Question

OFAC Compliance queries

Sridhar-137443

Hall of Fame

Points: 3762
More actions
January 6, 2010 at 8:23 am

#386047

Hi,
I am trying to implement OFAC Compliance check using sql queries to search for data in our database. If anybody has done stuff similar to this, can you please advise how u did it?
Thanks,
sridhar.

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

Sridhar-137443 (1/6/2010)
Hi,
I am trying to implement OFAC Compliance check using sql queries to search for data in our database. If anybody has done stuff similar to this, can you please advise how u did it?
Thanks,
sridhar.

Sorry, not familiar with OFAC, what is this?

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 2

Binged it now I know. Not something I really have to worry about where I currently work.

OFAC - Office of Foreign Asset Control.

Sridhar-137443 Hall of Fame Points: 3762 More actions · Answer 3

OFAC stands for Office of Foreign Assets Control. Their database has a list of names, addresses, passport numbers etc to query against our databases. If we find a matching in our database we need to report to the government.

for example

one of the tables in their database has first name, last name. so for each entry in this table we need to check if that first name and lastname exists in our database. Then there is another table that has the addresses. so we need to do the same thing for addresses and see if we can find any matches.

I noticed that some people are using SOUNDEX , DIFFERENCE for this. I thought I could use full text search since the names won't be exactly same. so I am trying to find out if anybody has already done something like this.

Thanks,

sridhar.

sturner SSC-Insane Points: 22411 More actions · Answer 4

yes, we do this every day (have to in the banking industry to comply with regs)

I set up a .net service the downloads the OFAC database everyday and bulk inserts into a staging DB. Then reorg it a bit. The I run a stored proc that scans our entire payee table against the OFAC adresses only (the scan proc doesn't care about names). It does this by joining the various address components using "like" and some fuzzy match logic we set up. If we get a hit, we flag the payment for closer scrutiny before we let it go through.

The match logic is fuzzy enough that we get an occasional match, but since the addresses in OFAC don't always adhere to a standard format we have to err on safe side and let a human make the final determination.

The probability of survival is inversely proportional to the angle of arrival.

Sridhar-137443 Hall of Fame Points: 3762 More actions · Answer 5

Hi,

Will it be possible to share the query text of the stored procedure?

Thanks,

sridhar.

sturner SSC-Insane Points: 22411 More actions · Answer 6

yes, I can do that if you provide me an email address.

The probability of survival is inversely proportional to the angle of arrival.

Sridhar-137443 Hall of Fame Points: 3762 More actions · Answer 7

Thank you. Please send it to dsridhar10@gmail.com. I appreciate your help.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 8

You really should have used PM to send your email address, now it is out there in the cyberworld.

sturner SSC-Insane Points: 22411 More actions · Answer 9

oh well, at least you can get another gmail account. lol

Query script is on the way.

The probability of survival is inversely proportional to the angle of arrival.

wbar SSC Rookie Points: 31 More actions · Answer 10

Hi Sturner,

I'm in a similar position to sridhar and was hoping that you could also help me out?

Thanks

wbar