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 = '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' ) ) order by c.claimDate select readyToClear, readyToClearMessage, reconciledAmount from tbl_TPM_Claims with (nolock) where ownerId = and ownerType = and claimId =