-- 1. DATA_LAYER: Tables
CREATE TABLE DATA_LAYER.departments (
    department_id   NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    department_name VARCHAR2(100) NOT NULL UNIQUE
);

-- 2. LOGIC_LAYER: Private implementation
CREATE OR REPLACE PACKAGE LOGIC_LAYER.departments_pkg AUTHID DEFINER AS
    TYPE t_department_rec IS RECORD (
        department_id   NUMBER,
        department_name VARCHAR2(100)
    );
    TYPE t_department_tab IS TABLE OF t_department_rec;

    FUNCTION get_departments RETURN t_department_tab PIPELINED;
END departments_pkg;
/

CREATE OR REPLACE PACKAGE BODY LOGIC_LAYER.departments_pkg AS
    FUNCTION get_departments RETURN t_department_tab PIPELINED IS
    BEGIN
        FOR rec IN (SELECT department_id, department_name FROM DATA_LAYER.departments) 
        LOOP
            PIPE ROW(rec);
        END LOOP;
    END get_departments;
END departments_pkg;
/

-- 3. API_LAYER: Public interface for APEX
CREATE OR REPLACE PACKAGE API_LAYER.departments_api AUTHID DEFINER AS
    FUNCTION get_departments RETURN LOGIC_LAYER.departments_pkg.t_department_tab PIPELINED;
END departments_api;
/

CREATE OR REPLACE PACKAGE BODY API_LAYER.departments_api AS
    FUNCTION get_departments RETURN LOGIC_LAYER.departments_pkg.t_department_tab PIPELINED IS
    BEGIN
        -- Calling the logic layer function
        FOR rec IN (SELECT * FROM TABLE(LOGIC_LAYER.departments_pkg.get_departments)) 
        LOOP
            PIPE ROW(rec);
        END LOOP;
    END get_departments;
END departments_api;
/

-- 4. GRANTS
GRANT SELECT ON DATA_LAYER.departments TO LOGIC_LAYER;
GRANT EXECUTE ON LOGIC_LAYER.departments_pkg TO API_LAYER;
GRANT EXECUTE ON API_LAYER.departments_api TO APEX_UI_LAYER;

Table functions are best suited to cases where you need to process the data in ways you can't (easily) do in SQL.

Using them as a wrapper for pure SELECT * FROM ... queries severely limits the optimizer's options. It can't "see through" these layers.

For example, querying by primary key will no longer use its index:

insert into departments by name
  select dbms_random.string ( 'u', 20 ) department_name 
  connect by level <= 10000;

commit;

alter session set statistics_level = all;
set serveroutput off

select * from departments 
where  department_id = 1;

select * from dbms_xplan.display_cursor ( format => 'IOSTATS LAST');

------------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name         | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |              |      1 |        |      1 |00:00:00.01 |       4 |
|   1 |  TABLE ACCESS BY INDEX ROWID| DEPARTMENTS  |      1 |      1 |      1 |00:00:00.01 |       4 |
|*  2 |   INDEX UNIQUE SCAN         | SYS_C0013614 |      1 |      1 |      1 |00:00:00.01 |       3 |
------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - access("DEPARTMENT_ID"=1)

select * from departments_pkg.get_departments ()
where  department_id = 1;

select * from dbms_xplan.display_cursor ( format => 'IOSTATS LAST');

---------------------------------------------------------------------------------------------------------------
| Id  | Operation                         | Name            | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
---------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                  |                 |      1 |        |      1 |00:00:00.14 |     937 |
|*  1 |  COLLECTION ITERATOR PICKLER FETCH| GET_DEPARTMENTS |      1 |     82 |      1 |00:00:00.14 |     937 |
---------------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - filter("KOKBF$"."DEPARTMENT_ID"=1)

Notice that the table function does nearly 250x more work (4 buffers vs 937).

(I've created the table and package in the same schema; separate schemas make no difference for this test).

Chaining them like this further restricts the optimizer.

So I would advise you to use views rather than (pipelined) table functions.

In terms of where the best place to put these views is, I'm unsure why you've gone for four layers of schemas, so I'm also unsure where best to place them. Here are some thoughts which may help:

- Keep the number of views in a chain small. i.e. avoid views querying views querying views .... The more views there are, the more chances there are of unintended side effects if you change views at the bottom of the chain
- Users with privileges on a view can view its definition in ALL_VIEWS. If you want to hide the logic in a view from the UI, it can't have direct access to it
- View can't be parameterized, which has functionality and performance implications. Macros are a way to achieve this if needed.
- For read-only operations, grant READ rather than SELECT. With SELECT privs, users can run SELECT ... FOR UPDATE and lock rows. With READ they can't.