Query on Country Code of OTM

[dilipkumar.mandal]

Hi,
Can anyone clarify the following?

We generally use country code as 2 digits. Like Japan is "JP".
Why OTM country master data using the 3 digits ISO Territory Code as Country Code? Why OTM country master data displaying the Country ID as 3 digits ISO Territory Code? Is there any specific reason?


Thanks,
Dilip

[josephliang]

Re: Query on Country Code of OTM

Can't tell you why, but do have a workaround for you:

1. Create the new 2-digit country codes

INSERT INTO country_code (country_code3_gid, country_code3_xid, country_code2, country_name, country_number, country_zone_gid, country_code_fips, geo_postal_code_length, is_eu_member_country, validate_postal_code, car_registration_code, domain_name)
SELECT country_code2, country_code2, country_code2, country_name, country_number, country_zone_gid, country_code_fips, geo_postal_code_length, is_eu_member_country, validate_postal_code, car_registration_code, domain_name
FROM country_code
WHERE length (country_code3_gid) = 3;

2. Country Code Conversion

set pagesize 0
set linesize 300
set trimspool on
SELECT DISTINCT    'UPDATE '
               || a.table_name
               || ' t SET '
               || a.column_name
               || ' = (SELECT  country_code2
       from country_code cc
 WHERE cc.COUNTRY_CODE3_GID = t.'
               || a.column_name
               || ')
  WHERE length ('
               || a.column_name
               || ') = 3;'
          FROM all_cons_columns a, all_constraints b
         WHERE b.table_name = a.table_name
           AND a.owner = 'GLOGOWNER'
           AND b.owner = 'GLOGOWNER'
           AND b.constraint_name = a.constraint_name
           AND b.constraint_type = 'R'
           AND b.r_constraint_name IN ('PK_COUNTRY_ZONE', 'PK_COUNTRY_CODE')
  AND a.table_name != 'COUNTRY_CODE'
 
spool c:\country_code.sql
/
spool off
 
@c:\country_code.sql

[Kristof Stevens]

Re: Query on Country Code of OTM

Is this script valid for 6.2? Because it seems the constraintnamens have been changed.

[josephliang]

Re: Query on Country Code of OTM

I haven't tried this in OTM 6.2
Just check update_cc3_cc2.sql and create_country_2char_triggers.sql under script8 folder.