/* GMS Active Assignee - transactional (ReloAction) Report: frmRptGMSActiveAssignee -> GMSAssigneeRpt.rpt Source SP: dbo.pr_GMSActiveAssignees Run this in the ReloAction (transactional) database. It reproduces the report SP: one assignment grain (TEFileNo + TEAssignmentID). Two parts UNIONed: - assignment-service rows (excludes standalone CPE/CST), plus - standalone CPE/CST rows for TEs with no other service in the window. Status is computed as of @EndDate. Set @CorpCode='' / @Division=' ' / @Policy=' ' for "all". */ DECLARE @CorpCode varchar(20) = ''; -- '' = all clients DECLARE @Division varchar(50) = ' '; -- ' ' = all divisions DECLARE @Policy varchar(50) = ' '; -- ' ' = all policies DECLARE @StartDate datetime = '1900-01-01'; DECLARE @EndDate datetime = CAST(GETDATE() AS date); -- Part 1: assignment services (non CPE/CST) SELECT DISTINCT vwouter.CorpCode, vwouter.FormalName, vwouter.tefileno, vwouter.teassignmentid, vwouter.tefirstname, vwouter.telastname, vwouter.tefirstname + ' ' + vwouter.telastname AS TEName, vwouter.DivisionName, vwouter.Department, vwouter.EmpNumber, vwouter.Descriptor4, vwouter.Descriptor5, vwouter.GradeLevel, vwouter.CorpAssignmentType AS AssignmentType, vwouter.ReloPolicy, vwouter.AssignEstStartDate, vwouter.AssignEstEndDate, vwouter.WorkStartDate, ext.ExtEndDate, vwouter.AssignCancelDate, vwouter.MoveComplete, (SELECT MAX(a.assignestenddate) FROM dbo.vwTEAssignments a WHERE a.tefileno = vwouter.tefileno AND a.CorpAssignmentType = 'Repatriation') AS RepatDate, CASE WHEN vwouter.HomeCountry IS NULL THEN vwouter.DPTWorkCountry ELSE vwouter.HomeCountry END AS HomeCountry, CASE WHEN vwouter.HostCountry IS NULL THEN vwouter.DSTWorkCountry ELSE vwouter.HostCountry END AS HostCountry, vwouter.DPTWorkCountry, vwouter.DSTWorkCountry, vwouter.DPTWorkCity, vwouter.DSTWorkCity, vwouter.PayrollCountry, vwouter.MaritalStatus, vwouter.DPTTESalary, vwouter.DPTTESalaryEffective, vwouter.DSTTESalaryCurrency, (SELECT CONVERT(varchar(30), CONVERT(varchar(1), ai.PartnerAccompany) + ' spouse, ') + CONVERT(varchar(30), CONVERT(varchar(3), ai.NumChildDep) + ' children, ') + CONVERT(varchar(30), CONVERT(varchar(3), ai.NumOtherDep) + ' dependents ') FROM dbo.tblTEAssignmentInfo ai WHERE ai.tefileno = vwouter.tefileno AND ai.teassignmentid = vwouter.teassignmentid) AS Accom, CASE WHEN (vwouter.MoveComplete IS NOT NULL AND vwouter.MoveComplete <= @EndDate) THEN 'Complete' ELSE CASE WHEN (SELECT TOP 1 t2.assignmenttype FROM dbo.tblteassignmentinfo t1 JOIN dbo.tblcorpassignment t2 ON t1.corpassignmentid = t2.corpassignmentid WHERE t1.tefileno = vwouter.tefileno AND t2.assignmenttype = 'Repatriation') = 'Repatriation' THEN 'Repatriation' ELSE CASE WHEN (vwouter.AssignEstStartDate > @EndDate) THEN 'Authorized' ELSE CASE WHEN ((vwouter.AssignEstEndDate <= @EndDate AND (ext.ExtEndDate <= @EndDate OR ext.ExtEndDate IS NULL)) OR (vwouter.AssignCancelDate <= @EndDate AND vwouter.AssignCancelDate IS NOT NULL)) THEN 'Complete' ELSE 'Active' END END END END AS Status FROM dbo.vwtefullservicelist vwouter LEFT OUTER JOIN dbo.vwteassignmentextensions ext ON vwouter.teassignmentid = ext.teassignmentid WHERE (vwouter.CorpCode = @CorpCode OR (@CorpCode = '' AND vwouter.InternalOrTestClient <> 1)) AND vwouter.DisplayType = 'C' AND vwouter.DomesticIntl = 'I' AND (vwouter.DivisionName = @Division OR @Division = ' ') AND (vwouter.ReloPolicy = @Policy OR @Policy = ' ') AND vwouter.CancelDate IS NULL AND (vwouter.MoveComplete IS NULL OR vwouter.MoveComplete >= @StartDate) AND ((vwouter.AssignEstStartDate <= @EndDate AND vwouter.AssignEstEndDate >= @StartDate) OR (vwouter.AuthDate <= @EndDate AND vwouter.AssignEstStartDate > @EndDate) OR (ext.ExtEndDate >= @StartDate)) AND vwouter.teassignmentid <> 0 AND vwouter.REMService = 0 AND vwouter.VisaService = 0 AND vwouter.componentservice = 0 AND vwouter.corpassignmenttype <> 'Repatriation' AND vwouter.servicecode <> 'CPE' AND vwouter.servicecode <> 'CST' UNION -- Part 2: standalone CPE/CST (TEs with no other qualifying service) SELECT DISTINCT vwouter.CorpCode, vwouter.FormalName, vwouter.tefileno, vwouter.teassignmentid, vwouter.tefirstname, vwouter.telastname, vwouter.tefirstname + ' ' + vwouter.telastname, vwouter.DivisionName, vwouter.Department, vwouter.EmpNumber, vwouter.Descriptor4, vwouter.Descriptor5, vwouter.GradeLevel, vwouter.CorpAssignmentType, vwouter.ReloPolicy, CASE WHEN vwouter.AssignEstStartDate IS NULL THEN vwouter.AuthDate ELSE vwouter.AssignEstStartDate END, CASE WHEN vwouter.AssignEstEndDate IS NULL THEN vwouter.AuthDate ELSE vwouter.AssignEstEndDate END, vwouter.WorkStartDate, CASE WHEN vwouter.AssignEstEndDate IS NULL THEN vwouter.AuthDate ELSE vwouter.AssignEstEndDate END, vwouter.AssignCancelDate, vwouter.MoveComplete, (SELECT MAX(a.assignestenddate) FROM dbo.vwTEAssignments a WHERE a.tefileno = vwouter.tefileno AND a.CorpAssignmentType = 'Repatriation'), CASE WHEN vwouter.HomeCountry IS NULL THEN vwouter.DPTWorkCountry ELSE vwouter.HomeCountry END, CASE WHEN vwouter.HostCountry IS NULL THEN vwouter.DSTWorkCountry ELSE vwouter.HostCountry END, vwouter.DPTWorkCountry, vwouter.DSTWorkCountry, vwouter.DPTWorkCity, vwouter.DSTWorkCity, ' ', vwouter.MaritalStatus, vwouter.DPTTESalary, vwouter.DPTTESalaryEffective, vwouter.DSTTESalaryCurrency, (SELECT CONVERT(varchar(30), CONVERT(varchar(1), ai.PartnerAccompany) + ' spouse, ') + CONVERT(varchar(30), CONVERT(varchar(3), ai.NumChildDep) + ' children, ') + CONVERT(varchar(30), CONVERT(varchar(3), ai.NumOtherDep) + ' dependents ') FROM dbo.tblTEAssignmentInfo ai WHERE ai.tefileno = vwouter.tefileno AND ai.teassignmentid = vwouter.teassignmentid), CASE WHEN vwouter.status <> 'Assignment Not Approved' THEN 'Pending' ELSE vwouter.status END FROM dbo.vwtefullservicelist vwouter WHERE (vwouter.CorpCode = @CorpCode OR (@CorpCode = '' AND vwouter.InternalOrTestClient <> 1)) AND (vwouter.servicecode = 'CPE' OR vwouter.servicecode = 'CST') AND vwouter.DomesticIntl = 'I' AND vwouter.AuthDate <= @EndDate AND (vwouter.DivisionName = @Division OR @Division = ' ') AND (vwouter.ReloPolicy = @Policy OR @Policy = ' ') AND (vwouter.CompleteDate IS NULL OR (vwouter.status = 'Assignment Not Approved' AND vwouter.CompleteDate >= @StartDate AND vwouter.CompleteDate <= @EndDate)) AND vwouter.CancelDate IS NULL AND vwouter.DisplayType = 'C' AND (SELECT COUNT(vwinner.servicecode) FROM dbo.vwtefullservicelist vwinner WHERE vwinner.tefileno = vwouter.tefileno AND vwinner.servicecode <> 'CPE' AND vwinner.servicecode <> 'CST' AND vwinner.visaservice = 0 AND vwinner.remservice = 0 AND vwinner.authdate <= @EndDate) = 0 ORDER BY CorpCode, telastname, tefirstname;