/* GMS Active Assignee - detail (DatawarehouseNCM) Fact: dbo.FactGMSActiveAssignee Report: frmRptGMSActiveAssignee / GMSAssigneeRpt.rpt (SP pr_GMSActiveAssignees) One row per international assignment (TEFileNo + TEAssignmentID), snapshot at load. Status ('Active','Authorized','Complete','Repatriation','Pending','Assignment Not Approved') is precomputed at load. Filters are optional (NULL / '' = all). */ DECLARE @CorpCode varchar(15) = NULL; -- e.g. 'ABBE' DECLARE @Division varchar(30) = NULL; -- e.g. 'North America' DECLARE @Policy varchar(50) = NULL; DECLARE @Status varchar(30) = NULL; -- e.g. 'Active' DECLARE @StartDate datetime = NULL; -- filter on AssignEstStartDate DECLARE @EndDate datetime = NULL; SELECT f.CorpCode, f.FormalName, f.TEFileNo, f.TEAssignmentID, f.TELastName, f.TEFirstName, f.TEName, f.DivisionName, f.Department, f.EmpNumber, f.GradeLevel, f.ReloPolicy, f.AssignmentType, f.HomeCountry, f.HostCountry, f.DPTWorkCity, f.DSTWorkCity, f.PayrollCountry, f.MaritalStatus, f.Accom, f.DPTTESalary, f.DSTTESalaryCurrency, f.DPTTESalaryEffective, f.AssignEstStartDate, f.AssignEstEndDate, f.ExtEndDate, f.WorkStartDate, f.AssignCancelDate, f.MoveComplete, f.RepatDate, f.Status FROM dbo.FactGMSActiveAssignee f WHERE (@CorpCode IS NULL OR @CorpCode = '' OR f.CorpCode = @CorpCode) AND (@Division IS NULL OR @Division = '' OR f.DivisionName = @Division) AND (@Policy IS NULL OR @Policy = '' OR f.ReloPolicy = @Policy) AND (@Status IS NULL OR @Status = '' OR f.Status = @Status) AND (@StartDate IS NULL OR f.AssignEstStartDate >= @StartDate) AND (@EndDate IS NULL OR f.AssignEstStartDate <= @EndDate) ORDER BY f.CorpCode, f.TELastName, f.TEFirstName, f.AssignEstStartDate;