with purchasingWithBrokerData as ( select purchasing.cho_id, purchasing.operatorId, purchasing.mfr_name, purchasing.mfrId, purchasing.quantityAmt, purchasing.quantityUOM, case when purchasing.quantityTime = 'M' then 'Monthly' when purchasing.quantityTime = 'Y' then 'Annually' else purchasing.quantityTime end as quantityTime, case when purchasing.purchasingStatus = 'B' then 'Buying' when purchasing.purchasingStatus = 'N' then 'NOT Buying' else purchasing.purchasingStatus end as purchasingStatus, purchasing.reason, purchasing.mfrSkuId, purchasing.mfrSku, purchasing.mfrSkuDesc, purchasing.mfrProdId, purchasing.mfrProduct, purchasing.mfrShortDesc, purchasing.lastPurchaseMonth, purchasing.skuId, purchasing.prodId, purchasing.skuTitle, purchasing.productTitle from tbl_DW_OperatorPurchasing_#attributes.ownerType##attributes.ownerId# purchasing where purchasing.operatorId = ) select * from purchasingWithBrokerData order by mfr_name, productTitle, skuTitle for JSON PATH, INCLUDE_NULL_VALUES with territoryResources as ( select distinct ptr.principalTerritoryId from tbl_OPR_ClientOperators opr with (nolock) inner join tbl_BRO_PrincipalTerritoryResources ptr with (nolock) on ptr.ownerId = opr.ownerId and ptr.ownerType = opr.fsltablecode and ptr.resourceId = opr.territoryId and ptr.resourceType = 'TER' where opr.ownerId = and opr.fsltablecode = and opr.operatorId = ) select distinct opr.operatorId, cho.cho_id, p.mfrId from tbl_OPR_ClientOperators opr with (nolock) inner join tbl_CHO_Operators cho with (nolock) on cho.cho_id = opr.fsl_choId inner join tbl_BRO_PrincipalTerritoryResources ptr with (nolock) on ptr.ownerId = opr.ownerId and ptr.ownerType = opr.fsltablecode and ptr.resourceType = 'BO' and ptr.principalTerritoryId in ( select principalTerritoryId from territoryResources ) inner join tbl_BRO_PrincipalTerritories pt with (nolock) on pt.principalTerritoryId = ptr.principalTerritoryId and pt.ownerId = ptr.ownerId and pt.ownerType = ptr.ownerType and pt.hireStatus = 'A' inner join tbl_BRO_Principals p with (nolock) on p.ownerId = pt.ownerId and p.ownerType = pt.ownerType and p.principalId = pt.principalId where opr.ownerId = and opr.fsltablecode = and opr.operatorId = and exists ( select * from fsenablers..tbl_APP_Configurations cfg with (nolock) where cfg.parameterName = 'apps.operatorSalesData.enabled' and cfg.parameterValue = 'true' and cfg.ownerType = 'MFR' and cfg.ownerId = p.mfrId ) and exists ( select * from tbl_SMA_BrokerOfficeLink sma with (nolock) where sma.mfr_id = p.mfrId and sma.brokerid = p.ownerId and sma.brokerofficeid = ptr.resourceId ) select name purchasingTableName from sysobjects with (nolock) where name like 'tbl[_]DW[_]OperatorPurchasing[_]MFR%' and xType = 'U' and name != 'tbl_DW_OperatorPurchasing_MFR6500' and ( name = 'tbl_DW_OperatorPurchasing_MFR0' or name = 'tbl_DW_OperatorPurchasing_MFR#mfrId#' ) with purchasing as ( union select m.mfr_name, m.mfr_id, p.operatorName, case when p.quantityTime = 'M' then p.quantityAmt * 12.0 else p.quantityAmt end as quantityAmt, upper( m.unitOfMeasurePlural ) quantityUOM, case when p.quantityTime = 'M' then 'Y' else p.quantityTime end as quantityTime, p.purchasingStatus, p.reason, p.skuId, p.sku, p.skuDesc, p.prodId, p.product, p.shortDesc, p.lastPurchaseMonth, p.sku + ' - ' + p.skuDesc as skuTitle, p.product + ' - ' + p.shortDesc as productTitle, p.cho_id from #purchasingTableName# p with (nolock) inner join tblManufacturers m with (nolock) on m.mfr_id = p.ownerId and 'MFR' = p.ownerType where p.cho_id = and p.lastPurchaseMonth is not null ), purchasingWithBrokerData as ( select purchasing.cho_id, #val( qmd_operator.operatorId )# operatorId, purchasing.mfr_name, purchasing.mfr_id, purchasing.quantityAmt, purchasing.quantityUOM, case when purchasing.quantityTime = 'M' then 'Monthly' when purchasing.quantityTime = 'Y' then 'Annually' else purchasing.quantityTime end as quantityTime, case when purchasing.purchasingStatus = 'B' then 'Buying' when purchasing.purchasingStatus = 'N' then 'NOT Buying' else purchasing.purchasingStatus end as purchasingStatus, purchasing.reason, purchasing.skuId, purchasing.sku, purchasing.skuDesc, purchasing.prodId, purchasing.product, purchasing.shortDesc, purchasing.lastPurchaseMonth, s.skuId brokerSkuId, p.prodId brokerProdId, case when s.skuId is not null then s.sku + ' - ' + s.skuDesc else purchasing.skuTitle end as skuTitle, case when p.prodId is not null then p.product + ' - ' + p.shortDesc else purchasing.productTitle end as productTitle from purchasing left outer join tbl_PRD_Skus s with (nolock) on s.ownerId = and s.fsl_tablecode = and s.mfrid = purchasing.mfr_id and s.mfrSkuId = purchasing.skuId left outer join tbl_PRD_Products p with (nolock) on p.ownerId = s.ownerId and p.fsl_tablecode = s.fsl_tablecode and p.prodId = s.prodId ) select * from purchasingWithBrokerData order by mfr_name, productTitle, skuTitle for JSON PATH, INCLUDE_NULL_VALUES