You might also try the alternate way to write that criteria:

and not
  (
               L.HTS_NUM =ISNULL(C.[CLASS_VALUE_1], '')  and
               L.HTS_NUM = ISNULL(C.[CLASS_VALUE_1], '')  and

So let's deal with the elephant in the room.  You are storing different notes generated at different time of the date to a single column in a single row, and...

This is probably applying a bandaid to a shotgun wound, but - have you considered considered creating persisted calculation columns for all of the repeated date operation you're doing on...

This should get you pretty close to it I think.  You're looking to populate attributes of an element, which all get prefixed with @ when you name them.