/* Authorization Report — detail (DatawarehouseNCM) BackOffice: frmClientSrvcSummary (lblRptType = "Auths") Crystal: ClientAuthorizations.rpt Sources: DimMain.AuthDate initial auth (vwTEInitialAuthorization) DimTEFullServiceList service lines (vwTEFullServiceList) DimInitials consultant name from PrimaryContact FactAuthActivity division and move type Profit center is omitted. @ActiveOnly = 1: in-process TEs (no move complete, no component/REM, open services). @ActiveOnly = 0: initial auth date in @StartDate / @EndDate. Service rows: DisplayType C. Cancelled services are excluded, matching Crystal. TE name is DimMain.TEName (Last, First). */ DECLARE @CorpCode varchar(12) = NULL; -- NULL or '' = all clients DECLARE @ActiveOnly bit = 0; DECLARE @StartDate date = '2025-01-01'; DECLARE @EndDate date = '2025-12-31'; DECLARE @ParentRollup bit = 0; ;WITH QualifyingTE AS ( SELECT s.TEFileNo, MIN(s.AuthDate) AS InitialAuthDate, MAX(s.PrimaryContact) AS PrimaryContact, MAX(s.MoveComplete) AS MoveComplete FROM dbo.DimTEFullServiceList s WHERE @ActiveOnly = 1 AND s.DisplayType = 'C' AND ISNULL(s.ComponentService, 0) = 0 AND s.MoveComplete IS NULL AND ISNULL(s.REMService, 0) = 0 AND s.CompleteDate IS NULL AND s.CancelDate IS NULL AND ( NULLIF(@CorpCode, '') IS NULL OR (@ParentRollup = 1 AND s.ParentCompany = @CorpCode) OR (@ParentRollup = 0 AND s.corpcode = @CorpCode) ) GROUP BY s.TEFileNo UNION ALL SELECT m.TEFileNo, m.AuthDate AS InitialAuthDate, pc.PrimaryContact, pc.MoveComplete FROM dbo.DimMain m OUTER APPLY ( SELECT MAX(s.PrimaryContact) AS PrimaryContact, MAX(s.MoveComplete) AS MoveComplete FROM dbo.DimTEFullServiceList s WHERE s.TEFileNo = m.TEFileNo ) pc WHERE @ActiveOnly = 0 AND m.AuthDate IS NOT NULL AND CAST(m.AuthDate AS date) >= @StartDate AND CAST(m.AuthDate AS date) <= @EndDate AND ( NULLIF(@CorpCode, '') IS NULL OR (@ParentRollup = 1 AND m.ParentCompany = @CorpCode) OR (@ParentRollup = 0 AND m.CorpCode = @CorpCode) ) ) SELECT te.TEFileNo, te.InitialAuthDate, m.CorpCode, div.DivisionName, m.TEName, m.FormalName, m.ParentCompany, m.EmpNumber, te.PrimaryContact, te.MoveComplete, sp.FirstName AS SrvcProviderFirstName, sp.LastName AS SrvcProviderLastName, fs.TEServiceID, fs.ServiceCode, fs.ServiceDesc AS ServiceDescription, fs.AuthDate AS ServiceAuthDate, fs.CompleteDate, fs.CancelDate, fs.ServiceType, fs.ServiceStatus AS Status, fs.SrvcProvider, fs.REMService, fs.ComponentService, fs.DisplayType, mt.MoveTypeDescription, mt.DomesticIntl FROM QualifyingTE te INNER JOIN dbo.DimMain m ON m.TEFileNo = te.TEFileNo OUTER APPLY ( SELECT TOP (1) fa.DimDivisionID, fa.DimMoveTypeID FROM dbo.FactAuthActivity fa WHERE fa.DimMainID = m.DimMainID ORDER BY fa.FactAuthActivityID ) fa LEFT JOIN dbo.DimDivision div ON div.DimDivisionID = fa.DimDivisionID LEFT JOIN dbo.DimMoveType mt ON mt.DimMoveTypeID = fa.DimMoveTypeID OUTER APPLY ( SELECT TOP (1) i.FirstName, i.LastName FROM dbo.DimInitials i WHERE i.Initials = te.PrimaryContact ORDER BY i.DimInitialsID ) sp LEFT JOIN dbo.DimTEFullServiceList fs ON fs.TEFileNo = te.TEFileNo AND fs.DisplayType = 'C' AND fs.CancelDate IS NULL ORDER BY m.FormalName, te.InitialAuthDate, te.TEFileNo, fs.ServiceCode;