-- ABS-25830/ ABS-59461

-- INVOICEAPPENDIXDEFINITION table --
INSERT INTO INVOICEAPPENDIXDEFINITION(INVOICEAPPENDIXDEFINITION_ID, INVOICEAPPENDIXTYPE, DESCRIPTION, REPORTNAME, CUSTOMERCODE, MODULE,  MANUAL, DIRECTINVOICE, OTHERSERVICES, RESIDENTIALSERVICES, SYSTEMUSER_ID, TIMESTAMP) 
       VALUES (46, 46, 'Cost Specification per dept individual', 'DPCostSpecificationIndividual.rpt', '', 'DPCOSTSPECIFICATION', 'N', 'Y', 'N', 'N', GETMINSYSTEMUSER(), SYSDATE);
COMMIT;

INSERT INTO INVOICEAPPENDIXDEFINITION(INVOICEAPPENDIXDEFINITION_ID, INVOICEAPPENDIXTYPE, DESCRIPTION, REPORTNAME, CUSTOMERCODE, MODULE,  MANUAL, DIRECTINVOICE, OTHERSERVICES, RESIDENTIALSERVICES, SYSTEMUSER_ID, TIMESTAMP) 
       VALUES (47, 47, 'Cost Specification per dept consolidated', 'DPCostSpecificationConsolidated.rpt', '', 'DPCOSTSPECIFICATION', 'N', 'Y', 'N', 'N', GETMINSYSTEMUSER(), SYSDATE);
COMMIT;

-- INVOICELINEDP table --
CREATE TABLE INVOICELINEDP
(
  INVOICELINE_ID            NUMBER NOT NULL,
  DISTRIBUTIONPOINT_LINK_ID NUMBER NOT NULL,
  PRODUCTGROUP_ID           NUMBER NOT NULL,
  BILLEDQTY                 NUMBER NOT NULL,
  AMOUNT                    NUMBER NOT NULL,
  SYSTEMUSER_ID             NUMBER NOT NULL,
  TIMESTAMP                 DATE NOT NULL
)
;

ALTER TABLE INVOICELINEDP
  ADD CONSTRAINT XPKINVOICELINEDP PRIMARY KEY (INVOICELINE_ID, DISTRIBUTIONPOINT_LINK_ID, PRODUCTGROUP_ID);
  
CREATE INDEX XIF1INVOICELINEDP ON INVOICELINEDP (INVOICELINE_ID ASC);
CREATE INDEX XIF2INVOICELINEDP ON INVOICELINEDP (DISTRIBUTIONPOINT_LINK_ID ASC);
CREATE INDEX XIF3INVOICELINEDP ON INVOICELINEDP (PRODUCTGROUP_ID ASC);

ALTER TABLE INVOICELINEDP
  ADD CONSTRAINT INVOICELINEDP_IL FOREIGN KEY (INVOICELINE_ID)
  REFERENCES INVOICELINE (INVOICELINE_ID) ON DELETE CASCADE;
ALTER TABLE INVOICELINEDP
  ADD CONSTRAINT INVOICELINEDP_DPL FOREIGN KEY (DISTRIBUTIONPOINT_LINK_ID)
  REFERENCES DISTRIBUTIONPOINT_LINK (DISTRIBUTIONPOINT_LINK_ID);
ALTER TABLE INVOICELINEDP
  ADD CONSTRAINT INVOICELINEDP_PGL FOREIGN KEY (PRODUCTGROUP_ID)
  REFERENCES PRODUCTGROUP (PRODUCTGROUP_ID);
