While it is possible to add a custom LOV to the timecard without using shipped configurable BC4J objects - see section 3.4 - this results in required customization to delivered code objects, specifically LovAM.xml and TimecardAM.xml. That method of adding a custom LOV to the timecard is desirable if you already have custom BC4J objects that you want to reuse with the timecard - for example, perhaps there is an informational LOV that the business has included with the Purchasing product, and you wish to have that same LOV on the timecard. However, if this is a relatively simple LOV, or you are just replacing the seeded LOVs with an LOV containing additional columns, then using the configurable LOV and not customizing the application module definitions, *AM.xml, is the prefered strategy.
There are several steps involved:
1. Create the supporting database view 2. Add the LOV component to the timecard 3. Personalize the LOV region
Comparing these steps to the ones in section 3.4 it is clear it is much easier to use the configurable objects to add an LOV onto the timecard.
To illustrate the process, we consider a specific example of adding a location field, where the location is determine from the US Payroll city and state tables, as a list of values to the timecard. Now, suppose this is a Project accounting and Payroll timecard, with a specific element called: Field Work, which if chosen the user should pick a location within their home state, but if it is anything else, then no location should be selectable (even if the user opens the LOV).
Clearly from the above example, we must have access to the users alternate name definitions - in this case we want to restrict the list based on a specific hours type. In fact we have received many enhancement requests to provide an object that exposes the alternate name definition to the middle tier such that alias bind values can passed to supporting queries. This is enabled for the BC4J objects supporting this LOV, which means the alias definition id is exposed to the middle tier by the BC4J object. So, when writing the database view, and setting the bind variables in the timecard layout defintion, care must be taken to bind the right alternate name definition in the layout - remember you can specify up to 10 alternate names - and retrieve the right alternate definition in the view. In our example, consider a user with the alternate name preferences as specified in figure 4. In this case the user has two alternate names specified, one for the expenditure type list and one for the payroll element (Hours Type) list. From this set up, it is clear this is an Entry Level Processing, ELP, user. In our case we want to change the list based on the hours type chosen, which means we must be careful to expose the second of the two alternate names in the view, and pass the second alternate name as the bind variable. So in the view we reference ’TC W TCRD ALIASES|2|’ to return the second alternate name set as defined in the preferences, and we bind the variable "TIMECARD ALIAS 2" in the qualifier definition. This ensures we are properly specifying the alternate name (alias definition) in both places.
Figure 4: Alternate Name Definition
3.3.1 Create the supporting database view
The database view must expose the alias definition id in the select clause in order to ensure that the appropriate list of values is provided to the user - in this case the list depends on the hours type chosen, so the configurable LOV BC4J object includes a alias definition id as the bind variable, the query executed for the LOV in the middle tier is:
select lov1column1,
from hxc cui custom lov1 v where aliasdefinitionid = :1
This query has two important consequences:
1. The columns in the view must be aliased to lov1columnN
2. The view definition must include a column called aliasdefinitionid Thus, the view definition must take form:
create or replace view HXC CUI CUSTOM LOV1 V (lov1column1,
With the SQL statement forming the configurable part of the view. In our example case, the SQL statement has form:
select distinct
cy.city name||’, ’||st.state abbrev "lov1column1", st.state name "lov1column2",
to number(hxc preference evaluation.resource preferences(fnd global.employee id,’TC W TCRD ALIASES|2|’))
"aliasdefinitionid"
from pay us states st, pay us city names cy,
per assignments asg, hr locations loc, hxc alias values hav where st.state code <= 51
and st.state code = cy.state code
and asg.person id = fnd global.employee id and asg.location id = loc.location id and loc.region 2 = st.state abbrev and hav.alias definition id =
to number(hxc preference evaluation.resource preferences(fnd global.employee id,’TC W TCRD ALIASES|2|’)) and hav.alias value name = ’Field Work’
While it is somewhat out of the scope of this section to discuss the SQL statement, it does highlight some typical configuration options. The use of FND GLOBAL functions to return person context into the view is key, as there is no mechanism within the custom LOV BC4J objects to pass the person id as context. There are several functions available on the FND GLOBAL package, and all can be used to provide context to server-side SQL statements.
In the fourth column of the custom LOV, lov1column4 we have created our own key for this field - in this case a jurisdiction code, which is artificial here because it excludes the county component, but for demonstration purposes will suffice. This is the value that will actually be stored in the time store, and available to recipient applications.
Also note, the resource preference call, which is used to determine the alias definition id used within the LOV. Only two parameters are passed, the first the person id is self explanatory, but the second, the string: TC W TCRD ALIASES|2| requires some explanation.
In this case we are asking for the alias definition id of the second alternate name associated with the person as of SYSDATE - this is important because in our example case the layout used has two alternate name sets, one for expenditure type and one for hours type - and recall it is the hours type value that we want passed into the LOV to limit the users’ choices.
The location field in this case forms part of the Project Accounting descriptive flexfield structure, and as such we need not only to set the value of the location the user picks, but also the attribute category for that descriptive flexfield structure. In this case, it doesn’t matter what value of location is chosen by the user, they are all associated with the ’ST’ attribute category, i.e. since this is first deposited in the OTL Time Store, we use the special prefix PAEXPITDFF - to identify this attribute as such. The fixed value in lov1column9 is used as a return item from the LOV - see next section - to populate this attribute category properly. For more details on this style of configuration see section 3.13.
Lastly, we supply the alias value id in lov1column10 such that we can provide the alias value id chosen by the user in the hours type choice list as a criterion for this LOV, i.e. if the user does not choose the alias value Field Work, then no choices will be available, which was our functional requirement.
3.3.2 Add the LOV component to the timecard
The custom LOV is added to the timecard layouts to ensure it is rendered for the user. This means we add the following component to the layout definition:
BEGIN HXC LAYOUT COMPONENTS "ELP ChoiceListDff Proj-Pay Timecard Layout - Location"
OWNER = "ORACLE"
COMPONENT VALUE = "LOCATION"
SEQUENCE = "260"
COMPONENT DEFINITION = "LOV"
RENDER TYPE = "WEB"
PARENT COMPONENT = "ELP ChoiceListDff Proj-Pay Timecard Layout - Day Scope Building blocks for worker timecard matrix"
REGION CODE = "HXC CUI TIMECARD"
REGION CODE APP SHORT NAME = "HXC"
ATTRIBUTE CODE = "HXC TIMECARD LOCATION"
ATTRIBUTE CODE APP SHORT NAME = "HXC"
BEGIN HXC LAYOUT COMP QUALIFIERS "ELP ChoiceListDff Proj-Pay Timecard Layout - Location"
OWNER = "ORACLE"
QUALIFIER ATTRIBUTE CATEGORY = "LOV"
QUALIFIER ATTRIBUTE1 = "CustomLov1VO"
QUALIFIER ATTRIBUTE2 = "Y"
QUALIFIER ATTRIBUTE3 = "/oracle/apps/hxc/selfservice/configui/webui/CuiCustomLov1RN"
QUALIFIER ATTRIBUTE4 = "809"
QUALIFIER ATTRIBUTE5 = "12"
QUALIFIER ATTRIBUTE6 = "Lov1column1|LOC-DISPLAY|CRITERIA|N|Lov1column10|HOURSTYPE|PASSIVE CRITERIA|Y|
Lov1column4|LOC|RESULT|N|Lov1column1|LOC-DISPLAY|RESULT|N|Lov1column9|PADFFAC|RESULT|N"
QUALIFIER ATTRIBUTE7 = "PADFFAC|Lov1column9"
QUALIFIER ATTRIBUTE8 = "Lov1column1"
QUALIFIER ATTRIBUTE9 = "Lov1column4"
QUALIFIER ATTRIBUTE10 = "oracle.apps.hxc.selfservice.timecard.server.CustomLov1VO"
QUALIFIER ATTRIBUTE11 = "TIMECARD ALIAS 2"
QUALIFIER ATTRIBUTE14 = "Lov1column9|HOURSTYPE|Y#Lov1column1|LOC-DISPLAY|Y"
QUALIFIER ATTRIBUTE15 = "Lov1column9 = ::Lov1column9#
upper(Lov1column1) like upper(’%’||::Lov1column1||’%’)"
QUALIFIER ATTRIBUTE20 = "N"
QUALIFIER ATTRIBUTE21 = "Y"
QUALIFIER ATTRIBUTE22 = "R"
QUALIFIER ATTRIBUTE25 = "FLEX"
QUALIFIER ATTRIBUTE26 = "Dummy Paexpitdff Context"
QUALIFIER ATTRIBUTE27 = "Attribute1"
QUALIFIER ATTRIBUTE28 = "LOC"
END HXC LAYOUT COMP QUALIFIERS END HXC LAYOUT COMPONENTS
Consider the component definition above:
• The text ELP ChoiceListDff Proj-Pay Timecard Layout - Location corresponds to the component name.
• The component in this case is owned by ORACLE which identifies it as a seeded component. In a configuration file, this can be anything, but something other than Oracle should be used for custom layouts.
• In this case, the component value, of LOCATION isn’t used in the code anywhere, but represents a good label.
• The sequence corresponds to a unique number within the layout definition and tells the configurable UI code where to situate the field with respect to the other fields defined in the layout.
• The component definition, LOV, is self-explanatory - this is a list of values style field.
• In this case render type means that we should show this component when the user accesses the timecard via a web browser.
• The parent component is the reference back to the timecard day matrix - i.e. this field is a child of the main table entry table and thus should be shown inside that table.
• The next four data entries tell the code where to locate the label for the field, in this case it is the AK attribute called HXC TIMECARD LOCATION within the HXC CUI TIMECARD region - and we should add this AK attribute via the AK forms to ensure the label is shown on the timecard page.
• The next section of the component definition correspond to the component qualifiers - this is data which modifies the default component behavior, and is necessary for a List of Values component. The items in bold above must be set this way when using the configurable LOV objects. They define the BC4J object, and the corresponding MDS region definition to use with the list of values.
• QUALIFIER ATTRIBUTE2 is used to turn middle tier caching on an off for the SQL statement associated with the list of values.
When testing it is a very good idea to set this to N such that you do not need to bounce the apache server repeatedly while testing your configuration. Once the configuration is fixed, and as long as for the same set of bind variables the same results will be shown, as is the case here, then the cache should be enabled for performance purposes. If this were a dynamic list, with lots of changes occurring, then the cache should remain disabled.
• QUALIFIER ATTRIBUTE5 specifies the display size of the field, in this case, 12 characters. The locations in the examples are somewhat larger than this, but to save horizontal scrolling on the time entry page we reduce this value to a minimum.
• QUALIFIER ATTRIBUTE6 is discussed in much greater detail in later sections of this document, essentially this data controls the criteria and results for the list of values. The value shown above indicates that the hours type value is a required criterion and that any data in the location field itself should also be used to filter the results. It also indicates that the location field and location display field should be given the values from lov1column4 (our key value which will be stored) and lov1column1 (our display value), respectively. It also indicates that the fixed text in lov1column9 should be placed into the PADFFAC labeled field - which in this case is a hidden field storing the attribute category.
• QUALIFIER ATTRIBUTE7 indicates any additional result columns. This is used in conjunction with qualifier attribute6, and is used if the user simply types in a value to the location display value and hits Continue or Save without recourse to the LOV.
It ensures that when the update code executes it understands that the value for the attribute category field must be populated from the query corresponding to this component.
• QUALIFIER ATTRIBUTE8 and QUALIFIER ATTRIBUTE9 indicate the id (key) and display attributes, and these are set to the appropriate columns, as previously discussed.
• QUALIFIER ATTRIBUTE11 holds the bind values to pass to the query on execution - these are bind parameters to the query in addition to the LOV criteria. When using the configurable LOV objects qualifier attribute11 must correspond to the bind variable for the alternate name definition retrieved in the underlying view - i.e. in this case alternate name definition 2 (any number between 1 and 10) can be used.
• QUALIFIER ATTRIBUTE14 and QUALIFIER ATTRIBUTE15 indicate the where clause that should be used when the user enters values and then clicks on the LOV flashlight icon. In this case, the alias value id from the hours type field must equal the value in lov1column9, and the value entered as search reduction criteria should be like the value in the display column - thus as configured this is a case insensitive criteria which will match any partial value in the display column. I.e. if the user enters
’GTON sq’, i.e. will match a value ’Washington Square’ in the list of values.
The rest of the qualifier attributes are the usual set for all layout components indicating where the field should appear, inside or outside the days in the matrix, to the left or right of that matrix, and where the value should be stored - in this case attribute1 of the Project Accounting additional descriptive flexfield. Once that layout is uploaded, and the right hours type value is chosen in the hours type list - if the user opens the location LOV the LOV appears as per figure 5.
Obviously while it is good that the LOV works, the generic column names and system data which appears is confusing for the user.
3.3.3 Personalize the LOV region
Use of the standard OAF personalization resolves the problem of the generic column names, and additional information showing to the user. Care should be taken when creating the personalization to ensure it is done at the appropriate level - for example, if several different custom LOVs will be used on differing layouts for differing employee populations, obviously a personalization at site-level would be inappropriate. Using personalization we can hide the columns the user does not need to see (all but the display column), and rename that, such that when opened, the LOV now appears:
and thus a fully functional configurable LOV is added to the timecard with the minimum of customization - a SQL statement, and a configuration via a timecard layout file.
Figure 5: Custom LOV Showing Locations