--spec
CREATE OR REPLACE PACKAGE test_facility_pkg AS 
     --type record
     TYPE t_fy_area_rec IS RECORD( 
        sorted_row_number NUMBER
       ,fy_facility_id facility_area.fy_facility_id%TYPE
       ,area_id facility_area.area_id%TYPE
       ,area_name facility_area.area_name%TYPE
       ,building_number facility_area.building_number%TYPE
    ); 
    
    --type table
    TYPE t_fy_area_tab IS TABLE OF t_fy_area_rec;
    
    --get_filtered_fy_areas
    FUNCTION get_filtered_fy_areas (
        in_facility_id NUMBER
       ,in_filter_crit CHAR_ARRAY
       ,in_sort_col VARCHAR2
       ,in_sort_dir VARCHAR2
    )
    RETURN t_fy_area_tab PIPELINED;
 
    --paginate_avail_fy_areas
    PROCEDURE paginate_avail_fy_areas (
        in_facility_id IN  NUMBER
       ,in_filter_crit IN  CHAR_ARRAY
       ,in_num_rows IN  NUMBER DEFAULT 25
       ,in_page_num IN  NUMBER DEFAULT  1
       ,in_sort_col IN  VARCHAR2
       ,in_sort_dir IN  VARCHAR2 DEFAULT 'DESC'
       ,out_tot_rows OUT NUMBER
       ,out_fy_areas OUT SYS_REFCURSOR
       ,out_msg OUT VARCHAR2
     );
   
END test_facility_pkg; --package spec
/

--body
CREATE OR REPLACE PACKAGE BODY test_facility_pkg AS

     --total filtered facility areas global variable
     g_tot_fy_areas NUMBER(10) := 0;

    --get_filtered_fy_areas    
    FUNCTION get_filtered_fy_areas (
        in_facility_id IN  NUMBER
       ,in_filter_crit IN  CHAR_ARRAY
       ,in_sort_col IN  VARCHAR2
       ,in_sort_dir IN  VARCHAR2
     )
    RETURN t_fy_area_tab PIPELINED
    IS
       --gather areas for a facility and sort them based on input
       CURSOR c_unfiltered_fy_areas IS
            SELECT *
            FROM (
                 SELECT fy_facility_id,area_id,area_name,building_number
                 FROM facility_area
                 WHERE fy_facility_id = in_facility_id
                 ORDER BY
                 DECODE(LOWER(in_sort_col)
                      ,'fy_facility_id',TO_CHAR(fy_facility_id)
                      ,'area_id',TO_CHAR(area_id)
                      ,'area_name',TO_CHAR(area_name)
                      ,'building_number',TO_CHAR(building_number)
                      ,TO_CHAR(area_name))
            )
           ORDER BY
                CASE WHEN UPPER(in_sort_dir) = 'DESC' THEN
                ROWNUM * -1
                ELSE ROWNUM END;
 
       t_fy_area_recs t_fy_area_rec;
       rec c_unfiltered_fy_areas%ROWTYPE;
       v_filter_crit CHAR_ARRAY := CHAR_ARRAY();
       v_counter NUMBER := 1;
       v_processed NUM_ARRAY := NUM_ARRAY();
 
     BEGIN
       test_facility_pkg.g_tot_fy_areas := 0;
       IF in_filter_crit.FIRST IS NULL OR in_filter_crit.COUNT = 0 OR in_filter_crit IS NULL THEN
            v_filter_crit.EXTEND;
            v_filter_crit(1) := '%';
       ELSE
            FOR i IN 1 .. in_filter_crit.COUNT LOOP
                 v_filter_crit.EXTEND;
                 v_filter_crit(i) := '%'||in_filter_crit(i)||'%';
            END LOOP;
       END IF;
 
       FOR rec IN c_unfiltered_fy_areas LOOP
            FOR j IN 1 .. v_filter_crit.COUNT LOOP
                 IF (rec.area_name LIKE v_filter_crit(j) OR rec.building_number LIKE v_filter_crit(j))
                 AND rec.area_id NOT MEMBER OF v_processed THEN
                      t_fy_area_recs.sorted_row_number := v_counter;
                      t_fy_area_recs.fy_facility_id := rec.fy_facility_id;
                      t_fy_area_recs.area_id := rec.area_id;
                      t_fy_area_recs.area_name := rec.area_name;
                      t_fy_area_recs.building_number := rec.building_number;
                      v_processed.EXTEND(1);
                      v_processed(v_counter) := rec.area_id;
                      v_counter := v_counter + 1;
                       test_facility_pkg.g_tot_fy_areas :=  test_facility_pkg.g_tot_fy_areas+1;                         
                      PIPE ROW(t_fy_area_recs);
                 END IF; --selected columns match LIKE filter criteria
            END LOOP; --filter criteria array loop
       END LOOP; --unfiltered facility areas cursor
 
       v_processed.DELETE;
       v_filter_crit.DELETE;

       test_facility_pkg.g_tot_fy_areas := v_counter-1;
 
       RETURN;
    END get_filtered_fy_areas; --function

    PROCEDURE paginate_avail_fy_areas (
       in_facility_id      IN  NUMBER
       ,in_filter_crit      IN  CHAR_ARRAY
       ,in_num_rows      IN  NUMBER DEFAULT 25
       ,in_page_num      IN  NUMBER DEFAULT  1
       ,in_sort_col           IN  VARCHAR2
       ,in_sort_dir           IN  VARCHAR2 DEFAULT 'DESC'
       ,out_tot_rows      OUT NUMBER
       ,out_fy_areas      OUT SYS_REFCURSOR
       ,out_msg           OUT VARCHAR2
         )
     IS
     BEGIN
       OPEN out_fy_areas FOR
            SELECT r.*
            FROM (
                 SELECT DISTINCT q.*
                 FROM TABLE(get_filtered_fy_areas(
                       in_facility_id
                      ,in_filter_crit
                      ,in_sort_col
                      ,in_sort_dir
                      )) q
                 ORDER BY q.sorted_row_number ASC
               ) r
            WHERE 
                ((in_page_num <= 0
                   AND 1=1
                 )
                OR
                (in_page_num > 0
                  AND r.sorted_row_number BETWEEN (((in_page_num-1)*in_num_rows)+1) AND (in_page_num*in_num_rows)
               ));

       out_tot_rows := test_facility_pkg.g_tot_fy_areas;
       out_msg  := 'SUCCESS';
   END paginate_avail_fy_areas; --function

END test_facility_pkg; --pacakge body
/

CLEAR BUFFER;
SET serveroutput ON SIZE 1000000;

var r REFCURSOR;
var b NUMBER;

DECLARE
  A_filter_crit    CHAR_ARRAY := CHAR_ARRAY();
  v_fy_facility_id  NUMBER;
  v_number_of_rows  NUMBER;
  v_page_number    NUMBER;
  v_sort_column    VARCHAR2(50);
  v_sort_direction  VARCHAR2(50);
  tot_rows NUMBER;
  errmsg VARCHAR2(5000);  
 
BEGIN
  A_filter_crit.EXTEND;
  A_filter_crit(1) := 'H';
  A_filter_crit.EXTEND;
  A_filter_crit(1) := '1';
  
  v_fy_facility_id  := 7021;
  v_number_of_rows  := 4;
  v_page_number    := 1;
  v_sort_column    := 'area_id';
  v_sort_direction  := 'ASC';
  
  test_facility_pkg.paginate_avail_fy_areas(
     v_fy_facility_id
    ,A_filter_crit
    ,v_number_of_rows
    ,v_page_number
    ,v_sort_column
    ,v_sort_direction
    ,:b
    ,:r
    ,errmsg
  );

END;
/ 
print r;
print b;