-- ABS-94768/ ABS-117795

--- CONTRACTCUSTOMERGAINLOSS TABLE ---
CREATE TABLE CONTRACTCUSTOMERGAINLOSS
(
  CONTRACTCUSTOMER_ID NUMBER NOT NULL,
  SALESEMPLOYEE_ID    NUMBER NULL,
  SALESEMPLOYEE2_ID   NUMBER NULL,
  GAINCOMPETITOR_ID   NUMBER NULL,
  GAINREASON_ID       NUMBER NULL,
  LOSTCOMPETITOR_ID   NUMBER NULL,
  LOSTREASON_ID       NUMBER NULL,
  SYSTEMUSER_ID       NUMBER NOT NULL,
  TIMESTAMP           DATE NOT NULL
)
;

ALTER TABLE CONTRACTCUSTOMERGAINLOSS
  ADD CONSTRAINT XPKCONTRACTCUSTOMERGAINLOSS PRIMARY KEY (CONTRACTCUSTOMER_ID);
  
CREATE INDEX XIF1CONTRACTCUSTOMERGAINLOSS ON CONTRACTCUSTOMERGAINLOSS (SALESEMPLOYEE_ID ASC);
CREATE INDEX XIF2CONTRACTCUSTOMERGAINLOSS ON CONTRACTCUSTOMERGAINLOSS (SALESEMPLOYEE2_ID ASC);
CREATE INDEX XIF3CONTRACTCUSTOMERGAINLOSS ON CONTRACTCUSTOMERGAINLOSS (GAINCOMPETITOR_ID ASC);
CREATE INDEX XIF4CONTRACTCUSTOMERGAINLOSS ON CONTRACTCUSTOMERGAINLOSS (GAINREASON_ID ASC);
CREATE INDEX XIF5CONTRACTCUSTOMERGAINLOSS ON CONTRACTCUSTOMERGAINLOSS (LOSTCOMPETITOR_ID ASC);
CREATE INDEX XIF6CONTRACTCUSTOMERGAINLOSS ON CONTRACTCUSTOMERGAINLOSS (LOSTREASON_ID ASC);

ALTER TABLE CONTRACTCUSTOMERGAINLOSS
  ADD CONSTRAINT FK_CONTRCUSTGAINLOSS_CONTRCUST FOREIGN KEY (CONTRACTCUSTOMER_ID)
  REFERENCES CONTRACTCUSTOMER (CONTRACTCUSTOMER_ID) ON DELETE CASCADE;
  
ALTER TABLE CONTRACTCUSTOMERGAINLOSS
  ADD CONSTRAINT FK_CONTRCUSTGAINLOSS_SALESEMP1 FOREIGN KEY (SALESEMPLOYEE_ID)
  REFERENCES EMPLOYEE (EMPLOYEE_ID);
  
ALTER TABLE CONTRACTCUSTOMERGAINLOSS
  ADD CONSTRAINT FK_CONTRCUSTGAINLOSS_SALESEMP2 FOREIGN KEY (SALESEMPLOYEE2_ID)
  REFERENCES EMPLOYEE (EMPLOYEE_ID);
  
ALTER TABLE CONTRACTCUSTOMERGAINLOSS
  ADD CONSTRAINT FK_CONTRCUSTGAINLOSS_GAINCOMP FOREIGN KEY (GAINCOMPETITOR_ID)
  REFERENCES COMPETITOR (COMPETITOR_ID);
  
ALTER TABLE CONTRACTCUSTOMERGAINLOSS
  ADD CONSTRAINT FK_CONTRCUSTGAINLOSS_GAINREASON FOREIGN KEY (GAINREASON_ID)
  REFERENCES GAINLOSTREASON (GAINLOSTREASON_ID);
  
ALTER TABLE CONTRACTCUSTOMERGAINLOSS
  ADD CONSTRAINT FK_CONTRCUSTGAINLOSS_LOSTCOMP FOREIGN KEY (LOSTCOMPETITOR_ID)
  REFERENCES COMPETITOR (COMPETITOR_ID);
  
ALTER TABLE CONTRACTCUSTOMERGAINLOSS
  ADD CONSTRAINT FK_CONTRCUSTGAINLOSS_LOSTREASON FOREIGN KEY (LOSTREASON_ID)
  REFERENCES GAINLOSTREASON (GAINLOSTREASON_ID);
