select distinct sma.brokerId, b.broker_Name, case when 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 = sma.mfr_id ) then 'Y' else 'N' end as operatorSalesDataEnabled, b.oneFSEnabled from tbl_SMA_BrokerOfficeLink sma with (nolock) inner join tblBrokers b on b.broker_id = sma.brokerId where sma.mfr_id =

delete from tbl_DW_OperatorPurchasing_BRO#_brokerId# where mfrId = with syndicated as ( select distinct cho.cho_Id 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' inner join tbl_BRO_PrincipalTerritoryResources ptr2 with (nolock) on ptr2.ownerId= ptr.ownerId and ptr2.ownerType = ptr.ownerType and ptr2.resourceType = 'TER' and ptr2.resourceId = opr.territoryId and ptr2.principalTerritoryId = ptr.principalTerritoryId 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 and p.mfrId = where opr.ownerId = and opr.fsltablecode = 'BRO' 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 ) ), mfrSkus as ( select mfrId, mfrSkuId, min( mfrSkuIdDate ) mfrSkuIdDate from tbl_PRD_Skus with (nolock) where mfrId > 0 and mfrSkuId > 0 and ownerId = and fsl_tablecode = 'BRO' group by mfrId, mfrSkuId ), skus as ( select s.skuId, s.mfrId, s.mfrSkuId, s.skuDesc, s.sku, s.prodId, s.ownerId, s.fsl_tablecode from tbl_PRD_Skus s with (nolock) inner join mfrSKus ms on ms.mfrId = s.mfrId and ms.mfrSkuId = s.mfrSkuId and ms.mfrSkuIdDate = s.mfrSkuIdDate where s.ownerId = and s.fsl_tablecode = 'BRO' ) insert into tbl_DW_OperatorPurchasing_BRO#_brokerId# ( ownerId, ownerType, mfrId, mfr_name, cho_id, operatorName, mfrSkuId, mfrSku, mfrSkuDesc, mfrProdId, mfrProduct, mfrShortDesc, skuTitle, productTitle, quantityAmt, quantityTime, quantityUOM, firstPurchaseMonth, lastPurchaseMonth, purchasingStatus, operatorId, territoryId, internalSalesId, territoryName, skuId, sku, skuDesc, prodId, product, shortDesc, dataDate, reason ) select opr.ownerId, opr.fsltablecode ownerType, m.mfr_id mfrId, m.mfr_name, op.cho_id, op.operatorName, op.skuId mfrSkuId, op.sku mfrSku, op.skuDesc mfrSkuDesc, op.prodId mfrProdId, op.product mfrProduct, op.shortDesc mfrShortDesc, case when s.skuId is not null then s.sku + ' - ' + s.skuDesc else op.sku + ' - ' + op.skuDesc end as skuTitle, case when p.prodId is not null then p.product + ' - ' + p.shortDesc else op.product + ' - ' + op.shortDesc end as productTitle, op.quantityAmt, op.quantityTime, op.quantityUOM, op.firstPurchaseMonth, op.lastPurchaseMonth, op.purchasingStatus, opr.operatorId, opr.territoryId, ter.internalSalesId, ter.name territoryName, s.skuId, s.sku, s.skuDesc, p.prodId, p.product, p.shortDesc, getDate() dataDate, op.reason from tbl_DW_OperatorPurchasing_MFR#_mfrId# op with (nolock) inner join tblManufacturers m with (nolock) on m.mfr_id = op.ownerId inner join tbl_OPR_CLientOperators opr with (nolock) on opr.fsl_choId= op.cho_id and opr.ownerId = and opr.fsltablecode = 'BRO' and opr.fsl_choId in ( select cho_id from syndicated ) left outer join skus s with (nolock) on s.mfrSkuId = op.skuId and s.mfrId = op.ownerId and s.ownerId = opr.ownerId and s.fsl_tablecode = opr.fsltablecode 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 inner join tbl_TER_Territories ter with (nolock) on ter.territoryId = opr.territoryId and ter.fsl_tablecode = opr.fsltablecode and ter.ownerId = opr.ownerId where op.lastPurchaseMonth is not null order by opr.fsltablecode, opr.ownerId, opr.operatorId, op.cho_id, op.skuId

Syndicated #_r.recordCount# records to #htmleditformat( broker_name )#