Working Query

Select
sh.CompanyID
,sh.SalesHeaderNo
,sh.SelltoContactNo

From DWH_Sales.sales.SalesHeader sh

inner join [DWH_Dimensions].[dim].customer c on sh.companyID = c.companyID and sh.selltocustomerno = c.customerno

Where 1=1
and sh.DocumentType in (1)
--and c.CustomerDiscGroup = 'WW-107000'
--and sh.SalesHeaderNo = 'DE-SCO-394124'

Union All

Select distinct


sha.CompanyID
,sha.SalesHeaderArchiveNo
,SelltoContactNo  = First_Value(sha.SelltoContactNo) Over(Partition by sha.CompanyID, sha.DocumentType, sha.SalesHeaderArchiveNo Order by sha.DocNoOccurrence desc, sha.VersionNo desc)

From   DWH_Sales.sales.SalesHeaderArchive sha
inner join [DWH_Dimensions].[dim].customer c on sha.companyID = c.companyID and sha.selltocustomerno = c.customerno

Where 1=1
and sha.DocumentType in (1)
And NOT EXISTS (Select * From DWH_Sales.sales.SalesHeader 
Where DocumentType in (1) and CompanyID = sha.CompanyID and DocumentType = sha.DocumentType and SalesHeaderNo = sha.SalesHeaderArchiveNo)