20210821 - 포트폴리오 메인 페이지 작성 - 예상금액확인

1. 단가 표현을 위한 Collection 생성

Pre-Rendering - Before Regions - Processes

Identification - Name : CreateCollection

Type : Execute Code

Source - Location : Local Database

Language : PL/SQL

PL/SQL Code : 

declare

  --l_session apex_application.g_instance%type := apex_application.g_instance;

  l_query varchar2(20000) := 

    'select cd

          , to_number(grp2) hh24

          , to_number(grp3) amt

          , 0 page_cnt

          , 0 page_amt

          , null, null, null, null, null, null

       from jkmst_cd_mst 

      where grp_id = 1 and cd != ''00''

      order by cd';

begin

  APEX_COLLECTION.DELETE_ALL_COLLECTIONS;

  APEX_COLLECTION.CREATE_COLLECTION_FROM_QUERY2 (

    p_collection_name => 'ESTIMATION', 

    p_query => l_query,

    p_generate_md5 => 'YES');

end;

2. 예상금액확인 리전 생성

Identification - Name : 예상금액확인

Type : Classic Report

** 앞서 만들어 놓은 collection 에서 데이터를 읽어 오면서 컬럼별 스타일 적용

select '<span class="t-Icon fa fa-number-'||trim(to_char(n001))||'-o fa-2x" aria-hidden="true"></span>' cd

     , n002 hh24, n003 amt

     , n004 page_cnt

     , (n003 * n004) page_amt

     , '<button class="ButtonXU t-Button t-Button--default t-Button--hot" id=U-'||trim(to_char(n001))||' type="button"><span aria-hidden="true" class="fa fa-arrow-circle-o-up"></span></button>' btn_up

     , '<button class="ButtonXD t-Button t-Button--default" id=D-'||trim(to_char(n001))||' type="button"><span aria-hidden="true" class="fa fa-arrow-circle-o-down"></span></button>' btn_down

  from apex_collections 

 where collection_name = 'ESTIMATION'

 order by seq_id

Appearance - Template Options : Use Template Defaults, Show Region Icon, Accent 2, Scroll - Default

Icon : fa-dollar fa-2x

그리고 디자인이 들어간 CD, BTN_UP, BTN_DOWN 은 컬럼 Type을 Markdown 으로하여 HTML 표현

Dynamic Action - Name : UbtnClicked

When - Event : Click

Selection Type : jQuery Selector

jQuery Selector : .ButtonXU (button class name in sql design)

-- True Action1 : Set Value (PL/SQL에서 클릭 버튼 종류를 인식하기 위해 U, D 값 셋팅)

Set Type : JavaScript Expression : this.triggeringElement.id

Affected Elements : P3_ID

-- True Action2 : Execute Server-side Code (클릭된 Row의 값 계산, 누적)

declare

  lv_value varchar2(100) := :P3_ID;

  lv_calc  varchar2(1)   := substr(lv_value,1,1);

  ln_level number        := to_number(substr(lv_value,3));

  ln_cnt   number        := 0;

begin

--raise_application_error(-20001, substr(lv_value,1,1));

  if :P3_ID is null then return; end if;

  select n004 into ln_cnt 

    from apex_collections

   where collection_name = 'ESTIMATION'

     and seq_id          = ln_level;

  

  if lv_calc = 'U' then

    ln_cnt := ln_cnt + 1;

  else

    ln_cnt := ln_cnt - 1;

  end if;

  if ln_cnt < 0 then return; end if;

--raise_application_error(-20001, ln_cnt);

  APEX_COLLECTION.UPDATE_MEMBER_ATTRIBUTE (

    p_collection_name => 'ESTIMATION',

    p_seq             => to_char(ln_level),

    p_attr_number     => 4,

    p_number_value    => ln_cnt);

  select sum(n003 * n004) into ln_cnt 

    from apex_collections

   where collection_name = 'ESTIMATION';

  :P3_SUM := TO_CHAR(ln_cnt,'999,999,999,999');

end;

-- True Action3 : Refresh (계산된 Collection에서 값 읽어오기)

Selection Type : Region

Region : 예상금액확인

-- True Action4 : Execute JavaScript Code (리전 상단의 합계 버튼에 값 셋팅)

$('#myStaticBTN span.t-Button-label').text(apex.item('P3_SUM').getValue())

참고

https://docs.oracle.com/en/database/oracle/application-express/21.1/aeapi/CREATE_COLLECTION_FROM_QUERY2-Procedure.html#GUID-7800CD9E-FD29-4E49-B637-A426D78A644C

CREATE_COLLECTION_FROM_QUERY2 Procedure

Use this procedure to create a collection from a supplied query. This method is identical to CREATE_COLLECTION_FROM_QUERY, however, the first 5 columns of the SELECT clause must be numeric and the next 5 must be date. After the numeric and date columns, there can be up to 50 character columns in the SELECT clause. The query is parsed as the application owner. If a collection exists with the same name for the current user in the same session for the current Application ID, an application error is raised.                   

이 프로시저를 사용할 경우 반드시 첫 5개는 숫자, 다음 5개는 일자 그리고 1개는 문자가 필요함. Null이라도 표시해야 함.

declare

  l_query varchar2(20000) := 

    'select prod_id, prod_qty, 0, 0, 0, null, null, null, null, null, null 

from po_mst where rownum <= 3';

begin

  

  APEX_COLLECTION.CREATE_COLLECTION_FROM_QUERY2 (

    p_collection_name => 'PRODLIST', 

    p_query => l_query,

    p_generate_md5 => 'YES',

    p_truncate_if_exists => 'YES');

end;