UPDATE I SET I.fseInclude = 'Y', I.fseOperatorId = NULL, I.fseBsrId = NULL, I.fseAffiliationOperatorId = NULL, I.fseCdrId1 = NULL, I.fseCdrId2 = NULL, I.fseZipCode = NULL, I.fseTerritoryId = NULL, I.fseSegmentId = NULL, I.fseClassificationId = NULL FROM tbl_IMPORT_Operators I WHERE I.eai_batchId = AND I.ownerId = AND I.ownerType = AND I.eai_lineNumber >= AND I.eai_lineNumber < select o.eai_importId from tbl_IMPORT_Operators o inner join ( select company_id, company_name, company_address1, company_address2, company_city, company_state, company_zip, count(*) dc from ( select distinct ownerId, ownerType, fseOperatorId, fseTerritoryId, fseCdrId1, fseCdrId2, fseSegmentId, fseZipCode, fseClassificationId, company_id, company_segment, company_sub_segment, company_name, company_comment, territory_id, company_phone, company_fax, company_address1, company_address2, company_city, company_state, company_zip, company_last_modify_date, fseChoId from tbl_IMPORT_Operators i where i.eai_batchId = and i.fseInclude = 'Y' ) x group by company_id, company_name, company_address1, company_address2, company_city, company_state, company_zip having count(*) > 1 ) d on d.company_id = o.company_id and d.company_name = o.company_name and d.company_address1 = o.company_address1 and d.company_address2 = o.company_address2 and d.company_city = o.company_city and d.company_state = o.company_state and d.company_zip = o.company_zip where o.eai_batchId = update i set i.fseInclude = 'N' from tbl_IMPORT_Operators i where i.eai_batchId = and i.ownerId = and i.ownerType = and i.eai_importId in ( ) update i set contact_first_name = case when charindex( ' ', ltrim( contact_full_name )) > 1 then substring( ltrim( contact_full_name ), 1, charindex( ' ', ltrim( contact_full_name )) - 1 ) else null end, contact_last_name = case when charindex( ' ', ltrim( reverse( contact_full_name ))) > 1 then reverse( substring( ltrim( reverse( contact_full_name )), 1, charindex( ' ', ltrim( reverse( contact_full_name ))) - 1 )) else null end from tbl_IMPORT_OPerators i where i.eai_batchId = and i.ownerId = and i.ownerType = and ( i.contact_full_name is not null and i.contact_full_name <> '' ) and ( ( i.contact_first_name is null or i.contact_first_name = '' ) and ( i.contact_last_name is null or i.contact_last_name = '' )) and i.fseInclude = 'Y' and i.eai_lineNumber >= and i.eai_lineNumber < select distinct ownerId, ownerType, fseOperatorId, fseTerritoryId, fseCdrId1, fseCdrId2, fseSegmentId, fseZipCode, fseClassificationId, company_id, company_segment, company_sub_segment, company_name, company_comment, territory_id, company_phone, company_fax, company_address1, company_address2, company_city, company_state, company_zip, company_last_modify_date, fseChoId, fseBsrId from tbl_IMPORT_Operators i where i.eai_batchId = and i.ownerId = and i.ownerType = and i.fseInclude = 'Y' and i.eai_lineNumber >= and i.eai_lineNumber < order by i.company_name select opr.operatorId, opr.mfrCustNum, opr.companyName, opr.city, opr.state, opr.address, opr.address2, opr.zipCode, opr.territoryId, opr.distrib1, opr.distrib2, opr.oprsegment, opr.classificationId, opr.mfr_bsr_id from tbl_OPR_ClientOperators opr left outer join tbl_IMPORT_Operators i on i.fseOperatorId = opr.operatorId and i.ownerId = opr.ownerId and i.ownerType = opr.fsltablecode and i.eai_batchId = where opr.ownerId = and opr.fsltablecode = and opr.territoryId = and left( opr.zipCode, 3 ) = and opr.state = and opr.city = and opr.address = and opr.companyName = and i.fseOperatorId is null queryAddColumn( qmd_operatorLookup, "stdAddress", "varchar", ArrayNew(1) ); queryAddColumn( qmd_operatorLookup, "stdAddress2", "varchar", ArrayNew(1) ); queryAddColumn( qmd_operatorLookup, "companyNameEditDistance", "integer", ArrayNew(1) ); queryAddColumn( qmd_operatorLookup, "addressEditDistance", "integer", ArrayNew(1) ); queryAddColumn( qmd_operatorLookup, "addressEditDistance2", "integer", ArrayNew(1) ); querySetCell( qmd_operatorLookup, "stdAddress", _fseStdStreet, qmd_operatorLookup.currentRow ); querySetCell( qmd_operatorLookup, "companyNameEditDistance", _companyNameDistance, qmd_operatorLookup.currentRow ); querySetCell( qmd_operatorLookup, "addressEditDistance", _addressDistance, qmd_operatorLookup.currentRow ); querySetCell( qmd_operatorLookup, "addressEditDistance2", _addressDistance2, qmd_operatorLookup.currentRow ); StructDelete( variables, "matcher" ); StructDelete( variables, "fseStreetParser" ); StructDelete( variables, "srcStreetParser" ); select * from qmd_operatorLookup where (( addressEditDistance = 0 or addressEditDistance2 = 0 ) and companyNameEditDistance <= 4 ) or (( addressEditDistance <= 1 or addressEditDistance2 <= 1 ) and companyNameEditDistance <= 2 ) or ( companyNameEditDistance = 0 ) order by companyNameEditDistance, addressEditDistance Potential match count = #qmd_operatorLookup.recordCount#

			#qmd_match.companyNameEditDistance#		#qmd_match.addressEditDistance#	#qmd_match.addressEditDistance2#
File		#company_id#	#company_name#		#company_address1#	#company_address2#	#company_city#	#company_state#	#company_zip#
CRM	#_operatorId#	#qmd_match.mfrCustNum#	#qmd_match.companyName#		#qmd_match.address#	#qmd_match.Address2#	#qmd_match.City#	#qmd_match.State#	#qmd_match.ZipCode#

update tbl_IMPORT_Operators set fseOperatorId = , fseOperatorStatus = 'M' where eai_batchId = and ownerId = and ownerType = and fseChoId = and company_id = and company_name = and company_name = and company_address1 = and company_city = and company_state = and fseZipCode = and territory_Id = and company_last_modify_date = and ( company_last_modify_date is null or company_last_modify_date = '' ) and company_address2 = and ( company_address2 is null or company_address2 = '' ) and company_segment = and ( company_segment is null or company_segment = '' ) and company_sub_segment = and ( company_sub_segment is null or company_sub_segment = '' ) and company_phone = and ( company_phone is null or company_phone = '' ) and company_fax = and ( company_fax is null or company_fax = '' ) and company_comment = and ( company_comment is null or company_comment = '' ) and fseTerritoryId = and fseTerritoryId is null and fseSegmentId = and fseSegmentId is null and fseCdrId1 = and fseCdrId2 is null and fseCdrId2 = and fseCdrId2 is null update tbl_OPR_CLientOperators set lastUpdated = { fn now() } , address = , address2 = , city = , state = , zipCode = , mfrCustNum = , distrib1 = , distrib2 = , oprSegment = , crmActive = 'Y' , territoryId = , setMethod = 1 , mfr_bsr_id = , setMethod = 1 , crmActive = 'Y' , classificationId = , fsl_choId = where operatorId = and ownerId = and fsltablecode = set nocount on insert into tbl_OPR_ClientOperators( ownerId, fsltablecode, mfrCustNum, companyName, address, address2, city, state, zipCode, phone, faxnumber, comments, setMethod, territoryId, crmActive, oprsegment, distrib1, distrib2, recordSource, dateCreated, classificationId ,fsl_choId ,mfr_bsr_id ) values ( , , , , , , , , , , , , , , 'Y', , null, 'N', , , , , { fn now() }, , , ) update tbl_IMPORT_Operators set fseOperatorStatus = 'A', fseOperatorId = @@identity where eai_batchId = and ownerId = and ownerType = and fseChoId = and company_id = and company_name = and company_name = and company_address1 = and company_city = and company_state = and fseZipCode = and territory_Id = and company_last_modify_date = and ( company_last_modify_date is null or company_last_modify_date = '' ) and company_address2 = and ( company_address2 is null or company_address2 = '' ) and company_segment = and ( company_segment is null or company_segment = '' ) and company_sub_segment = and ( company_sub_segment is null or company_sub_segment = '' ) and company_phone = and ( company_phone is null or company_phone = '' ) and company_fax = and ( company_fax is null or company_fax = '' ) and company_comment = and ( company_comment is null or company_comment = '' ) and fseTerritoryId = and fseTerritoryId is null and fseSegmentId = and fseSegmentId is null and fseCdrId1 = and fseCdrId2 is null and fseCdrId2 = and fseCdrId2 is null select operatorId = @@identity set nocount off Update: #fseOperatorId#
update tbl_OPR_CLientOperators set lastUpdated = { fn now() } , address = , address2 = , city = , state = , zipCode = , phone = , faxNumber = , mfrCustNum = , distrib1 = , distrib2 = , oprSegment = , classificationId = , fsl_choId = , territoryId = , crmActive = 'Y' , mfr_bsr_id = where operatorId = and ownerId = and fsltablecode = update opr set opr.companyName = cho.cho_name, opr.dbaName = cho.cho_dbaname, opr.address = cho.cho_address, opr.address2 = cho.cho_address2, opr.city = cho.cho_city, opr.state = cho.cho_state, opr.zipcode = cho.cho_zipcode, opr.countryId = cho.cho_countryId, opr.areacode = cho.cho_phoneAc, opr.phone = cho.cho_phone, opr.faxAreaCode = cho.cho_faxAc, opr.faxNumber = cho.cho_fax, opr.url = cho.cho_url from tbl_OPR_ClientOperators opr inner join tbl_CHO_Operators cho on cho.cho_id = opr.fsl_choId where opr.operatorId = and opr.ownerId = and opr.fsltablecode = update tbl_OPR_ClientOperators set mfrCustNum = where operatorId = and ownerId = and fsltablecode = update tbl_IMPORT_Operators set fseOperatorId = where eai_batchId = and ownerId = and ownerType = and affiliate_nbr = ---> SELECT O.mfrCustNum FROM tbl_OPR_ClientOperators O WHERE O.ownerId = AND O.FSLTableCode = AND O.mfrCustNum is not null and O.mfrCustNum <> '' AND O.RecordSource LIKE GROUP BY O.mfrCustNum HAVING COUNT(*) > 1 SELECT O.CompanyName, O.mfrCustNum, O.OperatorId FROM tbl_OPR_ClientOperators O WHERE O.ownerId = AND O.FSLTableCode = AND O.RecordSource LIKE AND O.mfrCustNum IN () ORDER BY CompanyName DELETE FROM tbl_OPR_ClientOperators WHERE ownerId = AND FSLTableCode = AND OperatorId IN () SELECT O.mfrCustNum FROM tbl_OPR_ClientOperators O WHERE O.ownerId = AND O.FSLTableCode = AND O.mfrCustNum is not null and O.mfrCustNum <> '' GROUP BY O.mfrCustNum HAVING COUNT(*) > 1 SELECT * FROM tbl_OPR_ClientOperators O WHERE O.ownerId = AND O.FSLTableCode = AND O.mfrCustNum is not null and O.mfrCustNum <> '' AND mfrCustNum IN () UPDATE tbl_OPR_ClientOperators SET CRMActive = 'N' WHERE ownerId = AND FSLTableCode = AND mfrCustNum IS NOT NULL AND mfrCustNum <> '' AND mfrCustNum IN () insert into tbl_CRM_Affiliations ( ownerId, ownerType, affiliationType, orgId, orgType, affiliationDate, affiliationStatus, partnerId, partnerType, partnerAffiliateNbr, sourceId, sourceType ) select distinct i.ownerId, i.ownerType, i.fseAffiliationType affiliationType, i.fseAffiliationOperatorId orgId, coalesce( i.affiliation_orgType, 'OPR' ) orgType, coalesce( affiliate_date, { fn now() } ) affiliationDate, 'A' affiliationStatus, i.fseOperatorId partnerId, 'OPR' partnerType, i.affiliate_nbr partnerAffiliateNbr, i.eai_batchId sourceId, 'SYS' sourceType from tbl_IMPORT_Operators i left outer join tbl_CRM_Affiliations caf on caf.ownerId = i.ownerId and caf.ownerType = i.ownerType and caf.affiliationType = i.fseAffiliationType and caf.partnerId = i.fseOperatorId and caf.partnerType = 'OPR' and caf.orgId = i.fseAffiliationOperatorId and caf.orgType = 'OPR' where i.fseOperatorId is not null and i.eai_batchId = and i.ownerId = and i.ownerType = and i.fseAffiliationOperatorId is not null and i.eai_lineNumber >= and i.eai_lineNumber < and caf.affiliationId is null update i set i.fseAffiliationId = a.affiliationId from tbl_IMPORT_Operators i inner join tbl_CRM_Affiliations a on a.ownerId = i.ownerId and a.ownerType = i.ownerType and a.affiliationType = i.affiliation_type and a.orgId = i.fseAffiliationOperatorId and a.orgType = i.affiliation_orgType and a.partnerType = 'OPR' and a.partnerId = i.fseOperatorId and a.partnerAffiliateNbr = i.affiliate_nbr inner join tbl_OPR_ClientOperators opr on opr.operatorId = a.partnerId and opr.ownerId = a.ownerId and opr.fsltablecode = a.ownerType where i.eai_batchId = and i.ownerId = and i.ownerType = and i.eai_lineNumber >= and i.eai_lineNumber < select eai_importId, eai_batchId, ownerId, ownerType, fseOperatorId, fseOperatorStatus, fseContactId, fseContactStatus, contact_last_name, contact_first_name, contact_fax, contact_phone, contact_mobile_phone, contact_email, contact_last_modify_date, primary_contact_indicator, contact_title, contact_job_function from tbl_IMPORT_Operators ci where ci.eai_batchId = and ci.ownerId = and ci.ownerType = and ci.fseOperatorId is not null and ci.fseInclude = 'Y' and ci.eai_lineNumber >= and ci.eai_lineNumber < select jobFunctionId from qmd_partnerContactJobFunctions where jobFunctionName = set nocount on insert into tbl_FSPro_members ( firstName, lastName, email, phone, fax, cellPhone, officeAddrSame, createDate, lastUpdated, originalSource, orgId, orgType, ownerId, fsl_tablecode, title, cajobfunction ) values( , , , , , , 1, { fn now() }, , , , , , , , ) select contactId = @@identity set nocount off update tbl_OPR_ClientOperators set primaryContactId = where operatorId = update tbl_IMPORT_Operators set fseContactId = , fseContactStatus = 'A' where eai_importId = select m.fspro_userid, m.firstName, m.lastName, m.email, m.title, m.caJobFunction, m.phone, m.fax, m.cellPhone from tbl_FSPro_members m where m.ownerId = and m.fsl_tablecode = and m.orgId = and m.orgType = 'OPR' and m.fspro_userId = update tbl_FSpro_members set lastUpdated = { fn now() } , firstName = , lastName = , email = , phone = , fax = , cellPhone = , title = where fspro_userId = and ownerId = and fsl_tablecode= select distinct fseAffiliationOperatorId from tbl_IMPORT_Operators where eai_batchId = and fseInclude = 'Y' and eai_lineNumber >= and eai_lineNumber < #summaryHTML# select count(*) recordsRemaining from tbl_IMPORT_Operators where ownerId = and ownerType = and eai_batchId = and eai_lineNumber > #summaryHTML#