APex5 TeamDev Area

Tweaking APEX 5 team development

or How to enhance feedbacks to get a new status

In a current project we do use feedbacks a lot. We discussed the need to add more and different feedback status types to allow a more detailed way of tracking some issues.

The application is not live yet. So this is a DEV and TEST system only. However this environment features some unusal aspects, which I might explain in another blog.

Disclaimer:All of this is not supported! So do it at your own risk. If you repeat this, then you probably won’t break your current installation. But there is a certain risk that you will get issues during future APEX upgrades or bugfixes.

This was all tested using an Apex 5.0.1 installation. Obviously this will not work in a cloud environment where you have no access to the apex_050000 schema.

Create a new additional feedback status type

The feedback status has an id and a name. The status_id is stored together with the feedback. The name of the status is made available via the apex view WWV_FLOW_FEEDBACK_STATUS.

In this view the four status ids are hardcoded. The name fetched from the apex internal system message tables. This is done so that the feedback status is shown depending on the current application builder language.

So to add a new feedback status several objects need to change. The view, the system messages, several LOVs (mostly sorting issues) and if wanted also the feedback main dashboard and the tool bar in the page designer.

I will show how to change all that step by step by creating a new feedback status “TESTING”.

Set environment
I was doing those changes in a 12.1.0.1 PDB as SYS. It should be possible to do the same just using DBA privileges.

First I setup my environment, so that everthing is done inside the APEX schema.

alter session set current_schema = apex_050000;
execute wwv_flow_security.g_security_group_id := 10;

Create a new message
Since my apex installation has the application builder installed in English (standard) and in German, we need to applied some changes two times. Once for each language. In general the translated applications have a specific offset (+3 for German).

The flow ID is the application ID. The apex installation files (also the lang_de.sql for the language specific installations) also have the flow_id as the file name. The F4411.sql file installs all the system messages. We can simply add our own new system message to that.

begin
 wwv_flow_api.create_message(
       p_id=>null
      ,p_flow_id => 4411
      ,p_name=>'TESTING'
      ,p_message_language=>'en'
      ,p_message_text=>'Testing'
      );

 wwv_flow_api.create_message(
     p_id => null
    ,p_flow_id => 4414
    ,p_name => 'TESTING'
    ,p_message_language => 'de'
    ,p_message_text => 'Testing'
    );
end;
/

I used ID=NULL, so that the trigger will use a sequence to set a new ID value for this system message. This could be problematic when a future patch set is installed. As long as a new apex version is installed in its own schema (e.g. APEX_050100) then we should be save. But if some patch or bugfix installs a system message using the same ID that we just used, it might not be installed. In that case simply delete the system message and rerun the bugfix. The system messages themselfs are not references via IDs. They are always referenced by value and then delivers the name appropriate to the current language.

wwv_flow_lang.system_message(‘CLOSED’)

  • English = “Closed”
  • German = “Geschlossen”

Change the feedback status view

First we make a copy of the current view. Just so that we can more easily revert back to the old code, in case something happens. You should copy and past the real metadata ddl from the view.

CREATE OR REPLACE VIEW WWV_FLOW_FEEDBACK_STATUS_OLD (ID, THE_NAME) AS 
  select 0 id, wwv_flow_lang.system_message('NO_STATUS') the_name from sys.dual union all
  select 1 id, wwv_flow_lang.system_message('ACKNOWLEDGED') the_name from sys.dual union all
  select 2 id, wwv_flow_lang.system_message('ADDITIONAL_INFORMATION_REQUESTED') the_name from sys.dual union all
  select 3 id, wwv_flow_lang.system_message('OPEN_PROCESSING_FEEDBACK') the_name from sys.dual union all
  select 4 id, wwv_flow_lang.system_message('CLOSED') the_name from sys.dual
  ;

And we change the view to include the new status.

CREATE OR REPLACE VIEW WWV_FLOW_FEEDBACK_STATUS (ID, THE_NAME) AS 
  select 0 id, wwv_flow_lang.system_message('NO_STATUS') the_name from sys.dual union all
  select 1 id, wwv_flow_lang.system_message('ACKNOWLEDGED') the_name from sys.dual union all
  select 2 id, wwv_flow_lang.system_message('ADDITIONAL_INFORMATION_REQUESTED') the_name from sys.dual union all
  select 3 id, wwv_flow_lang.system_message('OPEN_PROCESSING_FEEDBACK') the_name from sys.dual union all
  select 15 id, wwv_flow_lang.system_message('TESTING') the_name from sys.dual union all
  select 4 id, wwv_flow_lang.system_message('CLOSED') the_name from sys.dual
  ;

change LOV sorting
This change does not seem to be working immediately. There seems to be some kind of caching involved.

update wwv_flow_lists_of_values$
set lov_query = q'[select id||'. '||the_name the_name, id 
from wwv_flow_feedback_status 
order by decode(id,4,99,id)]'
where flow_id in (4000,4003,4800,4803)
and lov_name = 'FEEDBACK STATUS'
;

update wwv_flow_step_items
set lov = q'[select id||'. '||the_name 
the_name, id 
from wwv_flow_feedback_status 
order by decode(id,4,99,id)]'
where flow_id in (4800,4803)
and name = 'P8001_FEEDBACK_STATUS'
;

After that, the select list when editing feedback entries will be sorted in such a way that the CLOSED status is always at the end. No matter how many new feedback status you want to add, as long as those new IDs are all below 99.

Changing the dashboard
Two changes need to be made on the dashboard. One is to show the new entry. The second one is to show 6 lines instead of 5 lines as before. This is the setting of attribute_14

update wwv_flow_page_plugs
set plug_Source = q'[select /* APEX4800P8000b42 */
    s.the_name,
    (select count(*) 
     from wwv_flow_feedback f
     where f.security_group_id = :flow_security_group_id and
     nvl(f.feedback_status,0) = s.id) feedback_count,
     'f?p=4800:8000:'||:APP_SESSION||':::8000,RIR:IR_FEEDBACK_STATUS_ID:'||s.id link_url
from wwv_flow_feedback_status s
order by decode(id,4,99,id)]'
 ,attribute_14 = '6' -- how many rows to show
where flow_id in (4800, 4803)
and page_id like '8012%'
and plug_name like '% Status'
;

And this is how it looks after the change. One more horizontal bar is shown and the ordering of the different status has changed.
Apex5 Teamdevelopment Dashboard

Changing the Toolbar in the Pagedesigner
This has been the most challenging part so far.

Apex5_Teamdev_FBEntriesCount

I first had some troubles to find where this is located. Eventually discovered it in some installation script. The whole team-dev dropdown menu is stored in one single place.

This is the original select that is executed to find the number of feedback entries.

select count(*) c
from wwv_flow_feedback
where application_id = :fb_flow_id
and page_id = :fb_flow_page_id 
and security_group_id = :flow_security_group_id 
and nvl(feedback_status,0) < 3

It simply counts all the feedbacks for the current application. Feedbacks with status “CLOSED” (id 4) or “OPEN_PROCESSING_FEEDBACK” (id 3) are not counted.

Since I want to add the new status “TESTING” (id 15) I first change the filter to NOT IN.

But there is more. In this workspace I do not only have one application but three applications which are essentially a copy of the first one. They are called XXX_ALPHA, XXX_BETA, XXX_BASE. One is used for development purposes, the other two are used for testing. I want to see the feedbacks from the test applications also counted into the development environment.

The first idea is to change the select so, that it does some logic based upon the name. But there is a much better way: Application groups! We can organize applications that are in the same workspace to form application groups. This is easily done. Create a new application group and add all the applications that belong together into such a group.

Now we can count the feedbacks for all applications that are in the same group.

select count(*) c
from wwv_flow_feedback fb
join wwv_flows a on fb.application_id = a.id
-- show feedbacks from all applications in the same group
where a.group_id 
      = (select ap.group_id
         from wwv_flows ap
         where ap.security_group_id = :flow_security_group_id 
         and ap.id = :fb_flow_id)
and fb.page_id = :fb_flow_page_id 
and fb.security_group_id = :flow_security_group_id 
and nvl(fb.feedback_status,0) not in (3,4)
;

This select needs to be integrated into a more complicated thing. I also changed the select a tiny bit to consider appliactions correctly that do not have an application group set.

This time the quoted syntax really is helpful. The syntax checker unfortunatly has problems showing it correctly for q'[]’. Therefore SQL syntax highlighting is disabled now.

update wwv_flow_page_plugs
set plug_source = q'[sys.htp.p('
<div class="pageTeamDevBar">');
sys.htp.p('
<ul>');
for c1 in (select count(*) c
           from wwv_flow_tasks 
           where application_id = :fb_flow_id and 
                 page_id = :fb_flow_page_id and
                 security_group_id = :flow_security_group_id and
                 nvl(task_status,0) < 100
          ) 
loop
  sys.htp.p('
<li><a href="f?p=4800:3000:'||:app_session||':BUILDER::RIR:IR_APPLICATION_ID,IR_PAGE_ID,IRLT_PCT_COMPLETE:'||:fb_flow_id||','||:fb_flow_page_id||',100'||'" title="'||wwv_flow_lang.system_message('TO_DOS')||'"><span class="a-Icon icon-check"></span><span>'||c1.c||'</span></a></li>
');
end loop;
for c1 in (select count(*) c
           from wwv_flow_feedback fb
           -- show feedbacks from all applications in the same group
           join wwv_flows a on fb.application_id = a.id
           where (exists (select null from wwv_flows ap
                   where ap.security_group_id = :flow_security_group_id 
                   and ap.id = :fb_flow_id
                   and a.group_id = ap.group_id
                   )
            OR (a.group_id is null 
               and fb.application_id = :FB_FLOW_ID))
           and fb.page_id = :fb_flow_page_id 
           and fb.security_group_id = :flow_security_group_id 
           and nvl(fb.feedback_status,0) not in (3,4)
) 
loop
  sys.htp.p('
<li><a href="f?p=4800:8000:'||:app_session||':BUILDER::RIR:IR_APPLICATION_ID,IR_PAGE_ID,IRNIN_FEEDBACK_STATUS_ID:'||:fb_flow_id||','||:fb_flow_page_id||',\3,4\'||'" title="'||wwv_flow_lang.system_message('FEEDBACK_ENTRIES')||'"><span class="a-Icon icon-feedback"></span><span>'||c1.c||'</span></a></li>
');
end loop;
for c1 in (select count(*) c
           from wwv_flow_bugs 
           where application_id = :fb_flow_id and 
                 page_id = :fb_flow_page_id and
                 security_group_id = :flow_security_group_id and
                 nvl(bug_status,0) < 100
          ) 
loop
  sys.htp.p('
<li><a href="f?p=4800:3500:'||:app_session||':BUILDER::RIR:IR_APPLICATION_ID,IR_PAGE_ID,IRLT_BUG_PCT_COMPLETE:'||:fb_flow_id||','||:fb_flow_page_id||',100'||'" title="'||wwv_flow_lang.system_message('BUGS')||'"><span class="a-Icon icon-bug"></span><span>'||c1.c||'</span></a></li>
');
end loop;
for c1 in (select count(*) c
           from WWV_FLOW_APP_COMMENTS
           where flow_id = :fb_flow_id and 
                 instr(','||replace(replace(PAGES,' ',null),':',',')||',',','||:fb_flow_page_id||',') > 0
          ) 
loop
  sys.htp.p('
<li><a href="'||wwv_flow_utilities.prepare_url(p_url=>'f?p=4000:1235:'||:app_session||':SET_CURRENT_PAGE:')||'" title="'||wwv_flow_lang.system_message('COMMENTS')||'"><span class="a-Icon icon-comments"></span><span>'||c1.c||'</span></a></li>
');
end loop;
sys.htp.p('  </ul>
');
sys.htp.p('</div>
');
]'
where plug_name = 'Team Development Bar'
and flow_id in (4000,4003)
;

I also modified the link to the feedback page slightly to give a different filter.
I left the application filter intact there since it is easy to deselect that one.

But the IRLT_FEEDBACK_STATUS_ID:3 was changed to
IRNIN_FEEDBACK_STATUS_ID:\3,4\

NIN is the NOT IN filter, the backslashes on the values are needed so that the comma is not interpreted as the next value in the list.

The same logic is in place for the component view. In total I found 3 places that needed to be changed.

There is an item P4150_OPEN_FEEDBACK that also holds the feedback count

update wwv_flow_step_items
set source = q'[select '<a href="f?p=4800:8000:'||:app_session||
       ':BUILDER::RIR:IR_APPLICATION_ID,IR_PAGE_ID,IRNIN_FEEDBACK_STATUS_ID:'||
       :fb_flow_id||','||:fb_flow_page_id||',\3,4\'||
       '">'||c||'</a>' the_link
from (
select count(*) c
from wwv_flow_feedback fb
join wwv_flows a on fb.application_id = a.id
-- show feedbacks from all applications in the same group
where (exists (select null 
               from wwv_flows ap
               where ap.security_group_id = :flow_security_group_id 
               and ap.id = :fb_flow_id
               and a.group_id = ap.group_id
               )
      OR (a.group_id is null and fb.application_id = :FB_FLOW_ID)
and fb.page_id = :fb_flow_page_id 
and fb.security_group_id = :flow_security_group_id 
and nvl(fb.feedback_status,0) not in (3,4)
) x]'
where flow_id in (4000,4003)
and name =  'P4150_OPEN_FEEDBACK';

There is a page process “GetAdditionalData”

update wwv_flow_step_processing
set process_sql_clob = 
q'[declare
    c_page_id constant number := to_number( wwv_flow.g_x01 );
    l_count pls_integer;
begin

    wwv_flow_json.open_object;

    select count(*)
      into l_count
      from wwv_flow_app_comments
     where flow_id            = :FB_FLOW_ID
       and security_group_id  = :WORKSPACE_ID
       and instr( ',' || replace( replace( pages, ' ', null ), ':', ',' ) || ',' , ',' || c_page_id || ',' ) > 0;

    wwv_flow_json.  write( 'comments', l_count );

    wwv_flow_json.  open_object( 'teamDev' );
    
    select count(*)
      into l_count
      from wwv_flow_features
     where application_id           = :FB_FLOW_ID
       and module                   = to_char( c_page_id )
       and security_group_id        = :WORKSPACE_ID
       and nvl( feature_status, 0 ) < 100;

    wwv_flow_json.    write( 'features', l_count );
    
    select count(*)
      into l_count
      from wwv_flow_bugs
     where application_id       = :FB_FLOW_ID
       and page_id              = c_page_id
       and security_group_id    = :WORKSPACE_ID
       and nvl( bug_status, 0 ) < 100;

    wwv_flow_json.    write( 'bugs', l_count );

    select count(*)
      into l_count
      from wwv_flow_tasks
     where application_id        = :FB_FLOW_ID
       and page_id               = c_page_id
       and security_group_id     = :WORKSPACE_ID
       and nvl( task_status, 0 ) < 100;

    wwv_flow_json.    write( 'todos', l_count );
    
    select count(*)
      into l_count
      from wwv_flow_feedback fb
      join wwv_flows a on fb.application_id = a.id
     where fb.page_id                   = c_page_id
       and fb.security_group_id         = :WORKSPACE_ID
       and nvl( fb.feedback_status, 0 ) not in (3,4)
       and (exists (select null 
                   from wwv_flows ap
                   where ap.security_group_id = :flow_security_group_id 
                   and ap.id = :fb_flow_id
                   and a.group_id = ap.group_id
                   )
         OR (a.group_id is null 
            and fb.application_id = :FB_FLOW_ID)
         )
      ;

    wwv_flow_json.    write( 'feedback', l_count );

    wwv_flow_json.close_all;
end;]'
where flow_id in (4000,4003)
and process_name = 'getAdditionalData';

For some reason the link change for the page designer does not work. I have still to determine the cause of this. I guess the value of the drop down list is copied to some other place after the installation. The calculation is redone using the Json object, but the link is not exchanged. The link in the component view works as expected. It generates a NOT IN filter on the status.

So here is the final result for page designer and component view

Page Designer Toolbar: apex5_tweak_teamdev_PDneu Component view: apex5_tweak_teamdev_CVneu

Link result after click in Component View:

apex5_tweak_teamdev_Filterneu

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s