A pub has reopened its doors in Pickering following a major investment.