--ABS-104338/ABS-104395

-----------------------NOTCHARGEDINVOICELINE
CREATE SEQUENCE SEQ_NOTCHARGEDINVOICELINE
START WITH 1
INCREMENT BY 1;

CREATE TABLE NOTCHARGEDINVOICELINE
(
  NOTCHARGEDINVOICELINE_ID NUMBER NOT NULL,
  INVOICEHEADER_ID NUMBER NOT NULL,
  INVOICELINETYPE_ID NUMBER NOT NULL,
  INVOICELINESUBTYPE NUMBER DEFAULT 0 NOT NULL,
  NOCHARGEREASONTYPE NUMBER DEFAULT 0 NOT NULL CONSTRAINT CHK_NOTCHGINVLN_NOCHGREAS CHECK (NOCHARGEREASONTYPE IN (1, 2, 3, 4, 5, 6, 7)),
  PRODUCT_LINK_ID NUMBER NULL,
  SIZEDEFINITION_LINK_ID NUMBER NULL,
  FINISHINGMETHOD_ID NUMBER NULL,
  WEAREREMPLOYMENT_LINK_ID NUMBER NULL,
  BILLEDQTY NUMBER DEFAULT 0 NOT NULL,
  PRICE NUMBER NULL,
  AMOUNT NUMBER NOT NULL,
  PRODUCTLISTPERCUSTOMER_ID NUMBER NULL,
  SYSTEMUSER_ID  NUMBER NOT NULL,
  TIMESTAMP      DATE NOT NULL
);

COMMENT ON COLUMN NOTCHARGEDINVOICELINE.NOCHARGEREASONTYPE IS '1 = Setting at customer level
2 = Image Care
3 = Budget Protection
4 = General Insurance
5 = System setting
6 = Other module
7 = Bill Assure
';

ALTER TABLE NOTCHARGEDINVOICELINE ADD CONSTRAINT XPKNOTCHARGEDINVOICELINE PRIMARY KEY (NOTCHARGEDINVOICELINE_ID);

CREATE INDEX XIF1NOTCHARGEDINVOICELINE ON NOTCHARGEDINVOICELINE (INVOICEHEADER_ID ASC);
CREATE INDEX XIF2NOTCHARGEDINVOICELINE ON NOTCHARGEDINVOICELINE (INVOICELINETYPE_ID ASC);
CREATE INDEX XIF3NOTCHARGEDINVOICELINE ON NOTCHARGEDINVOICELINE (PRODUCT_LINK_ID ASC);
CREATE INDEX XIF4NOTCHARGEDINVOICELINE ON NOTCHARGEDINVOICELINE (SIZEDEFINITION_LINK_ID ASC);
CREATE INDEX XIF5NOTCHARGEDINVOICELINE ON NOTCHARGEDINVOICELINE (FINISHINGMETHOD_ID ASC);
CREATE INDEX XIF6NOTCHARGEDINVOICELINE ON NOTCHARGEDINVOICELINE (WEAREREMPLOYMENT_LINK_ID ASC);
CREATE INDEX XIF7NOTCHARGEDINVOICELINE ON NOTCHARGEDINVOICELINE (PRODUCTLISTPERCUSTOMER_ID ASC);

ALTER TABLE NOTCHARGEDINVOICELINE ADD CONSTRAINT FK_NOTCHGINVLN_INVH FOREIGN KEY (INVOICEHEADER_ID)
REFERENCES INVOICEHEADER (INVOICEHEADER_ID) ON DELETE CASCADE;

ALTER TABLE NOTCHARGEDINVOICELINE ADD CONSTRAINT FK_NOTCHGINVLN_INVLNTY FOREIGN KEY (INVOICELINETYPE_ID)
REFERENCES INVOICELINETYPE (INVOICELINETYPE_ID);

ALTER TABLE NOTCHARGEDINVOICELINE ADD CONSTRAINT FK_NOTCHGINVLN_PROD_LK FOREIGN KEY (PRODUCT_LINK_ID)
REFERENCES PRODUCT_LINK (PRODUCT_LINK_ID);

ALTER TABLE NOTCHARGEDINVOICELINE ADD CONSTRAINT FK_NOTCHGINVLN_SIZE_LK FOREIGN KEY (SIZEDEFINITION_LINK_ID)
REFERENCES SIZEDEFINITION_LINK (SIZEDEFINITION_LINK_ID);

ALTER TABLE NOTCHARGEDINVOICELINE ADD CONSTRAINT FK_NOTCHGINVLN_FM FOREIGN KEY (FINISHINGMETHOD_ID)
REFERENCES FINISHINGMETHOD (FINISHINGMETHOD_ID);

ALTER TABLE NOTCHARGEDINVOICELINE ADD CONSTRAINT FK_NOTCHGINVLN_WEMP_LK FOREIGN KEY (WEAREREMPLOYMENT_LINK_ID)
REFERENCES WEAREREMPLOYMENT_LINK (WEAREREMPLOYMENT_LINK_ID);

ALTER TABLE NOTCHARGEDINVOICELINE ADD CONSTRAINT FK_NOTCHGINVLN_PLPC FOREIGN KEY (PRODUCTLISTPERCUSTOMER_ID)
REFERENCES PRODUCTLISTPERCUSTOMER (PRODUCTLISTPERCUSTOMER_ID) ON DELETE SET NULL;

-----------------------SPECIALCHARGE

ALTER TABLE SPECIALCHARGE ADD (ENABLEBILLASSURE VARCHAR2(1) DEFAULT 'N' NOT NULL CONSTRAINT CHK_SPCH_ENABLEBILLASSURE CHECK (ENABLEBILLASSURE IN ('Y', 'N')));

COMMENT ON COLUMN SPECIALCHARGE.ENABLEBILLASSURE IS 'The attribute will only be used when module BILLASSURE is active';


