/* Vendor Usage — Household Goods Suppliers (transactional / ReloAction) BackOffice: frmRptVendorUsage (cboVendors index 1) Crystal: VendorUsage_HHG.rpt Source view: dbo.vwVendorsHHG Date filter: ROLOrderDate (order sent / accepted; workflow tracking for auto shipment). Pm-ServiceType: 0=Both, 1=Domestic, 2=International (form radios). Pm-Alaska ("Only Alaska Dept/Dest Moves") filters DPTWorkState / DSTWorkState. */ DECLARE @StartDate datetime = '2026-01-01'; DECLARE @EndDate datetime = '2026-01-31'; DECLARE @CorpCode varchar(15) = ''; -- '' = all DECLARE @ParentRollup bit = 0; DECLARE @SrvcProvider varchar(4) = ''; -- '' = all DECLARE @VendorParentID int = 0; -- 0 = all DECLARE @ServiceType smallint = 0; -- 0=Both, 1=Domestic, 2=International DECLARE @AlaskaOnly bit = 0; SELECT 'HHG' AS UsageReportType, v.TEFileNo, v.CorpCode, cl.ParentCompany, v.FormalName, te.TELastName, te.TEFirstName, v.ServiceType, v.SrvcProvider, v.SrvcFirstName, v.SrvcLastName, v.ROLOrderDate AS OrderDate, v.VendorParentID, v.CompanyName, v.SubName AS CompanySubName, v.HHGAutoShipment, fs.DPTWorkState, fs.DSTWorkState FROM dbo.vwVendorsHHG v INNER JOIN dbo.tblTEMain1 te ON te.TEFileNo = v.TEFileNo INNER JOIN dbo.tblCorpClient cl ON v.CorpCode = cl.CorpCode LEFT JOIN dbo.vwTEFullServiceList fs ON fs.TEFileNo = v.TEFileNo AND fs.ServiceCode = 'HHG' AND fs.CancelDate IS NULL WHERE v.ROLOrderDate BETWEEN @StartDate AND @EndDate AND ( (@ParentRollup = 0 AND (@CorpCode = '' OR v.CorpCode = @CorpCode)) OR (@ParentRollup = 1 AND (@CorpCode = '' OR cl.ParentCompany = @CorpCode OR v.CorpCode = @CorpCode)) ) AND (@SrvcProvider = '' OR v.SrvcProvider = @SrvcProvider) AND (@VendorParentID = 0 OR v.VendorParentID = @VendorParentID) AND (@ServiceType = 0 OR v.ServiceType = @ServiceType) AND ( @AlaskaOnly = 0 OR fs.DPTWorkState IN ('AK', 'Alaska') OR fs.DSTWorkState IN ('AK', 'Alaska') ) ORDER BY v.CompanyName, v.CorpCode, v.ROLOrderDate, v.TEFileNo;