with podBase as ( select pod.ownerType, pod.ownerId, pod.claimId, pod.proofOfDeliveryId, pod.operatorId, case when left( pod.flagComment, 17 ) = 'Excluded Operator' then 'Direct Conflict' else 'GPO Overlap' end conflictType, pod.flagComment, pod.itemQty, pod.totalRebate, pod.exceptionSuppressList, case when c.acknowledgedDate is not null then 'Closed' when c.claimStatus = 'PENDING' and c.pendingDate is not null and c.lastDraftSavedDate is not null then 'In Progress' when c.claimStatus = 'PENDING' and c.pendingDate is not null and c.lastDraftSavedDate is null then 'Pending Review' end as claimPendingStatus, pod.itemCode, pod.itemDescription from tbl_TPM_ProofOfDelivery pod with (nolock) inner join tbl_TPM_Claims c with (nolock) on pod.ownerId = c.ownerId and pod.ownerType = c.ownerType and pod.claimId = c.claimId where pod.claimId = and pod.flagged = 'Y' and ( left( pod.flagComment, 17 ) = 'Excluded Operator' or left( pod.flagComment, 15 ) = 'Already Claimed' ) ), claimPendingDetails as ( select pb.claimId, pb.proofOfDeliveryId, pb.operatorId, pb.conflictType, pb.flagComment, pb.itemQty, pb.totalRebate, pb.exceptionSuppressList, pb.claimPendingStatus, pb.itemCode, pb.itemDescription, cho.cho_name, cho.cho_address, cho.cho_city, cho.cho_state, co.fsl_choId, co.companyName, co.address, co.city, co.state from podBase pb left join tbl_OPR_ClientOperators co with (nolock) on co.ownerId = pb.ownerId and co.fsltablecode = pb.ownerType and co.operatorId = pb.operatorId inner join tbl_CHO_Operators cho with (nolock) on cho.cho_id = co.fsl_choId ) #forJSON( 'claimPendingDetails', attributes.datastore, 'proofOfDeliveryId' )#