Handling the sort event in Advanced Tables

Advanced Tables are the recommended approach for rendering tables in OA Framework since they offer both declarative support in addition to programmatic control. One of the features available in Advanced Tables is the ability to sort columns  by clicking on the column header. In this post, we will discuss how sort events are handled in Advanced Tables.

In general, the following table events are defined for an Advanced Table.
Navigation – Triggered when a user selects the Next or Previous link to navigate between different ranges of rows.
Sorting – Triggered when a user selects a beveled column heading to sort that column in ascending or descending order.
Insertion of a new row – Triggered when a user selects the Add Another Row button.
Recalculate column Total – Triggered when a user selects the Recalculate button to update the column total.
Detail Disclosure – Triggered when a user selects the Hide or Show link to collapse or expand the detail disclosure region.
Table Control – Triggered when a user selects the Action/Navigation button in the table Control bar.

These events are handled during processFormRequest and OA Framework automatically sets the following parameters based on which event was triggered:
SOURCE_PARAM – indicates the source of the event that is generating the current browser request.
EVENT_PARAM – indicates the event generated by the table.
VALUE_PARAM – indicates a value that is relevant to a particular event. For the sort event, it points to the column on which the sorting is done.
SIZE_PARAM – indicates the number of rows currently displayed in the table (relevant only to the navigation event).
STATE_PARAM – indicates the current sort state (ascending or descending) of the column on which sorting is invoked (relevant only for the sort event).
These parameters can be accessed programatically using pageContext.getParameter(<PARAMETER_NAME>). For example, pageContext.getParameter(SOURCE_PARAM) will fetch the name of the Advanced Table from which an event was triggered.

Below is the Advanced Table, xxAdvTab based on the View Object, xxAdvTabVO.

I have added the following code in the processFormRequest section of the controller in order to display the value of the event parameters during runtime.

if ("xxAdvTab";.equals(pageContext.getParameter(SOURCE_PARAM)))
{OAException mesg = new OAException("SOURCE_PARAM= " +pageContext.getParameter(SOURCE_PARAM)
+", EVENT_PARAM= " +pageContext.getParameter(EVENT_PARAM)
+", VALUE_PARAM= " +pageContext.getParameter(VALUE_PARAM)
+", SIZE_PARAM= " +pageContext.getParameter(SIZE_PARAM)
+", STATE_PARAM= " +pageContext.getParameter(STATE_PARAM)
,OAException.INFORMATION);
pageContext.putDialogMessage(mesg);}

1. Clicking on the Employee Name column sorts it in ascending order and sets the values of the parameters.

SOURCE_PARAM stores the name of the Advaced Table, xxAdvTab.
EVENT_PARAM stores the event, which is sort in this case.
VALUE_PARAM stores the column on which the sort was performed, in this case the second column. The value is 1 since the columns are numbered from 0 onwards.
SIZE_PARAM is null since it is used only in the Navigation event.
STATE_PARAM indicates the current sort state, ie, the sort state when the event was triggered. When the event was triggered, the Employee Name was in an unsorted state hence its value is null.

2. Clicking on the Employee Name column again sorts it in descending order and sets the values of the parameters.

All the parameters have the same values except for STATE_PARAM which is now ascending because when the event was triggered the Employee Name column was already sorted in the ascending order.

3. If the Employee Id column is now sorted, the parameters will have the following values.

SOURCE_PARAM stores the name of the Advaced Table, xxAdvTab.
EVENT_PARAM stores the event, which is sort in this case.
VALUE_PARAM shows 0 since the sort was performed on the first column.
SIZE_PARAM is null since it is used only in the Navigation event.
STATE_PARAM shows null since it shows the current sort state of the column on which the sort was invoked. Employee Id was in an unsorted state hence the value is null.

This brings us to a limitation of the STATE_PARAM parameter, it gives us the current sort state of the column which is being sorted. It does not provide the sort state of the Advanced Table as a whole. In the previous step, even though the Advanced Table was sorted based on the Employee Name in descending order, the STATE_PARAM parameter was set to null. One way of working around this is to get the sort status of the Advanced Table from the ORDER_BY clause of the underlying table. This can be done by changing the controller code as shown below:

//get a handle to the View Object
OAViewObject viewObject = (OAViewObject)oam.findViewObject("xxAdvTabVO1");
if ("xxAdvTab".equals(pageContext.getParameter(SOURCE_PARAM)))
{OAException mesg = new OAException("SOURCE_PARAM= " +pageContext.getParameter(SOURCE_PARAM)
+", EVENT_PARAM= " +pageContext.getParameter(EVENT_PARAM)
+", VALUE_PARAM= " +pageContext.getParameter(VALUE_PARAM)
+", SIZE_PARAM= " +pageContext.getParameter(SIZE_PARAM)
+", STATE_PARAM= " +pageContext.getParameter(STATE_PARAM)
//display the ORDER_BY clause of the VO
+", VO ORDER_BY clause= " +viewObject.getOrderByClause()
,OAException.INFORMATION);
pageContext.putDialogMessage(mesg);}

4. Performing Steps#1, 2 and 3 once again provides the following values of the parameters.

The standard parameters still show the same values as in Step#3, but the VO ORDER_BY clause now correctly shows that the Advanced Table was already sorted based on the Employee Name in the descending order when the sort event was triggered.

Refer to the chapter on Advanced Tables in the Oracle Application Framework Developers Guide for additional details.

List of JDR_UTILS APIs

JDR_UTILS is a PL/SQL package which can be used to query and inspect OA Framework metadata in the MDS repository. The complete list of JDR_UTILS APIs are given below.

Sample usage:

SQL> exec jdr_utils.listcustomizations('/oracle/apps/icx/icatalog/shopping/webui/NonCatalogRequestPG');

/oracle/apps/icx/icatalog/shopping/webui/customizations/site/0/NonCatalogRequestPG

PL/SQL procedure successfully completed

SQL>

PROCEDURE deleteEmptyCustomizations;
Deletes all empty customization documents from the repository. An empty customization document is a customization document that does not specify any modifications to the base metadata.

PROCEDURE deleteDocument(p_document VARCHAR2);
Deletes the document from the repository.
p_document – the fully qualified document name, which can represent either a document or package file.

PROCEDURE deletePackage(p_package VARCHAR2);
Deletes the package from the repository if the package is empty. If the package is not empty (i.e. it contains either documents or packages), then an error will be issued indicated that non-empty packages can not be deleted.
p_package – the fully qualified package name

FUNCTION exportDocument(
p_document VARCHAR2,
p_exportFinished OUT BOOLEAN,
p_formatted BOOLEAN DEFAULT TRUE) RETURN VARCHAR2;
Export the XML for a document and pass it back in 32k chunks. This function will return XML chunks, with a maximum size of 32k. Returns the exported XML in 32k chunks
p_document – the fully qualified name of the document.
p_exportFinished – OUT parameter which indicates whether or not the export is finished
p_formatted – TRUE indicates that the XML is formatted nicely

FUNCTION getComponentName(
p_docid jdr_paths.path_docid%TYPE,
p_compid jdr_components.comp_id%TYPE) RETURN VARCHAR2;
Gets the fully qualified name of the component.
p_docid – the ID of the document which contains the component
p_compid – the ID of the component (from comp_id in the jdr_components table)

FUNCTION getDocumentName(
p_docid jdr_paths.path_docid%TYPE) RETURN VARCHAR2;
Gets the fully qualified name of the document.
p_docid – the ID of the document

FUNCTION getTranslations(
p_document VARCHAR2) RETURN translationList;
Gets all of the translations of the specified document.
p_document – the fully qualified document name

PROCEDURE listContents(p_path VARCHAR2, p_recursive BOOLEAN DEFAULT FALSE);
Prints the contents of a package. For the non-recursive case, this will list the documents, package files and package directories. For the recursive case, this will list the document, package files and empty package directories (i.e. packages which contain no documents or child packages). In order to diferentiate documents from package directories, package directories will end with a ‘/’.
p_path – The path in which to list the documents. To specify the root directory, use ‘/’.
p_recursive – If TRUE, recursively lists the contents of sub-directories. Defaults to FALSE.

PROCEDURE listCustomizations(p_document VARCHAR2);
List the customizations for the specified document.
p_document – the fully qualified document name, which can represent either a document or package file.

PROCEDURE listDocuments(p_path VARCHAR2, p_recursive BOOLEAN DEFAULT FALSE);
Internally calls listContents.

PROCEDURE listLanguages(p_document VARCHAR2);
Lists the supported languages for the specified document.
p_document – the fully qualified document name, which can represent either a document or package file.

PROCEDURE printDocument(p_document VARCHAR2,
p_maxLineSize NUMBER DEFAULT MAX_LINE_SIZE);
Prints the contents of a JRAD document to the console.
p_document – the fully qualified document name, which can represent either a document or package file.
p_maxLineSize – the maximum size of line. This defaults to 255 which is the maximim allowable size of a line (the 255 limit is a limitation of the DBMS_OUPUT package).

PROCEDURE printTranslations(p_document VARCHAR2,
p_language VARCHAR2,
p_maxLineSize NUMBER DEFAULT MAX_LINE_SIZE);
Prints the translations for the document in XLIFF format.
p_document – the fully qualified document name, which can represent either a document or package file.
p_language – the language to use for the translations
p_maxLineSize – the maximum size of line. This defaults to 255 which is the maximim allowable size of a line (the 255 limit is a limitation of the DBMS_OUPUT package).

PROCEDURE saveTranslations(
p_document VARCHAR2,
p_translations translationList,
p_commit BOOLEAN := TRUE);
Saves the specified translations for the specified document.This procedure will do the following:
1. Lock the document so as to prevent multiple users attempting to modify translations at the same time
2. Delete all of the translations for the specified document
3. Insert the new translations
4. Commit the data unless p_commit set to FALSE
p_document – the fully qualified document name
p_translations – the list of translations to insert
p_commit – if TRUE, the data is committed. Default is TRUE