Consolidate `cancelable` scope for pipelines and jobs

What does this MR do and why?

Related to !140522 (comment 1763158527)

We have two scopes in has_status.rb which do the same thing. More details about the sql queries in the comments.