Announcement

Collapse
No announcement yet.

Query on Country Code of OTM

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • Query on Country Code of OTM

    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

  • #2
    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
    Code:
    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
    Code:
    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
    --
    Joseph Liang
    MavenWire APAC
    http://www.mavenwire.com/

    Comment


    • #3
      Re: Query on Country Code of OTM

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

      Comment


      • #4
        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.
        --
        Joseph Liang
        MavenWire APAC
        http://www.mavenwire.com/

        Comment

        Working...
        X