How do i crack this non-ansi code blob
Consider these two simple tables:

CREATE TABLE dbo.t1
(
    t1_ID int NOT NULL
);

CREATE TABLE dbo.t2
(
    t2_ID int NOT NULL
);


"old style" JOINs used in SQL Server 2000 and earlier, that look like this:

SELECT *
FROM dbo.t1
    , dbo.t2
WHERE t1.t1_ID *= t2.t2_ID;


Are equivalent to an ANSI-style LEFT JOIN:

SELECT *
FROM dbo.t1 
    LEFT JOIN dbo.t2 ON t1.t1_ID = t2.t2_ID;


This:

SELECT *
FROM dbo.t1
    , dbo.t2
WHERE t1.t1_ID =* t2.t2_ID;


becomes:

SELECT *
FROM dbo.t1 
    RIGHT JOIN dbo.t2 ON t1.t1_ID = t2.t2_ID;


Although, I'm not a fan of using RIGHT JOIN, and would rewrite this to its equivalent LEFT JOIN, as:

SELECT *
FROM dbo.t2 
    LEFT JOIN dbo.t1 ON t2.t2_ID = t1.t1_ID;


The dynamic SQL in your question is complex enough that I'd first use PRINT to print out a sample of the generated code, then I'd use the above rules to convert the old style joins into ANSI-compliant ones.  

I've mocked-up the following from your code, in an attempt to simplify it:

DECLARE @SQLCmd nvarchar(max);
DECLARE @InvType nvarchar(100) = 'InvType';
DECLARE @TableName nvarchar(128) = 'TableName';
DECLARE @RetnType nvarchar(100) = 'RetnType';
DECLARE @WhereCond nvarchar(1000) = 'WhereCond';

Select @SQLCmd = 
'SELECT ''' + @InvType + ''' AS Inv_Type, ' + @TableName + '_Number AS     Item_Number, Alternate_ID, Description1, 
    Company_ID, Department_ID, Group_Id, Section_Id, Media_Code, 
    Location_Current, Location_Home, Hold_Status, Record_Type,   Disposition_Status, ' + @TableName + '.Retention_Code, 
    Retn_Disposal_Method, Retn_Authorize_Code, Reservation_Status 
FROM ' + @TableName + ', RETENTION, (SELECT Item_Type, Item_Number, MAX(Reservation_Process_Status) AS 
Reservation_Status FROM RESERVATION GROUP BY Item_Type, Item_Number) AS RESV 
WHERE ' + @TableName + '.Retention_Code *= RETENTION.Retention_Code AND ' +
    @TableName + '_Number *= RESV.Item_Number AND ''' + @InvType + ''' = RESV.Item_Type 
    ' +
Case LTrim(@RetnType)
    When '' Then ''
    Else 'AND ' + @TableName + '.Retention_Type = ''' + @RetnType + ''''
End
+ ' 
    AND ' + @WhereCond +
' UNION 
SELECT ''' + @InvType + ''' AS Inv_Type, ' + @TableName + '_Number AS Item_Number, Alternate_ID, Description1, 
    Company_ID, Department_ID, Group_Id, Section_Id, Media_Code, 
    Location_Current, Location_Home, Hold_Status, Record_Type, Disposition_Status, ' + @TableName + '.Retention_Code, 
    Retn_Disposal_Method, Retn_Authorize_Code, Reservation_Status 
FROM ' + @TableName + ', RETENTION, (SELECT Item_Type, Item_Number, MAX(Reservation_Process_Status) AS 
Reservation_Status FROM RESERVATION GROUP BY Item_Type, Item_Number) AS RESV 
WHERE ' + @TableName + '.Retention_Code *= RETENTION.Retention_Code AND 
' + @TableName + '_Number *= RESV.Item_Number AND ''' + @InvType + ''' = RESV.Item_Type 
' +
Case LTrim(@RetnType)
    When '' Then ''
    Else 'AND ' + @TableName + '.Retention_Type = ''' + @RetnType + ''''
End

PRINT @SQLCmd;


This results in:

SELECT 'InvType' AS Inv_Type, TableName_Number AS     Item_Number, Alternate_ID, Description1, 
    Company_ID, Department_ID, Group_Id, Section_Id, Media_Code, 
    Location_Current, Location_Home, Hold_Status, Record_Type,   Disposition_Status, TableName.Retention_Code, 
    Retn_Disposal_Method, Retn_Authorize_Code, Reservation_Status 
FROM TableName, RETENTION, (SELECT Item_Type, Item_Number, MAX(Reservation_Process_Status) AS 
Reservation_Status FROM RESERVATION GROUP BY Item_Type, Item_Number) AS RESV 
WHERE TableName.Retention_Code *= RETENTION.Retention_Code AND TableName_Number *= RESV.Item_Number AND 'InvType' = RESV.Item_Type 
    AND TableName.Retention_Type = 'RetnType' 
    AND WhereCond UNION 
SELECT 'InvType' AS Inv_Type, TableName_Number AS Item_Number, Alternate_ID, Description1, 
    Company_ID, Department_ID, Group_Id, Section_Id, Media_Code, 
    Location_Current, Location_Home, Hold_Status, Record_Type, Disposition_Status, TableName.Retention_Code, 
    Retn_Disposal_Method, Retn_Authorize_Code, Reservation_Status 
FROM TableName, RETENTION, (SELECT Item_Type, Item_Number, MAX(Reservation_Process_Status) AS 
Reservation_Status FROM RESERVATION GROUP BY Item_Type, Item_Number) AS RESV 
WHERE TableName.Retention_Code *= RETENTION.Retention_Code AND 
TableName_Number *= RESV.Item_Number AND 'InvType' = RESV.Item_Type 
AND TableName.Retention_Type = 'RetnType'


Which I'd reformat so that each column is on a separate line:

SELECT 'InvType' AS Inv_Type
    , TableName_Number AS     Item_Number
    , Alternate_ID
    , Description1
    , Company_ID
    , Department_ID
    , Group_Id
    , Section_Id
    , Media_Code
    , Location_Current
    , Location_Home
    , Hold_Status
    , Record_Type
    , Disposition_Status
    , TableName.Retention_Code
    , Retn_Disposal_Method
    , Retn_Authorize_Code
    , Reservation_Status 
FROM TableName
    , RETENTION
    , (
        SELECT Item_Type
            , Item_Number
            , MAX(Reservation_Process_Status) AS Reservation_Status 
        FROM RESERVATION 
        GROUP BY Item_Type, Item_Number
        ) AS RESV 
WHERE TableName.Retention_Code *= RETENTION.Retention_Code 
    AND TableName_Number *= RESV.Item_Number 
    AND 'InvType' = RESV.Item_Type 
    AND TableName.Retention_Type = 'RetnType' 
    AND WhereCond 
UNION 
SELECT 'InvType' AS Inv_Type
    , TableName_Number AS Item_Number
    , Alternate_ID
    , Description1
    , Company_ID
    , Department_ID
    , Group_Id
    , Section_Id
    , Media_Code
    , Location_Current
    , Location_Home
    , Hold_Status
    , Record_Type
    , Disposition_Status
    , TableName.Retention_Code
    , Retn_Disposal_Method
    , Retn_Authorize_Code
    , Reservation_Status 
FROM TableName
    , RETENTION
    , (
        SELECT Item_Type
            , Item_Number
            , MAX(Reservation_Process_Status) AS Reservation_Status 
        FROM RESERVATION 
        GROUP BY Item_Type
            , Item_Number
        ) AS RESV 
WHERE TableName.Retention_Code *= RETENTION.Retention_Code 
    AND TableName_Number *= RESV.Item_Number 
    AND 'InvType' = RESV.Item_Type 
    AND TableName.Retention_Type = 'RetnType'


A bit tedious, but now we can see what the query actually does.

Converting the "old-style" joins into ANSI-style joins results in:

SELECT 'InvType' AS Inv_Type
    , TableName_Number AS     Item_Number
    , Alternate_ID
    , Description1
    , Company_ID
    , Department_ID
    , Group_Id
    , Section_Id
    , Media_Code
    , Location_Current
    , Location_Home
    , Hold_Status
    , Record_Type
    , Disposition_Status
    , TableName.Retention_Code
    , Retn_Disposal_Method
    , Retn_Authorize_Code
    , Reservation_Status 
FROM TableName
    LEFT JOIN RETENTION ON TableName.Retention_Code = RETENTION.Retention_Code
    LEFT JOIN (
        SELECT Item_Type
            , Item_Number
            , MAX(Reservation_Process_Status) AS Reservation_Status 
        FROM RESERVATION 
        GROUP BY Item_Type, Item_Number
        ) AS RESV ON TableName_Number = RESV.Item_Number 
WHERE 'InvType' = RESV.Item_Type 
    AND TableName.Retention_Type = 'RetnType' 
    AND WhereCond 
UNION 
SELECT 'InvType' AS Inv_Type
    , TableName_Number AS Item_Number
    , Alternate_ID
    , Description1
    , Company_ID
    , Department_ID
    , Group_Id
    , Section_Id
    , Media_Code
    , Location_Current
    , Location_Home
    , Hold_Status
    , Record_Type
    , Disposition_Status
    , TableName.Retention_Code
    , Retn_Disposal_Method
    , Retn_Authorize_Code
    , Reservation_Status 
FROM TableName
    LEFT JOIN RETENTION ON TableName.Retention_Code = RETENTION.Retention_Code 
    LEFT JOIN (
        SELECT Item_Type
            , Item_Number
            , MAX(Reservation_Process_Status) AS Reservation_Status 
        FROM RESERVATION 
        GROUP BY Item_Type
            , Item_Number
        ) AS RESV ON TableName_Number = RESV.Item_Number  
WHERE 'InvType' = RESV.Item_Type 
    AND TableName.Retention_Type = 'RetnType'


Folding that back into dynamic SQL results in:

DECLARE @SQLCmd nvarchar(max);
DECLARE @TableName nvarchar(128);
DECLARE @InvType nvarchar(100);
DECLARE @RetnType nvarchar(100);
DECLARE @WhereCond nvarchar(1000);

SET @SQLCmd = 'SELECT ''' + @InvType + ''' AS Inv_Type
    , TableName_Number AS     Item_Number
    , Alternate_ID
    , Description1
    , Company_ID
    , Department_ID
    , Group_Id
    , Section_Id
    , Media_Code
    , Location_Current
    , Location_Home
    , Hold_Status
    , Record_Type
    , Disposition_Status
    , TableName.Retention_Code
    , Retn_Disposal_Method
    , Retn_Authorize_Code
    , Reservation_Status 
FROM ' + @TableName + '
    LEFT JOIN RETENTION ON ' + @TableName + '.Retention_Code = RETENTION.Retention_Code
    LEFT JOIN (
        SELECT Item_Type
            , Item_Number
            , MAX(Reservation_Process_Status) AS Reservation_Status 
        FROM RESERVATION 
        GROUP BY Item_Type, Item_Number
        ) AS RESV ON TableName_Number = RESV.Item_Number 
WHERE ' + @InvType + ' = RESV.Item_Type 
    AND ' + @TableName + '.Retention_Type = ''' + @RetnType + ''' 
    AND ' + @WhereCond + '
UNION 
SELECT ''' + @InvType + ''' AS Inv_Type
    , ' + @TableName + '_Number AS Item_Number
    , Alternate_ID
    , Description1
    , Company_ID
    , Department_ID
    , Group_Id
    , Section_Id
    , Media_Code
    , Location_Current
    , Location_Home
    , Hold_Status
    , Record_Type
    , Disposition_Status
    , ' + @TableName + '.Retention_Code
    , Retn_Disposal_Method
    , Retn_Authorize_Code
    , Reservation_Status 
FROM ' + @TableName + '
    LEFT JOIN RETENTION ON ' + @TableName + '.Retention_Code = RETENTION.Retention_Code 
    LEFT JOIN (
        SELECT Item_Type
            , Item_Number
            , MAX(Reservation_Process_Status) AS Reservation_Status 
        FROM RESERVATION 
        GROUP BY Item_Type
            , Item_Number
        ) AS RESV ON ' + @TableName + '_Number = RESV.Item_Number  
WHERE ''' + @InvType + ''' = RESV.Item_Type 
    AND TableName.Retention_Type = ''' + @RetnType + ''''