OAF – Implementing Validation View object, Validation Application Module and Entity Expert

While we implement the business logic or performing validations in entity object, we might come across situations where in we need to execute SQL statements to retrieve some value from the database.

Sample Scenario:-

While validating Sales Order in the Sales Order entity object, XXSalesOrderEO, verify whether the customer exists in the Customer master or not.

SQL:

SELECT customer_id

          , customer_name

  FROM xx_customer_master

1. Create a validation view object, XXCustomerMasterVVO using the above SQL.
2. Create a validation application module, XXOrderEntryVAM and add the above VVO as a VO instance. Note: The approach to create VVO and VAM is same as that of creating a regular view object and application module.
3. Create Entity expert by simply creating a java class, XXSalesOrdEntityExpert that extends oracle.apps.fnd.framework.server.OAEntityExpert class.
4. Register the entity expert to the Entity object as below. Note that in case of standalone EO, register to that object directly. And in case of master-detail entities, register to the top-level entity.
1. Edit entity object in Jdeveloper and navigate to the Properties tab
2. Add the following two properties.
• Property 1 Name: ExpertClass
• Property 1 Value: xx.oracle.apps.so.schema.server.XXSalesOrdEntityExpert
• Property 2 Name: VAMDef
• Property 2 Value: xx.oracle.apps.so.schema.server. XXOrderEntryVAM
5. Create a method in the Entity expert class that executes the VVO 

public String customerExists(String custName) {
        String customerExists = "N";
        XXCustomerMasterVVOImpl custMasterVO = 
            (XXCustomerMasterVVOImpl)findValidationViewObject("XXCustomerMasterVVO1");
        custMasterVO.setWhereClause(null);
        custMasterVO.setWhereClauseParams(null);
        custMasterVO.setWhereClause("customer_name = '" + custName + "'");
        custMasterVO.executeQuery();

  if (custMasterVO.getRowCount() > 0)
   customerExists = "Y";
  return customerExists;
    }

      6. Consume the above Entity Expert’s method in the EntityImpl class, XXSalesOrderEOImpl  to perform the validation.

XXSalesOrdEntityExpert soExpert = (XXSalesOrdEntityExpert)getOADBTransaction().getExpert(XXSalesOrderEOImpl.getDefinitionObject())
 String   customerExists = 
                            soExpert.customerExists(custName);