-- The below constraint (ROUTE_DISTRICT_ID_PLANT_ID) is missing on some databases
BEGIN
EXECUTE IMMEDIATE ('ALTER TABLE ROUTE
  DROP CONSTRAINT ROUTE_DISTRICT_ID');
EXCEPTION 
  WHEN OTHERS THEN NULL;
END;
/

BEGIN
EXECUTE IMMEDIATE ('ALTER TABLE ROUTE
  DROP CONSTRAINT ROUTE_DISTRICT_ID_PLANT_ID');
EXCEPTION 
  WHEN OTHERS THEN NULL;
END;
/


ALTER TABLE ROUTE ADD CONSTRAINT ROUTE_DISTRICT_ID FOREIGN KEY (DISTRICT_ID)
REFERENCES DISTRICT (DISTRICT_ID);

INSERT INTO DISTRICTPLANT(district_id, plant_id,employee_id,systemuser_id, timestamp)
SELECT dd.district_id,
       pl.plant_id,
       nvl((select min(e.employee_id) from employee e where e.plant_id = pl.plant_id),(select min(employee_id) from employee)),
       getminsystemuser(),
       sysdate
  FROM (SELECT DISTRICT_ID FROM DISTRICT WHERE CODE = '-') dd,
       (SELECT PLANT_ID FROM PLANT) pl
	   where not exists ( select 1 
	   from districtplant x
	   where x.district_id=dd.district_id
	   and x.plant_id=pl.plant_id);


ALTER TABLE ROUTE ADD CONSTRAINT ROUTE_DISTRICT_ID_PLANT_ID FOREIGN KEY (DISTRICT_ID, PLANT_ID)
REFERENCES DISTRICTPLANT (DISTRICT_ID, PLANT_ID);
