-- Interface Module: ExtPurchOrder_Tx
-- Interface: Ext_Purch_Order_Out

-- Create sequence for bdi_seq_id column

CREATE SEQUENCE ext_purch_order_out_seq ORDER CACHE 20 MAXVALUE 9999999999999999999;


-- Create BDI Outbound Table: EXT_PURCH_ORDER_OUT
-- This table is used to integrate Purchase Order information.

CREATE TABLE EXT_PURCH_ORDER_OUT (
  -- bdi internal column
  bdi_seq_id NUMBER DEFAULT ext_purch_order_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',
  -- the unique number identifying the order.
  ext_po_id NUMBER(12),
  -- the unique identifier of the  location for which the inventory will be delivered to for the order.
  po_store NUMBER(10),
  -- the supplier ID of the order or where the inventory will be coming from.
  po_supplier VARCHAR2(10),
  -- the status of the purchase order. valid values: 1 - Approved, 2 - In Progress, 3 - Canceled, 4 - Completed, 9999 - Active
  po_status NUMBER(4),
  -- the external identifier for the purchase order.
  po_ext_id VARCHAR2(128),
  -- the earliest date that the inventory would arrive at the store for the order.
  po_not_before_date TIMESTAMP,
  -- the latest date that the inventory would arrive at the store for the order.
  po_not_after_date TIMESTAMP,
  -- the date when the order was created.
  po_create_date TIMESTAMP,
  -- the date when the order was updated.
  po_update_date TIMESTAMP,
  -- the date of when all items were received on the order.
  po_complete_date TIMESTAMP,
  -- the unique identifier of the user who orginated the purchase order.
  po_user VARCHAR2(128),
  -- the description and/or comments associated with the purchase order as defined by the user.
  po_comments VARCHAR2(2000),
  -- the customer order external identifier
  po_cust_ord_id VARCHAR2(128),
  -- the fulfillment order external identifier
  po_ful_ord_ext_id VARCHAR2(128),
  -- the external status of the purchase order. valid values: 1 - Worksheet, 2 - Submitted, 3 - Approved, 4 - Closed
  po_ext_status NUMBER(4),
  -- the unique number identifying the order line item.
  ext_line_id NUMBER(12),
  -- the unique identifier of the item that is on the purchase order.
  po_line_item VARCHAR2(25),
  -- item supplier origin country id.
  po_line_supp_country VARCHAR2(3),
  -- the size of the pack coming from the supplier.
  po_line_case_size NUMBER(10),
  -- the number of single units expected to be delivered to the store.
  po_line_expected NUMBER(20),
  -- the number of single units received by sim to the current date.
  po_line_received NUMBER(20),
  -- the unit cost currency code for the line item.
  po_line_unit_cost_currency VARCHAR2(3),
  -- the unit cost value for the line item.
  po_line_unit_cost_value NUMBER(12),
  -- Preferred unit of measure of the line item.
  po_line_preferred_uom VARCHAR2(4),
  -- Y if published to or from an external system, N if never been published.
  po_line_published VARCHAR2(1)
);

COMMENT ON TABLE EXT_PURCH_ORDER_OUT IS 'This table is used to integrate Purchase Order information.';
COMMENT ON COLUMN EXT_PURCH_ORDER_OUT.bdi_seq_id IS 'bdi internal column';
COMMENT ON COLUMN EXT_PURCH_ORDER_OUT.bdi_app_name IS 'bdi internal column';
COMMENT ON COLUMN EXT_PURCH_ORDER_OUT.bdi_dataset_type IS 'bdi internal column';
COMMENT ON COLUMN EXT_PURCH_ORDER_OUT.bdi_dataset_action IS 'bdi internal column';
COMMENT ON COLUMN EXT_PURCH_ORDER_OUT.ext_po_id IS 'the unique number identifying the order.';
COMMENT ON COLUMN EXT_PURCH_ORDER_OUT.po_store IS 'the unique identifier of the  location for which the inventory will be delivered to for the order.';
COMMENT ON COLUMN EXT_PURCH_ORDER_OUT.po_supplier IS 'the supplier ID of the order or where the inventory will be coming from.';
COMMENT ON COLUMN EXT_PURCH_ORDER_OUT.po_status IS 'the status of the purchase order. valid values: 1 - Approved, 2 - In Progress, 3 - Canceled, 4 - Completed, 9999 - Active';
COMMENT ON COLUMN EXT_PURCH_ORDER_OUT.po_ext_id IS 'the external identifier for the purchase order.';
COMMENT ON COLUMN EXT_PURCH_ORDER_OUT.po_not_before_date IS 'the earliest date that the inventory would arrive at the store for the order.';
COMMENT ON COLUMN EXT_PURCH_ORDER_OUT.po_not_after_date IS 'the latest date that the inventory would arrive at the store for the order.';
COMMENT ON COLUMN EXT_PURCH_ORDER_OUT.po_create_date IS 'the date when the order was created.';
COMMENT ON COLUMN EXT_PURCH_ORDER_OUT.po_update_date IS 'the date when the order was updated.';
COMMENT ON COLUMN EXT_PURCH_ORDER_OUT.po_complete_date IS 'the date of when all items were received on the order.';
COMMENT ON COLUMN EXT_PURCH_ORDER_OUT.po_user IS 'the unique identifier of the user who orginated the purchase order.';
COMMENT ON COLUMN EXT_PURCH_ORDER_OUT.po_comments IS 'the description and/or comments associated with the purchase order as defined by the user.';
COMMENT ON COLUMN EXT_PURCH_ORDER_OUT.po_cust_ord_id IS 'the customer order external identifier';
COMMENT ON COLUMN EXT_PURCH_ORDER_OUT.po_ful_ord_ext_id IS 'the fulfillment order external identifier';
COMMENT ON COLUMN EXT_PURCH_ORDER_OUT.po_ext_status IS 'the external status of the purchase order. valid values: 1 - Worksheet, 2 - Submitted, 3 - Approved, 4 - Closed';
COMMENT ON COLUMN EXT_PURCH_ORDER_OUT.ext_line_id IS 'the unique number identifying the order line item.';
COMMENT ON COLUMN EXT_PURCH_ORDER_OUT.po_line_item IS 'the unique identifier of the item that is on the purchase order.';
COMMENT ON COLUMN EXT_PURCH_ORDER_OUT.po_line_supp_country IS 'item supplier origin country id.';
COMMENT ON COLUMN EXT_PURCH_ORDER_OUT.po_line_case_size IS 'the size of the pack coming from the supplier.';
COMMENT ON COLUMN EXT_PURCH_ORDER_OUT.po_line_expected IS 'the number of single units expected to be delivered to the store.';
COMMENT ON COLUMN EXT_PURCH_ORDER_OUT.po_line_received IS 'the number of single units received by sim to the current date.';
COMMENT ON COLUMN EXT_PURCH_ORDER_OUT.po_line_unit_cost_currency IS 'the unit cost currency code for the line item.';
COMMENT ON COLUMN EXT_PURCH_ORDER_OUT.po_line_unit_cost_value IS 'the unit cost value for the line item.';
COMMENT ON COLUMN EXT_PURCH_ORDER_OUT.po_line_preferred_uom IS 'Preferred unit of measure of the line item.';
COMMENT ON COLUMN EXT_PURCH_ORDER_OUT.po_line_published IS 'Y if published to or from an external system, N if never been published.';


-- Add BDI primary key constraint

ALTER TABLE EXT_PURCH_ORDER_OUT ADD CONSTRAINT pk_ext_purch_order_out PRIMARY KEY (bdi_app_name, bdi_seq_id);


-- Add check constraint for bdi_dataset_type column

ALTER TABLE EXT_PURCH_ORDER_OUT ADD CONSTRAINT chk_type_ext_purch_order_out CHECK (bdi_dataset_type IN ('FULL', 'PARTIAL'));


-- Add check constraint for bdi_dataset_action column

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