query consuming more io and CPU

The sort to tempdb is trigged by the distinct clause. Since you're outputting only columns from the Standard table, you could try adding a subquery with the other 3 tables in the where clause. Assuming that StandardID is unique in the Standard table, this will allow you to dish the distinct.

INSERT INTO @table
(
    StandardID,
    StdNbr,
    StdActiveFlag,
    RegionID
)
SELECT
    StandardID = S.StandardID,
    StdNbr = 
        ISNULL
        (
            CASE 
                WHEN DATALENGTH(S.StandardNumber) > 0 
                THEN S.StandardNumber + ''
                ELSE 
                    RTRIM(S.StdPrefixCode) + '-' + 
                    RIGHT('000' + CONVERT(VARCHAR(3), S.StandardNbr), 3) + '-' + 
                    S.StandardVersion
            END,
            'N/A'
        ),
        StandardActiveFlag = S.StandardActiveFlag,
        RegionID = S.RegionID
FROM dbo.Standards AS S
WHERE
    S.CompanyID = @OATI_CoID
    AND (@ActiveFlag IS NULL OR S.StandardActiveFlag = @ActiveFlag)
    AND EXISTS
    (
        SELECT * 
        FROM dbo.Requirement AS req
        JOIN dbo.RequirementRespEntity AS ReqRE
            ON ReqRE.StandardID = req.StandardID
            AND ReqRE.RequirementID = req.RequirementID
        JOIN dbo.CompanyRespEntity AS CRE
            ON CRE.ResponsibleEntityID = ReqRE.ResponsibleEntityID
        WHERE  
            req.StandardID = S.StandardID
            AND CRE.CompanyID = @CompanyID
    )
OPTION (RECOMPILE); -- Optional

If that is not suitable for some reason, the cardinality estimate (CE) to look at first is the one on the Standards table:

The seek predicate is probably not a problem, given good statistics, but the highlighted residual predicate most likely is causing the underestimate (11 vs 489 rows). This sort of conditional predicate is tough for CE to get right.

The usual solutions are to remove the dependency on the variable using dynamic SQL, an IF block with separate specific queries for the two cases (@ActiveFlag IS NULL, or not), or an OPTION (RECOMPILE) hint. That last option will add a little extra overhead to each call for a fresh compilation, but the current value of @ActiveFlag will be inlined.

I would probably lean toward using an IF block with two queries optimized for the two cases. The one for not null would have a S.StandardActiveFlag = @ActiveFlag predicate, the other would have no predicate on S.StandardActiveFlag.

Once that issue is corrected, you will most likely find that the Sort no longer spills. Not that it appears to be a huge problem right now – 80ms total duration is not that huge, unless the query is executed very very frequently perhaps.