Error with complex Oracle query

In blue in your post is a $this->db->select() where you have a FROM and WHERE
Then you have a $this->db->where() which appends the SELECT with "WHERE..." giving you the two WHEREs in the SQL.

Take out all of the WHERE commands and FROM commands within the SELECT command - and use the respective $this->db->from() and $this->db>where() to construct those.