/* Active Transferee Report — detail (ReloAction) BackOffice: frmRptActiveTECount Crystal: ActiveTECount.rpt (skip ActiveTECountClosed / UNIP for now) Universe: vwActiveTransferees (= open services: Cancel/Complete null, DisplayType=C, InternalOrTestClient<>1) excluding tblTESBTold. Filters mirror Crystal params. UserType: 1=Internal, 3=External, 0/NULL=All. IsVIP = tblTEMain1.ExecPgm. DOTA summary: optional — see 02 Active TE Count DOTA Summary.sql (needs RAC populate into tblTEDOTARecap at runtime in BO; DW uses FactTEAccountDOTA). Run on ReloAction. */ DECLARE @CorpCode varchar(15) = NULL; -- NULL/'' = all; else CorpCode or ParentCompany DECLARE @ParentRollup bit = 0; DECLARE @ProfitCenter varchar(10) = NULL; -- NULL/'' = all (PC) DECLARE @CAC varchar(10) = NULL; -- NULL/'' = all (CAC) DECLARE @SrvcProvider varchar(4) = NULL; -- NULL/'' = all DECLARE @AuthorizedBy int = 0; -- 0 = all ContactID DECLARE @UserType smallint = 0; -- 0=all, 1=internal, 3=external DECLARE @Months int = 0; -- AuthDate older than N months (0 = no cutoff) DECLARE @VIPOnly bit = 0; DECLARE @IncludeREM bit = 1; -- 0 = exclude REMService=1 DECLARE @CutoffDate datetime = DATEADD(month, -@Months, CAST(GETDATE() AS date)); SELECT RTRIM(a.TEFileNo) AS TEFileNo, RTRIM(a.CorpCode) AS CorpCode, RTRIM(a.ParentCompany) AS ParentCompany, a.FormalName, a.TELastName, a.TEFirstName, a.DivisionName, a.ReloPolicy, a.moveTypeDescription AS MoveTypeDescription, a.CostCenter, a.DisplayType, a.DomesticIntl, a.UserType, CAST(ISNULL(m1.ExecPgm, 0) AS bit) AS IsVIP, a.AuthDate, a.ServiceCode, a.ServiceType, a.Status, a.Description, a.SrvcProvider, a.SrvcFirstName, a.SrvcLastName, a.ProfitCenter, a.CAC, a.ContactID, a.AuthLastName, a.AuthFirstName, a.REMService, a.ComponentService, a.ServiceGroupCode, a.MoveComplete FROM dbo.vwActiveTransferees a INNER JOIN dbo.tblTEMain1 m1 ON a.TEFileNo = m1.TEFileNo WHERE a.TEFileNo NOT IN (SELECT TEFileNo FROM dbo.tblTESBTold) AND ( NULLIF(@CorpCode, '') IS NULL OR (@ParentRollup = 1 AND a.ParentCompany = @CorpCode) OR (@ParentRollup = 0 AND a.CorpCode = @CorpCode) ) AND (NULLIF(@ProfitCenter, '') IS NULL OR a.ProfitCenter = @ProfitCenter) AND (NULLIF(@CAC, '') IS NULL OR a.CAC = @CAC) AND (NULLIF(@SrvcProvider, '') IS NULL OR a.SrvcProvider = @SrvcProvider) AND (@AuthorizedBy = 0 OR a.ContactID = @AuthorizedBy) AND (@UserType = 0 OR a.UserType = @UserType) AND (@Months = 0 OR a.AuthDate <= @CutoffDate) AND (@VIPOnly = 0 OR ISNULL(m1.ExecPgm, 0) = 1) AND (@IncludeREM = 1 OR ISNULL(a.REMService, 0) = 0) ORDER BY a.CorpCode, a.TEFileNo, a.ServiceCode;