ADF BC: LOV For Referrence Attribute From Another Table

Master ------------------------------------------------------------------> Detail

For Example

Master Entity is DepartmentTest and Detail Entity is EmployeeTest

1.       Add two transient attributes to VO of type String one for Code with [MasterCode] Name and the other for Name with [MasterName] e.g. (DepartmentTestCode, DepartmentTestName….)

2.       Make sure in the details tab updatable is always

3.       Make View Accessor to this VO

4.       Use the first transient attribute [MasterCode] and make to its List of Values

·         In configuration tab

§  Make ListDataSource [your view Accessor]

§  View Attribute & List Attribute  [make view attribute (MasterCode,MasterName,Surrogate) and List Attributes (DetailCode, DetailName, Primary key ) respectively ]

For example 

View Attribute	List Attribute
DepartmentTestCode	EmployeeTestCode
DepartmentTestName	EmployeeTestName
DepartmentTestId (Surrogate)	EmployeeTestID (primary key)

·         In UI Hints tab 

§  Default List Type : input text with list of values

§  Selected : select [MasterCode] transient attribute

5.       In VO

·          Select [MasterName] transient attribute and make dependencies based on [MasterCode] transient attribute

·         Select [Surrogate key] and make dependencies based on [MasterCode] transient attribute

6.       In VO

·          Select [MasterName] transient attribute and go to details tab and  write sql query  to not retrieve the first record with null value

Syntax:

SELECT MasterTable.MasterName FROM TableName where MasterTable. primary key = DetailTableEntity.Surrogate Key

Example:

SELECT DepartmentTest.DepartmentTestName FROM HR.DEPARTMENT_TEST  where DepartmentTest. DepartmentTestId = EmployeeTestEntity. DepartmentTestId

·         Select [MasterCode] transient attribute and go to details tab and  write sql query  to not retrieve the first record with null value

Syntax:

SELECT MasterTable.MasterCode FROM TableName where MasterTable. Primary key = DetailTableEntity.Surrogate Key

Example:

SELECT DepartmentTest. DepartmentTestCode FROM HR.DEPARTMENT_TEST where DepartmentTest. DepartmentTestId = EmployeeTestEntity. DepartmentTestId

Or using Groovy Expression by two ways: [optional ]

§  MasterName Groovy write this code :

            oracle.jbo.Key key = new oracle.jbo.Key(DepartmentTestId);

            return DeptVoAcc1.findByKey(key,1)[0].getAttribute("DepartmentTestName");

§   MasterName Groovy follow this steps :

            a. adf.object.applicationModule.getMasterName(DepartmentTestId)

           

           b. Function Application Module

  

 public String getMasterName(Number dno) {

        if (dno == null)

            return null;

        else {

            ViewObject vo = findViewObject("Department1");

            try {

                Row row =

                    vo.findByKey(new Key(new Object[] { new oracle.jbo.domain.Number(dno) }),1)[0];

                if (row != null) {

                    Object name = row.getAttribute("DepartmentTestName");

                    if (name != null)

                        return name.toString();

                    else

                        return null;

                } else

                    return "Specified department does not exists";

            } catch (Exception e) {

                e.printStackTrace();

                return null;

            }

        }

    }

7.       In UI

·         Drag and drop [MasterCode] transient attribute with list of values  (ADF LOV Input)

·         Drag and drop [MasterName] transient attribute with input text without label and disable = true

·         Drag and drop [Surrogate key ] with input text and hidden or not dragged

·         Make auto submit of MasterCode = true

If you don't make dependencies in VO you can

·         Make partial dependencies on MasterName  depend on the id of MasterCode

·          Make partial dependencies on Surrogate key  depend on the id of MasterCode