Implement LOV(List of Values) and Dependent LOV in OAF

In this article we will see how to implement LOV and Dependent LOV in OAF.

Database Design

Table Script

CREATE TABLE XXRD_COUNTRIES
(
COUNTRY_ID NUMBER,
COUNTRY_NAME VARCHAR2(20 BYTE)
);

CREATE TABLE XXRD_STATES
(
COUNTRY_ID NUMBER,
STATE_ID NUMBER,
STATE_NAME VARCHAR2(20 BYTE)
);

Data Script

INSERT INTO XXRD_COUNTRIES VALUES (1,’US’);
INSERT INTO XXRD_COUNTRIES VALUES (2,’IND’);
INSERT INTO XXRD_COUNTRIES VALUES (3,’UK’);

INSERT INTO XXRD_STATES VALUES (1,100,’CA’);
INSERT INTO XXRD_STATES VALUES (1,101,’VA’);
INSERT INTO XXRD_STATES VALUES (1,102,’NJ’);

INSERT INTO XXRD_STATES VALUES (2,200,’KA’);
INSERT INTO XXRD_STATES VALUES (2,201,’WB’);
INSERT INTO XXRD_STATES VALUES (2,202,’DL’);

INSERT INTO XXRD_STATES VALUES (3,300,’LONDON’);
INSERT INTO XXRD_STATES VALUES (3,301,’NORWICH’);
INSERT INTO XXRD_STATES VALUES (3,302,’WESTHAMP’);

Ideally, LOVs are created for Master Data which can be set in Lookup or Value set in EBS. Here to make the project simple, I have created couple of tables one for storing country and another for state. Now let’s see how we implement LOV. We will create External regions for LOVs and include those in the main page.

At first we will have to View Objects for LOV and then followed by Application Module for attaching VOs of LOV and external list of values region.

We will maintain different directory structure for LOV,A�say

BC4J’s for LOVs will be placed under: xxrd.oracle.apps.ap.lov.server

Region of LOV will be placed under: xxrd.oracle.apps.ap.lov.webui

1. Enter the following:
   LOV VO Name: XXRDCountryLovVO
   Package:A�xxrd.oracle.apps.ap.lov.server
   VO Type: Read Only Access
2. Write the query.
3. Uncheck all Impl and RowImpl java class generation as not required.
4. Now Create AM for LOV. Enter the following:
   AM Name: XXRDLovAM
   Package:A�xxrd.oracle.apps.ap.lov.server
5. Shuffle XXRDCountryLovVO’s Instance XXRDCountryLovVO1 to enlist under XXRDLovAM.
6. Uncheck the option to generate XXRDLovAMImpl java class
7. Now create the external region. Navigation: Right Click on Project > OA Components > Region
8. Enter
   Region Name: XXRDCountryLovRN
   Package: xxrd.oracle.apps.ap.lov.webui Style: listOfValues
9. Attach AM created for LOV and set scope as Public.
   <<Attach image of scope as >>
10. Right Click on listOfValues Region to create table region.A�
11. Right Click on table to add an Item.
    ID: CountryName
    Item Style: messageStyledText
    Search Allowed:True
    View Instance: XXRDCountryLovVO1
    View Attribute: CountryName
    Prompt: Country Name
12. Similarly right click on table region to add CountryID of type formValue.
    ID: CountryIDFV
    Item Style: formValue
    View Instance: XXRDCountryLovVO1
    View Attribute: << attach image for form value>>
13. Next create a transient attribute in XXRDEmpVO. Enter
    Name: CountryID
    Type: String
    Updatable: Always
14. And here’s how it looks:
15. We have to create an item of type formValue as shown below:
    ID: CountryIDFormVal
    Item Style: formValue
    View Instance: XXRDEmpVO1
    View Attribute: CountryID
16. Now, in the original page, right click on CreateRN to create a messageLovInput item for Country.
    ID: Country
    Item Style: messageLovInput
    External LOV: /xxrd/oracle/apps/ap/lov/webui/XXRDCountryLovRN
    View Instance: XXRDEmpVO1
    View Attribute: Country
    Prompt: Country
17. Next we have to create couple of LOV Maps which will map the messageLovInput item with items of the listOfValues Region.
    First LovMap:
    Second LovMap:
18. Launch the page.
    
19. Verify data
20. We are going to implement Dependent LOV now. that does mean only the States corresponding to a Country being selected will be shown in the LOV. In short State LOV is dependent on Country LOV.
21. Create BC4Js for State LOV.
    Name: XXRDStateLovVO
    Package: xxrd.oracle.apps.ap.lov.server
22. Enter the query.
23. Uncheck all options for generating java classes.
24. Attach the State Lov VO Instance (XXRDStateLovVO1) to Lov AM.
25. Create Lov Region for State Lov.
    Name: XXRDStateLovRN
    Package: xxrd.oracle.apps.ap.lov.webui
    Style: listOfValues
26. Right Click to add table region.
27. In the table region, add three items viz. one messageStyledText and couple of formValue.
    ID: Statename
    Item Style: messageStyledText
    Search Allowed: True
    View Instance: XXRDStateLovVO1
    View Attribute: StateName
28. Add another item.
    ID: StateIDFV
    Item Style: formValue
    View Instance: XXRDStateLovVO1
    View Attribute: StateId
29. Add another item.
    ID: CountryIdFV
    Item Style: formValue
    View Instance: XXRDStateLovVO1
    View Attribute: CountryId
30. Finally it will look like:
31. Now in the main page, add messageLovInput Item, State.
    ID: State
    Item Style: messageLovInput
    External LOV: /xxrd/oracle/apps/ap/lov/webui/XXRDStateLovRN
    View Instance: XXRDEmpVO1
    View Attribute: State
    Prompt: A�State
32. Create LOV Mappings.<< state lov map >>
    
33. Launch the page.

Hope this helps.