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' 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 with (nolock) 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 = or exists ( select * from tbl_CRM_Affiliations a with (nolock) where a.ownerId = opr.ownerId and a.ownerType = opr.fsltablecode and a.partnerId = opr.operatorId and a.partnerType = 'OPR' and a.orgType = 'OPR' and a.orgId = ) ) 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 ( ) and r.ownerId = and r.ownerType = select rank, partnerId, partnerType, requestId, assignedContractNbr, contractName from qmd_contractScope_Primary union select rank, partnerId, partnerType, requestId, assignedContractNbr, contractName from qmd_contractScope_Other order by rank, assignedContractNbr select operatorId from tbl_OPR_ClientOperators with (nolock) where ownerId = and fsltablecode = and deliveryLocationFor = union select operatorId from tbl_OPR_ClientOperators with (nolock) where ownerId = and fsltablecode = and oprParentCo = and oprCompanyType = '0' and independentYN = 0 union select a.partnerId as operatorId from tbl_OPR_ClientOperators opr with (nolock) inner join tbl_CRM_Affiliations a with (nolock) on a.orgId = opr.operatorId and a.ownerType = opr.fsltablecode and a.ownerId = opr.ownerId and a.orgType = 'OPR' and a.partnerType = 'OPR' where opr.ownerId = and opr.fsltablecode = and opr.operatorId = union select opr.operatorId from tbl_OPR_ClientOperators opr with (nolock) where opr.ownerId = and opr.fsltablecode = and opr.oprParentCo = select distinct case when c.partnerType = 'CDR' then c.partnerId else 0 end claimCdrRecordId, case when cdr.cdr_recordId is null then 'Unknown Distributor' else cdr.cdr_dstName end cdr_dstName, case when cdr.cdr_recordId is null then 0 else cdr.cdr_recordId end cdr_recordId, case when cdr.cdr_recordId is null then 1 else 0 end sortGroup from tbl_TPM_Claims c with (nolock) inner join tbl_TPM_ClaimContracts cc with (nolock) on cc.claimId = c.claimId and cc.ownerId = c.ownerId and cc.ownerType = c.ownerType and cc.requestId in ( ) and cc.reconciled = 'Y' inner join tbl_TPM_ProofOfDelivery pod with (nolock) on pod.claimId = cc.claimId and pod.ownerId = cc.ownerId and pod.ownerType = cc.ownerType and pod.contractNbr = cc.contractNbr left outer join tbl_CDR_Distributors cdr with (nolock) on cdr.cdr_recordId = pod.cdrRecordId and cdr.ownerId = pod.ownerId and cdr.fsl_tablecode = pod.ownerType where c.ownerId = and c.ownerType = and ( pod.operatorId in ( ) or ( c.partnerId in ( ) and c.partnerType = 'OPR' ) or c.partnerType = 'CDR' ) order by sortGroup, cdr_dstName select distinct qualifierId from tbl_MFR_TPF_Detail#_tpfSuffix# tpf with (nolock) where tpf.mfr_id = and tpf.forecast_status = 'C' and tpf.tPartnerId = and tpf.tPartnerType = and nullif( tpf.rebateContractId, 0 ) is null and tpf.qualifierType = 'CDR' and tpf.qualifierId > 0 with distributorClaimants as ( select cdr.cdr_recordId, cdr.cdr_dstName from tbl_CDR_Distributors cdr with (nolock) where cdr.ownerId = and cdr.fsl_tablecode = and cdr.cdr_recordId in ( ) UNION ALL select 0 as cdr_recordId, 'Unknown Distributor' as cdr_dstName ) #forJSON( 'distributorClaimants', attributes.datastore, 'cdr_dstName' )# select distinct 1 as ranking, r.requestId, c.assignedContractNbr, c.contractName, c.partnerType, c.partnerId 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' select distinct 2 as ranking, r.requestId, c.assignedContractNbr, c.contractName, c.partnerType, c.partnerId 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.requestId = r.requestId and cc.ownerId = r.ownerId and cc.ownerType = r.ownerType and cc.reconciled = 'Y' inner join tbl_TPM_Claims cl with (nolock) on cl.ownerId = cc.ownerId and cl.ownerType = cc.ownerType and cl.claimId = cc.claimId left outer join tbl_TPM_ProofOfDelivery pod with (nolock) on pod.claimId = cc.claimId and pod.ownerId = cc.ownerId and pod.ownerType = cc.ownerType and pod.contractNbr = cc.contractNbr where c.ownerId = and c.ownerType = and r.status = 'APPROVED' and ( pod.cdrRecordId = or ( cl.partnerType = and cl.partnerId = ) ) and 'CDR' = and c.assignedContractNbr not in ( ) select ranking, requestId, assignedContractNbr, contractName, partnerType, partnerId from qmd_contractScope_Primary union select ranking, requestId, assignedContractNbr, contractName, partnerType, partnerId from qmd_contractScope_other order by ranking, assignedContractNbr with distributorClaimants as ( select cdr.cdr_dstName, cdr.cdr_recordId from tbl_TPM_Claims c with (nolock) inner join tbl_CDR_Distributors cdr with (nolock) on cdr.cdr_recordId = c.partnerId and cdr.ownerId = c.ownerId and cdr.fsl_tablecode = c.ownerType inner join tbl_TPM_ClaimContracts cc on cc.claimId = c.claimId and cc.ownerId = c.ownerId and cc.ownerType = c.ownerType and cc.requestId in ( ) and cc.reconciled = 'Y' where cdr.ownerId = and cdr.fsl_tablecode = and c.partnerType = 'CDR' union select cdr.cdr_dstName, cdr.cdr_recordId from tbl_TPM_Claims c with (nolock) inner join tbl_TPM_ClaimContracts cc on cc.claimId = c.claimId and cc.ownerId = c.ownerId and cc.ownerType = c.ownerType and cc.requestId in ( ) and cc.reconciled = 'Y' inner join tbl_TPM_ProofOfDelivery pod with (nolock) on pod.claimId = cc.claimId and pod.ownerId = cc.ownerId and pod.ownerType = cc.ownerType and pod.contractNbr = cc.contractNbr inner join tbl_CDR_Distributors cdr with (nolock) on cdr.cdr_recordId = pod.cdrRecordId and cdr.ownerId = pod.ownerId and cdr.fsl_tablecode = pod.ownerType where cdr.ownerId = and cdr.fsl_tablecode = ) #forJSON( 'distributorClaimants', attributes.datastore, 'cdr_dstName' )#