In Release 8.5, the Best Match Region for Sterling Business Intelligence functionality enables you to generate Data Warehouse reports with best match region information for sourcing and resource pools. To enable this functionality, the short zip code field in YFS_REGION_BEST_MATCH must be correctly populated with short zip codes from YFS_PERSON_INFO records. The short zip code field in new person info records is populated from the zip code field by configuring either of the following:
Existing customers should follow the manual migration process, which explains how to use database level utilities to update zip codes in existing person info records.
This section describes the migration process.
None.
The method for updating person info records is based on the content of your data and the database vendor. This section provides guidelines for update statements and several examples:
update YFS_PERSON_INFO set SHORT_ZIP_CODE = ZIP_CODE where COUNTRY = 'US'
update YFS_PERSON_INFO set SHORT_ZIP_CODE = substr(ZIP_CODE,1,3) where COUNTRY='CA'
Canadian postal codes store high level region information in the first three alphanumeric characters. The last three characters are more granular.
Some scenarios may require more complex logic in which database vendor regular expression syntax or database scripts must be used.
Oracle provides regular expression functions in version 10g and later, as shown in the following sample update statement to keep the first five digits:
update YFS_PERSON_INFO set SHORT_ZIP_CODE = REGEXP_SUBSTR(ZIP_CODE,'[[:digit:]]{5}');
For DB2®, a database script can be created to handle regular expression situations for DB2 installation.
With all database vendors, update statements can be batched to provide better performance, such as row limiting parallel threads to update the table. Records can be limited by using the following strategy on Oracle and similar syntax on other databases:
update YFS_PERSON_INFO set SHORT_ZIP_CODE = ZIP_CODE where COUNTRY = 'US' and SHORT_ZIP_CODE IS NULL AND ROWNUM < 10000