Friday, February 1, 2013

Creating LOV in Oracle WebADI Spreadsheet


It's very helpful for users if we provide LOVs for some of the obvious columns in our custom Oracle WebADI Spreadsheets. In this topic, I am trying to explain on how to create LOV in Oracle WebADI spreadsheets.

My attempt to create LOVs from Oracle Self-Service pages(available in 12.1.3) was futile so I had to create LOV using API. Below API creates an LOV for Person ID field and it displays Employee Number and Name in the LOV to choose from and are return back to the spreadsheet as well.

BNE_INTEGRATOR_UTILS.CREATE_TABLE_LOV
  (P_APPLICATION_ID                 => <Application ID>,
   P_INTERFACE_CODE               => <Interface Code>,
   P_INTERFACE_COL_NAME    => 'P_PERSON_ID',
  --Interface Column Name from where the user double clicks to open the LOV and P_ID_COL is also passed to this spreadsheet column.
  P_ID_COL                                    => 'PERSON_ID',
  P_MEAN_COL                            => 'EMPLOYEE_NUMBER',
  P_DESC_COL                             => 'FULL_NAME',
  P_TABLE                                     => 'PER_PEOPLE_X',
  P_ADDL_W_C                            => NULL,
  P_WINDOW_CAPTION            => 'Select an employee', --LOV window caption
  P_WINDOW_WIDTH                => 600, --Window Width. Optional.
  P_WINDOW_HEIGHT               => 500, --Window Height. Optional
  P_TABLE_BLOCK_SIZE           => 10, --Number of records to be displayed in the LOV
  P_TABLE_SORT_ORDER         => 'ascending', --Order to be displayed in LOV
  P_USER_ID                                => <FND User ID>,
  --FND User ID of the person who is creating this LOV.
  P_TABLE_COLUMNS              => 'EMPLOYEE_NUMBER,FULL_NAME',
  --LOV fields to be displayed. Note person ID will not be displayed.
  P_TABLE_SELECT_COLUMNS => 'P_PERSON_ID', --EMPLOYEE_NUMBER(First value in P_TABLE_COLUMNS) is passed to this spreadsheet column and is displayed. PERSON_ID is passed to this same field as hidden value and is passed to the API when upload happens. If you want FULL_NAME etc also passed to the spereadsheet then mention that spreadsheet column(s) separated by commas 
  P_TABLE_COLUMN_ALIAS      => 'P_PERSON_ID',
  P_TABLE_HEADERS                   => 'Employee Number,Full Name',
  P_POPLIST_FLAG                       => 'N')

P_INTERFACE_COL_NAME
Interface Column Name where the LOV should appear. User double clicks this cell to open LOV.
P_TABLE
Table/View where the data comes to LOV. If you are planning to use SQL you can use but there is a limit of number of characters. So better create a view and use it if it's the case.
P_ID_COL
ID Column of the table.
P_MEAN_COL
Meaning Column of the table.
P_DESC_COL
Description Column of the table. If you want other fields to be displayed in the LOV add here by separating them with commas.
P_ADDL_W_C
Additional WHERE clause if any.
P_TABLE_COLUMNS
LOV fields to be displayed. Make sure before you mention the columns here, these should be included in any of P_ID_COL, P_MEAN_COL or P_DESC_COL.
P_TABLE_SELECT_COLUMNS
Mention spreadsheet column name(s). After selecting a value from LOV, the values mentioned in the parameter P_TABLE_COLUMNS will be passed to these columns. If it has only one column mentioned then the first value from P_TABLE_COLUMNS will be stored in the column.
P_TABLE_COLUMN_ALIAS
Has to explore this however setting it same as P_TABLE_SELECT_COLUMNS is working for me.
P_TABLE_HEADERS
Column Header name in LOV. Make sure to follow the same sequence as P_TABLE_COLUMNS. If you don't pass any value to this field then it uses the column names mentioned in P_TABLE_COLUMNS.
P_POPLIST_FLAG
N -> For LOV , Y -> For PopList

The LOV shows like below
Employee Number LOV
Please let me know if I miss any points. I am happy to add here if it's relevant.

Regards,
Peddi.