-- Interface Module: ExtDsd_Tx
-- Interface: Ext_Dsd_Out

-- Create sequence for bdi_seq_id column

CREATE SEQUENCE ext_dsd_out_seq ORDER CACHE 20 MAXVALUE 9999999999999999999;


-- Create BDI Outbound Table: EXT_DSD_OUT
-- This table is used to integrate DSD information.

CREATE TABLE EXT_DSD_OUT (
  -- bdi internal column
  bdi_seq_id NUMBER DEFAULT ext_dsd_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 identifier of the direct delivery record.
  ext_dsd_id NUMBER(12),
  -- the unique identifier of the store that this direct delivery is for.
  dsd_store NUMBER(10),
  -- the unique identifier of the supplier that shipped the items.
  dsd_supplier NUMBER(10),
  -- The current status of the direct delivery. The valid values: 0 - New, 1 - In Progress, 2 - Received, 3 - Canceled, 4 : Rejected, 9999 - Active.
  dsd_status NUMBER(4),
  -- the unique identifier of the purchase order that this direct delivery is associated to.
  ext_dsd_po_id NUMBER(12),
  -- the advanced shipping notification identifier that is associated with the direct delivery.
  dsd_asn VARCHAR2(128),
  -- The expected date of the delivery.
  dsd_expected_date TIMESTAMP,
  -- the date the direct delivery record was created.
  dsd_create_date TIMESTAMP,
  -- the date the direct delivery was last updated.
  dsd_update_date TIMESTAMP,
  -- the received date of the Delivery.
  dsd_received_date TIMESTAMP,
  -- the unique identifier of the invoice that this direct delivery is associated to, as entered by the user.
  dsd_invoice VARCHAR2(128),
  -- the date of the invoice as entered by the user.
  dsd_invoice_date TIMESTAMP,
  -- the user who created the Delivery.
  dsd_create_user VARCHAR2(128),
  -- the customer order identifier.
  dsd_cust_ord_id VARCHAR2(128),
  -- the fulfillment order external identifier.
  dsd_ful_ord_ext_id VARCHAR2(128),
  -- Indicate DSD Origin type, 0 - ASN, 1 - PO, 2 - DEX-NEX, 3 - on the fly
  dsd_origin_type NUMBER(2),
  -- the carrier name.
  dsd_carrier_entity VARCHAR2(128),
  -- Indicate carrier type, 0 - Corporate, 1 - Third Party
  dsd_carrier_type NUMBER(2),
  -- Unique code identifier for a carrier
  dsd_carrier_code VARCHAR2(4),
  -- the Country code
  dsd_country_code VARCHAR2(3),
  -- the address of source sending delivery.
  dsd_source_address VARCHAR2(1000),
  -- the license plate.
  dsd_license_plate VARCHAR2(128),
  -- the freight identifier.
  dsd_freight_id VARCHAR2(128),
  -- the currency code.
  dsd_currency_code VARCHAR2(3),
  -- the invoice cost value.
  dsd_invoice_cost NUMBER(12),
  -- the user who received the delivery.
  dsd_received_user VARCHAR2(128),
  -- the user who last updated the delivery.
  dsd_update_user VARCHAR2(128),
  -- delivery Bill Of Lading from external system or entered by SIM user.
  dsd_bol_ext_id VARCHAR2(128),
  -- the purchase order external identifier.
  dsd_adj_po_ext_id VARCHAR2(128),
  -- the receiver unit adjustment item identifier.
  dsd_adj_item VARCHAR2(25),
  -- the receiver unit adjustment date.
  dsd_adj_date TIMESTAMP,
  -- the receiver unit adjustment quantity.
  dsd_adj_quantity NUMBER(20),
  -- the unique identifier of the container.
  ext_dsd_carton_id NUMBER(12),
  -- the unique external identifier of the container.
  dsd_carton_ext_id VARCHAR2(128),
  -- container status. valid values- 1 - New, 2 - In Progress, 3 - Submitted, 4 - Received, 5 - Damaged, 6 - Missing, 7 - Canceled
  dsd_carton_status NUMBER(4),
  -- The unique store identifier to which container is associated to.
  dsd_carton_store NUMBER(10),
  -- the reference identifier.
  dsd_carton_reference_id VARCHAR2(128),
  -- the reason for container damage.
  dsd_carton_damaged_reason VARCHAR2(128),
  -- the serial code.
  dsd_carton_serial_code NUMBER(18),
  -- the Tracking number for the container.
  dsd_carton_tracking_number VARCHAR2(128),
  -- Indicates all remaining quantities should be damaged on final receipt.
  dsd_carton_damage_remaining VARCHAR2(1),
  -- the user who created the direct delivery container.
  dsd_carton_create_user VARCHAR2(128),
  -- the user who last updated the direct delivery container.
  dsd_carton_update_user VARCHAR2(128),
  -- the user who received the direct delivery container.
  dsd_carton_receive_user VARCHAR2(128),
  -- the date when the direct delivery container was created.
  dsd_carton_create_date TIMESTAMP,
  -- the date when the direct delivery container was updated.
  dsd_carton_update_date TIMESTAMP,
  -- the date when the direct delivery container was received.
  dsd_carton_receive_date TIMESTAMP,
  -- Indicates whether the container has uin items. Valid values: Y or N.
  dsd_carton_uin_required VARCHAR2(1),
  -- Indicates whether the inventory on container is to be received at shop floor. Valid values: Y or N.
  dsd_carton_rcv_at_shop_floor VARCHAR2(1),
  -- Used to flag containers that require detailed receiving.
  dsd_carton_quality_control VARCHAR2(1),
  -- Indicates whether the delivery is external created. Valid values: Y or N
  dsd_carton_external_create VARCHAR2(1),
  -- Indicates whether the delivery is adjusted. Valid values: Y or N
  dsd_carton_adjusted VARCHAR2(1),
  -- Indicates if container contains customer order items. 1 - Yes, 2 - Mix, 3 - No
  dsd_carton_cust_ord_related NUMBER(4),
  -- the unique external identifier of the line item.
  ext_dsd_line_id NUMBER(12),
  -- the unique identifier of the item that is contained in this container.
  dsd_line_item VARCHAR2(25),
  -- the number of units in the case that this item was shipped in.
  dsd_line_case_size NUMBER(10),
  -- the total number of units expected in this direct delivery.
  dsd_line_expected NUMBER(20),
  -- the total number of units received in this direct delivery.
  dsd_line_received NUMBER(20),
  -- the total number of units that were damaged when the direct delivery was received.
  dsd_line_damaged NUMBER(20),
  -- the unit cost currency code for the line item.
  dsd_line_unit_cost_curr VARCHAR2(3),
  -- the unit cost value for the line item.
  dsd_line_unit_cost_val NUMBER(12),
  -- the override unit cost currency.
  dsd_line_ovrd_unit_cost_curr VARCHAR2(3),
  -- the override unit cost value.
  dsd_line_ovrd_unit_cost_val NUMBER(12),
  -- the store identifier.
  dsd_line_store NUMBER(10),
  -- the purchase order identifier.
  dsd_line_po_id NUMBER(12),
  -- the purchase order external identifier.
  dsd_line_po_ext_id VARCHAR2(128),
  -- the customer order identifier.
  dsd_line_cust_ord_id VARCHAR2(128),
  -- the fulfillment order external identifier.
  dsd_line_ful_ord_ext_id VARCHAR2(128),
  -- the vendor product number.
  dsd_line_vpn VARCHAR2(256),
  -- Amount of received inventory over the expected quantities.
  dsd_line_received_over NUMBER(20),
  -- Amount of damaged inventory over the expected quantities.
  dsd_line_damaged_over NUMBER(20),
  -- Units previous received when container is reopened for adjustment.
  dsd_line_prev_received NUMBER(20),
  -- Units previous received as damaged when container is reopened for adjustment.
  dsd_line_prev_damaged NUMBER(20)
);

COMMENT ON TABLE EXT_DSD_OUT IS 'This table is used to integrate DSD information.';
COMMENT ON COLUMN EXT_DSD_OUT.bdi_seq_id IS 'bdi internal column';
COMMENT ON COLUMN EXT_DSD_OUT.bdi_app_name IS 'bdi internal column';
COMMENT ON COLUMN EXT_DSD_OUT.bdi_dataset_type IS 'bdi internal column';
COMMENT ON COLUMN EXT_DSD_OUT.bdi_dataset_action IS 'bdi internal column';
COMMENT ON COLUMN EXT_DSD_OUT.ext_dsd_id IS 'the unique identifier of the direct delivery record.';
COMMENT ON COLUMN EXT_DSD_OUT.dsd_store IS 'the unique identifier of the store that this direct delivery is for.';
COMMENT ON COLUMN EXT_DSD_OUT.dsd_supplier IS 'the unique identifier of the supplier that shipped the items.';
COMMENT ON COLUMN EXT_DSD_OUT.dsd_status IS 'The current status of the direct delivery. The valid values: 0 - New, 1 - In Progress, 2 - Received, 3 - Canceled, 4 : Rejected, 9999 - Active.';
COMMENT ON COLUMN EXT_DSD_OUT.ext_dsd_po_id IS 'the unique identifier of the purchase order that this direct delivery is associated to.';
COMMENT ON COLUMN EXT_DSD_OUT.dsd_asn IS 'the advanced shipping notification identifier that is associated with the direct delivery.';
COMMENT ON COLUMN EXT_DSD_OUT.dsd_expected_date IS 'The expected date of the delivery.';
COMMENT ON COLUMN EXT_DSD_OUT.dsd_create_date IS 'the date the direct delivery record was created.';
COMMENT ON COLUMN EXT_DSD_OUT.dsd_update_date IS 'the date the direct delivery was last updated.';
COMMENT ON COLUMN EXT_DSD_OUT.dsd_received_date IS 'the received date of the Delivery.';
COMMENT ON COLUMN EXT_DSD_OUT.dsd_invoice IS 'the unique identifier of the invoice that this direct delivery is associated to, as entered by the user.';
COMMENT ON COLUMN EXT_DSD_OUT.dsd_invoice_date IS 'the date of the invoice as entered by the user.';
COMMENT ON COLUMN EXT_DSD_OUT.dsd_create_user IS 'the user who created the Delivery.';
COMMENT ON COLUMN EXT_DSD_OUT.dsd_cust_ord_id IS 'the customer order identifier.';
COMMENT ON COLUMN EXT_DSD_OUT.dsd_ful_ord_ext_id IS 'the fulfillment order external identifier.';
COMMENT ON COLUMN EXT_DSD_OUT.dsd_origin_type IS 'Indicate DSD Origin type, 0 - ASN, 1 - PO, 2 - DEX-NEX, 3 - on the fly';
COMMENT ON COLUMN EXT_DSD_OUT.dsd_carrier_entity IS 'the carrier name.';
COMMENT ON COLUMN EXT_DSD_OUT.dsd_carrier_type IS 'Indicate carrier type, 0 - Corporate, 1 - Third Party';
COMMENT ON COLUMN EXT_DSD_OUT.dsd_carrier_code IS 'Unique code identifier for a carrier';
COMMENT ON COLUMN EXT_DSD_OUT.dsd_country_code IS 'the Country code';
COMMENT ON COLUMN EXT_DSD_OUT.dsd_source_address IS 'the address of source sending delivery.';
COMMENT ON COLUMN EXT_DSD_OUT.dsd_license_plate IS 'the license plate.';
COMMENT ON COLUMN EXT_DSD_OUT.dsd_freight_id IS 'the freight identifier.';
COMMENT ON COLUMN EXT_DSD_OUT.dsd_currency_code IS 'the currency code.';
COMMENT ON COLUMN EXT_DSD_OUT.dsd_invoice_cost IS 'the invoice cost value.';
COMMENT ON COLUMN EXT_DSD_OUT.dsd_received_user IS 'the user who received the delivery.';
COMMENT ON COLUMN EXT_DSD_OUT.dsd_update_user IS 'the user who last updated the delivery.';
COMMENT ON COLUMN EXT_DSD_OUT.dsd_bol_ext_id IS 'delivery Bill Of Lading from external system or entered by SIM user.';
COMMENT ON COLUMN EXT_DSD_OUT.dsd_adj_po_ext_id IS 'the purchase order external identifier.';
COMMENT ON COLUMN EXT_DSD_OUT.dsd_adj_item IS 'the receiver unit adjustment item identifier.';
COMMENT ON COLUMN EXT_DSD_OUT.dsd_adj_date IS 'the receiver unit adjustment date.';
COMMENT ON COLUMN EXT_DSD_OUT.dsd_adj_quantity IS 'the receiver unit adjustment quantity.';
COMMENT ON COLUMN EXT_DSD_OUT.ext_dsd_carton_id IS 'the unique identifier of the container.';
COMMENT ON COLUMN EXT_DSD_OUT.dsd_carton_ext_id IS 'the unique external identifier of the container.';
COMMENT ON COLUMN EXT_DSD_OUT.dsd_carton_status IS 'container status. valid values- 1 - New, 2 - In Progress, 3 - Submitted, 4 - Received, 5 - Damaged, 6 - Missing, 7 - Canceled';
COMMENT ON COLUMN EXT_DSD_OUT.dsd_carton_store IS 'The unique store identifier to which container is associated to.';
COMMENT ON COLUMN EXT_DSD_OUT.dsd_carton_reference_id IS 'the reference identifier.';
COMMENT ON COLUMN EXT_DSD_OUT.dsd_carton_damaged_reason IS 'the reason for container damage.';
COMMENT ON COLUMN EXT_DSD_OUT.dsd_carton_serial_code IS 'the serial code.';
COMMENT ON COLUMN EXT_DSD_OUT.dsd_carton_tracking_number IS 'the Tracking number for the container.';
COMMENT ON COLUMN EXT_DSD_OUT.dsd_carton_damage_remaining IS 'Indicates all remaining quantities should be damaged on final receipt.';
COMMENT ON COLUMN EXT_DSD_OUT.dsd_carton_create_user IS 'the user who created the direct delivery container.';
COMMENT ON COLUMN EXT_DSD_OUT.dsd_carton_update_user IS 'the user who last updated the direct delivery container.';
COMMENT ON COLUMN EXT_DSD_OUT.dsd_carton_receive_user IS 'the user who received the direct delivery container.';
COMMENT ON COLUMN EXT_DSD_OUT.dsd_carton_create_date IS 'the date when the direct delivery container was created.';
COMMENT ON COLUMN EXT_DSD_OUT.dsd_carton_update_date IS 'the date when the direct delivery container was updated.';
COMMENT ON COLUMN EXT_DSD_OUT.dsd_carton_receive_date IS 'the date when the direct delivery container was received.';
COMMENT ON COLUMN EXT_DSD_OUT.dsd_carton_uin_required IS 'Indicates whether the container has uin items. Valid values: Y or N.';
COMMENT ON COLUMN EXT_DSD_OUT.dsd_carton_rcv_at_shop_floor IS 'Indicates whether the inventory on container is to be received at shop floor. Valid values: Y or N.';
COMMENT ON COLUMN EXT_DSD_OUT.dsd_carton_quality_control IS 'Used to flag containers that require detailed receiving.';
COMMENT ON COLUMN EXT_DSD_OUT.dsd_carton_external_create IS 'Indicates whether the delivery is external created. Valid values: Y or N';
COMMENT ON COLUMN EXT_DSD_OUT.dsd_carton_adjusted IS 'Indicates whether the delivery is adjusted. Valid values: Y or N';
COMMENT ON COLUMN EXT_DSD_OUT.dsd_carton_cust_ord_related IS 'Indicates if container contains customer order items. 1 - Yes, 2 - Mix, 3 - No';
COMMENT ON COLUMN EXT_DSD_OUT.ext_dsd_line_id IS 'the unique external identifier of the line item.';
COMMENT ON COLUMN EXT_DSD_OUT.dsd_line_item IS 'the unique identifier of the item that is contained in this container.';
COMMENT ON COLUMN EXT_DSD_OUT.dsd_line_case_size IS 'the number of units in the case that this item was shipped in.';
COMMENT ON COLUMN EXT_DSD_OUT.dsd_line_expected IS 'the total number of units expected in this direct delivery.';
COMMENT ON COLUMN EXT_DSD_OUT.dsd_line_received IS 'the total number of units received in this direct delivery.';
COMMENT ON COLUMN EXT_DSD_OUT.dsd_line_damaged IS 'the total number of units that were damaged when the direct delivery was received.';
COMMENT ON COLUMN EXT_DSD_OUT.dsd_line_unit_cost_curr IS 'the unit cost currency code for the line item.';
COMMENT ON COLUMN EXT_DSD_OUT.dsd_line_unit_cost_val IS 'the unit cost value for the line item.';
COMMENT ON COLUMN EXT_DSD_OUT.dsd_line_ovrd_unit_cost_curr IS 'the override unit cost currency.';
COMMENT ON COLUMN EXT_DSD_OUT.dsd_line_ovrd_unit_cost_val IS 'the override unit cost value.';
COMMENT ON COLUMN EXT_DSD_OUT.dsd_line_store IS 'the store identifier.';
COMMENT ON COLUMN EXT_DSD_OUT.dsd_line_po_id IS 'the purchase order identifier.';
COMMENT ON COLUMN EXT_DSD_OUT.dsd_line_po_ext_id IS 'the purchase order external identifier.';
COMMENT ON COLUMN EXT_DSD_OUT.dsd_line_cust_ord_id IS 'the customer order identifier.';
COMMENT ON COLUMN EXT_DSD_OUT.dsd_line_ful_ord_ext_id IS 'the fulfillment order external identifier.';
COMMENT ON COLUMN EXT_DSD_OUT.dsd_line_vpn IS 'the vendor product number.';
COMMENT ON COLUMN EXT_DSD_OUT.dsd_line_received_over IS 'Amount of received inventory over the expected quantities.';
COMMENT ON COLUMN EXT_DSD_OUT.dsd_line_damaged_over IS 'Amount of damaged inventory over the expected quantities.';
COMMENT ON COLUMN EXT_DSD_OUT.dsd_line_prev_received IS 'Units previous received when container is reopened for adjustment.';
COMMENT ON COLUMN EXT_DSD_OUT.dsd_line_prev_damaged IS 'Units previous received as damaged when container is reopened for adjustment.';


-- Add BDI primary key constraint

ALTER TABLE EXT_DSD_OUT ADD CONSTRAINT pk_ext_dsd_out PRIMARY KEY (bdi_app_name, bdi_seq_id);


-- Add check constraint for bdi_dataset_type column

ALTER TABLE EXT_DSD_OUT ADD CONSTRAINT chk_type_ext_dsd_out CHECK (bdi_dataset_type IN ('FULL', 'PARTIAL'));


-- Add check constraint for bdi_dataset_action column

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