Staff Assignments - Reset with Term Change

/** Routine to reassign staff assignments in bulk, using variation of same code that works day-to-day **/

/** Use this login to allow correct operation when connecting Management Studio remotely **/
EXEC AS Login = '422X\ExecMSCRM'

IF object_id('tempdb..#OppUpdate') IS NOT NULL
DROP TABLE tempdb..#OppUpdate

-- Create Temp Table OppUpdate to hold GUIDS for Opportunities with Entry Terms that need updating
DECLARE @Yr nvarchar(4), @Term int
SET @Yr = '2011'  -- Put year value that change records have to be greater than
SET @Term = '0'    -- Put term ordinal value that is first term in beginning year for changes

SELECT OpportunityID, o.a422_rel_entrytermidName INTO #OppUpdate
FROM Opportunity AS o
  INNER JOIN a422_dom_EntryTerm AS et ON et.A422_dom_entrytermId = o.a422_rel_entrytermid
  -- Add if address or geomarket needed to qualify
  -- INNER JOIN Contact AS c ON c.ContactID = o.ContactID
WHERE et.A422_Year > @Yr
  AND NOT (et.A422_TermOrdinal < @Term and et.A422_Year = @Yr)

--Set owner to geographically assigned staff
SET  owninguser = COALESCE(gz.owninguser, gs.owninguser)
FROM OpportunityBase AS o
            INNER JOIN #OppUpdate AS ou ON ou.OpportunityId = o.OpportunityId
            INNER JOIN ContactBase AS cb ON o.ContactId = cb.ContactId
            INNER JOIN CustomerAddressBase AS a ON cb.ContactId = a.ParentId AND a.AddressNumber = 1
            LEFT JOIN a422_ZipCodeExtensionBase AS z ON z.a422_actualzip = SUBSTRING(a.PostalCode, 1, 5)
            LEFT JOIN a422_GeomarketBase AS gz ON z.a422_GeomarketId = gz.a422_GeomarketId
            LEFT JOIN a422_Geomarket AS gs ON a.StateOrProvince = gs.a422_ShortName

-- Update Opportunity Owner with Organization Assignment
SET opp.OwningUser = org.a422_rel_staffassignedid
FROM a422_PersonOrgExtensionBase AS po
           INNER JOIN Account AS org ON po.a422_organizationid = org.AccountId
           INNER JOIN OpportunityBase AS opp ON po.a422_personid = opp.ContactId
           INNER JOIN #OppUpdate AS ou ON ou.OpportunityId = opp.OpportunityId
WHERE org.a422_rel_staffassignedid Is Not Null
           AND po.a422_Activeorg = 1
           AND opp.OwningUser <> org.a422_rel_staffassignedid
           AND opp.DeletionStateCode = 0
           AND opp.StateCode = 0

/** Update Owner for records that were set to Null because of no assignment **/
UPDATE Opportunity
SET  OwningUser =
  SELECT SystemUserID
  FROM SystemUser
  WHERE FullName = 'C422 Admin'
FROM Opportunity

DROP TABLE tempdb..#OppUpdate

/** It is a little confusing (I have to remind myself each time), but the year would actually be 2010 for that list. That is, if 2011 should be included, then the year will be > 2010. The logic is to find all terms where Term Year > the value you specify and Term Ordinal is >= the value you specify. Usually at this time of year, it would be 2011 because we would be changing 2012 and beyond. I’m not sure why they are reassigning the 2011 semesters that have already passed. **/

/**Spring 2011,Fall 2011,Spring 2012,Fall 2012 and all terms forward **/