-- Interface Module: PartOrgUnit_Fnd
-- Interface: Partner_Org_Unit_Out

-- Create sequence for bdi_seq_id column

CREATE SEQUENCE partner_org_unit_out_seq ORDER CACHE 20 MAXVALUE 9999999999999999999;


-- Create BDI Outbound Table: PARTNER_ORG_UNIT_OUT
-- This table is used to integrate Partner Org Unit information.

CREATE TABLE PARTNER_ORG_UNIT_OUT (
  -- bdi internal column
  bdi_seq_id NUMBER DEFAULT partner_org_unit_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',
  -- This field contains either Suppler or Supplier Site.
  partner NUMBER(10,0) NOT NULL,
  -- This field contains org_unit_id.
  org_unit_id NUMBER(15,0) NOT NULL,
  -- Identifies the type of the partner. S for Supplier and U for Supplier Site.
  partner_type VARCHAR2(1) NOT NULL,
  -- Primary payment site indicator.
  primary_pay_site VARCHAR2(1)
);

COMMENT ON TABLE PARTNER_ORG_UNIT_OUT IS 'This table is used to integrate Partner Org Unit information.';
COMMENT ON COLUMN PARTNER_ORG_UNIT_OUT.bdi_seq_id IS 'bdi internal column';
COMMENT ON COLUMN PARTNER_ORG_UNIT_OUT.bdi_app_name IS 'bdi internal column';
COMMENT ON COLUMN PARTNER_ORG_UNIT_OUT.bdi_dataset_type IS 'bdi internal column';
COMMENT ON COLUMN PARTNER_ORG_UNIT_OUT.bdi_dataset_action IS 'bdi internal column';
COMMENT ON COLUMN PARTNER_ORG_UNIT_OUT.partner IS 'This field contains either Suppler or Supplier Site.';
COMMENT ON COLUMN PARTNER_ORG_UNIT_OUT.org_unit_id IS 'This field contains org_unit_id.';
COMMENT ON COLUMN PARTNER_ORG_UNIT_OUT.partner_type IS 'Identifies the type of the partner. S for Supplier and U for Supplier Site.';
COMMENT ON COLUMN PARTNER_ORG_UNIT_OUT.primary_pay_site IS 'Primary payment site indicator.';


-- Add BDI primary key constraint

ALTER TABLE PARTNER_ORG_UNIT_OUT ADD CONSTRAINT pk_partner_org_unit_out PRIMARY KEY (bdi_app_name, bdi_seq_id);


-- Add check constraint for bdi_dataset_type column

ALTER TABLE PARTNER_ORG_UNIT_OUT ADD CONSTRAINT chk_type_partner_org_unit_out CHECK (bdi_dataset_type IN ('FULL', 'PARTIAL'));


-- Add check constraint for bdi_dataset_action column

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