select affiliation_orgType, affiliation_orgNbr, count(*) lineCount from tbl_IMPORT_Opportunities 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_Opportunities set fseAffiliationBrokerId = , recordSource = , fseOpportunityStatus = 'A' where ownerId = and ownerType = and eai_batchId = update i set i.fsePartnerId = a.partnerId, i.fsePartnerType = a.affiliationType from tbl_IMPORT_Opportunities i inner join tbl_CRM_Affiliations a on a.ownerId = i.ownerId and a.ownerType = i.ownerType and a.affiliationType = i.company_type and a.orgId = i.fseAffiliationBrokerId and a.orgType = i.affiliation_orgType and a.partnerType = 'OPR' and a.partnerAffiliateNbr = i.company_id 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 = update i set i.fsePartnerContactId = a.partnerId from tbl_IMPORT_Opportunities i inner join tbl_CRM_Affiliations a on a.ownerId = i.ownerId and a.ownerType = i.ownerType and a.affiliationType = i.company_type and a.orgId = i.fseAffiliationBrokerId and a.orgType = i.affiliation_orgType and a.partnerType = 'OPRC' and a.partnerAffiliateNbr = i.contact_id 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.fsePartnerId and mem.orgType = i.fsePartnerType where i.eai_batchId = and i.ownerId = and i.ownerType = and i.fsePartnerId is not null update i set i.fseUpdateUserId = m.fspro_userId from tbl_IMPORT_Opportunities i inner join tbl_FSpro_members m on m.ownerId = fseAffiliationBrokerId and m.fsl_tablecode = 'BRO' and m.staffMember = 1 and m.email = i.update_user_email where i.eai_batchId = and i.ownerId = and i.ownerType = update i set i.fseUpdateUserId = m.fspro_userId from tbl_IMPORT_Opportunities i inner join tbl_FSpro_members m on m.ownerId = fseAffiliationBrokerId and m.fsl_tablecode = 'BRO' and m.staffMember = 0 and m.email = i.update_user_email where i.eai_batchId = and i.ownerId = and i.ownerType = and i.fseUpdateUserId is null update i set i.fseCreateUserId = m.fspro_userId from tbl_IMPORT_Opportunities i inner join tbl_FSpro_members m on m.ownerId = fseAffiliationBrokerId and m.fsl_tablecode = 'BRO' and m.staffMember = 1 and m.email = i.create_user_email where i.eai_batchId = and i.ownerId = and i.ownerType = update i set i.fseCreateUserId = m.fspro_userId from tbl_IMPORT_Opportunities i inner join tbl_FSpro_members m on m.ownerId = fseAffiliationBrokerId and m.fsl_tablecode = 'BRO' and m.staffMember = 0 and m.email = i.create_user_email where i.eai_batchId = and i.ownerId = and i.ownerType = and i.fseCreateUserId is null update i set i.fseStageId = s.stageId from tbl_IMPORT_Opportunities i inner join tbl_SPL_Stages s on s.ownerId = i.ownerId and s.ownerType = i.ownerType and s.stateCode = substring( i.status_code, 1, 1 ) and s.statusCode = substring( i.status_code, 2, 1 ) where i.eai_batchId = and i.ownerId = and i.ownerType = and i.status_code <> 'OA' update i set i.fseStageId = s.stageId from tbl_IMPORT_Opportunities i inner join tbl_SPL_Stages s on s.ownerId = i.ownerId and s.ownerType = i.ownerType and s.stateCode = substring( i.status_code, 1, 1 ) and s.statusCode = substring( i.status_code, 2, 1 ) and s.likelihoodPercent = i.status_likelihood_percent where i.eai_batchId = and i.ownerId = and i.ownerType = select distinct status_code, status_likelihood_percent from tbl_IMPORT_Opportunities i where i.eai_batchId = and i.ownerId = and i.ownerType = and i.fseStageId is null select stageId from tbl_SPL_Stages s where s.stateCode = 'O' and s.statusCode = 'I' and s.ownerId = and s.ownerType = order by s.sequence select s.stageId from tbl_SPL_Stages s where s.stateCode = and s.statusCode = and s.likelihoodPercent <= and s.ownerId = and s.ownerType = order by s.likelihoodPercent desc update i set i.fseStageId = from tbl_IMPORT_Opportunities i where i.eai_batchId = and i.ownerId = and i.ownerType = and i.fseStageId is null and i.status_code = and i.status_likelihood_percent = select distinct i.campaign_year, i.campaign_period from tbl_IMPORT_Opportunities i where i.eai_batchId = and i.ownerId = and i.ownerType = select campaignId from tbl_CRM_Campaigns where ownerId = and ownerType = and timeframeYear = and timeframePeriod = order by sortRank update i set i.fseCampaignId = from tbl_IMPORT_Opportunities i where i.eai_batchId = and i.ownerId = and i.ownerType = and i.campaign_year = and i.campaign_period = update i set i.fseOpportunityId = spl.opportunityId, i.fseCurrentStageId = spl.stageId, i.fseCurrentOpportunityDescription = spl.opportunityDescription, i.fseOpportunityStatus = 'U' from tbl_IMPORT_Opportunities i inner join tbl_SPL_Opportunities spl on spl.ownerId = i.ownerId and spl.ownerType = i.ownerType and spl.xrefData = i.affiliation_orgType + ':' + i.affiliation_orgNbr + ':' + i.opportunity_id and spl.partnerId = i.fsePartnerId and spl.partnerType = i.fsePartnerType where i.eai_batchId = and i.ownerId = and i.ownerType = insert into tbl_SPL_Opportunities ( ownerId, ownerType, typeCode, partnerId, partnerType, ContactID, cdrDistributorId, createUserId, createDate, updateUserId, updateDate, opportunityName, campaignId, stageId, opportunityDescription, QuantityAmt, QuantityUnit, DurationAmt, DurationUnit, UnitPrice, TradeSpendAmt, FinMarginAmt, incomeAmt, AvailableDate, LaunchDate, FirstActiveDate, xrefData, recordSource ) select ownerId, ownerType, 'NEW', fsePartnerId, fsePartnerType, fsePartnerContactId, fseDistributorId, fseCreateUserId, create_date, fseUpdateUserId, update_date, opportunity_name, fseCampaignId, fseStageId, status_comment, quantity_amt, quantity_unit, duration_amt, duration_unit, unit_price, trade_spend_amt, fin_margin_amt, income_amt, available_date, launch_date, first_active_date, affiliation_orgType + ':' + affiliation_orgNbr + ':' + opportunity_id, recordSource from tbl_IMPORT_Opportunities i where i.eai_batchId = and i.ownerId = and i.ownerType = and i.fseOpportunityStatus = 'A' and i.fseOpportunityId is null and i.fsePartnerId is not null and i.fseStageId is not null update i set i.fseOpportunityId = spl.opportunityId from tbl_IMPORT_Opportunities i inner join tbl_SPL_Opportunities spl on spl.ownerId = i.ownerId and spl.ownerType = i.ownerType and spl.xrefData = i.affiliation_orgType + ':' + i.affiliation_orgNbr + ':' + i.opportunity_id and spl.partnerId = i.fsePartnerId and spl.partnerType = i.fsePartnerType where i.eai_batchId = and i.ownerId = and i.ownerType = and i.fseOpportunityStatus = 'A' insert into tbl_SPL_Opportunity_StageChanges ( opportunityId, previousStageId, newStageId, changeDateTime, fspro_userId, ipaddress, event, message ) select fseOpportunityId opportunityId, null previousStageId, fseStageId newStageId, create_date changeDateTime, fseCreateUserId fspro_userid, '0.0.0.0' ipaddress, 'created' event, status_comment message from tbL_IMPORT_Opportunities where eai_batchId = and ownerId = and ownerType = and fseOpportunityStatus = 'A' and fseOpportunityId is not null update spl set spl.contactId = i.fsePartnerContactId, spl.updateUserId = i.fseUpdateUserId, spl.updateDate = i.update_date, spl.opportunityName = i.opportunity_name, spl.stageId = i.fseStageId, spl.campaignId = i.fseCampaignId, spl.opportunityDescription = i.status_comment, spl.quantityAmt = i.quantity_amt, spl.quantityUnit = i.quantity_unit, spl.durationAmt = i.duration_amt, spl.unitPrice = i.unit_price, spl.tradeSpendAmt = i.trade_spend_amt, spl.finMarginAmt = i.fin_margin_amt, spl.incomeAmt = i.income_Amt, spl.availableDate = i.available_Date, spl.launchDate = i.launch_Date, spl.firstActiveDate = i.first_active_Date from tbl_SPL_Opportunities spl inner join tbl_IMPORT_Opportunities i on i.ownerId = spl.ownerId and i.ownerType = spl.ownerType and i.fseOpportunityId = spl.opportunityId where i.eai_batchId = and i.ownerId = and i.ownerType = and i.fseOpportunityStatus = 'U' insert into tbl_SPL_Opportunity_StageChanges ( opportunityId, previousStageId, newStageId, changeDateTime, fspro_userId, ipaddress, event, message ) select fseOpportunityId opportunityId, null previousStageId, fseStageId newStageId, update_date changeDateTime, fseUpdateUserId fspro_userid, '0.0.0.0' ipaddress, 'changed' event, status_comment message from tbL_IMPORT_Opportunities where eai_batchId = and ownerId = and ownerType = and fseOpportunityStatus = 'U' and ( fseStageId <> fseCurrentStageId or status_comment <> fseCurrentOpportunityDescription ) select i.fseOpportunityId, i.subject_details from tbl_IMPORT_Opportunities i where i.eai_batchId = and i.ownerId = and i.ownerType = and i.subject_details is not null and i.fseOpportunityId is not null select distinct oa.attrId from tbl_ORG_Attributes oa inner join tbl_ORG_AttributeLinks oal on oal.child_attrId = oa.attrId and oal.child_attrTypeId = oa.attrTypeId and oal.mstr_attrTypeId = 28 inner join tbl_CMM_Filters f on f.filterId = oal.mstr_attrId and f.filterTypeId = 1 inner join tbl_ORG_AttributeLinks oal2 on oal2.mstr_attrId = oa.attrId and oal2.mstr_attrTypeId = oa.attrTypeId and oal2.Child_attrTypeId = 59 inner join tbl_PRD_Skus s on s.prodId = oal2.child_attrId and s.ownerId = oa.ownerId and s.fsl_tablecode = oa.fsl_tablecode where f.ownerId = and f.fsl_tablecode = and sku in ( ) update tbl_SPL_Opportunities set subjectType = 'GTM', subjectId = where opportunityId = and ownerId = and ownerType = update tbl_SPL_Opportunities set subjectType = null, subjectId = null where opportunityId = and ownerId = and ownerType = delete from tbl_SPL_OpportunitySubjectDetails where opportunityId = and ownerId = and ownerType = insert into tbl_SPL_OpportunitySubjectDetails ( ownerId, ownerType, opportunityId, subjectDetailType, subjectDetailId ) select distinct ownerId, fsl_tablecode, #fseOpportunityId#, 'SKU', skuId from tbl_PRD_SKUs where sku in ( ) and ownerId = and fsl_tablecode = 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 = 'U' 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 = 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_nbr partnerAffiliateNbr, eai_batchId sourceId, 'SYS' sourceType from tbl_IMPORT_Operators 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_nbr 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# --->