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 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 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 cdra.cdr_recordId, cdra.cdr_dstName, cdr.cdr_dstCode from tbl_CDR_Distributors cdr with (nolock) inner join tbl_CDR_Distributors cdra with (nolock) on cdra.fsl_dstId = cdr.fsl_dstId and cdra.ownerId = cdr.ownerId and cdra.fsl_tablecode = cdr.fsl_tablecode and cdra.cdr_dstCOmpanyType = 'S' and cdra.cdr_specialAcctType = 'RDA' where cdr.cdr_recordId in ( ) and cdr.ownerId = and cdr.fsl_tablecode = select distinct 2 as rank, 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 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 with rawClaims as ( select c.ownerId, c.ownerType, r.contractId, c.claimId, c.claimDate, c.claimInvoice, c.claimPeriodStart, c.claimPeriodEnd, cc.firstDate, cc.lastDate, r.assignedContractNbr, r.description, c.partnerId, c.partnerType, pod.cdrRecordId, cc.approvedAmount, ceiling( sum( case when pod.proofOfDeliveryId is not null then case when pod.skuId > 0 then pod.itemQty else 0 end else cc.qualifiedQuantity end ) ) tradeCases, sum( case when pod.proofOfDeliveryId is not null then case when cc.status = 'APPROVED_CLAIMAMT' then pod.totalRebate else pod.qualifiedAmount end else cc.approvedAmount end ) tradeDollars, c.checkIssued, count( distinct pod.proofOfDeliveryId ) podLines from tbl_TPM_ClaimContracts cc with (nolock) left outer join tbl_TPM_ProofOfDelivery pod with (nolock) on pod.claimId = cc.claimId and pod.contractNbr = cc.contractNbr and pod.ownerType = cc.ownerType and pod.ownerId = cc.ownerId inner join tbl_TPM_Claims c with (nolock) on c.claimId = cc.claimId and c.ownerType = cc.ownerType and c.ownerId = cc.ownerId inner join tbl_TPM_Requests r with (nolock) on r.requestId= cc.requestId and r.ownerType = cc.ownerType and r.ownerId = cc.ownerId inner join tbl_TPM_Contracts ct with (nolock) on ct.contractId = r.contractId and ct.ownerId = r.ownerId and ct.ownerType = r.ownerType inner join tbl_TPM_RequestTypes rt with (nolock) on rt.ownerId = r.ownerId and rt.ownerType = r.ownerType and rt.requestTypeId = r.requestTypeId where cc.requestId in ( ) and cc.reconciled = 'Y' and cc.ownerId = and cc.ownerType = and ( pod.status != 'DENIED' or ( nullif( pod.status, '' ) is null and rt.requestTypeId = 76 ) ) and pod.status != 'DENIED' and ( ( c.partnerType = 'OPR' and c.partnerId in ( )) or pod.operatorId in ( ) or ( ct.partnerType = 'OPR' and ct.partnerId in ( ))) and ( ( c.partnerType = 'CDR' and c.partnerId in ( )) or coalesce( pod.cdrRecordId, 0 ) in ( )) and ( or ( coalesce( coalesce( cc.lastDate, c.claimPeriodEnd ), c.claimDate ) between and ) ) and ct.contractName = and pod.skuId in ( ) and ( ( c.partnerType = 'CDR' and c.partnerId in ( )) or coalesce( pod.cdrRecordId, 0 ) in ( )) group by c.ownerId, c.ownerType, c.claimId, r.contractId, c.claimDate, c.claimInvoice, c.claimPeriodStart, c.claimPeriodEnd, cc.firstDate, cc.lastDate, r.assignedContractNbr, r.description, c.partnerId, c.partnerType, pod.cdrRecordId, c.checkIssued, cc.approvedAmount ), claims as ( select contractId, claimId, claimDate, claimInvoice, claimPeriodStart, claimPeriodEnd, max( firstDate ) firstDate, min( lastDate ) lastDate, assignedContractNbr, description, partnerId, partnerType, sum( tradeCases ) tradeCases, sum( tradeDollars ) tradeDollars, count( podLines ) podLines, coalesce( opr.companyName, cdr.cdr_dstName ) claimantName, '#attributes.timeframe#' as timeframe from rawClaims left outer join tbl_OPR_ClientOperators opr with (nolock) on rawClaims.ownerId = opr.ownerId and rawClaims.ownerType = opr.fsltablecode and rawClaims.partnerId = opr.operatorId and rawClaims.partnerType = 'OPR' left outer join tbl_CDR_Distributors cdr with (nolock) on rawClaims.ownerId = cdr.ownerId and rawClaims.ownerType = cdr.fsl_tablecode and rawClaims.partnerId = cdr.cdr_recordId and rawClaims.partnerType = 'CDR' group by claimId, contractId, claimDate, claimInvoice, claimPeriodStart, claimPeriodEnd, assignedContractNbr, description, partnerId, partnerType, coalesce( opr.companyName, cdr.cdr_dstName ) ) #forJSON( 'claims', attributes.datastore, 'claimDate desc' )#