-- Interface Module: Calendar_Fnd
-- Interface: Calendar_In

-- Create BDI Inbound Table: CALENDAR_IN
-- This table is used to integrate calendar information.


CREATE TABLE CALENDAR_IN (
  -- bdi internal column
  bdi_seq_id NUMBER NOT NULL,
  -- bdi internal column
  bdi_app_name VARCHAR2(50) NOT NULL,
  -- bdi internal column
  bdi_dataset_type VARCHAR2(20),
  -- bdi internal column
  bdi_dataset_action VARCHAR2(20),
  -- Holds the date from which the 4-5-4 data was derived, in YYYYMMDD format.
  day TIMESTAMP NOT NULL,
  -- Holds the end of week date for the day, in YYYYMMDD format.
  week TIMESTAMP NOT NULL,
  -- Holds the end of month date for the date, in YYYYMMDD format.
  eom_date TIMESTAMP NOT NULL,
  -- Holds the day number within the week.
  day_of_week NUMBER(2) NOT NULL,
  -- Holds the week number within the month.
  day_date_454_week NUMBER(2) NOT NULL,
  -- Holds the 4-5-4 month of the year, valid values 1-12.
  month NUMBER(2) NOT NULL,
  -- Holds the 4-5-4 year.
  year NUMBER(4) NOT NULL,
  -- Holds the 4-5-4 quarter of the year, valid values 1-4.
  quarter NUMBER(1) NOT NULL,
  -- Holds the MCAL year in YYYY format.
  mcal_year NUMBER(5) NOT NULL,
  -- Holds the 4-5-4 half of the year, valid values are 1 or 2.
  half NUMBER(1) NOT NULL,
  -- Holds the 4-5-4 week of the year, valid values 1-53.
  week_of_year NUMBER(2) NOT NULL
);

COMMENT ON TABLE CALENDAR_IN IS 'This table is used to integrate calendar information.';
COMMENT ON COLUMN CALENDAR_IN.bdi_seq_id IS 'bdi internal column';
COMMENT ON COLUMN CALENDAR_IN.bdi_app_name IS 'bdi internal column';
COMMENT ON COLUMN CALENDAR_IN.bdi_dataset_type IS 'bdi internal column';
COMMENT ON COLUMN CALENDAR_IN.bdi_dataset_action IS 'bdi internal column';
COMMENT ON COLUMN CALENDAR_IN.day IS 'Holds the date from which the 4-5-4 data was derived, in YYYYMMDD format.';
COMMENT ON COLUMN CALENDAR_IN.week IS 'Holds the end of week date for the day, in YYYYMMDD format.';
COMMENT ON COLUMN CALENDAR_IN.eom_date IS 'Holds the end of month date for the date, in YYYYMMDD format.';
COMMENT ON COLUMN CALENDAR_IN.day_of_week IS 'Holds the day number within the week.';
COMMENT ON COLUMN CALENDAR_IN.day_date_454_week IS 'Holds the week number within the month.';
COMMENT ON COLUMN CALENDAR_IN.month IS 'Holds the 4-5-4 month of the year, valid values 1-12.';
COMMENT ON COLUMN CALENDAR_IN.year IS 'Holds the 4-5-4 year.';
COMMENT ON COLUMN CALENDAR_IN.quarter IS 'Holds the 4-5-4 quarter of the year, valid values 1-4.';
COMMENT ON COLUMN CALENDAR_IN.mcal_year IS 'Holds the MCAL year in YYYY format.';
COMMENT ON COLUMN CALENDAR_IN.half IS 'Holds the 4-5-4 half of the year, valid values are 1 or 2.';
COMMENT ON COLUMN CALENDAR_IN.week_of_year IS 'Holds the 4-5-4 week of the year, valid values 1-53.';


-- Add BDI primary key constraint

ALTER TABLE CALENDAR_IN ADD CONSTRAINT pk_calendar_in PRIMARY KEY (bdi_app_name, bdi_seq_id);