Tuesday, 11 December 2012

10. Dependent LOV in OAF

Concept of dependent LOV is that, we select a value from one LOV field and based on that value, another LOV field will show filtered results.

Here, I have used two LOVs: one for PO Headers and another for PO Lines.
So first, user will select a PO Header Id from LOV1.
As we are making these dependent, so LOV2 will show only those PO Lines that are related to PO header Id selected.

1) Create a new OA page:
Right click on project (xxcus) --> New --> Web Tier --> OA Components --> select 'Page' item. Click OK. (This will open a popup window)
We are creating Dependent LOV page, so specify the details of page as below:
          Name: XxDemoDependentLovPG
          Package: xxcus.oracle.apps.fnd.dpndntlov.webui

2) Create two new view objects (VO):

First for PO Headers

Right click (dpndntlov) --> New View Object (This will open a wizard having 7 steps).

Step 1
        Package: xxcus.oracle.apps.fnd.dpndntlov.lov.server
        Name: XxPoHeaderLovVO
        Choose the radio button 'Read-only Access' (as we are not performing  any DML operation). Click Next.

Step 2
       Enter the below query in 'Query Statement':
    select PO_HEADER_ID from po_headers_all where rownum <50

As there is no need to generate VOImpl/VORowImpl, keep defaults for step3, 4, 5, 6 & 7 and click Finish. Save All.

Second for PO Lines

Right click --> New View Object

Step 1
         Package: xxcus.oracle.apps.fnd.dpndntlov.lov.server
         Name: XxPoLinesLovVO
         Choose the radio button 'Read-only Access'. Click Next.

Step 2
        Enter the below query in 'Query Statement':
        select PO_HEADER_ID, PO_LINE_ID from po_lines_all

Keep defaults for step3, 4, 5, 6 & 7 and click Finish. Save All.

3) Create a new Application Module (AM):
Step 1
        Package: xxcus.oracle.apps.fnd.dpndntlov.server
        Name: XxDemoDependentLovAM. Click Next.

Step 2
        Here we will add an instance of the VOs created in (2).
        Select XxPoHeaderLovVO & XxPoLinesLovVO from 'Available View Objects' and shuttle it to 'Data Model' using ">" button.

Keep defaults for all other steps (3, 4). Click Finish.

4) Creating the Page Layout & Setting its Properties:
Page will appear as below :

1) Create a new region under pageLayout of type defaultSingleColumn.
2) Create 2 items under defaultSingleColumn region (messageLovInput, messageLovInput).

Now change the properties for each field from property inspector as shown below:

         ID: pageLayoutRN
         AM Definition: xxcus.oracle.apps.fnd.dpndntlov.server.XxDemoDependentLovAM
         Window Title: Dependent LOV Demo Page
         Title: Dependent LOV Demo

         ID: singleColRN; Text: PO Lines Dependent on PO Header

messageLovInput (for PO Header): 
         ID: poHearderLov
         Prompt: PO Header ID

messageLovInput (for PO Lines):
         ID: poLineLov
         Prompt: PO Line ID
Creating LOV regions:

Expand poHearderLov LOV, right click on region1 and create a new region using wizard.

Step 1
select 'XxDemoDependentLovAM' from Application Module drop down and select 'XxPoHeaderLovVO1' in available view usages. Click Next.

Step 2
choose region style as 'table' from drop down. Click Next.

Step 3
Shuttle all the fields from available to selected attributes. Click Next.
Also modify the prompt to make it more user friendly (like PO Header ID).

This will create an item 'PoHeaderId' under 'XxPoHeaderLovVO1' table region.
Set search allowed property to TRUE for the item created.
            LOV region item: PoHeaderId
            Return item: poHearderLov
            Criteria item: poHearderLov

Similarly, create LOV region for poLineLov choosing XxDemoDependentLovAM as AM and XxPoLinesLovVO1 as view object.
Modify the prompt to PO Lines ID.

This will create 2 items 'PoHeaderId1' & 'PoLineId' under 'XxPoLinesLovVO1' table region.
Set search allowed property of 'PoLineId' to TRUE.

            LOV region item: PoLineId
            Return item: poLineLov
            Criteria item: poLineLov

Here, in lovMap3, we are making this dependent on first LOV by setting criteria item as value of first LOV field.
            LOV region item:PoHeaderId1
            Criteria item: poHearderLov

The declarative page structure in jDev will be similar to as shown below:

Now select a value (PO Header Id) from LOV1 such as 4 (as shown in above screenshot).

Perform a search on PO Line ID LOV. This will show only those lines whose header Id is 4.
Below is screenshot for the same:


  1. Sushant, keep up the good work, really impressive blog with sound explanation. Hopefully you will turn out to be a great techie.