---ABS-113566

---CUSTOMER table----
ALTER TABLE CUSTOMER
DROP CONSTRAINT CUSTOMERTYPE_VALID;

ALTER TABLE CUSTOMER
ADD CONSTRAINT CHK_CUSTOMER_CUSTOMERTYPE CHECK (CUSTOMERTYPE IN (0,1,2,3));

COMMENT ON COLUMN CUSTOMER.CUSTOMERTYPE IS
'0 = External
1 = Internal (stockroom)
2 = Internal (Business unit)
3 = Internal (Truck Stockroom)';


---PACKINGNOTE TABLE----


ALTER TABLE PACKINGNOTE
DROP CONSTRAINT PNORIGIN_VALID1_1;

ALTER TABLE PACKINGNOTE
ADD CONSTRAINT CHK_PACKINGNOTE_ORIGIN CHECK (ORIGIN IN (0,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20));

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 = MultiReadShipment
15 = Charge To Product Process
16 = Customer Packing Note
17 = Curtain outscan
18 = Cabinet Service
19 = BM6 Packing Note
20 = Truck Replenishment';


---PACKINGNOTELINE TABLE----
ALTER TABLE PACKINGNOTELINE
DROP CONSTRAINT PNLORIGIN_VALID;

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

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
16 = BM6 Ordering
17 = Truck Replenishment';

----TRUCK TABLE---

ALTER TABLE TRUCK 
ADD INTERNALCUSTOMER_ID NUMBER NULL;

COMMENT ON COLUMN TRUCK.INTERNALCUSTOMER_ID IS 'Used for FACILITYSERVICE module';

CREATE UNIQUE INDEX XAK3TRUCK ON TRUCK(INTERNALCUSTOMER_ID);

ALTER TABLE TRUCK
ADD CONSTRAINT FK_TRUCK_INTERNALCUSTOMERID FOREIGN KEY (INTERNALCUSTOMER_ID) REFERENCES CUSTOMER (CUSTOMER_ID) ON DELETE CASCADE;
