Use the Flexible Address Formats feature to enter and validate addresses in country-specific formats. See: Flexible Addresses.
For each address style, you can define country-specific validation that controls the information you enter in certain address elements. For example, you may want to restrict the entry of cities for French addresses to a predefined list, or you may want to restrict a postal code to a certain range of numbers.
Validation can take the form of a simple list of values or a complex parent-child relationship between address elements. This section discusses how to implement list of value validation on your flexible address formats. Refer to the Oracle E-Business Suite Flexfields Guide for information on complex validation.
This section uses the Southern European address style for Spanish and French address entry to demonstrate how to set up a list of values on an address style. Assume that within this flexfield you want to have a list of values on the City field that will list either Spanish or French cities, depending upon the country of the address you are entering. For more information about the Southern European and other address styles, see: Address Style Mappings.
Prerequisites
Decide which values you want to display in your list of values.
This example uses the following locations: France, Spain, Bordeaux, Barcelona, Lyon, Balboa, Paris, and Madrid.
Navigate to the Countries and Territories window.
Query the countries for which you wish to validate address information.
Note the two character short code which identifies each of the countries you want to validate against in the list of values.
The country codes for Spain and France are 'ES' and 'FR', respectively.
Attention: It is very important to identify the correct country code. Otherwise, the list of values will return no data for these countries and you will be unable to enter any information in the address element to which the value is assigned.
Create a valid list of values for each address component you wish to validate. See: Creating a valid list of values for address components.
Define a value set listing your location values. This value set will be attached to the appropriate segment of the address style.
Assign the value set to the appropriate address element in each of the address descriptive flexfields. See: Assigning a value set to an address element.
Warning: The address styles should be identical across all address flexfields. If they are not the same, you risk creating addresses that are incompatible with different windows.
Using the Application Developer responsibility, navigate to the Special Lookups window.
Enter your new lookup Type.
The lookup should belong to the Application Object Library application and should have an Access Level of System.
You should name all of your flexible address lookups consistently to avoid confusion. For example, FAF_<column_name>, where <column_name> is the name of the address column to which you are assigning the list of values choice. In this example you would call your lookup type FAF_CITY.
Attention: If more than one country uses a particular address style, you can only have one lookup type for each address element. For example, you can only have one City lookup type for all countries using the Northern European address style. It is advisable to adopt this policy for all lookup types, regardless of the number of countries using them, because they will be easier to maintain.
Enter a list of valid locations. You only need to enter three pieces of information: Language, Code, and Meaning.
The Code is a unique identifier which will enable Oracle Receivables to identify which city to select when it displays the list of values. Therefore, the Code column must be called <country_code><n>, where <country_code> is the two character short code for the country of the address information you are entering, and <n> is a sequential number which ensures the Code is unique.
Enter the actual information you wish to retrieve in the Meaning column.
In this example, the code and meaning values would look as follows in this table:
| Code | Meaning |
|---|---|
| ES1 | Barcelona |
| ES2 | Balboa |
| ES3 | Madrid |
| FR1 | Bordeaux |
| FR2 | Lyon |
| FR3 | Paris |
Using the Application Developer responsibility, navigate to the Value Sets window.
Enter the name of your value set.
For example, FAF_<address_column>, where <address_column> is the name of the column that you will attach to the value set. In this example you would call your value set FAF_CITY.
Ensure that the Format Type and Maximum Size correspond to the type and size of the column you will be populating with this value set.
Set the Validation Type to Table.
Choose Edit Information.
Enter Application Object Library as the Table Application.
Enter FND_LOOKUP_VALUES as the Table Name.
Enter MEANING as the Value Column.
Enter the following statement in the WHERE / ORDER BY region:
WHERE LOOKUP_TYPE = '<lookup_name>' AND SUBSTR(LOOKUP_CODE,1,2) =:GLOBAL.FLEX_COUNTRY_CODE
Where <lookup_name> is the name of the lookup you defined which contains the valid values you want to include in the list of values. See: Creating a valid list of values for address components.
In this example the following statement would be used:
WHERE LOOKUP_TYPE = 'FAF_CITY' AND SUBSTR(LOOKUP_CODE,1,2) =:GLOBAL.FLEX_COUNTRY_CODE
The two-character country code of the country that you enter in all windows with flexible addresses is stored in the :GLOBAL.FLEX_COUNTRY_CODE field. Use this country code to develop country-specific validation within a flexfield that can be used in many countries.
In this example, we will assign the value set to the City segment in the Southern European address style for the Site Address descriptive flexfield.
Using the Application Developer responsibility, navigate to the Descriptive Flexfield Segments window.
Query the appropriate address descriptive flexfield, then uncheck the Freeze Flexfield Definition check box.
Oracle Receivables provides the following address descriptive flexfields, as described in this table:
| Descriptive Flexfield Name | Displayed in: |
|---|---|
| Bank Address | Banks |
| Remit Address | Remit-to Address, Customers |
| Check Address | Payment Summary, Payment Overview |
| Site Address | Suppliers |
Navigate to the Context Field Values tabbed region, then select the address style to which you are assigning the value set.
In this example the value set will be added to the Southern European address style.
Choose Segments, then select the relevant address segment.
In this example it is the City segment.
Enter the name of the Value Set that you created (in this example it is FAF_CITY).
Return to the Descriptive Flexfield window and check the Freeze Flexfield Definition check box.
Save your work.
Follow the above steps to set up the same validation for each address descriptive flexfield.
The address styles should be identical across all address flexfields. If they are not the same, you risk creating addresses that are incompatible with different windows.
Warning: Do not assign a value set to a flexfield if any of the countries using that flexfield do not have any data defined in the lookup. Using the list of values will return no data for these countries and you will be unable to enter any information in the address element on which the value set is used.