-- Interface Module: FinisherAddr_Fnd
-- Interface: Finisher_Addr_Out

-- Create sequence for bdi_seq_id column

CREATE SEQUENCE finisher_addr_out_seq ORDER CACHE 20 MAXVALUE 9999999999999999999;


-- Create BDI Outbound Table: FINISHER_ADDR_OUT
-- This table is used to integrate finisher address information.

CREATE TABLE FINISHER_ADDR_OUT (
  -- bdi internal column
  bdi_seq_id NUMBER DEFAULT finisher_addr_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',
  -- Contains the code for the module that the address is attached to. Valid values are WH (Warehouse) and PTNR (Partner).
  module VARCHAR2(4) NOT NULL,
  -- Contains the unique ID of the Finisher.  Data will always be present in this field.
  finisher_id VARCHAR2(10) NOT NULL,
  -- This indicates the sequence that addresses within the same type were entered.
  seq_no NUMBER(4) NOT NULL,
  -- This column contains a unique number used to distinguish between different addresses.
  addr NUMBER(11) NOT NULL,
  -- Contains the code used to identify the address type.  Common examples include 01 (Business), 02 (Postal), 03 (Returns), 04 (Order), 05 (Invoice) and 06 (Remittance).  Data will always be present in this field.
  addr_type VARCHAR2(2) NOT NULL,
  -- Contains the code used to identify the address type.  Common Common examples include Business, Postal, Returns, Order, Invoice and Remittance.  Description data is only sent in the primary integration language of the system.
  addr_type_desc VARCHAR2(20) NOT NULL,
  -- Indicates whether the address type is the primary address type for the module. Valid values are Y and N.
  primary_addr_type_ind VARCHAR2(1),
  -- Indicates whether the address is the primary address for the address type.  Valid values are Y and N.
  primary_addr_ind VARCHAR2(1) NOT NULL,
  -- Contains the first line of the address.  This information is required.
  add_1 VARCHAR2(240) NOT NULL,
  -- This column contains the second line of the address.
  add_2 VARCHAR2(240),
  -- This column contains the third line of the address.
  add_3 VARCHAR2(240),
  -- Contains the name of the city that is associated with the address.  This information is required.
  city VARCHAR2(120) NOT NULL,
  -- This column holds the county name for the location.
  county VARCHAR2(250),
  -- This column contains the state abbreviation for the address.
  state VARCHAR2(3),
  -- This column contains the name of the state.
  state_desc VARCHAR2(120),
  -- Contains the ISO 3166-1 country code associated with the address.  This information is required.
  country VARCHAR2(3) NOT NULL,
  -- This column contains the name of the country.
  country_desc VARCHAR2(120),
  -- This column contains the zip code for the address.
  post_code VARCHAR2(30),
  -- Identifies the jurisdiction code for the country-state relationship.
  jurisdiction_code VARCHAR2(10),
  -- This column contains the name of the contact for the supplier at this address.
  contact_name VARCHAR2(120),
  -- This column contains the phone number of the contact person at this address.
  contact_phone VARCHAR2(20),
  -- This column contains the telex number of the partner or suppliers representative contact.
  contact_telex VARCHAR2(20),
  -- This column contains the fax number of the contact person at this address.
  contact_fax VARCHAR2(20),
  -- This column contains the email address of the partner or suppliers representative contact.
  contact_email VARCHAR2(100)
);

COMMENT ON TABLE FINISHER_ADDR_OUT IS 'This table is used to integrate finisher address information.';
COMMENT ON COLUMN FINISHER_ADDR_OUT.bdi_seq_id IS 'bdi internal column';
COMMENT ON COLUMN FINISHER_ADDR_OUT.bdi_app_name IS 'bdi internal column';
COMMENT ON COLUMN FINISHER_ADDR_OUT.bdi_dataset_type IS 'bdi internal column';
COMMENT ON COLUMN FINISHER_ADDR_OUT.bdi_dataset_action IS 'bdi internal column';
COMMENT ON COLUMN FINISHER_ADDR_OUT.module IS 'Contains the code for the module that the address is attached to. Valid values are WH (Warehouse) and PTNR (Partner).';
COMMENT ON COLUMN FINISHER_ADDR_OUT.finisher_id IS 'Contains the unique ID of the Finisher.  Data will always be present in this field.';
COMMENT ON COLUMN FINISHER_ADDR_OUT.seq_no IS 'This indicates the sequence that addresses within the same type were entered.';
COMMENT ON COLUMN FINISHER_ADDR_OUT.addr IS 'This column contains a unique number used to distinguish between different addresses.';
COMMENT ON COLUMN FINISHER_ADDR_OUT.addr_type IS 'Contains the code used to identify the address type.  Common examples include 01 (Business), 02 (Postal), 03 (Returns), 04 (Order), 05 (Invoice) and 06 (Remittance).  Data will always be present in this field.';
COMMENT ON COLUMN FINISHER_ADDR_OUT.addr_type_desc IS 'Contains the code used to identify the address type.  Common Common examples include Business, Postal, Returns, Order, Invoice and Remittance.  Description data is only sent in the primary integration language of the system.';
COMMENT ON COLUMN FINISHER_ADDR_OUT.primary_addr_type_ind IS 'Indicates whether the address type is the primary address type for the module. Valid values are Y and N.';
COMMENT ON COLUMN FINISHER_ADDR_OUT.primary_addr_ind IS 'Indicates whether the address is the primary address for the address type.  Valid values are Y and N.';
COMMENT ON COLUMN FINISHER_ADDR_OUT.add_1 IS 'Contains the first line of the address.  This information is required.';
COMMENT ON COLUMN FINISHER_ADDR_OUT.add_2 IS 'This column contains the second line of the address.';
COMMENT ON COLUMN FINISHER_ADDR_OUT.add_3 IS 'This column contains the third line of the address.';
COMMENT ON COLUMN FINISHER_ADDR_OUT.city IS 'Contains the name of the city that is associated with the address.  This information is required.';
COMMENT ON COLUMN FINISHER_ADDR_OUT.county IS 'This column holds the county name for the location.';
COMMENT ON COLUMN FINISHER_ADDR_OUT.state IS 'This column contains the state abbreviation for the address.';
COMMENT ON COLUMN FINISHER_ADDR_OUT.state_desc IS 'This column contains the name of the state.';
COMMENT ON COLUMN FINISHER_ADDR_OUT.country IS 'Contains the ISO 3166-1 country code associated with the address.  This information is required.';
COMMENT ON COLUMN FINISHER_ADDR_OUT.country_desc IS 'This column contains the name of the country.';
COMMENT ON COLUMN FINISHER_ADDR_OUT.post_code IS 'This column contains the zip code for the address.';
COMMENT ON COLUMN FINISHER_ADDR_OUT.jurisdiction_code IS 'Identifies the jurisdiction code for the country-state relationship.';
COMMENT ON COLUMN FINISHER_ADDR_OUT.contact_name IS 'This column contains the name of the contact for the supplier at this address.';
COMMENT ON COLUMN FINISHER_ADDR_OUT.contact_phone IS 'This column contains the phone number of the contact person at this address.';
COMMENT ON COLUMN FINISHER_ADDR_OUT.contact_telex IS 'This column contains the telex number of the partner or suppliers representative contact.';
COMMENT ON COLUMN FINISHER_ADDR_OUT.contact_fax IS 'This column contains the fax number of the contact person at this address.';
COMMENT ON COLUMN FINISHER_ADDR_OUT.contact_email IS 'This column contains the email address of the partner or suppliers representative contact.';


-- Add BDI primary key constraint

ALTER TABLE FINISHER_ADDR_OUT ADD CONSTRAINT pk_finisher_addr_out PRIMARY KEY (bdi_app_name, bdi_seq_id);


-- Add check constraint for bdi_dataset_type column

ALTER TABLE FINISHER_ADDR_OUT ADD CONSTRAINT chk_type_finisher_addr_out CHECK (bdi_dataset_type IN ('FULL', 'PARTIAL'));


-- Add check constraint for bdi_dataset_action column

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