-- Interface Module: Store_Fnd
-- Interface: Store_Out

-- Create sequence for bdi_seq_id column

CREATE SEQUENCE store_out_seq ORDER CACHE 20 MAXVALUE 9999999999999999999;


-- Create BDI Outbound Table: STORE_OUT
-- This table is used to integrate store information.

CREATE TABLE STORE_OUT (
  -- bdi internal column
  bdi_seq_id NUMBER DEFAULT store_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',
  -- Unique ID of the store.
  store_id NUMBER(10,0) NOT NULL,
  -- This will indicate whether a particular store is a franchise or company store.
  store_type VARCHAR2(6),
  -- Contains the name of the store which, along with the store number, identifies the store.
  store_name VARCHAR2(150),
  -- Contains a ten character abbreviation of the store name.
  store_name_10 VARCHAR2(10),
  -- Contains a three character abbreviation of the store name.
  store_name_abbr VARCHAR2(3),
  -- Secondary name of the store.
  store_name_sec VARCHAR2(150),
  -- Contains the code letter indicating the class of which the store is a member. Valid values are A through E.
  store_class_id VARCHAR2(1),
  -- Contains the name of the store class.
  store_class_desc VARCHAR2(250),
  -- Contains the name of the store manager.
  manager VARCHAR2(120),
  -- Contains the date on which the store opened.
  open_date TIMESTAMP,
  -- Contains the date on which the store closed.
  close_date TIMESTAMP,
  -- Contains the date on which the store was acquired.
  acquire_date TIMESTAMP,
  -- Contains the date on which the store was last remodeled.
  remodel_date TIMESTAMP,
  -- Contains the fax number for the store.
  fax_number VARCHAR2(20),
  -- Contains the phone number for the store.
  phone_number VARCHAR2(20),
  -- Holds the email address for the location.
  email VARCHAR2(100),
  -- Contains the total square footage of the store.
  total_sq_feet NUMBER(8,0),
  -- Contains the total square footage of the stores selling area.
  selling_sq_feet NUMBER(8,0),
  -- Holds the total merchandisable space of the location.
  linear_distance NUMBER(8,0),
  -- Contains the number of the Value Added Tax region in which this store is contained.
  vat_region NUMBER(4,0),
  -- Indicates whether or not Value Added Tax will be included in the retail prices for the store. Valid values are Y or N.
  vat_incl_ind VARCHAR2(1),
  -- This column indicates whether the store can hold stock. In a non-multichannel environment this will always be Y.
  stock_holding_ind VARCHAR2(1),
  -- In a multichannel environment this will contain the channel with which the store is associated. Valid values can be found on the channels table.
  channel_id NUMBER(4,0),
  -- Contains the name of the channel.
  channel_name VARCHAR2(120),
  -- Contains the number indicating the format of the store. Valid values are found on the store format table.
  store_format_id NUMBER(4,0),
  -- Contains the name of the store format.
  store_format_name VARCHAR2(60),
  -- Contains the name of the mall in which the store is located.
  mall_name VARCHAR2(120),
  -- Contains the number of the district in which the store is a member.
  district NUMBER(10,0),
  -- Contains the transfer zone in which the store is located. Valid values are located on the tsfzone table.
  transfer_zone NUMBER(4,0),
  -- Contains the name of the Transfer Zone.
  transfer_zone_desc VARCHAR2(120),
  -- Contains the number of the warehouse that may be used as the default for creating crossdock masks. This determines which stores are associated with or sourced from a warehouse. Will hold only virtual warehouses in a multi-channel environment.
  default_wh NUMBER(10,0),
  -- Contains the number of days before a store closing that the store will stop accepting orders.
  stop_order_days NUMBER(3,0),
  -- Contains the number of days before the store_open_date that the store will begin accepting orders.
  start_order_days NUMBER(3,0),
  -- This field contains the currency code under which the store operates.
  currency_code VARCHAR2(3),
  -- This column identifies the language to be used for the given store.
  store_lang_iso_code VARCHAR2(6),
  -- Contains the level at which unique POS transaction numbers are generated. If the store has one sequence number that is used for all registers, then the value in this columnwill be S (Store) otherwise it will be R (Register).
  tran_no_generate VARCHAR2(6),
  -- Indicates whether or not the POS system at the Store is integrated.
  integrated_pos_ind VARCHAR2(1) NOT NULL,
  -- This field holds the Dun and Bradstreet number to identify the store.
  duns_number VARCHAR2(9),
  -- This field contains the Dun and Bradstreet number to identify the location.
  duns_loc VARCHAR2(4),
  -- This field will hold a store number which will be used to relate the current store to the historical data of an existing store.
  sister_store NUMBER(10,0),
  -- This is the Id of the transfer entity this store belongs to. A transfer entity is a group of locations that are a part of single legal entity and share same accounting set of books.
  tsf_entity_id NUMBER(10,0),
  -- Column will contain the organizational unit ID value.
  org_unit_id NUMBER(15,0),
  -- This column will indicate whether the client is allowing automatic receipt for the store. Valid Values are Y (Yes), N (No), D (System Default). Default value should be D.
  auto_rcv VARCHAR2(1),
  -- Identifies stores that are undergoing a significant remerchandising effort. Used only when AIP is integrated with RMS.
  remerch_ind VARCHAR2(1),
  -- Numeric Id of the customer.
  wf_customer NUMBER(10,0),
  -- Indicates the time zone of the store. For example, America/New_York.
  timezone VARCHAR2(64),
  -- This Column determines whether the location is customer order location or not.
  customer_order_loc_ind VARCHAR2(1),
  -- This column determines whether the location will support gift wrapping or not.
  gift_wrapping_ind VARCHAR2(1) NOT NULL,
  -- This column determines whether the location will support shipping to the customer for customer orders. Valid values are Y (Yes), N (No).
  customer_order_ship_ind VARCHAR2(1) NOT NULL
);

COMMENT ON TABLE STORE_OUT IS 'This table is used to integrate store information.';
COMMENT ON COLUMN STORE_OUT.bdi_seq_id IS 'bdi internal column';
COMMENT ON COLUMN STORE_OUT.bdi_app_name IS 'bdi internal column';
COMMENT ON COLUMN STORE_OUT.bdi_dataset_type IS 'bdi internal column';
COMMENT ON COLUMN STORE_OUT.bdi_dataset_action IS 'bdi internal column';
COMMENT ON COLUMN STORE_OUT.store_id IS 'Unique ID of the store.';
COMMENT ON COLUMN STORE_OUT.store_type IS 'This will indicate whether a particular store is a franchise or company store.';
COMMENT ON COLUMN STORE_OUT.store_name IS 'Contains the name of the store which, along with the store number, identifies the store.';
COMMENT ON COLUMN STORE_OUT.store_name_10 IS 'Contains a ten character abbreviation of the store name.';
COMMENT ON COLUMN STORE_OUT.store_name_abbr IS 'Contains a three character abbreviation of the store name.';
COMMENT ON COLUMN STORE_OUT.store_name_sec IS 'Secondary name of the store.';
COMMENT ON COLUMN STORE_OUT.store_class_id IS 'Contains the code letter indicating the class of which the store is a member. Valid values are A through E.';
COMMENT ON COLUMN STORE_OUT.store_class_desc IS 'Contains the name of the store class.';
COMMENT ON COLUMN STORE_OUT.manager IS 'Contains the name of the store manager.';
COMMENT ON COLUMN STORE_OUT.open_date IS 'Contains the date on which the store opened.';
COMMENT ON COLUMN STORE_OUT.close_date IS 'Contains the date on which the store closed.';
COMMENT ON COLUMN STORE_OUT.acquire_date IS 'Contains the date on which the store was acquired.';
COMMENT ON COLUMN STORE_OUT.remodel_date IS 'Contains the date on which the store was last remodeled.';
COMMENT ON COLUMN STORE_OUT.fax_number IS 'Contains the fax number for the store.';
COMMENT ON COLUMN STORE_OUT.phone_number IS 'Contains the phone number for the store.';
COMMENT ON COLUMN STORE_OUT.email IS 'Holds the email address for the location.';
COMMENT ON COLUMN STORE_OUT.total_sq_feet IS 'Contains the total square footage of the store.';
COMMENT ON COLUMN STORE_OUT.selling_sq_feet IS 'Contains the total square footage of the stores selling area.';
COMMENT ON COLUMN STORE_OUT.linear_distance IS 'Holds the total merchandisable space of the location.';
COMMENT ON COLUMN STORE_OUT.vat_region IS 'Contains the number of the Value Added Tax region in which this store is contained.';
COMMENT ON COLUMN STORE_OUT.vat_incl_ind IS 'Indicates whether or not Value Added Tax will be included in the retail prices for the store. Valid values are Y or N.';
COMMENT ON COLUMN STORE_OUT.stock_holding_ind IS 'This column indicates whether the store can hold stock. In a non-multichannel environment this will always be Y.';
COMMENT ON COLUMN STORE_OUT.channel_id IS 'In a multichannel environment this will contain the channel with which the store is associated. Valid values can be found on the channels table.';
COMMENT ON COLUMN STORE_OUT.channel_name IS 'Contains the name of the channel.';
COMMENT ON COLUMN STORE_OUT.store_format_id IS 'Contains the number indicating the format of the store. Valid values are found on the store format table.';
COMMENT ON COLUMN STORE_OUT.store_format_name IS 'Contains the name of the store format.';
COMMENT ON COLUMN STORE_OUT.mall_name IS 'Contains the name of the mall in which the store is located.';
COMMENT ON COLUMN STORE_OUT.district IS 'Contains the number of the district in which the store is a member.';
COMMENT ON COLUMN STORE_OUT.transfer_zone IS 'Contains the transfer zone in which the store is located. Valid values are located on the tsfzone table.';
COMMENT ON COLUMN STORE_OUT.transfer_zone_desc IS 'Contains the name of the Transfer Zone.';
COMMENT ON COLUMN STORE_OUT.default_wh IS 'Contains the number of the warehouse that may be used as the default for creating crossdock masks. This determines which stores are associated with or sourced from a warehouse. Will hold only virtual warehouses in a multi-channel environment.';
COMMENT ON COLUMN STORE_OUT.stop_order_days IS 'Contains the number of days before a store closing that the store will stop accepting orders.';
COMMENT ON COLUMN STORE_OUT.start_order_days IS 'Contains the number of days before the store_open_date that the store will begin accepting orders.';
COMMENT ON COLUMN STORE_OUT.currency_code IS 'This field contains the currency code under which the store operates.';
COMMENT ON COLUMN STORE_OUT.store_lang_iso_code IS 'This column identifies the language to be used for the given store.';
COMMENT ON COLUMN STORE_OUT.tran_no_generate IS 'Contains the level at which unique POS transaction numbers are generated. If the store has one sequence number that is used for all registers, then the value in this columnwill be S (Store) otherwise it will be R (Register).';
COMMENT ON COLUMN STORE_OUT.integrated_pos_ind IS 'Indicates whether or not the POS system at the Store is integrated.';
COMMENT ON COLUMN STORE_OUT.duns_number IS 'This field holds the Dun and Bradstreet number to identify the store.';
COMMENT ON COLUMN STORE_OUT.duns_loc IS 'This field contains the Dun and Bradstreet number to identify the location.';
COMMENT ON COLUMN STORE_OUT.sister_store IS 'This field will hold a store number which will be used to relate the current store to the historical data of an existing store.';
COMMENT ON COLUMN STORE_OUT.tsf_entity_id IS 'This is the Id of the transfer entity this store belongs to. A transfer entity is a group of locations that are a part of single legal entity and share same accounting set of books.';
COMMENT ON COLUMN STORE_OUT.org_unit_id IS 'Column will contain the organizational unit ID value.';
COMMENT ON COLUMN STORE_OUT.auto_rcv IS 'This column will indicate whether the client is allowing automatic receipt for the store. Valid Values are Y (Yes), N (No), D (System Default). Default value should be D.';
COMMENT ON COLUMN STORE_OUT.remerch_ind IS 'Identifies stores that are undergoing a significant remerchandising effort. Used only when AIP is integrated with RMS.';
COMMENT ON COLUMN STORE_OUT.wf_customer IS 'Numeric Id of the customer.';
COMMENT ON COLUMN STORE_OUT.timezone IS 'Indicates the time zone of the store. For example, America/New_York.';
COMMENT ON COLUMN STORE_OUT.customer_order_loc_ind IS 'This Column determines whether the location is customer order location or not.';
COMMENT ON COLUMN STORE_OUT.gift_wrapping_ind IS 'This column determines whether the location will support gift wrapping or not.';
COMMENT ON COLUMN STORE_OUT.customer_order_ship_ind IS 'This column determines whether the location will support shipping to the customer for customer orders. Valid values are Y (Yes), N (No).';


-- Add BDI primary key constraint

ALTER TABLE STORE_OUT ADD CONSTRAINT pk_store_out PRIMARY KEY (bdi_app_name, bdi_seq_id);


-- Add check constraint for bdi_dataset_type column

ALTER TABLE STORE_OUT ADD CONSTRAINT chk_type_store_out CHECK (bdi_dataset_type IN ('FULL', 'PARTIAL'));


-- Add check constraint for bdi_dataset_action column

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