select fspro_userId from tbl_FSPRO_Members with (nolock) where ownerId = and fsl_tablecode = and email = insert into tbl_CDR_DstCodes( ownerId, ownerType, cdr_recordId, cdr_dstCode, cdr_dstcode_type, cdr_dstcode_comments ) select cdr.ownerId, cdr.fsl_tablecode ownerType, cdr.cdr_recordId, cdr.cdr_dstCode, 'M' cdr_dstcode_type, 'Primary Account Number' cdr_dstcode_comments from tbl_CDR_Distributors cdr left outer join tbl_CDR_Dstcodes dst on dst.cdr_recordId = cdr.cdr_recordId and dst.cdr_dstcode = cdr.cdr_dstcode and dst.ownerId = cdr.ownerId and dst.ownerType = cdr.fsl_tablecode where cdr.FSL_TableCode = and dst.cdr_recordId is null and nullif( cdr.cdr_dstcode, '' ) is not null and cdr.OwnerID = delete from tbl_CDR_Distributors where ownerId = and fsl_tablecode = delete c from tbl_CDR_DstCodes c where not exists ( select * from tbl_CDR_Distributors d where d.ownerId = c.OwnerID and d.FSL_TableCode = c.ownerType and d.cdr_recordid = c.cdr_recordId ) and c.ownerId = and c.ownerType = UPDATE I SET I.FseInclude = 'Y', I.fse_partnerId = NULL, I.fse_contactId = NULL, I.fseTerritoryId = NULL, I.fseTerritoryIdStatus = NULL, I.fseCDRTerritoryId = null, I.FseBSRId = NULL, I.fseCDRStatus = '?', I.territory_name_id = RTRIM(I.territory_name_id) FROM tbl_IMPORT_Distributors I WHERE I.ownerId = AND I.ownerType = AND I.eai_batchId = update i set I.FseInclude = 'N' from tbl_IMPORT_Distributors i where i.ownerId = and i.ownerType = and i.eai_batchId = and coalesce(i.channel,'') = '' update i set I.FseInclude = 'N' from tbl_IMPORT_Distributors i inner join tbl_IMPORT_Exclusions x on x.ownerId = i.ownerId and x.ownerType = i.ownerType and x.exclusionType = 'CDR' and x.exclusionId = i.company_id where i.ownerId = and i.ownerType = and i.eai_batchId = select dc.cdr_dstcode, COUNT(*) instanceCount from tbl_CDR_DstCodes dc inner join tbl_CDR_Distributors cdr on cdr.cdr_recordId = dc.cdr_recordId and cdr.ownerId = dc.ownerid where dc.ownerId = and dc.ownerType = and nullif( dc.cdr_dstCode, '' ) is not null group by dc.cdr_dstcode having COUNT(*) > 1 update tbl_IMPORT_Distributors set fseInclude = 'N' where eai_batchId = and company_id = and ownerId = and ownerType = ---> select distinct staging.territory_name_id as territoryCode from tbl_IMPORT_Distributors staging with (nolock) where nullif( staging.territory_name_id, '' ) is not null and staging.ownerId = and staging.ownertype = and staging.eai_batchid = and staging.fseInclude = 'Y' and staging.fseTerritoryId is null select territoryId from tbl_TER_Territories with (nolock) where ownerId = and fsl_tablecode = and internalSalesId = order by territoryId select territoryId from tbl_TER_Territories with (nolock) where ownerId = and fsl_tablecode = and name = order by territoryId update staging set staging.fseTerritoryId = ,staging.fseTerritoryIdStatus = from tbl_IMPORT_Distributors staging where staging.ownerId = and staging.ownertype = and staging.eai_batchid = and staging.fseInclude = 'Y' and staging.territory_name_id = and staging.fseTerritoryId is null select eai_importid,redistribution_account_for,company_name from tbl_IMPORT_Distributors where ownerId = and ownertype = and eai_batchid = and company_type = 'SPE' and special_account_type = 'RDA' and FseInclude = 'Y' update tbl_IMPORT_Distributors set FseInclude = 'N' where eai_batchId = and ownerId = and ownerType = and eai_importid = select cdr_recordid from tbl_CDR_Distributors where cdr_dstname like and ownerId = and fsl_tablecode = and cdr_dstcompanytype = 'R' update tbl_IMPORT_Distributors set fseRedistAcctFor = , FseInclude = 'Y' where eai_batchId = and ownerId = and ownerType = and eai_importid = update tbl_IMPORT_Distributors set FseInclude = 'N' where eai_batchId = and ownerId = and ownerType = and eai_importid = update tbl_IMPORT_Distributors set recordSource = where ownerId = and ownertype = and eai_batchid = AND FseInclude = 'Y' update tbl_IMPORT_Distributors set company_type = 'CDR' where ownerId = and ownertype = and eai_batchid = and company_type is null AND FseInclude = 'Y' update i set i.fse_partnerId = cdr.cdr_recordId, i.fseCDRTerritoryId = cdr.cdr_territoryId, i.fseCDRDstCompanyType = COALESCE(cdr.cdr_dstCompanyType,'?'), i.fseCDRStatus = '?' from tbl_IMPORT_Distributors i LEFT OUTER join tbl_CDR_DstCodes dst on dst.ownerId = i.ownerId and dst.ownerType = i.ownerType and dst.cdr_dstCode = i.company_id LEFT OUTER JOIN tbl_cdr_distributors cdr on cdr.ownerId = i.ownerId and cdr.fsl_tablecode = i.ownerType and cdr.cdr_recordid = dst.cdr_recordid where i.ownerId = and i.ownertype = and i.eai_batchid = and nullif( i.company_id, '' ) is not null AND I.FseInclude = 'Y' update i set i.fse_partnerId = cdr.cdr_recordId, i.fseCDRTerritoryId = cdr.cdr_territoryId, i.fseCDRDstCompanyType = COALESCE(cdr.cdr_dstCompanyType,'?'), i.fseCDRStatus = '?' from tbl_IMPORT_Distributors i LEFT OUTER JOIN tbl_cdr_distributors cdr on cdr.ownerId = i.ownerId and cdr.fsl_tablecode = i.ownerType and cdr.cdr_dstName = i.company_name where i.ownerId = and i.ownertype = and i.eai_batchid = and nullif( i.company_id, '' ) is null AND I.FseInclude = 'Y' and i.fse_partnerId is null select distinct sales_person_name from tbl_IMPORT_Distributors where ownerId = and ownertype = and eai_batchid = and sales_person_name is not null and FseInclude = 'Y' select fspro_userid from tbl_FSPro_members where email = and ownerId = and fsl_tablecode = and staffMember = 1 select fspro_userid from tbl_FSPro_members where firstname = and lastname = and ownerId = and fsl_tablecode = and staffMember = 1 update tbl_IMPORT_Distributors set fseSalesPersonId = where ownerId = and ownertype = and eai_batchid = and sales_person_name = AND FseInclude = 'Y' select distinct fse_partnerId, ownerId, ownerType, recordSource, company_id, company_name, company_type, territory_name_id, company_phone, company_fax, company_address1, company_address2, company_city, company_state, company_zip, RTRIM(coalesce( nullif( company_country, '' ), 'USA' )) company_country, company_last_modify_date, focus_company_indicator, fseTerritoryId, fseTerritoryIdStatus, fseCDRTerritoryId, fseCdrDstCompanyType, eai_lineNumber, coalesce(redistribution_account_for, '0') redistribution_account_for, coalesce(special_account_type, '0') special_account_type, fseRedistAcctFor, rec_status from tbl_IMPORT_Distributors i where i.eai_batchId = and i.ownerId = and i.ownerType = AND I.FseInclude = 'Y' and nullif( fseTerritoryId, 0 ) is not null order by company_id, eai_lineNumber insert into tbl_CDR_Distributors ( ownerId, fsl_tablecode, cdr_dstcode, cdr_dstname, cdr_dstaddress1, cdr_dstaddress2, cdr_dstcity, cdr_dststate, cdr_dstzip, cdr_dstcountryId, recordSource, createDate, lastUpdated, setMethod, cdr_dstPhone, cdr_dstFax, cdr_territoryId, fseTerritoryIdStatus, cdr_dstcompanytype, cdr_redistAcctFor, cdr_specialAcctType, crmactive ) values ( , , , , , , , , , , , { fn now() }, { fn now() }, 1, , , , , , , , ) INSERT INTO tbl_CDR_DstCodes (OwnerId, OwnerType, cdr_recordId, cdr_dstcode, cdr_dstcode_type, cdr_dstcode_comments) VALUES (, , , , 'M', 'Primary Account Number' ) update tbl_IMPORT_Distributors set fse_partnerId = , fseCDRStatus = 'A' where coalesce( company_id, '' ) = company_name = and eai_batchId = and ownerId = and ownerType = and fseTerritoryId = -- #company_country# = #_countryId# UPDATE CDR SET #_arrayColumn# = , lastUpdated = { fn now() }, lastUpdatedBy = null FROM tbl_CDR_Distributors CDR WHERE CDR.ownerId = AND CDR.fsl_tablecode = and cdr.cdr_recordId = update tbl_IMPORT_Distributors set fseCDRStatus = 'U' where company_id = company_name = and eai_batchId = and ownerId = and ownerType = and fseTerritoryId = UPDATE CDR SET cdr_territoryId = , fseTerritoryIdStatus = FROM tbl_CDR_Distributors CDR WHERE CDR.ownerId = AND CDR.fsl_tablecode = and cdr.cdr_recordId = select eai_importId, ownerId, ownerType, fse_partnerId, fse_contactId, recordSource, 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 from tbl_IMPORT_Distributors ci where ci.ownerId = and ci.ownerType = and ci.eai_batchId = and ci.fse_partnerId is not null and ci.fse_contactId is null AND CI.FseInclude = 'Y' AND NOT (Contact_Last_Name + Contact_First_Name IS NULL OR Contact_Last_Name + Contact_First_Name = '') AND NOT EXISTS ( SELECT * FROM tbl_FSPro_Members M WHERE M.OwnerID = CI.OwnerId AND M.FSL_TableCode = CI.ownerType AND OrgType = 'CDRD' AND M.LastName + M.FirstName = Contact_Last_Name + Contact_First_Name ) select jobFunctionId from qmd_partnerContactJobFunctions where jobFunctionName = set nocount on insert into tbl_FSPro_members ( firstName, lastName, email, phone, phoneExt, fax, cellPhone, officeAddrSame, createDate, lastUpdated, originalSource, orgId, orgType, ownerId, fsl_tablecode, title, cajobfunction, staffMember) values( , , , , , , , 1, { fn now() }, , , , , , , , , ) select contactId = @@identity set nocount off update tbl_CDR_Distributors set primaryContactId = where cdr_recordId = update tbl_IMPORT_Distributors set fse_contactId = where eai_importId = AND FseInclude = 'Y' update cdr set cdr.cdr_territoryId = t.territoryId from tbl_IMPORT_Distributors i inner join tbl_CDR_Distributors cdr on cdr.cdr_recordId = i.fse_partnerId and cdr.ownerId = i.ownerId and cdr.fsl_tablecode = i.ownerType left outer join tbl_TER_Territories mappedTerritory on mappedTerritory.territoryId = cdr.cdr_territoryId and mappedTerritory.ownerId = cdr.ownerId and mappedTerritory.fsl_tablecode = cdr.fsl_tablecode inner join tbl_TER_Territories t on t.ownerId = i.ownerId and t.fsl_tablecode = i.ownerType and t.internalSalesId = i.territory_name_id where i.ownerId = and i.ownertype = and i.eai_batchid = and mappedTerritory.territoryId is null AND I.FseInclude = 'Y' update cdr set cdr.cdr_territoryId = t.territoryId from tbl_IMPORT_Distributors i inner join tbl_CDR_Distributors cdr on cdr.cdr_recordId = i.fse_partnerId and cdr.ownerId = i.ownerId and cdr.fsl_tablecode = i.ownerType left outer join tbl_TER_Territories mappedTerritory on mappedTerritory.territoryId = cdr.cdr_territoryId and mappedTerritory.ownerId = cdr.ownerId and mappedTerritory.fsl_tablecode = cdr.fsl_tablecode inner join tbl_TER_Territories t on t.ownerId = i.ownerId and t.fsl_tablecode = i.ownerType and t.name = i.territory_name_id where i.ownerId = and i.ownertype = and i.eai_batchid = and mappedTerritory.territoryId is null AND I.FseInclude = 'Y' UPDATE CDR SET CDR.cdr_territoryId = (SELECT ISNULL(MIN(cdr_territoryid),0) As MinTerritoryId FROM tbl_CDR_Distributors X WHERE X.ownerId = I.ownerId AND X.FSL_TableCode = I.ownerType AND X.mfr_bsr_id = M.FSPro_UserId ) FROM tbl_IMPORT_Distributors I INNER JOIN tbl_CDR_Distributors CDR ON CDR.cdr_recordId = I.fse_partnerId AND CDR.ownerId = I.ownerId AND CDR.fsl_tablecode = I.ownerType INNER JOIN tbl_FSPRO_Members M ON M.OwnerId = I.OwnerId AND M.FSL_TableCode = I.ownertype AND M.FirstName + ' ' + M.LastName = territory_name_id WHERE I.ownerId = AND I.ownertype = AND I.eai_batchid = AND (CDR.cdr_territoryId IS NULL OR CDR.cdr_territoryId = 0) AND I.FseInclude = 'Y' UPDATE CDR SET CDR.cdr_territoryId = ISNULL(TM.TerritoryId,0) FROM tbl_IMPORT_Distributors I INNER JOIN tbl_CDR_Distributors CDR ON CDR.cdr_recordId = I.fse_partnerId AND CDR.ownerId = I.ownerId AND CDR.fsl_tablecode = I.ownerType INNER JOIN tbl_FSPRO_Members M ON M.OwnerId = I.OwnerId AND M.FSL_TableCode = I.ownertype AND M.FirstName + ' ' + M.LastName = territory_name_id INNER JOIN tbl_TER_TeamMembers TM ON TM.Ownerid = M.OwnerId AND TM.OwnerType = M.FSL_TableCode AND TM.TeamMemberUserId = M.FSPro_UserID WHERE I.ownerId = AND I.ownertype = AND I.eai_batchid = AND (CDR.cdr_territoryId IS NULL OR CDR.cdr_territoryId = 0) AND I.FseInclude = 'Y' UPDATE CDR SET CDR.cdr_territoryId = ISNULL(T.TerritoryId,0) FROM tbl_IMPORT_Distributors I INNER JOIN tbl_CDR_Distributors CDR ON CDR.cdr_recordId = I.fse_partnerId AND CDR.ownerId = I.ownerId AND CDR.fsl_tablecode = I.ownerType INNER JOIN tbl_TER_Territories T ON T.ownerId = I.ownerId AND T.fsl_tablecode = I.ownerType AND T.name = 'UNASSIGNED' WHERE I.ownerId = AND I.ownertype = AND I.eai_batchid = AND (CDR.cdr_territoryId IS NULL OR CDR.cdr_territoryId = 0) AND I.FseInclude = 'Y' ---> UPDATE CDR SET CDR.cdr_territoryId = ISNULL(I.fseTerritoryId,0), CDR.fseTerritoryIdStatus = I.fseTerritoryIdStatus FROM tbl_IMPORT_Distributors I INNER JOIN tbl_CDR_Distributors CDR ON CDR.cdr_recordId = I.fse_partnerId AND CDR.ownerId = I.ownerId AND CDR.fsl_tablecode = I.ownerType WHERE I.ownerId = AND I.ownertype = AND I.eai_batchid = AND (CDR.cdr_territoryId IS NULL OR CDR.cdr_territoryId = 0) AND I.FseInclude = 'Y' UPDATE CDR SET CDR.cdr_territoryId = ISNULL(I.fseTerritoryId,0), CDR.fseTerritoryIdStatus = I.fseTerritoryIdStatus FROM tbl_IMPORT_Distributors I INNER JOIN tbl_CDR_Distributors CDR ON CDR.cdr_recordId = I.fse_partnerId AND CDR.ownerId = I.ownerId AND CDR.fsl_tablecode = I.ownerType INNER JOIN tbl_TER_Territories T ON T.ownerId = I.ownerId AND T.fsl_tablecode = I.ownerType AND T.internalSalesId = 'Unassigned' WHERE I.ownerId = AND I.ownertype = AND I.eai_batchid = AND I.FseInclude = 'Y' SET NOCOUNT ON UPDATE CDR SET cdr_dstname = I.Company_Name, cdr_dstaddress1 = I.company_address1, cdr_dstaddress2 = I.company_address2, cdr_dstcity = I.company_city, cdr_dststate = I.company_state, cdr_dstzip = I.company_zip, lastUpdated = { fn now() }, focusAccount = ISNULL(focus_company_indicator,'N') FROM tbl_IMPORT_Distributors I INNER JOIN tbl_CDR_Distributors CDR ON CDR.cdr_recordId = I.fse_partnerId AND CDR.ownerId = I.ownerId AND CDR.fsl_tablecode = I.ownerType WHERE I.ownerId = AND I.ownertype = AND I.eai_batchid = AND I.FseInclude = 'Y' SELECT changedRows = @@ROWCOUNT SET NOCOUNT OFF SET NOCOUNT ON UPDATE CDR SET cdr.cdr_territoryid = I.fseTerritoryId FROM tbl_IMPORT_Distributors I INNER JOIN tbl_CDR_Distributors CDR ON CDR.cdr_recordId = I.fse_partnerId AND CDR.ownerId = I.ownerId AND CDR.fsl_tablecode = I.ownerType WHERE I.ownerId = AND I.ownertype = AND I.eai_batchid = AND I.FseInclude = 'Y' AND I.fseTerritoryIdStatus = 'I' SELECT changedRows = @@ROWCOUNT SET NOCOUNT OFF ---> UPDATE tbl_IMPORT_Distributors SET FseBSRId = fseSalesPersonId WHERE ownerId = AND ownertype = AND eai_batchid = AND FseInclude = 'Y' AND fseSalesPersonId is not null AND fseSalesPersonId > 0 SET NOCOUNT ON UPDATE I SET I.FseBSRId = (SELECT Min(FSPro_UserId) FROM tbl_FSPRO_Members M WHERE M.OwnerId = I.OwnerId AND M.FSL_TableCode = I.ownertype AND M.FirstName + ' ' + M.LastName = I.territory_name_id and M.staffMember = 1 ) FROM tbl_IMPORT_Distributors I INNER JOIN tbl_CDR_Distributors CDR ON CDR.cdr_recordId = I.fse_partnerId AND CDR.ownerId = I.ownerId AND CDR.fsl_tablecode = I.ownerType AND CDR.cdr_dstcompanytype = I.fseCDRDstCompanyType WHERE I.ownerId = AND I.ownertype = AND I.eai_batchid = AND I.FseInclude = 'Y' and i.fseTerritoryIdStatus in ( 'M', 'T' ) AND I.fseSalesPersonId is not null AND I.fseSalesPersonId > 0 AND I.FseBSRId is not null AND I.FseBSRId > 0 SELECT changedRows = @@ROWCOUNT SET NOCOUNT OFF SET NOCOUNT ON UPDATE CDR SET CDR.mfr_bsr_id = I.FseBSRId FROM tbl_IMPORT_Distributors I INNER JOIN tbl_CDR_Distributors CDR ON CDR.cdr_recordId = I.fse_partnerId AND CDR.ownerId = I.ownerId AND CDR.fsl_tablecode = I.ownerType AND CDR.cdr_dstcompanytype = I.fseCDRDstCompanyType WHERE I.ownerId = AND I.ownertype = AND I.eai_batchid = AND (CDR.mfr_bsr_id IS NULL OR CDR.mfr_bsr_id = 0) AND I.FseBSRId IS NOT NULL AND I.FseBSRId > 0 AND I.FseInclude = 'Y' SELECT changedRows = @@ROWCOUNT SET NOCOUNT OFF select cdr.cdr_recordId, cdr.cdr_dstName from tbl_CDR_Distributors cdr with (nolock) inner join tbl_Fspro_members m on m.fspro_userId = cdr.mfr_bsr_id and m.ownerId > 0 where cdr.ownerId = and cdr.fsl_tablecode = and m.staffMember = 0 update tbl_CDR_Distributors set mfr_bsr_id = NULL where cdr_recordId in ( select cdr.cdr_recordId from tbl_CDR_Distributors cdr with (nolock) inner join tbl_Fspro_members m on m.fspro_userId = cdr.mfr_bsr_id and m.ownerId > 0 where cdr.ownerId = and cdr.fsl_tablecode = and m.staffMember = 0 ) SET NOCOUNT ON SELECT ExclusionCnt = COUNT(*) FROM tbl_IMPORT_Distributors I WHERE I.eai_batchId = AND I.ownerId = AND I.ownerType = AND FseInclude = 'N' SET NOCOUNT OFF select eai_lineNumber, company_id, company_name, company_address1, company_address2, company_city, company_state, company_zip from tbl_IMPORT_Distributors where eai_batchId = and ownerId = and ownerType = and company_id in ( ) and fseInclude = 'N' order by eai_lineNumber

#lsnumberformat( qmd_skippedDups.recordcount )# skipped because **CONFLICTS** exist in the Library
Line	Customer ID	Distributor
#eai_lineNumber#	#htmleditformat( company_id )#	#htmleditformat( company_name )# #htmleditformat( company_address1 )# #htmleditformat( company_address2 )# #htmleditformat( company_city )#, #htmleditformat( company_state )# #htmleditformat( company_zip )#

Source File:	#_recordSource#
Total number of Records in File:	#Evaluate(request.qcf_disprecord.lineCount -1)#
New Records Inserted:	#_importCount#
Existing Records Updated:	#_updateCount#
Existing Records Moved:	#_moveCount#
Excluded Record Count:	#qmd_Exclusions.ExclusionCnt#
Rep Assignments Set:	#qmd_mapMfr_BSR_Id.ChangedRows#

#summaryHTML#