select opr.ownerId, opr.fsltablecode, opr.operatorId, a.partnerAffiliateNbr, m.ownerId memberOwnerId, m.fSLTablecode memberFSLTablecode, m.mfrCustNum, m.fsl_choId memberfsl_choId, m.operatorId memberOperatorId from tbl_OPR_ClientOperators opr with (nolock) inner join tbl_CRM_Affiliations a with (nolock) on a.ownerId = opr.ownerId and a.ownerType = opr.fsltablecode and a.partnerId = opr.operatorId and a.partnerType = 'OPR' and a.orgType ='OPR' and nullif( a.partnerAffiliateNbr, '' ) is not null inner join tbL_OPR_CLientOperators org with (nolock) on org.ownerId = a.ownerId and org.fsltablecode = a.ownerType and org.operatorId = a.orgId inner join tbl_OPR_CLientOperators m with (nolock) on m.ownerId = org.fsl_choId and m.fsltablecode = 'CHO' and m.mfrCustNum = a.partnerAffiliateNbr and m.fsl_choId > 0 and m.oprCompanyType = '0' where nullif( opr.fsl_choId, 0 ) is null and opr.ownerId = and opr.fsltablecode = order by opr.operatorId select opr.ownerId, opr.fsltablecode, opr.operatorId, opr.companyName, dd.accountNumber, cdr.cdr_dstName, m.mfrCustNum memberNumber, m.fsl_choId memberFSL_choId, m.operatorId memberOperatorId, m.ownerId memberOwnerId, m.fsltablecode memberFSLTablecode, m.companyName memberCompanyName, dcdr.cdr_dstName memberDistributorName from tbl_OPR_ClientOperators opr with (nolock) -- distributor assigned customer number inner join tbl_OPR_DistributionDetails dd with (nolock) on dd.ownerId = opr.ownerId and dd.ownerType = opr.fsltablecode and dd.operatorId = opr.operatorId and nullif( dd.accountNumber, '' ) is not null -- the operator distributor inner join tbL_CDR_Distributors cdr with (nolock) on cdr.ownerId = dd.ownerId and cdr.fsl_tablecode = dd.ownerType and cdr.cdr_recordID = dd.cdr_recordId -- linked to 1fs inner join tbl_DST_Distributors dst on dst.dstId = cdr.fsl_dstId -- distributor distributor branch by 1fs id inner join tbl_CDR_Distributors dcdr with (nolock) on dcdr.ownerId = dst.dstParentCo and dcdr.fsl_tablecode = 'DST' and dcdr.cdr_dstCompanyType = 'B' and dcdr.fsl_mapPriority = 1 and dcdr.fsl_dstId = dst.dstId --- distributor operator with assigned number via primary distributor inner join tbl_OPR_CLientOperators m with (nolock) on m.ownerId = dcdr.ownerId and m.fsltablecode = dcdr.fsl_tablecode and m.distrib1 = dcdr.cdr_recordId and m.mfrCustNum = dd.accountNumber and m.fsl_choId > 0 and m.oprCompanyType = '0' where nullif( opr.fsl_choId, 0 ) is null and opr.ownerId = and opr.fsltablecode = -- ordered by operatorid to allow for skipping multiple mappings order by opr.operatorId update tbl_OPR_CLientOperators set fsl_choId = , fsl_choIdBy = 0, fsl_choIdDate = , fsl_choIdTrace = where ownerId = and fsltablecode = and operatorId =