-- order 502809

DROP INDEX XIF1060WORKSTATION;
      

      /*
      CHANGE REPORT for Table WORKSTATION
          - change unknown attribute from 93 to (empty), change null constraint from NULL to NOT NULL of column PLANTDEPARTMENT_ID
          WARNING : Load data or ALTER statement for table WORKSTATION may fail (existing data may violate the new column rules: changing column PLANTDEPARTMENT_ID to NOT NULL without DEFAULT).
          - Adding column DELNOTEALLPLANTDEPARTMENTS
      ACTION is ALTER or modify Table WORKSTATION
      IMPACT ANALYSIS REPORT for ALTER or modify Table WORKSTATION
          - index XIF1060WORKSTATION needs to be removed in order to proceed. 
      */


ALTER TABLE WORKSTATION ADD (DELNOTEALLPLANTDEPARTMENTS NUMBER DEFAULT 1 NOT 
    NULL
                                   CONSTRAINT DelNoteAllPlantDepart_Valid
                                          CHECK (DELNOTEALLPLANTDEPARTMENTS IN 
    (0, 1)));


UPDATE WORKSTATION W
SET W.DELNOTEALLPLANTDEPARTMENTS = 0
WHERE W.PLANTDEPARTMENT_ID IS NOT NULL;

commit;

UPDATE WORKSTATION W
SET    W.PLANTDEPARTMENT_ID = COALESCE((SELECT MIN(PDD.PLANTDEPARTMENT_ID)
                                        FROM   WORKSTATION     WO,
                                               PLANTDEPARTMENT PDD
                                        WHERE  WO.PLANT_ID = PDD.PLANT_ID
                                               AND
                                               PDD.DEFAULTPLANTDEPARTMENT = 'Y'),
                                (SELECT MIN(PLANTDEPARTMENT_ID)
                                FROM   PLANTDEPARTMENT))
WHERE W.PLANTDEPARTMENT_ID IS NULL;

commit;


ALTER TABLE WORKSTATION MODIFY (PLANTDEPARTMENT_ID NOT NULL);


CREATE INDEX XIF1060WORKSTATION ON WORKSTATION
(
       PLANTDEPARTMENT_ID             ASC
);


CREATE OR REPLACE PROCEDURE checkPlantWithoutPlantDepart IS
--select all plants that don't have a plantDepartment defined
CURSOR plantWithoutPlantDepart IS 
SELECT p.plant_id
FROM plant p
WHERE p.plant_id NOT  IN (SELECT plant_id
FROM plantdepartment);
BEGIN
--insert a generic plantDepartment for all the selected plants
FOR pd IN plantWithoutPlantDepart LOOP
INSERT INTO plantdepartment
VALUES (seq_plantdepartment.nextval,'-','-',getminsystemuser(),pd.plant_id ,getcurrentdatetime(),'Y','N');
END LOOP;
END checkPlantWithoutPlantDepart; 
/

EXECUTE checkPlantWithoutPlantDepart;

DROP PROCEDURE checkPlantWithoutPlantDepart;