Updating additional SQL data

Edit online

You can extend the optional SQL data that is provided in Sterling Order Management to include additional ZIP codes and region data.

Sterling Order Management currently provides optional SQLs for loading US ZIP codes and region data. You can also extend these SQLs to add other non-US postal codes and region data.

The following SQL files are currently available by default with Sterling Order Management:
  • runtime/database/FactorySetup/Optional/db2/RegionSchema-US/RegionSchema-US.sql
  • runtime/database/FactorySetup/Optional/db2/ZipCodeLocation/US_ZipcodeLocation.sql

To extend the additional SQL data, you must add your additional SQL files under <runtime>/extensions/global/database/FactorySetup/Optional/db2 directory.

The SQLs must have only INSERT INTO statements for the following tables:
  • YFS_REGION_SCHEMA
  • YFS_REGION_LEVEL
  • YFS_REGION_LEVEL_ASSOC
  • YFS_REGION
  • YFS_REGION_DETAIL
  • YFS_ZIP_CODE_LOCATION
Note:
  • Avoid appending to the existing out-of-the-box US_ZipcodeLocation.sql file as duplication errors might cause Load Optional DB2 Scripts to unpick custom ZIP code location entries.
  • If you see failure for already inserted records in both region schema and ZIP code location, you can ignore them.
  • You must ensure that ZIP_CODE_LOCATION_KEY is unique for entries and does not clash with any new records that are released by the product in future.

    Suggestion for key: <Year><Month><Date><Time>_Extn = 201807171511_Extn