-- ABS-31454
---------------   SERVICECHARGEEVENT   ---------------
CREATE TABLE SERVICECHARGEEVENT
(
SERVICECHARGEEVENT_ID NUMBER NOT NULL,
FACTORYDESCRIPTION VARCHAR2(60) NOT NULL,
SYSTEMUSER_ID NUMBER NOT NULL,
TIMESTAMP DATE NOT NULL
);

ALTER TABLE SERVICECHARGEEVENT ADD CONSTRAINT XPKSERVICECHARGEEVENT PRIMARY KEY (SERVICECHARGEEVENT_ID);

INSERT INTO SERVICECHARGEEVENT (SERVICECHARGEEVENT_ID,FACTORYDESCRIPTION,SYSTEMUSER_ID,TIMESTAMP) VALUES ('1','Rag LOG items with RV',GETMINSYSTEMUSER(),SYSDATE);
COMMIT;

INSERT INTO SERVICECHARGEEVENT (SERVICECHARGEEVENT_ID,FACTORYDESCRIPTION,SYSTEMUSER_ID,TIMESTAMP) VALUES ('2','Rag LOG items without RV',GETMINSYSTEMUSER(),SYSDATE);
COMMIT;

INSERT INTO SERVICECHARGEEVENT (SERVICECHARGEEVENT_ID,FACTORYDESCRIPTION,SYSTEMUSER_ID,TIMESTAMP) VALUES ('3','Rag COG items',GETMINSYSTEMUSER(),SYSDATE);
COMMIT;

INSERT INTO SERVICECHARGEEVENT (SERVICECHARGEEVENT_ID,FACTORYDESCRIPTION,SYSTEMUSER_ID,TIMESTAMP) VALUES ('4','Return LOG items to stock with RV',GETMINSYSTEMUSER(),SYSDATE);
COMMIT;

INSERT INTO SERVICECHARGEEVENT (SERVICECHARGEEVENT_ID,FACTORYDESCRIPTION,SYSTEMUSER_ID,TIMESTAMP) VALUES ('5','Return LOG items to stock without RV',GETMINSYSTEMUSER(),SYSDATE);
COMMIT;

INSERT INTO SERVICECHARGEEVENT (SERVICECHARGEEVENT_ID,FACTORYDESCRIPTION,SYSTEMUSER_ID,TIMESTAMP) VALUES ('6','Return COG items to stock',GETMINSYSTEMUSER(),SYSDATE);
COMMIT;


---------------   SERVICECHARGEEVENT_DESC   ---------------
CREATE TABLE SERVICECHARGEEVENT_DESC
(
SERVICECHARGEEVENT_ID NUMBER NOT NULL,
LANGUAGE_ID NUMBER NOT NULL,
DESCRIPTION VARCHAR2(60) NOT NULL,
SYSTEMUSER_ID NUMBER NOT NULL,
TIMESTAMP DATE NOT NULL
);

ALTER TABLE SERVICECHARGEEVENT_DESC ADD CONSTRAINT XPKSERVICECHARGEEVENT_DESC PRIMARY KEY
(SERVICECHARGEEVENT_ID, LANGUAGE_ID);

CREATE INDEX XIF1SERVICECHARGEEVENT_DESC ON SERVICECHARGEEVENT_DESC (SERVICECHARGEEVENT_ID ASC);

CREATE INDEX XIF2SERVICECHARGEEVENT_DESC ON SERVICECHARGEEVENT_DESC (LANGUAGE_ID DESC);

ALTER TABLE SERVICECHARGEEVENT_DESC ADD CONSTRAINT SERVICECHARGEEVENT_DESC_SCE FOREIGN KEY
(SERVICECHARGEEVENT_ID) REFERENCES SERVICECHARGEEVENT (SERVICECHARGEEVENT_ID);

ALTER TABLE SERVICECHARGEEVENT_DESC ADD CONSTRAINT SERVICECHARGEEVENT_DESC_LID FOREIGN KEY
(LANGUAGE_ID) REFERENCES LANGUAGES (LANGUAGE_ID);


---------------   BUSERVICECHARGEEVENT   -------------
CREATE SEQUENCE SEQ_BUSERVICECHARGEEVENT
START WITH 1
INCREMENT BY 1;

CREATE TABLE BUSERVICECHARGEEVENT
(
BUSERVICECHARGEEVENT_ID NUMBER NOT NULL,
BUSINESSUNIT_ID NUMBER NOT NULL,
SERVICECHARGEEVENT_ID NUMBER NOT NULL,
VARIABLESERVICE_ID NUMBER NULL,
SYSTEMUSER_ID NUMBER NOT NULL,
TIMESTAMP DATE NOT NULL
);

ALTER TABLE BUSERVICECHARGEEVENT ADD CONSTRAINT XPKBUSERVICECHARGEEVENT PRIMARY KEY (BUSERVICECHARGEEVENT_ID);

CREATE UNIQUE INDEX XAK1BUSERVICECHARGEEVENT ON BUSERVICECHARGEEVENT(BUSINESSUNIT_ID ASC, SERVICECHARGEEVENT_ID ASC);

CREATE INDEX XIF1BUSERVICECHARGEEVENT ON BUSERVICECHARGEEVENT (BUSINESSUNIT_ID ASC);

CREATE INDEX XIF2BUSERVICECHARGEEVENT ON BUSERVICECHARGEEVENT (SERVICECHARGEEVENT_ID ASC);

CREATE INDEX XIF3BUSERVICECHARGEEVENT ON BUSERVICECHARGEEVENT (VARIABLESERVICE_ID ASC);

ALTER TABLE BUSERVICECHARGEEVENT ADD CONSTRAINT BUSERVICECHARGEEVENT_BU FOREIGN KEY
(BUSINESSUNIT_ID) REFERENCES BUSINESSUNIT (BUSINESSUNIT_ID) ON DELETE CASCADE;

ALTER TABLE BUSERVICECHARGEEVENT ADD CONSTRAINT BUSERVICECHARGEEVENT_SCE FOREIGN KEY
(SERVICECHARGEEVENT_ID) REFERENCES SERVICECHARGEEVENT (SERVICECHARGEEVENT_ID)
ON DELETE CASCADE;

ALTER TABLE BUSERVICECHARGEEVENT ADD CONSTRAINT BUSERVICECHARGEEVENT_VARSERV FOREIGN KEY
(VARIABLESERVICE_ID) REFERENCES REPAIRTYPE (REPAIRTYPE_ID) ON DELETE CASCADE;



