with contractScopePrimary as ( select distinct 1 as rank, c.partnerId, c.partnerType, r.requestId, c.assignedContractNbr, c.contractName from tbl_TPM_Requests r with (nolock) inner join tbl_TPM_Contracts c with (nolock) on c.contractId = r.contractId and c.ownerId = r.ownerId and c.ownerType = r.ownerType where c.partnerId = and c.partnerType = and c.ownerId = and c.ownerType = and r.status = 'APPROVED' ), claimContracts as ( select distinct cc.requestId from tbl_TPM_ProofOfDelivery pod with (nolock) 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_TPM_ClaimContracts cc on cc.ownerId = pod.ownerId and cc.ownerType = pod.ownerType and cc.contractNbr = pod.contractNbr and cc.claimId = pod.claimId where pod.ownerId = and pod.ownerType = and ( opr.operatorId = or opr.deliveryLocationFor = ) ), contractScopeOther as ( select distinct 2 as rank, c.partnerId, c.partnerType, r.requestId, c.assignedContractNbr, c.contractName from tbl_TPM_Requests r with (nolock) inner join tbl_TPM_Contracts c with (nolock) on c.contractId = r.contractId and c.ownerId = r.ownerId and c.ownerType = r.ownerType inner join tbl_TPM_ClaimContracts cc with (nolock) on cc.ownerId = c.ownerId and cc.ownerType = c.ownerType and cc.requestId = r.requestId inner join tbl_TPM_Claims cl with (nolock) on cl.ownerId = cc.ownerId and cl.ownerType = cc.ownerType and cl.claimId = cc.claimId and cl.claimStatus = 'APPROVED' where r.status = 'APPROVED' and r.requestId in ( select claimContracts.requestId from claimContracts ) and r.ownerId = and r.ownerType = ), contractScope as ( select --rank, partnerId, partnerType, requestId, assignedContractNbr, contractName, contractName + ' (' + assignedContractNbr + ')' as agreement from contractScopePrimary union select --rank, partnerId, partnerType, requestId, assignedContractNbr, contractName, contractName + ' (' + assignedContractNbr + ')' as agreement from contractScopeOther group by requestId, assignedContractNbr, contractName ) #forJSON( 'contractScope', attributes.datastore, 'assignedContractNbr' )#