Q60: What is the main purpose of lookups in Oracle Apps?
Q61: What is the differences between system, extensible, and userlookup types?
Q62: How can a lookup type be directly attached to a flexfield segment to do validations?
Q63: Please give a couple of examples and use cases where we use lookups.
——————————————————————————–
Q60: What is the main purpose of lookups in Oracle Apps?
The main purpose of a lookup is to keep programs flexible and easier to configure. Lookups are an approach of creating a configurable “list of values” in E-Business Suite.One of the simplest examples of a lookup type is gender. A “gender lookup” will have definitions as shown next:
Code Meaning
M Male
F Female
U Unknown
Let us assume that there is a table for employees named PER_ALL_PEOPLE_F and it has the following columns:
FIRST_NAME
LAST_NAME
DATE_OF_BIRTH
GENDER
The screen that displays an employee’s gender will display a value of Male, Female, or Unknown. However, the database column PER_ALL_PEOPLE_F.GENDER will store a value of M, F, or U. Hence, the screen displays the meaning, whereas the database columns reference the lookup via a lookup code.
If in the future your organization wants the users to see “Undisclosed” instead of “Unknown,” you will have to make a change to just one record in the lookups table via the lookup screen. By doing so, you will avoid having to update thousands of records in PER_ALL_PEOPLE_F. Your new lookup will look like the following:
Code Meaning
M Male
F Female
U Undisclosed
Here lies the power of the lookups; you do not need to modify thousands of records in a transactional table. A simple change via the lookup screen will suffice. Using the lookup screen, you can either create new custom lookups or modify existing lookups.
Q61: What is the differences between system, extensible, and userlookup types?
The following table shows the differences between system, extensible, and user lookup types:
Access Level Change Text of Meaning Add New Lookup Codes Modify Enabled Flag
User Yes Yes Yes
Extensible Yes Yes No
System Yes No No
Briefly to say, Some preseeded lookups given by Oracle cannot be modified. For example,Oracle has a lookup type called a termination type as shown in above figure. Oracle has some rules defined within the Payroll Engine program that read the value of a termination type code for employees before calculating their final salaries. Obviously,such lookup codes, if changed, can affect the logic within the Payroll Engine. For this reason, Oracle flags some lookups as system lookups, and the Lookup Entry screen will not let you modify those lookup codes.
Q62: How can a lookup type be directly attached to a flexfield segment to do validations?
To do this, create a value set of Table type that validates against FND_LOOKUP_VALUES using the desired lookup. In this manner, you can validate flexfield segments against lookup values.
The point here is that a lookup type cannot be directly attached to a flexfield segment to do validations, however, it is indirectly possible to do so.
Q63: Please give a couple of examples and use cases where we use lookups.
Example I: Interfacing Data to Third-Party Systems
Sometimes there is a need to create a database view that becomes the source of information for data transfer to other third-party systems. You might have to put filtration rules in place; for example, for HRMS Extract, people with person types Employee and Applicant should be extracted. To achieve this, instead of hard-coding Employee and Applicant in the database view, you can do the following:
1. Create a lookup type XX_EXTRACT_EMP_TYPES.
2. Add lookup codes Employee and Applicant to this lookup type.
3. In the database view, join the HR Person Types from HR Tables with FND_LOOKUP_VALUES for this lookup type.
Example II:Development of Custom Screens
In custom screen development, there is often a need to provide a list of values (LOV) on a field, as per business requirements. If the LOV data is not available in standard Oracle tables, then it is recommended you maintain a lookup that drives such a lookup value.
Q61: What is the differences between system, extensible, and userlookup types?
Q62: How can a lookup type be directly attached to a flexfield segment to do validations?
Q63: Please give a couple of examples and use cases where we use lookups.
——————————————————————————–
Q60: What is the main purpose of lookups in Oracle Apps?
The main purpose of a lookup is to keep programs flexible and easier to configure. Lookups are an approach of creating a configurable “list of values” in E-Business Suite.One of the simplest examples of a lookup type is gender. A “gender lookup” will have definitions as shown next:
Code Meaning
M Male
F Female
U Unknown
Let us assume that there is a table for employees named PER_ALL_PEOPLE_F and it has the following columns:
FIRST_NAME
LAST_NAME
DATE_OF_BIRTH
GENDER
The screen that displays an employee’s gender will display a value of Male, Female, or Unknown. However, the database column PER_ALL_PEOPLE_F.GENDER will store a value of M, F, or U. Hence, the screen displays the meaning, whereas the database columns reference the lookup via a lookup code.
If in the future your organization wants the users to see “Undisclosed” instead of “Unknown,” you will have to make a change to just one record in the lookups table via the lookup screen. By doing so, you will avoid having to update thousands of records in PER_ALL_PEOPLE_F. Your new lookup will look like the following:
Code Meaning
M Male
F Female
U Undisclosed
Here lies the power of the lookups; you do not need to modify thousands of records in a transactional table. A simple change via the lookup screen will suffice. Using the lookup screen, you can either create new custom lookups or modify existing lookups.
Q61: What is the differences between system, extensible, and userlookup types?
The following table shows the differences between system, extensible, and user lookup types:
Access Level Change Text of Meaning Add New Lookup Codes Modify Enabled Flag
User Yes Yes Yes
Extensible Yes Yes No
System Yes No No
Briefly to say, Some preseeded lookups given by Oracle cannot be modified. For example,Oracle has a lookup type called a termination type as shown in above figure. Oracle has some rules defined within the Payroll Engine program that read the value of a termination type code for employees before calculating their final salaries. Obviously,such lookup codes, if changed, can affect the logic within the Payroll Engine. For this reason, Oracle flags some lookups as system lookups, and the Lookup Entry screen will not let you modify those lookup codes.
Q62: How can a lookup type be directly attached to a flexfield segment to do validations?
To do this, create a value set of Table type that validates against FND_LOOKUP_VALUES using the desired lookup. In this manner, you can validate flexfield segments against lookup values.
The point here is that a lookup type cannot be directly attached to a flexfield segment to do validations, however, it is indirectly possible to do so.
Q63: Please give a couple of examples and use cases where we use lookups.
Example I: Interfacing Data to Third-Party Systems
Sometimes there is a need to create a database view that becomes the source of information for data transfer to other third-party systems. You might have to put filtration rules in place; for example, for HRMS Extract, people with person types Employee and Applicant should be extracted. To achieve this, instead of hard-coding Employee and Applicant in the database view, you can do the following:
1. Create a lookup type XX_EXTRACT_EMP_TYPES.
2. Add lookup codes Employee and Applicant to this lookup type.
3. In the database view, join the HR Person Types from HR Tables with FND_LOOKUP_VALUES for this lookup type.
Example II:Development of Custom Screens
In custom screen development, there is often a need to provide a list of values (LOV) on a field, as per business requirements. If the LOV data is not available in standard Oracle tables, then it is recommended you maintain a lookup that drives such a lookup value.
0 comments