Create LOV in OAF

1. Objective
   The Objective of this session is to add a customer LOV to the order creation page.
2. Development

1. Create Customer Table

• Login as xxtrng;
• Create Table using the below script

CREATE TABLE XXTRNG.XXAP_CUSTOMERS

( CUSTOMER_ID NUMBER PRIMARY KEY

, CUSTOMER_NAME VARCHAR2(100)

, ADDRESS_LINE1 VARCHAR2(240)

, ADDRESS_LINE2 VARCHAR2(240)

, COUNTY VARCHAR2(240)

, STATE VARCHAR2(240)

, ZIP_CODE VARCHAR2(50)

, COUNTRY VARCHAR2(50)

, CREATED_BY NUMBER

, CREATION_DATE DATE

, LAST_UPDATED_BY NUMBER

, LAST_UPDATE_DATE DATE

, LAST_UPDATE_LOGIN NUMBER

);

• Grant permissions on table to APPS

grant all on XXAP_CUSTOMERS to apps;

• Create APPS synonym for table

CREATE SYNONYM APPS.XXAP_CUSTOMERS FOR XXTRNG.XXAP_CUSTOMERS;

• Create sequence for table

create sequence XXAP_CUSTOMERS_S START WITH 600001;

• Create APPS synonym for table

CREATE SYNONYM APPS.XXAP_CUSTOMERS_S FOR XXTRNG.XXAP_CUSTOMERS_S;

1. Create Customer Entity Object

• Create XxapCustomersEO under the following package:

oracle.apps.inv.customer.schema.server

1. Create Customer View Object

• Create XxapCustomersVO under the following package:

oracle.apps.inv.customer.server

1. Add method to the Application Module for initializing View Object

• Add the following code to the application module “XxapOrderAM”:

public void initXxapCustomerVO()

{

// Get the Customer VO

XxapCustomersVOImpl customerVO = getXxapCustomersVO1();

//Create a new Customer VO Row

XxapCustomersVORowImpl row = (XxapCustomersVORowImpl)customerVO.createRow();

//Generate customer_id from sequence XXAP_CUSTOMERS_S

Number customerId = getOADBTransaction().getSequenceValue(“XXAP_CUSTOMERS_S”);

//Set the above generated customerId to customer VO Row.

row.setCustomerId(customerId);

//The customer Row is dirtied out after setting the customer_id, make it fresh by calling the following method.

row.setNewRowState(row.STATUS_INITIALIZED);

//Insert the row back into the VO, so as to use it on the page.

customerVO.insertRow(row);

}

1.     Create Customer Page

• Create page XxapCustomerPG under the following package: oracle.apps.inv.customer.webui

1. Add Controller to the Customer Page

• Create a new controller XxapCustomerPageCO and add it to this page.

1.     Create Read Only View Object

• Create a read only View Object “XxapCustomersLovVO” in the following package oracle.apps.inv.appsphor.order.lov.server using following query:

SELECT xc.customer_id

, xc.customer_name

, xc.address_line1

, xc.zip_code

FROM xxap_customers xc

1. Add LOV View Object to the Application Module

• Add this XxapCustomersLovVO to XxapOrderAM present under oracle.apps.inv.order.server
• This is done by opening the AM. Select Data Model in left navigator, and then move the VO from left to right.

1. Edit Order Header PG

• Modify XxapOrderHeaderPG to have another field called CustomerZip of type MessageTextInput to hold zip code.

• Modify XxapOrderHeaderPG page to change the Customer field to an Lov. Select Customer field and change the item type to MessageLovInput as shown in below figure:

• The LOV item by default contains two regions. One region of type list Of Values.

• Another region of type LOV Mappings:

• Modify the list Of Values region to add another region using wizard.

• Select CustomersLovVO to create the table region.

• On step 3 select all the columns and move them to right and finish the process. For CustomerName and ZipCode fields set the search allowed property to true and sort allowed properties to yes.

• Add lovMapping for customer name. Select CustomerName in the LOV Region Item, Customer in the Return Item and Customer for Criteria Item.

• Add another lovMapping for ZipCode. Select ZipCode in LOV Region Item and CustomerZip in the Return Item.