--
-- packages/acs-reference/sql/us-states.sql
--
-- @author jon@arsdigita.com
-- @creation-date 2000-11-28
-- @cvs-id $Id: us-states.sql,v 1.1 2001/04/22 00:52:27 jong Exp $

create table us_states (
    abbrev          char(2)
                    constraint us_states_abbrev_pk primary key,
    state_name      varchar2(100)
	            constraint us_states_state_name_nn not null
                    constraint us_states_state_name_uq unique,
    fips_state_code char(2)
                    constraint us_states_fips_state_code_uq unique
);

comment on table us_states is '
This is the US states table.
';

comment on column us_states.abbrev is '
This is the 2 letter abbreviation for states.
';

comment on column us_states.fips_state_code is '
The FIPS code used by the USPS for certain delivery types.
';

-- add this table into the reference repository
declare
    v_id integer;
begin
    v_id := acs_reference.new(
        table_name      => 'US_STATES',
        source          => 'Internal',
        source_url      => '',
        last_update     => sysdate,
        internal_data_p => 't',
        effective_date  => sysdate
    );
commit;
end;
/

-- Some data

set feedback off;

insert into us_states values ('AL','ALABAMA','01');
insert into us_states values ('AK','ALASKA','02');
insert into us_states values ('AZ','ARIZONA','04');
insert into us_states values ('AR','ARKANSAS','05');
insert into us_states values ('CA','CALIFORNIA','06');
insert into us_states values ('CO','COLORADO','08');
insert into us_states values ('CT','CONNECTICUT','09');
insert into us_states values ('DE','DELAWARE','10');
insert into us_states values ('DC','DISTRICT OF COLUMBIA','11');
insert into us_states values ('FL','FLORIDA','12');
insert into us_states values ('GA','GEORGIA','13');
insert into us_states values ('HI','HAWAII','15');
insert into us_states values ('ID','IDAHO','16');
insert into us_states values ('IL','ILLINOIS','17');
insert into us_states values ('IN','INDIANA','18');
insert into us_states values ('IA','IOWA','19');
insert into us_states values ('KS','KANSAS','20');
insert into us_states values ('KY','KENTUCKY','21');
insert into us_states values ('LA','LOUISIANA','22');
insert into us_states values ('ME','MAINE','23');
insert into us_states values ('MD','MARYLAND','24');
insert into us_states values ('MA','MASSACHUSETTS','25');
insert into us_states values ('MI','MICHIGAN','26');
insert into us_states values ('MN','MINNESOTA','27');
insert into us_states values ('MS','MISSISSIPPI','28');
insert into us_states values ('MO','MISSOURI','29');
insert into us_states values ('MT','MONTANA','30');
insert into us_states values ('NE','NEBRASKA','31');
insert into us_states values ('NV','NEVADA','32');
insert into us_states values ('NH','NEW HAMPSHIRE','33');
insert into us_states values ('NJ','NEW JERSEY','34');
insert into us_states values ('NM','NEW MEXICO','35');
insert into us_states values ('NY','NEW YORK','36');
insert into us_states values ('NC','NORTH CAROLINA','37');
insert into us_states values ('ND','NORTH DAKOTA','38');
insert into us_states values ('OH','OHIO','39');
insert into us_states values ('OK','OKLAHOMA','40');
insert into us_states values ('OR','OREGON','41');
insert into us_states values ('PA','PENNSYLVANIA','42');
insert into us_states values ('RI','RHODE ISLAND','44');
insert into us_states values ('SC','SOUTH CAROLINA','45');
insert into us_states values ('SD','SOUTH DAKOTA','46');
insert into us_states values ('TN','TENNESSEE','47');
insert into us_states values ('TX','TEXAS','48');
insert into us_states values ('UT','UTAH','49');
insert into us_states values ('VT','VERMONT','50');
insert into us_states values ('VA','VIRGINIA','51');
insert into us_states values ('WA','WASHINGTON','53');
insert into us_states values ('WV','WEST VIRGINIA','54');
insert into us_states values ('WI','WISCONSIN','55');
insert into us_states values ('WY','WYOMING','56');
insert into us_states values ('AS','AMERICAN SAMOA','60');
insert into us_states values ('GU','GUAM','66');
insert into us_states values ('MP','NORTHERN MARIANA ISLANDS','69');
insert into us_states values ('PR','PUERTO RICO','72');
insert into us_states values ('VI','VIRGIN ISLANDS','78');
insert into us_states values ('FM','FED. STATES OF MICRONESIA','64');
insert into us_states values ('UM','US MINOR OUTLYING ISLANDS','74');
insert into us_states values ('67','JOHNSTON ATOLL','67');
insert into us_states values ('MH','MARSHALL ISLANDS','68');
insert into us_states values ('PW','PALAU','70');
insert into us_states values ('71','MIDWAY ISLANDS','71');
insert into us_states values ('76','NAVASSA ISLAND','76');
insert into us_states values ('79','WAKE ISLAND','79');
insert into us_states values ('81','BAKER ISLAND','81');
insert into us_states values ('84','HOWLAND ISLAND','84');
insert into us_states values ('86','JARVIS ISLAND','86');
insert into us_states values ('89','KINGMAN REEF','89');
insert into us_states values ('95','PALMYRA ATOLL','95');

set feedback on;

commit;