I can help with some of the issues you're facing.

Using Common Table Expressions (CTEs) in Oracle: Simplifying Updates with Derived Tables and MERGE Statement

Optimizing Oracle 12c Joins: Efficient Joining of Max Date Record