Advanced Functionality in Portal Applications

1
Advanced Functionality in Portal Applications

• Peter A. Brassington
• MFG Systems

2
Intended Audience

• Portal Developers with the following experience.
• Wizard Based component Development
• Web PL/SQL knowledge including HTP,HTF packages.
• Very basic knowledge of HTML java-script

3
Oracle Portal Application Development

• Wizard Based Component Development
• Enhancements using PL/SQL custom code blocks.
• Embed database procedures in wizard based
  components
• Customize layout by altering or adding to the
  generated HTML.
• Embed custom java-script in components

4
HTML Weaknesses

• Stateless Cannot store and retrieve variables
  across different pages.
• Type Ahead - Cannot fill in fields as data is
  entered.
• Validation Cannot Validate entered values based
  on business rules.
• Cannot Conditionally hide/display parts of a
  page.

5
Three Problems Discussed

1. Building a custom LOV to replace default LOV
   limitations.
2. Auto populating description fields based on
   values entered in a code field.
3. Dynamically generate form buttons to provide
   navigation to other components based on the
   forms data values.

6
Default LOV Limitations

• Lists may be too large making it difficult to
  find values.
• Search criteria cannot be used to filter lists.
• Multiple fields cannot be displayed in the LOV.
• Multiple form fields cannot be populated from the
  LOV.

7
Problem 1

• Building a custom LOV to replace default LOV
  limitations.

8
Form with custom LOV
The Search button calls the LOV parameter page.
Clicking the Select button in the LOV returns the
value to the form.
9
Steps to implement custom LOV

• Add a button next to field requiring the LOV.
• Define a SQL report that will generate the LOV.
• Define Custom logic on the forms button to do
  the following.
• Create a session and store the forms name and
  module session id.
• Call the parameter form of the report that will
  generate the LOV.
• Define a function that will generate a row level
  button in the report and include the function in
  the reports SQL.
• Define a procedure to process the report buttons.
  This procedure should set form values in the
  calling form based on the selected record and
  return control to the calling form.

10
Custom LOV SQL Report (Step 2)

• SELECT PORTAL30_DEMO.DISPLAY_RPT_DEPT_BUTTON
  (A.DEPTNO) Sel, A.DEPTNO, A.DNAME,
  A.LOC,'lt/FORMgt' fc FROM PORTAL30_DEMO.DEPT A
  Where A.DEPTNO like department
• The following must be displayed as HTML
• PORTAL30_DEMO.DISPLAY_RPT_DEPT_BUTTON(A.DEPTNO)
  generates the select button on the report and
  captures the deptno to be used for additional
  processing.
• 'lt/FORMgt' generates the closes form tag
  corresponding to the open form tag for each
  Select button.

11
Custom LOVForm Button Logic (Step 3)
DECLARE /Define and load a session that you
will reference / v_session portal30.wwsto_api_s
ession portal30.wwsto_api_session.load_session(
'CONTEXT', 'MY_SESSION') BEGIN /Define and
set variables (attributes) in the session /
v_session.set_attribute('FORM_SESSION_ID',
p_session."_id") v_session.set_attribute('FOR
M_NAME', p_session."_module".name) /Save
the referenced session / v_session.save_sessi
on /Call the parameter form of the report
that will generate the list /
portal30.wwa_app_module.set_target('PORTAL30_DEMO.
RPT_DEPT.show_parms','CALL') END
Custom logic above for this button.
12
Custom LOVForm Button Logic (Step 3)
DECLARE /Define and load a session that you
will reference / v_session portal30.wwsto_api_s
ession portal30.wwsto_api_session.load_session(
'CONTEXT', 'MY_SESSION') BEGIN /Define and
set variables (attributes) in the session /
v_session.set_attribute('FORM_SESSION_ID',
p_session."_id") v_session.set_attribute('FOR
M_NAME', p_session."_module".name) /Save
the referenced session / v_session.save_sessi
on /Call the parameter form of the report
that will generate the list /
portal30.wwa_app_module.set_target('PORTAL30_DEMO.
RPT_DEPT.show_parms','CALL') END
Custom logic above for this button.
13
Custom LOV Function to Generate Select Button
(Step 4)
Function PORTAL30_DEMO.DISPLAY_RPT_DEPT_BUTTON
(dept_no IN number) RETURN varchar2 IS tag
varchar2(4000) BEGIN tag 'lt!----
Custom code for Select dept button ----gt'
'ltFORM ACTION"/pls/' portal30.wwctx_api.get_
dad_name '/portal30_demo.ADD_DEPT_NO"gt'
'ltINPUT TYPEHIDDEN NAMEp_dept_no VALUE""gt
'ltINPUT TYPESubmit NAMEp_button
VALUE"Select" onClick"this.form.p_dept_no.value
' dept_no '"gt' return tag END
Function generates these buttons.
14
Custom LOVSelect Button Logic (Step 5)
Procedure PORTAL30_DEMO.ADD_DEPT_NO (p_dept_no
number, p_button varchar2 default null)
IS /Define a module session that will reference
he calling form module. / p_session
PORTAL30.wwa_api_module_session /Define a user
session that will reference user-defined sessions
/ v_session portal30.wwsto_api_session id
number begin / Load the user defined session
and retrieves the stored module name and session
id / v_session portal30.wwsto_api_session.load
_session('CONTEXT','MY_SESSION') id
v_session.get_attribute_as_number('FORM_SESSION_ID
') / Load the module session and set the
deptno field in the form / p_session
PORTAL30.wwa_api_module_session.load_session(id)
if (p_session."_module".name 'EXAMPLE_FORM')
then p_session.set_value('DEFAULT','A_DEPTNO',
p_dept_no) p_session.save_session
/Return control back to the calling form /
owa_util.redirect_url('/pls/'
portal30.wwctx_api.get_dad_name
'/PORTAL30.wwa_app_module.show?p_sessionid'
p_session."_id") end if exception when others
then null end
15
Custom LOVSelect Button Logic (Step 5)
Procedure PORTAL30_DEMO.ADD_DEPT_NO (p_dept_no
number, p_button varchar2 default null)
IS /Define a module session that will reference
the calling form module. / p_session
PORTAL30.wwa_api_module_session /Define a user
session that will reference user-defined sessions
/ v_session portal30.wwsto_api_session id
number begin / Load the user defined session
and retrieves the stored module name and session
id / v_session portal30.wwsto_api_session.load
_session('CONTEXT','MY_SESSION') id
v_session.get_attribute_as_number('FORM_SESSION_ID
') / Load the module session and set the
deptno field in the form / p_session
PORTAL30.wwa_api_module_session.load_session(id)
if (p_session."_module".name 'EXAMPLE_FORM')
then p_session.set_value('DEFAULT','A_DEPTNO',
p_dept_no) p_session.save_session
/Return control back to the calling form /
owa_util.redirect_url('/pls/'
portal30.wwctx_api.get_dad_name
'/PORTAL30.wwa_app_module.show?p_sessionid'
p_session."_id") end if exception when others
then null end
16
Custom LOVSelect Button Logic (Step 5)
Procedure PORTAL30_DEMO.ADD_DEPT_NO (p_dept_no
number, p_button varchar2 default null)
IS /Define a module session that will reference
he calling form module. / p_session
PORTAL30.wwa_api_module_session /Define a user
session that will reference user-defined sessions
/ v_session portal30.wwsto_api_session id
number begin / Load the user defined session
and retrieves the stored module name and session
id / v_session portal30.wwsto_api_session.load
_session('CONTEXT','MY_SESSION') id
v_session.get_attribute_as_number('FORM_SESSION_ID
') / Load the module session and set the
deptno field in the form / p_session
PORTAL30.wwa_api_module_session.load_session(id)
if (p_session."_module".name 'EXAMPLE_FORM')
then p_session.set_value('DEFAULT','A_DEPTNO',
p_dept_no) p_session.save_session
/Return control back to the calling form /
owa_util.redirect_url('/pls/'
portal30.wwctx_api.get_dad_name
'/PORTAL30.wwa_app_module.show?p_sessionid'
p_session."_id") end if exception when others
then null end
17
Custom LOVSelect Button Logic (Step 5)
Procedure PORTAL30_DEMO.ADD_DEPT_NO (p_dept_no
number, p_button varchar2 default null)
IS /Define a module session that will reference
he calling form module. / p_session
PORTAL30.wwa_api_module_session /Define a user
session that will reference user-defined sessions
/ v_session portal30.wwsto_api_session id
number begin / Load the user defined session
and retrieves the stored module name and session
id / v_session portal30.wwsto_api_session.load
_session('CONTEXT','MY_SESSION') id
v_session.get_attribute_as_number('FORM_SESSION_ID
') / Load the module session and set the
deptno field in the form / p_session
PORTAL30.wwa_api_module_session.load_session(id)
if (p_session."_module".name 'EXAMPLE_FORM')
then p_session.set_value('DEFAULT','A_DEPTNO',
p_dept_no) p_session.save_session
/Return control back to the calling form /
owa_util.redirect_url('/pls/'
portal30.wwctx_api.get_dad_name
'/PORTAL30.wwa_app_module.show?p_sessionid'
p_session."_id") end if exception when others
then null end
18
SummarizeSteps to implement custom LOV

• Add a button next to field requiring the LOV.
• Define a SQL report that will generate the LOV.
• Define Custom logic on the forms button to do
  the following.
• Create a session and store the forms name and
  module session id.
• Call the parameter form of the report that will
  generate the LOV.
• Define a function that will generate a row level
  button in the report and include the function in
  the reports SQL.
• Define a procedure to process the report buttons.
  This procedure should set form values in the
  calling form based on the selected record and
  return control to the calling form.

19
Problem 2

• Auto populating description fields based on
  values entered in a code field.

20
Auto Populating Description Fields

• Requirements
• Enter code fields and have the description fields
  populated automatically
• Description fields should be populated as soon as
  the user navigates from the entered field
• Obstacles
• Description fields are display only fields and
  are not columns in the forms base table
• Portals built-in APIs (e.g. p_session.set_value)
  cannot be used to populate non-base table
  fields.
• E.g. p_session.set_value

21
Auto Populating Description FieldsSteps

• Add a display field to the form. Set the field
  as updateable so that it shows up on the form.
• Add the following code to the onChange JavaScript
  event handler do_event(this.form,this.name,1,'ON_C
  LICK','') to force the form to submit itself.
• Add code to the forms after displaying the
  page PL/SQL block. This code will populate the
  Department Description field when the form is
  refreshed and a Department Number is present.

22
Auto Populating Description Fieldsafter
displaying the page block

• v_dept_name varchar2(200)
• v_dept_no number
• begin
• / Use built in Portal APIs to grab the dept_no
  value /
• v_dept_no p_session.get_value_as_number(p_blo
  ck_name gt 'DEFAULT',
• p_attribute_name gt 'A_DEPTNO')
• /If the dept_no is valid fetch the description
  from the database otherwise set Dept. description
  to INVALID DEPT/
• if v_dept_no is not null then
• begin
• select dname into v_dept_name from
  portal30_demo.dept
• where deptno v_dept_no
• exception when no_data_found then
• v_dept_name 'INVALID DEPT.'
• end
• end if
• /Use JavaScript to set the description field
  value /
• htp.p('ltSCRIPT LANGUAGE"Javascript1.1"gt var
  deptname new String("EXAMPLE_FORM.DEFAULT.DEPT_D
  ESC.01") document.forms0.elementseval(deptname
  ).value"'v_dept_name'"lt/SCRIPTgt')
• end

ltSCRIPT LANGUAGE"Javascript1.1"gt var deptname
new String("EXAMPLE_FORM.DEFAULT.DEPT_DESC.01")
document.forms0.elementseval(deptname).value"
RESEARCH"lt/SCRIPTgt
23
Auto Populating Description FieldsExample

• The Dept Name is a display only field is
  populated when
• The dept No field is changed and the user
  navigates from the field. (This results in a page
  refresh)
• Records Are queried in the form.

24
Problem 3

• Dynamically generate form buttons to provide
  navigation to other components based on the form
  values.

25
Displaying Conditional Buttons

• Requirements
• Display additional buttons at the bottom of the
  form.
• These buttons are for navigation to other forms
  and are based on the data retrieved in the form.

26
Displaying Conditional Buttons
These buttons are displayed after data is queried
and may vary depending on the data
27
Displaying Conditional ButtonsSteps

1. Create a stored procedure to generate button(s).
2. Create a stored procedure that will take form
   values when a button is pressed and links to
   other components.
3. Add code to the forms after displaying the
   page PL/SQL block to call the procedure created
   in Step1.

28
Displaying Conditional ButtonsGenerating
Buttons (Step 1)

• procedure compound_buttons(p_compound_id IN out
  number) is
• v_comp_count number 0
• begin
• if p_compound_id is not null then
• /Define a PL/SQL form. The compound_id and the
  button name are submitted when the buttons are
  processed. /
• htp.formopen('process_compound_buttons')
• / Define hidden form field for form values used
  in processing/
• htp.formhidden('p_compound_id',p_compound_id)
• select count() into v_comp_count
• from compound_structures c
• where c.compound_id p_compound_id
• / This section will be used to call a form in
  either Insert or Update mode depending on the
  results of the above query. /
• if v_comp_count 0 then
• htp.formsubmit('p_request','Add
  Structure')
• else
• htp.formsubmit('p_request','View
  Structure')
• end if
• / htp.formsubmit submits form buttons for
  processing /
• htp.formsubmit('p_request','Associate Person to
  Compound')

29
Displaying Conditional ButtonsGenerating
Buttons (cont.) (Step 1)

• procedure compound_buttons(p_compound_id IN out
  number) is
• htp.formopen('process_compound_buttons')
• htp.formhidden('p_compound_id',p_compound_id)
• if v_comp_count 0 then
• htp.formsubmit('p_request','Add
  Structure')
• else
• htp.formsubmit('p_request','View
  Structure')
• end if
• htp.formsubmit('p_request','Associate Person to
  Compound')
• htp.formsubmit('p_request','Associate Parent to
  Compound')
• htp.formclose

ltFORM ACTION"process_compound_buttons"
METHOD"POST"gt ltINPUT TYPE"hidden"
NAME"p_compound_id" VALUE"33864"gt ltINPUT
TYPE"submit" NAME"p_request" VALUE"Add
Structure"gt ltINPUT TYPE"submit" NAME"p_request"
VALUE"Associate Person to Compound"gt ltINPUT
TYPE"submit" NAME"p_request" VALUE"Associate
Parent to Compound"gt lt/FORMgt
30
Displaying Conditional ButtonsProcessing
Buttons (Step 2)

• PROCEDURE PROCESS_COMPOUND_BUTTONS(p_request
  IN VARCHAR2 default null,p_compound_id IN
  VARCHAR2 default null) IS
• p_request_upper varchar2(60)
• temp_vals varchar2(400)
• BEGIN
• p_request_upper upper(p_request)
• /The following code calls a portal form in
  insert mode setting the forms compound_id
  field/
• If p_request_upper 'ADD STRUCTURE' then
• PORTAL30.wwa_app_module.link(p_arg_names gt
  portal30.wwv_standard_util.string_to_table2('_modu
  leidcompound_id'), p_arg_values gt
  portal30.wwv_standard_util.string_to_table2('41033
  99196'P_compound_id))
• /The following code calls other portal forms
  querying them based on the compound_id/
• elsif p_request_upper 'VIEW STRUCTURE' then
• temp_vals '4103399196YES'to_number
  (p_compound_id)''
• PORTAL30.wwa_app_module.link(p_arg_names
  gt portal30.wwv_standard_util.string_to_table2('_m
  oduleid_show_headercompound_id_compound_id_cond
  '), p_arg_values gt portal30.wwv_standard_util.str
  ing_to_table2(temp_vals))
• elsif p_request_upper 'ASSOCIATE PARENT TO
  COMPOUND' then
• temp_vals '12143076441YES'to_number(p
  _compound_id)''
• PORTAL30.wwa_app_module.link(p_arg_names gt
  portal30.wwv_standard_util.string_to_table2('_modu
  leid_show_headercompound_id_compound_id_cond'),
  p_arg_values gt portal30.wwv_standard_util.string
  _to_table2(temp_vals))
• end if
• END

31
Displaying Conditional ButtonsProcessing
Buttons(Module Links) (Step 2)

• PROCEDURE PROCESS_COMPOUND_BUTTONS(p_request
  IN VARCHAR2 default null,p_compound_id IN
  VARCHAR2 default null) IS
• p_request_upper varchar2(60)
• temp_vals varchar2(400)
• BEGIN
• p_request_upper upper(p_request)
• /The following code calls a portal form in
  insert mode setting the forms compound_id
  field/
• If p_request_upper 'ADD STRUCTURE' then
• PORTAL30.wwa_app_module.link(p_arg_names gt
  portal30.wwv_standard_util.string_to_table2('_modu
  leidcompound_id'), p_arg_values gt
  portal30.wwv_standard_util.string_to_table2('41033
  99196'P_compound_id))
• /The following code calls other portal forms
  querying them based on the compound_id/
• elsif p_request_upper 'VIEW STRUCTURE' then
• temp_vals '4103399196YES'to_number
  (p_compound_id)''
• PORTAL30.wwa_app_module.link(p_arg_names
  gt portal30.wwv_standard_util.string_to_table2('_m
  oduleid_show_headercompound_id_compound_id_cond
  '), p_arg_values gt portal30.wwv_standard_util.str
  ing_to_table2(temp_vals))

32
Displaying Conditional ButtonsPlacing Buttons
on Form (Step 3)

• The following code is added to the forms after
  displaying the page PL/SQL block

declare v_compound_id varchar2(10) begin
/ captures the value of the compound_id in the
form to pass it to the procedure displaying the
buttons. The procedure will only display buttons
if this is a valid compound_id. If no values are
passed, no buttons are displayed. /
v_compound_id p_session.get_value_as_NUMBER(p_b
lock_name gt 'MASTER_BLOCK', p_attribute_name
gt 'A_COMPOUND_ID') sms.inventory_custom_logic.c
ompound_buttons(v_compound_id) end
33
Displaying Conditional ButtonsSteps (Summarized)

1. Create a stored procedure to generate button(s).
2. Create a stored procedure that will take form
   values when a button is pressed and links to
   other components.
3. Add code to the forms after displaying the
   page PL/SQL block to call the procedure created
   in Step1.

34
Additional Info

• technet.oracle.com
• Discussion Forums
• White papers
• User Group Presentations/White papers
• The corresponding white paper to this
  presentation was published in Select magazine in
  September 2002.

35
Questions?

• Peter Brassington
• brassington_at_mfgsys.com