-- packages/acs-reference/sql/oracle/ref-currency-create.sql
--
-- @author jon@jongriffin.com
-- @creation-date 2001-08-27
-- @cvs-id $Id: ref-currency-create.sql,v 1.1.1.1 2005/11/14 16:31:43 torbenb Exp $


create table currencies (
    -- since currencies 
    -- 3 char alphabetic
    codeA char(3)
        constraint currencies_code_a_pk
        primary key,
    -- this is the currency #
    codeN number,
    -- this is the minor unit
    -- not sure of the use but it is in the standar
    minor_unit char(1),
    -- explanation per iso
    note varchar(4000),
    -- this violates 3nf but is used for 2 reasons
    -- 1. to help efficiency
    -- 2. to make querys not fail if no translation exists yet
    default_name varchar(100)
        constraint currencies_default_name_nn
        not null
);

comment on table currencies is '
    This is the currency code/english name table from ISO 4217.
';

-- add this table into the reference repository
declare
    v_id integer;
begin
    v_id := acs_reference.new(
        table_name     => 'CURRENCIES',
        source         => 'ISO 4217',
        source_url     => 'http://www.iso.ch',
        last_update    => to_date('2000-10-30','YYYY-MM-DD'),
        effective_date => sysdate
    );
commit;
end;
/

-- This is the translated mapping of country names

create table currency_names (
    -- lookup into the countries table
    codeA char(3)
        constraint currency_names_iso_fk
        references currencies (codeA),
    -- lookup into the language_codes table
    language_code 
        constraint currency_names_lang_code_fk
        references language_codes (language_id),
    -- the translated name
    name varchar(100)
);

comment on table currency_names is ' 
    This is the translated mapping of currency names and language codes.
';

comment on column currency_names.language_code is '
    This is a lookup into the iso languages table.
';

declare
    v_id integer;
begin
    v_id := acs_reference.new(
        table_name     => 'CURRENCY_NAMES',
        source         => 'ISO 4217',
        source_url     => 'http://www.iso.ch',
        last_update    => to_date('2000-10-30','YYYY-MM-DD'),
        effective_date => sysdate
    );
commit;
end;
/

-- map from currencies to country
create table currency_country_map (
    codeA char(3)
        constraint currency_country_map_code_fk
        references currencies (codeA),
    -- foreign key to relate country to currency
    -- this can by one => many therefor can't be unique
    -- i.e. Cuba has USD and CUP
    country_code char(2)
        constraint curr_cntry_map_country_fk
        references countries (iso)
);

declare
    v_id integer;
begin
    v_id := acs_reference.new(
        table_name     => 'CURRENCY_COUNTRY_MAP',
        source         => 'ISO 4217',
        source_url     => 'http://www.iso.ch',
        last_update    => to_date('2000-10-30','YYYY-MM-DD'),
        effective_date => sysdate
    );
commit;
end;
/

-- I will add a view to join this stuff later.