/* All the tables referenced here are already in DW */ /* Authorization Report — supplier management extract BackOffice: frmClientSrvcSummary (lblRptType = "Auths") Crystal: ClientAuthorizations.rpt (or ClientAuthorizations{CorpCode}.rpt) Parameterless extract for reporting / data warehouse loading. Report filters (auth date range, active-only, client, parent rollup, etc.) are applied downstream. */ SELECT auth.TEFileNo, auth.AuthDate AS InitialAuthDate, auth.CorpCode, auth.DivisionName, auth.TELastName, auth.TEFirstName, cl.FormalName, cl.ParentCompany, m.EmpNumber, m.PrimaryContact, m.MoveComplete, sp.FirstName AS SrvcProviderFirstName, sp.LastName AS SrvcProviderLastName, sp.ProfitCenter, pc.Name AS ProfitCenterName, fs.TEServiceID, fs.ServiceCode, fs.Description AS ServiceDescription, fs.AuthDate AS ServiceAuthDate, fs.CompleteDate, fs.CancelDate, fs.ServiceType, fs.Status, fs.SrvcProvider, fs.REMService, fs.ComponentService, fs.DisplayType, mt.moveTypeDescription, mt.DomesticIntl FROM dbo.vwTEInitialAuthorization auth INNER JOIN dbo.tblTEMain1 m ON auth.TEFileNo = m.TEFileNo INNER JOIN dbo.tblCorpClient cl ON auth.CorpCode = cl.CorpCode LEFT JOIN dbo.tblInitials sp ON m.PrimaryContact = sp.Initials LEFT JOIN dbo.tblRAProfitCenters pc ON sp.ProfitCenter = pc.ProfitCenter LEFT JOIN dbo.vwTEFullServiceList fs ON auth.TEFileNo = fs.TEFileNo AND fs.DisplayType = 'C' LEFT JOIN dbo.tblMoveTypes mt ON m.MoveType = mt.MoveType;