--ABS-104005
-----WORKSTATIONEXTRAINFO ------

CREATE TABLE WORKSTATIONEXTRAINFO
(
  WORKSTATION_ID NUMBER NOT NULL,
  ALLOWCHANGEGDNDELDATE NUMBER DEFAULT 0 NOT NULL CONSTRAINT CHK_ALLCHGGDN_WOST CHECK (ALLOWCHANGEGDNDELDATE  IN (0, 1, 2)),
  SYSTEMUSER_ID  NUMBER NOT NULL,
  TIMESTAMP      DATE NOT NULL
);

ALTER TABLE WORKSTATIONEXTRAINFO
  ADD CONSTRAINT XPKWORKSTATIONEXTRAINFO PRIMARY KEY (WORKSTATION_ID );
ALTER TABLE WORKSTATIONEXTRAINFO
  ADD CONSTRAINT FK_WOEXTRAINF_WOST FOREIGN KEY (WORKSTATION_ID )
  REFERENCES WORKSTATION(WORKSTATION_ID ) ON DELETE CASCADE;

CREATE OR REPLACE PROCEDURE INSERT_WORKSTATIONEXTRAINFO AS
STMT VARCHAR2(5000);
BEGIN
STMT := 'INSERT INTO WORKSTATIONEXTRAINFO
(WORKSTATION_ID,
ALLOWCHANGEGDNDELDATE,
SYSTEMUSER_ID,
TIMESTAMP)
      SELECT W.WORKSTATION_ID, 0, GETMINSYSTEMUSER(), SYSDATE  FROM WORKSTATION W WHERE W.WORKSTATION_ID NOT IN (SELECT WE.WORKSTATION_ID FROM WORKSTATIONEXTRAINFO WE)';
 EXECUTE IMMEDIATE(STMT);
COMMIT;
 EXCEPTION WHEN OTHERS  THEN NULL;
 END INSERT_WORKSTATIONEXTRAINFO;
/
EXECUTE INSERT_WORKSTATIONEXTRAINFO;

DROP PROCEDURE  INSERT_WORKSTATIONEXTRAINFO;
