-- ABS-24732/ ABS-25196

--PURCHASEREQUISITION table--
CREATE SEQUENCE SEQ_PURCHASEREQUISITION
START WITH 1
INCREMENT BY 1;

CREATE TABLE PURCHASEREQUISITION
(
  PURCHASEREQUISITION_ID      NUMBER NOT NULL,
  STOCKROOM_ID                NUMBER NOT NULL,
  SUPPLIER_ID                 NUMBER NOT NULL,
  CUSTOMER_ID                 NUMBER NULL,
  CUSTOMEROWNED               VARCHAR2(1) DEFAULT 'N' NOT NULL CONSTRAINT YN_VALIDATIONFIELD274
                              CHECK (CUSTOMEROWNED IN ('Y','N')),
  PRODUCT_ID                  NUMBER NOT NULL,
  SIZEDEFINITION_ID           NUMBER NOT NULL,
  REQUESTEDGRADE_ID           NUMBER NOT NULL,
  MODIFICATION1_ID            NUMBER NULL,
  MODIFICATION2_ID            NUMBER NULL,
  MODIFICATION3_ID            NUMBER NULL,
  CODEUNIQUELY                VARCHAR2(1) DEFAULT 'N' NOT NULL CONSTRAINT YN_VALIDATIONFIELD275
                              CHECK (CODEUNIQUELY IN ('Y','N')),
  PREWASH                     NUMBER DEFAULT 0 NOT NULL CONSTRAINT PREWASH_VALID1_2
                              CHECK (PREWASH BETWEEN 0 AND 1),
  PURCHASECATEGORY            NUMBER DEFAULT 1 NOT NULL CONSTRAINT PURCHASECATEGORY_VALID1_2
                              CHECK (PURCHASECATEGORY BETWEEN 0 AND 5),
  QTYREQUESTED                NUMBER DEFAULT 0 NOT NULL,
  QTYCALCULATED               NUMBER DEFAULT 0 NOT NULL,
  QTYTOORDER                  NUMBER DEFAULT 0 NOT NULL,
  STOCKRESERVATIONPERGRADE_ID NUMBER NULL,
  AGGSUPPLIERPRODUCTSIZE_ID   NUMBER NULL,
  REQUESTEDDELIVERYDATE       DATE NOT NULL,
  EXPECTEDDELIVERYDATE        DATE NOT NULL,
  SHIPTO                      NUMBER DEFAULT 0 NOT NULL CONSTRAINT SHIPTO_VALID1_3
                              CHECK (SHIPTO BETWEEN 0 AND 2),
  SHIPTOSTOCKROOM_ID          NUMBER NULL,
  BILLTOCOMPANY               NUMBER DEFAULT 0 NOT NULL CONSTRAINT BILLTOCOMPANY_VALID1_3
                              CHECK (BILLTOCOMPANY BETWEEN 0 AND 1),
  LEGALENTITY_ID              NUMBER NOT NULL,
  SUPPLIERORDERINGTERMS_ID    NUMBER NULL,
  SUPPLIERPRICETERMS_ID       NUMBER NULL,
  PURCHASEPRICE               NUMBER(15,6) NULL,
  CREATIONUSER_ID             NUMBER NOT NULL,
  CREATIONTIMESTAMP           DATE NOT NULL,
  SYSTEMUSER_ID               NUMBER NOT NULL,
  TIMESTAMP                   DATE NOT NULL,
  RECALCNEEDED                NUMBER DEFAULT 0 NOT NULL CONSTRAINT RECALCNEEDED_VALID1_1
                              CHECK (RECALCNEEDED BETWEEN 0 AND 2),
  CODEUNIQUELYBYSUPPLIER      NUMBER DEFAULT 0 NOT NULL CONSTRAINT CODEUNIQUELYBYSUPP_VALID1_1
                              CHECK (CODEUNIQUELYBYSUPPLIER BETWEEN 0 AND 1),
  PREWASHBYSUPPLIER           NUMBER DEFAULT 0 NOT NULL CONSTRAINT PREWASHBYSUPPLIER_VALID1_1
                              CHECK (PREWASHBYSUPPLIER BETWEEN 0 AND 1)
);
 
COMMENT ON COLUMN PURCHASEREQUISITION.PREWASH
  IS 'Indicated if item to be ordered will have to be prewashed by supplier or not. Possible values:
0 - No prewash needed
1 - Prewash needed
';
COMMENT ON COLUMN PURCHASEREQUISITION.PURCHASECATEGORY
  IS 'Priority of the purchase requisition. Possible values:
0 = Not applicable
1 = All
2 = New contract 
3 = Renewal
4 = Replacement
5 = Increase stock level';

COMMENT ON COLUMN PURCHASEREQUISITION.QTYCALCULATED
  IS 'Quantity calculated taking into account defined minimum order quantity and package quantity';

COMMENT ON COLUMN PURCHASEREQUISITION.QTYTOORDER
  IS 'Filled by default with QtyCalculated but can be changed by user who generated the requisition';
  
COMMENT ON COLUMN PURCHASEREQUISITION.REQUESTEDDELIVERYDATE
  IS 'The date the items are needed to fulfill demand for backorders (from originating work orders)';
  
COMMENT ON COLUMN PURCHASEREQUISITION.SHIPTO
  IS 'Indicates if ordered items should be shipped to a different address than ordering stockroom. Possible values:
0 = Ordering Stockroom
1 = Other Stockroom
2 = Ordering customer
';

COMMENT ON COLUMN PURCHASEREQUISITION.SHIPTOSTOCKROOM_ID
  IS 'Will only be filled in case items are intended to be shipped to another stockroom (ShipTo = 2)';
  
COMMENT ON COLUMN PURCHASEREQUISITION.BILLTOCOMPANY
  IS 'Indicates if ordered items should be billed to a different bill-to company (address) than ordering stockroom. Possible values:
0 = Ordering Stockroom
1 = Ship-to Stockroom
';

COMMENT ON COLUMN PURCHASEREQUISITION.LEGALENTITY_ID
  IS 'Will be filled with LegalEntity_ID either from ordering stockroom or ship-to stockroom';

COMMENT ON COLUMN PURCHASEREQUISITION.RECALCNEEDED
  IS 'Indicates if purchase requisition needs to be reprocessed due to update. Possible values:
0 = Not applicable
1 = Recalculation needed due to creation/update of quantity
2 = Recalculation needed due to price/ordering terms definition at higher level
';

COMMENT ON COLUMN PURCHASEREQUISITION.CODEUNIQUELYBYSUPPLIER
  IS 'Indicates if item to be ordered will have to be coded uniquely by supplier or not. Possible values:
0 - not applicable / not coded uniquely
1 - will be coded uniquely
';

COMMENT ON COLUMN PURCHASEREQUISITION.PREWASHBYSUPPLIER
  IS 'Indicated if item to be ordered will have to be prewashed by supplier or not. Possible values:
0 - not applicable / no prewash
1 - will be prewashed
';

ALTER TABLE PURCHASEREQUISITION
  ADD CONSTRAINT XPKPURCHASEREQUISITION PRIMARY KEY (PURCHASEREQUISITION_ID);
  
CREATE UNIQUE INDEX XAK1PURCHASEREQUISITION 
ON PURCHASEREQUISITION (STOCKROOM_ID ASC, SUPPLIER_ID ASC, CUSTOMER_ID ASC, CUSTOMEROWNED ASC, PRODUCT_ID ASC, SIZEDEFINITION_ID ASC,
 REQUESTEDGRADE_ID ASC, MODIFICATION1_ID ASC, MODIFICATION2_ID ASC, MODIFICATION3_ID ASC, CODEUNIQUELY ASC,
 PREWASH ASC, PURCHASECATEGORY ASC, SHIPTO ASC, SHIPTOSTOCKROOM_ID ASC, BILLTOCOMPANY ASC);

CREATE INDEX XIF1PURCHASEREQUISITION ON PURCHASEREQUISITION (STOCKROOM_ID ASC);

CREATE INDEX XIF2PURCHASEREQUISITION ON PURCHASEREQUISITION (SUPPLIER_ID ASC);

CREATE INDEX XIF3PURCHASEREQUISITION ON PURCHASEREQUISITION (CUSTOMER_ID ASC);

CREATE INDEX XIF4PURCHASEREQUISITION ON PURCHASEREQUISITION (PRODUCT_ID ASC);

CREATE INDEX XIF5PURCHASEREQUISITION ON PURCHASEREQUISITION (SIZEDEFINITION_ID ASC);

CREATE INDEX XIF6PURCHASEREQUISITION ON PURCHASEREQUISITION (REQUESTEDGRADE_ID ASC);

CREATE INDEX XIF7PURCHASEREQUISITION ON PURCHASEREQUISITION (MODIFICATION1_ID ASC);

CREATE INDEX XIF8PURCHASEREQUISITION ON PURCHASEREQUISITION (MODIFICATION2_ID ASC);

CREATE INDEX XIF9PURCHASEREQUISITION ON PURCHASEREQUISITION (MODIFICATION3_ID ASC);

CREATE INDEX XIF10PURCHASEREQUISITION ON PURCHASEREQUISITION (STOCKRESERVATIONPERGRADE_ID ASC);

CREATE INDEX XIF11PURCHASEREQUISITION ON PURCHASEREQUISITION (AGGSUPPLIERPRODUCTSIZE_ID ASC);

CREATE INDEX XIF12PURCHASEREQUISITION ON PURCHASEREQUISITION (SHIPTOSTOCKROOM_ID ASC);

CREATE INDEX XIF13PURCHASEREQUISITION ON PURCHASEREQUISITION (LEGALENTITY_ID ASC);

CREATE INDEX XIF14PURCHASEREQUISITION ON PURCHASEREQUISITION (SUPPLIERORDERINGTERMS_ID ASC);

CREATE INDEX XIF15PURCHASEREQUISITION ON PURCHASEREQUISITION (SUPPLIERPRICETERMS_ID ASC);

ALTER TABLE PURCHASEREQUISITION
  ADD CONSTRAINT PURCHASEREQUISITION_STOCKROOM FOREIGN KEY (STOCKROOM_ID)
  REFERENCES STOCKROOM (STOCKROOM_ID);
  
ALTER TABLE PURCHASEREQUISITION
  ADD CONSTRAINT PURCHASEREQUISITION_SUPPLIER FOREIGN KEY (SUPPLIER_ID)
  REFERENCES SUPPLIER (SUPPLIER_ID);
  
ALTER TABLE PURCHASEREQUISITION
  ADD CONSTRAINT PURCHASEREQUISITION_CUSTOMER FOREIGN KEY (CUSTOMER_ID)
  REFERENCES CUSTOMER (CUSTOMER_ID);
  
ALTER TABLE PURCHASEREQUISITION
  ADD CONSTRAINT PURCHASEREQUISITION_PRODUCT FOREIGN KEY (PRODUCT_ID)
  REFERENCES PRODUCT (PRODUCT_ID);
  
ALTER TABLE PURCHASEREQUISITION
  ADD CONSTRAINT PURCHASEREQUISITION_SIZEDEF FOREIGN KEY (SIZEDEFINITION_ID)
  REFERENCES SIZEDEFINITION (SIZEDEFINITION_ID);
  
ALTER TABLE PURCHASEREQUISITION
  ADD CONSTRAINT PURCHASEREQUISITION_REQGR FOREIGN KEY (REQUESTEDGRADE_ID)
  REFERENCES QUALITYGRADE (QUALITYGRADE_ID);
  
ALTER TABLE PURCHASEREQUISITION
  ADD CONSTRAINT PURCHASEREQUISITION_MODIF1 FOREIGN KEY (MODIFICATION1_ID)
  REFERENCES MODIFICATION (MODIFICATION_ID);
  
ALTER TABLE PURCHASEREQUISITION
  ADD CONSTRAINT PURCHASEREQUISITION_MODIF2 FOREIGN KEY (MODIFICATION2_ID)
  REFERENCES MODIFICATION (MODIFICATION_ID);
  
ALTER TABLE PURCHASEREQUISITION
  ADD CONSTRAINT PURCHASEREQUISITION_MODIF3 FOREIGN KEY (MODIFICATION3_ID)
  REFERENCES MODIFICATION (MODIFICATION_ID);
  
ALTER TABLE PURCHASEREQUISITION
  ADD CONSTRAINT PURCHASEREQUISITION_SRG FOREIGN KEY (STOCKRESERVATIONPERGRADE_ID)
  REFERENCES STOCKRESERVATIONPERGRADE (STOCKRESERVATIONPERGRADE_ID) ON DELETE SET NULL;
  
ALTER TABLE PURCHASEREQUISITION
  ADD CONSTRAINT PURCHASEREQUISITION_AGGSUPPSZ FOREIGN KEY (AGGSUPPLIERPRODUCTSIZE_ID)
  REFERENCES AGGSUPPLIERPRODUCTSIZE (AGGSUPPLIERPRODUCTSIZE_ID) ON DELETE SET NULL;
  
ALTER TABLE PURCHASEREQUISITION
  ADD CONSTRAINT PURCHASEREQUISITION_SHIPTOST FOREIGN KEY (SHIPTOSTOCKROOM_ID)
  REFERENCES STOCKROOM (STOCKROOM_ID);
  
ALTER TABLE PURCHASEREQUISITION
  ADD CONSTRAINT PURCHASEREQUISITION_LEGENT FOREIGN KEY (LEGALENTITY_ID)
  REFERENCES LEGALENTITY (LEGALENTITY_ID);
  
ALTER TABLE PURCHASEREQUISITION
  ADD CONSTRAINT PURCHASEREQUISITION_SUPPORDER FOREIGN KEY (SUPPLIERORDERINGTERMS_ID)
  REFERENCES AGGSUPPLIERORDERINGTERMS (SUPPLIERORDERINGTERMS_ID);
  
ALTER TABLE PURCHASEREQUISITION
  ADD CONSTRAINT PURCHASEREQUISITION_SUPPPRICET FOREIGN KEY (SUPPLIERPRICETERMS_ID)
  REFERENCES AGGSUPPLIERPRICETERMS (SUPPLIERPRICETERMS_ID);

ALTER TABLE PURCHASEREQUISITION
  ADD CONSTRAINT PURCHASEREQUISITION_VALID1
  CHECK ((CUSTOMEROWNED = 'Y' AND CUSTOMER_ID IS NOT NULL) OR (CUSTOMEROWNED = 'N'));

---BACKORDERREQUISITIONWOL table---
CREATE SEQUENCE SEQ_BACKORDERREQUISITIONWOL
START WITH 1
INCREMENT BY 1;

CREATE TABLE BACKORDERREQUISITIONWOL
(
  BACKORDERREQUISITIONWOL_ID NUMBER NOT NULL,
  BACKORDEREXECUTIONWOL_ID   NUMBER NOT NULL,
  PURCHASEREQUISITION_ID     NUMBER NOT NULL,
  SYSTEMUSER_ID              NUMBER NOT NULL,
  TIMESTAMP                  DATE NOT NULL
);

ALTER TABLE BACKORDERREQUISITIONWOL
  ADD CONSTRAINT XPKBACKORDERREQUISITIONWOL PRIMARY KEY (BACKORDERREQUISITIONWOL_ID);

CREATE UNIQUE INDEX XAK1BACKORDERREQUISITIONWOL ON BACKORDERREQUISITIONWOL (BACKORDEREXECUTIONWOL_ID ASC, PURCHASEREQUISITION_ID ASC);

CREATE INDEX XIF1BACKORDERREQUISITIONWOL ON BACKORDERREQUISITIONWOL (BACKORDEREXECUTIONWOL_ID ASC);

CREATE INDEX XIF2BACKORDERREQUISITIONWOL ON BACKORDERREQUISITIONWOL (PURCHASEREQUISITION_ID ASC);

ALTER TABLE BACKORDERREQUISITIONWOL
  ADD CONSTRAINT BACKORDERREQUISITIONWOL_BOE FOREIGN KEY (BACKORDEREXECUTIONWOL_ID)
  REFERENCES BACKORDEREXECUTIONWOL (BACKORDEREXECUTIONWOL_ID) ON DELETE CASCADE;
  
ALTER TABLE BACKORDERREQUISITIONWOL
  ADD CONSTRAINT BACKORDERREQUISITIONWOL_PR FOREIGN KEY (PURCHASEREQUISITION_ID)
  REFERENCES PURCHASEREQUISITION (PURCHASEREQUISITION_ID) ON DELETE CASCADE;
  
---BACKORDERREQUISITIONWOLE table---
CREATE SEQUENCE SEQ_BACKORDERREQUISITIONWOLE
START WITH 1
INCREMENT BY 1;

CREATE TABLE BACKORDERREQUISITIONWOLE
(
  BACKORDERREQUISITIONWOLE_ID NUMBER NOT NULL,
  BACKORDEREXECUTIONWOLE_ID   NUMBER NOT NULL,
  PURCHASEREQUISITION_ID     NUMBER NOT NULL,
  SYSTEMUSER_ID              NUMBER NOT NULL,
  TIMESTAMP                  DATE NOT NULL
)
;

ALTER TABLE BACKORDERREQUISITIONWOLE
  ADD CONSTRAINT XPKBACKORDERREQUISITIONWOLE PRIMARY KEY (BACKORDERREQUISITIONWOLE_ID);

CREATE UNIQUE INDEX XAK1BACKORDERREQUISITIONWOLE ON BACKORDERREQUISITIONWOLE (BACKORDEREXECUTIONWOLE_ID ASC, PURCHASEREQUISITION_ID ASC);

CREATE INDEX XIF1BACKORDERREQUISITIONWOLE ON BACKORDERREQUISITIONWOLE (BACKORDEREXECUTIONWOLE_ID ASC);

CREATE INDEX XIF2BACKORDERREQUISITIONWOLE ON BACKORDERREQUISITIONWOLE (PURCHASEREQUISITION_ID ASC);

ALTER TABLE BACKORDERREQUISITIONWOLE
  ADD CONSTRAINT BACKORDERREQUISITIONWOLE_BOE FOREIGN KEY (BACKORDEREXECUTIONWOLE_ID)
  REFERENCES BACKORDEREXECUTIONWOLE (BACKORDEREXECUTIONWOLE_ID) ON DELETE CASCADE;
  
ALTER TABLE BACKORDERREQUISITIONWOLE
  ADD CONSTRAINT BACKORDERREQUISITIONWOLE_PR FOREIGN KEY (PURCHASEREQUISITION_ID)
  REFERENCES PURCHASEREQUISITION (PURCHASEREQUISITION_ID) ON DELETE CASCADE;
