/*order 502098*/      

     CREATE OR REPLACE FUNCTION lts_exists(p_locationtype_id_ IN NUMBER,
                                      p_stockroom_id_    IN NUMBER) RETURN NUMBER
IS
  f_result_ NUMBER := -1;
  CURSOR getlocationtypeperstockroom IS
    SELECT count(*)
    FROM LOCATIONTYPEPERSTOCKROOM lts
    WHERE lts.LOCATIONTYPE_ID = p_locationtype_id_
    AND lts.STOCKROOM_ID = p_stockroom_id_;
BEGIN
   OPEN getlocationtypeperstockroom;
   FETCH getlocationtypeperstockroom INTO f_result_;
   CLOSE getlocationtypeperstockroom;
   RETURN nvl(f_result_, 0);
END lts_exists;
/
CREATE OR REPLACE FUNCTION lts_has_default(p_stockroom_id_ IN NUMBER) RETURN NUMBER
IS
  f_result_ NUMBER := -1;
  CURSOR hasdefaultlocationtype IS
    SELECT count(*)
    FROM LOCATIONTYPEPERSTOCKROOM lts
    WHERE lts.STOCKROOM_ID = p_stockroom_id_
    AND lts.DEFAULTLOCATION = 'Y';
BEGIN
   OPEN hasdefaultlocationtype;
   FETCH hasdefaultlocationtype INTO f_result_;
   CLOSE hasdefaultlocationtype;
   RETURN nvl(f_result_, 0);
END lts_has_default;
/

create or replace procedure toBeRun is
  CURSOR getlocationtypes IS
    SELECT *
    FROM LOCATIONTYPE lt
    WHERE lt.ACTIVE = 'Y'
    AND lt.CODE IN ('PICK','OVERFLOW')
    ORDER BY lt.LOCATIONTYPE_ID;

  CURSOR getstockrooms IS
    SELECT *
    FROM STOCKROOM SR
    ORDER BY sr.STOCKROOM_ID;

  result_          NUMBER := -1;
  hasdefault_      NUMBER := -1;
  default_         VARCHAR2(1) := 'N';
BEGIN
   FOR srrec_ IN getstockrooms LOOP
      FOR ltrec_ IN getlocationtypes LOOP
         -- Check if current location type has alreday been defined for current stockroom
         result_ := lts_exists(ltrec_.locationtype_id, srrec_.stockroom_id);
         -- Only add location type when it does not exist yet
         IF (result_ = 0) THEN
            BEGIN
               -- Check if there is already a default location type defined
               hasdefault_ := lts_has_default(srrec_.stockroom_id);
               IF (hasdefault_ = 0) THEN
                  default_ := 'Y';
               ELSE
                  default_ := 'N';
               END IF;
               -- Actually create location type for stockroom
               INSERT INTO LOCATIONTYPEPERSTOCKROOM
                 (LOCATIONTYPE_ID, STOCKROOM_ID, DEFAULTLOCATION, PICKINGLOCATION,
                  VALUECONTRIBUTION, PRIORITY, SYSTEMUSER_ID, TIMESTAMP)
               VALUES
                 (ltrec_.LOCATIONTYPE_ID, srrec_.STOCKROOM_ID, default_, ltrec_.PICKINGLOCATION,
                  ltrec_.VALUECONTRIBUTION, ltrec_.PRIORITY, getminsystemuser(), sysdate);
            END;
         END IF;
      END LOOP;
   END LOOP;
END toBeRun;
/

execute toBeRun ;

drop procedure toBeRun;

DROP FUNCTION lts_has_default;

DROP FUNCTION lts_exists;

