Advanced Table Functionalities in OAF!
June 19, 2013 1 Comment
1] Add A Row:
To add an ‘Add a Row’ button, in the advanced table, go to
Advanced Table > Advanced Table Components > Footer > Table Footer > New > Add A Row.
In the Structure pane, select the addTableRow item that is newly created, as shown in the figure above, and use the Property Inspector to set its following properties (* Required):
ID* – specify an identifier that uniquely identifies the addTableRow item in the page.
Add Rows Label – specify text to override the “Add Another Row” default text that appears in the Add Another Row button.
Rows to Add – specify the number of rows to add each time a user chooses the Add Another Row button. The value for this property must not be greater than the value set for the Records Displayed property on the advanced table. The default is 1. Note that this property is valid only if the Insert Rows Automatically property is set to True.
Insert Rows Automatically – specify True to indicate rows are to be added automatically when the Add Another Row button is chosen. Specify False if you want to respond to the event in your controller, so that you can, for example, set the primary keys or set some default row attributes. Note: The Insert Rows Automatically property is equivalent to the ‘autoInsertion’ property that was set in your controller using the setAutoInsertion API for classic tables.
If you want to handle the Add a Row Event in the Controller, First set Insert Rows Automatically to False and then in controller, processFormRequest method, call your AM method when user click on the ‘Add a Row’ in your advanced table – ‘AdvTblRN’.
if ("addRows".equals(pageContext.getParameter("event")) &&
"AdvTblRN".equals(pageContext.getParameter("source")) )
{
System.out.println("The source is :"+pageContext.getParameter("source"));
String p_header_id = (String)pageContext.getTransactionValue("p_tx_header_id");
Serializable[] param = {p_header_id};
am.invokeMethod("createRow", param);
}
In AMImpl wrote the method:
public void createRow( String p_header_id)
{
OAViewObject pervo = getPriceLinesVO1(); // Advanced Table VO
if(!pervo.isPreparedForExecution()){
pervo.executeQuery();
}
pervo.setMaxFetchSize(0);
pervo.last(); // Go to the last Row of the VO
String line_number = pervo.getCurrentRow().getAttribute("LineNumber").toString();
String description = pervo.getCurrentRow().getAttribute("Description").toString();
Number line_num = new Number(Integer.parseInt(line_number) + 1);
pervo.next();
Row row = pervo.createRow(); // Create a New Row
// Insert the values in the row.
Number header_id = new Number(Integer.parseInt(p_header_id));
row.setAttribute("HeaderId",header_id);
row.setAttribute("LineId",getOADBTransaction().getSequenceValue("XX_PRICE_LINES_SEQ"));
row.setAttribute("Description",description);
row.setAttribute("LineNumber",line_num);
pervo.insertRow(row); // Insert the row in DB
row.setNewRowState(Row.STATUS_NEW);
getOADBTransaction().commit();
System.out.println("Commit Done");
}
2] Delete Multiple Rows:
Create a Transient Attribute of type String with any name (say SelectFlag) in the VO. Choose updatable always check button.
To add a Delete button, in the advanced table, go to
Advanced Table > Advanced Table Components > TableSelection > MultipleSelection > New > SubmitButton.
In the MultipleSelection, add the view attribute defined above.
In the controller, add logic as below:
if (pageContext.getParameter("DelBtn") != null)
{
System.out.println("In Delete Button");
System.out.println("The source is:"+pageContext.getParameter("source"));
OAViewObject linesVO=(OAViewObject)am.findViewObject("PriceLinesVO1");
Row[] row=linesVO.getFilteredRows("SelectFlag","Y");
System.out.println("Total Rows: "+linesVO.getRowCount());
System.out.println("Selected Rows: "+row.length);
Row row[] = linesVO.getAllRowsInRange();
for (int i=0;i<row.length;i++)
{
PriceLinesVORowImpl rowi = (PriceLinesVORowImpl) row[i];
if (rowi.getSelectFlag()!= null && rowi.getSelectFlag().equals("Y"))
{
rowi.remove();
}
}
am.getOADBTransaction().commit();
}
3] Update Multiple Rows:
Create a Transient Attribute of type String with any name (say SelectFlag) in the VO. Choose updatable always check button.
To add a Update button, in the advanced table, go to
Advanced Table > Advanced Table Components > TableSelection > MultipleSelection > New > SubmitButton.
In the MultipleSelection, add the view attribute defined above.
In the controller, add logic as below:
if (pageContext.getParameter("UpdateBtn") != null)
{
System.out.println("In Update Button");
Number user_id = new Number(am.getOADBTransaction().getUserId());
String updated_by = user_id.toString();
OAViewObject linesVO=(OAViewObject)am.findViewObject("PriceLinesVO1");
Row row[] = linesVO.getAllRowsInRange();
for (int i=0;i<row.length;i++)
{
PriceLinesVORowImpl rowi = (PriceLinesVORowImpl) row[i];
if (rowi.getSelectFlag()!= null && rowi.getSelectFlag().equals("Y"))
{
String Bucket_low = rowi.getAttribute("BucketLow").toString();
String Bucket_high = rowi.getAttribute("BucketHigh").toString();
Number Price = (Number) rowi.getAttribute("Price");
Serializable[] param = {rowi.getAttribute("LineId").toString(),rowi.getAttribute("BucketLow").toString(),rowi.getAttribute("BucketHigh").toString(),rowi.getAttribute("Price").toString(),updated_by};
am.invokeMethod("UpdateLinesRecord",param);
}
}
OAViewObject volns = (OAViewObject)am.findViewObject("PriceLinesVO1");
volns.clearCache();
volns.reset();
volns.executeQuery();
}
In AM method, you can call PLSQL Procedure to update the data.
public void UpdateLinesRecord(String LineId, String BucketLow, String BucketHigh, String Price, String updated_by)
{
try {
CallableStatement cs=this.getOADBTransaction().
getJdbcConnection().prepareCall("{call APPS.XXTEST_LINES_PKG.UPDATE_LINES(?,?,?,?,?)}");
cs.setString(1,LineId);
cs.setString(2,BucketLow);
cs.setString(3,BucketHigh);
cs.setString(4,Price);
cs.setString(5,updated_by);
cs.execute();
cs.close();
}
catch(SQLException e) {
System.out.println("the exception is"+e);
}
}
4] Delete or Update Single Row at one time.
If you want to add update and delete icons per rows do the followings:
- Create Columns in the advanced table say ‘Delete’ and ‘Update’.
- In the Delete Column, add an image item (deleteicon_enabled.gif). For all image names go to $OA_MEDIA folder.
- Set Action type to fire action.
- Give any name to Event (Say – ‘deleteLine’)
- In the parameter, pass the identifier of the row which user wants to delete. You can use SPEL for that.
Example>> p_header_id: ${oa.QotDtlVO1.HeaderId}
In the controller, handle the event – ‘deleteLine’.
if ("deleteLine".equals(pageContext.getParameter(EVENT_PARAM)))
{
//Handle delete logic here. You can call AM methods which eventually calls PLSQL Procedures to delete the data.
}
Similarly you can add image link for update also.
Do not forget to reset the VO after operation to reflect the new data.
Dibyajyoti Koch:A Blog on Oracle Application 