20241016 - EBA_DEMO_WORKLIST 조회된 리전의 PK 값 읽어오기 (feat. Smart Filter 스마트 필터)

1. DB 패키지 생성

create or replace package "EBA_DEMO_WORKLIST" as

    type eba_demo_worklist_pk_t_row is record (

        id varchar2(1000)

    );

    type eba_demo_worklist_pk_t_tab is table of eba_demo_worklist_pk_t_row;

    function get_region_results_pk_values(

        p_page_id          in number,

        p_region_static_id in varchar2,

        p_pk_column_name   in varchar2 )

    return eba_demo_worklist_pk_t_tab pipelined;

end "EBA_DEMO_WORKLIST";

/

create or replace package body "EBA_DEMO_WORKLIST" as

    function get_region_results_pk_values(

        p_page_id          in number,

        p_region_static_id in varchar2,

        p_pk_column_name in varchar2 )  

    return eba_demo_worklist_pk_t_tab pipelined is

    l_region_id   number;

    l_context     apex_exec.t_context;

    type t_col_index is table of pls_integer index by varchar2(255);

    l_col_index t_col_index;

   

    ---------------------------------------------------------------------------

    procedure get_column_indexes( p_columns wwv_flow_t_varchar2 ) is

    begin

        for i in 1 .. p_columns.count loop

            l_col_index( p_columns( i ) ) := apex_exec.get_column_position(

                                                 p_context => l_context,

                                                 p_column_name => p_columns( i ) );

        end loop;

    end get_column_indexes;

begin

    -- 1. get the id of the region from its name

    select region_id

      into l_region_id

      from apex_application_page_regions

     where application_id = v('APP_ID')

       and page_id        = p_page_id

       and static_id      = p_region_static_id;

       

    -- 2. Get a cursor (apex_exec.t_context) for the current region data

    l_context := apex_region.open_query_context(

                     p_page_id      => p_page_id,

                     p_region_id    => l_region_id );

    get_column_indexes( wwv_flow_t_varchar2( p_pk_column_name) );

    while apex_exec.next_row( p_context => l_context ) loop

        pipe row( eba_demo_worklist_pk_t_row(

                      apex_exec.get_varchar2  ( p_context => l_context,

                                              p_column_idx => l_col_index( p_pk_column_name ))));

    end loop;

    apex_exec.close( l_context );

    return;

exception

    when no_data_needed then

        apex_exec.close( l_context );

        return;

    when others then

        apex_exec.close( l_context );

        raise;

end get_region_results_pk_values;

end "EBA_DEMO_WORKLIST";

/

2. 두 개의 리전 생성

1) 스마트 필터와 모든 다른 조건들이 연결이 되어 조회되는 리전 (SID > RESULTS) + 감추기

2) 위 리전에서 조회된 PK 값을 읽어서 조회할 최종 보여지게 되는 리전

where pomst.po_id in

(select to_number(id)

from eba_demo_worklist.get_region_results_pk_values(299,'RESULTS','PO_ID'))

3. 스마트 필터와 연결된 리전 감추기

.hidden {

   display: none;

}

참고

https://diveintoapex.com/2022/06/05/smartfiltering-multiple-regions/

https://blogs.oracle.com/apex/post/add-a-chart-to-your-faceted-search-page