-- ABS-44359/ ABS-51178

---BUSINESSUNITSPECIALSCAN table---
CREATE SEQUENCE SEQ_BUSINESSUNITSPECIALSCAN
START WITH 1
INCREMENT BY 1;

CREATE TABLE BUSINESSUNITSPECIALSCAN
(
  BUSINESSUNITSPECIALSCAN_ID NUMBER NOT NULL,
  BUSINESSUNIT_ID            NUMBER NOT NULL,
  SPECIALSCANS_ID            NUMBER NOT NULL,
  PRODUCT_ID                 NUMBER NOT NULL,
  SYSTEMUSER_ID              NUMBER NOT NULL,
  TIMESTAMP                  DATE NOT NULL
)
;

COMMENT ON COLUMN BUSINESSUNITSPECIALSCAN.PRODUCT_ID
  IS 'Must be residential product (TypeOfMerchandize = 5)';

ALTER TABLE BUSINESSUNITSPECIALSCAN
  ADD CONSTRAINT XPKBUSINESSUNITSPECIALSCAN PRIMARY KEY (BUSINESSUNITSPECIALSCAN_ID);

CREATE INDEX XAK1BUSINESSUNITSPECIALSCAN ON BUSINESSUNITSPECIALSCAN (BUSINESSUNIT_ID ASC, SPECIALSCANS_ID ASC);
CREATE INDEX XIF1BUSINESSUNITSPECIALSCAN ON BUSINESSUNITSPECIALSCAN (BUSINESSUNIT_ID ASC);
CREATE INDEX XIF2BUSINESSUNITSPECIALSCAN ON BUSINESSUNITSPECIALSCAN (SPECIALSCANS_ID ASC);
CREATE INDEX XIF3BUSINESSUNITSPECIALSCAN ON BUSINESSUNITSPECIALSCAN (PRODUCT_ID ASC);

ALTER TABLE BUSINESSUNITSPECIALSCAN
  ADD CONSTRAINT BUSINESSUNITSPECIALSCAN_BU FOREIGN KEY (BUSINESSUNIT_ID)
  REFERENCES BUSINESSUNIT (BUSINESSUNIT_ID) ON DELETE CASCADE;
  
ALTER TABLE BUSINESSUNITSPECIALSCAN
  ADD CONSTRAINT BUSINESSUNITSPECIALSCAN_SC FOREIGN KEY (SPECIALSCANS_ID)
  REFERENCES SPECIALSCANS (SPECIALSCANS_ID) ON DELETE CASCADE;
  
ALTER TABLE BUSINESSUNITSPECIALSCAN
  ADD CONSTRAINT BUSINESSUNITSPECIALSCAN_PR FOREIGN KEY (PRODUCT_ID)
  REFERENCES PRODUCT (PRODUCT_ID);

---CLIENTDELIVERYNOTELINE table---
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 8 = Special Scan';
 
ALTER TABLE CLIENTDELIVERYNOTELINE
  DROP CONSTRAINT CLIENTDNLINE_ORIGIN_VALID;
ALTER TABLE CLIENTDELIVERYNOTELINE
  ADD CONSTRAINT CLIENTDNLINE_ORIGIN_VALID
  CHECK (ORIGIN IN (1, 2, 3, 4, 5, 6, 7, 8));

---CUSTOMERRESIDENTIALSETTINGS table---
ALTER TABLE CUSTOMERRESIDENTIALSETTINGS ADD (INVOICESPECIALSCANS NUMBER DEFAULT 0 NOT NULL
                                                         CONSTRAINT INVOICESPECIALSCANS_VALID1_1 CHECK (INVOICESPECIALSCANS IN (0, 1)));

COMMENT ON COLUMN CUSTOMERRESIDENTIALSETTINGS.INVOICESPECIALSCANS
  IS '0 = Not applicable
1 = Special scans are invoiced (by defining a product for a special scan type).
'; 