select c.claimId from tbl_TPM_Claims c with (nolock) where c.ownerId = 288 and c.ownertype = 'MFR' and exists ( select * from tbl_TPM_ProofOfDelivery epod with (nolock) where epod.claimId = c.claimId and epod.ownerId = c.ownerId and epod.ownerType = c.ownerType and epod.flagged = 'Y' and epod.flagComment = 'Item Not on Contract' ) and not exists ( select * from tbl_TPM_ProofOfDelivery epod where epod.claimId = c.claimId and epod.ownerId = c.ownerId and epod.ownerType = c.ownerType and epod.flagged = 'Y' and epod.flagComment = 'Item Not Recognized' ) and not exists ( select * from tbl_TPM_ProofOfDelivery epod where epod.claimId = c.claimId and epod.ownerId = c.ownerId and epod.ownerType = c.ownerType and epod.flagged = 'Y' and epod.flagComment = 'Missing Distributor' ) and coalesce( c.readyToClear, 'N' ) != 'Y' order by c.claimId