-- Interface Module: CurrConvRates_Fnd
-- Interface: Curr_Conv_Rates_Out

-- Create sequence for bdi_seq_id column

CREATE SEQUENCE curr_conv_rates_out_seq ORDER CACHE 20 MAXVALUE 9999999999999999999;


-- Create BDI Outbound Table: CURR_CONV_RATES_OUT
-- This table is used to integrate currency rates information.

CREATE TABLE CURR_CONV_RATES_OUT (
  -- bdi internal column
  bdi_seq_id NUMBER DEFAULT curr_conv_rates_out_seq.nextval NOT NULL,
  -- bdi internal column
  bdi_app_name VARCHAR2(50) DEFAULT sys_context('userenv', 'current_schema') NOT NULL,
  -- bdi internal column
  bdi_dataset_type VARCHAR2(20) DEFAULT 'FULL',
  -- bdi internal column
  bdi_dataset_action VARCHAR2(20) DEFAULT 'REPLACE',
  -- Holds the convert from currency code. It will always be the primary currency.
  from_currency_code VARCHAR2(3) NOT NULL,
  -- Holds the convert to currency code. It will only include currencies for which stores and warehouses exist in RMS. 
  to_currency_code VARCHAR2(3) NOT NULL,
  -- Holds the next effective date of the exchange rate for the currencies and the exchange type.
  effective_date TIMESTAMP NOT NULL,
  -- Identifies the type of exchange rate.  Valid values are C - Consolidation, O - Operational based on the RMS system options setting.
  exchange_type VARCHAR2(1) NOT NULL,
  -- Contains the exchange rate between the from and to currencies for the specified exchange type on the next effective date. It is expressed in the to-currency.
  exchange_rate NUMBER(20,10) NOT NULL
);

COMMENT ON TABLE CURR_CONV_RATES_OUT IS 'This table is used to integrate currency rates information.';
COMMENT ON COLUMN CURR_CONV_RATES_OUT.bdi_seq_id IS 'bdi internal column';
COMMENT ON COLUMN CURR_CONV_RATES_OUT.bdi_app_name IS 'bdi internal column';
COMMENT ON COLUMN CURR_CONV_RATES_OUT.bdi_dataset_type IS 'bdi internal column';
COMMENT ON COLUMN CURR_CONV_RATES_OUT.bdi_dataset_action IS 'bdi internal column';
COMMENT ON COLUMN CURR_CONV_RATES_OUT.from_currency_code IS 'Holds the convert from currency code. It will always be the primary currency.';
COMMENT ON COLUMN CURR_CONV_RATES_OUT.to_currency_code IS 'Holds the convert to currency code. It will only include currencies for which stores and warehouses exist in RMS. ';
COMMENT ON COLUMN CURR_CONV_RATES_OUT.effective_date IS 'Holds the next effective date of the exchange rate for the currencies and the exchange type.';
COMMENT ON COLUMN CURR_CONV_RATES_OUT.exchange_type IS 'Identifies the type of exchange rate.  Valid values are C - Consolidation, O - Operational based on the RMS system options setting.';
COMMENT ON COLUMN CURR_CONV_RATES_OUT.exchange_rate IS 'Contains the exchange rate between the from and to currencies for the specified exchange type on the next effective date. It is expressed in the to-currency.';


-- Add BDI primary key constraint

ALTER TABLE CURR_CONV_RATES_OUT ADD CONSTRAINT pk_curr_conv_rates_out PRIMARY KEY (bdi_app_name, bdi_seq_id);


-- Add check constraint for bdi_dataset_type column

ALTER TABLE CURR_CONV_RATES_OUT ADD CONSTRAINT chk_type_curr_conv_rates_out CHECK (bdi_dataset_type IN ('FULL', 'PARTIAL'));


-- Add check constraint for bdi_dataset_action column

ALTER TABLE CURR_CONV_RATES_OUT ADD CONSTRAINT chk_actn_curr_conv_rates_out CHECK (bdi_dataset_action IN ('REPLACE', 'CREATE', 'UPDATE', 'DELETE'));