update tbl_IMPORT_Operators set company_id = primary_distributor_id + '-' + company_id where ownerId = and ownerType = and eai_batchId = and len( primary_distributor_id ) = 3 and len( company_id ) = 6 update tbl_IMPORT_Operators set company_id = primary_distributor_id + '-' + company_id where ownerId = and ownerType = and eai_batchId = and len( primary_distributor_id ) = 3 and nullif( company_id, '' ) is not null and left( company_id, 4 ) != primary_distributor_id + '-'

Debug: default roster mode is #_rosterModeDefault#

select * from tbl_IMPORT_Operators where eai_batchId = and eai_lineNumber = 1 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, company_country, company_cuisine, 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_country,company_cuisine, 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, company_country, company_cuisine 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 and d.company_cuisine = o.company_cuisine and d.company_country = o.company_country 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 = select companyName from tbl_OPR_ClientOperators with (nolock) where ownerId = and fsltablecode = and companyName = and oprCompanyType = '1' and 1 = 2 select opr.companyName from tbl_OPR_ClientOperators opr with (nolock) inner join tbl_IMPORT_Aliases a with (nolock) on a.ownerId = opr.ownerId and a.ownerType = opr.fsltablecode and a.objectType = 'operatorCompanyName' and a.objectId = opr.operatorId where opr.ownerId = and opr.fsltablecode = and a.alias = and opr.oprCompanyType = '1' update tbl_IMPORT_Operators set external_id_type = 'HQ', external_id_owner = , affiliation_orgName = null where ownerId = and ownerType = and eai_batchId = and affiliation_orgName = and nullif( affiliation_type, '' ) is null ---> select distinct external_id_type, external_id_owner from tbl_IMPORT_Operators where eai_batchId = and nullif( external_id_type, '' ) is not null and coalesce( external_id_type, '' ) <> 'MDM' select broker_id from tblBrokers where broker_name = or BrokerAbbrevName = select mfr_id from tblManufacturers where mfr_name = or MFRAbbrev_Name = select * from qmd_pt where hireStatus in ( 'A','P' ) select opr.operatorId from tbl_OPR_ClientOperators opr left outer join tbl_CHO_Operators cho on cho.cho_id = opr.fsl_choId where opr.ownerId = and opr.fsltablecode = and oprCompanyType = '1' and ( opr.companyName = or opr.dbaName = or opr.mfrCustNum = or cho.cho_name = or cho.cho_dbaName = ) select userid from tbl_MSC_FslibraryUsers with (nolock) where company = update tbl_IMPORT_Operators set fseExternalIdRefId = where eai_batchId = and external_id_type = and external_id_owner = update tbl_IMPORT_Operators set fseInclude = 'N', fseExternalIdRefId = 0 where eai_batchId = and external_id_type = and external_id_owner =

Load Halted - Unresolved External Id References
Type	Owner	Status
#htmleditformat( external_id_type )#	#htmleditformat( external_id_owner )#	#htmleditformat( external_id_status )#

Multiple Matches - The value in EXTERNAL_ID_OWNER matched more than one Manufacturer by Manufacturer Name or Manufacturer Abbreviation in the the MFR Profile Database

Unsupported Type - The value in EXTERNAL_ID_TYPE is invalid and must be MFR if the EXTERNAL ID mapping/setting behaviour is to be used

Not Found - The value in EXTERNAL_ID_OWNER did not match a Manufacturer by Manufacturer Name or Manufacturer Abbreviation in the the MFR Profile Database

MFR Not Active/Pending - The value in EXTERNAL_ID_OWNER matches a Manufacturer but that Manufacturer is not in the clients Manufacturer Portfolio with at least one MFR Territory with a hire status of Active or Pending

No Territory Resources - The value in EXTERNAL_ID_OWNER matches a Manufacturer but that Manufacturer is not in the clients Manufacturer Portfolio with at least one MFR Territory with a hire status of Active or Pending

select imp.eai_lineNumber, imp.eai_importId from tbl_IMPORT_Operators imp with (nolock) inner join tbl_OPR_ClientOperators opr with (nolock) on opr.ownerId = imp.ownerId and opr.fsltablecode = imp.ownerType and opr.operatorId = imp.fseOperatorId where imp.eai_batchId = and imp.ownerId = and imp.ownerType = and imp.fseOperatorId is not null and imp.eai_lineNumber >= and imp.eai_lineNumber < update tbl_IMPORT_Operators set fseOperatorStatus = 'U' where ownerId = and ownertype = and eai_batchid = and eai_lineNumber = and eai_importId = select distinct external_id, external_id_owner, fseTerritoryId from tbl_IMPORT_Operators where eai_batchId = and ownerId = and ownerType = and external_id_type = 'MDM' and nullif( external_id, '' ) is not null and fseOperatorId is null and eai_lineNumber >= and eai_lineNumber < select operatorId, territoryId, crmActive from tbl_OPR_ClientOperators where mdmId = and mdmType = and ownerId = and fsltablecode = and territoryId = order by operatorId update tbl_IMPORT_Operators set fseOperatorId = , fseOperatorStatus = 'U', fseTerritoryId = where eai_batchId = and ownerId = and ownerType = and external_id_type = 'MDM' and external_id_owner = and external_id = and fseTerritoryId = and coalesce( fseTerritoryId, 0 ) = 0 and nullif( external_id, '' ) is not null and fseOperatorId is null select distinct primary_dist_duns from tbl_IMPORT_Operators with (nolock) where eai_batchId = and ownerId = and ownerType = and eai_lineNumber >= and eai_lineNumber < and nullif( primary_dist_duns, '' ) is not null and fseCdrId1 is null select cdr.cdr_recordId from tbl_CDR_Distributors cdr with (nolock) inner join tbl_DST_Distributors dst with (nolock) on dst.dstId = cdr.fsl_dstID where cdr.ownerId = and cdr.fsl_tablecode = -- and cdr.fsl_mapPriority = 1 and dst.dunsId = update tbl_IMPORT_Operators set fseCdrId1 = where eai_batchId = and ownerId = and ownerType = and eai_lineNumber >= and eai_lineNumber < and primary_dist_duns = and fseCdrId1 is null

TerritoryMode is #_config.opt_territoryMode#

update b set b.fseTerritoryId = cdr.cdr_territoryId from tbl_IMPORT_Operators b inner join tbl_CDR_Distributors cdr with (nolock) on cdr.cdr_recordId = b.fseCdrId1 and cdr.ownerId = b.ownerId and cdr.fsl_tablecode = b.ownerType where b.eai_batchId = and b.ownerId = and b.ownerType = and b.fseTerritoryId is null select eai_importId, company_address1, company_address2, company_city, company_state, company_zip, fseChoId from tbl_IMPORT_Operators with (nolock) where fseChoId is not null and fseOperatorId is null and eai_batchId = update i set i.company_address1 = case when coalesce( i.company_address1, '' ) = '' then cho.cho_address else i.company_address1 end, i.company_address2 = case when coalesce( i.company_address2, '' ) = '' then cho.cho_address2 else i.company_address2 end, i.company_city = case when coalesce( i.company_city, '' ) = '' then cho.cho_city else i.company_city end, i.company_state = case when coalesce( i.company_state, '' ) = '' then cho.cho_state else i.company_state end, i.company_zip = case when coalesce( i.company_zip, '' ) = '' then cho.cho_zipcode else i.company_zip end from tbl_IMPORT_Operators i inner join tbl_CHO_Operators cho on cho.cho_id = i.fseChoId where i.eai_batchId = and i.eai_importId = update staging set staging.fseOperatorId = opr.operatorId, staging.fseOperatorStatus = 'U' from tbl_IMPORT_Operators staging inner join tbl_OPR_ClientOperators opr on opr.chdId = staging.external_id where staging.ownerId = and staging.ownertype = and staging.eai_batchid = and staging.fseInclude = 'Y' and staging.fseOperatorId is null and staging.external_id_type = 'CHD' and staging.external_id is not null and staging.eai_lineNumber >= and staging.eai_lineNumber < select staging.eai_importId, staging.eai_lineNumber, opr.operatorId from tbl_IMPORT_Operators staging with (nolock) inner join tbl_OPR_ClientOperators opr with (nolock) on opr.ownerId = staging.ownerId and opr.fsltablecode = staging.ownertype and opr.oprparentCo = staging.fseExternalIdRefId and opr.unitNbr = staging.external_id where staging.ownerId = and staging.ownertype = and staging.eai_batchid = and staging.fseInclude = 'Y' and staging.fseOperatorId is null and staging.external_id_type = 'HQ' and staging.external_id is not null and staging.fseExternalIdRefId > 0 and staging.eai_lineNumber >= and staging.eai_lineNumber < update tbl_IMPORT_Operators set fseOperatorId = , fseOperatorStatus = 'U' where ownerId = and ownertype = and eai_batchid = and eai_lineNumber = and eai_importId = select distinct i.external_id, i.external_id_type, i.fseExternalIdRefId, o.operatorId, o.territoryId from tbl_IMPORT_Operators i with (nolock) inner join tbl_IMPORT_Aliases a with (nolock) on a.ownerId = i.ownerId and a.ownerType = i.ownerType and a.objectType = 'OPR' and a.alias = 'M:#qmd_batch.ownerId#;B:' + convert( varchar, i.fseExternalIdRefId ) + ';O:' + i.external_id inner join tbl_OPR_ClientOperators o with (nolock) on o.ownerId = a.ownerId and o.fsltablecode = a.ownerType and o.operatorId = a.objectId where i.ownerId = and i.ownerType = and i.eai_batchId = and i.fseInclude = 'Y' and i.external_id_type = 'BRO' and nullif( i.external_id, '' ) is not null and i.fseExternalIdRefId is not null and i.fseOperatorId is null update tbl_IMPORT_Operators set fseOperatorId = , fseOperatorStatus = 'U', fseTerritoryId = where eai_batchId = and external_id = and external_id_type = and fseExternalIdRefId = select distinct i.external_id, i.external_id_type, i.fseExternalIdRefId, o.operatorId, o.territoryId from tbl_IMPORT_Operators i with (nolock) inner join tbl_IMPORT_Aliases a with (nolock) on a.ownerId = i.ownerId and a.ownerType = i.ownerType and a.objectType = 'OPR' and a.alias = 'M:#qmd_batch.ownerId#;MSC:' + convert( varchar, i.fseExternalIdRefId ) + ';O:' + i.external_id inner join tbl_OPR_ClientOperators o with (nolock) on o.ownerId = a.ownerId and o.fsltablecode = a.ownerType and o.operatorId = a.objectId where i.ownerId = and i.ownerType = and i.eai_batchId = and i.fseInclude = 'Y' and i.external_id_type = 'MSC' and nullif( i.external_id, '' ) is not null and i.fseExternalIdRefId is not null and i.fseOperatorId is null update tbl_IMPORT_Operators set fseOperatorId = , fseOperatorStatus = 'U', fseTerritoryId = where eai_batchId = and external_id = and external_id_type = and fseExternalIdRefId = select distinct s.external_id, s.external_id_type, s.fseExternalIdRefId, opr.operatorId, opr.territoryId from tbl_IMPORT_Operators s with (nolock) inner join tbl_BRO_PrincipalOperators po with (nolock) on po.mfrCustNbr = s.external_id and po.ownerId = s.ownerId and po.ownerType = s.ownerType inner join tbl_BRO_principalTerritories pt with (nolock) on pt.principalTerritoryId = po.principalTerritoryId and pt.ownerId = po.ownerId and pt.ownerType = po.ownerType inner join tbl_BRO_Principals p with (nolock) on p.ownerId = s.ownerId and p.ownerType = s.ownerType and p.mfrId = s.fseExternalIdRefId inner join tbl_OPR_ClientOperators opr with (nolock) on opr.operatorId = po.operatorId and opr.ownerId = po.ownerId and opr.fsltablecode = po.ownerType where s.fseInclude = 'Y' and s.external_id_type = 'MFR' and s.fseExternalIdRefId is not null and nullif( s.external_id, '' ) is not null and s.fseOperatorId is null and s.eai_lineNumber >= and s.eai_lineNumber < and s.eai_batchId = and s.ownerId = and s.ownerType = update tbl_IMPORT_Operators set fseOperatorId = , fseOperatorStatus = 'U', fseTerritoryId = where eai_batchId = and external_id = and external_id_type = and fseExternalIdRefId = and eai_lineNumber >= and eai_lineNumber < select distinct fseAffiliationOperatorId, affiliation_orgName, affiliation_subOrgName from tbl_IMPORT_Operators with (nolock) where ownerId = and ownerType = and eai_batchId = and fseAffiliationOperatorId > 0 and fseAffiliationSubOperatorId is null and nullif( affiliation_subOrgName, '' ) is not null and eai_lineNumber >= and eai_lineNumber < select distinct opr.operatorId from tbl_OPR_ClientOperators opr with (nolock) inner join tbl_IMPORT_Aliases a with (nolock) on a.ownerId = opr.ownerId and a.ownerType = opr.fsltablecode and a.objectType = 'operatorCompanyName' and a.objectId = opr.operatorId where a.ownerId = and a.ownerType = and a.alias = update tbl_IMPORT_Operators set fseAffiliationSubOperatorId = where ownerId = and ownerType = and eai_batchId = and fseAffiliationOperatorId = and affiliation_orgName = and affiliation_subOrgName = and fseAffiliationSubOperatorId is null and eai_lineNumber >= and eai_lineNumber < update tbl_IMPORT_Operators set company_priority = null where eai_batchId = and company_priority not in ( '*','A','B','C','D' ) 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 ownerId, ownerType, fseOperatorId, fseTerritoryId, fseZipTerritoryId, fseCdrId1, fseCdrId2, fseSegmentId, fseZipCode, fseZipCodeStatus, fseClassificationId, company_id, company_segment, company_sub_segment, company_name, company_comment, company_url, company_units_ly, company_units_ty, company_units_ny, external_id, external_id_type, external_id_owner, fseExternalIdRefId, territory_id, company_priority, company_phone, company_fax, company_address1, company_address2, company_city, company_county, company_state, company_zip, company_country,company_last_modify_date, company_cuisine, fseCuisineId, fseCountryId, fseChoId, min( coalesce( fseBsrId, 0 )) fseBsrId , buy_desc from tbl_IMPORT_Operators i with (nolock) where i.eai_batchId = and i.ownerId = and i.ownerType = and i.fseInclude = 'Y' and i.eai_lineNumber >= and i.eai_lineNumber < group by ownerId, ownerType, fseOperatorId, fseTerritoryId, fseZipTerritoryId, fseCdrId1, fseCdrId2, fseSegmentId, fseZipCode, fseZipCodeStatus, fseClassificationId, company_id, company_segment, company_sub_segment, company_name, company_comment, company_url, company_units_ly, company_units_ty, company_units_ny, external_id, external_id_type, external_id_owner, fseExternalIdRefId, territory_id, company_priority, company_phone, company_fax, company_address1, company_address2, company_city, company_county, company_state, company_zip,company_country, company_last_modify_date,company_cuisine, fseCuisineId,fseCountryId, fseChoId , buy_desc order by i.company_name SELECT operatorId FROM tbl_OPR_ClientOperators with (nolock) WHERE operatorId = AND ownerId = AND fsltablecode = AND oprcompanytype = 1 // 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 and addressEditDistance = 255 ) order by companyNameEditDistance, addressEditDistance Potential match count = #qmd_operatorLookup.recordCount#

Operator	Company Name	File	Diff	Address	File	Diff	Address2	File	Distance
#operatorId#	#htmleditformat( companyname )#	#htmleditformat( srcCompanyName )#	#companyNameEditDistance#	#htmleditformat( stdAddress )#	#htmleditformat( srcAddress )#	#addressEditDistance#	#htmleditformat( stdAddress2 )#	#htmleditformat( srcAddress2 )#	#addressEditDistance2#

#htmleditformat( qmd_operatorLookup_result.sql )#

			#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_county = and company_state = and company_country = 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 company_cuisine = and ( company_cuisine is null or company_cuisine = '' ) and fseTerritoryId = and fseTerritoryId is null and fseSegmentId = and fseSegmentId is null and fseCuisineId = and fseCuisineId is null and fseCountryId = and fseCountryId is null and fseCdrId1 = and fseCdrId2 is null and fseCdrId2 = and fseCdrId2 is null update tbl_OPR_CLientOperators set lastUpdated = { fn now() }, lastImportSource = , buyingDecisions = , salesForceId_cs = , mdmType = , mdmId = , chdId = , unitNbr = , oprParentCo = , address = , address2 = , city = , state = , zipCode = , county = , phone = , mfrCustNum = , comments = case when coalesce( comments, '' ) = '' then else comments end , distrib1 = , distrib2 = , oprSegment = , cuisineId = , countryId = , crmActive = 'Y' , territoryId = , setMethod = 1 , mfr_bsr_id = , setMethod = 1 , crmActive = 'Y' , oprType = , classificationId = , fsl_choId = , independentYN = 0 , oprcompanytype = '1' , oprparentco = , independentYN = 0 , oprcompanytype = '0' , url = , num_units_thisYr = , num_units_nextYr = , num_units_lastYr = , comments = , address = , address2 = , city = , county = , state = , zipCode = , phone = , mfrCustNum = , distrib1 = , distrib2 = , oprSegment = , crmActive = 'Y' , territoryId = , setMethod = 1 , mfr_bsr_id = , setMethod = 1 , crmActive = 'Y' , oprType = , url = , num_units_lastYr = , num_units_thisYr = , num_units_nextYr = , classificationId = , fsl_choId = , independentYN = 0 , oprcompanytype = '1' , oprparentco = , independentYN = 0 , oprcompanytype = '0' where operatorId = and ownerId = and fsltablecode = insert into tbl_OPR_ClientOperators( ownerId, fsltablecode, mfrCustNum, companyName, address, address2, city, county, state, zipCode, phone, faxnumber, comments, setMethod, territoryId, crmActive, oprsegment,cuisineId,countryID, distrib1, distrib2, recordSource, lastImportSource, dateCreated, classificationId ,fsl_choId ,independentYN ,oprcompanytype , oprparentco , independentYN , oprcompanytype ,mfr_bsr_id , oprType , chdId , mdmType, mdmId , unitNbr, oprParentCo ,url, num_units_lastYr, num_units_thisYr, num_units_nextYr , buyingDecisions ) values ( , , , , , , , , , , , , , , , 'Y', , null, 'N', , , , , , , , { fn now() }, ,,0,'1' ,,0,'0' , , , , , , , , , , , , ) update tbl_IMPORT_Operators set fseOperatorStatus = 'A', fseOperatorId = 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_cuisine = and ( company_cuisine is null or company_cuisine = '' ) 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 fseCuisineId = and fseCuisineId is null and fseCountryId = and fseCountryId is null and fseCdrId1 = and fseCdrId2 is null and fseCdrId2 = and fseCdrId2 is null update tbl_OPR_ClientOperators set mfrCustNum = , customfld_4 = 'Y' where operatorId = and ownerId = and fsltablecode = Update: #fseOperatorId#
SELECT * FROM tbl_OPR_ClientOperators WHERE operatorId = AND OwnerId = AND fsltablecode = select crmLocked from tbl_OPR_ClientOperators where operatorId = update tbl_OPR_CLientOperators set lastUpdated = { fn now() }, lastImportSource = , companyName = , address = , address2 = , city = , county = , state = , zipCode = , phone = , faxNumber = , comments = case when coalesce( comments, '' ) = '' then else comments end , mfrCustNum = , distrib1 = , distrib2 = , oprSegment = , cuisineId = , countryId = , classificationId = , oprType = , url = , num_units_lastYr = , num_units_thisYr = , num_units_nextYr = , fsl_choId = , independentYN = 0 , oprcompanytype = '1' , oprparentco = , independentYN = 0 , oprcompanytype = '0' , companyName = , address = , address2 = , city = , county = , state = , zipCode = , phone = , faxNumber = , comments = , mfrCustNum = , distrib1 = , distrib2 = , oprSegment = , cuisineId = , countryId = , classificationId = , oprType = , url = , num_units_lastYr = , num_units_thisYr = , num_units_nextYr = , fsl_choId = , independentYN = 0 , oprcompanytype = '1' , oprparentco = , independentYN = 0 , oprcompanytype = '0' , mdmType = , mdmId = , 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, opr.num_units_lastYr = cho.cho_UnitsLastYear, opr.num_units_thisYr = cho.cho_UnitsThisYear, opr.num_units_nextYr = cho.cho_UnitsNextYear 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 = select num_units_lastYr, num_units_thisYr, num_units_nextYr from tbl_OPR_ClientOperators with (nolock) where operatorId = and ownerId = and 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 = ---> #PercentComplete# % processed in " & GetTickCount() - _startingTickCount & "ms")> ---> SELECT O.mfrCustNum FROM tbl_OPR_ClientOperators O WHERE O.ownerId = AND O.FSLTableCode = and nullif( o.mfrCustNum, '' ) is not null 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 () AND nullif( o.mfrCustNum, '' ) is not null 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 nullIf( o.mfrCustNum, '' ) is not null AND 1 = 2 GROUP BY O.mfrCustNum HAVING COUNT(*) > 1 SELECT * FROM tbl_OPR_ClientOperators O WHERE O.ownerId = AND O.FSLTableCode = AND nullif( o.mfrCustNum, '' ) is not null AND mfrCustNum IN () UPDATE tbl_OPR_ClientOperators SET CRMActive = 'N' WHERE ownerId = AND FSLTableCode = AND mfrCustNum IS NOT NULL AND mfrCustNum <> '' AND mfrCustNum IN () update caf set caf.affiliationStatus = 'A', caf.deaffiliationDate = null from tbl_IMPORT_Operators i inner 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.fseAffiliationOperatorId is not null and caf.affiliationStatus = 'I' and i.eai_lineNumber >= and i.eai_lineNumber < update caf set caf.membershipQualificationId = i.fseMembershipQualificationId from tbl_IMPORT_Operators i inner 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.fseAffiliationOperatorId is not null and i.eai_lineNumber >= and i.eai_lineNumber < select distinct i.fseOperatorId, i.affiliate_date, i.fseAffiliationType, i.fseAffiliationOperatorId, i.ownerId, i.ownerType from tbl_IMPORT_Operators i inner 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.fseAffiliationOperatorId is not null and caf.affiliationStatus = 'A' and i.affiliate_date is not null and i.eai_lineNumber >= and i.eai_lineNumber < UPDATE tbl_CRM_Affiliations SET affiliationDate = WHERE ownerId = and ownerType = and affiliationType = and partnerId = and partnerType = 'OPR' and orgId = and orgType = 'OPR' select distinct i.fseOperatorId, i.affiliate_nbr, i.fseAffiliationType, i.fseAffiliationOperatorId from tbl_IMPORT_Operators i inner 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.fseAffiliationOperatorId is not null and caf.affiliationStatus = 'A' and nullif( i.affiliate_nbr, '' ) is not null and nullif( caf.partnerAffiliateNbr, '' ) is null and fseAffiliationId is null and i.eai_lineNumber >= and i.eai_lineNumber < select affiliationId from tbl_CRM_Affiliations where ownerId = and ownerType = and nullif( partnerAffiliateNbr, '' ) is null and affiliationType = and orgType ='OPR' and orgId = and partnerId = and partnertype = 'OPR' and affiliationStatus = 'A' update caf set caf.partnerAffiliateNbr = from tbl_CRM_Affiliations caf where caf.ownerId = and caf.ownerType = and caf.affiliationId = update i set i.fseAffiliationId = from tbl_IMPORT_Operators i where fseOperatorId = and i.eai_batchId = and i.fseAffiliationId is null insert into tbl_CRM_Affiliations ( ownerId, ownerType, affiliationType, orgId, orgType, affiliationDate, affiliationStatus, partnerId, partnerType, partnerAffiliateNbr, sourceId, sourceType, membershipQualificationId ) select distinct i.ownerId, i.ownerType, i.fseAffiliationType affiliationType, i.fseAffiliationOperatorId orgId, coalesce( i.affiliation_orgType, 'OPR' ) orgType, coalesce( affiliate_date, ) affiliationDate, 'A' affiliationStatus, i.fseOperatorId partnerId, 'OPR' partnerType, i.affiliate_nbr partnerAffiliateNbr, i.eai_batchId sourceId, 'SYS' sourceType, fseMembershipQualificationId from tbl_IMPORT_Operators i with (nolock) left outer join tbl_CRM_Affiliations caf with (nolock) 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_salutation, contact_last_name, contact_first_name, contact_fax, contact_phone, contact_phone_ext, contact_mobile_phone, contact_email, contact_last_modify_date, primary_contact_indicator, contact_title, contact_job_function, substring(contact_email_yes_no,1,1) contact_email_yes_no 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 ( salutation, firstName, lastName, email, phone, phoneExt, fax, cellPhone, officeAddrSame, createDate, lastUpdated, originalSource, orgId, orgType, ownerId, fsl_tablecode, title, cajobfunction, EmailYesNo ) values( , , , , , , , , 1, { fn now() }, , , , , , , , , 1 ) 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.salutation, m.fspro_userid, m.firstName, m.lastName, m.email, m.title, m.caJobFunction, m.phone, m.phoneExt, m.fax, m.cellPhone, m.EmailYesNo 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() } , salutation = , firstName = , lastName = , email = , phone = , phoneExt = , fax = , cellPhone = , title = , EmailYesNo = 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 < select count(*) recordsRemaining from tbl_IMPORT_Operators where ownerId = and ownerType = and eai_batchId = and eai_lineNumber > select distinct s.external_id, s.fseExternalIdRefId mfrId, opr.operatorId, opr.territoryId, s.external_id_priority from tbl_IMPORT_Operators s inner join tbl_OPR_ClientOperators opr on opr.operatorId = s.fseOperatorId and opr.ownerId = s.ownerId where s.external_id_type = 'MFR' and s.fseExternalIdRefId is not null and s.fseInclude = 'Y' and s.eai_batchId = order by mfrId, opr.territoryId select ppl.principalId, m.mfr_name from tbl_BRO_principals ppl inner join tblManufacturers m on m.mfr_id = ppl.mfrId where ppl.mfrId = and ppl.ownerId = select pt.principalTerritoryId, pt.mfrTerritoryName, pt.mfrTerritoryNbr from tbl_BRO_PrincipalTerritories pt inner join tbl_BRO_PrincipalTerritoryResources ptr on ptr.principalTerritoryId = pt.principalTerritoryId where ptr.resourceType = 'TER' and ptr.resourceId = and pt.principalId = and pt.hireStatus in ( 'A', 'P' ) select po.principalOperatorId, po.mfrCustNbr, po.focusAccount from tbl_BRO_PrincipalOperators po inner join tbl_BRO_PrincipalTerritories pt on pt.principalTerritoryId = po.principalTerritoryId where po.ownerId = and po.operatorId = and pt.principalId = insert into tbl_BRO_PrincipalOperators( ownerId, ownerType, principalTerritoryId, operatorId, mfrCustNbr, focusAccount, recordSource ) values ( , , , , , , '/z_qryMaster/utilityQueries/DataImport/Operators/baseOperatorImport.cfm') update tbl_BRO_PrincipalOperators set mfrCustNbr = , focusAccount = where principalOperatorId = update tbl_IMPORT_Operators set fseExternalIdStatus = where eai_batchId = and fseOperatorId = and external_id = and fseExternalIdRefId = insert into tbl_IMPORT_Aliases ( ownerId, ownerType, alias, objectType, objectId ) select distinct ownerId, ownerType, 'M:#qmd_batch.ownerId#;' + case when external_id_type = 'BRO' then 'B' else external_id_type end + ':' + convert( varchar, fseExternalIdRefId ) + ';O:' + external_id as alias, 'OPR' objectType, fseOperatorId objectId from tbl_IMPORT_Operators where fseExternalIdRefId is not null and fseOperatorStatus in ( 'A','M' ) and fseOperatorId is not null and fseInclude = 'Y' and nullif( external_id_type, '' ) is not null and nullif( external_id, '' ) is not null and eai_batchId = and ownerId = and ownerType =

Member Group Roster Load Mode

In Roster Load Mode, any record with a member id (company_id) specified no fuzzy matching will be done, the member id will be the sole method for determining if the record already exists or not. New members will be added if the provided member_id is not found. Existing members will be updated based on the contents of the file.

select i.eai_lineNUmber, o.ownerId, o.fsltablecode, i.company_name, i.company_address1, i.company_address2, i.company_city, i.company_state, i.company_zip, o.companyName, o.address, o.address2, o.city, o.state, o.zipcode, ter.name, ter.territoryId from tbl_IMPORT_Operators i with (nolock) inner join tbl_OPR_ClientOperators o with (nolock) on o.operatorId = i.fseOperatorId and o.ownerId = i.ownerId and o.fsltablecode = i.ownerType join tbl_TER_Territories ter with (nolock) on ter.territoryId = o.territoryId and ter.ownerId = i.ownerId and ter.fsl_tableCode = i.ownerType where eai_batchId = and fseInclude = 'Y' and fseOperatorStatus = 'M' and i.ownerId = and i.ownerType = order by ter.name, eai_lineNumber

Batch: #qmd_batch.batchId#

Fuzzy Match Results
#name#
Match	Name	Address	City/State/Zip	Line
#lsnumberformat( currentRow )#	#htmleditformat( company_name )#	#htmleditformat( company_address1 )# #htmleditformat( company_address2 )#	#htmleditformat( company_city )#, #htmleditformat( company_state )# #htmleditformat( company_zip )#	#lsnumberformat( eai_lineNumber )#
&##160;	#htmleditformat( companyName )#	#htmleditformat( address )# #htmleditformat( address2 )#	#htmleditformat( city )#, #htmleditformat( state )# #htmleditformat( zipcode )#	&##160;

select count(*) as fuzzyMatchCount, ter.name, ter.territoryId from tbl_IMPORT_Operators i inner join tbl_OPR_ClientOperators o on o.operatorId = i.fseOperatorId and o.ownerId = i.ownerId and o.fsltablecode = i.ownerType join tbl_TER_Territories ter on ter.territoryId = o.territoryId and ter.ownerId = i.ownerId and ter.fsl_tableCode = i.ownerType where eai_batchId = and fseInclude = 'Y' and fseOperatorStatus = 'M' group by o.territoryId, ter.name, ter.territoryId

Fuzzy Match Summary By Territory
Territory	Fuzzy Matched
#htmleditformat( name )#	#htmleditformat( fuzzyMatchCount )#

select fseExternalIdStatus, count(*) lineCount, min( eai_lineNumber ) firstLine from tbl_IMPORT_Operators where eai_batchId = and fseExternalIdStatus is not null group by fseExternalIdStatus order by lineCount desc

External ID Status
Status	Lines	First
#htmleditformat( fseExternalIdStatus )#	#lsnumberformat( lineCount )#	#lsnumberformat( firstLine )#

select staging.fseZipCode, staging.company_state, usps.stateAbbr, count(*) lineCount, min( eai_lineNumber ) firstLine from tbl_IMPORT_Operators staging inner join tbl_USPS_CityStateDetails usps on usps.zipCode = left( fseZipCode, 5 ) where staging.fseZipCode is not null and usps.stateAbbr <> staging.company_state and staging.ownerId = and staging.ownertype = and staging.eai_batchid = group by staging.fseZipCode, usps.stateAbbr, staging.company_state order by lineCount desc

Sketchy Zip Codes
File		Postal	&##160;
Zip	State	State	First	Count
#htmleditformat( fseZipCode )#	#htmleditformat( company_state )#	#htmleditformat( stateAbbr )#	#lsnumberformat( firstLine )#	#lsnumberformat( lineCount )#

select i.affiliation_orgName, i.affiliation_subOrgName, count( distinct i.fseOperatorId ) operators from tbl_IMPORT_Operators i with (nolock) where nullif( i.affiliation_subOrgName, '' ) is not null and nullif( i.affiliation_orgName, '' ) is not null and nullif( fseAffiliationSubOperatorId, 0 ) is null and i.ownerId = and i.ownerType = and i.eai_batchId = group by i.affiliation_orgName, i.affiliation_subOrgName order by operators desc

The names below were not found as GPOs as CMCs. You can map the names below to existing operator records by using the "Roster Sub-Group Names" field in Operator Directory (M92) and reprocess this batch from the details tab in Batch Manager.

Mode = #_mode#
#summaryHTML# #matchingResults[x]# update tbl_OPR_ClientOperators set companyName = upper( companyName ), dbaName = upper( dbaName ), address = upper( address ), address2 = upper( address2 ), city = upper( city ), state = upper( state ), zipCode = upper( zipCode ) where ownerId = 14 and fsltablecode = 'BRO' and ( 1 = 2 or upper( coalesce( #_tc#, '' )) <> coalesce( #_tc#, '' ) COLLATE Latin1_General_CS_AS ) select fseOperatorId from tbl_IMPORT_Operators where eai_batchId = and ownerId = and ownerType = and eai_lineNumber >= and eai_lineNumber < ---> ---> select distinct fseAffiliationOperatorId, affiliation_orgName, affiliation_subOrgName from tbl_IMPORT_Operators with (nolock) where ownerId = and ownerType = and eai_batchId = and fseAffiliationOperatorId > 0 and fseAffiliationSubOperatorId is null and nullif( affiliation_subOrgName, '' ) is not null select distinct opr.operatorId from tbl_OPR_ClientOperators opr with (nolock) inner join tbl_IMPORT_Aliases a with (nolock) on a.ownerId = opr.ownerId and a.ownerType = opr.fsltablecode and a.objectType = 'operatorCompanyName' and a.objectId = opr.operatorId where a.ownerId = and a.ownerType = and a.alias = and opr.fsl_choId > 0 update tbl_IMPORT_Operators set fseAffiliationSubOperatorId = where ownerId = and ownerType = and eai_batchId = and fseAffiliationOperatorId = and affiliation_orgName = and affiliation_subOrgName = and fseAffiliationSubOperatorId is null select org.operatorId orgId, 'OPR' orgType, case when org.oprCompanyType = 'C' then 'CMC' when org.oprCompanytype = 'G' then 'GPO' else org.oprCompanyType end affiliationType, org.companyName orgName, p.operatorId partnerId, 'OPR' partnerType, p.companyName partnerName from tbl_IMPORT_Operators i with (nolock) inner join tbl_OPR_CLientOperators org with (nolock) on org.operatorId= i.fseAffiliationSubOperatorId and org.ownerId = i.ownerId and org.fsltablecode = i.ownerType and org.oprCompanyType in ( 'G', 'C' ) inner join tbl_OPR_ClientOperators p with (nolock) on p.operatorId= i.fseOperatorId and p.ownerId = i.ownerId and p.fsltablecode = i.ownerType where i.ownerId = and i.ownerType = and i.eai_batchId = and i.fseAffiliationOperatorId <> i.fseAffiliationSubOperatorId select affiliationId from tbl_CRM_Affiliations with (nolock) where ownerId = and ownerType = and affiliationType = and orgType ='OPR' and orgId = and partnerId = and partnertype = 'OPR' insert into tbl_CRM_Affiliations ( ownerId, ownerType, orgType, orgId, partnerType, partnerId, affiliationType, partnerAffiliateNbr, affiliationDate, affiliationStatus, sourceId, sourceType ) values ( , , , , , , , '', , 'A', , 'SYS' ) select oneFSEnabled from tblBrokers with (nolock) where oneFSEnabled = 'Y' and broker_id = union select oneFSEnabled from tblManufacturers with (nolock) where oneFSEnabled = 'Y' and mfr_id = update opr set opr.fsl_choIdStatus = 'A', opr.fsl_choIdBy = -1, opr.fsl_choIdDate = , opr.fsl_choId = m.fsl_choId, opr.fsl_choIdTrace = 'CHO:#_config.OPT_SOURCECHOID#:' + convert( varchar, m.operatorId ), opr.oprCompanyType = m.oprCompanyType, opr.independentYN = m.independentYN from tbl_CRM_Affiliations a with (nolock) inner join tbl_OPR_ClientOperators org with (nolock) on org.operatorId = a.orgId and 'OPR' = a.orgType and org.fsl_choId = and org.ownerId = a.ownerId and org.fsltablecode = a.ownerType inner join tbl_OPR_ClientOperators m with (nolock) on m.mfrCustNum = a.partnerAffiliateNbr and m.ownerId = org.fsl_choId and m.fsltablecode = 'CHO' and m.fsl_choId > 0 inner join tbl_CHO_Operators cho with (nolock) on cho.cho_id = m.fsl_choId and cho.cho_type in ( 'I', 'U' ) inner join tbl_OPR_ClientOperators opr on opr.ownerId = a.ownerId and opr.fsltablecode = a.ownerType and opr.operatorId = a.partnerId and 'OPR' = a.partnerType and coalesce( opr.fsl_choId, 0 ) = 0 where a.ownerType = and a.ownerId = and a.partnerType = 'OPR' and a.orgType = 'OPR' and nullif( a.partnerAffiliateNbr, '' ) is not null

#_r.recordCount# 1fs mappings propagated from CHOID:#_config.OPT_SOURCECHOID#

select fsl_choId, operatorId from tbl_OPR_ClientOperators with (nolock) where ownerId = and fsltablecode = and fsl_choId > 0 and fsl_choIdDate = and fsl_choIdTrace like 'CHO:#_config.OPT_SOURCECHOID#:%' select distinct fseOperatorId from tbl_IMPORT_Operators with (nolock) where ownerId = and ownerType = and eai_batchId= and fseOperatorId > 0 select batchId from tbl_EAI_inboundBatches where batchTime = and ownerId = and ownerType = and fileProcessor = '/mstrcfmod/OPR/batch/propagateMapping.cfm' ---> select company_name, company_address1, company_city, company_state, company_zip, company_id, coalesce( ter.name, 'NOT FOUND' ) as [FSL_Territory], min( eai_lineNumber ) first_line, count(*) as occurences from tbl_IMPORT_Operators i with (nolock) left outer join tbl_TER_Territories ter with (nolock) on ter.territoryId = i.fseTerritoryId and ter.ownerId = i.ownerId and ter.fsl_tablecode = i.ownerType where eai_batchId = and fseOperatorId is null group by company_id, company_name, company_address1, company_city, company_state, company_zip, ter.name order by first_line select company_segment, company_comment, company_url, company_units_ly, company_units_ty, company_units_ny, external_id, external_id_type, external_id_owner, territory_id, company_priority, company_phone, company_fax, company_address2, company_last_modify_date from tbl_IMPORT_Operators where eai_batchId = and fseOperatorId is null and company_id = order by company_name