One other thing I dislike is differed binding.  What is that you ask?  This is using differed binding: select top 1 * from CoreHistory..PRODUCT where PRODUCT_ID = c.PRODUCT_ID order by SESSION_ID desc. Instead of specifying the schema of the table PRODUCT in the CoreHistory database you have used the .. notation.  This will default to dbo except if the user running this query has a different default schema.  Unless you need this functionality you really should explicitly specify the schema.

Other than that, I haven't looked through the code as it is long and I am at work so no time at them moment (maybe later or when I get home).  If you could post the actual execution plan for the execution of the procedure (as a .sqlplan file), that I would look at quicker than the code at the moment.