--ABS-113748
COMMENT ON COLUMN STATUSQUOHEADER.STATUSQUOTYPE IS '1= Items In Circ
2= Sets
3= Wearers
4= Stock Quantities
5 = Lease
6 = stock charge
7 = mats in circulation
8 = on site inventory
9 = costs PBA
10 = compress
11 = clean up
12 = commission
13 = max items in circ
14 = AR Aging
15 = Items In Circ Residential';

ALTER TABLE STATUSQUOHEADER
  DROP CONSTRAINT STATUSQUOTYPE_VALID;
ALTER TABLE STATUSQUOHEADER
  ADD CONSTRAINT STATUSQUOTYPE_VALID
  CHECK (STATUSQUOTYPE BETWEEN 1 AND 15);

CREATE TABLE SQRESIDENTIALITEM
(
  SQRESIDENTIALITEM_ID   NUMBER NOT NULL,
  STATUSQUOHEADER_ID     NUMBER NOT NULL,
  CUSTOMER_LINK_ID       NUMBER NOT NULL,
  CLIENT_LINK_ID         NUMBER NOT NULL,
  PRODUCT_LINK_ID        NUMBER NOT NULL,
  SIZEDEFINITION_LINK_ID NUMBER NOT NULL,
  FINISHINGMETHOD_ID     NUMBER NOT NULL,
  QUANTITY               NUMBER NOT NULL,
  DAYSSETTING            NUMBER,
  DAYSSETTING_QUANTITY   NUMBER,
  CLIENTDELIVERYNOTE_ID  NUMBER
);
ALTER TABLE SQRESIDENTIALITEM
  ADD CONSTRAINT PK_SQRESIDENTIALITEM PRIMARY KEY (SQRESIDENTIALITEM_ID);
ALTER TABLE SQRESIDENTIALITEM
  ADD CONSTRAINT XAK1_SQRESIDENTIALITEM UNIQUE (STATUSQUOHEADER_ID, CUSTOMER_LINK_ID, CLIENT_LINK_ID, PRODUCT_LINK_ID, SIZEDEFINITION_LINK_ID, FINISHINGMETHOD_ID);
ALTER TABLE SQRESIDENTIALITEM
  ADD CONSTRAINT FK_SQRESIDENTIALITEMFK_CLIENT FOREIGN KEY (CLIENT_LINK_ID)
  REFERENCES CLIENT_LINK (CLIENT_LINK_ID);
ALTER TABLE SQRESIDENTIALITEM
  ADD CONSTRAINT FK_SQRESIDENTIALITEMFK_PRODUCT FOREIGN KEY (PRODUCT_LINK_ID)
  REFERENCES PRODUCT_LINK (PRODUCT_LINK_ID);
ALTER TABLE SQRESIDENTIALITEM
  ADD CONSTRAINT FK_SQRESIDENTIALITEMFK_SIZE FOREIGN KEY (SIZEDEFINITION_LINK_ID)
  REFERENCES SIZEDEFINITION_LINK (SIZEDEFINITION_LINK_ID);
ALTER TABLE SQRESIDENTIALITEM
  ADD CONSTRAINT FK_SQRESIDENTIALITEM_CUST FOREIGN KEY (CUSTOMER_LINK_ID)
  REFERENCES CUSTOMER_LINK (CUSTOMER_LINK_ID);
ALTER TABLE SQRESIDENTIALITEM
  ADD CONSTRAINT FK_SQRESIDENTIALITEM_SQHEADER FOREIGN KEY (STATUSQUOHEADER_ID)
  REFERENCES STATUSQUOHEADER (STATUSQUOHEADER_ID) ON DELETE CASCADE;

create sequence SEQ_SQRESIDENTIALITEM
minvalue 1
maxvalue 9999999999999999999999999999
start with 1
increment by 1
cache 20;
