with operators as ( select distinct pod.operatorId from tbl_TPM_ProofOfDelivery pod with (nolock) inner join tbl_OPR_CLientOperators opr2 with (nolock) on opr2.operatorId = pod.operatorId and opr2.fsltablecode = pod.ownerType and opr2.ownerId = pod.ownerId inner join tbl_CHO_Operators cho with (nolock) on cho.cho_id = opr2.fsl_choId and cho.cho_type not in ( 'I','U') inner join tbl_TPM_Claims c on c.ownerId = pod.ownerId and c.ownerType = pod.ownerType and c.claimId = pod.claimId inner join tbl_OPR_CLientOperators claimant with (nolock) on claimant.ownerId = c.ownerId and claimant.fsltablecode = c.ownerType and claimant.operatorId = c.partnerId and 'OPR' = c.partnerType where pod.ownerId = and pod.ownerType = and c.readyToClearMessage like '%Non-Delivery Locations' ) select mg.ownerId as MemberGroupChoId, mg.mfrCustNum, mg.operatorId as memberOperatorId, mg.fsl_choId memberChoId, c.cho_type memberChoType, mg.companyName memberOperatorName, c.cho_name memberChoName, opr.fsltablecode, opr.ownerId, opr.operatorId clientOperatorId, opr.companyName clientOperatorName, a.orgId clientMemberGroupOperatorId, a.partnerAffiliateNbr operatorMemberNbr, opr.fsl_choId clientChoId, cc.cho_type clientChoType, cc.cho_name clientChoName, case when exists ( select * from tbl_TPM_ProofOfDelivery pod with (nolock) where pod.ownerId = opr.ownerId and pod.ownerType = opr.fsltablecode and pod.operatorId = opr.operatorId ) then 'Y' else 'N' end hasClaims, case when exists ( select * from tbl_OPR_CLientOperators units with (nolock) where units.ownerId = opr.ownerId and units.fsltablecode = opr.fsltablecode and units.oprParentCo = opr.operatorId ) then 'Y' else 'N' end hasUnits from tbl_OPR_CLientOperators mg with (nolock) inner join tbl_CHO_Operators c on c.cho_id = mg.fsl_choId and c.cho_type in ( 'U', 'I' ) inner join tbl_OPR_CLientOperators gpo with (nolock) on gpo.fsl_choId= mg.ownerId and gpo.ownerId > 0 and gpo.fsltablecode in ( 'MFR' ) inner join tbl_CRM_Affiliations a with (nolock) on a.ownerId = gpo.ownerId and a.ownerType = gpo.fsltablecode and a.orgId = gpo.operatorId and a.orgType = 'OPR' and a.partnerType = 'OPR' and LTRIM(RTRIM(SUBSTRING(a.partnerAffiliateNbr, (PATINDEX('%[^0]%', a.partnerAffiliateNbr)), 25))) = LTRIM(RTRIM(SUBSTRING(mg.mfrCustNum, (PATINDEX('%[^0]%', mg.mfrCustNum)), 25))) inner join tbl_OPR_CLientOperators opr with (nolock) on opr.ownerId = a.ownerId and opr.fsltablecode = a.ownerType and opr.operatorId = a.partnerId and opr.operatorId in ( select operatorId from operators ) /* and exists ( select * from tbl_TPM_ProofOfDelivery pod with (nolock) inner join tbl_OPR_CLientOperators opr2 with (nolock) on opr2.operatorId = pod.operatorId and opr2.fsltablecode = pod.ownerType and opr2.ownerId = pod.ownerId inner join tbl_CHO_Operators cho with (nolock) on cho.cho_id = opr2.fsl_choId and cho.cho_type not in ( 'I','U') inner join tbl_TPM_Claims c on c.ownerId = pod.ownerId and c.ownerType = pod.ownerType and c.claimId = pod.claimId inner join tbl_OPR_CLientOperators claimant with (nolock) on claimant.ownerId = c.ownerId and claimant.fsltablecode = c.ownerType and claimant.operatorId = c.partnerId and 'OPR' = c.partnerType where pod.ownerId = opr.ownerId and pod.ownerType = opr.fsltablecode and pod.operatorId = opr.operatorId and c.readyToClearMessage like '%Non-Delivery Locations' ) */ left outer join tbl_CHO_Operators cc with (nolock) on cc.cho_Id = opr.fsl_choId where mg.fsltablecode = 'CHO' and mg.ownerId = and mg.ownerId not in ( 499 ) -- and mg.ownerId in ( select cho_id from tbl_CHO_Operators where cho_segmentId = 54 and cho_type in ( 'G', 'C' )) and nullif( mg.mfrCustNum, '' ) is not null and mg.fsl_choId = and nullif( mg.mfrCustNum, '' ) is not null and mg.fsl_choId > 0 and mg.oprCompanyType = '0' and mg.fsl_choId != coalesce( opr.fsl_choId, 0 ) and coalesce( cc.cho_type, '?' ) not in ( 'U', 'I' ) and opr.ownerId != 26 and opr.ownerId = and opr.fsltablecode = order by MemberGroupChoId, opr.ownerId, opr.fsltablecode, mg.mfrCustNum select distinct c.claimId, opr.ownerId, opr.fsltablecode, opr.companyName, opr.operatorId, opr.fsl_choId, opr.recordSource, opr.lastImportSource, opr.fsl_choId from tbl_TPM_CLaims c with (nolock) inner join tbl_TPM_ProofOfDelivery pod with (nolock) on pod.ownerId = c.ownerId and pod.ownerType = c.ownerType and pod.claimId = c.claimId inner join tbl_OPR_CLientOperators opr with (nolock) on opr.ownerId = pod.ownerId and opr.fsltablecode = pod.ownerType and opr.operatorId = pod.operatorId inner join tbl_CHO_OPerators cho with (nolock) on cho.cho_id = opr.fsl_choId and cho.cho_type not in ( 'I', 'U' ) inner join tbl_OPR_CLientOperators copr with (nolock) on copr.ownerId = c.ownerId and copr.fsltablecode = c.ownerType and copr.operatorId = c.partnerId and copr.fsl_choId = and copr.fsl_choId > 0 where c.readyToClearMessage like '%non-delivery%' and c.ownerId = and c.ownerType = and c.partnerType = 'OPR' delete from tbl_CRM_Affiliations where ownerId = and ownerType = and partnerId = and partnerType = 'OPR' and partnerAffiliateNbr = and orgtype = 'OPR' and orgId = UPDATE tbl_OPR_ClientOperators SET FSL_choId = 0, fsl_choIdStatus = null, fsl_choIdBy = null, fsl_choIdDate = null, oprCompanyType = '0', independentYN = 1, oprParentCo = null WHERE OwnerID = AND FSLTableCode = AND operatorId = and fsl_choId = update tbl_OPR_CLientOperators set oprParentCo = null WHERE OwnerID = AND FSLTableCode = AND oprParentCo = select distinct claimId, ownerId, fsltablecode from qmd_cantFix UPDATE tbl_OPR_ClientOperators SET FSL_choId = 0, fsl_choIdStatus = null, fsl_choIdBy = null, fsl_choIdDate = null, oprCompanyType = '0', independentYN = 1, oprParentCo = null WHERE OwnerID = AND FSLTableCode = AND operatorId = and fsl_choId = update tbl_OPR_CLientOperators set oprParentCo = null WHERE OwnerID = AND FSLTableCode = AND oprParentCo = select claimId, readyToClear, readyToClearMessage from tbl_TPM_Claims where ownerId = and ownerType = and claimId in ( )

Applied

Rolling Back