Implement CWallet::SignTransaction using ScriptPubKeyMan::SignTransaction

Summary:
Backport of Core PR18115 part [3/9] : https://github.com/bitcoin/bitcoin/pull/18115/commits/f37de927442d3f024926a66c436d59e391c8696a

Depends on D8095

Test Plan:

ninja all check-all

Reviewers: #bitcoin_abc, PiRK

Reviewed By: PiRK

Differential Revision: https://reviews.bitcoinabc.org/D8096