DUPLICATE ADDRESSES and SSN's
NOTE: This message and script was posted in November 2006 on the ACL User Forum:
I was going to post this as a response to the Duplicate Address queestion, but this is SIGNIFICANTLY more than what was asked for and I don't have the time to break it down... but I think you'll appreciate it. This is a script that looks for duplicate SSN and Addresses ON TWO TABLES (which is why I've created a new subject.) It will also validate if the SSN is valid (assuming you get the list from the SSN Adminstration website.)
This script is written to compare the SSN and TIN between two tables (EG Vendor/Employee.) But if you look at the subscripts: Dialogue Address and Validate Address you'll see how I handled situations wherein we have up to 4 address segments.
But now that I'm thinking about it, I suspect that I would write this scipt with a GROUP-END command if I were to write it today---that would be significantly shorter. Here is the original script I had:
********Main Script***************
SET SAFETY OFF
DIALOG (DIALOG TITLE "User Dialog" WIDTH 493 HEIGHT 450 ) (BUTTONSET TITLE "&OK;&Cancel" AT 370 12 DEFAULT 1 ) (TEXT TITLE "There are two scripts designed to identify potential fraudulent vendors." AT 36 28 WIDTH 303 HEIGHT 34 ) (TEXT TITLE "First, a SSN-TIN validation. This test will check to see if there are any matches between a Vendor's TIN's and an Employee's SSN." AT 36 76 WIDTH 439 HEIGHT 33 ) (TEXT TITLE "Second, is an address validation. This test will check to see if there are any matches between a Vendor's Address and an Employee's Address." AT 36 136 WIDTH 452 HEIGHT 54 ) (TEXT TITLE "Please Select Which Test You Wish to Run:" AT 36 352 ) (TEXT TITLE "cc: Porter Broyles" AT 360 412 ) (TEXT TITLE "If you choose to compare SSN and TIN's, you will be prompted for a third test. This test will validate if the Social Security Number conform to the guidance issued by the Social Security Administration. " AT 36 196 WIDTH 441 HEIGHT 64 ) (TEXT TITLE "It will not validate if a SSN is valid for a specific individual, but will alert you if the number has not been issued." AT 36 268 WIDTH 440 HEIGHT 68 ) (CHECKBOX TITLE "Check Addresses" TO "s_addresscheck" AT 72 384 ) (CHECKBOX TITLE "Check TIN/SSN" TO "s_tincheck" AT 72 420 )
ASSIGN FLAG = "F"
IF s_addresscheck = T DO SCRIPT Dialogue_Address WHILE Flag = "F"
ASSIGN FLAG = "F"
IF s_tincheck = T DO SCRIPT Dialogue_Tin WHILE Flag = "F"
IF s_addresscheck = T DO SCRIPT Validate_Address
IF s_tincheck = T DO SCRIPT Validate_Tin
SET SAFETY ON
****************Dialogue Address**************
SET SAFETY OFF
ASSIGN FLAG = "T"
COMMENT A
Pause "Requesting data to compare Employee Addresses with Vendor Addresses."
IF "%_v_ven_data%" = "" ASSIGN v_ven_data = ""
IF "%_v_emp_data%" ="" ASSIGN %_v_emp_data% =""
DIALOG (DIALOG TITLE "User Dialog" WIDTH 492 HEIGHT 255 ) (BUTTONSET TITLE "&OK;&Cancel" AT 396 12 DEFAULT 1 ) (TEXT TITLE "Please enter the table which contains the Vendor Data" AT 24 40 ) (TEXT TITLE "Please enter the table with the Employee Data." AT 24 148 ) (ITEM TITLE "f" TO "_v_ven_data" AT 48 72 WIDTH 283 DEFAULT "%_v_ven_data%" ) (ITEM TITLE "f" TO "_v_emp_data" AT 48 180 WIDTH 279 DEFAULT "%_v_emp_data%" )
OPEN %_v_ven_Data%
COMMENT B
DELETE %_v_ven_ID%_std OK
DELETE %_v_ven_name%_std OK
DELETE %_v_ven_address_1%_std OK
DELETE %_v_ven_address_2%_std OK
DELETE %_v_ven_address_3%_std OK
DELETE %_v_ven_address_4%_std OK
DELETE v_ven_address_5_std OK
DELETE v_ven_address_6_std OK
DELETE v_ven_address_7_std OK
DELETE %_v_ven_address_1%_all OK
DELETE %_v_ven_zip%1 OK
COMMENT C
DIALOG (DIALOG TITLE "*" WIDTH 549 HEIGHT 494 ) (BUTTONSET TITLE "&OK;&Cancel" AT 432 12 DEFAULT 1 ) (TEXT TITLE "Select Field with Vendor Name " AT 24 88 ) (TEXT TITLE "Select field with Vendor ID " AT 24 124 ) (TEXT TITLE "Select Field with Vendor Address 1" AT 24 160 ) (TEXT TITLE "Select Field with Vendor Address 2" AT 24 196 ) (TEXT TITLE "Select Field with Vendor Address 3" AT 24 232 ) (TEXT TITLE "Select Field with Vendor Address 4" AT 24 268 ) (TEXT TITLE "Select Field with Vendor State" AT 24 304 ) (TEXT TITLE "Select Field with Vendor Zip" AT 24 340 ) (TEXT TITLE "Select Other Field to be captured" AT 24 376 ) (ITEM TITLE "C" TO "_v_ven_name" AT 264 84 WIDTH 237 DEFAULT "%_v_ven_name%" ) (ITEM TITLE "CN" TO "_v_ven_ID" AT 264 120 WIDTH 237 DEFAULT "%_v_ven_ID%" ) (ITEM TITLE "C" TO "_v_ven_address_1" AT 264 156 WIDTH 235 DEFAULT "%_v_ven_address_1%" ) (ITEM TITLE "C" TO "_v_ven_address_2" AT 264 192 WIDTH 235 ) (ITEM TITLE "C" TO "_v_ven_address_3" AT 264 228 WIDTH 235 ) (ITEM TITLE "C" TO "_v_ven_address_4" AT 264 264 WIDTH 236 ) (ITEM TITLE "C" TO "_v_ven_state" AT 264 300 DEFAULT "%_v_ven_state%" ) (ITEM TITLE "CN" TO "_v_ven_zip" AT 264 336 DEFAULT "%_v_ven_zip%" ) (ITEM TITLE "CND" TO "_v_ven_other_1" AT 264 372 WIDTH 249 DEFAULT "%_v_ven_other_1%" ) (ITEM TITLE "CND" TO "_v_ven_other_2" AT 264 420 WIDTH 247 DEFAULT "%_v_ven_other_2%" ) (TEXT TITLE "*" AT 516 88 WIDTH 8 ) (TEXT TITLE "*" AT 516 124 ) (TEXT TITLE "*" AT 516 160 ) (TEXT TITLE "*" AT 396 304 ) (TEXT TITLE "*" AT 396 340 ) (TEXT TITLE "*" AT 528 376 ) (TEXT TITLE "*" AT 528 424 )
IF "%_v_ven_address_1%" <> "" ASSIGN _VV1 = "T"
IF "%_v_ven_address_2%" <> "" ASSIGN _VV2 = "T"
IF "%_v_ven_address_3%" <> "" ASSIGN _VV3 = "T"
IF "%_v_ven_address_4%" <> "" ASSIGN _VV4 = "T"
IF "%_v_ven_address_1%" = "" ASSIGN _VV1 = "F"
IF "%_v_ven_address_2%" = "" ASSIGN _VV2 = "F"
IF "%_v_ven_address_3%" = "" ASSIGN _VV3 = "F"
IF "%_v_ven_address_4%" = "" ASSIGN _VV4 = "F"
IF FTYPE("%_v_ven_zip%") = "N" DEFINE FIELD %_v_ven_zip%1 COMPUTED STRING(%_v_ven_zip%,5)
IF FTYPE("%_v_ven_zip%") <> "N" DEFINE FIELD %_v_ven_zip%1 COMPUTED SUBSTRING(ALLTRIM(%_v_ven_zip%),1,5)
DELETE %_v_ven_zip%2 OK
OPEN %_v_emp_Data%
COMMENT D
DELETE %_v_emp_ID%_std OK
DELETE %_v_emp_fname%_std OK
DELETE %_v_emp_lname%_std OK
DELETE %_v_emp_address_1%_std OK
DELETE %_v_emp_address_2%_std OK
DELETE %_v_emp_address_3%_std OK
DELETE %_v_emp_address_4%_std OK
DELETE v_emp_address_5_std OK
DELETE v_emp_address_6_std OK
DELETE v_emp_address_7_std OK
DELETE %_v_emp_address_1%_all OK
DELETE %_v_emp_zip%1 OK
DIALOG (DIALOG TITLE "User Dialog" WIDTH 534 HEIGHT 544 ) (BUTTONSET TITLE "&OK;&Cancel" AT 12 468 DEFAULT 1 ) (TEXT TITLE "Select Field with Employee First Name" AT 12 88 ) (TEXT TITLE "Select field with Employee ID" AT 12 124 ) (TEXT TITLE "Select Field with Employee Address 1" AT 12 160 ) (TEXT TITLE "Select Field with Employee Address 2" AT 12 196 ) (TEXT TITLE "Select Field with Employee Address 3" AT 12 232 ) (TEXT TITLE "Select Field with Employee Address 4" AT 12 268 ) (TEXT TITLE "Select Field with Employee State" AT 12 304 ) (TEXT TITLE "Select Field with employee Zip" AT 12 340 ) (TEXT TITLE "Select Other Fields to be captured" AT 12 376 ) (ITEM TITLE "C" TO "_v_emp_lname" AT 264 48 WIDTH 248 HEIGHT 99 DEFAULT "%_v_emp_lname%" ) (ITEM TITLE "C" TO "_v_emp_fname" AT 264 84 WIDTH 246 DEFAULT "%_v_emp_fname%" ) (ITEM TITLE "CN" TO "_v_emp_ID" AT 264 120 WIDTH 248 DEFAULT "%_v_emp_ID%" ) (ITEM TITLE "C" TO "_v_emp_address_1" AT 264 156 WIDTH 247 DEFAULT "%_v_emp_address_1%" ) (ITEM TITLE "C" TO "_v_emp_address_2" AT 264 192 WIDTH 248 ) (ITEM TITLE "C" TO "_v_emp_address_3" AT 264 228 WIDTH 248 ) (ITEM TITLE "C" TO "_v_emp_address_4" AT 264 264 WIDTH 250 ) (ITEM TITLE "C" TO "_v_emp_state" AT 264 300 DEFAULT "%_v_emp_state%" ) (ITEM TITLE "CN" TO "_v_emp_zip" AT 264 336 DEFAULT "%_v_emp_zip%" ) (ITEM TITLE "CND" TO "_v_emp_other_1" AT 264 372 WIDTH 250 DEFAULT "%_v_emp_other_1%" ) (ITEM TITLE "CND" TO "_v_emp_other_2" AT 264 420 WIDTH 249 DEFAULT "%_v_emp_other_2%" ) (TEXT TITLE "Select Field with Employee Last Name" AT 12 52 ) (TEXT TITLE "*" AT 516 52 ) (TEXT TITLE "*" AT 516 88 ) (TEXT TITLE "*" AT 516 124 ) (TEXT TITLE "*" AT 516 160 ) (TEXT TITLE "*" AT 396 304 ) (TEXT TITLE "*" AT 396 340 ) (TEXT TITLE "*" AT 516 376 ) (TEXT TITLE "*" AT 516 424 )
IF "%_v_emp_address_1%" <> "" ASSIGN _VE1 = "T"
IF "%_v_emp_address_2%" <> "" ASSIGN _VE2 = "T"
IF "%_v_emp_address_3%" <> "" ASSIGN _VE3 = "T"
IF "%_v_emp_address_4%" <> "" ASSIGN _VE4 = "T"
IF "%_v_emp_address_1%" = "" ASSIGN _VE1 = "F"
IF "%_v_emp_address_2%" = "" ASSIGN _VE2 = "F"
IF "%_v_emp_address_3%" = "" ASSIGN _VE3 = "F"
IF "%_v_emp_address_4%" = "" ASSIGN _VE4 = "F"
IF FTYPE("%_v_emp_zip%") = "N" DEFINE FIELD %_v_emp_zip%1 COMPUTED STRING(%_v_emp_zip%,5)
IF FTYPE("%_v_ven_zip%") <> "N" DEFINE FIELD %_v_emp_zip%1 COMPUTED SUBSTRING(ALLTRIM(%_v_emp_zip%),1,5)
DELETE %_v_emp_zip%2 OK
IF "%_v_emp_fname%" = "" ASSIGN FLAG = "F"
IF "%_v_emp_ID%" = "" ASSIGN FLAG = "F"
IF "%_v_emp_address_1%" = "" ASSIGN FLAG = "F"
IF "%_v_emp_state%" = "" ASSIGN FLAG = "F"
IF "%_v_emp_zip%" = "" ASSIGN FLAG = "F"
IF "%_v_emp_other_1%" = "" ASSIGN FLAG = "F"
IF "%_v_emp_other_2%" = "" ASSIGN FLAG = "F"
IF "%_v_ven_ID%" = "" ASSIGN FLAG = "F"
IF "%_v_ven_name%" = "" ASSIGN flag = "F"
IF "%_v_ven_address_1%" = "" ASSIGN flag = "F"
IF "%_v_ven_state%" = "" ASSIGN flag = "F"
IF "%_v_ven_zip%" = "" ASSIGN flag = "F"
IF "%_v_ven_other_1%" = "" ASSIGN flag = "F"
IF "%_v_ven_other_2%" = "" ASSIGN flag = "F"
If Flag = "F" Pause "ALL Item's with an * must be entered."
SET SAFETY ON
**************Dialogue TIN**************
SET SAFETY OFF
ASSIGN FLAG = "T"
Pause "Requesting data to compare Employee Social Security Numbers with Vendor Tax Identification Numbers"
DIALOG (DIALOG TITLE "User Dialog" WIDTH 495 HEIGHT 351 ) (BUTTONSET TITLE "&OK;&Cancel" AT 360 24 DEFAULT 1 ) (TEXT TITLE "Select Table with Employee SSN" AT 36 160 ) (ITEM TITLE "f" TO "_v_EmployeeSSN" AT 84 192 WIDTH 205 HEIGHT 130 DEFAULT "Employee_Data" ) (TEXT TITLE "Select Table with Vendor TIN" AT 36 88 ) (ITEM TITLE "f" TO "_v_VendorTIN" AT 84 120 WIDTH 205 HEIGHT 130 DEFAULT "Vendor_TINS" ) (TEXT TITLE "cc Porter" AT 408 232 ) (TEXT TITLE "Please enter the tables that contain the TIN and SSN information." AT 36 28 WIDTH 297 HEIGHT 43 ) (TEXT TITLE "NOTE: While this test was designed to compare Vendor TINS with Employee SSN's, it can be used to compare SSN' from two populations or TIN's from two populations just as easily." AT 24 268 WIDTH 462 HEIGHT 52 )
open %_v_EmployeeSSN%
DIALOG (DIALOG TITLE "User Dialog" WIDTH 475 HEIGHT 542 ) (BUTTONSET TITLE "&OK;&Cancel" AT 370 12 DEFAULT 1 ) (TEXT TITLE "Select Field with Employee Social Security Number" AT 24 28 WIDTH 281 HEIGHT 42 ) (ITEM TITLE "C" TO "_Emp_SSN_field" AT 96 84 WIDTH 202 HEIGHT 130 DEFAULT "%_Emp_SSN_field%" ) (TEXT TITLE "Select Field with Employee First Name" AT 24 148 ) (ITEM TITLE "C" TO "_V_Emp_First_Name" AT 96 180 WIDTH 241 DEFAULT "%_V_Emp_First_Name%" ) (TEXT TITLE "Select Field with Employee Last name" AT 24 232 ) (ITEM TITLE "C" TO "_V_Emp_Last_name" AT 96 264 WIDTH 243 DEFAULT "%_V_Emp_Last_name%" ) (TEXT TITLE "Select 3 Fields to be Extracted " AT 24 316 ) (ITEM TITLE "CND" TO "_V_Extract_Field_1" AT 96 384 WIDTH 241 DEFAULT "%_V_Extract_Field_1%" ) (ITEM TITLE "CND" TO "_V_Extract_Field_2" AT 96 432 WIDTH 245 DEFAULT "%_V_Extract_Field_2%" ) (ITEM TITLE "CND" TO "_V_Extract_field_3" AT 96 480 WIDTH 235 DEFAULT "%_V_Extract_field_3%" ) (TEXT TITLE "NOTE: Make sure to select 3 fields." AT 96 352 )
DIALOG (DIALOG TITLE "User Dialog" WIDTH 466 HEIGHT 246 ) (BUTTONSET TITLE "&OK;&Cancel" AT 370 12 DEFAULT 1 ) (TEXT TITLE "Is the Table Highest Group 1 loaded and do you want to run the SSN validation?" AT 24 40 WIDTH 197 HEIGHT 63 ) (RADIOBUTTON TITLE "Yes;NO" TO "RADIO1" AT 96 132 DEFAULT 2 )
open %_v_VendorTIN%
DIALOG (DIALOG TITLE "User Dialog" WIDTH 490 HEIGHT 530 ) (BUTTONSET TITLE "&OK;&Cancel" AT 370 12 DEFAULT 1 ) (TEXT TITLE "Select Field with the Vendor TIN" AT 24 28 WIDTH 281 HEIGHT 42 ) (ITEM TITLE "C" TO "_Ven_TIN_field" AT 96 84 WIDTH 202 HEIGHT 130 DEFAULT "%_Ven_TIN_field%") (TEXT TITLE "Select Field with Vendor Name" AT 24 136 ) (TEXT TITLE "Select Field with Vendor Number/ID" AT 24 220 ) (TEXT TITLE "Select 3 Fields to Extract" AT 24 304 ) (ITEM TITLE "C" TO "_V_Vendor_Name" AT 96 168 DEFAULT "%_V_Vendor_Name%") (ITEM TITLE "CN" TO "_V_Vendor_Num" AT 96 252 DEFAULT "%_V_Vendor_Num%") (ITEM TITLE "CND" TO "_V_Ven_Extract_1" AT 96 336 DEFAULT "%_V_Ven_Extract_1%") (ITEM TITLE "CND" TO "_V_Ven_Extract_2" AT 96 384 DEFAULT "%_V_Ven_Extract_2%") (ITEM TITLE "CND" TO "_V_Ven_Extract_3" AT 96 432 DEFAULT "%_V_Ven_Extract_3%" ) (TEXT TITLE "NOTE: Make sure to select 3 fields." AT 96 484 )
IF "%_Emp_SSN_field%" = "" ASSIGN FLAG = "F"
IF "%_V_Emp_First_Name%" = "" ASSIGN FLAG = "F"
IF "%_V_Emp_Last_name%" = "" ASSIGN FLAG = "F"
IF "%_V_Extract_Field_1%" = "" ASSIGN FLAG = "F"
IF "%_V_Extract_Field_2%" = "" ASSIGN FLAG = "F"
IF "%_V_Extract_field_3%" = "" ASSIGN FLAG = "F"
IF "%_Ven_TIN_field%"= "" ASSIGN FLAG = "F"
IF "%_V_Vendor_Name%"= "" ASSIGN FLAG = "F"
IF "%V_Vendor_Num%"= "" ASSIGN FLAG = "F"
IF "%_V_Ven_Extract_1%"= "" ASSIGN FLAG = "F"
IF "%_V_Ven_Extract_2%"= "" ASSIGN FLAG = "F"
IF "%_V_Ven_Extract_3%" = "" ASSIGN FLAG = "F"
*************SSN Validator2
SET SAFETY OFF
OPEN %_v_employeeSSN%
DELETE Group_Modifier OK
DELETE GROUP OK
DELETE AREA OK
DELETE SERIAL OK
DELETE Valid OK
DELETE Manual_validate OK
DEFINE FIELD AREA COMPUTED Substring(ALLTRIM(SPLIT(%_emp_ssn_field%_new,"-",1)),1,3)
DEFINE FIELD GROUP COMPUTED SPLIT(%_emp_ssn_field%_new,"-",2)
DEFINE FIELD SERIAL COMPUTED SPLIT(%_emp_ssn_field%_new,"-",3)
COMMENT Because the group numbers are not issued numerically, I added prefixes to the group value to make them numeric.
DEFINE FIELD Group_Modifier COMPUTED
VALUE(Group,0) IF MATCH( Group, "01","03","05","07","09" )
Value("10"+Group,0) IF MATCH( Group, "02","04","06","08", )
Value("1"+Group,0) IF MATCH( Substring(Group,2,1), "2","4","6","8","0" )
Value("100"+Group,0) IF MATCH( Substring(Group,2,1), "1","3","5","7","9" )
0
OPEN Highest_Group_1
DEFINE REPORT Default_View
INDEX ON Area TO "Area1" OPEN
SET INDEX TO "Area1"
OPEN %_v_employeeSSN%
DEFINE RELATION AREA WITH Highest_Group_1 INDEX Area1
DEFINE FIELD Valid COMPUTED
F IF MATCH( AREA, "666","000" ) OR BETWEEN( AREA, "800", "999")
F IF GROUP = "00" OR SERIAL = "0000"
F IF Highest_Group_1.Group_Modifier < Group_Modifier
T
DEFINE FIELD Manual_Validate COMPUTED
"Alpha Characters" IF LENGTH( EXCLUDE( UPPER( %_emp_ssn_field%),"1234567890./-_")) <>0
"8 or less" IF LENGTH( INCLUDE( %_emp_ssn_field%, "1234567890"))<9
"no"
EXTRACT FIELDS %_emp_ssn_field% %_emp_ssn_field%_new Valid Manual_Validate TO "Invalid SSN Inquiry" OPEN
SET SAFETY ON
*****************Validate Address
SET SAFETY OFF
COMMENT E
OPEN %_v_Ven_data%
DEFINE FIELD %_v_ven_name%_std COMPUTED SUBSTRING(ALLTRIM(UPPER(%_v_ven_name%)),1,50)
DEFINE FIELD %_v_ven_address_1%_std COMPUTED SUBSTRING(ALLTRIM(INCLUDE(UPPER(%_v_ven_address_1%+%_v_ven_state%+%_v_ven_zip%1),"0123456789ASDFGHJKLMNBVCXZQWERTYUIOP")),1,50)
IF _VV2="T" DEFINE FIELD %_v_ven_address_2%_std COMPUTED SUBSTRING(ALLTRIM(INCLUDE(UPPER("%_v_ven_address_2%+%_v_ven_state%+%_v_ven_zip%1"),"0123456789ASDFGHJKLMNBVCXZQWERTYUIOP")),1,50)
IF _VV3="T" DEFINE FIELD %_v_ven_address_3%_std COMPUTED SUBSTRING(ALLTRIM(INCLUDE(UPPER("%_v_ven_address_3%+%_v_ven_state%+%_v_ven_zip%1"),"0123456789ASDFGHJKLMNBVCXZQWERTYUIOP")),1,50)
IF _VV4="T" DEFINE FIELD %_v_ven_address_4%_std COMPUTED SUBSTRING(ALLTRIM(INCLUDE(UPPER("%_v_ven_address_4%+%_v_ven_state%+%_v_ven_zip%1"),"0123456789ASDFGHJKLMNBVCXZQWERTYUIOP")),1,50)
IF _VV2="T" DEFINE FIELD v_ven_address_5_std COMPUTED SUBSTRING(ALLTRIM(INCLUDE(UPPER("%_v_ven_address_1%+%_v_ven_address_2%+%_v_ven_state%+%v_ven_zip%1"),"0123456789ASDFGHJKLMNBVCXZQWERTYUIOP")),1,50)
IF _VV3="T" AND _VV2="T" DEFINE FIELD v_ven_address_6_std COMPUTED SUBSTRING(ALLTRIM(INCLUDE(UPPER("%_v_ven_address_1%+%_v_ven_address_2%+%_v_ven_address_3%+%_v_ven_state%+%_v_ven_zip%1"),"0123456789ASDFGHJKLMNBVCXZQWERTYUIOP")),1,50)
IF _VV3="T" AND _VV2="T"DEFINE FIELD v_ven_address_7_std COMPUTED SUBSTRING(ALLTRIM(INCLUDE(UPPER("%_v_ven_address_1%+%_v_ven_address_2%+%_v_ven_address_3%+%_v_ven_address_4%+%_v_ven_state%+%_v_ven_zip%1"),"0123456789ASDFGHJKLMNBVCXZQWERTYUIOP")),1,50)
GROUP
EXTRACT %_v_ven_name%_std %_v_ven_id% %_v_ven_address_1%_std %_v_ven_other_1% %_v_ven_other_2% TO "%_v_ven_Data%_add"
IF _VV2="T" EXTRACT %_v_ven_name%_std %_v_ven_id% %_v_ven_address_2%_std %_v_ven_other_1% %_v_ven_other_2% TO "%_v_ven_Data%_add"
IF _VV3="T" EXTRACT %_v_ven_name%_std %_v_ven_id% %_v_ven_address_3%_std %_v_ven_other_1% %_v_ven_other_2% TO "%_v_ven_Data%_add"
IF _VV4="T" EXTRACT %_v_ven_name%_std %_v_ven_id% %_v_ven_address_4%_std %_v_ven_other_1% %_v_ven_other_2% TO "%_v_ven_Data%_add"
IF _VV2="T" EXTRACT %_v_ven_name%_std %_v_ven_id% v_ven_address_5_std %_v_ven_other_1% %_v_ven_other_2% TO "%_v_ven_Data%_add"
IF _VV3="T" AND _VV2="T" EXTRACT %_v_ven_name%_std %_v_ven_id% v_ven_address_6_std %_v_ven_other_1% %_v_ven_other_2% TO "%_v_ven_Data%_add"
IF _VV4= "T" AND _VV3="T" AND _VV2="T" EXTRACT %_v_ven_name%_std %_v_ven_id% v_ven_address_7_std %_v_ven_other_1% %_v_ven_other_2% TO "%_v_ven_Data%_add"
END
COMMENT F
OPEN %_v_Emp_data%
DEFINE FIELD %_v_emp_fname%_std COMPUTED SUBSTRING(ALLTRIM(UPPER(%_v_emp_fname%)),1,50)
DEFINE FIELD %_v_emp_lname%_std COMPUTED SUBSTRING(ALLTRIM(UPPER(%_v_emp_lname%)),1,50)
DEFINE FIELD %_v_emp_address_1%_std COMPUTED SUBSTRING(ALLTRIM(INCLUDE(UPPER(%_v_emp_address_1%+%_v_emp_state%+%_v_emp_zip%1),"0123456789ASDFGHJKLMNBVCXZQWERTYUIOP")),1,50)
IF _VE2="T" DEFINE FIELD %_v_emp_address_2%_std COMPUTED SUBSTRING(ALLTRIM(INCLUDE(UPPER("%_v_emp_address_2%+%_v_emp_state%+%_v_emp_zip%1"),"0123456789ASDFGHJKLMNBVCXZQWERTYUIOP")),1,50)
IF _VE3="T" DEFINE FIELD %_v_emp_address_3%_std COMPUTED SUBSTRING(ALLTRIM(INCLUDE(UPPER("%_v_emp_address_3%+%_v_emp_state%+%_v_emp_zip%1"),"0123456789ASDFGHJKLMNBVCXZQWERTYUIOP")),1,50)
IF _VE4="T" DEFINE FIELD %_v_emp_address_4%_std COMPUTED SUBSTRING(ALLTRIM(INCLUDE(UPPER("%_v_emp_address_4%+%_v_emp_state%+%_v_emp_zip%1"),"0123456789ASDFGHJKLMNBVCXZQWERTYUIOP")),1,50)
IF _VE2="T" DEFINE FIELD v_emp_address_5_std COMPUTED SUBSTRING(ALLTRIM(INCLUDE(UPPER("%_v_emp_address_1%+%_v_emp_address_2%+%_v_emp_state%+%_v_emp_zip%1"),"0123456789ASDFGHJKLMNBVCXZQWERTYUIOP")),1,50)
IF _VE3="T" AND _VE2="T" DEFINE FIELD v_emp_address_6_std COMPUTED SUBSTRING(ALLTRIM(INCLUDE(UPPER("%_v_emp_address_1%+%_v_emp_address_2%+%_v_emp_address_3%+%_v_emp_state%+%_v_emp_zip%1"),"0123456789ASDFGHJKLMNBVCXZQWERTYUIOP")),1,50)
IF _VE4="T" AND _VE3="T" AND _VE2="T" DEFINE FIELD v_emp_address_7_std COMPUTED SUBSTRING(ALLTRIM(INCLUDE(UPPER("%_v_emp_address_1%+%_v_emp_address_2%+%_v_emp_address_3%+%_v_emp_address_4%+%_v_emp_state%+%_v_emp_zip%1"),"0123456789ASDFGHJKLMNBVCXZQWERTYUIOP")),1,50)
GROUP
EXTRACT %_v_emp_fname%_std %_v_emp_lname%_std %_v_emp_id% %_v_emp_address_1%_std %_v_emp_other_1% %_v_emp_other_2% TO "%_v_emp_Data%_add"
IF _VE2="T" EXTRACT %_v_emp_fname%_std %_v_emp_lname% %_v_emp_id% %_v_emp_address_2%_std %_v_emp_other_1% %_v_emp_other_2% TO "%_v_emp_Data%_add"
IF _VE3="T" EXTRACT %_v_emp_fname%_std %_v_emp_lname% %_v_emp_id% %_v_emp_address_3%_std %_v_emp_other_1% %_v_emp_other_2% TO "%_v_emp_Data%_add"
IF _VE4="T" EXTRACT %_v_emp_fname%_std %_v_emp_lname% %_v_emp_id% %_v_emp_address_4%_std %_v_emp_other_1% %_v_emp_other_2% TO "%_v_emp_Data%_add"
IF _VE2="T" EXTRACT %_v_emp_fname%_std %_v_emp_lname% %_v_emp_id% v_emp_address_5_std %_v_emp_other_1% %_v_emp_other_2% TO "%_v_emp_Data%_add"
IF _VE2="T" AND _VE3= "T" EXTRACT %_v_emp_fname%_std %_v_emp_lname% %_v_emp_id% v_emp_address_6_std %_v_emp_other_1% %_v_emp_other_2% TO "%_v_emp_Data%_add"
IF _VE2="T" AND _VE3="T" AND _VE4="T" EXTRACT %_v_emp_fname%_std %_v_emp_lname% %_v_emp_id% v_emp_address_7_std %_v_emp_other_1% %_v_emp_other_2% TO "%_v_emp_Data%_add"
END
OPEN %_v_Ven_data%_add
SUMMARIZE ON %_v_ven_name%_std %_v_ven_address_1%_std OTHER %_v_ven_id% %_v_ven_other_1% %_v_ven_other_2% TO "%_v_ven_Data%_sum"
OPEN %_v_Emp_data%_add
SUMMARIZE ON %_v_emp_fname%_std %_v_emp_lname%_std %_v_emp_address_1%_std OTHER %_v_emp_id% %_v_emp_other_1% %_v_emp_other_2% TO "%_v_emp_Data%_sum"
OPEN %_v_Emp_data%_sum
INDEX ON %_v_emp_address_1%_std TO "address" OPEN
SET INDEX TO "address"
OPEN %_v_Ven_data%_sum
DEFINE RELATION %_v_ven_address_1%_std WITH %_v_Emp_data%_sum INDEX address
DEFINE FIELD name_match COMPUTED
"Matched" IF %_v_Emp_data%_sum.%_v_emp_fname%_std = SPLIT(%_v_ven_name%_std, " ", 1) AND %_v_Emp_data%_sum.%_v_emp_lname%_std = SPLIT(%_v_ven_name%_std, " ", 5)
"Matched" IF %_v_Emp_data%_sum.%_v_emp_fname%_std = SPLIT(%_v_ven_name%_std, " ", 1) AND %_v_Emp_data%_sum.%_v_emp_lname%_std = SPLIT(%_v_ven_name%_std, " ", 4)
"Matched" IF %_v_Emp_data%_sum.%_v_emp_fname%_std = SPLIT(%_v_ven_name%_std, " ", 1) AND %_v_Emp_data%_sum.%_v_emp_lname%_std = SPLIT(%_v_ven_name%_std, " ", 3)
"Matched" IF %_v_Emp_data%_sum.%_v_emp_fname%_std = SPLIT(%_v_ven_name%_std, " ", 1) AND %_v_Emp_data%_sum.%_v_emp_lname%_std = SPLIT(%_v_ven_name%_std, " ", 2)
"Part Matched" IF SUBSTRING(%_v_Emp_data%_sum.%_v_emp_fname%_std,1,5) = SUBSTRING(SPLIT(%_v_ven_name%_std, " ", 1),1,4) or SUBSTRING(%_v_Emp_data%_sum.%_v_emp_lname%_std,1,7) = SUBSTRING(SPLIT(%_v_ven_name%_std, " ", 5),1,7)
"Part Matched" IF SUBSTRING(%_v_Emp_data%_sum.%_v_emp_fname%_std,1,5) = SUBSTRING(SPLIT(%_v_ven_name%_std, " ", 1),1,4) or SUBSTRING(%_v_Emp_data%_sum.%_v_emp_lname%_std,1,7) = SUBSTRING(SPLIT(%_v_ven_name%_std, " ", 4),1,7)
"Part Matched" IF SUBSTRING(%_v_Emp_data%_sum.%_v_emp_fname%_std,1,5) = SUBSTRING(SPLIT(%_v_ven_name%_std, " ", 1),1,4) OR SUBSTRING(%_v_Emp_data%_sum.%_v_emp_lname%_std,1,7) = SUBSTRING(SPLIT(%_v_ven_name%_std, " ", 3),1,7)
"Part Matched" IF SUBSTRING(%_v_Emp_data%_sum.%_v_emp_fname%_std,1,5) = SUBSTRING(SPLIT(%_v_ven_name%_std, " ", 1),1,4) OR SUBSTRING(%_v_Emp_data%_sum.%_v_emp_lname%_std,1,7) = SUBSTRING(SPLIT(%_v_ven_name%_std, " ", 2),1,7)
"No Match"
EXTRACT FIELDS name_match %_v_Emp_data%_sum.%_v_emp_fname%_std %_v_Emp_data%_sum.%_v_emp_lname%_std %_v_Emp_data%_sum.%_v_emp_address_1%_std %_v_Emp_data%_sum.%_v_emp_id% %_v_Emp_data%_sum.%_v_emp_other_1% %_v_Emp_data%_sum.%_v_emp_other_2% %_v_ven_name%_std %_v_ven_address_1%_std %_v_ven_id% %_v_ven_other_1% %_v_ven_other_2% IF %_v_ven_address_1%_std = %_v_Emp_data%_sum.%_v_emp_address_1%_std AND LENGTH(ALLTRIM(%_v_ven_address_1%_std)) > 5 TO "Addresses exact match" OPEN
OPEN %_v_Ven_data%_sum
EXTRACT FIELDS name_match %_v_Emp_data%_sum.%_v_emp_fname%_std %_v_Emp_data%_sum.%_v_emp_lname%_std %_v_Emp_data%_sum.%_v_emp_address_1%_std %_v_Emp_data%_sum.%_v_emp_id% %_v_Emp_data%_sum.%_v_emp_other_1% %_v_Emp_data%_sum.%_v_emp_other_2% %_v_ven_name%_std %_v_ven_address_1%_std %_v_ven_id% %_v_ven_other_1% %_v_ven_other_2% IF %_v_ven_address_1%_std <> %_v_Emp_data%_sum.%_v_emp_address_1%_std AND SUBSTRING(%_v_ven_address_1%_std,1,8) = SUBSTRING(%_v_Emp_data%_sum.%_v_emp_address_1%_std,1,8) AND LENGTH(ALLTRIM(%_v_ven_address_1%_std)) > 5 TO "Addresses Partial Match" OPEN
DELETE %_v_Emp_data%_add.fil OK
DELETE %_v_Emp_data%_sum.fil OK
DELETE %_v_Ven_data%_add.fil OK
DELETE %_v_Ven_data%_sum.fil OK
DELETE FORMAT %_v_Emp_data%_add OK
DELETE FORMAT %_v_Emp_data%_sum OK
DELETE FORMAT %_v_Ven_data%_add OK
DELETE FORMAT %_v_Ven_data%_sum OK
SET SAFETY ON
*********************Validate TIN
SET SAFETY OFF
open %_v_EmployeeSSN%
DELETE %_EMP_SSN_field%_new OK
DELETE %_EMP_SSN_Field%temp OK
DEFINE FIELD %_EMP_SSN_Field%temp COMPUTED INCLUDE(%_EMP_SSN_FIELD%,"1234567890")
DEFINE FIELD %_EMP_SSN_field%_new COMPUTED
SUBSTR( ALLTRIM(%_EMP_SSN_Field%temp), 1, 3) + "-" + SUBSTR( ALLTRIM(%_EMP_SSN_Field%temp), 4, 2) + "-" + SUBSTR( ALLTRIM(%_EMP_SSN_Field%temp), 6, 4) IF LENGTH(ALLTRIM(%_EMP_SSN_Field%temp))=9
SUBSTR( "0"+ALLTRIM(%_EMP_SSN_Field%temp), 1, 3) + "-" + SUBSTR( "0"+ALLTRIM(%_EMP_SSN_Field%temp), 4, 2) + "-" + SUBSTR("0"+ ALLTRIM(%_EMP_SSN_Field%temp), 6, 4) IF LENGTH(ALLTRIM( %_EMP_SSN_Field%temp)) = 8
SUBSTR( "00"+ALLTRIM(%_EMP_SSN_Field%temp), 1, 3) + "-" + SUBSTR( "00"+ALLTRIM(%_EMP_SSN_Field%temp), 4, 2) + "-" + SUBSTR("00"+ ALLTRIM(%_EMP_SSN_Field%temp), 6, 4) IF LENGTH(ALLTRIM( %_EMP_SSN_Field%temp)) = 7
SUBSTR( "000"+ALLTRIM(%_EMP_SSN_Field%temp), 1, 3) + "-" + SUBSTR( "000"+ALLTRIM(%_EMP_SSN_Field%temp), 4, 2) + "-" + SUBSTR("000"+ ALLTRIM(%_EMP_SSN_Field%temp), 6, 4) IF LENGTH(ALLTRIM( %_EMP_SSN_Field%temp)) = 6
" "
open %_v_VendorTIN%
DELETE %_Ven_TIN_field%_new OK
DELETE %_Ven_TIN_field%temp OK
DELETE MATCH OK
DELETE EMPLOYEE_MATCH OK
DEFINE FIELD %_Ven_TIN_field%temp COMPUTED INCLUDE(%_Ven_TIN_field%,"1234567890")
COMMENT For comparison purposes, the TIN will be displayed in SSN format rather than typical PIN format.
DEFINE FIELD %_Ven_TIN_field%_new COMPUTED
SUBSTR( ALLTRIM(%_Ven_TIN_field%temp), 1, 3) + "-" + SUBSTR( ALLTRIM(%_Ven_TIN_field%temp), 4, 2) + "-" + SUBSTR( ALLTRIM(%_Ven_TIN_field%temp), 6, 4) IF LENGTH(ALLTRIM(%_Ven_TIN_field%temp))=9
SUBSTR( "0"+ALLTRIM(%_Ven_TIN_field%temp), 1, 3) + "-" + SUBSTR( "0"+ALLTRIM(%_Ven_TIN_field%temp), 4, 2) + "-" + SUBSTR("0"+ ALLTRIM(%_Ven_TIN_field%temp), 6, 4) IF LENGTH(ALLTRIM( %_Ven_TIN_field%temp)) = 8
SUBSTR( "00"+ALLTRIM(%_Ven_TIN_field%temp), 1, 3) + "-" + SUBSTR( "00"+ALLTRIM(%_Ven_TIN_field%temp), 4, 2) + "-" + SUBSTR("00"+ ALLTRIM(%_Ven_TIN_field%temp), 6, 4) IF LENGTH(ALLTRIM( %_Ven_TIN_field%temp)) = 7
SUBSTR( "000"+ALLTRIM(%_Ven_TIN_field%temp), 1, 3) + "-" + SUBSTR( "000"+ALLTRIM(%_Ven_TIN_field%temp), 4, 2) + "-" + SUBSTR("000"+ ALLTRIM(%_Ven_TIN_field%temp), 6, 4) IF LENGTH(ALLTRIM( %_Ven_TIN_field%temp)) = 6
" "
IF RADIO1 = 1 DO SSN_Validator
SET SAFETY OFF
Open %_v_EmployeeSSN%
DEFINE REPORT Default_View
DELETE INDEX "Emp_SSN" OK
INDEX ON %_EMP_SSN_Field%_new TO "Emp_SSN" OPEN
SET INDEX TO "Emp_SSN"
open %_v_VendorTIN%
DEFINE RELATION %_Ven_TIN_field%_new WITH %_v_EmployeeSSN% INDEX Emp_SSN
DEFINE FIELD MATCH COMPUTED %_v_EmployeeSSN%.%_EMP_SSN_field%_new = %_Ven_TIN_Field%_new AND %_Ven_TIN_Field%_new <> " "
SET FILTER MATCH = T
COMMENT Field EMPLOYEE_MATCH will identify vendor names which appear to be the same as the employee names. This is often the case if an employee is listed as a vendor for various business reimbursements (EG Travel.) PARTIAL indicates that there is a partial match (First or last name)
DEFINE FIELD Employee_Match COMPUTED
"EMPLOYEE" IF UPPER(%_v_EmployeeSSN%.%_V_Emp_First_name%) = UPPER(SPLIT(%_V_Vendor_Name%," ",1)) AND UPPER(%_v_EmployeeSSN%.%_V_Emp_Last_Name%)=UPPER(SPLIT(%_V_Vendor_Name%," ",5))
"EMPLOYEE" IF UPPER(%_v_EmployeeSSN%.%_V_Emp_First_name%) = UPPER(SPLIT(%_V_Vendor_Name%," ",1)) AND UPPER(%_v_EmployeeSSN%.%_V_Emp_Last_Name%)=UPPER(SPLIT(%_V_Vendor_Name%," ",4))
"EMPLOYEE" IF UPPER(%_v_EmployeeSSN%.%_V_Emp_First_name%) = UPPER(SPLIT(%_V_Vendor_Name%," ",1)) AND UPPER(%_v_EmployeeSSN%.%_V_Emp_Last_Name%)=UPPER(SPLIT(%_V_Vendor_Name%," ",3))
"EMPLOYEE" IF UPPER(%_v_EmployeeSSN%.%_V_Emp_First_name%) = UPPER(SPLIT(%_V_Vendor_Name%," ",1)) AND UPPER(%_v_EmployeeSSN%.%_V_Emp_Last_Name%)=UPPER(SPLIT(%_V_Vendor_Name%," ",2))
"EMPLOYEE" IF UPPER(%_v_EmployeeSSN%.%_V_Emp_First_name%) = UPPER(SPLIT(%_V_Vendor_Name%," ",1)) AND UPPER(%_v_EmployeeSSN%.%_V_Emp_Last_Name%)=UPPER(SPLIT(%_V_Vendor_Name%," ",1))
"PARTIAL" IF UPPER(%_v_EmployeeSSN%.%_V_Emp_First_name%) = UPPER(SPLIT(%_V_Vendor_Name%," ",1)) OR UPPER(%_v_EmployeeSSN%.%_V_Emp_Last_Name%)=UPPER(SPLIT(%_V_Vendor_Name%," ",5))
"PARTIAL" IF UPPER(%_v_EmployeeSSN%.%_V_Emp_Last_Name%)=UPPER(SPLIT(%_V_Vendor_Name%," ",4))
"PARTIAL" IF UPPER(%_v_EmployeeSSN%.%_V_Emp_Last_Name%)=UPPER(SPLIT(%_V_Vendor_Name%," ",3))
"PARTIAL" IF UPPER(%_v_EmployeeSSN%.%_V_Emp_Last_Name%)=UPPER(SPLIT(%_V_Vendor_Name%," ",2))
"PARTIAL" IF UPPER(%_v_EmployeeSSN%.%_V_Emp_Last_Name%)=UPPER(SPLIT(%_V_Vendor_Name%," ",1))
"VENDOR"
DELETE TIN_and_SSN_Match.fil OK
DELETE FORMAT TIN_and_SSN_Match OK
EXTRACT FIELDS %_Ven_TIN_field%_new %_V_Vendor_Name% %_V_Vendor_Num% %_V_Ven_Extract_1% %_V_Ven_Extract_2% %_V_Ven_Extract_3% %_v_EmployeeSSN%.%_EMP_SSN_field%_new %_v_EmployeeSSN%.%_V_Emp_First_name% %_v_EmployeeSSN%.%_V_Emp_Last_Name% %_v_EmployeeSSN%.%_V_Extract_field_1% %_v_EmployeeSSN%.%_V_Extract_field_2% %_v_EmployeeSSN%.%_V_Extract_field_3% Employee_Match IF MATCH TO "TIN and SSN Match" OPEN
OPEN TIN_and_SSN_Match
SET SAFETY ON