Updates to Fraud Analysis Techniques Using ACL – 2009

September 2009

Completeness script - for Unicode version of ACL only:

Change to read:

Bonus Features:

The following is a script that I wrote and that you are permitted to download and use as long as you retain the “CAATS 2009” info.

Consecutive Invoices:

The following ACL script searches for consecutive invoices for a vendor. It creates two file Consec_inv2 – which has all consecutive invoices; and Consec_inv3 which only contains records for a vendor when there is 3 or more consecutive invoices.

set safety off
DIALOG (DIALOG TITLE "CAATS - consecutive invoices" WIDTH 512 HEIGHT 366 ) (BUTTONSET TITLE "&OK;&Cancel" AT 396 24 DEFAULT 1 ) (TEXT TITLE "Consecutive Invoices" AT 24 28 ) (TEXT TITLE "Scripts will test for consecutive invoices e.g. 123 and 124 or A12 and A13." AT 60 64 WIDTH 293 HEIGHT 30 ) (TEXT TITLE "Note: will not identify A299 and A300 as consecutive" AT 60 100 ) (TEXT TITLE "Select input file" AT 48 148 ) (ITEM TITLE "f" TO "v_infile" AT 108 180 WIDTH 240 HEIGHT 160 ) (TEXT TITLE "CAATS 2009" AT 372 316 )
OPEN %v_infile%
DIALOG (DIALOG TITLE "Field selection" WIDTH 492 HEIGHT 381 ) (BUTTONSET TITLE "&OK;&Cancel" AT 360 24 DEFAULT 1 ) (TEXT TITLE "Select Parameters" AT 24 52 ) (TEXT TITLE "Select field containing invoice number (must be character)" AT 264 112 WIDTH 168 HEIGHT 34 ) (TEXT TITLE "Select field containing Vendor Number (must be character)" AT 36 112 WIDTH 171 HEIGHT 29 ) (ITEM TITLE "C" TO "v_vendno" AT 36 168 WIDTH 173 HEIGHT 157 ) (ITEM TITLE "C" TO "v_invno" AT 264 168 WIDTH 186 HEIGHT 164 )

SORT on %v_vendno% %v_invno% to temp OPEN

DEFINE Field U_invno Computed Include(Upper(%v_invno%),'QWERTYUIOPLKJHGFDSAUZXCVBNM0987654321')
DEFINE FIELD Invno1 COMPUTED substr(U_invno,1,LEN(ALLTRIM(%v_invno%))-1)
DEFINE FIELD invno2 COMPUTED last(alltrim(U_invno),1)
DEFINE FIELD invno2a COMPUTED ascii(invno2)

prev_vend=blanks(15)
prev_invno2a=0
prev_invno1=blanks(15)
prev_invno=blanks(15)
prev_inv1=%v_invno%
Ext_Flag='N'
v_ctr=1

Group if Prev_vend=%v_vendno% and prev_invno1=invno1 and prev_invno2a=invno2a
Extract str(recno()-1,12) as 'Recnum' str(v_ctr,5) as 'Consec_Flag' prev_vend prev_invno prev_inv1 to temp2 EOF EOF
prev_invno1=invno1
prev_invno2a=invno2a+1
prev_vend=%v_vendno%
prev_invno=%v_invno%
Ext_Flag='Y'
ELSE if (Prev_vend<>%v_vendno% or prev_invno1<>invno1 or prev_invno2a<>invno2a) and ext_flag='Y'
Extract str(recno()-1,12) as 'Recnum' str(v_ctr,5) as 'Consec_Flag' prev_vend prev_invno prev_inv1 to temp2 EOF
prev_invno1=invno1
prev_invno2a=invno2a+1
prev_vend=%v_vendno%
prev_invno=%v_invno%
prev_inv1=%v_invno%
Ext_Flag='N'
v_ctr=v_ctr+1
ELSE
prev_invno1=invno1
prev_invno2a=invno2a+1
prev_vend=%v_vendno%
prev_invno=%v_invno%
prev_inv1=%v_invno%
Ext_Flag='N'
v_ctr=v_ctr+1
END

OPEN temp2
SUMM on Recnum OTHER ALL to temp OPEN
Extract Consec_Flag prev_vend prev_invno prev_inv1 to consec_inv2 OPEN
Summ on consec_flag Other Prev_vend prev_invno prev_inv1 to temp OPEN
Define field vend_inv computed prev_vend+prev_inv1
INDEX ON vend_inv TO "Vend_Inv" 
OPEN consec_inv2
Define field vend_inv computed prev_vend+prev_inv1
DEFINE RELATION vend_inv WITH temp INDEX Vend_Inv
Extract prev_vend prev_invno prev_inv1 to consec_inv3 OPEN if temp.count>=3

Delete temp.fil OK
delete format temp OK
Delete temp2.fil OK
delete format temp2 OK
set safety on