select affiliation_orgType, affiliation_orgNbr, count(*) lineCount from tbl_IMPORT_Operators where ownerId = and ownerType = and eai_batchId = group by affiliation_orgType, affiliation_orgNbr select distinct bro.broker_id, bro.broker_name, bro.brokerAbbrevName from tbl_SMA_BrokerOfficeLink sma inner join tblBrokers bro on bro.broker_id = sma.brokerId where sma.brokerId = and sma.mfr_Id = select distinct sma.territoryId from tbl_SMA_BrokerOfficeLink sma inner join tblBrokers bro on bro.broker_id = sma.brokerId where sma.brokerId = and sma.mfr_Id = update tbl_IMPORT_Operators set fseAffiliationBrokerId = , affiliation_type = 'OPR', recordSource = , fseOperatorStatus = 'A', fseContactStatus = 'A', fseTerritoryId = null, fseOperatorId = null, fseSegmentId = null, fseAffiliationId = null where ownerId = and ownerType = and eai_batchId = update i set i.fseSegmentId = cs.clientsegid from tbl_IMPORT_Operators i inner join tbl_OPR_ClientSegments cs on cs.segmentId = i.company_segment_fslid and cs.ownerId = i.ownerId and cs.fsl_tablecode = i.ownerType where i.ownerId = and i.ownerType = and i.eai_batchId = and i.fseSegmentId is null update i set i.fseSegmentId = coalesce( css.clientsegid, cs.clientsegid ) from tbl_IMPORT_Operators i left outer join tbl_OPR_ClientSegments cs on cs.description = i.company_segment and cs.clientSegLevel = 1 and cs.ownerId = i.ownerId and cs.fsl_tablecode = i.ownerType left outer join tbl_OPR_ClientSegments css on css.description = i.company_sub_segment and css.clientSegLevel = 2 and css.parentClientSegId = cs.clientsegid and css.ownerId = cs.ownerId and css.fsl_tablecode = cs.fsl_tablecode where i.ownerId = and i.ownerType = and i.eai_batchId = and i.fseSegmentId is null set nocount on update i set i.fseOperatorId = a.partnerId, i.fseOperatorStatus = 'U', i.fseTerritoryId = opr.territoryId, 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.fseAffiliationBrokerId and a.orgType = i.affiliation_orgType and a.partnerType = 'OPR' 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 = select rowsAffected = @@rowcount set nocount off set nocount on update i set i.fseCdrId#dstIdx# = cdr.cdr_recordId from tbl_IMPORT_Operators i inner join tbl_CDR_Distributors cdr on cdr.fsl_dstid = i.company_dst#dstIdx#_fslid and cdr.ownerId = i.ownerId and cdr.fsl_tablecode = i.ownerType inner join tbl_TER_Territories t on t.ownerId = cdr.ownerId and t.fsl_tablecode = cdr.fsl_tablecode and t.territoryId = cdr.cdr_territoryId where i.ownerId = and i.ownertype = and i.eai_batchid = and i.fseTerritoryId is null and t.territoryId in () select rowsAffected = @@rowcount set nocount off Distributors Matched: #qmd_setCdrDistributor.rowsAffected#
set nocount on update i set i.fseTerritoryId = t.territoryId from tbl_IMPORT_Operators i inner join tbl_TER_Territories t on t.ownerId = i.ownerId and t.fsl_tablecode = i.ownerType and t.internalSalesId = i.territory_id where i.ownerId = and i.ownertype = and i.eai_batchid = and i.fseTerritoryId is null and i.territory_id is not null and t.territoryId in () select rowsAffected = @@rowcount set nocount off set nocount on update i set i.fseTerritoryId = t.territoryId from tbl_IMPORT_Operators i inner join tbl_TER_Territories t on t.ownerId = i.ownerId and t.fsl_tablecode = i.ownerType and t.name = i.territory_id where i.ownerId = and i.ownertype = and i.eai_batchid = and i.fseTerritoryId is null and i.territory_id is not null and t.territoryId in () select rowsAffected = @@rowcount set nocount off set nocount on update i set i.fseTerritoryId = t.territoryId from tbl_IMPORT_Operators i inner join tbl_CDR_Distributors cdr on cdr.fsl_dstid = i.company_dst#dstIdx#_fslid and cdr.ownerId = i.ownerId and cdr.fsl_tablecode = i.ownerType inner join tbl_TER_Territories t on t.ownerId = cdr.ownerId and t.fsl_tablecode = cdr.fsl_tablecode and t.territoryId = cdr.cdr_territoryId where i.ownerId = and i.ownertype = and i.eai_batchid = and i.fseTerritoryId is null and t.territoryId in () select rowsAffected = @@rowcount set nocount off set nocount on update i set i.fseTerritoryId = ter.territoryId from tbl_IMPORT_Operators i inner join tbl_UT_ZipCodes_3d z3d on z3d.zipcode_3d = left( i.company_zip, 3 ) inner join tbl_TER_ZipCodeLink zcl on zcl.zip3d_id = z3d.zip3d_id inner join tbl_TER_Territories ter on ter.territoryId = zcl.territoryId and ter.ownerId = i.ownerId and ter.fsl_tablecode = i.ownerType where i.eai_batchId = and i.ownerId = and i.ownerType = and i.fseTerritoryId is null and ter.territoryId in () select rowsAffected = @@rowcount set nocount off update i set i.fseBsrId = m.fspro_userId from tbl_IMPORT_Operators i inner join tbl_FSPro_members m on m.email = i.custmgr_email and m.ownerId = i.fseAffiliationBrokerId and m.fsl_tablecode = 'BRO' and m.staffMember = 1 where i.eai_batchId = and i.ownerId = and i.ownerType = select * from qmd_classifications order by sortRank desc select distinct ownerId, ownerType, fseOperatorId, fseTerritoryId, fseSegmentId, fseAffiliationBrokerId, fseBsrId, recordSource, company_name, company_url, territory_id, company_phone, company_address1, company_address2, company_city, company_state, company_zip, company_last_modify_date, affiliate_nbr, affiliation_type, affiliation_orgNbr, affiliation_orgType from tbl_IMPORT_Operators i where i.eai_batchId = and i.ownerId = and i.ownerType = and i.fseOperatorId is null and i.fseTerritoryId is not null T1: #fseTerritoryId#
select opr.operatorId, opr.companyName, opr.city, opr.state, opr.address, opr.address2, opr.zipCode 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 = and i.fseOperatorStatus is null where opr.ownerId = and opr.fsltablecode = and opr.territoryId = and left( opr.zipCode, 5 ) = and i.fseOperatorId is null Potential match count = #qmd_operatorLookup.recordCount#
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 (( addressEditDistance <= 3 or addressEditDistance2 <= 3 ) and companyNameEditDistance = 0 ) order by companyNameEditDistance, addressEditDistance

		#qmd_match.companyNameEditDistance#		#qmd_match.addressEditDistance#	#qmd_match.addressEditDistance2#
LIST		#company_name#		#company_address1#	#company_address2#	#company_city#	#company_state#	#company_zip#
PORTFOLIO	#qmd_match.operatorId#	#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 affiliate_nbr = insert into tbl_OPR_ClientOperators ( ownerId, fsltablecode, companyName, mfrcustnum, address, address2, city, state, zipcode, recordSource, datecreated, lastUpdated, oprSegment, independentYn, oprCompanyType, setmethod, classificationId, focusAccount, territoryId, xrefData, mfr_bsr_id, distrib1, distrib2, lastInteractionDate, url ) select distinct ownerId, ownerType fsltablecode, company_name companyName, null mfrcustnum, company_address1 address, company_address2 address2, company_city city, company_state state, company_zip zipcode, recordSource, { fn now() } datecreated, convert( datetime, company_last_modify_date ) lastUpdated, fseSegmentId oprSegment, 1 independentYn, 0 oprCompanyType, case when fseBsrId is null then 3 else 5 end setmethod, #_classificationId# classificationId, 'N' focusAccount, fseTerritoryId territoryId, affiliate_nbr xrefData, coalesce( fseBsrId, 0 ) fseBsrId, fseCDRId1, fseCDRId2, convert( datetime, company_last_interaction_date ), case when company_url = '' then null else company_url end company_url from tbl_IMPORT_Operators where eai_batchId = and ownerId = and ownerType = and fseOperatorStatus = 'A' and fseTerritoryId is not null /* bind new operator Ids */ update i set i.fseOperatorId = opr.operatorId from tbl_IMPORT_Operators i inner join tbl_OPR_ClientOperators opr on opr.ownerId = i.ownerId and opr.fsltablecode = i.ownerType and opr.xrefData = i.affiliate_nbr and opr.recordSource = i.recordSource where i.fseOperatorId is null and i.eai_batchId = and i.ownerId = and i.ownerType = update opr set opr.mfrCustNum = ltrim( str( opr.operatorId / 10000.0 + 15000.0, 25, 4 )) from tbl_IMPORT_Operators i inner join tbl_OPR_ClientOperators opr on opr.ownerId = i.ownerId and opr.fsltablecode = i.ownerType and opr.operatorId = i.fseOperatorId and opr.recordSource = i.recordSource where i.fseOperatorStatus = 'A' and opr.mfrcustnum is null and i.eai_batchId = and i.ownerId = and i.ownerType = update opr set opr.companyName = i.company_name, opr.address = i.company_address1, opr.address2 = i.company_address2, opr.city = i.company_city, opr.state = i.company_state, opr.zipcode = i.company_zip, opr.lastUpdated = convert( datetime, i.company_last_modify_date ), opr.lastInteractionDate = convert( datetime, i.company_last_interaction_date ), opr.oprSegment = i.fseSegmentId, opr.territoryId = i.fseTerritoryId, opr.setMethod = case when i.fseBsrId is null then 3 else 5 end, opr.mfr_bsr_id = coalesce( i.fseBsrId, 0 ), opr.distrib1 = i.fseCDRId1, opr.distrib2 = i.fseCDRId2, opr.xrefData = i.affiliate_nbr, opr.crmActive = 'Y', opr.url = case when i.company_url = '' then null else i.company_url end from tbl_OPR_ClientOperators opr inner join tbl_IMPORT_Operators i on i.fseOperatorId = opr.operatorId and i.ownerId = opr.ownerId and i.ownerType = opr.fsltablecode where i.eai_batchId = and i.ownerId = and i.ownerType = and ( i.fseOperatorStatus = 'A' or i.fseOperatorStatus = 'M' ) and i.fseTerritoryId is not null insert into tbl_CRM_Affiliations ( ownerId, ownerType, affiliationType, orgId, orgType, affiliationDate, affiliationStatus, partnerId, partnerType, partnerAffiliateNbr, sourceId, sourceType ) select distinct ownerId, ownerType, affiliation_type affiliationType, fseAffiliationBrokerId orgId, affiliation_orgType orgType, { fn now() } affiliationDate, 'A' affiliationStatus, fseOperatorId partnerId, 'OPR' partnerType, affiliate_nbr partnerAffiliateNbr, eai_batchId sourceId, 'SYS' sourceType from tbl_IMPORT_Operators where fseOperatorId is not null and fseOperatorStatus in ( 'M', 'A' ) and eai_batchId = and ownerId = and ownerType = 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.fseAffiliationBrokerId 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 = set nocount on update i set i.fseContactId = a.partnerId, i.fseContactStatus = 'U', i.fseContactAffiliationId = 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.fseAffiliationBrokerId and a.orgType = i.affiliation_orgType and a.partnerType = 'OPRC' and a.partnerAffiliateNbr = i.affiliate_contactNbr inner join tbl_FSPro_members mem on mem.fspro_userid = a.partnerId and mem.ownerId = a.ownerId and mem.fsl_tablecode = a.ownerType and mem.orgId = i.fseOperatorId and mem.orgType = 'OPR' where i.eai_batchId = and i.ownerId = and i.ownerType = and i.fseOperatorId is not null select rowsAffected = @@rowcount set nocount off update i set i.fseContactId = mem.fspro_userId, i.fseContactStatus = 'M' from tbl_IMPORT_Operators i inner join tbl_FSPro_Members mem on mem.ownerId = i.ownerId and mem.fsl_tablecode = i.ownerType and mem.orgId = i.fseOperatorId and mem.orgType = 'OPR' and ( mem.email = i.contact_email or ( mem.firstName = i.contact_first_name and mem.lastName = i.contact_last_name )) where i.eai_batchId = and i.ownerId = and i.ownerType = and i.fseOperatorId is not null and i.fseContactId is null update tbl_IMPORT_Operators set contact_first_name = case when contact_first_name is null and contact_full_name is not null then rtrim( left( contact_full_name, charindex( ' ', contact_full_name ))) else contact_first_name end, contact_last_name = case when contact_last_name is null and contact_full_name is not null then ltrim( right( contact_full_name, len( contact_full_name ) - charindex( ' ', contact_full_name ))) else contact_last_name end where eai_batchId = and ownerId = and ownerType = select eai_importId, ownerId, ownerType, fseOperatorId, fseContactId, recordSource, affiliate_contactNbr, contact_salutation, 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, fseOperatorStatus, contact_address1, contact_address2, contact_city, contact_state, contact_zip, contact_use_office_addr from tbl_IMPORT_Operators ci where ci.ownerId = and ci.ownerType = and ci.eai_batchId = and ci.fseOperatorId is not null and ci.fseContactId is null and ci.fseContactStatus = 'A' select jobFunctionId from qmd_partnerContactJobFunctions where jobFunctionName = set nocount on insert into tbl_FSPro_members ( salutation, firstName, lastName, email, phone, fax, cellPhone, officeAddrSame, createDate, lastUpdated, originalSource, orgId, orgType, ownerId, fsl_tablecode, title, cajobfunction, address1, address2, city, state, zipcode ) values( , , , , , , , , { fn now() }, , , , , , , , , , , , , ) select contactId = @@identity set nocount off update tbl_IMPORT_Operators set fseContactId = where eai_importId = update m set m.firstName = i.contact_first_name, m.lastName = i.contact_last_name, m.salutation = i.contact_salutation, m.address1 = i.contact_address1, m.address2 = i.contact_address2, m.city = i.contact_city, m.state = i.contact_state, m.zipCode = i.contact_zip, m.title = i.contact_title, m.phone = i.contact_phone, m.fax = i.contact_fax, m.cellPhone = i.contact_mobile_phone, m.officeAddrSame = case when i.contact_use_office_addr = 1 then 1 else 0 end, m.lastUpdated = convert( datetime, i.contact_last_modify_date ), m.email = i.contact_email from tbl_FSPro_members m inner join tbl_IMPORT_Operators i on i.ownerId = m.ownerId and i.ownerType = m.fsl_tablecode and i.fseContactId = m.fspro_userId and i.fseContactStatus in ( 'U', 'M' ) and i.fseOperatorId = m.orgId where i.eai_batchId = and i.ownerId = and i.ownerType = and m.orgType = 'OPR' update opr set opr.primaryContactId = i.fseContactId from tbl_OPR_ClientOperators opr inner join tbl_IMPORT_Operators i on i.ownerId = opr.ownerId and i.ownerType = opr.fsltablecode and i.fseOperatorId = opr.operatorId where i.eai_batchId = and i.ownerId = and i.ownerType = and i.primary_contact_indicator = 'Y' insert into tbl_CRM_Affiliations ( ownerId, ownerType, affiliationType, orgId, orgType, affiliationDate, affiliationStatus, partnerId, partnerType, partnerAffiliateNbr, sourceId, sourceType ) select distinct ownerId, ownerType, affiliation_type affiliationType, fseAffiliationBrokerId orgId, affiliation_orgType orgType, { fn now() } affiliationDate, 'A' affiliationStatus, fseContactId partnerId, 'OPRC' partnerType, affiliate_contactNbr partnerAffiliateNbr, eai_batchId sourceId, 'SYS' sourceType from tbl_IMPORT_Operators i where i.eai_batchId = and i.ownerId = and i.ownerType = and i.fseContactStatus in ('M','A') and i.fseContactId is not null #summaryHTML# #summaryHTML#