Helping the North Sea transition to a “renewables powerhouse” should be a “national mission” for the UK, ministers in both Edinburgh and London have been told.