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 distinct skuId from tbl_MFR_TPF_Detail#_tpfSuffix# tpf with (nolock) where tpf.mfr_id = and tpf.forecast_status = 'C' and ( tpf.rebateContractId in ( ) or ( tpf.tPartnerId = and tpf.tPartnerType = and nullif( tpf.rebateContractId, 0 ) is null )) with skuList as ( select s.sku, s.skuDesc, s.skuId, s.sku + ' - ' + s.skuDesc as tradeSku from tbl_PRD_Skus s with (nolock) where s.ownerId = and s.fsl_tablecode = and s.skuId in ( ) ) #forJSON( 'skuList', attributes.datastore, 'sku' )# 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 skuList as ( select s.sku, s.skuDesc, s.skuId, s.sku + ' - ' + s.skuDesc as tradeSku from tbl_PRD_Skus s with (nolock) where s.ownerId = and s.fsl_tablecode = and exists ( select * from tbl_MFR_TPF_Detail#_tpfSuffix# r where r.mfr_id = s.ownerId and r.skuId = s.skuId and r.forecast_status = 'R' and r.rebateContractId in ( ) ) ) #forJSON( 'skuList', attributes.datastore, 'sku' )#