update fsr set fsr.fseCountryId = cntry.countryId from tbl_IMPORT_FoodServiceRewardsDistributors fsr inner join tbl_UT_Countries cntry on cntry.country2dCode = case when fsr.fsrBusCountry = 'USA' then 'US' when left( fsr.fsrBusCountry, 3 ) = 'CAN' then 'CA' else 'OT' end where fsr.eai_batchId = and fsr.ownerId = and fsr.ownerType = set nocount on update fsr set fsr.fseDistributorId = dst.dstid, fsr.fseDistributorStatus = 'U' from tbl_IMPORT_FoodServiceRewardsDistributors fsr inner join tbl_DST_FoodServiceRewards dstfsr on dstfsr.fsrDistributorshipId = fsr.fsrDistributorshipId inner join tbl_DST_Distributors dst on dst.dstid = dstfsr.dstid where fsr.eai_batchId = and fsr.ownerId = and fsr.ownerType = select rowsAffected = @@rowCount set nocount off select distinct fsr.ownerId, fsr.ownerType, fsr.fsrDistributorshipId, fsr.fsrBusName, fsr.fsrBusUnitName, fsr.fsrBusAddr1, fsr.fsrBusAddr2, fsr.fsrBusCity, fsr.fsrBusState, fsr.fsrBusPostalCode, fsr.fseCountryId from tbl_IMPORT_FoodServiceRewardsDistributors fsr where fsr.eai_batchId = and fsr.ownerId = and fsr.ownerType = and fsr.fsrStatus <> 'I' and fsr.fseDistributorId is null select dstId, dstName, dstCity, dstState, dstAddr1, dstAddr2, dstZip from tbl_DST_Distributors dst where dststate = and ( dstName like or dstName like or dstCity = or left( dstZip, 5 ) = ) queryAddColumn( qmd_distributorLookup, "stdAddress", "varchar", ArrayNew(1) ); queryAddColumn( qmd_distributorLookup, "stdAddress2", "varchar", ArrayNew(1) ); queryAddColumn( qmd_distributorLookup, "companyNameEditDistance", "integer", ArrayNew(1) ); queryAddColumn( qmd_distributorLookup, "addressEditDistance", "integer", ArrayNew(1) ); querySetCell( qmd_distributorLookup, "stdAddress", _fseStdStreet, qmd_distributorLookup.currentRow ); querySetCell( qmd_distributorLookup, "stdAddress2", _fseStdStreet2, qmd_distributorLookup.currentRow ); querySetCell( qmd_distributorLookup, "companyNameEditDistance", _companyNameDistance, qmd_distributorLookup.currentRow ); querySetCell( qmd_distributorLookup, "addressEditDistance", _addressDistance, qmd_distributorLookup.currentRow ); StructDelete( variables, "matcher" ); StructDelete( variables, "fseStreetParser" ); StructDelete( variables, "fseStreetParser2" ); StructDelete( variables, "fsrStreetParser" ); select * from qmd_distributorLookup where ( addressEditDistance = 0 and companyNameEditDistance <= 4 ) or ( addressEditDistance <= 1 and companyNameEditDistance <= 2 ) order by companyNameEditDistance, addressEditDistance update tbl_IMPORT_FoodServiceRewardsDistributors set fseDistributorId = , fseDistributorStatus = 'M' where eai_batchId = and ownerId = and ownerType = and fsrDistributorshipId = update tbl_IMPORT_FoodServiceRewardsDistributors set fseDistributorStatus = 'A' where eai_batchId = and ownerId = and ownerType = and fsrDistributorshipId = insert into tbl_DST_FoodServiceRewards ( dstid, fsrDistributorshipId, fsrMappedBy, fsrMappedOn, fsrBusName, fsrBusUnitName, fsrBusCity, fsrBusState, fsrBusPostalCode, fsrBusAddr1, fsrBusAddr2, fsrBusAddr3, fsrLoadedOn ) select distinct dst.dstid, fsr.fsrDistributorshipId, -1, { fn now() }, fsr.fsrBusName, fsr.fsrBusUnitName, fsr.fsrBusCity, fsr.fsrBusState, fsr.fsrBusPostalCode, fsr.fsrBusAddr1, fsr.fsrBusAddr2, fsr.fsrBusAddr3, { fn now() } from tbl_DST_Distributors dst inner join tbl_IMPORT_FoodServiceRewardsDistributors fsr on fsr.fseDistributorId = dst.dstId where fsr.eai_batchId = and fsr.ownerId = and fsr.ownerType = and fsr.fsrBusUnitName <> 'SUPPRESSED' and fsr.fseDistributorStatus = 'M' update dstfsr set dstfsr.fsrBusName = fsr.fsrBusName, dstfsr.fsrBusUnitName = fsr.fsrBusUnitName, dstfsr.fsrBusCity = fsr.fsrBusCity, dstfsr.fsrBusState = fsr.fsrBusState, dstfsr.fsrBusPostalCode = fsr.fsrBusPostalCode, dstfsr.fsrBusAddr1 = fsr.fsrBusAddr1, dstfsr.fsrBusAddr2 = fsr.fsrBusAddr2, dstfsr.fsrBusAddr3 = fsr.fsrBusAddr3, dstfsr.fsrLoadedOn = { fn now() } from tbl_DST_FoodServiceRewards dstfsr inner join tbl_DST_Distributors dst on dst.dstid = dstfsr.dstid inner join tbl_IMPORT_FoodServiceRewardsDistributors fsr on fsr.fseDistributorId = dstfsr.dstId and fsr.fsrDistributorshipId = dstfsr.fsrDistributorshipId where fsr.eai_batchId = and fsr.ownerId = and fsr.ownerType = and fsr.fseDistributorStatus = 'U' set nocount on update fsr set fsr.fseDistributorId = cdr.cdr_recordId from tbl_IMPORT_FoodServiceRewardsDistributors fsr inner join tbl_CDR_Distributors cdr on cdr.ownerId = fsr.ownerId and cdr.fsl_tablecode = fsr.ownerType and cdr.FoodServiceRewardsId = fsr.fsrDistributorshipId where fsr.eai_batchId = and fsr.ownerId = and fsr.ownerType = and fsr.fsrStatus <> 'I' and fsr.fseDistributorId is null and fsr.fseDistributorStatus = 'A' select rowsAffected = @@rowCount set nocount off update cdr set cdr.foodserviceRewardsId = dst.fsrDistributorshipId from tbl_CDR_Distributors cdr left outer join tbl_DST_Distributors dst on dst.dstId = cdr.fsl_dstid where cdr.ownerId = and cdr.fsl_tablecode = ---> set nocount on update fsr set fsr.cdrDistributorId = cdr.cdr_recordId from tbl_IMPORT_FoodServiceRewardsDistributors fsr inner join tbl_CDR_Distributors cdr on cdr.ownerId = fsr.ownerId and cdr.fsl_tablecode = fsr.ownerType and cdr.fsl_dstid = fsr.fseDistributorId inner join tbl_DST_Distributors dst on dst.dstId = cdr.fsl_dstid where fsr.eai_batchId = and fsr.ownerId = and fsr.ownerType = and fsr.fsrStatus <> 'I' select rowsAffected = @@rowCount set nocount off update fsr set fsr.fseContactId = m.fspro_userid, fsr.fseContactStatus = 'U' from tbl_IMPORT_FoodServiceRewardsDistributors fsr inner join tbl_FSPro_Members m on m.ownerId = fsr.ownerId and m.fsl_tablecode = fsr.ownerType and m.orgId = fsr.cdrDistributorId and m.orgType = 'CDRD' and m.foodServiceRewardsId = fsr.fsrDistributorId where fsr.eai_batchId = and fsr.ownerId = and fsr.ownerType = and fsr.cdrDistributorId is not null and fsr.fsrStatus <> 'I' insert into tbl_FSPro_Members ( ownerId, fsl_tablecode, foodservicerewardsid, orgType, orgId, salutation, firstName, lastName, email, emailYesNo, title, officeAddrSame, originalSource ) select ownerId, ownerType, fsrDistributorId, 'CDRD', cdrDistributorId, fsrPrefix, fsrFirstName, fsrLastName, fsrEmailAddress, case when fsrEmailDNM = 'Y' then 1 else 0 end, fsrJobTitle, 1, '#_recordSource#' from tbl_IMPORT_FoodServiceRewardsDistributors fsr where fsr.eai_batchId = and fsr.ownerId = and fsr.ownerType = and fsr.cdrDistributorId is not null and fseContactId is null and fsrStatus <> 'I' update fsr set fsr.fseContactId = m.fspro_userid, fsr.fseContactStatus = 'A' from tbl_IMPORT_FoodServiceRewardsDistributors fsr inner join tbl_FSPro_Members m on m.ownerId = fsr.ownerId and m.fsl_tablecode = fsr.ownerType and m.orgId = fsr.cdrDistributorId and m.orgType = 'CDRD' and m.foodServiceRewardsId = fsr.fsrDistributorId where fsr.eai_batchId = and fsr.ownerId = and fsr.ownerType = and fsr.cdrDistributorId is not null and fsr.fseContactId is null

FoodServiceRewards Distributor File Load Results

select distinct fsr.fsrDistributorshipId, fsr.fsrBusName, fsr.fsrBusUnitName, fsr.fsrBusAddr1, fsr.fsrBusAddr2, fsr.fsrBusCity, fsr.fsrBusState, fsr.fsrBusCity, fsr.fsrBusPostalCode, fsr.fseDistributorStatus, dst.dstName, dst.dstAddr1, dst.dstAddr2, dst.dstCity, dst.dstState, dst.dstZip, cdr.cdr_dstcode, cdr.cdr_dstname from tbl_IMPORT_FoodServiceRewardsDistributors fsr inner join tbl_DST_Distributors dst on dst.dstId = fsr.fseDistributorId inner join tbl_CDR_Distributors cdr on cdr.cdr_recordId = fsr.cdrDistributorId and cdr.ownerId = fsr.ownerId and cdr.fsl_tablecode = fsr.ownerType where fsr.ownerId = and fsr.ownerType = and fsr.eai_batchId = and fsr.cdrDistributorId is not null and fsr.fsrStatus <> 'I' order by dst.dstName select distinct fsrDistributorshipId, fsrBusName, fsrBusUnitName, fsrBusAddr1, fsrBusAddr2, fsrBusCity, fsrBusState, fsrBusCity, fsrBusPostalCode from tbl_IMPORT_FoodServiceRewardsDistributors where fseDistributorId is null and ownerId = and ownerType = and eai_batchId = and fsrStatus <> 'I' and fsrBusName <> 'SUPPRESSED' order by fsrBusName select distinct fsrDistributorshipId, fsrBusName, fsrBusUnitName, fsrBusAddr1, fsrBusAddr2, fsrBusCity, fsrBusState, fsrBusCity, fsrBusPostalCode from tbl_IMPORT_FoodServiceRewardsDistributors where fseDistributorId is not null and cdrDistributorId is null and ownerId = and ownerType = and eai_batchId = and fsrStatus <> 'I' and fsrBusName <> 'SUPPRESSED' order by fsrBusName select distinct fsrDistributorshipId, fsrBusName, fsrBusUnitName, fsrBusAddr1, fsrBusAddr2, fsrBusCity, fsrBusState, fsrBusCity, fsrBusPostalCode, case when fseDistributorId is null then 'FSL' else 'Client' end reason from tbl_IMPORT_FoodServiceRewardsDistributors where ( fseDistributorId is null or cdrDistributorId is null ) and ownerId = and ownerType = and eai_batchId = and fsrStatus <> 'I' and fsrBusName <> 'SUPPRESSED' order by fsrBusName
FSR Distributors Mapped#lsNumberFormat(qmd_loaded.recordCount)#0
FSR Distributors NOT in Client Directory#lsNumberFormat(qmd_notInClientDir.recordCount)#0
FSR Distributors NOT FOUND in Profile Database#lsNumberFormat(qmd_notInFSL.recordCount)#0

FoodServiceRewards Distributors Mapped

FSR Id Client Code Name Addr 1 Addr 2 City State Zip Status
#fsrDistributorshipId# #cdr_dstCode#&##160; #fsrBusName# #fsrBusAddr1# #fsrBusAddr2#&##160; #fsrBusCity# #fsrBusState# #fsrBusPostalCode# Matched AddedExisting
&##160; FSL #dstName# #dstAddr1# #dstAddr2#&##160; #dstCity# #dstState# #dstZip#
#qmd_loaded.recordCount# Distributors Loaded

FoodServiceRewards Distributors NOT in Client Directory

generateNotMappedTable( qmd_notInClientDir );

FoodServiceRewards Distributors NOT FOUND in Profile Database

generateNotMappedTable( qmd_notInFSL );
FSR Id Bus. Name Addr 1 Addr 2 City State Zip
#fsrDistributorshipId# #fsrBusName# #fsrBusAddr1# #fsrBusAddr2#&##160; #fsrBusCity# #fsrBusState# #fsrBusPostalCode#
#arguments.qmd_results.recordCount# Distributors NOT Loaded