--- ABS-24734
-----------------------   PURCHASEORDERHEADER   -----------------------------------
ALTER TABLE PURCHASEORDERHEADER
DROP CONSTRAINT ORIGIN_VALID;

ALTER TABLE PURCHASEORDERHEADER ADD (SHIPTO NUMBER DEFAULT 0 NOT NULL
				     CONSTRAINT SHIPTO_VALID CHECK (SHIPTO IN (0, 1, 2, 3)),
				     SHIPTOSTOCKROOM_ID NUMBER NULL,
				     BILLTOCOMPANY NUMBER DEFAULT 0 NOT NULL
				     CONSTRAINT BILLTOCOMPANY_VALID CHECK (BILLTOCOMPANY IN (0, 1, 2)),
				     LEGALENTITY_ID NUMBER NULL,
				     APPROVALEMPLOYEE_ID NUMBER NULL,
				     CUSTOMEROWNED VARCHAR2(1) DEFAULT 'N' NOT NULL
				     CONSTRAINT YN_VALIDATIONFIELD284 CHECK (CUSTOMEROWNED IN ('Y','N')),
				     INTERNALREMARK VARCHAR2(1000) NULL);

COMMENT ON COLUMN PURCHASEORDERHEADER.ORIGIN IS '1 = Purchase request
2 = Manual created
3 = Conversion
4 = Work order approval
5 = Work order status control
6 = Purchase requisition';

COMMENT ON COLUMN PURCHASEORDERHEADER.ORDERPRIORITY_ID IS 'ID of the purchase order priority that identifies the priority of the current purchase order. Foreign key to OrderPriority table. Mandatory, so must be filled.
Will be filled by default with the purchase order priority that is marked as being the default for type PurchaseOrder. Will only be filled with a value <> default value’ when module POSPLIT or module ADVANCEPURCHASING is in the license';

COMMENT ON COLUMN PURCHASEORDERHEADER.SHIPTO IS '0 = Ordering Stockroom
1 = Other Stockroom
2 = Ordering customer
3 = Specific delivery address';

COMMENT ON COLUMN PURCHASEORDERHEADER.BILLTOCOMPANY IS '0 = Ordering Stockroom
1 = Ship-to Stockroom
2 = Other';

CREATE INDEX XIF13PURCHASEORDERHEADER ON PURCHASEORDERHEADER (DELIVERYPOINT_LINK_ID ASC);

CREATE INDEX XIF14PURCHASEORDERHEADER ON PURCHASEORDERHEADER (ORDERPRIORITY_ID ASC);

CREATE INDEX XIF15PURCHASEORDERHEADER ON PURCHASEORDERHEADER (SHIPTOSTOCKROOM_ID ASC);

CREATE INDEX XIF16PURCHASEORDERHEADER ON PURCHASEORDERHEADER (LEGALENTITY_ID ASC);

CREATE INDEX XIF17PURCHASEORDERHEADER ON PURCHASEORDERHEADER (APPROVALEMPLOYEE_ID ASC);

ALTER TABLE PURCHASEORDERHEADER ADD CONSTRAINT PURCHASEORDERHEADER_DELPOINT FOREIGN KEY (DELIVERYPOINT_LINK_ID)
REFERENCES DELIVERYPOINT_LINK (DELIVERYPOINT_LINK_ID);

ALTER TABLE PURCHASEORDERHEADER ADD CONSTRAINT PURCHASEORDERHEADER_SHIPST FOREIGN KEY (SHIPTOSTOCKROOM_ID)
REFERENCES STOCKROOM (STOCKROOM_ID);

ALTER TABLE PURCHASEORDERHEADER ADD CONSTRAINT PURCHASEORDERHEADER_LEGENT FOREIGN KEY (LEGALENTITY_ID)
REFERENCES LEGALENTITY (LEGALENTITY_ID);

ALTER TABLE PURCHASEORDERHEADER ADD CONSTRAINT PURCHASEORDERHEADER_APPEMP FOREIGN KEY (APPROVALEMPLOYEE_ID)
REFERENCES EMPLOYEE (EMPLOYEE_ID);

ALTER TABLE PURCHASEORDERHEADER ADD CONSTRAINT ORIGIN_VALID CHECK (ORIGIN IN (1, 2, 3, 4, 5, 6));

ALTER TABLE PURCHASEORDERHEADER ADD CONSTRAINT CUSTOW_CUSTID_CHECK CHECK
((CUSTOMEROWNED = 'Y' AND CUSTOMER_LINK_ID IS NOT NULL) OR CUSTOMEROWNED = 'N');

-----------------------   PURCHASEORDERADDRESSINFO   --------------------------------------
ALTER TABLE PURCHASEORDERADDRESSINFO ADD (COUNTRY_ID NUMBER NULL);

CREATE INDEX XIF1PURCHASEORDERADDRESSINFO ON PURCHASEORDERADDRESSINFO (COUNTRY_ID ASC);

ALTER TABLE PURCHASEORDERADDRESSINFO ADD CONSTRAINT PURCHASEORDERADDRESSINFO_COUNT FOREIGN KEY (COUNTRY_ID) 
REFERENCES COUNTRY (COUNTRY_ID);

-----------------------   PURCHASEORDERLINE   ---------------------------------------------
ALTER TABLE PURCHASEORDERLINE ADD (PREWASHBYSUPPLIER NUMBER DEFAULT 0 NOT NULL
				   CONSTRAINT PREWASHBYSUPPLIER_VALID CHECK (PREWASHBYSUPPLIER IN (0, 1)),
				   MODIFICATION1_ID NUMBER NULL,
				   MODIFICATION2_ID NUMBER NULL,
				   MODIFICATION3_ID NUMBER NULL,
				   AGGSUPPLIERPRODUCTSIZE_ID NUMBER NULL,
				   SUPPLIERORDERINGTERMS_ID NUMBER NULL,
				   SUPPLIERPRICETERMS_ID NUMBER NULL,
				   SUPPLIERDELIVERYDAYS NUMBER DEFAULT 1 NOT NULL);

COMMENT ON COLUMN PURCHASEORDERLINE.PREWASHBYSUPPLIER IS '0 - No prewash needed
1 - Prewash needed';

CREATE INDEX XIF8PURCHASEORDERLINE ON PURCHASEORDERLINE (MODIFICATION1_ID ASC);

CREATE INDEX XIF9PURCHASEORDERLINE ON PURCHASEORDERLINE (MODIFICATION2_ID ASC);

CREATE INDEX XIF10PURCHASEORDERLINE ON PURCHASEORDERLINE (MODIFICATION3_ID ASC);

CREATE INDEX XIF11PURCHASEORDERLINE ON PURCHASEORDERLINE (AGGSUPPLIERPRODUCTSIZE_ID ASC);

CREATE INDEX XIF12PURCHASEORDERLINE ON PURCHASEORDERLINE (SUPPLIERORDERINGTERMS_ID ASC);

CREATE INDEX XIF13PURCHASEORDERLINE ON PURCHASEORDERLINE (SUPPLIERPRICETERMS_ID ASC);

ALTER TABLE PURCHASEORDERLINE ADD CONSTRAINT PURCHASEORDERLINE_MOD1 FOREIGN KEY
(MODIFICATION1_ID) REFERENCES MODIFICATION (MODIFICATION_ID);

ALTER TABLE PURCHASEORDERLINE ADD CONSTRAINT PURCHASEORDERLINE_MOD2 FOREIGN KEY
(MODIFICATION2_ID) REFERENCES MODIFICATION (MODIFICATION_ID);

ALTER TABLE PURCHASEORDERLINE ADD CONSTRAINT PURCHASEORDERLINE_MOD3 FOREIGN KEY
(MODIFICATION3_ID) REFERENCES MODIFICATION (MODIFICATION_ID);

ALTER TABLE PURCHASEORDERLINE ADD CONSTRAINT PURCHASEORDERLINE_AGGSUP FOREIGN KEY
(AGGSUPPLIERPRODUCTSIZE_ID) REFERENCES AGGSUPPLIERPRODUCTSIZE (AGGSUPPLIERPRODUCTSIZE_ID)
ON DELETE SET NULL;

ALTER TABLE PURCHASEORDERLINE ADD CONSTRAINT PURCHASEORDERLINE_SUPORTERM FOREIGN KEY
(SUPPLIERORDERINGTERMS_ID) REFERENCES AGGSUPPLIERORDERINGTERMS (SUPPLIERORDERINGTERMS_ID);

ALTER TABLE PURCHASEORDERLINE ADD CONSTRAINT PURCHASEORDERLINE_SUPPRTERMS FOREIGN KEY
(SUPPLIERPRICETERMS_ID) REFERENCES AGGSUPPLIERPRICETERMS (SUPPLIERPRICETERMS_ID);

-----------------------   PURCHASEORDERLINEDETAIL   -----------------------------------
CREATE SEQUENCE SEQ_PURCHASEORDERLINEDETAIL
START WITH 1
INCREMENT BY 1;

CREATE TABLE PURCHASEORDERLINEDETAIL
(
PURCHASEORDERLINEDETAIL_ID NUMBER NOT NULL,
PURCHASEORDERLINE_ID NUMBER NOT NULL,
CUSTOMER_LINK_ID NUMBER NULL,
CUSTOMEROWNED VARCHAR2(1) DEFAULT 'N' NOT NULL CONSTRAINT YN_VALIDATIONFIELD285 CHECK (CUSTOMEROWNED IN ('Y','N')),
QUANTITYORDERED NUMBER DEFAULT 0 NOT NULL,
QUANTITYCANCELLED NUMBER DEFAULT 0 NOT NULL,
ORIGIN NUMBER DEFAULT 0 NOT NULL CONSTRAINT ORIGIN_VALID8 CHECK (ORIGIN IN (0,1)),
PREWASH NUMBER DEFAULT 0 NOT NULL CONSTRAINT PREWASH_VALID2 CHECK (PREWASH IN (0, 1)),
SYSTEMUSER_ID NUMBER NOT NULL,
TIMESTAMP DATE NOT NULL
);

COMMENT ON COLUMN PURCHASEORDERLINEDETAIL.ORIGIN IS '0 =Generated
1 =Manual';

COMMENT ON COLUMN PURCHASEORDERLINEDETAIL.PREWASH IS '0 = No prewash needed
1 = Prewash needed';

ALTER TABLE PURCHASEORDERLINEDETAIL ADD CONSTRAINT XPKPURCHASEORDERLINEDETAIL
PRIMARY KEY (PURCHASEORDERLINEDETAIL_ID);

CREATE UNIQUE INDEX XAK1PURCHASEORDERLINEDETAIL ON PURCHASEORDERLINEDETAIL
(PURCHASEORDERLINE_ID ASC, CUSTOMER_LINK_ID ASC, CUSTOMEROWNED ASC, ORIGIN ASC);

CREATE INDEX XIF1PURCHASEORDERLINEDETAIL ON PURCHASEORDERLINEDETAIL (PURCHASEORDERLINE_ID ASC);

CREATE INDEX XIF2PURCHASEORDERLINEDETAIL ON PURCHASEORDERLINEDETAIL (CUSTOMER_LINK_ID ASC);

ALTER TABLE PURCHASEORDERLINEDETAIL ADD CONSTRAINT PURCHASEORDERLINEDETAIL_POL FOREIGN KEY
(PURCHASEORDERLINE_ID) REFERENCES PURCHASEORDERLINE (PURCHASEORDERLINE_ID)
ON DELETE CASCADE;

ALTER TABLE PURCHASEORDERLINEDETAIL ADD CONSTRAINT PURCHASEORDERLINEDETAIL_CLID FOREIGN KEY
(CUSTOMER_LINK_ID) REFERENCES CUSTOMER_LINK (CUSTOMER_LINK_ID);

ALTER TABLE PURCHASEORDERLINEDETAIL ADD CONSTRAINT CUSTOWNED_CUSTID_VALID CHECK
((CUSTOMEROWNED = 'Y' AND CUSTOMER_LINK_ID IS NOT NULL) OR CUSTOMEROWNED = 'N');

------------------------   AGGSUPPLIERPRODUCT   --------------------------------------
ALTER TABLE AGGSUPPLIERPRODUCT ADD (ALLOWSUPPLIERMODIFICATIONS NUMBER DEFAULT 0 NOT NULL
				    CONSTRAINT ALLOWSUPPMODIFICATIONS_VALID CHECK (ALLOWSUPPLIERMODIFICATIONS IN (0, 1)));

COMMENT ON COLUMN AGGSUPPLIERPRODUCT.ALLOWSUPPLIERMODIFICATIONS IS '0 = Not applicable / No modifications
1 = Modifications may be done';

------------------------   SUPPLIER   -------------------------------------------------
ALTER TABLE SUPPLIER ADD (MODIFICATIONSBYSUPPLIER NUMBER DEFAULT 0 NOT NULL
			  CONSTRAINT MODIFICATIONSBYSUPPLIER_VALID CHECK (MODIFICATIONSBYSUPPLIER IN (0, 1)));

COMMENT ON COLUMN SUPPLIER.MODIFICATIONSBYSUPPLIER IS '0 = Not applicable / No modifications may be done
1 = Modifications may be done';

------------------------   ORDERPRIORITY   --------------------------------------------
ALTER TABLE ORDERPRIORITY ADD (DEFAULTFORCATEGORY VARCHAR2(1) DEFAULT 'N' NOT NULL
				CONSTRAINT YN_VALIDATIONFIELD286 CHECK (DEFAULTFORCATEGORY IN ('Y', 'N')));

ALTER TABLE ORDERPRIORITY ADD CONSTRAINT DEFAULTFORCATEGORY_CHECK CHECK
(DEFAULTFORCATEGORY = 'N' OR (DEFAULTFORCATEGORY = 'Y' AND ORDERTYPE = 2));

------------------------   POHEADERCONFIRMATION   -------------------------------------
CREATE SEQUENCE SEQ_POHEADERCONFIRMATION
START WITH 1
INCREMENT BY 1;

CREATE TABLE POHEADERCONFIRMATION
(
POHEADERCONFIRMATION_ID NUMBER NOT NULL,
PURCHASEORDERHEADER_ID NUMBER NOT NULL,
CONFIRMATIONTIMESTAMP DATE NOT NULL,
CONFIRMATIONUSER_ID NUMBER NOT NULL,
CONFIRMEDDELIVERYDATE DATE NOT NULL,
SYSTEMUSER_ID NUMBER NOT NULL,
TIMESTAMP DATE NOT NULL
);

ALTER TABLE POHEADERCONFIRMATION ADD CONSTRAINT XPKPOHEADERCONFIRMATION PRIMARY KEY (POHEADERCONFIRMATION_ID);

CREATE UNIQUE INDEX XAK1POHEADERCONFIRMATION ON POHEADERCONFIRMATION (PURCHASEORDERHEADER_ID ASC,
CONFIRMATIONTIMESTAMP ASC);

CREATE INDEX XIF1POHEADERCONFIRMATION ON POHEADERCONFIRMATION (PURCHASEORDERHEADER_ID ASC);

ALTER TABLE POHEADERCONFIRMATION ADD CONSTRAINT POHEADERCONFIRMATION_POHID FOREIGN KEY
(PURCHASEORDERHEADER_ID) REFERENCES PURCHASEORDERHEADER (PURCHASEORDERHEADER_ID)
ON DELETE CASCADE;

------------------------   POLINECONFIRMATION   ---------------------------------------
CREATE SEQUENCE SEQ_POLINECONFIRMATION
START WITH 1
INCREMENT BY 1;

CREATE TABLE POLINECONFIRMATION
(
POLINECONFIRMATION_ID NUMBER NOT NULL,
PURCHASEORDERLINE_ID NUMBER NOT NULL,
CONFIRMATIONTIMESTAMP DATE NOT NULL,
CONFIRMATIONUSER_ID NUMBER NOT NULL,
QUANTITYCONFIRMED NUMBER NOT NULL,
CONFIRMEDDELIVERYDATE DATE NOT NULL,
ORIGIN NUMBER DEFAULT 0 NOT NULL 
CONSTRAINT ORIGIN_VALID9 CHECK (ORIGIN IN (0, 1)),
SYSTEMUSER_ID NUMBER NOT NULL,
TIMESTAMP DATE NOT NULL
);

COMMENT ON COLUMN POLINECONFIRMATION.ORIGIN IS '0 = From PO line
1 = From PO header';

ALTER TABLE POLINECONFIRMATION ADD CONSTRAINT XPKPOLINECONFIRMATION PRIMARY KEY (POLINECONFIRMATION_ID);

CREATE UNIQUE INDEX XAK1POLINECONFIRMATION ON POLINECONFIRMATION (PURCHASEORDERLINE_ID ASC,
CONFIRMATIONTIMESTAMP ASC);

CREATE INDEX XIF1POLINECONFIRMATION ON POLINECONFIRMATION (PURCHASEORDERLINE_ID ASC);

ALTER TABLE POLINECONFIRMATION ADD CONSTRAINT POLINECONFIRMATION_POLID FOREIGN KEY
(PURCHASEORDERLINE_ID) REFERENCES PURCHASEORDERLINE (PURCHASEORDERLINE_ID)
ON DELETE CASCADE;

------------------------   BACKORDERPURCHASEORDERWOL   --------------------------------
CREATE SEQUENCE SEQ_BACKORDERPURCHASEORDERWOL
START WITH 1
INCREMENT BY 1;

CREATE TABLE BACKORDERPURCHASEORDERWOL
(
BACKORDERPURCHASEORDERWOL_ID NUMBER NOT NULL,
BACKORDEREXECUTIONWOL_ID NUMBER NOT NULL,
PURCHASEORDERLINEDETAIL_ID NUMBER NOT NULL,
SYSTEMUSER_ID NUMBER NOT NULL,
TIMESTAMP DATE NOT NULL
);

ALTER TABLE BACKORDERPURCHASEORDERWOL ADD CONSTRAINT XPKBACKORDERPURCHASEORDERWOL PRIMARY KEY
(BACKORDERPURCHASEORDERWOL_ID);

CREATE UNIQUE INDEX XAK1BACKORDERPURCHASEORDERWOL ON BACKORDERPURCHASEORDERWOL
(BACKORDEREXECUTIONWOL_ID ASC, PURCHASEORDERLINEDETAIL_ID ASC);

CREATE INDEX XIF1BACKORDERPURCHASEORDERWOL ON BACKORDERPURCHASEORDERWOL 
(BACKORDEREXECUTIONWOL_ID ASC);

CREATE INDEX XIF2BACKORDERPURCHASEORDERWOL ON BACKORDERPURCHASEORDERWOL 
(PURCHASEORDERLINEDETAIL_ID ASC);

ALTER TABLE BACKORDERPURCHASEORDERWOL ADD CONSTRAINT BACKORDERPURCHASEORDERWOL_BOE FOREIGN KEY
(BACKORDEREXECUTIONWOL_ID) REFERENCES BACKORDEREXECUTIONWOL (BACKORDEREXECUTIONWOL_ID)
ON DELETE CASCADE;

ALTER TABLE BACKORDERPURCHASEORDERWOL ADD CONSTRAINT BACKORDERPURCHASEORDERWOL_POLD FOREIGN KEY
(PURCHASEORDERLINEDETAIL_ID) REFERENCES PURCHASEORDERLINEDETAIL (PURCHASEORDERLINEDETAIL_ID)
ON DELETE CASCADE;

------------------------   BACKORDERPURCHASEORDERWOLE   --------------------------------
CREATE SEQUENCE SEQ_BACKORDERPURCHASEORDERWOLE
START WITH 1
INCREMENT BY 1;

CREATE TABLE BACKORDERPURCHASEORDERWOLE
(
BACKORDERPURCHASEORDERWOLE_ID NUMBER NOT NULL,
BACKORDEREXECUTIONWOLE_ID NUMBER NOT NULL,
PURCHASEORDERLINEDETAIL_ID NUMBER NOT NULL,
SYSTEMUSER_ID NUMBER NOT NULL,
TIMESTAMP DATE NOT NULL
);

ALTER TABLE BACKORDERPURCHASEORDERWOLE ADD CONSTRAINT XPKBACKORDERPURCHASEORDERWOLE PRIMARY KEY
(BACKORDERPURCHASEORDERWOLE_ID);

CREATE UNIQUE INDEX XAK1BACKORDERPURCHASEORDERWOLE ON BACKORDERPURCHASEORDERWOLE
(BACKORDEREXECUTIONWOLE_ID ASC, PURCHASEORDERLINEDETAIL_ID ASC);

CREATE INDEX XIF1BACKORDERPURCHASEORDERWOLE ON BACKORDERPURCHASEORDERWOLE 
(BACKORDEREXECUTIONWOLE_ID ASC);

CREATE INDEX XIF2BACKORDERPURCHASEORDERWOLE ON BACKORDERPURCHASEORDERWOLE 
(PURCHASEORDERLINEDETAIL_ID ASC);

ALTER TABLE BACKORDERPURCHASEORDERWOLE ADD CONSTRAINT BACKORDERPURCHASEORDERWOLE_BOE FOREIGN KEY
(BACKORDEREXECUTIONWOLE_ID) REFERENCES BACKORDEREXECUTIONWOLE (BACKORDEREXECUTIONWOLE_ID)
ON DELETE CASCADE;

ALTER TABLE BACKORDERPURCHASEORDERWOLE ADD CONSTRAINT BACKORDERPURCHASEORDERWOLE_POL FOREIGN KEY
(PURCHASEORDERLINEDETAIL_ID) REFERENCES PURCHASEORDERLINEDETAIL (PURCHASEORDERLINEDETAIL_ID)
ON DELETE CASCADE;