Yet another way to create "editable interactive report" part 2

While back I did write blog post Yet another way to create "editable interactive report". This post is part 2, where I explain how you can add select lists and text area to report. In example I have use APEX_COLLECTION, but you can modify code to work with real table.

First create Before Regions PL/SQL process that creates apex_collection:

APEX_COLLECTION.CREATE_COLLECTION_FROM_QUERYB2 (
      p_collection_name => 'EMP',
      p_query => '
        SELECT e1.empno
          , e1.sal
          , COALESCE(e1.comm, 0) AS comm
          , e1.mgr
          , e1.deptno
          , e1.hiredate
          , NULL
          , NULL
          , NULL
          , NULL
          , e1.ename
          , e1.job
       FROM emp e1
      '
    );
    

Then go to Shared Components > Lists of Values and create new LOV:

• From Scratch
• name: MANAGER
• type: Dynamic
• Query:

select ename d, empno r
    from   emp
    order by 1

Create another new LOV:

• From Scratch
• name: DEPARTMENT
• type: Dynamic
• Query:

select dname d, deptno r
    from   dept
    order by 1

Go back to edit page and create interactive report from query:

SELECT seq_id
      ,n001    AS empno
      ,c001    AS ename
      ,c002    AS job
      ,n004    AS mgr
      ,d001    AS hiredate
      ,n002    AS sal
      ,n003    AS comm
      ,n005    AS dname
      ,clob001 AS enotes
      ,n004    AS mgrno
      ,n005    AS deptno
    FROM apex_collections
    WHERE collection_name = 'EMP'

Change ENOTES column Display as to standard report column, MGRNO and DEPTNO columns to hidden.

Edit ENOTES column attributes and add to column formatting:

<textarea class="ir-edit-input" id="CLOB-#SEQ_ID#-1" cols="30" rows="3">#ENOTES#</textarea>

You can control text are width and height using attributes cols and rows.

Edit MGR column attributes and change Display Type to Display as Text (based on LOV, escape special characters).
Set Named List of Values to MANAGER

Set column formatting

<select class="ir-edit-input" data-lov="MANAGER" data-display-null="- Select -" data-last-value="#MGRNO#" id="N-#SEQ_ID#-4"></select>

Attribute data-lov defines shared components LOV name used for column select list.
Include attribute data-display-null if LOV should have null value. This attribute value is used as null display value.

From report query you can see that apex_collection columns n004 and n005 are defined two times with different alias. We did set columns with alias MGRNO and DETPNO as hidden column. Attribute data-last-value value should be set to that hidden column substitution string because we like have real column value to this attribute. If we use column substitution string value from column where Display Type was set to Display as Text (based on LOV, escape special characters), then attribute gets LOV display value. And select list do not work correctly in that case.

Edit DNAME column attributes and change Display Type to Display as Text (based on LOV, escape special characters).
Set Named List of Values to DEPARTMENT

Set column formatting

<select class="ir-edit-input" data-lov="DEPARTMENT" data-last-value="#DEPTNO#" id="N-#SEQ_ID#-5"></select>

Setup for other columns see previous blog post.

Create After Footer PL/SQL process:

DECLARE
      cur  SYS_REFCURSOR;
      lov_d VARCHAR2(4000);
      lov_r VARCHAR2(4000);
      l_cnt PLS_INTEGER;
    BEGIN
      htp.prn('<script type="text/javascript">');
      FOR c1 IN (
        SELECT list_of_values_query
         ,list_of_values_name
        FROM apex_application_lovs
        WHERE application_id = :APP_ID
        AND list_of_values_name IN('MANAGER', 'DEPARTMENT')
      ) LOOP
        l_cnt := 0;
        OPEN cur FOR c1.list_of_values_query;
        LOOP
        FETCH cur INTO lov_d, lov_r;
        EXIT WHEN cur%NOTFOUND;
          l_cnt := cur%ROWCOUNT;
          IF l_cnt = 1 THEN
            htp.prn('var ' || c1.list_of_values_name || ' = [{');
          ELSE
            htp.prn(',{');
          END IF;
          htp.prn('"D":"' || lov_d || '","R":"' || lov_r || '"}');
        END LOOP;
        CLOSE cur;
        IF l_cnt = 0 THEN
          htp.p('var ' || c1.list_of_values_name || ' = [];');
        ELSE
          htp.p('];');
        END IF;
      END LOOP;
      htp.prn('</script>');
    END;

This process will print LOV values as JSON objects.

We need JavaScript function that creates select list options from JSON objects.
Add to page JavaScript Function and Global Variable Declaration:

(function($){
     $.fn.htmldbCreateSelect = function(){
      this.filter("select").empty().each(function(i){
       lSelf = $(this);
       lov = eval(lSelf.data("lov"));
       if(lSelf.data("display-null")){
        lSelf.append($("<option/>",{"value":"","text":lSelf.data("display-null")}));
       }
       $.each(lov,function(i,d){
        lSelf.append($("<option/>",{"value":d["R"],"text":d["D"]}));
       });
       lSelf.val(lSelf.data("last-value"));
      });
      return this;
     }
    })(apex.jQuery);

Next create dynamic action

• Name: Edit IR
• Event: After Refresh
• Selection Type: Region
• Region: {select IR region}
• Condition: -No Condition-
• Action: Execute JavaScript code
• Fire On Page Load: True
• Selection Type: None
• Code:

$(this.triggeringElement)
    .find("td .ir-edit-input")
    .change({region:this.triggeringElement},function(e){
     var $lThis=$(this), $lRegion=$(e.data.region);
     apex.server.process("SAVE_DATA",{
      x01:this.id,
      f01:$s_Split(this.value, 4000)
     },{
      dataType:"html",
      beforeSend:function(){
       $lThis.prop("disabled", true).parent("td").addClass("ui-autocomplete-loading");
      },
      success:function(pData){
       if(pData!=="OK"){
        alert(pData);
        $lThis.val($lThis.data("last-value")).prop("disabled", false).parent("td").removeClass("ui-autocomplete-loading");
        return false;
       }
       /* Refresh IR after value is saved */
       $lRegion.trigger("apexrefresh");
      }
     });
    })
    .htmldbCreateSelect()
    .filter("[id^=D]")
    .datepicker({
     dateFormat:"dd.mm.yy",
     constrainInput:true,
     changeMonth:true,
     changeYear:true,
     showButtonPanel:true,
     showOn:"both",
     buttonImageOnly:true,
     buttonImage:"#IMAGE_PREFIX#asfdcldr.gif",
     showAnim:"slideDown"
    });
    

Then create On Demand process called SAVE_DATA

DECLARE
      l_val      VARCHAR2(32700);
      l_len      PLS_INTEGER := 0;
      l_data     CLOB;
      l_date_val DATE;
      l_num_val  NUMBER;
      l_arr      APEX_APPLICATION_GLOBAL.VC_ARR2;
    BEGIN
      l_len := APEX_APPLICATION.G_f01.COUNT;
     
      IF l_len > 0 THEN
        l_val := APEX_APPLICATION.G_f01(1);
      END IF;
      l_arr := APEX_UTIL.STRING_TO_TABLE(APEX_APPLICATION.G_X01, '-');
      IF l_arr(1) = 'C' THEN
        APEX_COLLECTION.UPDATE_MEMBER_ATTRIBUTE (
          p_collection_name => 'EMP',
          p_seq             => l_arr(2),
          p_attr_number     => l_arr(3),
          p_attr_value      => l_val
        );
      ELSIF l_arr(1) = 'N' THEN
        BEGIN
          l_num_val := TO_NUMBER(l_val);
        EXCEPTION WHEN VALUE_ERROR
        THEN
          HTP.PRN(q'[Couldn't save value. Please check that value is valid number.]');
          RETURN;
        END;
        APEX_COLLECTION.UPDATE_MEMBER_ATTRIBUTE (
          p_collection_name => 'EMP',
          p_seq             => l_arr(2),
          p_attr_number     => l_arr(3),
          p_number_value    => l_num_val
        );
      ELSIF l_arr(1) = 'D' THEN
        BEGIN
          l_date_val := TO_DATE(l_val, 'DD.MM.YYYY');
        EXCEPTION WHEN OTHERS
        THEN
          HTP.PRN(q'[Couldn't save value. Please check that value is valid date.]');
          RETURN;
        END;
        APEX_COLLECTION.UPDATE_MEMBER_ATTRIBUTE (
          p_collection_name => 'EMP',
          p_seq             => l_arr(2),
          p_attr_number     => l_arr(3),
          p_date_value      => l_date_val
        );
      ELSIF l_arr(1) = 'CLOB' THEN
       
        IF l_len = 0
        OR COALESCE(LENGTH(APEX_APPLICATION.G_f01(1)), 0) = 0
        THEN
          APEX_COLLECTION.UPDATE_MEMBER_ATTRIBUTE (
            p_collection_name => 'EMP',
            p_seq             => l_arr(2),
            p_clob_number     => 1,
            p_clob_value      => NULL
          );
        ELSE
         
          dbms_lob.createtemporary(
            lob_loc => l_data,
            cache   => TRUE,
            dur     => dbms_lob.session
          );
          dbms_lob.open(l_data, dbms_lob.lob_readwrite);
       
          FOR i IN 1 .. l_len
          LOOP
            dbms_lob.writeappend(
              lob_loc => l_data,
              amount  => LENGTH(APEX_APPLICATION.G_f01(i)),
              buffer  => APEX_APPLICATION.G_f01(i)
            );
          END LOOP;
          dbms_lob.close(l_data);
          APEX_COLLECTION.UPDATE_MEMBER_ATTRIBUTE (
            p_collection_name => 'EMP',
            p_seq             => l_arr(2),
            p_clob_number     => 1,
            p_clob_value      => l_data
          );
        END IF;
      END IF;
     
      HTP.PRN('OK');
    END;

And for last add to page CSS inline:

.ui-autocomplete-loading {
     background: url("#IMAGE_PREFIX#libraries/jquery-ui/1.8/themes/base/images/ui-anim_basic_16x16.gif") no-repeat scroll 98% 10% #EFEFEF !important;
    }
    .apexir_WORKSHEET_DATA td {
     padding-right: 22px !important;
     white-space: nowrap;
     vertical-align: top;
    }
    .apexir_WORKSHEET_DATA .ir-edit-input {
     color: inherit;
     text-align: inherit;
    }

Now when you run page, you should have "editable IR" where is select list for manager and department, and text area for notes.

See working example.

Sample is also available for download.