Understanding Pipelined Functions in Oracle SQL: When Does Pipelining Fail?

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

Understanding Package Methods in Oracle: A Deep Dive

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