Query to find State and State Code in Oracle Applications

 Query to find State and State Code

SELECT g.geography_code    state_code,

       gi.identifier_value state_name

  FROM HZ_GEOGRAPHIES             g,

       (SELECT t.geography_id,

               t.identifier_value

          FROM HZ_GEOGRAPHY_IDENTIFIERS t

         WHERE t.identifier_subtype = 'STANDARD_NAME'

           AND t.application_id = 222

           AND t.language_code = 'US'

           AND t.geography_type = 'STATE'

           AND t.primary_flag = 'N'

           AND TRUNC (t.last_update_date) =

               (SELECT TRUNC (MAX (t2.last_update_date))

                  FROM HZ_GEOGRAPHY_IDENTIFIERS t2

                 WHERE t2.geography_id = t.geography_id)) gi

 WHERE g.geography_id = gi.geography_id

   AND g.country_code = 'US'

   AND g.application_id = 222

   AND g.geography_type = 'STATE'

 ORDER BY gi.identifier_value;