select partnerType from tbl_LST_PartnerLists with (nolock) where ownerId = and ownerType = and listId = with opportunities as ( select * from tbl_DW_Opportunities#_tableSuffix# spl with (nolock) where 1 = 1 and spl.partnerTerritoryId in ( ) and spl.effectiveRepId in ( ) and spl.partnerType = and exists ( select * from tbl_LST_PartnerListItems pli with (nolock) where pli.listId = and pli.partnerId = spl.partnerId ) ), opportunitiesFormatted as ( select *, sku + ' - ' + skuDesc as skuFull, product + ' - ' + shortDesc as productFull, catNum + ' - ' + category as categoryFull, effectiveRepFirstName + ' ' + effectiveRepLastName as effectiveRepFullName, case when statusCode = 'A' then stageName + ' - ' + convert( varchar, cast( likelihoodPercent * 100.0 as int )) + '%' else stageName end as stageNameFull, case when budgetCategoryMgr_fspro_userId > 0 then budgetCategoryMgr_firstName + ' ' + budgetCategoryMgr_lastName else null end as budgetCategoryMgr_fullName, case when partnerType = 'OPR' then oprClassification else cdrClassification end as partnerClassification, case when partnerType = 'OPR' and oprClassificationId > 0 then partnerType + ':' + cast( oprClassificationId as varchar ) when partnerType = 'CDR' and cdrClassificationId > 0 then partnerType + ':' + cast( cdrClassificationId as varchar ) when partnerType = 'OPR' then 'OPR:0' when partnerType = 'CDR' then 'CDR:0' else null end as partnerClassificationKey, case when purchasingStatus is not null then 'Y' else 'N' end as has_purchasing_data, cast( case when partnerType = 'OPR' and oprCompanyType = '1' then 1 else 0 end as bit ) as isChainHQ, cast( case when partnerType = 'OPR' and oprCompanyType = '0' and independentYN = 0 then 1 else 0 end as bit ) as isChainUnit , concat_ws( '|', objName1, objName2, objName3, objName4, objName5 ) allObjNames from opportunities ) #forJSON( 'opportunitiesFormatted', attributes.dataStore, 'opportunityId' )#