-- ABS-29800

---ERP_I_PURCHASEORDERHEADER table---
ALTER TABLE ERP_I_PURCHASEORDERHEADER ADD (PRIORITYCODE VARCHAR2(12) DEFAULT ' ' NOT NULL,
                                           PRIORITYDESCRIPTION VARCHAR2(40) DEFAULT ' ' NOT NULL,
                                           PRIORITYPURCHASECATEGORY NUMBER DEFAULT 0 NOT NULL,
                                           SHIPTOSTOCKROOMCODE VARCHAR2(12) NULL,
                                           SHIPTOCUSTOMERNUMBER NUMBER NULL,
                                           SHIPTOCUSTOMERLEGALNAME VARCHAR2(80) NULL,
                                           LEGALENTITYCODE VARCHAR2(10) NULL,
                                           LEGALENTITYLEGALNAME VARCHAR2(80) NULL,
                                           INTERNALREMARK VARCHAR2(1000) NULL
                                           );

COMMENT ON COLUMN ERP_I_PURCHASEORDERHEADER.PRIORITYCODE
  IS 'ORDERPRIORITY.PRIORITYCODE';
COMMENT ON COLUMN ERP_I_PURCHASEORDERHEADER.PRIORITYDESCRIPTION
  IS 'ORDERPRIORITY_DESC.DESCRIPTION';
COMMENT ON COLUMN ERP_I_PURCHASEORDERHEADER.PRIORITYPURCHASECATEGORY
  IS 'ORDERPRIORITY.PURCHASECATEGORY';
COMMENT ON COLUMN ERP_I_PURCHASEORDERHEADER.SHIPTOSTOCKROOMCODE
  IS 'SHIPTOSTOCKROOM.STOCKROOM.CODE';
COMMENT ON COLUMN ERP_I_PURCHASEORDERHEADER.SHIPTOCUSTOMERNUMBER
  IS 'SHIPTOCUSTOMER.CUSTOMERNUMBER VIA DELIVERYPOINT_LINK_ID';
COMMENT ON COLUMN ERP_I_PURCHASEORDERHEADER.SHIPTOCUSTOMERLEGALNAME
  IS 'SHIPTOCUSTOMER.LEGALNAME VIA DELIVERYPOINT_LINK_ID';
COMMENT ON COLUMN ERP_I_PURCHASEORDERHEADER.LEGALENTITYCODE
  IS 'LEGALENTITY.CODE';
COMMENT ON COLUMN ERP_I_PURCHASEORDERHEADER.LEGALENTITYLEGALNAME
  IS 'LEGALENTITY.LEGALNAME';
  
/*Code uniquely by supplier is already there it is ERP_I_PURCHASEORDERLINE.UNIQUEITEM*/
--ERP_I_PURCHASEORDERLINE table
ALTER TABLE ERP_I_PURCHASEORDERLINE ADD (PREWASHBYSUPPLIER NUMBER DEFAULT 0 NOT NULL,
                                         MODIFICATIONCODE1 VARCHAR2(10) NULL,
                                         MODIFICATIONDESCRIPTION1 VARCHAR2(40) NULL,
                                         MODIFICATIONCODE2 VARCHAR2(10) NULL,
                                         MODIFICATIONDESCRIPTION2 VARCHAR2(40) NULL,
                                         MODIFICATIONCODE3 VARCHAR2(10) NULL,
                                         MODIFICATIONDESCRIPTION3 VARCHAR2(40) NULL
                                         );

COMMENT ON COLUMN ERP_I_PURCHASEORDERLINE.PREWASHBYSUPPLIER
  is '0=No prewash needed, 1=Prewash needed';  
  
---ERP_I_PURCHASEORDERLINEDETAIL table---
CREATE TABLE ERP_I_PURCHASEORDERLINEDETAIL
(
  TRANSFER_ID              NUMBER NOT NULL,
  TRANSFER_STATUS          NUMBER DEFAULT 0 NOT NULL,
  EXT_PURCHASEORDERLINE_ID NUMBER NOT NULL,
  PURCHASEORDERNUMBER      NUMBER NOT NULL,
  CUSTOMERNUMBER           NUMBER NULL,
  CUSTOMEROWNED            VARCHAR2(1) DEFAULT 'N' NOT NULL,
  QUANTITYORDERED          NUMBER DEFAULT 0 NOT NULL,
  QUANTITYCANCELLED        NUMBER DEFAULT 0 NOT NULL,
  ORIGIN                   NUMBER DEFAULT 0 NOT NULL,
  PREWASH                  NUMBER DEFAULT 0 NOT NULL
);

ALTER TABLE ERP_I_PURCHASEORDERLINEDETAIL
  ADD CONSTRAINT PK_ERP_I_PURCHASEORDERLDETAIL UNIQUE (TRANSFER_ID, EXT_PURCHASEORDERLINE_ID, CUSTOMERNUMBER);
ALTER TABLE ERP_I_PURCHASEORDERLINEDETAIL
  ADD CONSTRAINT FK_ERP_I_PURCHASEORDERLDETAIL FOREIGN KEY (TRANSFER_ID, EXT_PURCHASEORDERLINE_ID)
  REFERENCES ERP_I_PURCHASEORDERLINE (TRANSFER_ID, EXT_PURCHASEORDERLINE_ID) ON DELETE CASCADE;

---ERP_O_PURCHASEORDERHEADER table---
ALTER TABLE ERP_O_PURCHASEORDERHEADER ADD (PRIORITYCODE VARCHAR2(12) DEFAULT ' ' NOT NULL,
                                           PRIORITYDESCRIPTION VARCHAR2(40) DEFAULT ' ' NOT NULL,
                                           PRIORITYPURCHASECATEGORY NUMBER DEFAULT 0 NOT NULL,
                                           SHIPTOSTOCKROOMCODE VARCHAR2(12) NULL,
                                           SHIPTOCUSTOMERNUMBER NUMBER NULL,
                                           SHIPTOCUSTOMERLEGALNAME VARCHAR2(80) NULL,
                                           LEGALENTITYCODE VARCHAR2(10) NULL,
                                           LEGALENTITYLEGALNAME VARCHAR2(80) NULL,
                                           INTERNALREMARK VARCHAR2(1000) NULL
                                           );

COMMENT ON COLUMN ERP_O_PURCHASEORDERHEADER.PRIORITYCODE
  IS 'ORDERPRIORITY.PRIORITYCODE';
COMMENT ON COLUMN ERP_O_PURCHASEORDERHEADER.PRIORITYDESCRIPTION
  IS 'ORDERPRIORITY_DESC.DESCRIPTION';
COMMENT ON COLUMN ERP_O_PURCHASEORDERHEADER.PRIORITYPURCHASECATEGORY
  IS 'ORDERPRIORITY.PURCHASECATEGORY';
COMMENT ON COLUMN ERP_O_PURCHASEORDERHEADER.SHIPTOSTOCKROOMCODE
  IS 'SHIPTOSTOCKROOM.STOCKROOM.CODE';
COMMENT ON COLUMN ERP_O_PURCHASEORDERHEADER.SHIPTOCUSTOMERNUMBER
  IS 'SHIPTOCUSTOMER.CUSTOMERNUMBER VIA DELIVERYPOINT_LINK_ID';
COMMENT ON COLUMN ERP_O_PURCHASEORDERHEADER.SHIPTOCUSTOMERLEGALNAME
  IS 'SHIPTOCUSTOMER.LEGALNAME VIA DELIVERYPOINT_LINK_ID';
COMMENT ON COLUMN ERP_O_PURCHASEORDERHEADER.LEGALENTITYCODE
  IS 'LEGALENTITY.CODE';
COMMENT ON COLUMN ERP_O_PURCHASEORDERHEADER.LEGALENTITYLEGALNAME
  IS 'LEGALENTITY.LEGALNAME';
  
/*Code uniquely by supplier is already there it is ERP_O_PURCHASEORDERLINE.UNIQUEITEM*/
---ERP_O_PURCHASEORDERLINE table---
ALTER TABLE ERP_O_PURCHASEORDERLINE ADD (PREWASHBYSUPPLIER NUMBER DEFAULT 0 NOT NULL,
                                         MODIFICATIONCODE1 VARCHAR2(10) NULL,
                                         MODIFICATIONDESCRIPTION1 VARCHAR2(40) NULL,
                                         MODIFICATIONCODE2 VARCHAR2(10) NULL,
                                         MODIFICATIONDESCRIPTION2 VARCHAR2(40) NULL,
                                         MODIFICATIONCODE3 VARCHAR2(10) NULL,
                                         MODIFICATIONDESCRIPTION3 VARCHAR2(40)
                                         );

COMMENT ON COLUMN ERP_O_PURCHASEORDERLINE.PREWASHBYSUPPLIER
  is '0=No prewash needed, 1=Prewash needed';  
  
---ERP_O_PURCHASEORDERLINEDETAIL table---
CREATE TABLE ERP_O_PURCHASEORDERLINEDETAIL
(
  TRANSFER_ID              NUMBER NOT NULL,
  TRANSFER_STATUS          NUMBER DEFAULT 0 NOT NULL,
  EXT_PURCHASEORDERLINE_ID NUMBER NOT NULL,
  PURCHASEORDERNUMBER      NUMBER NOT NULL,
  CUSTOMERNUMBER           NUMBER NULL,
  CUSTOMEROWNED            VARCHAR2(1) DEFAULT 'N' NOT NULL,
  QUANTITYORDERED          NUMBER DEFAULT 0 NOT NULL,
  QUANTITYCANCELLED        NUMBER DEFAULT 0 NOT NULL,
  ORIGIN                   NUMBER DEFAULT 0 NOT NULL,
  PREWASH                  NUMBER DEFAULT 0 NOT NULL
);