--ABS-106405

----- BUSINESSUNITEXTRAINFO ------

CREATE TABLE BUSINESSUNITEXTRAINFO
(
  BUSINESSUNIT_ID NUMBER NOT NULL,
  DETERMINESTOCKROOMUPONRETURN VARCHAR2(1) DEFAULT 'Y' NOT NULL CONSTRAINT CHK_BUEXTRA_DETSTOCK CHECK (DETERMINESTOCKROOMUPONRETURN  IN ('Y','N')),
  SYSTEMUSER_ID  NUMBER NOT NULL,
  TIMESTAMP      DATE NOT NULL
);

ALTER TABLE BUSINESSUNITEXTRAINFO
  ADD CONSTRAINT XPKBUSINESSUNITEXTRAINFO PRIMARY KEY (BUSINESSUNIT_ID);
ALTER TABLE BUSINESSUNITEXTRAINFO
  ADD CONSTRAINT FK_BUEXTRA_BU FOREIGN KEY (BUSINESSUNIT_ID)
  REFERENCES BUSINESSUNIT(BUSINESSUNIT_ID) ON DELETE CASCADE;

CREATE OR REPLACE PROCEDURE INSERT_BUSINESSUNITEXTRAINFO AS
STMT VARCHAR2(5000);
BEGIN
  FOR X IN (SELECT BU.BUSINESSUNIT_ID FROM BUSINESSUNIT BU WHERE BU.BUSINESSUNIT_ID NOT IN (SELECT BUEI.BUSINESSUNIT_ID FROM BUSINESSUNITEXTRAINFO BUEI)) LOOP
    BEGIN
STMT := 'INSERT INTO BUSINESSUNITEXTRAINFO
(BUSINESSUNIT_ID,
DETERMINESTOCKROOMUPONRETURN,
SYSTEMUSER_ID,
TIMESTAMP)
      SELECT '||X.BUSINESSUNIT_ID||', ''Y'', GETMINSYSTEMUSER(), SYSDATE FROM DUAL';
 EXECUTE IMMEDIATE(STMT);
 EXCEPTION WHEN OTHERS  THEN NULL;
 COMMIT;
 END;
END LOOP;
END INSERT_BUSINESSUNITEXTRAINFO;
/
EXECUTE INSERT_BUSINESSUNITEXTRAINFO;

DROP PROCEDURE  INSERT_BUSINESSUNITEXTRAINFO;