Demo: https://apex.oracle.com/pls/apex/f?p=80981:3
The old stuff
First step is to refresh your memory and have a glance at the (good) old Forms interface. I even have a screen print of a client/server environment (6i) which still works under a VM / Windows XP:
In Oracle Forms the generic Headstart Report Launch Form looks like this:
The parameters can be defined by the Headstart Foundation Application:
The new way in Apex
In this section I will explain how to create the Report Launch Form in Apex. It won’t be a guided instruction for Novice users, although I do my best to explain the steps with enough details.
Step 1: create a blank Page
We need two Regions on the page:
(a) a master Region where the user can select which report he wants to run (action 1.1 of sequence diagram)
and after selecting it
(b) show the list of parameters and possible values in the detailed section (action 2.1 and 2.2 of sequence diagram)
Step 2: create Region for selecting the report
So now we create first a Static Content Region with the name “Select Report” which will contain the Select List for the available reports (mind you, in Forms the reportmodule to run was tight to the menu option so you had as many menu items as possible reports):
Create Region
Identification:
Title: Select Report
Appearance:
Template: Hero
Icon: fa-list
Add some css to the page to suppress the Region title next to the Hero Icon
Page, Css, Inline:
h1.t-HeroRegion-title {
display:none;
}
Create page item P3_REPORT_MODULE in region “Select Report”
Create Page Item
Identification:
Name: P3_REPORT_MODULE
Type: Select List
Label
Label: <empty>
Settings
Page Action on Selection: Submit Page
Appearance:
Template: Optional
Template Options: Use Template Defaults, Stretch Form Item, Large
List of Values
Type: SQL Query
select upper(implementation_name) || ' ' || title || '(' || purpose || ')' d
, id r
from qms_modules
where short_name like 'HSD%'
and short_name not like '%530R'
order by 1
Null Display Value: -- Select Report --
We can now try to run the page and we should get some results
Step 3: add Region for display report parameters
The content of this region is generated by a stored procedure in the database: P003_RENDER_PARAMS. This is the most interesting part of rendering this form and for the sake of readability, the details of it will be explained in step 4. Be aware that for completing step 3 the package APEXDEMO must already be present in the database. If not, just replace the procedure call by a null; statement and come back to it later on.
Create Region
Identification
Title: Parameters
Type: PL/SQL Dynamic Content
begin
apexdemo.p003_render_params -- report selection
( p_mde_id => :P3_REPORT_MODULE ) ;
end;
Appearance
Template: Blank with Attributes
Server-side Condition
Type: Item is NOT NULL
Item: P3_REPORT_MODULE
Add some CSS styling (btw the id #p3_params comes from the p003_render_params procedure):
Page, Css, Inline:
#p3_params select, #p3_params input {
width: 100%;
}
#p3_params span.prompt.required::after {
content: "*";
padding-left: 5px;
color: red;
font-size: 1.7em;
}
Step 4: Rendering procedure P003_RENDER_PARAMS
The source of the content is in principle a key:value pair of the dynamic SQL-statement of the LOV Query or just a free text-field. Hey: do I read LOV Query? In that case we could use APEX_ITEM.SELECT_LIST_FROM_QUERY function for it? Unfortunately, we cannot. As you can see in table 18-16 of Apex 18.2 API documentation the definition of the query is SELECT <description>, <id> FROM <table>, whereas the LOV query of Headstart is defined by SELECT <id>, <description> FROM <table>. Swapping around is not a good idea, because the statement can be composed of complex definitions.
Since we don’t want to touch the QMS-table content, the workaround for it is to process the dynamic SQL-statement into a collection. That is also my main reason for putting this functionality in the database, I don’t like to put a lot of logic in the Apex front-end.
Also here for reason of comprehensibility I will list only parts of the procedure. The readability of the code is hampered by the html-tags which are necessary for a proper layout of the Region. By the way, the html code is just a copy of an ordinary standard report layout, so starting with headings, table definition and footers. I will concentrate on the row-content which is the core of the procedure
PSEUDO code:
procedure
define array to hold dynamic LOV result
begin
for r in (select parameter records from QMS_MDE_PARAMS)
loop
if LOV defined
exec LOV query bulk collect into array
print apex_item.select_list
else
print apex_item.text
end if;
end loop;
end;
Above pseudo code now in somewhat more detail. In the p_attributes parameter of the APEX_ITEM, I use additional data-elements which will be used in Javascript code to generate the parameter string (in JSON format) (action 3.1 of the sequence diagram) which will be sent to the database to validate and process for the Report Server request (action 3.1.1 of sequence diagram).
procedure p003_render_params -- report selection
( p_mde_id in number -- report ID
) is
.. declare other local variables
type r_lov is record
( code varchar2(255)
, description varchar2(255)
) ;
type t_lov is table of r_lov;
l_lov t_lov;
begin
p003_render_region_start; -- keep layout aside as much as possible
for r in ( select mpm.mde_id
, mpm.mpm_subtype
, decode(mpm.mpm_subtype
, 'SYS', 'u-hot' -- system parameters
, 'u-normal' -- user parameters
) class
, mpm.name
, mpm.data_type
, mpm.ind_mandatory
, decode(mpm.ind_mandatory
, 'Y', 'required'
, 'optional'
) class_mandatory
, mpm.ind_uppercase
, mpm.prompt
, mpm.plength
, mpm.default_value
, mpm.default_description
, mpm.hint_text
, mpm.lov_query
, mpm.lov_multi_select
from qms_mde_params mpm
where mpm.mde_id = p_mde_id
and mpm.name not in ('SERVER','ORACLE_SHUTDOWN')
order by
mpm.mpm_subtype desc
, mpm.display_seqno
)
loop
if r.lov_query is not null
then
begin
execute immediate r.lov_query
bulk collect
into l_lov
;
exception when others then
-- log error SQLERRM
raise_application_error(-20001,'Error in LOV query');
end;
-- p_list_values
-- Example: 'Yes;Y,No;N'
for j in 1 .. l_lov.count
loop
-- avoid undesired tokens
l_lov(j).description := replace(l_lov(j).description, ';');
l_lov(j).description := replace(l_lov(j).description, ',', ' ');
if j = 1
then
l_list_values := l_lov(j).description || ';' || l_lov(j).code ;
else
l_list_values := substr( l_list_values || ',' ||
l_lov(j).description || ';' || l_lov(j).code
, 1, 4000) ;
end if;
end loop;
end if;
if r.lov_query is not null and
r.lov_multi_select = 'N'
then -- select list
sys.htp.prn(apex_item.select_list( p_idx => 1
, p_list_values => l_list_values
, p_attributes => 'data-name="' || r.name || '"; '||
'data-uppercase="' || r.ind_uppercase || '"; '||
'data-required="' || r.ind_mandatory || '"'
, p_value => l_default_value
, p_show_null => 'YES'
, p_null_text => '-- Select '|| r.prompt ||' --'
)
);
elsif r.lov_query is not null and
r.lov_multi_select = 'Y'
then -- multiple select list, no shuttle item available in API
sys.htp.prn(apex_item.select_list( p_idx => 1
, p_list_values => l_list_values
, p_value => null -- l_default_value, doesn't work, have to figure out yet
, p_attributes => 'multiple=true; size=7; '||
'data-name="' || r.name || '"; '||
'data-uppercase="' || r.ind_uppercase || '"; '||
'data-required="' || r.ind_mandatory || '"'
, p_show_null => 'YES'
, p_null_text => '-- Select '|| r.prompt ||' --'
)
);
else -- ordinary text item
sys.htp.prn(apex_item.text( p_idx => 1
, p_item_id => r.name
, p_item_label => r.name
, p_attributes => 'data-name="' || r.name || '" '||
'data-uppercase="' || r.ind_uppercase || '" '||
'data-required="' || r.ind_mandatory || '"'
, p_value => l_default_value
)
);
end if;
end loop;
-- close table and region
p003_render_region_end;
end p003_render_params;
In Headstart, a multi select would result in a Shuttle Control item, however this item type is not available in Apex. Workaround is to script the Shuttle item in html itself, but that’s up to you. For now, it results in a multi-selectlist.
<< Prev: Apex interacts with Report Server >> Next: Submit Apex report parameters