update fsr
set fsr.fseCountryId = cntry.countryId
from tbl_IMPORT_FoodServiceRewardsOperators fsr
inner join tbl_UT_Countries cntry
on cntry.country2dCode = case when fsr.fsrBusCountry = 'USA' then 'US' when fsr.fsrBusCountry = 'CAN' then 'CA' else 'OT' end
where fsr.eai_batchId =
and fsr.ownerId =
and fsr.ownerType =
update fsr
set fsr.fseSegmentId = css.clientSegId
from tbl_IMPORT_FoodServiceRewardsOperators fsr
inner join tbl_OPR_ClientSegments cs
on cs.ownerId = fsr.ownerId
and cs.fsl_tablecode = fsr.ownerType
and cs.description = fsr.fsrOperatorSegment
and cs.clientSegLevel = 1
inner join tbl_OPR_ClientSegments css
on css.ownerId = cs.ownerId
and css.fsl_tablecode = css.fsl_tablecode
and css.parentclientsegid = cs.clientSegId
and css.clientSegLevel = 2
and css.description = fsr.fsrOperatorSubSegment
where fsr.eai_batchId =
and fsr.ownerId =
and fsr.ownerType =
set nocount on
update fsr
set fsr.fseOperatorId = opr.operatorId,
fsr.fseOperatorStatus = 'U',
fsr.fseTerritoryId = opr.territoryId
from tbl_IMPORT_FoodServiceRewardsOperators fsr
inner join tbl_OPR_ClientOperators opr
on opr.ownerId = fsr.ownerId
and opr.fsltablecode = fsr.ownerType
and opr.FoodServiceRewardsId = fsr.fsrBusinessId
where fsr.eai_batchId =
and fsr.ownerId =
and fsr.ownerType =
and fsr.fsrStatus <> 'I'
select rowsAffected = @@rowCount
set nocount off
set nocount on
update fsr
set fseTerritoryId = ter.territoryId
from tbl_IMPORT_FoodServiceRewardsOperators fsr
inner join (
select z3.zipcode_3d, ter.territoryId, ter.name
from tbl_TER_Territories ter
inner join tbl_TER_ZipCodeLink zcl
on zcl.territoryId = ter.territoryId
inner join tbl_UT_ZipCodes_3d z3
on z3.zip3d_id = zcl.zip3d_id
where ter.ownerId =
and ter.fsl_tablecode =
) ter
on ter.zipcode_3d = left( fsr.fsrBusPostalCode, 3 )
inner join tbl_UT_Countries cntry
on cntry.countryId = fsr.fseCountryId
and cntry.country2dCode = 'US'
where fsr.eai_batchId =
and fsr.ownerId =
and fsr.ownerType =
and fsr.fseTerritoryId is null
select rowsAffected = @@rowCount
set nocount off
select * from qmd_classifications order by sortRank desc
insert into tbl_OPR_ClientOperators (
ownerId, fsltablecode, FoodServiceRewardsId, companyName, dbaName, address, address2, city, state, zipcode, countryId, territoryId, oprSegment, distrib1, crmactive , setMethod, recordSource, classificationId
)
select distinct ownerId, ownerType, fsrBusinessId, fsrBusName, fsrBusUnitName, fsrBusAddr1, fsrBusAddr2, fsrBusCity, fsrBusState, fsrBusPostalCode, fseCountryId, fseTerritoryId, coalesce( fseSegmentId, 0), coalesce( fseDistributorId, 0 ), 'Y', 2, '#_recordSource#', #_classificationId#
from tbl_IMPORT_FoodServiceRewardsOperators fsr
where fsr.eai_batchId =
and fsr.ownerId =
and fsr.ownerType =
and fsr.fseOperatorId is null
and fsr.fsrStatus <> 'I'
set nocount on
update fsr
set fsr.fseOperatorId = opr.operatorId,
fsr.fseOperatorStatus = 'A'
from tbl_IMPORT_FoodServiceRewardsOperators fsr
inner join tbl_OPR_ClientOperators opr
on opr.ownerId = fsr.ownerId
and opr.fsltablecode = fsr.ownerType
and opr.FoodServiceRewardsId = fsr.fsrBusinessId
where fsr.eai_batchId =
and fsr.ownerId =
and fsr.ownerType =
and fsr.fseOperatorId is null
and fsr.fsrStatus <> 'I'
select rowsAffected = @@rowCount
set nocount off
update fsr
set fsr.fseContactId = m.fspro_userid,
fsr.fseContactStatus = 'U'
from tbl_IMPORT_FoodServiceRewardsOperators fsr
inner join tbl_FSPro_Members m
on m.ownerId = fsr.ownerId
and m.fsl_tablecode = fsr.ownerType
and m.orgId = fsr.fseOperatorId
and m.orgType = 'OPR'
and m.foodServiceRewardsId = fsr.fsrOperatorId
where fsr.eai_batchId =
and fsr.ownerId =
and fsr.ownerType =
and fsr.fseOperatorId is not null
and fsr.fsrStatus <> 'I'
insert into tbl_FSPro_Members (
ownerId, fsl_tablecode, foodservicerewardsid, orgType, orgId, salutation, firstName, lastName, email, emailYesNo, title, officeAddrSame, originalSource
)
select ownerId, ownerType, fsrOperatorId, 'OPR', fseOperatorId, fsrPrefix, fsrFirstName, fsrLastName, fsrEmailAddress, case when fsrEmailDNM = 'Y' then 1 else 0 end, fsrJobTitle, 1, '#_recordSource#'
from tbl_IMPORT_FoodServiceRewardsOperators fsr
where fsr.eai_batchId =
and fsr.ownerId =
and fsr.ownerType =
and fsr.fseOperatorId is not null
and fseContactId is null
and fsrStatus <> 'I'
update fsr
set fsr.fseContactId = m.fspro_userid,
fsr.fseContactStatus = 'A'
from tbl_IMPORT_FoodServiceRewardsOperators fsr
inner join tbl_FSPro_Members m
on m.ownerId = fsr.ownerId
and m.fsl_tablecode = fsr.ownerType
and m.orgId = fsr.fseOperatorId
and m.orgType = 'OPR'
and m.foodServiceRewardsId = fsr.fsrOperatorId
where fsr.eai_batchId =
and fsr.ownerId =
and fsr.ownerType =
and fsr.fseOperatorId is not null
and fsr.fseContactId is null
update tbl_IMPORT_FoodServiceRewardsOperators
set tmpFirstSponsorRedemption = convert( datetime, left( fsrFirstSponsorRedemption, 2 ) + '/' + substring( fsrFirstSponsorRedemption, 3, 2 ) + '/' + right( fsrFirstSponsorRedemption, 4 ))
where fsrFirstSponsorRedemption is not null and fsrFirstSponsorRedemption <> ''
and ownerId =
and ownerType =
and eai_batchId =
update tbl_IMPORT_FoodServiceRewardsOperators
set tmpLastSponsorRedemption = convert( datetime, left( fsrLastSponsorRedemption, 2 ) + '/' + substring( fsrLastSponsorRedemption, 3, 2 ) + '/' + right( fsrLastSponsorRedemption, 4 ))
where fsrLastSponsorRedemption is not null and fsrLastSponsorRedemption <> ''
and ownerId =
and ownerType =
and eai_batchId =
update opr
set opr.foodservicerewardsLastDate = activity.fsrLastDate,
opr.foodservicerewardsFirstDate = activity.fsrFirstDate
from tbl_OPR_CLientOperators opr
inner join (
select ownerId, ownerType, fseOperatorId, fsrBusinessId, min( tmpFirstSponsorRedemption) fsrFirstDate, max( tmpLastSponsorRedemption ) fsrLastDate
from tbl_IMPORT_FoodServiceRewardsOperators
where eai_batchId =
and fseOperatorId is not null and tmpLastSponsorRedemption is not null
group by ownerId, ownerType, fseOperatorId, fsrBusinessId
) activity
on activity.fseOperatorId = opr.operatorId
and activity.fsrBusinessId = opr.foodSErviceRewardsId
and activity.ownerId = opr.ownerId
and activity.ownerType = opr.fsltablecode
where opr.ownerId =
and opr.fsltablecode =
update tbl_OPR_ClientOperators
set crmActive = 'N'
where ownerId =
and fsltablecode =
and ( FoodServiceRewardsLastDate is null
or datepart( year, foodservicerewardslastDate ) < 2009 )
FoodServiceRewards Operator File Load Results
select tRank, territoryName, sum( case when fseOperatorStatus = 'A' then 1 else 0 end ) added, sum( case when fseOperatorStatus = 'U' then 1 else 0 end ) updated
from ( select distinct fsr.ownerId, fsr.ownerType, case when t.territoryId is not null then 10 else 20 end tRank, fsr.eai_batchId, t.territoryId, coalesce( t.name, 'Unassigned' ) territoryName, fsr.fseOperatorId, fsr.fseOperatorStatus from tbl_IMPORT_FoodServiceRewardsOperators fsr
left join tbl_TER_Territories t
on t.territoryId = fsr.fseTerritoryId
and t.ownerId = fsr.ownerId
and t.fsl_tablecode = fsr.ownerType
where fsr.eai_batchId =
and fsr.ownerId =
and fsr.ownerType =
) fsr
where fsr.eai_batchId =
and fsr.ownerId =
and fsr.ownerType =
group by tRank, territoryName
order by trank, territoryName
Sales Territory
Added
Updated
#territoryName#
#lsnumberformat( added )#
#lsnumberformat( updated )#
select distinct cntry.country, left( fsrBusPostalCode, 3 ) postalCodePrefix
from tbl_IMPORT_FoodSErviceRewardsOperators fsr
inner join tbl_UT_Countries cntry
on cntry.countryId = fsr.fseCountryId
where fsr.eai_batchId =
and fsr.ownerId =
and fsr.ownerType =
and fseTerritoryId is null and fsrStatus <> 'I'
and fseOperatorStatus = 'A'
and fseOperatorId is not null
order by cntry.country