select ownerId, ownerType, batchTime, config, triggerTime, batchId, batchClass from tbl_EAI_InboundBatches b with (nolock) where batchId =
Processing has been skipped because of a batch queue delay of more than 60 minutes (#lsnumberformat( datediff( "n", qmd_batch.batchTime, qmd_batch.triggerTime ))#). This processing will run again tomorrow.
 select updateAnalyzer from tbl_TPM_Settings where ownerId = and ownerType =
Using #_tpfTable#
truncate table tbl_DW_1fsOperatorMemberships#_tableSuffix# IF OBJECT_ID('#_1fsMemberGroupOperatorsTable#', 'U') IS NOT NULL DROP TABLE #_1fsMemberGroupOperatorsTable#; select fsltablecode, ownerId, fsl_choId, oprCompanyType, min( crmActiveDate ) crmActiveDate, max( crmDeactiveDate ) crmDeactiveDate, max( freshnessDate ) freshnessDate into #_1fsMemberGroupOperatorsTable# from tbl_OPR_ClientOperators with (nolock) where fsltablecode = 'CHO' and ownerId > 0 and fsl_choId > 0 and oprCompanyType = '0' group by fsltablecode, ownerId, fsl_choId, oprCompanyType; alter table #_1fsMemberGroupOperatorsTable# alter column fsl_choId int not null; alter table #_1fsMemberGroupOperatorsTable# add constraint PK_#_1fsMemberGroupOperatorsTable# primary key ( fsltablecode, ownerId, fsl_choId ) insert into tbl_DW_1fsOperatorMemberships#_tableSuffix# ( ownerType, ownerId, memberGroupChoId, memberGroupName, memberChoId, memberName, affiliationDate, deaffiliationDate, clientAffiliationDate, clientDeaffiliationDate ) select a.ownerType, a.ownerId, orgCho.cho_id memberGroupChoId, orgCho.cho_name memberGroupName, mCho.cho_Id memberChoId, mCho.cho_name memberName, min( orgM.crmActiveDate ) affiliationDate, -- max( orgM.crmDeactiveDate ) deaffiliationDate, case when max( orgM.crmDeactiveDate ) is not null then max( orgM.crmDeactiveDate ) when max( coalesce( orgM.crmDeactiveDate, orgM.freshnessDate ) ) = max( orgM.freshnessDate ) then null else max( coalesce( orgM.crmDeactiveDate, orgM.freshnessDate ) ) end deaffiliationDate, min( a.affiliationDate ) clientAffiliationDate, -- max( a.deaffiliationDate ) clientDeaffiliationDate, case when max( orgM.crmDeactiveDate ) is not null then max( orgM.crmDeactiveDate ) when max( coalesce( orgM.crmDeactiveDate, orgM.freshnessDate ) ) = max( orgM.freshnessDate ) then null else max( coalesce( orgM.crmDeactiveDate, orgM.freshnessDate ) ) end clientDeaffiliationDate from tbl_CRM_Affiliations a with (nolock) inner join #_clientMappedOperatorsTable# org with (nolock) on org.ownerId = a.ownerId and org.fsltablecode = a.ownerType and org.operatorId = a.orgId inner join tbl_CHO_Operators orgCho with (nolock) on orgCho.cho_id = org.fsl_choId and orgCho.cho_type in ( 'G', 'C' ) inner join #_clientMappedOperatorsTable# m with (nolock) on m.ownerId = a.ownerId and m.fsltablecode = a.ownertype and m.operatorId = a.partnerId inner join tbl_CHO_Operators mCho with (nolock) on mCho.cho_id = m.fsl_choId and mCho.cho_type in ( 'I', 'U' ) inner join #_1fsMemberGroupOperatorsTable# orgM with (nolock) on orgM.ownerId = orgCho.cho_id and orgM.fsltablecode = 'CHO' and orgM.fsl_choId = mCho.cho_id and orgM.oprCompanyType = '0' where a.ownerId = and a.ownerType = and a.orgType = 'OPR' and a.partnerType = 'OPR' group by a.ownerId, a.ownerType, orgCho.cho_id, orgCho.cho_name, mCho.cho_Id, mCho.cho_name IF OBJECT_ID('#_1fsMemberGroupOperatorsTable#', 'U') IS NOT NULL DROP TABLE #_1fsMemberGroupOperatorsTable#; truncate table tbl_DW_1fsSubGroups#_tableSuffix# inner join tbl_CHO_Operators mgcho with (nolock) on mgcho.cho_id = mg.ownerId inner join tbl_OPR_CLientOperators mgOpr with (nolock) on mgOPR.fsl_choId = mgCHo.cho_id inner join tbl_OPR_ClientOperators sgOpr with (nolock) on sgOPR.ownerId = mgOpr.ownerId and sgOPR.fsltablecode = mgOPR.fsltablecode and sgOPR.fsl_choId = sgCHO.cho_id left outer join tbl_OPR_ClientOperators pgOPR with (nolock) on pgOpr.ownerId = mgOpr.ownerId and pgOpr.fsltablecode = mgOpr.fsltablecode and pgOpr.fsl_choId = pgCHO.cho_id where mg.fsltablecode = 'CHO' and mgOPR.ownerId = and mgOPR.fsltablecode = order by memberGroupChoId, subGroupChoId ---> select distinct mgOPR.fsltablecode as ownerType, mgOPR.ownerId, mgcho.cho_id memberGroupChoId, mgcho.cho_name memberGroupName, mgcho.cho_type memberGroupType, sgcho.cho_id subGroupChoId, sgcho.cho_name subGroupName, sgcho.cho_type subGroupType, sg.lastUpdated, pgOpr.fsl_choId parentGroupChoId, case when pgOPR.fsl_choId > 0 then pgcho.cho_name else cast( null as varchar) end parentGroupName, case when pgOPR.fsl_choId > 0 then pgcho.cho_type else cast( null as varchar) end parentGroupType from tbl_OPR_ClientOperators mg with (nolock) inner join tbl_OPR_SubGroups sg with (nolock) on sg.ownerId = mg.ownerId and sg.ownerType = mg.fsltablecode and sg.operatorId = mg.operatorId left outer join tbl_OPR_ClientOperators pg with (nolock) on pg.operatorId = sg.parentGroupOperatorId and pg.ownerId = sg.ownerId and pg.fsltablecode = sg.ownerType and pg.fsl_choId > 0 left outer join tbl_CHO_Operators pgCHO with (nolock) on pgcho.cho_id = pg.fsl_choId inner join tbl_CHO_Operators sgcho with (nolock) on sgcho.cho_id = mg.fsl_choId and sgcho.cho_type in ( 'C', 'G' ) inner join tbl_CHO_Operators mgcho with (nolock) on mgcho.cho_id = mg.ownerId inner join tbl_OPR_CLientOperators mgOpr with (nolock) on mgOPR.fsl_choId = mgCHo.cho_id inner join tbl_OPR_ClientOperators sgOpr with (nolock) on sgOPR.ownerId = mgOpr.ownerId and sgOPR.fsltablecode = mgOPR.fsltablecode and sgOPR.fsl_choId = sgCHO.cho_id left outer join tbl_OPR_ClientOperators pgOPR with (nolock) on pgOpr.ownerId = mgOpr.ownerId and pgOpr.fsltablecode = mgOpr.fsltablecode and pgOpr.fsl_choId = pgCHO.cho_id where mg.fsltablecode = 'CHO' and mgOPR.ownerId = and mgOPR.fsltablecode = order by memberGroupChoId, subGroupChoId insert into tbl_DW_1fsSubGroups#_tableSuffix# ( ownerType, ownerId, memberGroupChoId, memberGroupName, memberGroupType, subGroupChoId, subGroupName, subGroupType, lastUpdated, parentGroupChoId, parentGroupName, parentGroupType ) values ( , , , , , , , , , , , ) truncate table tbl_DW_1FSOperators#_tableSuffix# insert into tbl_DW_1fsOperators#_tableSuffix# ( ownerType, ownerId, choId, operatorName, operatorAddress, operatorCity, operatorState, operatorZipCode, operatorSegment, operatorSegmentId, operatorCounty, lastActivityDate, operatorType, parentChoId, parentName, channelSegment, mainSegment, subSegment, primaryGPO, averageClientVolume ,accountOwnerOwnerType, accountOwnerOwnerId, accountOwnerUserId, accountOwnerFullName ) select distinct opr.fsltablecode as ownerType, opr.ownerId, cho.cho_Id choId, cho.cho_name operatorName, cho.cho_address operatorAddress, cho.cho_city operatorCity, cho.cho_state operatorState, cho.cho_zipcode operatorZipCode, seg.operatorSegment as operatorSegment, seg.segmentId operatorSegmentId, ( select top 1 usps.countyName from tbl_USPS_CityStateDetails usps with (nolock) where usps.zipCode = cho.cho_zipcode ) as operatorCounty, cho.freshnessDate as lastActivityDate, case when cho.cho_type = 'I' then 'INDEPENDENT' when cho.cho_type = 'U' then 'UNIT' when cho.cho_type = 'O' then 'PARENT' when cho.cho_type = 'C' then 'CMC' when cho.cho_type = 'G' then 'GPO' when cho.cho_type = 'H' then 'HOLDING' else 'UNKNOWN ' + cho.cho_type end operatorType, pcho.cho_Id parentChoId, pcho.cho_name parentName, seg.channelSegment, seg.mainSegment, seg.subSegment, case when seg.segmentId = 54 then 'true' else 'false' end primaryGPO, 0 ,u.accountOwnerOwnerType, u.accountOwnerOwnerId, u.accountOwnerUserId, u.accountOwnerFullName from tbl_OPR_ClientOperators opr with (nolock) inner join tbl_CHO_Operators cho with (nolock) on cho.cho_id = opr.fsl_choId left outer join tbl_OPR_Segments seg with (nolock) on seg.segmentId = cho.cho_segmentId left join tbl_CHO_Operators pcho with (nolock) on pcho.cho_id = cho.cho_parentCo and pcho.cho_type = 'O' left outer join tbl_DW_1fsUniverse#_tableSuffix# u on u.operatorChoId = opr.fsl_choId where opr.ownerId = and opr.fsltablecode = order by cho.cho_Id ) values ( , , , , , , , , , , , , , , , , , , , , , , , , ) ---> select ownerId, ownerType, contractId, partnerId, partnerType, businessOwnerUserId from tbl_TPM_Contracts ct with (nolock) where ct.ownerId = and ct.ownerType = and ct.partnerType = 'OPR' and not exists ( select * from tbl_fspro_members m with (nolock) where m.ownerId = ct.ownerId and m.fsl_tablecode = ct.ownertype and m.fspro_userId = ct.businessOwnerUserId ) update tbl_TPM_Contracts set businessOwnerUserId = where ownerId = and ownerType = and contractId = truncate table tbl_DW_OperatorAgreements#_tableSuffix# insert into tbl_DW_OperatorAgreements#_tableSuffix# ( ownerType, ownerId, cho_id, cho_name, assignedContractNbr, contractName, startDate, endDate, contractId, businessOwnerUserId, businessOwnerFirstName, businessOwnerLastName, businessOwnerEmail ) select ct.ownerType, ct.ownerId, cho.cho_id, cho.cho_name, ct.assignedContractNbr, ct.contractName, min( r.termStart ) startDate, max( r.termEnd ) endDate, ct.contractId, ct.businessOwnerUserId, bo.firstName as businessOwnerFirstName, bo.lastName as businessOwnerLastName, bo.email as businessOwnerEmail from tbl_TPM_Contracts ct with (nolock) inner join tbl_TPM_Requests r with (nolock) on r.ownerId = ct.ownerId and r.ownerType = ct.ownerType and r.contractId = ct.contractId and r.status = 'APPROVED' and r.volumeGenerator = 'Y' left outer join tbl_fspro_members bo with (nolock) on bo.ownerId = ct.ownerId and bo.fsl_tablecode = ct.ownerType and bo.fspro_userId = ct.businessOwnerUserId inner join #_clientMappedOperatorsTable# opr with (nolock) on opr.ownerId = ct.ownerId and opr.fsltablecode = ct.ownerType and opr.operatorId = ct.partnerId inner join tbl_CHO_Operators cho with (nolock) on cho.cho_id = opr.fsl_choId where ct.partnerType = 'OPR' and ct.ownerId = and ct.ownerType = group by ct.ownerId, ct.ownerType, cho.cho_id, cho.cho_name, ct.assignedContractNbr, ct.contractName, ct.contractId, ct.businessOwnerUserId, bo.firstName, bo.lastName, bo.email order by ownerType, ownerId, cho_id, assignedContractNbr truncate table tbl_DW_OperatorAnalyzer#_tableSuffix# where sma.mfr_id = @ownerId ), primaryBrokerOffices as ( select ownerId, fsl_tablecode, territoryId, broker_id, broker_name, broker_office_id, broker_office_name from allBrokerOffices where broker_office_rank = 1 ), agreements as ( select r.requestId, r.ownerId, r.ownerType, cho.cho_id agreementOwnerChoId, cho.cho_name agreementOwnerName, r.payableTo sourceType from tbl_TPM_Contracts ct with(nolock) inner join tbl_TPM_Requests r with (nolock) on r.ownerId = ct.ownerId and r.ownerType = ct.ownerType and r.contractId= ct.contractId inner join #_clientMappedOperatorsTable# opr with (nolock) on opr.operatorId = ct.partnerId and opr.ownerId = ct.ownerId and opr.fsltablecode = ct.ownerType and opr.fsl_choId > 0 inner join tbl_CHO_Operators cho with (nolock) on cho.cho_id = opr.fsl_choId where ct.ownerId = @ownerId and ct.ownerType = @ownerType and ct.partnerType = 'OPR' ), purchases as ( select tpf.mfr_id as ownerId, '#qmd_batch.ownerType#' as ownerType, convert( int, tpf.forecast_year ) purchasingYear, convert( int, tpf.forecast_month ) purchasingMonth, cho.cho_id as cho_id, tpf.forecast_qty, tpf.forecast_amt, tpf.forecast_exp, s.skuId, s.sku, s.skuDesc, tpf.rebateContractId as requestId, d.dstId, d.dstName, d.parentDstId, d.parentDstName from tbl_MFR_TPF_Detail#_tpfSuffix# tpf with (nolock) inner join #_clientMappedOperatorsTable# opr with (nolock) on opr.operatorId = tpf.tPartnerId and opr.ownerId = tpf.mfr_id and opr.fsltablecode = @ownerType inner join tbl_CHO_Operators cho with (nolock) on cho.cho_id = opr.fsl_choId inner join tbl_DW_ProductHierarchy#_tableSuffix# s with (nolock) on s.ownerId = tpf.mfr_id and s.ownerType = @ownerType and s.skuId = tpf.skuId left outer join tbl_TPM_Requests r with (nolock) on r.requestId = tpf.rebateContractId and r.ownerId = tpf.mfr_id and r.ownerType = 'MFR' left outer join tbl_DW_1fsDistributors#_tableSuffix# d with (nolock) on d.ownerId = tpf.mfr_id and d.ownerType = 'MFR' and d.cdr_recordId = tpf.qualifierId and 'CDR' = tpf.qualifierType where tpf.forecast_status = 'C' and tpf.mfr_id = @ownerId and convert( int, forecast_year ) >= @cutoffYear and tpf.tPartnerType = 'OPR' and ( ( coalesce( opr.inferredPurchasing, 'N' ) = 'Y' and coalesce( tpf.rebateContractId, 0 ) = 0 ) or coalesce( opr.inferredPurchasing, 'N' ) = 'N' or coalesce( r.volumeGenerator, 'N' ) = 'Y' ) ), rankedMappedOperators as ( select ownerId, fsltablecode ownerType, fsl_choId, territoryId, row_number() over( partition by fsl_choId order by operatorId ) operatorRank from #_clientMappedOperatorsTable# opr with (nolock) ), territoriesByLibraryPre as ( select ownerId, ownerType, fsl_choId, territoryId from rankedMappedOperators with (nolock) where operatorRank = 1 ), territoriesByLibrary as ( select t1.ownerId, t1.ownerType, t1.fsl_choId, t.territoryId, t.name as territoryName, t.territoryPath as territoryPath, bo.broker_id, bo.broker_name, bo.broker_office_id, bo.broker_office_name from territoriesByLibraryPre t1 left outer join tbl_TER_Territories t with (nolock) on t.territoryId = t1.territoryId and t.ownerId = t1.ownerId and t.fsl_tablecode = t1.ownerType left outer join primaryBrokerOffices bo with (nolock) on bo.ownerId= t.ownerId and bo.fsl_tablecode = t.fsl_tablecode and bo.territoryId = t.territoryId where t1.ownerId = @ownerId and t1.ownerType = @ownerType ), operators as ( select distinct opr.ownerId, opr.fsltablecode ownerType, cho.cho_segmentId as segmentId, seg.operatorSegment operatorSegment, seg.mainSegment mainSegment, seg.subSegment subSegment, seg.channelSegment channelSegment, cho.cho_type, cho.cho_Id, cho.cho_name operatorName, ter.territoryId, ter.territoryName, ter.territoryPath, ter.broker_id, ter.broker_name, ter.broker_office_id, ter.broker_office_name, cho.cho_address as operatorAddress, cho.cho_city as operatorCity, cho.cho_state as operatorState, cho.cho_zipcode as operatorZipCode, ( select top 1 usps.countyName from tbl_USPS_CityStateDetails usps with (nolock) where usps.zipCode = cho.cho_zipcode ) as operatorCounty, case when exists ( select * from tbl_DW_1fsSubGroups#_tableSuffix# sg with (nolock) where sg.ownerId = opr.ownerId and sg.ownerType = opr.fsltablecode and cho.cho_id in ( sg.memberGroupChoId, sg.parentGroupChoId )) then 'true' else 'false' end as hasSubGroups, case when coalesce( o.parentChoId, 0 ) > 0 then o.parentChoId else o.choId end as rollup_cho_id, case when coalesce( o.parentChoId, 0 ) > 0 then p.operatorName else o.operatorName end as rollup_operatorName, case when coalesce( o.parentChoId, 0 ) > 0 then p.operatorCity else o.operatorCity end as rollup_operatorCity, case when coalesce( o.parentChoId, 0 ) > 0 then p.operatorState else o.operatorState end as rollup_operatorState, case when coalesce( o.parentChoId, 0 ) > 0 then p.operatorZipCode else o.operatorZipCode end as rollup_operatorZipCode, case when coalesce( o.parentChoId, 0 ) > 0 then p.operatorAddress else o.operatorAddress end as rollup_operatorAddress, case when coalesce( o.parentChoId, 0 ) > 0 then p.operatorCounty else o.operatorCounty end as rollup_operatorCounty, case when coalesce( o.parentChoId, 0 ) > 0 then p.hasSubGroups else o.hasSubGroups end as rollup_hasSubGroups, case when coalesce( o.parentChoId, 0 ) > 0 then 'O' else cho.cho_type end as rollup_cho_type from #_clientMappedOperatorsTable# opr with (nolock) inner join tbl_CHO_Operators cho with (nolock) on cho.cho_id = opr.fsl_choId inner join tbl_DW_1fsOperators#_tableSuffix# o with (nolock) on o.choId = opr.fsl_choid and o.ownerId = opr.ownerId and o.ownerType = opr.fsltablecode left outer join tbl_DW_1fsOperators#_tableSuffix# p with (nolock) on p.choId = o.parentChoId and p.ownerId = o.ownerId and p.ownerType = o.ownerType left outer join tbl_OPR_Segments seg with (nolock) on seg.segmentId = cho.cho_segmentId left outer join territoriesByLibrary ter on ter.fsl_choId = cho.cho_Id and ter.ownerId = opr.ownerId and ter.ownerType = opr.fsltablecode where opr.fsl_choId > 0 and opr.ownerId = @ownerId and opr.fsltablecode = @ownerType -- and cho.cho_type <> 'U' ), /* use tbl_DW_ProductHierarchy_MFRn instead productLevels as ( select s.ownerId, s.fsl_tablecode as ownerType, s.skuId, p.prodId, p.product, p.shortDesc, c.categoryId, c.catnum, c.category, l.prodLine_id, l.productCode productlineCode, l.productLine, bgtCategory.attrId budgetCategoryId, bgtCategory.attrDescription budgetCategory, b.brand_Id brandId, b.brand, b.brandOwnerId, b.brandType brandOwnerType from tbl_PRD_Skus s with (nolock) left outer join tbl_MFR_Brands b with (nolock) on b.brand_Id = s.brandId left outer join tbl_PRD_Products p with (nolock) on p.prodId = s.prodId and p.ownerId = s.ownerId and p.fsl_tablecode = s.fsl_tablecode left outer join tbl_ORG_Attributes bgtCategory with (nolock) on bgtCategory.ownerId = s.ownerId and bgtCategory.fsl_tablecode = s.fsl_tablecode and bgtCategory.attrId = case when s.bgtCatOverride = 'Y' then s.bgtCatId else p.bgtCatId end left outer join tbl_PRD_Categories c with (nolock) on c.categoryId = p.categoryId and c.ownerId = p.ownerId and c.fsl_tablecode = p.fsl_tablecode left outer join tbl_MFR_ProductLines l with (nolock) on l.prodline_id = c.prodLineId and l.mfr_id = c.ownerId and 'MFR' = c.fsl_tablecode where s.ownerId = and s.fsl_tablecode = ), */ data as ( select operators.ownerId, operators.ownerType, operators.operatorName, operators.cho_id, operators.cho_type, operators.segmentId, operators.operatorSegment, operators.channelSegment, operators.mainSegment, operators.subSegment, purchases.purchasingYear, purchases.purchasingMonth, purchases.forecast_qty, purchases.forecast_amt, purchases.forecast_exp, purchases.skuId, purchases.sku, purchases.skuDesc, agreements.requestId, agreements.agreementOwnerChoId, agreements.agreementOwnerName, prd.prodId, prd.product, prd.shortDesc, prd.categoryId, prd.catnum, prd.category, prd.prodline_id, prd.productLineCode, prd.productLine, prd.productHierarchyPath, operators.territoryId, operators.territoryName, operators.territoryPath, operators.broker_id, operators.broker_name, operators.broker_office_id, operators.broker_office_name, operators.operatorAddress, operators.operatorCity, operators.operatorState, operators.operatorZipCode, operators.operatorCounty, purchases.dstId, purchases.dstName, purchases.parentDstId, purchases.parentDstName, operators.hasSubGroups, prd.budgetCategoryId, prd.budgetCategory, prd.brandId, prd.brand, prd.brandOwnerId, prd.brandOwnerType, agreements.sourceType, prd.ofsCategoryId, prd.ofsCategory, operators.rollup_cho_id, operators.rollup_operatorName, operators.rollup_operatorCity, operators.rollup_operatorState, operators.rollup_operatorZipCode, operators.rollup_operatorAddress, operators.rollup_operatorCounty, operators.rollup_hasSubGroups, operators.rollup_cho_type from operators inner join purchases on purchases.cho_id = operators.cho_id and purchases.ownerId = operators.ownerId and purchases.ownerType = operators.ownerType left outer join agreements on agreements.ownerId = purchases.ownerId and agreements.ownerType = purchases.ownerType and agreements.requestId = purchases.requestId left outer join tbl_DW_ProductHierarchy#_tableSuffix# prd on prd.ownerId = purchases.ownerId and prd.ownerType = purchases.ownerType and prd.skuId = purchases.skuId ) insert into tbl_DW_OperatorAnalyzer#_tableSuffix# ( ownerId, ownerType, operatorName, cho_id, cho_type, segmentId, operatorSegment, channelSegment, mainSegment, subSegment, purchasingMonth, forecast_qty, forecast_amt, forecast_exp, skuId, sku, skuDesc, requestId, agreementOwnerChoId, agreementOwnerName, prodId, product, shortDesc, categoryId, catnum, category, prodline_id, productLineCode, productLine, productHierarchyPath, territoryId, territoryName, territoryPath, broker_id, broker_name, broker_office_id, broker_office_name, operatorAddress, operatorCity, operatorState, operatorZipCode, operatorCounty, dstId, dstName, parentDstId, parentDstName, hasSubGroups, budgetCategoryId, budgetCategory, brandId, brand, brandOwnerId, brandOwnerType, sourceType, ofsCategoryId, ofsCategory, rollup_cho_id, rollup_operatorName, rollup_operatorCity, rollup_operatorState, rollup_operatorZipCode, rollup_operatorAddress, rollup_operatorCounty, rollup_hasSubGroups, rollup_cho_type, businessOwnerUserId, businessOwnerFullName ) select ownerId, ownerType, operatorName, cho_id, cho_type, segmentId, operatorSegment, channelSegment, mainSegment, subSegment, convert( datetime, convert( varchar, purchasingMonth ) + '/01/' + convert( varchar, purchasingYear )) purchasingMonth, forecast_qty, forecast_amt, forecast_exp, skuId, sku, skuDesc, requestId, agreementOwnerChoId, agreementOwnerName, prodId, product, shortDesc, categoryId, catnum, category, prodline_id, productLineCode, productLine, productHierarchyPath, territoryId, territoryName, territoryPath, broker_id, broker_name, broker_office_id, broker_office_name, operatorAddress, operatorCity, operatorState, operatorZipCode, operatorCounty, dstId, dstName, parentDstId, parentDstName, hasSubGroups, budgetCategoryId, budgetCategory, brandId, brand, brandOwnerId, brandOwnerType, sourceType, ofsCategoryId, ofsCategory, rollup_cho_id, rollup_operatorName, rollup_operatorCity, rollup_operatorState, rollup_operatorZipCode, rollup_operatorAddress, rollup_operatorCounty, rollup_hasSubGroups, rollup_cho_type, ( select top 1 agreements.businessOwnerUserId from tbl_DW_OperatorAgreements#_tableSuffix# agreements where agreements.cho_id = data.agreementOwnerChoId order by agreements.businessOwnerUserId ) businessOwnerUserId, ( select top 1 agreements.businessOwnerFirstName + ' ' + agreements.businessOwnerLastName as businessOwnerFullName from tbl_DW_OperatorAgreements#_tableSuffix# agreements where agreements.cho_id = data.agreementOwnerChoId order by agreements.businessOwnerUserId ) businessOwnerFullName from data ---> with agreementOwner as ( select oa.ownerId, oa.ownerType, oa.agreementOwnerChoId, oa.agreementOwnerName, oa.purchasingMonth, oa.skuId, oa.sku, oa.skuDesc, oa.prodId, oa.product, oa.shortDesc, oa.categoryId, oa.catnum, oa.category, oa.prodline_id, oa.productLineCode, oa.productLine, oa.productHierarchyPath, oa.requestId, oa.sourceType, oa.dstId, oa.dstName, oa.parentDstId, oa.parentDstName, sum( oa.forecast_qty ) forecast_qty, sum( oa.forecast_amt ) forecast_amt, sum( oa.forecast_exp ) forecast_exp, sum( oa.totalPrice_retail ) as totalPrice_retail, sum( oa.totalPrice_net ) as totalPrice_net, oa.rollup_cho_id, oa.rollup_operatorName, oa.rollup_operatorCity, oa.rollup_operatorState, oa.rollup_operatorZipCode, oa.rollup_operatorAddress, oa.rollup_operatorCounty, oa.rollup_hasSubGroups, oa.rollup_cho_type, oa.rollup_accountOwnerFullName from tbl_DW_OperatorAnalyzer#_tableSuffix# oa with (nolock) inner join tbl_DW_1fsOperators#_tableSuffix# o on o.choId = oa.cho_id and o.locationLevelPurchasing = 'Y' where oa.cho_id = agreementOwnerChoId and oa.ownerId = and oa.ownerType = group by oa.ownerId, oa.ownerType, oa.agreementOwnerChoId, oa.agreementOwnerName, oa.purchasingMonth, oa.skuId, oa.sku, oa.skuDesc, oa.prodId, oa.product, oa.shortDesc, oa.categoryId, oa.catnum, oa.category, oa.prodline_id, oa.productLineCode, oa.productLine, oa.productHierarchyPath, oa.requestId, oa.sourceType, oa.dstId, oa.dstName, oa.parentDstId, oa.parentDstName, oa.rollup_cho_id, oa.rollup_operatorName, oa.rollup_operatorCity, oa.rollup_operatorState, oa.rollup_operatorZipCode, oa.rollup_operatorAddress, oa.rollup_operatorCounty, oa.rollup_hasSubGroups, oa.rollup_cho_type, oa.rollup_accountOwnerFullName ), loc as ( select ownerId, ownerType, agreementOwnerChoId, purchasingMonth, skuId, sku, skuDesc, requestId, sourceType, dstId, dstName, parentDstId, parentDstName, sum( forecast_qty ) forecast_qty, sum( forecast_amt ) forecast_amt, sum( forecast_exp ) forecast_exp, sum( totalPrice_retail ) as totalPrice_retail, sum( totalPrice_net ) as totalPrice_net from tbl_DW_OperatorAnalyzer#_tableSuffix# with (nolock) where cho_id <> agreementOwnerChoId and ownerId = and ownerType = group by ownerId, ownerType, agreementOwnerChoId, purchasingMonth, skuId, sku, skuDesc, requestId, sourceType, dstId, dstName, parentDstId, parentDstName ) insert into tbl_DW_OperatorAnalyzer#_tableSuffix# ( ownerId, ownertype, cho_id, cho_type, agreementOwnerChoId, agreementOwnerName, purchasingMonth, skuId, sku, skuDesc, prodId, product, shortDesc, categoryId, catnum, category, prodline_id, productLineCode, productLine, productHierarchyPath, requestId, sourceType, forecast_qty, forecast_amt, forecast_exp, totalPrice_retail, totalPrice_net, dstId, dstName, parentDstId, parentDstName, averageClientVolume, rollup_cho_id, rollup_operatorName, rollup_operatorCity, rollup_operatorState, rollup_operatorZipCode, rollup_operatorAddress, rollup_operatorCounty, rollup_hasSubGroups, rollup_cho_type, rollup_accountOwnerFullName, businessOwnerUserId, businessOwnerFullName ) select a.ownerId, a.ownerType, 0 as cho_id, 'I' as cho_type, a.agreementOwnerChoId, a.agreementOwnerName, a.purchasingMonth, a.skuId, a.sku, a.skuDesc, a.prodId, a.product, a.shortDesc, a.categoryId, a.catnum, a.category, a.prodline_id, a.productLineCode, a.productLine, a.productHierarchyPath, a.requestId, a.sourceType, a.forecast_qty - coalesce( l.forecast_qty, 0 ) forecast_qty, a.forecast_amt - coalesce( l.forecast_amt, 0 ) forecast_amt, a.forecast_exp - coalesce( l.forecast_exp, 0 ) forecast_exp, a.totalPrice_retail - coalesce( l.totalPrice_retail, 0 ) totalPrice_retail, a.totalPrice_net - coalesce( l.totalPrice_net, 0 ) totalPrice_net, a.dstId, a.dstName, a.parentDstId, a.parentDstName, 0, a.rollup_cho_id, a.rollup_operatorName, a.rollup_operatorCity, a.rollup_operatorState, a.rollup_operatorZipCode, a.rollup_operatorAddress, a.rollup_operatorCounty, a.rollup_hasSubGroups, a.rollup_cho_type, a.rollup_accountOwnerFullName, ( select top 1 agreements.businessOwnerUserId from tbl_DW_OperatorAgreements#_tableSuffix# agreements where agreements.cho_id = a.agreementOwnerChoId order by agreements.businessOwnerUserId ) businessOwnerUserId, ( select top 1 agreements.businessOwnerFirstName + ' ' + agreements.businessOwnerLastName as businessOwnerFullName from tbl_DW_OperatorAgreements#_tableSuffix# agreements where agreements.cho_id = a.agreementOwnerChoId order by agreements.businessOwnerUserId ) businessOwnerFullName from agreementOwner a left outer join loc l on l.agreementOwnerChoId = a.agreementOwnerChoId and l.purchasingMonth = a.purchasingMonth and l.skuId = a.skuId and l.sku = a.sku and l.skuDesc = a.skuDesc and l.requestId = a.requestId and l.sourceType = a.sourceType and l.ownerId = a.ownerId and l.ownerType = a.ownerType and coalesce( l.dstId, 0 ) = coalesce( a.dstId, 0 ) where l.requestId is null or ( coalesce( l.forecast_qty, 0 ) <> a.forecast_qty ) update oa set oa.averageClientVolume = u.averageClientVolume, oa.accountOwnerOwnerId = u.accountOwnerOwnerId, oa.accountOwnerOwnerType = u.accountOwnerOwnerType, oa.accountOwnerUserId = u.accountOwnerUserId, oa.accountOwnerFullName = u.accountOwnerFullName from tbl_DW_OperatorAnalyzer#_tableSuffix# oa inner join tbl_DW_1fsUniverse#_tableSuffix# u on u.operatorChoId = oa.cho_id update oa set oa.rollup_accountOwnerFullName = u.accountOwnerFullName from tbl_DW_OperatorAnalyzer#_tableSuffix# oa inner join tbl_DW_1fsUniverse#_tableSuffix# u on u.operatorChoId = oa.rollup_cho_id update oa set oa.masterRank = mr.masterRank from tbl_DW_OperatorAnalyzer#_tableSuffix# oa inner join tbl_DW_OperatorAnalyzerRollup mr on mr.cho_id = oa.cho_Id with classifications as ( select c.cho_id, l.abbreviation classificationAbbr, l.name classification, l.sortRank, l.classificationId, row_number() over ( partition by c.cho_id order by l.sortRank ) as rn from tbl_OPR_ClientOperators o with (nolock) inner join tbl_CHO_Operators c with (nolock) on c.cho_id = o.fsl_choId inner join tbl_OPR_Classifications l with (nolock) on l.classificationId = o.classificationId and l.ownerId = o.ownerId and l.ownerType = o.fsltablecode where o.ownerId = and o.fsltablecode = ) update oa set oa.classificationAbbr = c.classificationAbbr, oa.classification = c.classification, oa.classificationId = c.classificationId from tbl_DW_OperatorAnalyzer#_tableSuffix# oa inner join classifications c on c.cho_id = oa.cho_id where c.rn = 1 and oa.ownerId = and oa.ownerType = truncate table tbl_DW_PurchasingMonths#_tableSuffix# insert into tbl_DW_PurchasingMonths#_tableSuffix# ( ownerType, ownerId, purchasingMonth, analyzerRecords ) select ownerType, ownerId, purchasingMonth, count(*) analyzerRecords from tbl_DW_OperatorAnalyzer#_tableSuffix# with (nolock) where ownerId = and ownerType = group by ownerType, ownerId, purchasingMonth order by ownertype, ownerId, purchasingMonth update o set o.locationLevelPurchasing = case when exists ( select * from tbl_DW_OperatorAnalyzer#_tableSuffix# oa where oa.ownerId = o.ownerId and oa.ownerType = o.ownerType and oa.agreementOwnerChoId = o.choId and oa.agreementOwnerChoId <> oa.cho_id and oa.cho_id <> 0 ) then 'Y' else 'N' end, o.purchasing = case when exists ( select * from tbl_DW_OperatorAnalyzer#_tableSuffix# oa where oa.ownerId = o.ownerId and oa.ownerType = o.ownerType and oa.cho_id = o.choId ) then 'Y' else 'N' end, o.hasSubGroups = case when exists ( select * from tbl_DW_1fsSubGroups#_tableSuffix# sg with (nolock) where sg.ownerId = o.ownerId and sg.ownerType = o.ownerType and o.choId in ( sg.memberGroupChoId, sg.parentGroupChoId )) then 'true' else 'false' end from tbl_DW_1fsOperators#_tableSuffix# o where o.ownerId = and o.ownerType = update o set o.locationLevelPurchasingType = '1' from tbl_DW_1fsOperators#_tableSuffix# o where o.ownerId = and o.ownerType = and locationLevelPurchasing = 'Y' update o set o.locationLevelPurchasing = 'Y', o.locationLevelPurchasingType = '2' from tbl_DW_1fsOperators#_tableSuffix# o where o.ownerId = and o.ownertype = and exists ( select * from tbl_DW_1fsSubGroups#_tableSuffix# sg inner join tbl_DW_1fsOperators#_tableSuffix# mg on mg.choId = sg.memberGroupChoId and mg.ownerId = sg.ownerId and mg.ownerType = sg.ownerType where sg.subGroupChoId = o.choId and sg.subGroupType in ( 'G', 'C', 'S' ) and mg.locationLevelPurchasing = 'Y' ) update mg set mg.locationLevelPurchasing = 'Y', mg.locationLevelPurchasingType = '3' from tbL_DW_1fsOperators#_tableSuffix# mg where mg.operatorType in ( 'CMC', 'GPO' ) and mg.locationLevelPurchasing = 'N' and mg.ownerId = and mg.ownerType = and exists ( select * from tbl_DW_1fsOperatorMemberships#_tableSuffix# mm inner join tbl_DW_OperatorAnalyzer#_tableSuffix# oa on oa.cho_id = mm.memberChoId and oa.ownerId = mm.ownerId and oa.ownerType = mm.ownerType where mm.ownerId= mg.ownerId and mm.ownerType = mg.ownerType and mm.memberGroupChoId = mg.choId and oa.agreementOwnerChoId = mg.choId ) update o set o.locationLevelPurchasing = 'Y', o.locationLevelPurchasingType = '4' from tbl_DW_1fsOperators#_tableSuffix# o where o.operatorType = 'PARENT' and o.locationLevelPurchasing = 'N' and o.purchasing = 'Y' and o.ownerId = and o.ownertype = and exists ( select * from tbl_DW_OperatorAnalyzer#_tableSuffix# u with (nolock) where u.ownerId = o.ownerId and u.ownerType = o.ownerType and u.rollup_cho_id = o.choId and u.agreementOwnerChoId = u.rollup_cho_id and u.cho_Id <> u.agreementOwnerChoId ) and exists ( select * from tbl_DW_1fsOperators#_tableSuffix# u with (nolock) where u.ownerId = o.ownerId and u.ownerType = o.ownerType and u.parentChoId = o.choId and u.purchasing = 'Y' ) update oa set oa.locationLevelAgreementOwner = case when o.locationLevelPurchasing = 'Y' then 1 else 0 end from tbl_DW_OperatorAnalyzer#_tableSuffix# oa left outer join tbl_DW_1fsOperators#_tableSuffix# o on o.ownerId = oa.ownerId and o.ownerType = oa.ownerType and o.choId = oa.agreementOwnerChoId where oa.ownerId = and oa.ownerType = update h set h.operatorPurchasing = case when exists ( select * from tbl_DW_OperatorAnalyzer#_tableSuffix# oa where oa.ownerId = h.ownerId and oa.ownerType = h.ownerType and oa.skuId = h.skuId ) then 'Y' else 'N' end from tbl_DW_ProductHierarchy#_tableSuffix# h where h.ownerId = and h.ownerType = update d set d.operatorPurchasing = case when exists ( select * from tbl_DW_OperatorAnalyzer#_tableSuffix# oa where oa.ownerId = d.ownerId and oa.ownerType = d.ownerType and oa.dstId = d.dstId ) then 'Y' else 'N' end from tbl_DW_1fsDistributors#_tableSuffix# d where d.ownerId = and d.ownerType = with contractsWithPurchasing as ( select distinct oa.ownerId, oa.ownerType, oa.contractId from tbl_DW_OperatorAgreements#_tableSuffix# oa with (nolock) inner join tbl_TPM_Requests r with (nolock) on r.ownerId = oa.ownerId and r.ownerType = oa.ownerType and r.contractId = oa.contractId where exists ( select * from tbl_DW_OperatorAnalyzer#_tableSuffix# a with (nolock) where a.requestId = r.requestId and a.ownerId = r.ownerId and a.ownerType = r.ownerType ) ) update a set a.operatorPurchasing = case when p.contractId is not null then 'Y' else 'N' end from tbl_DW_OperatorAgreements#_tableSuffix# a left outer join contractsWithPurchasing p on p.ownerId = a.ownerId and p.ownerType = a.ownerType and p.contractId = a.contractId

Operator Rollup

update a set a.rollup_cho_id = case when coalesce( o.parentChoId, 0 ) > 0 then o.parentChoId else o.choId end, a.rollup_operatorName = case when coalesce( o.parentChoId, 0 ) > 0 then p.operatorName else o.operatorName end, a.rollup_operatorCity = case when coalesce( o.parentChoId, 0 ) > 0 then p.operatorCity else o.operatorCity end, a.rollup_operatorState = case when coalesce( o.parentChoId, 0 ) > 0 then p.operatorState else o.operatorState end, a.rollup_operatorZipCode = case when coalesce( o.parentChoId, 0 ) > 0 then p.operatorZipCode else o.operatorZipCode end, a.rollup_operatorAddress = case when coalesce( o.parentChoId, 0 ) > 0 then p.operatorAddress else o.operatorAddress end, a.rollup_operatorCounty = case when coalesce( o.parentChoId, 0 ) > 0 then p.operatorCounty else o.operatorCounty end, a.rollup_hasSubGroups = case when coalesce( o.parentChoId, 0 ) > 0 then p.hasSubGroups else o.hasSubGroups end, a.rollup_cho_type = case when coalesce( o.parentChoId, 0 ) > 0 then 'O' else a.cho_type end from tbl_DW_OperatorAnalyzer#_tableSuffix# a inner join tbl_DW_1fsOperators#_tableSuffix# o with (nolock) on o.choId = a.cho_id and o.ownerId = a.ownerId and o.ownerType = a.ownerType left outer join tbl_DW_1fsOperators#_tableSuffix# p with (nolock) on p.choId = o.parentChoId and p.ownerId = o.ownerId and p.ownerType = o.ownerType where a.ownerId = and a.ownerType = and 1 = 2 update a set a.rollup_cho_id = a.cho_Id, a.rollup_cho_type = 'I' from tbl_DW_OperatorAnalyzer#_tableSuffix# a where a.ownerId = and a.ownerType = and a.cho_id = 0 select name from sys.tables where name = with purchaseHistory as ( select cho_id, skuId, purchasingMonth, forecast_qty, row_number() over( partition by cho_id, skuId order by purchasingMonth desc ) historyRank, count(*) over( partition by cho_id, skuId ) monthsPurchasing, min( purchasingMonth ) over ( partition by cho_id, skuId ) as firstPurchaseMonth, sum( forecast_qty ) over( partition by cho_id, skuId ) totalPurchaseQty from tbl_DW_OperatorAnalyzer#_tableSuffix# oa with (nolock) where oa.forecast_qty > 0 ), lastPurchase as ( select cho_id, skuId, firstPurchaseMonth, purchasingMonth lastPurchaseMonth, '#qmd_unitOfMeasure.unitOfMeasurePlural#' lastPurchaseUOM, forecast_qty lastPurchaseQty, monthsPurchasing, totalPurchaseQty from purchaseHistory where historyRank = 1 ) update o set o.firstPurchaseMonth = lp.firstPurchaseMonth, o.lastPurchaseMonth = lp.lastPurchaseMonth, o.lastPurchaseUOM = lp.lastPurchaseUOM, o.lastPurchaseQty = lp.lastPurchaseQty, o.totalPurchaseQty = lp.totalPurchaseQty from tbl_DW_Opportunities#_tableSuffix# o inner join lastPurchase lp on lp.skuId = o.skuId and lp.cho_id = o.operatorOneFsId and lp.lastPurchaseMonth >= o.estOrderDate where o.operatorOneFsId > 0 truncate table tbl_DW_OperatorSegments#_tableSuffix# insert into tbl_DW_OperatorSegments#_tableSuffix# ( ownerType, ownerId, operatorSegmentId, operatorSegment ) select distinct ownerType, ownerId, SegmentId operatorSegmentId, operatorSegment from tbl_DW_OperatorAnalyzer#_tableSuffix# with (nolock) where segmentId > 0 and ownerId = and ownerType = truncate table tbl_DW_OperatorTerritories#_tableSuffix# insert into tbl_DW_OperatorTerritories#_tableSuffix# ( ownerType, ownerId, territoryId, territoryName, territoryPath ) select fsl_tablecode as ownerType, ownerId, territoryId, name as territoryName, territoryPath from tbl_TER_TErritories ter with (nolock) where exists ( select * from tbl_DW_OperatorAnalyzer#_tableSuffix# dw with (nolock) where dw.territoryId = ter.territoryId ) and ter.ownerId = and ter.fsl_tablecode = insert into tbl_DW_OperatorAgreements#_tableSuffix# ( ownerType, ownerId, cho_id, cho_name, assignedContractNbr, contractName, startDate, endDate, contractId, businessOwnerUserId, businessOwnerFirstName, businessOwnerLastName, businessOwnerEmail ) select ct.ownerType, ct.ownerId, cho.cho_id, cho.cho_name, ct.assignedContractNbr, ct.contractName, min( r.termStart ) startDate, max( r.termEnd ) endDate, ct.contractId, ct.businessOwnerUserId, bo.firstName as businessOwnerFirstName, bo.lastName as businessOwnerLastName, bo.email as businessOwnerEmail from tbl_TPM_Contracts ct with (nolock) inner join tbl_TPM_Requests r with (nolock) on r.ownerId = ct.ownerId and r.ownerType = ct.ownerType and r.contractId = ct.contractId and r.status = 'APPROVED' and r.volumeGenerator = 'Y' left outer join tbl_fspro_members bo with (nolock) on bo.ownerId = ct.ownerId and bo.fsl_tablecode = ct.ownerType and bo.fspro_userId = ct.businessOwnerUserId inner join #_clientMappedOperatorsTable# opr with (nolock) on opr.ownerId = ct.ownerId and opr.fsltablecode = ct.ownerType and opr.operatorId = ct.partnerId inner join tbl_CHO_Operators cho with (nolock) on cho.cho_id = opr.fsl_choId where ct.partnerType = 'OPR' and ct.ownerId = and ct.ownerType = group by ct.ownerId, ct.ownerType, cho.cho_id, cho.cho_name, ct.assignedContractNbr, ct.contractName, ct.contractId, ct.businessOwnerUserId, bo.firstName, bo.lastName, bo.email order by ownerType, ownerId, cho_id, assignedContractNbr ---> update o set o.beneficiary = 'Y' from tbl_DW_1fsOperators#_tableSuffix# o where exists ( select * from tbl_DW_OperatorAgreements#_tableSuffix# oa where oa.ownerId = o.ownerId and oa.ownerType =o.ownerType and oa.cho_id = o.choId ) and o.ownerId = and o.ownerType = truncate table tbl_DW_OperatorAnalyzerClaimDetails#_tableSuffix# -- this is in lieu of checking the volumeGenerator field because it needs to match the data that was capture, not specifically the state of the current volume generator field with generators as ( select mfr_id as ownerId, 'MFR' as ownerType, rebateContractId as requestId from tbl_MFR_TPF_Detail#_tpfSuffix# with (nolock) where forecast_status = 'C' and mfr_id = and rebateContractId > 0 and tPartnerType = 'OPR' union all select ownerId, ownerType, requestId from tbl_TPM_Requests with (nolock) where ownerId = and ownerType = and volumeGenerator = 'Y' ) select distinct ownerId, ownerType, requestId into ###_generatorsTable# from generators; create index idx_#_generatorsTable# on ###_generatorsTable#(ownerType,ownerId,requestId); insert into tbl_DW_OperatorAnalyzerClaimDetails#_tableSuffix# ( ownerType, ownerId, cho_id, cho_name, assignedContractNbr, contractName, claimInvoice, claimDate, periodStart, periodEnd, claimant, skuId, sku, skuDesc, itemQty, locationName, contractId ) select ct.ownerType, ct.ownerId, coalesce( cho.cho_id, 0 ) cho_id, case when cho.cho_id is not null then cho.cho_name else 'Unmapped Operator' end cho_name, ct.assignedContractNbr, ct.contractName, c.claimInvoice, c.claimDate, -- coalesce( cc.firstDate, c.claimPeriodStart ) periodStart, case when cc.lastDate is not null then cc.firstDate when c.claimPeriodEnd is not null then c.claimPeriodStart else dateadd( month, -1, format( c.claimDate, 'MM/01/yyyy' )) end as periodStart, -- coalesce( cc.lastDate, c.claimPeriodEnd ) periodEnd, case when cc.lastDate is not null then cc.lastDate when c.claimPeriodEnd is not null then c.claimPeriodEnd else dateadd( day, -1, format( c.claimDate, 'MM/01/yyyy' )) end as periodEnd, -- oprc.companyName as claimant, case when c.partnerType = 'OPR' then oprc.companyName else cdrc.cdr_dstName end as claimant, s.skuId, s.sku, s.skuDesc, sum( pod.#_itemQtyCol# ) itemQty, coalesce( opr.companyName, 'Unknown Location' ) as locationName, ct.contractId from tbl_TPM_Claims c with (nolock) left outer join tbl_OPR_ClientOperators oprc with (nolock) on oprc.ownerId = c.ownerId and oprc.fsltablecode = c.ownerType and oprc.operatorId = c.partnerId and 'OPR' = c.partnerType left outer join tbl_CDR_Distributors cdrc with (nolock) on cdrc.ownerId = c.ownerId and cdrc.fsl_tablecode = c.ownerType and cdrc.cdr_recordId = c.partnerId and 'CDR' = c.partnerType inner join tbl_TPM_ClaimContracts cc with (nolock) on cc.reconciled = 'Y' and cc.ownerId = c.ownerId and cc.ownerType = c.ownerType and cc.claimId = c.claimId inner join tbl_TPM_Requests r with (nolock) on r.requestId = cc.requestId and r.ownerId = cc.ownerId and r.ownerType = cc.ownerType inner join tbl_TPM_Contracts ct with (nolock) on ct.contractId = r.contractId and ct.ownerId= r.ownerId and ct.ownerType = r.ownerType inner join tbl_TPM_ProofOfDelivery pod with (nolock) on pod.ownerId = cc.ownerId and pod.ownerType = cc.ownerType and pod.contractNbr = cc.contractNbr and pod.claimId = cc.claimId and pod.status != 'DENIED' left outer join tbl_OPR_CLientOperators opr with (nolock) on opr.ownerId = pod.ownerId and opr.fsltablecode = pod.ownerType and opr.operatorId = pod.operatorId left outer join tbl_CHO_Operators cho with (nolock) on cho.cho_id = opr.fsl_choId inner join tbl_PRD_Skus s with (nolock) on s.skuId = pod.skuId and s.ownerId = pod.ownerId and s.fsl_tablecode = pod.ownertype inner join ###_generatorsTable# as g with (nolock) on g.ownerId = r.ownerId and g.ownerType = r.ownerType and g.requestId = r.requestId where c.ownerId = and c.ownerType = -- and r.requestId in ( select rebateContractId from ###_generatorsTable# ) -- and r.volumeGenerator = 'Y' group by ct.ownerType, ct.ownerId, ct.assignedContractNbr, ct.contractName, c.claimInvoice, c.claimDate, cc.firstDate, c.claimPeriodStart, cc.lastDate, c.claimPeriodEnd, oprc.companyName, cho.cho_id, cho.cho_name, s.skuId, s.sku, s.skuDesc, opr.companyName, ct.contractId, c.partnerType, cdrc.cdr_dstName if object_id( 'tempdb.dbo.###_generatorsTable#', 'U' ) is not null drop table ###_generatorsTable#; ---> truncate table tbl_DW_OperatorExclusions#_tableSuffix# with exclusions as ( select c.ownerType, c.ownerId, c.claimId, c.claimDate, c.claimRef, c.claimInvoice, c.partnerType as claimantPartnerType, case when c.partnerType = 'OPR' then oprc.operatorId else cdrc.cdr_recordId end as claimantPartnerId, case when c.partnerType = 'OPR' then oprc.companyName else cdrc.cdr_dstname end as claimantPartnerName, opr1.operatorId as beneficiaryOperatorId1, opr1.companyName as beneficiaryOperatorName1, opr1.fsl_choId as choId1, ct1.contractId as contractId1, ct1.assignedContractNbr as assignedContractNbr1, ct1.contractName as contractName1, opr1.companyName as benficiaryName1, case when opr2.operatorId is null then 0 else opr2.operatorId end as beneficiaryOperatorId2, case when opr2.operatorId is null then 'Unknown Beneficiary' else opr2.companyName end as beneficiaryOperatorName2, case when opr2.operatorId is null then 0 else opr2.fsl_choId end as choId2, case when ct2.contractId is null then 0 else ct2.contractId end as contractId2, case when ct2.contractId is null then 'Unknown' else ct2.assignedContractNbr end as assignedContractNbr2, case when ct2.contractId is null then 'Unknown Agreement' else ct2.contractName end as contractName2, case when opr2.operatorId is null then 'Unknown Beneficiary' else opr2.companyName end as benficiaryName2, pod.totalRebate, pod.itemQty from tbl_TPM_ProofOfDelivery pod with (nolock) inner join tbl_TPM_ClaimContracts cc with (nolock) on cc.ownerId = pod.ownerId and cc.ownerType = pod.ownerType and cc.claimId = pod.claimId and cc.contractNbr = pod.contractNbr inner join tbl_TPM_Claims c with (nolock) on c.ownerId = cc.ownerId and c.ownerType = cc.ownerType and c.claimId = cc.claimId left outer join tbl_OPR_ClientOperators oprc with (nolock) on oprc.ownerId = c.ownerId and oprc.fsltablecode = c.ownerType and oprc.operatorId = c.partnerId and 'OPR' = c.partnerType left outer join tbl_CDR_Distributors cdrc with (nolock) on cdrc.ownerId = c.ownerId and cdrc.fsl_tablecode = c.ownerType and cdrc.cdr_recordId = c.partnerId and 'CDR' = c.partnerType inner join tbl_TPM_Requests r1 with (nolock) on r1.ownerId = cc.ownerId and r1.ownerType = cc.ownerType and r1.requestId = cc.requestId inner join tbl_TPM_Contracts ct1 with (nolock) on ct1.ownerId = r1.ownerId and ct1.ownerType = r1.ownerType and ct1.contractId = r1.contractId inner join tbl_OPR_ClientOperators opr1 with (nolock) on opr1.ownerId = ct1.ownerId and opr1.fsltablecode = ct1.ownerType and opr1.operatorId = ct1.partnerId LEFT OUTER join tbl_TPM_Requests r2 with (nolock) on r2.ownerId = pod.ownerId and r2.ownerType = pod.ownerType and r2.requestId = pod.flagLinkId LEFT OUTER join tbl_TPM_Contracts ct2 with (nolock) on ct2.ownerId = r2.ownerId and ct2.ownerType = r2.ownerType and ct2.contractId = r2.contractId LEFT OUTER join tbl_OPR_ClientOperators opr2 with (nolock) on opr2.ownerId = ct2.ownerId and opr2.fsltablecode = ct2.ownerType and opr2.operatorId = ct2.partnerId where c.ownerId = and c.ownerType = and pod.flagged = 'Y' and ( pod.flagComment like 'Excluded Operator%' or pod.flagComment like 'Already Claimed%' ) and pod.flagLinkType = 'TPM' and ct1.partnerType = 'OPR' and coalesce( ct2.partnerType, 'OPR' ) = 'OPR' and cc.reconciled= 'Y' ) insert into tbl_DW_OperatorExclusions#_tableSuffix# ( ownerType, ownerId, claimId, claimDate, claimRef, claimInvoice, claimantPartnerType, claimantPartnerId, claimantPartnerName, beneficiaryOperatorId1, beneficiaryOperatorName1, choId1, contractId1, assignedContractNbr1, contractName1, beneficiaryOperatorId2, beneficiaryOperatorName2, choId2, contractId2, assignedContractNbr2, contractName2, claimDollars, claimCases ) select ownerType, ownerId, claimId, claimDate, claimRef, claimInvoice, claimantPartnerType, claimantPartnerId, claimantPartnerName, beneficiaryOperatorId1, beneficiaryOperatorName1, choId1, contractId1, assignedContractNbr1, contractName1, beneficiaryOperatorId2, beneficiaryOperatorName2, choId2, contractId2, assignedContractNbr2, contractName2, sum( totalRebate ) claimDollars, sum( itemQty ) claimCases from exclusions group by ownerType, ownerId, claimId, claimDate, claimRef, claimInvoice, claimantPartnerType, claimantPartnerId, claimantPartnerName, beneficiaryOperatorId1, beneficiaryOperatorName1, choId1, contractId1, assignedContractNbr1, contractName1, beneficiaryOperatorId2, beneficiaryOperatorName2, choId2, contractId2, assignedContractNbr2, contractName2 update tbl_TPM_Settings set updateAnalyzer = 0 where ownerId = and ownerType = select batchId, status, message, runSeconds from tbl_EAI_inboundBatches with (nolock) where fileFormat = '$OperatorAnalyzerETL' and batchTime >= '11/16/2020' insert into tbl_EAI_inboundBatchMessages( batchId, lineNumber, importId, message, messageLevel ) values ( , null, null, , 'INFO' )
#htmleditformat( _timingMessage )# IF OBJECT_ID('#_clientMappedOperatorsTable#', 'U') IS NULL BEGIN select fsltablecode, ownerId, operatorId, fsl_choId, territoryId, inferredPurchasing into #_clientMappedOperatorsTable# from tbl_OPR_CLientOperators with (nolock) where ownerId = and fsltablecode = and fsl_choId > 0 order by fsltablecode, ownerId, operatorId; alter table #_clientMappedOperatorsTable# add constraint PK_#_clientMappedOperatorsTable# primary key ( fsltablecode, ownerId, operatorId ); END IF OBJECT_ID('#_clientMappedOperatorsTable#', 'U') IS NOT NULL DROP TABLE #_clientMappedOperatorsTable#; delete tpf from tbl_MFR_TPF_Detail#_tpfSuffix# tpf where not exists ( select * from tbl_TPM_Requests r with (nolock) where r.ownerId = tpf.mfr_id and r.ownerType = 'MFR' and r.requestId = tpf.rebateContractId ) and tpf.forecast_status = 'C' and nullif( rebateContractId, 0 ) is not null