--ABS-13983 

---PLPCDISTRIBPOINTNOBUDGET table---
    
CREATE TABLE PLPCDISTRIBPOINTNOBUDGET(
  PRODUCTLISTPERCUSTOMER_ID      NUMBER NOT NULL,
  DISTRIBUTIONPOINT_ID           NUMBER NOT NULL,
  SYSTEMUSER_ID                  NUMBER NOT NULL,
  TIMESTAMP                      DATE DEFAULT SYSDATE NOT NULL
);

ALTER TABLE PLPCDISTRIBPOINTNOBUDGET
  ADD CONSTRAINT XPKPLPCDISTRIBPOINTNOBUDGET PRIMARY KEY (PRODUCTLISTPERCUSTOMER_ID, DISTRIBUTIONPOINT_ID);

CREATE INDEX XIF1PLPCDISTRIBPOINTNOBUDGET ON PLPCDISTRIBPOINTNOBUDGET (PRODUCTLISTPERCUSTOMER_ID ASC);

CREATE INDEX XIF2PLPCDISTRIBPOINTNOBUDGET ON PLPCDISTRIBPOINTNOBUDGET (DISTRIBUTIONPOINT_ID ASC);

ALTER TABLE PLPCDISTRIBPOINTNOBUDGET
  ADD CONSTRAINT PLPCDISTRIBPOINTNOBUDGET_PLPC FOREIGN KEY (PRODUCTLISTPERCUSTOMER_ID)
  REFERENCES PRODUCTLISTPERCUSTOMER (PRODUCTLISTPERCUSTOMER_ID) ON DELETE CASCADE;
  
ALTER TABLE PLPCDISTRIBPOINTNOBUDGET
  ADD CONSTRAINT PLPCDISTRIBPOINTNOBUDGET_DP FOREIGN KEY (DISTRIBUTIONPOINT_ID)
  REFERENCES DISTRIBUTIONPOINT (DISTRIBUTIONPOINT_ID) ON DELETE CASCADE;


   
---WEIGHINGCATEGPERCUSTOMER table---

ALTER TABLE WEIGHINGCATEGPERCUSTOMER ADD (
                                     BUDGETUSAGE NUMBER DEFAULT 0 NOT NULL
                                     CONSTRAINT WCPCBUDGETUSAGE_VALID1_1 CHECK (BUDGETUSAGE BETWEEN 0 AND 2)
                                     );
                                     
COMMENT ON COLUMN WEIGHINGCATEGPERCUSTOMER.BUDGETUSAGE
  IS '0 = Not in Budget
      1 = Self paid, not in Budget
      2 = In Budget';  
                     
                      
---CLIENTDELIVERYNOTELINE table---
  
ALTER TABLE CLIENTDELIVERYNOTELINE
	ADD CONSTRAINT CLIENTDNLINE_ORIGIN_VALID  CHECK (ORIGIN IN (1, 2, 3, 4, 5, 6, 7));
                                 
COMMENT ON COLUMN CLIENTDELIVERYNOTELINE.ORIGIN
  IS '1 = Manual created,
2 = Soil receiving scanning
3 = Soil receiving counting
4 = Outscan/sort scanning
5 = Outscan/sort counting
6 = Conversion
7 = Charge to Product process'; 
  
     
---PACKINGNOTE table---

ALTER TABLE PACKINGNOTE
  DROP CONSTRAINT PNORIGIN_VALID1_1;
  
ALTER TABLE PACKINGNOTE ADD CONSTRAINT PNORIGIN_VALID1_1 CHECK (ORIGIN BETWEEN 0 AND 15);
      
COMMENT ON COLUMN PACKINGNOTE.ORIGIN
  IS '0 = Generated based on Order
1 = Manually added
2	= Generated based on Outscan
3	= Quantity Correction Ticket
4 = Stock Delivery Note
5 = Special Order
6 = Bulk Note
7 = Bin Note
8 = Transfer Weights
9 = Import from DW
10 = Mounting Order
11 = Sales Order
12 = Credit Note
13 = Residential Delivery Note
14 = Customer Packing Note
15 = Charge To Product Process';                
                                     
     
---PACKINGNOTELINE table---

ALTER TABLE PACKINGNOTELINE
  DROP CONSTRAINT PNLORIGIN_valid;
  
ALTER TABLE PACKINGNOTELINE
	ADD  CONSTRAINT PNLORIGIN_valid  CHECK (ORIGIN IN (0, 1, 2, 3, 4, 9, 10, 11, 12, 13, 15));

COMMENT ON COLUMN PACKINGNOTELINE.ORIGIN IS '0 = Generated based on Order
1 = Manually added
2 = Generated based on Outscan
3 = Quantity Correction Ticket
4 = Stock Delivery Note
9 = Import from DW
10 = Mounting Order
11 = Sales Order
12 = Credit Note
13 = Residential Delivery Note
15 = Charge To Product Process'; 
  