CREATE OR REPLACE PACKAGE pkg_bicsuite_util AS
   /*
     Function: privs

     Return a string with a char for each privileg in the
     bicsuite integer representation of grants.

     Meanings of the returned chars are:

       K: CREATE
       C: CREATE_CONTENT
       P: CREATE_PARENT_CONTENT (artificial privilege)
       D: DROP
       E: EDIT
       G: GRANT (for later grant with grant option functionality)
       R: RESOURCE
       M: MONITOR
       O: OPERATE
       S: SUBMIT
       U: USE
       V: VIEW
       X: EXECUTE
   */
   FUNCTION privs (
      p_privs                             NUMBER)
      RETURN VARCHAR2;

   /*
     Function seconds2string

     Return a readable string representation like 3h5m35s
     for p_s seconds
   */
   FUNCTION seconds2string (
      p_s                                 NUMBER)
      RETURN VARCHAR2;

   /*
     Function scope_path

     return the path of the scope id given in p_id
     if p_quote is 'Y' the path elements are quoted with ' chars.
     This is usefull if the generated path will be used to generate
     sdms commands where quotes may be neccessary for case sensitive names.
   */
   FUNCTION scope_path (
      p_id                                NUMBER
    , p_quote                             CHAR DEFAULT 'N')
      RETURN VARCHAR2;

   /*
     Function scheduled_event_path

     return the path of the scheduled event id given in p_id
     if p_quote is 'Y' the path elements are quoted with ' chars.
     This is usefull if the generated path will be used to generate
     sdms commands where quotes may be neccessary for case sensitive names.
   */
   FUNCTION scheduled_event_path (
      p_id                                NUMBER
    , p_quote                             CHAR DEFAULT 'N')
      RETURN VARCHAR2;

   /*
     Function hierarchy_path

     return the heirarchy path of scheduling entity id given in p_id.
     if more than one parent is found for a scheduling entity when walking
     up the scheduling hierarchy the traversal stops and the alreaqdy build path
     with a leading <MULTIPLE> is returned.
     if p_quote is 'Y' the path elements are quoted with ''chars.
     This is usefull if the generated path will be used to generate
     sdms commands where quotes may be neccessary for case sensitive names.
   */
   FUNCTION hierarchy_path (
      p_id                                NUMBER
    , p_quote                             CHAR DEFAULT 'N')
      RETURN VARCHAR2;

   /*
     Function category_path

     return the path of a named_resource or category id given in p_id.
     if p_quote is 'Y' the path elements are quoted with ' chars.
     This is usefull if the generated path will be used to generate
     sdms commands where quotes may be neccessary for case sensitive names.
   */
   FUNCTION category_path (
      p_id                                NUMBER
    , p_quote                             CHAR DEFAULT 'N')
      RETURN VARCHAR2;

   /*
     Function folder_path

     return the path of a folder or scheduling entity id given in p_id.
     if p_quote is 'Y' the path elements are quoted with ' chars.
     This is usefull if the generated path will be used to generate
     sdms commands where quotes may be neccessary for case sensitive names.
   */
   FUNCTION folder_path (
      p_id                                NUMBER
    , p_quote                             CHAR DEFAULT 'N')
      RETURN VARCHAR2;

   /*
     Function versioned_folder_path

     return the path of a folder or scheduling entity id given in p_id
     for the version id given in p_version.
     if p_quote is 'Y' the path elements are quoted with ' chars.
     This is usefull if the generated path will be used to generate
     sdms commands where quotes may be neccessary for case sensitive names.
   */
   FUNCTION versioned_folder_path (
      p_id                                NUMBER
    , p_version                           NUMBER
    , p_quote                             CHAR DEFAULT 'N')
      RETURN VARCHAR2;
END pkg_bicsuite_util;
/

CREATE OR REPLACE PACKAGE BODY pkg_bicsuite_util AS
   
/**************************************************************************/

   /*
   Function: binary

   Translates the integer p_n to its binary representation using p_len bits.
   if p_len is null then no leading bits are produced
   */
   FUNCTION BINARY (
      p_n                                 NUMBER
    , p_len                               NUMBER DEFAULT NULL)
      RETURN VARCHAR2 AS
      v_n   NUMBER        := p_n;
      v_r   VARCHAR2 (64);
   BEGIN
      IF p_len > 64 THEN
         raise_application_error (-20000, 'p_len Parameter exceeds maximum of 64');
      END IF;

      FOR v_i IN 1 .. NVL (p_len, 64) LOOP
         IF MOD (v_n, 2) = 0 THEN
            v_r := '0' || v_r;
         ELSE
            v_r := '1' || v_r;
         END IF;

         v_n := TRUNC (v_n / 2);

         IF     p_len IS NULL
            AND v_n = 0 THEN
            EXIT;
         END IF;
      END LOOP;

      IF v_n > 0 THEN
         raise_application_error (-20000
                                ,    'binary representation of '
                                  || TRIM (TO_CHAR (p_n))
                                  || ' exceeds given length of '
                                  || TRIM (TO_CHAR (p_len)));
      END IF;

      RETURN v_r;
   END;

   
/**************************************************************************/
   /*
   Function: privs

   Translates the binary string p_binary,
   containing only '1' and '0' chars of length 64,
   into a string containing chars representing granted privileges.
   */
   FUNCTION privs (
      p_binary                            VARCHAR2)
      RETURN VARCHAR2 AS
      v_map   VARCHAR2 (64) := '---------------------------G--GE---V---U---S---O---M---E---DRPCK';
      v_r     VARCHAR2 (64);
   BEGIN
      FOR v_i IN 1 .. LENGTH (v_map) LOOP
         IF SUBSTR (p_binary, v_i, 1) = '1' THEN
            v_r := v_r || SUBSTR (v_map, v_i, 1);
         END IF;
      END LOOP;

      RETURN v_r;
   END;

   
/**************************************************************************/
   /*
   Function: privs

   Overloaded privs function translating the number p_privs in its
   binary string representation of length 64 and then calling privs(p_binary)
   */
   FUNCTION privs (
      p_privs                             NUMBER)
      RETURN VARCHAR2 AS
   BEGIN
      RETURN privs (BINARY (p_privs, 64));
   END;

   FUNCTION seconds2string (
      p_s                                 NUMBER)
      RETURN VARCHAR2 AS
      v_p   NUMBER;
      v_d   NUMBER;
      v_h   NUMBER;
      v_m   NUMBER;
      v_s   NUMBER;
      v_r   VARCHAR2 (64);
   BEGIN
      v_p := ABS (p_s);
      v_d := TRUNC (v_p / (60 * 60 * 24));
      v_p := v_p - v_d * (60 * 60 * 24);
      v_h := TRUNC (v_p / (60 * 60));
      v_p := v_p - v_h * (60 * 60);
      v_m := TRUNC (v_p / 60);
      v_p := v_p - v_m * 60;

      IF v_d > 0 THEN
         v_r := LTRIM (TO_CHAR (v_d)) || 'd';
      END IF;

      IF    v_h > 0
         OR v_r IS NOT NULL THEN
         v_r := v_r || LTRIM (TO_CHAR (v_h)) || 'h';
      END IF;

      IF    v_m > 0
         OR v_r IS NOT NULL THEN
         v_r := v_r || LTRIM (TO_CHAR (v_m)) || 'm';
      END IF;

      v_r := v_r || LTRIM (TO_CHAR (ROUND (v_p))) || 's';

      IF p_s < 0 THEN
         v_r := '-' || v_r;
      END IF;

      RETURN v_r;
   END;

   
/**************************************************************************/
   FUNCTION scope_path (
      p_id                                NUMBER
    , p_quote                             CHAR DEFAULT 'N')
      RETURN VARCHAR2 AS
      v_path        VARCHAR2 (2000);
      v_parent_id   NUMBER (38);
      v_name        VARCHAR2 (64);
   BEGIN
      BEGIN
         SELECT NAME
              , parent_id
           INTO v_path
              , v_parent_id
           FROM sci_scope
          WHERE ID = p_id;
      END;

      IF     p_quote = 'Y'
         AND v_parent_id IS NOT NULL THEN
         v_path := v_path || '''';
      END IF;

      WHILE v_parent_id IS NOT NULL LOOP
         SELECT NAME
              , parent_id
           INTO v_name
              , v_parent_id
           FROM sci_scope
          WHERE ID = v_parent_id;

         IF p_quote = 'Y' THEN
            IF v_name = 'GLOBAL' THEN
               v_path := v_name || '.''' || v_path;
            ELSE
               v_path := v_name || '''.''' || v_path;
            END IF;
         ELSE
            v_path := v_name || '.' || v_path;
         END IF;
      END LOOP;

      RETURN v_path;
   END;

   
/**************************************************************************/

   FUNCTION scheduled_event_path (
      p_id                                NUMBER
    , p_quote                             CHAR DEFAULT 'N')
      RETURN VARCHAR2 AS
      v_path          VARCHAR2 (2000);
      v_schedule_id   NUMBER (38);
      v_name          VARCHAR2 (64);
   BEGIN
      SELECT ev.NAME
           , sce.sce_id
        INTO v_path
           , v_schedule_id
        FROM sci_scheduled_event sce
           , sci_event ev
       WHERE sce.ID = p_id
         AND ev.ID = sce.evt_id;

      IF p_quote = 'Y' THEN
         v_path := v_path || '''';
      END IF;

      WHILE v_schedule_id IS NOT NULL LOOP
         SELECT NAME
              , parent_id
           INTO v_name
              , v_schedule_id
           FROM sci_schedule
          WHERE ID = v_schedule_id;

         IF p_quote = 'Y' THEN
            IF v_name = 'ROOT' THEN
               v_path := v_name || '.''' || v_path;
            ELSE
               v_path := v_name || '''.''' || v_path;
            END IF;
         ELSE
            v_path := v_name || '.' || v_path;
         END IF;
      END LOOP;

      RETURN v_path;
   END;

   
/**************************************************************************/

   FUNCTION hierarchy_path (
      p_id                                NUMBER
    , p_quote                             CHAR DEFAULT 'N')
      RETURN VARCHAR2 AS
      v_type    VARCHAR2 (16);
      v_fpath   VARCHAR2 (2000);
      v_hpath   VARCHAR2 (2000);
      v_sep     CHAR (1);
      v_id      NUMBER (38);
      v_name    VARCHAR2 (64);
   BEGIN
      v_hpath := '';
      v_sep := '';
      v_id := p_id;

      WHILE v_id IS NOT NULL LOOP
         SELECT TYPE
           INTO v_type
           FROM sci_c_scheduling_entity
          WHERE ID = v_id;

         v_fpath := folder_path (v_id, p_quote);
         v_hpath := v_type || ':' || v_fpath || v_sep || v_hpath;
         v_sep := ',';

         BEGIN
            SELECT se_parent_id
              INTO v_id
              FROM sci_c_scheduling_hierarchy
             WHERE se_child_id = v_id;
         EXCEPTION
            WHEN TOO_MANY_ROWS THEN
               v_hpath := '<MULTIPLE>' || v_sep || v_hpath;
               v_id := NULL;
            WHEN NO_DATA_FOUND THEN
               v_id := NULL;
         END;
      END LOOP;

      RETURN TRIM (v_hpath);
   END;

   
/**************************************************************************/
   FUNCTION category_path (
      p_id                                NUMBER
    , p_quote                             CHAR DEFAULT 'N')
      RETURN VARCHAR2 AS
      v_path        VARCHAR2 (2000);
      v_parent_id   NUMBER (38);
      v_name        VARCHAR2 (64);
   BEGIN
      BEGIN
         SELECT NAME
              , parent_id
           INTO v_path
              , v_parent_id
           FROM sci_named_resource
          WHERE ID = p_id;
      END;

      IF     p_quote = 'Y'
         AND v_parent_id IS NOT NULL THEN
         v_path := v_path || '''';
      END IF;

      WHILE v_parent_id IS NOT NULL LOOP
         SELECT NAME
              , parent_id
           INTO v_name
              , v_parent_id
           FROM sci_named_resource
          WHERE ID = v_parent_id;

         IF p_quote = 'Y' THEN
            IF v_name = 'RESOURCE' THEN
               v_path := v_name || '.''' || v_path;
            ELSE
               v_path := v_name || '''.''' || v_path;
            END IF;
         ELSE
            v_path := v_name || '.' || v_path;
         END IF;
      END LOOP;

      RETURN v_path;
   END;

   
/**************************************************************************/

   FUNCTION folder_path (
      p_id                                NUMBER
    , p_quote                             CHAR DEFAULT 'N')
      RETURN VARCHAR2 AS
      v_path        VARCHAR2 (2000);
      v_folder_id   NUMBER (38);
      v_name        VARCHAR2 (64);
   BEGIN
      BEGIN
         SELECT NAME
              , folder_id
           INTO v_path
              , v_folder_id
           FROM sci_c_scheduling_entity
          WHERE ID = p_id;
      EXCEPTION
         WHEN NO_DATA_FOUND THEN
            SELECT NAME
                 , parent_id
              INTO v_path
                 , v_folder_id
              FROM sci_c_folder
             WHERE ID = p_id;
      END;

      IF     p_quote = 'Y'
         AND v_folder_id IS NOT NULL THEN
         v_path := v_path || '''';
      END IF;

      WHILE v_folder_id IS NOT NULL LOOP
         SELECT NAME
              , parent_id
           INTO v_name
              , v_folder_id
           FROM sci_c_folder
          WHERE ID = v_folder_id;

         IF p_quote = 'Y' THEN
            IF v_name = 'SYSTEM' THEN
               v_path := v_name || '.''' || v_path;
            ELSE
               v_path := v_name || '''.''' || v_path;
            END IF;
         ELSE
            v_path := v_name || '.' || v_path;
         END IF;
      END LOOP;

      RETURN v_path;
   END;

   
/**************************************************************************/

   FUNCTION versioned_folder_path (
      p_id                                NUMBER
    , p_version                           NUMBER
    , p_quote                             CHAR DEFAULT 'N')
      RETURN VARCHAR2 AS
      v_path        VARCHAR2 (2000);
      v_folder_id   NUMBER (38);
      v_name        VARCHAR2 (64);
   BEGIN
      BEGIN
         SELECT NAME
              , folder_id
           INTO v_path
              , v_folder_id
           FROM sci_v_scheduling_entity
          WHERE ID = p_id
            AND p_version BETWEEN valid_from AND valid_to - 1;
      EXCEPTION
         WHEN NO_DATA_FOUND THEN
            SELECT NAME
                 , parent_id
              INTO v_path
                 , v_folder_id
              FROM sci_v_folder
             WHERE ID = p_id
               AND p_version BETWEEN valid_from AND valid_to - 1;
      END;

      IF     p_quote = 'Y'
         AND v_folder_id IS NOT NULL THEN
         v_path := v_path || '''';
      END IF;

      WHILE v_folder_id IS NOT NULL LOOP
         SELECT NAME
              , parent_id
           INTO v_name
              , v_folder_id
           FROM sci_v_folder
          WHERE ID = v_folder_id
            AND p_version BETWEEN valid_from AND valid_to - 1;

         IF p_quote = 'Y' THEN
            IF v_name = 'SYSTEM' THEN
               v_path := v_name || '.''' || v_path;
            ELSE
               v_path := v_name || '''.''' || v_path;
            END IF;
         ELSE
            v_path := v_name || '.' || v_path;
         END IF;
      END LOOP;

      RETURN v_path;
   END;
END pkg_bicsuite_util;
/
