OAF Table Region DML Operations
Hello,
In this tutorial, OAF Table Region DML operations will be implemented.
We will cover the following topics:
- Fetching rows from database table and display them on table region
- Adding single rows into table region
- Removing multiple rows from table region.
Oracle WHO Columns
In Oracle E-Business Suite Database, most tables have file who columns. We are also using these columns in our custom applications. These columns are:
- CREATED_BY: The NUMBER column that shows which FND_USER-USER_ID created the row
- CREATION_DATE: The DATE column that shows when row is created.
- LAST_UPDATE_DATE: The DATE column that shows when row is updated.
- LAST_UPDATED_BY: The NUMBER column that shows which FND_USER-USER_ID updated the row
- LAST_UPDATE_LOGIN: The NUMBER column that shows which USERENV (‘SESSIONID’) updated the row
If WHO columns are not included in database table, correspending entitiy object must have related set methods overwritten. It would be better if who columns would be created in database tables.
Before starting an OAF application, creating database model would be beneficial. In addition, make sure that primay and foreign keys are created with database tables. Foreign keys will allow JDeveloper to generate association and view link by itself.
Run the following script in order to create database table.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 |
create table hr.xx_oaf_anil_personel ( person_id number, first_name varchar2(120), last_name varchar2(120), salary number, date_of_birth date, hire_date date, created_by number, creation_date date, last_update_date date, last_updated_by number, last_update_login number ); alter table hr.xx_oaf_anil_personel add constraint xx_oaf_anil_personel_pk primary key (person_id); create public synonym xx_oaf_anil_personel for hr.xx_oaf_anil_personel; create sequence hr.xx_oaf_anil_personel_s start with 1 increment by 1 minvalue 1 nocache nocycle noorder; |
1.Open JDeveloper. Right click to your project and select “New”.
2. Select “ADF Business Components from Tables” from “ADF Business Componenets” sub-menu under Business Tier and click Ok button.
3. Click Next button in order to move on next step.
4. In first step, Entity Object will be created. Define your package properly. Select your Schema from drop-down menu and click to “Query” button. Find your database table under “Available” section and click to the left-to-right arrow for moving it into “Selected” section. Click to the “Next” button.
5. In second step, updatable view object will be created. Select Entity Object from “Available” section and move it to selected. Change the name and package properly.
6. In third step, read-only view objects will be defined. These read-only view objects may be list of value queries. In this tutorial, we will not use and lov or poplist. Therefore just click to Next button.
7. In fourth step, Application Module will be created. Select Application Module chechbox. Define your package and Name properly.
8. Just click to the Next button in fifth step.
9. In the last step, click to the Finish button. JDeveloper will start generating BC4J objects and packages.
10. After business componenets will be created, the strcuture for packages look like following:
11. Right click to project and select New. Under Web Tier menu select OA Componenets sub-menu. Select Page object and click Ok button.
12. Give a proper name and package to the page.
13. Give proper ID to pageLayoutRegion. Give proper titles to “Window Title” and “Title” attributes ınde pageLayoutRegion. Set “AM Definition” by selecting your application module.
14. Run the page and check everyting is Ok.
15. Set a controller object for pageLayoutRegion by right click to PageLayoutRN and select “Set New Controller”.
16. Give proper names to class name and package name.
17. Create a header region under pageLayoutRegion. Right click to PageLayoutRN and select “New Region”. Set “Region Type” as “Header”. Give proper ID and text values.
18. Create an advanced table under header region.
| Region Style | ID | View Instance | Width |
|---|---|---|---|
| advancedTable | DmlAdvanceTableRN | XxOafAnilPersonelEOVO1 | 100% |
19. Add 5 columns under advanced table by right clicking to DmlAdvanceTableRN and select New -> Column.
20. Create 5 column headers under 5 columns.
21. At this step, the page structure should be look like this:
22. Give proper ID and prompt values to columns and column headers.
column1
ID: FirstNameCol
sortableHeader1
ID: FirstNameColHdr
Prompt: First Name
column2 ID: LastNameCol
sortableHeader2
ID: LastNameColHdr
Prompt: Last Name
column3
ID: SalaryCol
sortableHeader3
ID: SalaryColHdr
Prompt: Salary
column4
ID: DateOfBirthCol
sortableHeader4
ID: DateOfBirthColHdr
Prompt: Date of Birth
column5
ID: HireDateCol
sortableHeader5
ID: HireDateColHdr
Prompt: Hire Date
23. At this step, the page structure should be look like this:
24. Create messageTextInput items under columns.
FirstNameCol->New->Item
| ID | ITEM STYLE | DATA TYPE | MAXIMUM LENGTH | VIEW ATTRIBUTE |
|---|---|---|---|---|
| FirstName | messageTextInput | VARCHAR2 | 120 | FirstName |
LastNameCol->New->Item
| ID | ITEM STYLE | DATA TYPE | MAXIMUM LENGTH | VIEW ATTRIBUTE |
|---|---|---|---|---|
| LastName | messageTextInput | VARCHAR2 | 120 | LastName |
SalaryCol->New->Item
| ID | ITEM STYLE | DATA TYPE | MAXIMUM LENGTH | VIEW ATTRIBUTE |
|---|---|---|---|---|
| Salary | messageTextInput | NUMBER | Salary |
DateOfBirthCol->New->Item
| ID | ITEM STYLE | DATA TYPE | MAXIMUM LENGTH | VIEW ATTRIBUTE |
|---|---|---|---|---|
| DateOfBirth | messageTextInput | DATE | DateOfBirth |
HireDateCol->New->Item
| ID | ITEM STYLE | DATA TYPE | MAXIMUM LENGTH | VIEW ATTRIBUTE |
|---|---|---|---|---|
| HireDate | messageTextInput | DATE | HireDate |
25. At this step, the page structure should be look like this:
26.In order to display table data on page load, SQL query in View Object must be executed. Open the controller java file which is created in step 16. Controller objects have 2 methods. These are:
- processRequest: This method runs before page renders.
- processFormRequest: This methods runs when an action is happened on the page.
In application module java file, we will write a method which will execute the sql query in view object which is attached to table region in the page. However, before writing application module method, we need to add an initQuery method into view object implementation file. Open “<YourVoName>Impl.java file.
Add the following method into view object impl file.
|
1 2 3 4 5 |
public void initQuery() { executeQuery(); } |
Open application module impl java file.
Add the following method into application module implementation java file. This method will check the vo is null or not and then call the initQuery method in view object implementation file.
|
1 2 3 4 5 6 7 8 9 |
public void executeOafAnilPersonelVO() { XxOafAnilPersonelEOVOImpl vo = getXxOafAnilPersonelEOVO1(); if (vo == null) { throw new OAException("Error: XxOafAnilPersonelEOVOImpl is null"); } vo.initQuery(); } |
Open controller object again and add the following code in processRequest method.
|
1 2 3 4 5 6 7 8 9 10 |
public void processRequest(OAPageContext pageContext, OAWebBean webBean) { super.processRequest(pageContext, webBean); //get application module object from the context by sending webBean. webBean object represents pageLayoutRegion because controller object is attached to it. OAApplicationModule am = pageContext.getApplicationModule(webBean); //call application module method am.invokeMethod("executeOafAnilPersonelVO"); } |
27. Run the page and test it. Table will be empty since no rows are added into database table.
Add Row
1. Create tableActions into the advanced table region. Right click to DmlAdvanceTableRN and select New -> tableActions. Change flowLayout region ID to a proper name.
2. Right click to TableActionsRN and select New->Item. Change Item properties with following:
| ID | ITEM STLE | PROMPT | ACTION TYPE | EVENT |
|---|---|---|---|---|
| AddNewRow | button | Yeni Satır Ekle | firePartialAction | addNewRow |
3. Double click to Entity Object and open the wizard. Move to the “Java” section and select “Create Method” checkbox. Click OK button and the wizard will be closed. This will overwirte create method in entity object java file. This method will be called whenever a row is created. Initial attribute values can be set in this method. In this tutorial, primary key of table will be set with a number value which will be fetched from a database sequence object.
4. Open entity object implementation java file. Change the create method with the following.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
/**Add attribute defaulting logic in this method. */ public void create(AttributeList attributeList) { super.create(attributeList); //get transaction OADBTransactionImpl transaction = (OADBTransactionImpl)getOADBTransaction(); //get next value from sequence in transaction Number PersonId = transaction.getSequenceValue("HR.XX_OAF_ANIL_PERSONEL_S"); //set it to primary key setPersonId(PersonId); } |
5. Open application module java file and create addNewRow method.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
public void addNewRow() { //Find view object from application module OAViewObject vo = (OAViewObject)getXxOafAnilPersonelEOVO1(); //unless vo query is executed or if no row has inserted before, setMaxFetchSize to 0 if (vo.getFetchedRowCount() == 0) { vo.setMaxFetchSize(0); } //create a row object from view object. At this point execution will jump to entity object create method Row row = vo.createRow(); //insert row into view object vo.insertRow(row); //in order to keep row state, set it as initialized. row.setNewRowState(Row.STATUS_INITIALIZED); } |
6. Open controller object java file and write following code in processFormRequest method.
|
1 2 3 4 5 6 7 8 9 10 11 12 |
public void processFormRequest(OAPageContext pageContext, OAWebBean webBean) { super.processFormRequest(pageContext, webBean); OAApplicationModule am = pageContext.getApplicationModule(webBean); //catch the button action which will be created in table's tableActions section. Pay attention to ID value of button is used for catching the action. if ("addNewRow".equals(pageContext.getParameter(OAWebBeanConstants.EVENT_PARAM))) { am.invokeMethod("addNewRow"); } } |
7. Up to this point, all these operations take action on front end. In OAF, transaction management is handled by application module. One application module represents one transaction. When a transaction is ended with commit or rollback, all of the updatable view objects (view object which is derived from entity object) pass transaction data to the entity object and prepared statements are executed by OAF in order to insert data into table and commit or rollback the transaction. In order to end transaction, we need a submit button in the page.
8. Right click to pageLayoutRegion and select New->Region. Change the region properties with the followings:
| ID | Region Style |
|---|---|
| PageButtonBarRN | pageButtonBar |
Right click to pageButtonBar and select New->Item. Change the Item properties with the followings:
| ID | Item Style | Prompt |
|---|---|---|
| Apply | submitButton | Kaydet |
9. Create an apply method in application module java file. This method will get the transaction and commit it.
|
1 2 3 4 5 |
public void apply() { getOADBTransaction().commit(); } |
10. Open controller object java file and change processFormRequest method with the following.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
public void processFormRequest(OAPageContext pageContext, OAWebBean webBean) { super.processFormRequest(pageContext, webBean); //call application module object from pageContext with pageLayoutBean OAApplicationModule am = pageContext.getApplicationModule(webBean); //Catch the submit button action. Pay attention that this is different from calling actions. This will post data. if (pageContext.getParameter("Apply") != null) { am.invokeMethod("apply"); } else if ("addNewRow".equals(pageContext.getParameter(OAWebBeanConstants.EVENT_PARAM))) { am.invokeMethod("addNewRow"); } } |
11. Run the page and test the results.
|
1 2 3 |
select * from xx_oaf_anil_personel; |
Results: 
Multiple Row Delete
1. In order to mark the rows for deleting, add a transient attribute into view object. Double click to the view object and open the wizard.
2. Move to the “Attributes” section and click to the “New” button.
3. Name the attribute as “SelectedRow”. Set type as String and make sure that updatable section is set as “Always”.
4. Click to Ok and close the windows. Then click OK button and wizard will be closed. At this point transient attribute should be created in view object.
5. Click to the page and right click to the advancedTable(DmlAdvanceTableRN ) region. Select new->multipleSelection.
6. Select multipleSelection1 which is generated by Jdeveloper under the advanced table region. Select View Attribute value as “SelectedRow”.
7. Open view object edit wizard. Choose Java section from left menu and check “View Row Class “<YourVoName>RowImpl” – Generate Java File”. Click OK button.
8. Open application module java file and add the following method.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 |
public void removeSelectedRows() { //Find view object in application module OAViewObject vo = (OAViewObject)getXxOafAnilPersonelEOVO1(); //create a row list java.util.List<Row> removeList = new ArrayList<Row>(); //get the row count from view object int rowCount = vo.getFetchedRowCount(); if (rowCount > 0) { //create a row object from voRowImpl class. XxOafAnilPersonelEOVORowImpl row = null; //Create a RowSetIterator from view object. Set it start as 0 and range as rowCount RowSetIterator iter = vo.createRowSetIterator("Iter"); iter.setRangeStart(0); iter.setRangeSize(rowCount); for (int i = 0; i < rowCount; i++) { row = (XxOafAnilPersonelEOVORowImpl)iter.getRowAtRangeIndex(i); if (row != null) { //check whether row is selected or not. If selected, add it to the list. if ("Y".equals(row.getSelectedRow())) { removeList.add(row); } } } //always close row set iterator iter.closeRowSetIterator(); //remove rows one by one for (Row deleteRow : removeList) { deleteRow.remove(); } } } |
Not: Please pay attention to iterator usage. You can move between view object rows with vo.next method. However it will change the current row in view object and that is not a thing we always want.
9.Open the page structure and add a button under TableActionsRN. Set the following values:
| ID | ITEM STLE | PROMPT | ACTION TYPE | EVENT |
|---|---|---|---|---|
| DeleteSelectedRows | button | Seçili Satırları Sil | firePartialAction | deleteSelectedRows |
10. Open controller object java file and change processFormRequest method with the following code.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
public void processFormRequest(OAPageContext pageContext, OAWebBean webBean) { super.processFormRequest(pageContext, webBean); OAApplicationModule am = pageContext.getApplicationModule(webBean); if (pageContext.getParameter("Apply") != null) { am.invokeMethod("apply"); } else if ("addNewRow".equals(pageContext.getParameter(OAWebBeanConstants.EVENT_PARAM))) { am.invokeMethod("addNewRow"); } else if ("deleteSelectedRows".equals(pageContext.getParameter(OAWebBeanConstants.EVENT_PARAM))) { am.invokeMethod("removeSelectedRows"); } } |
11. Final structure of page:
12. Run the page and test the results.
This tutorial covers many basic and important topics in OAF. I hope it will prove some useful information for EBS developers.
Regards,
Anil
Thank you so much sir. It is really usefull
Hi, do you have a similar example to in using Multi-Selector from search then using those values to insert to another table?
Thank you
java.util.List removeList = new ArrayList();
Showing Erreo
//remove rows one by one
for (Row deleteRow: removeList) {
deleteRow.remove();
}
Error:
Error(93,31): ‘;’ expected
Error(93,22): malformed expression
java.util.List
Please Provide imports also with the code otherwise no use of code….
Great Post !!!!! Thanks !!!