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 right( '0000000000000000000000000' + m.mfrCustNum, 25 ) = right( '0000000000000000000000000' + a.partnerAffiliateNbr, 25 ) 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 with operators as ( select dd.ownerId, dd.ownerType, dd.operatorId, dd.accountNumber, dst.dstId, dst.dstParentCo from tbl_OPR_CLientOperators opr with (nolock) 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 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 inner join tbl_DST_Distributors dst with (nolock) on dst.dstId = cdr.fsl_dstId where opr.ownerId = and opr.fsltablecode = and coalesce( opr.fsl_choId, 0 ) = 0 ) select opr.ownerId, opr.ownerType, opr.operatorId, dopr.ownerId memberOwnerId, dopr.fsltablecode memberFSLTablecode, dopr.operatorId memberOperatorId, dopr.fsl_choId memeberfsl_choId from operators opr inner join tbl_OPR_ClientOperators dopr with (nolock) on opr.ownerId = opr.dstParentCo and dopr.fsltablecode = 'DST' and dopr.mfrCustNum = opr.accountNumber inner join tbl_CDR_Distributors dcdr with (nolock) on dcdr.ownerId = dopr.ownerId and dcdr.fsl_tablecode = dopr.fsltablecode and dcdr.cdr_recordid = dopr.distrib1 inner join tbl_CHO_Operators cho with (nolock) on cho.cho_id = dopr.fsl_choId where dcdr.fsl_dstId = opr.dstId and dopr.oprCompanyType = '0' and dcdr.cdr_dstCompanyType = 'B' and dcdr.fsl_mapPriority = 1 order by opr.operatorId update tbl_OPR_CLientOperators set fsl_choId = , fsl_choIdBy = 0, fsl_choIdDate = , fsl_choIdTrace = where ownerId = and fsltablecode = and operatorId =

Not Havesting Location Data

 select c.claimId, case when c.partnerType = 'CDR' then cdr.cdr_dstName else opr.companyName end claimant, format( c.claimDate, 'MM/dd/yyyy' ) claimDate, c.claimRef, c.claimInvoice, c.claimAmount, c.readyToClear, c.readyToClearMessage, c.reconciledAmount from tbl_TPM_Claims c with (nolock) left outer join tbl_CDR_Distributors cdr with (nolock) on cdr.cdr_recordId = c.partnerId and cdr.ownerId = c.ownerId and cdr.fsl_tablecode = c.ownerType and 'CDR' = c.partnerType left outer join tbl_OPR_ClientOperators opr with (nolock) on opr.operatorId = c.partnerId and opr.ownerId = c.ownerId and opr.fsltablecode = c.ownerType and 'OPR' = c.partnerType where c.claimType in ( 'B', 'D' ) and c.partnerType in ('CDR', 'OPR' ) and c.ownerId = and c.ownerType = and c.claimStatus <> 'HOLD' and c.readyToClear in ( 'N' ) and c.reconciledAmount is null and ( ( c.claimStatus = 'APPROVED' and exists ( select * from tbl_TPM_ClaimContracts cc where cc.claimid = c.claimId and cc.ownerId = c.ownerId and cc.ownerType = c.ownerType and ( cc.status = 'APPROVED_CLAIMAMT' or ( cc.status = 'APPROVED_VERIFIEDAMT' ) )) ) or ( c.claimStatus = 'DENIED' ) ) and c.mappingCompleted is not null order by c.claimDate select readyToClear, readyToClearMessage, reconciledAmount from tbl_TPM_Claims with (nolock) where ownerId = and ownerType = and claimId =