Query to find State and State Code in Oracle Applications

 Query to find State and State Code

SELECT hg.geography_code     state_code, 

       hgi.identifier_value  state_name

  FROM (SELECT gi.geography_id,

               gi.identifier_value,

               ROW_NUMBER() OVER (PARTITION BY gi.geography_id ORDER BY gi.last_update_date DESC) rn

          FROM hz_geography_identifiers gi

         WHERE gi.identifier_type = 'NAME'

           AND gi.identifier_subtype = 'STANDARD_NAME'

           AND gi.geography_type = 'STATE'

           AND gi.language_code = 'US'

           AND gi.primary_flag = 'N') hgi

  JOIN hz_geographies hg

    ON hg.geography_id = hgi.geography_id

 WHERE hg.geography_type = 'STATE'

   AND hgi.rn = 1

 ORDER BY hgi.identifier_value;