/* Active Transferee Report — detail (DatawarehouseNCM) BackOffice: frmRptActiveTECount / ActiveTECount.rpt Source: FactActiveTransferee (+ optional FactTEAccountDOTA via 02). UserType: 1=Internal, 3=External, 0/NULL=All. IsVIP = ExecPgm. Run on DatawarehouseNCM after populate. */ DECLARE @CorpCode varchar(15) = NULL; DECLARE @ParentRollup bit = 0; DECLARE @ProfitCenter varchar(10) = NULL; DECLARE @CAC varchar(10) = NULL; DECLARE @SrvcProvider varchar(4) = NULL; DECLARE @AuthorizedBy int = 0; DECLARE @UserType smallint = 0; DECLARE @Months int = 0; DECLARE @VIPOnly bit = 0; DECLARE @IncludeREM bit = 1; DECLARE @CutoffDate datetime = DATEADD(month, -@Months, CAST(GETDATE() AS date)); SELECT f.TEFileNo, f.CorpCode, f.ParentCompany, f.FormalName, f.TELastName, f.TEFirstName, f.DivisionName, f.ReloPolicy, f.MoveTypeDescription, f.CostCenter, f.DisplayType, f.DomesticIntl, f.UserType, f.IsVIP, f.AuthDate, f.ServiceCode, f.ServiceType, f.Status, f.Description, f.SrvcProvider, f.SrvcFirstName, f.SrvcLastName, f.ProfitCenter, f.CAC, f.ContactID, f.AuthLastName, f.AuthFirstName, f.REMService, f.ComponentService, f.ServiceGroupCode, f.MoveComplete FROM dbo.FactActiveTransferee f WHERE ( NULLIF(@CorpCode, '') IS NULL OR (@ParentRollup = 1 AND f.ParentCompany = @CorpCode) OR (@ParentRollup = 0 AND f.CorpCode = @CorpCode) ) AND (NULLIF(@ProfitCenter, '') IS NULL OR f.ProfitCenter = @ProfitCenter) AND (NULLIF(@CAC, '') IS NULL OR f.CAC = @CAC) AND (NULLIF(@SrvcProvider, '') IS NULL OR f.SrvcProvider = @SrvcProvider) AND (@AuthorizedBy = 0 OR f.ContactID = @AuthorizedBy) AND (@UserType = 0 OR f.UserType = @UserType) AND (@Months = 0 OR f.AuthDate <= @CutoffDate) AND (@VIPOnly = 0 OR f.IsVIP = 1) AND (@IncludeREM = 1 OR ISNULL(f.REMService, 0) = 0) ORDER BY f.CorpCode, f.TEFileNo, f.ServiceCode;