-- ABS-74081 / ABS-81689
---INVOICELINECLIENT---
CREATE TABLE INVOICELINECLIENT
(
INVOICELINE_ID        NUMBER NOT NULL,
CLIENT_LINK_ID        NUMBER NOT NULL,
CLIENTNUMBER          NUMBER NOT NULL,
NAME                  VARCHAR2(40) NOT NULL,
FIRSTNAME             VARCHAR2(24) NULL,
ROOMNUMBER            VARCHAR2(12) NULL,
QUANTITY              NUMBER DEFAULT 0 NOT NULL,
WEIGHT                NUMBER DEFAULT 0 NOT NULL,
AMOUNT                NUMBER DEFAULT 0 NOT NULL,
SYSTEMUSER_ID         NUMBER NOT NULL,
TIMESTAMP             DATE NOT NULL
);

COMMENT ON COLUMN  INVOICELINECLIENT.CLIENTNUMBER
  IS 'Stored for history';
COMMENT ON COLUMN  INVOICELINECLIENT.NAME
  IS 'Stored for history';
COMMENT ON COLUMN  INVOICELINECLIENT.FIRSTNAME
  IS 'Stored for history';
COMMENT ON COLUMN  INVOICELINECLIENT.ROOMNUMBER
  IS 'Stored for history';
COMMENT ON COLUMN  INVOICELINECLIENT.QUANTITY
  IS 'For clean / soil weight store 0. All other invoicelinetypes filled with quantity for this client.';
COMMENT ON COLUMN  INVOICELINECLIENT.WEIGHT
  IS 'Only filled for clean / soil weight. Set to 0 for all other invoicelinetypes.';
COMMENT ON COLUMN  INVOICELINECLIENT.AMOUNT
  IS 'Number of decimals determined from currency.';

ALTER TABLE INVOICELINECLIENT
  ADD CONSTRAINT XPKINVOICELINECLIENT PRIMARY KEY (INVOICELINE_ID,CLIENT_LINK_ID);

CREATE INDEX XIF1INVOICELINECLIENT ON INVOICELINECLIENT (INVOICELINE_ID ASC);

CREATE INDEX XIF2INVOICELINECLIENT ON INVOICELINECLIENT (CLIENT_LINK_ID ASC);

ALTER TABLE INVOICELINECLIENT
  ADD CONSTRAINT INVOICELINECLIENT_INVOICELINE FOREIGN KEY (INVOICELINE_ID)
  REFERENCES INVOICELINE(INVOICELINE_ID) ON DELETE CASCADE;

ALTER TABLE INVOICELINECLIENT
  ADD CONSTRAINT INVOICELINECLIENT_CLIENT_LINK FOREIGN KEY (CLIENT_LINK_ID)
  REFERENCES CLIENT_LINK(CLIENT_LINK_ID);