with requests as ( select r.ownerType, r.ownerId, r.requestId, r.description, r.status, r.statusDate, r.statusDueDate, r.requestLevel, r.parentRequestId, r.startDate, r.endDate, r.estimatedTrade, r.realized, r.realizedDate, r.promoNbr, r.versionNbr, r.createDate, r.createdBy, r.updateDate, r.updatedBy, r.comments, r.totalEstimatedTrade, r.distributorSales, r.volumeGenerator, r.salesImpact, t.requestTypeId, t.requestType, t.useInvHistPrice, m.requestModel, m.requireChildren, m.allowChildren, m.allowRevisions, m.useAsChild, m.timeframeMode, m.volumeBased, t.primaryPartnerType, t.secondaryPartnerType, t.allowMultiplePrimaryPartners, t.allowMultipleSecondaryPartners, ro.approverUserId requestOwnerUserId, ro.statusComment, rom.firstName, rom.lastName, rom.email, rom.title, rom.fsl_tablecode requestOwnerOwnerType, rom.ownerId requestOwnerOwnerId, r.allowanceAmt, r.estQuantity, r.volumeUOM, r.frequency, r.payableTo, r.payeeId, r.assignedContractNbr, r.entryMethod, sr.requestId sourceRequestId, r.linkId, r.linkType, r.costBasis, rp.partnerId primaryPartnerId, floor( 10191817 * sqrt( rp.partnerId * log10( rp.partnerId ) ) ) as primaryPartnerTk, r.paymentMethod, r.renewal, 'INACTIVE' claimStatus, r.generalLedgerCode, t.generalLedgerCode defaultGeneralLedgerCode, coalesce( nullif( r.generalLedgerCode, '' ), t.generalLedgerCode ) effectiveGeneralLedgerCode, coalesce( r.defaultIncentivePct, t.defaultIncentivePct ) defaultIncentivePct, r.allowLumpSumClaims, r.estLumpSumCost, r.lumpSumComments, case when rp.partnerType = 'OPR' then opr.companyName else cdr.cdr_dstName end as primaryPartnerName , case when rp.partnerType = 'OPR' then opr.territoryId else cdr.cdr_territoryId end as territoryId, case when exists (select * from tbl_TPM_Requests rr where rr.ownerId = r.ownerId and rr.ownerType = r.ownerType and rr.sourceRequestId = r.requestId and rr.status <> 'CANCELLED' ) then 'Y' else 'N' end revisionsPresent, r.termUnit, r.termLength, r.termStart, r.termEnd, r.deliveryTermOverride, c.contractId, c.contractName, r.chainSequence, r.currencyCode, case when r.chainSequence >= coalesce( c.lastApprovedChainSequence, 0 ) then 'true' else 'false' end lastInTheChain, r.brokerBidId, r.bidNumber, r.internalContactId, r.concurrencyTs, coalesce( ct.operatorExclusionMode, 'NO' ) as operatorExclusionMode, ct.externalAgreement, r.budgetId, coalesce( r.countAllCases, 0 ) countAllCases, r.specialPricingRequestId, r.baseRequestProductsRequestId, r.principalId, rtrim( mfr.mfr_name ) mfrName from tbl_TPM_Requests r with (nolock) left outer join tbl_TPM_Contracts ct with (nolock) on ct.contractId = r.contractId and ct.ownerId = r.ownerId and ct.ownerType = r.ownerType left outer join tbl_TPM_Contracts c with (nolock) on c.ownerId = r.ownerId and c.ownerType = r.ownerType and c.contractId = r.contractId inner join tbl_TPM_RequestTypes t with (nolock) on t.requestTypeId = r.requestTypeId and t.ownerId = r.ownerId and t.ownerType = r.ownerType inner join tbl_TPM_RequestModels m with (nolock) on m.requestModel = t.requestModel left outer join tbl_TPM_RequestApprovers ro with (nolock) on ro.requestId = r.requestId and ro.ownerId = r.ownerId and ro.ownerType = r.ownerType and ro.approverRole = 'OWNER' left outer join tbl_FSpro_members rom with (nolock) on rom.fspro_userid = ro.approverUserId and rom.ownerId > 1 left outer join tbl_TPM_Requests sr with (nolock) on sr.requestId = r.sourceRequestId and sr.ownerId = r.ownerId and sr.ownerType = r.ownerType left outer join tbl_TPM_RequestPartners rp with (nolock) on rp.requestId = r.requestId and rp.ownerId = r.ownerId and rp.ownerType = r.ownerType and rp.primaryPartner = 'Y' and rp.partnerType = t.primaryPartnerType left outer join tbl_OPR_ClientOperators opr with (nolock) on opr.operatorId = rp.partnerId and opr.ownerId = rp.ownerId and opr.fsltablecode = rp.ownerType and 'OPR' = rp.partnerType left outer join tbl_CDR_Distributors cdr with (nolock) on cdr.cdr_recordId = rp.partnerId and cdr.ownerId = rp.ownerId and cdr.fsl_tablecode = rp.ownerType and 'CDR' = rp.partnerType left outer join tbl_BRO_Principals ppl with (nolock) on ppl.ownerId = r.ownerId and ppl.ownerType = r.ownerType and ppl.principalId = r.principalId left outer join tblManufacturers mfr with (nolock) on mfr.mfr_id = ppl.mfrId where r.ownerId = and r.ownerType = and r.requestId IN ( select rp.requestId from tbl_TPM_RequestPartners rp with (nolock) where rp.ownerId = and rp.ownerType = and rp.partnerType = and rp.partnerId = and rp.primaryPartner = 'Y' ) and r.requestId in ( select rp.requestId from tbl_TPM_RequestPartners rp with (nolock) inner join tbl_OPR_ClientOperators opr with (nolock) on opr.operatorId = rp.partnerId and opr.ownerId = rp.ownerId and opr.fsltablecode = rp.ownerType and opr.territoryId in ( ) where rp.ownerId = and rp.ownerType = and rp.partnerType = t.primaryPartnerType and rp.partnerType = 'OPR' union select rp.requestId from tbl_TPM_RequestPartners rp with (nolock) inner join tbl_CDR_Distributors cdr with (nolock) on cdr.cdr_recordId = rp.partnerId and cdr.ownerId = rp.ownerId and cdr.fsl_tablecode = rp.ownerType and cdr.cdr_territoryId in ( ) where rp.ownerId = and rp.ownerType = and rp.partnerType = t.primaryPartnerType and rp.partnerType = 'CDR' ) and r.status in ( ) ) #forJSON( 'requests', attributes.datastore, 'status,startDate' )#