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.