DECLARE 
     -- Comment out this line for Procedure
     @ordOrderNo INT = 2716,

     @ordCommtAbrv VARCHAR(30),
     @salesOrderNo VARCHAR(10),
     @trailingIdent VARCHAR(1) = 'S';

-- Drop the temp table in case for some reason it did not get dropped after the last stored procedure.
IF OBJECT_ID('tempdb..#abrvTable') IS NOT NULL
BEGIN
    DROP TABLE #abrvTable;
END

-- Create abbreviation temp table
CREATE TABLE #abrvTable 
(
    fullTerm VARCHAR(100), 
    abrvTerm VARCHAR(15)
);

-- Insert data into data table
INSERT INTO #abrvTable (fullTerm, abrvTerm)
VALUES
    -- Color
    ('Amber', 'ASH'), ('Ash', 'BISC'),
    -- Species
    ('Alder', 'ALD'), ('Beech', 'BEE'),
    -- Door Style
    ('Bridgeport', 'BRPT'), ('Grandview', 'GV'),
    -- Drawer front Style
    ('5PC_TOP', '5TOP'), ('Matching', '5PCS'),
    -- Product line
    ('FFO', 'FF'), ('FLO', 'FL');

-- Concat Order Comment Abbrevation based on feature options
SET @ordCommtAbrv = 'NULL';
SET @salesOrderNo = RIGHT('00000' + CAST(@ordOrderNo AS VARCHAR(5)), 5);

SELECT 
    -- Where the magic happens with trailing letter with break
    -- Right now it's ordered by door style, it should be switch to abrv_comment
    @salesOrderNo + CHAR(ASCII('A') + DENSE_RANK() OVER (ORDER BY 'Abrv_Comment') -1) + @trailingIdent AS SalesOrderNo,

    -- Leave Empty
    '' AS [ARDivision No],

    vwolni.[venCode] AS [Customer#],
    [pdCode] AS [Item Code],
    -- olnpd.olnpdEngModel AS [Eng Model],
    [olniQty] AS [QTY],
    '--TODOs: Concat feature' AS [ftr_Comment],
    vwolni.olnpdNetPrice AS [Unit Price],
    [bomtCode] AS [Item Type],

    -- Control Value | Make sure Sage value is matched
    olnopt_Finish.olnoValue AS [Color],
    -- Control Value | Make sure Sage value is matched
    olnopt_Species.olnoValue AS [Species],
    -- Control Value | Make sure Sage value is matched
    olnopt_Door_Style.olnoValue AS "Door Style",
    -- Control Value | Make sure Sage value is matched
    olnopt_Drawer_Head.olnoValue AS [Drawer Head],

    '--Report Only' AS [Custom Finish],
    '--Report Only' AS [Collection / Order Form Type],
    [olniQty] AS [Number of cabinets],
    '--Report Only'/*--Subtotal unit price(vwolni.olnpdNetPrice*olniQty)*/ AS [Orderline Total],
    ord.ordPONumber as [Customer PO],
    (SELECT ISNULL((SELECT abrvTerm 
                    FROM #abrvTable 
                    WHERE fullTerm = olnopt_Finish.olnoValue), 'NULL'))
      +('/')
      +(SELECT ISNULL((SELECT abrvTerm FROM #abrvTable WHERE fullTerm = olnopt_Species.olnoValue), 'NULL'))
      +('/')
      +(SELECT ISNULL((SELECT abrvTerm FROM #abrvTable WHERE fullTerm = olnopt_Door_Style.olnoValue), 'NULL'))
      +('/')
      +(SELECT ISNULL((SELECT abrvTerm FROM #abrvTable WHERE fullTerm = olnopt_Drawer_Head.olnoValue), 'NULL'))
      +('/')
        AS 'Abrv_Comment',
    '--Report Only' AS [Factor],
    '--Report Only' AS [Multiplier],
    OrgCom.octValue AS [E-mail],
    '--Report Only' AS [Contact person],

    -- Control Value | Make sure Sage value is matched
    shmCode AS [Ship Via],
    -- Control Value | Make sure Sage value is matched
    subquery_att.SalesPersonCode AS [Salesperson],

    OrdPr.ordpValue AS [Freight],
    subquery_att.[Job Number] AS [JobNo],
    '--Report Only' AS [ShopSQFT],
    '--Report Only' AS [Fin Shop SQFT],
    olnopt_Drawer_Box.olnoValue AS [DrawerBox],
    optCode AS [Cabinet Interior-Exterior]
FROM 
    ...

I tried using just olnopt_Door_Style.olnoValue to test and the DENSE RANK line for sales order Number works. But when I switch it to Abrv_comment , it doesn't work with the error code:

Msg 5309, Level 16, State 1, Line 63
Windowed functions, aggregates and NEXT VALUE FOR functions do not support constants as ORDER BY clause expressions.