-- Interface Module: VatItem_Fnd
-- Interface: Vat_Item_Out

-- Create sequence for bdi_seq_id column

CREATE SEQUENCE vat_item_out_seq ORDER CACHE 20 MAXVALUE 9999999999999999999;


-- Create BDI Outbound Table: VAT_ITEM_OUT
-- This table is used to integrate VAT Item information.

CREATE TABLE VAT_ITEM_OUT (
  -- bdi internal column
  bdi_seq_id NUMBER DEFAULT vat_item_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',
  -- ID of item.
  item VARCHAR2(25) NOT NULL,
  -- This field holds the active date for record on future cost.
  active_date TIMESTAMP NOT NULL,
  -- This field indicates the type of VAT. Valid values include R (Retail), C (Cost), B (Both).
  vat_type VARCHAR2(1) NOT NULL,
  -- This field contains the alphanumeric identification for the VAT code.
  vat_code VARCHAR2(6) NOT NULL,
  -- This field contains the VAT rate for the item-location.
  vat_rate NUMBER(20,10) NOT NULL,
  -- Indicates if the item is subject to reverse charge VAT at the vat region. Valid values include Y (Yes), N (No).
  reverse_vat_ind VARCHAR2(1) NOT NULL,
  -- Contains the number of the Value Added Tax region a location belongs to.
  vat_region NUMBER(4) NOT NULL
);

COMMENT ON TABLE VAT_ITEM_OUT IS 'This table is used to integrate VAT Item information.';
COMMENT ON COLUMN VAT_ITEM_OUT.bdi_seq_id IS 'bdi internal column';
COMMENT ON COLUMN VAT_ITEM_OUT.bdi_app_name IS 'bdi internal column';
COMMENT ON COLUMN VAT_ITEM_OUT.bdi_dataset_type IS 'bdi internal column';
COMMENT ON COLUMN VAT_ITEM_OUT.bdi_dataset_action IS 'bdi internal column';
COMMENT ON COLUMN VAT_ITEM_OUT.item IS 'ID of item.';
COMMENT ON COLUMN VAT_ITEM_OUT.active_date IS 'This field holds the active date for record on future cost.';
COMMENT ON COLUMN VAT_ITEM_OUT.vat_type IS 'This field indicates the type of VAT. Valid values include R (Retail), C (Cost), B (Both).';
COMMENT ON COLUMN VAT_ITEM_OUT.vat_code IS 'This field contains the alphanumeric identification for the VAT code.';
COMMENT ON COLUMN VAT_ITEM_OUT.vat_rate IS 'This field contains the VAT rate for the item-location.';
COMMENT ON COLUMN VAT_ITEM_OUT.reverse_vat_ind IS 'Indicates if the item is subject to reverse charge VAT at the vat region. Valid values include Y (Yes), N (No).';
COMMENT ON COLUMN VAT_ITEM_OUT.vat_region IS 'Contains the number of the Value Added Tax region a location belongs to.';


-- Add BDI primary key constraint

ALTER TABLE VAT_ITEM_OUT ADD CONSTRAINT pk_vat_item_out PRIMARY KEY (bdi_app_name, bdi_seq_id);


-- Add check constraint for bdi_dataset_type column

ALTER TABLE VAT_ITEM_OUT ADD CONSTRAINT chk_type_vat_item_out CHECK (bdi_dataset_type IN ('FULL', 'PARTIAL'));


-- Add check constraint for bdi_dataset_action column

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