[FEATURE REQ]/[BUG] [Distributed tracing] Report failures properly

[lmolkova]

Library name

Azure.Core

Please describe the feature.

1. Apparently, Scope.Failed(ex) does not work on OTel path (for ActivitySource). We need to use Activity.SetStatus method there.

2. Also, we should add something like Scope.Failed(string) that would set error with optional description when there is no exception to report, like in RequestActivityPolicy - otel.status_code was an old workaround and is not expected to work in a generic case

3. We need to set error conditionally in RequestActivityPolicy depending on RequestOptions and don't fail HTTP activity when configured so.