Subversion Repositories DevTools

CREATE PACKAGE BODY "RELEASE_MANAGER"."PK_ENVIRONMENT" 
IS
/*
------------------------------
||  Last Modified:  Rupesh Solanki
||  Modified Date:  30/Aug/2007
||  Body Version:   1.2
------------------------------
*/

   /*-------------------------------------------------------------------------------------------------------*/
   FUNCTION select_environment_area (cdlock IN package_versions.dlocked%TYPE)
      RETURN NUMBER
   IS
   BEGIN
       /*
      || N - unlocked
      || Y - release and locked
      || P - penging approval
      || A - approved package ready for auto-build
      */
      IF cdlock = 'N' OR cdlock = 'R'
      THEN
         -- WORK IN PROGRESS --
         RETURN 0;
      ELSIF cdlock = 'P' OR cdlock = 'A'
      THEN
         -- PENDING --
         RETURN 1;
      ELSIF cdlock = 'Y'
      THEN
         -- RELEASED --
         RETURN 2;
      ELSE
         -- NOT FOUND --
         raise_application_error
                         (-20000,
                             'Cannot decide where to place package. [cDlock='
                          || cdlock
                          || ']'
                         );
      END IF;
   END;

/*-------------------------------------------------------------------------------------------------------*/
   FUNCTION get_package_area (pvid IN NUMBER, rtagid IN NUMBER)
      RETURN NUMBER
   IS
      envtab    NUMBER            := -1;

      CURSOR curarea
      IS
         SELECT 2 AS envtab
           FROM release_content rc
          WHERE rc.rtag_id = rtagid AND rc.pv_id = pvid
         UNION
         SELECT 0 AS envtab
           FROM work_in_progress wip
          WHERE wip.rtag_id = rtagid AND wip.pv_id = pvid
         UNION
         SELECT 1 AS envtab
           FROM planned pl
          WHERE pl.rtag_id = rtagid AND pl.pv_id = pvid;

      recarea   curarea%ROWTYPE;
   BEGIN
      OPEN curarea;

      FETCH curarea
       INTO recarea;

      IF curarea%FOUND
      THEN
         envtab := recarea.envtab;
      END IF;

      CLOSE curarea;

      RETURN envtab;
   END;

/*-------------------------------------------------------------------------------------------------------*/
   FUNCTION get_view_location (pvid IN NUMBER, rtagid IN NUMBER)
      RETURN NUMBER
   IS
      ispatch   package_versions.dlocked%TYPE;
      viewid    NUMBER                          := -1;

      CURSOR curview
      IS
         SELECT rc.base_view_id AS view_id
           FROM release_content rc
          WHERE rc.rtag_id = rtagid AND rc.pv_id = pvid
         UNION
         SELECT wip.view_id AS view_id
           FROM work_in_progress wip
          WHERE wip.rtag_id = rtagid AND wip.pv_id = pvid
         UNION
         SELECT pl.view_id AS view_id
           FROM planned pl
          WHERE pl.rtag_id = rtagid AND pl.pv_id = pvid;

      recview   curview%ROWTYPE;
   BEGIN
      -- Get dlock state
      SELECT pv.is_patch
        INTO ispatch
        FROM package_versions pv
       WHERE pv.pv_id = pvid;

      -- Decide which view id should package go under.
      IF (ispatch != 'Y') OR (ispatch IS NULL)
      THEN
         -- Get VIEW ID of Package
         OPEN curview;

         FETCH curview
          INTO recview;

         IF curview%FOUND
         THEN
            viewid := recview.view_id;
         ELSE
            raise_application_error
               (-20000,
                   'Cannot find view_id to proceed. [PvId='
                || pvid
                || ']. The current version may not exist in the release anymore.'
               );
         END IF;

         CLOSE curview;
      ELSE
         -- Get VIEW ID of Patch (view id of parent package)
         SELECT rc.base_view_id
           INTO viewid
           FROM release_content rc, package_patches ppv
          WHERE rc.rtag_id = rtagid
            AND rc.pv_id = ppv.pv_id
            AND ppv.patch_id = pvid;
      END IF;

      RETURN viewid;
   END;

/*-------------------------------------------------------------------------------------------------------*/
   PROCEDURE add_package (
      pvid     IN   NUMBER,
      viewid   IN   NUMBER,
      rtagid   IN   NUMBER,
      userid   IN   NUMBER
   )
   IS
      dlocked   package_versions.dlocked%TYPE;
      envtab    NUMBER;
   BEGIN
      IF can_edit_pkg_in_project (pvid, rtagid) = 1
      THEN
         -- Get dlock state
         SELECT pv.dlocked
           INTO dlocked
           FROM package_versions pv
          WHERE pv.pv_id = pvid;

         -- Get which area should go under
         envtab := select_environment_area (dlocked);
         -- Log
         log_action (pvid, 'action', userid, 'Start of Package Add...');

         -- Remove Package
         IF envtab = 0
         THEN
            -- WORK IN PROGRESS --
            pk_work_in_progress.add_package (pvid, viewid, rtagid, userid);
         ELSIF envtab = 1
         THEN
            -- PENDING --
            pk_planned.add_package (pvid, viewid, rtagid, userid);
         ELSIF envtab = 2
         THEN
            -- RELEASED --
            -- NOTE: this package will be replaced with matching package
            pk_release.add_package (pvid, viewid, rtagid, userid);
            -- Now do post Release Actions
            pk_release.run_post_actions (pvid, rtagid);
         END IF;

         -- Log
         log_action (pvid, 'action', userid, 'End of Package Add...');
      END IF;
   END;

/*-------------------------------------------------------------------------------------------------------*/
   PROCEDURE add_package_bulk (
      pvidlist   IN   VARCHAR2,
      viewid     IN   NUMBER,
      rtagid     IN   NUMBER,
      userid     IN   NUMBER
   )
   IS
      nidcollector   relmgr_number_tab_t            := relmgr_number_tab_t
                                                                          ();
      dlocked        package_versions.dlocked%TYPE;
      pvid           NUMBER;
      envtab         NUMBER;
   BEGIN
      /*--------------- Business Rules Here -------------------*/
      IF (pvidlist IS NULL)
      THEN
         raise_application_error (-20000,
                                  'Please select at least one package.'
                                 );
      END IF;

/*-------------------------------------------------------*/
      nidcollector := in_list_number (pvidlist);

      FOR i IN 1 .. nidcollector.COUNT
      LOOP
         pvid := nidcollector (i);
         add_package (pvid, viewid, rtagid, userid);
      END LOOP;
   END;

/*-------------------------------------------------------------------------------------------------------*/
   PROCEDURE replace_package (
      newpvid   IN   NUMBER,
      oldpvid   IN   NUMBER,
      rtagid    IN   NUMBER,
      userid    IN   NUMBER
   )
   IS
      dlocked        package_versions.dlocked%TYPE;
      viewid         NUMBER;
      envtab         NUMBER;
      ROWCOUNT       NUMBER;
      creleasemode   CHAR (1);
      npkgid         NUMBER;
   BEGIN
      /*--------------- Business Rules Here -------------------*/
      -- Check if oldPvId exists. It could have been removed
      SELECT COUNT (pv.pv_id)
        INTO ROWCOUNT
        FROM package_versions pv
       WHERE pv.pv_id = oldpvid;

/*-------------------------------------------------------*/

      /* This procedure is usually used by "History" option in Release Manager */

      -- Get dlock state
      SELECT pv.dlocked
        INTO dlocked
        FROM package_versions pv
       WHERE pv.pv_id = newpvid;

      -- Get VIEW_ID ---
      IF ROWCOUNT = 1
      THEN
         viewid := get_view_location (oldpvid, rtagid);
      ELSE
         -- Set ViewID to default
         viewid := 7;
      END IF;

      -- Get which area should go under
      envtab := select_environment_area (dlocked);
      -- Log
      log_action (oldpvid, 'action', userid, 'Start of Package Replace...');

      -- Replace package
      IF envtab = 0
      THEN
         -- WORK IN PROGRESS --

         -- Delete old package
         pk_work_in_progress.remove_package (oldpvid, rtagid, userid);
         -- Add new package
         pk_work_in_progress.add_package (newpvid, viewid, rtagid, userid);
      ELSIF envtab 
= 1
      THEN
         -- PENDING --

         -- Delete old package
         pk_planned.remove_package (oldpvid, rtagid, userid);
         -- Add new package
         pk_planned.add_package (newpvid, viewid, rtagid, userid);
      ELSIF envtab = 2
      THEN
         -- RELEASED --

         -- Delete old package
         pk_release.remove_package (oldpvid, rtagid, userid);
         -- Add new package
         pk_release.add_package (newpvid, viewid, rtagid, userid);
         -- Now do post Release Actions
         pk_release.run_post_actions (newpvid, rtagid);
      END IF;

      -- Log
      log_action (oldpvid, 'action', userid, 'End of Package Replace...');
   END;

/*-------------------------------------------------------------------------------------------------------*/
   FUNCTION remove_package (
      pvid          IN   NUMBER,
      rtagid        IN   NUMBER,
      userid        IN   NUMBER,
      forceremove   IN   CHAR
   )
      RETURN NUMBER
   IS
      envtab        NUMBER;
      isused        BOOLEAN;
      recordcount   NUMBER;
   BEGIN
/*--------------- Business Rules Here -------------------*/
/*-------------------------------------------------------*/

      -- Find location of package
      envtab := get_package_area (pvid, rtagid);

      -- Remove Package
      IF envtab = 0
      THEN
         -- WORK IN PROGRESS --
         -- Delete package
         pk_work_in_progress.remove_package (pvid, rtagid, userid);
         RETURN 0;
      ELSIF envtab = 1
      THEN
         -- PENDING --
         -- Delete package
         pk_planned.remove_package (pvid, rtagid, userid);
         RETURN 0;
      ELSIF envtab = 2
      THEN
         -- RELEASED --

         -- Check if is used by other packages
         isused := TRUE;

         IF forceremove = 'N'
         THEN
            SELECT COUNT (pv.pv_id)
              INTO recordcount
              FROM (SELECT dpv.pkg_id, dpv.v_ext
                      FROM release_content rc,
                           package_dependencies dep,
                           package_versions dpv
                     WHERE rc.rtag_id = rtagid
                       AND rc.pv_id = dep.pv_id
                       AND dep.dpv_id = dpv.pv_id) rdep,
                   package_versions pv
             WHERE pv.pkg_id = rdep.pkg_id
               AND NVL (pv.v_ext, '|LINK_A_NULL|') =
                                             NVL (rdep.v_ext, '|LINK_A_NULL|')
               AND pv.pv_id = pvid;

            IF recordcount > 0
            THEN
               RETURN 1;                    -- Return 1 as package being used
            ELSE
               isused := FALSE;
            END IF;
         END IF;

         IF forceremove = 'Y' OR NOT isused
         THEN
            -- Delete old package
            pk_release.remove_package (pvid, rtagid, userid);
            -- Now do post Release Actions
            pk_release.run_post_actions (pvid, rtagid);
            RETURN 0;
         END IF;
      END IF;
   END;

/*-------------------------------------------------------------------------------------------------------*/
   PROCEDURE get_environment_items (
      viewtype           IN       NUMBER,
      userid             IN       NUMBER,
      rtagid             IN       NUMBER,
      sviewidshowlist    IN       VARCHAR2,
      ntruerecordcount   OUT      NUMBER,
      recordset          OUT      typecur
   )
   IS
   BEGIN
      -- Get true record count because views can give false count
      SELECT COUNT (pl.pv_id)
        INTO ntruerecordcount
        FROM environment_view pl
       WHERE pl.rtag_id = rtagid;

      IF viewtype = 1
      THEN
         /*--- GUEST VIEW ---*/
         OPEN recordset FOR
            SELECT   *
                FROM (
                      /* Base Views collapsed */
                      SELECT DISTINCT vi.view_id, vi.view_name,
                                      TO_NUMBER (NULL) AS pkg_state,
                                      TO_NUMBER (NULL) AS deprecated_state,
                                      TO_NUMBER (NULL) AS pv_id,
                                      NULL AS pkg_name, NULL AS pkg_version,
                                      NULL AS dlocked, NULL AS pv_description
                                 FROM environment_view rel, views vi
                                WHERE rel.view_id = vi.view_id
                                  AND rtag_id = rtagid
                                  AND rel.view_id NOT IN (
                                         SELECT *
                                           FROM THE
                                                   (SELECT CAST
                                                              (in_list_number
                                                                  (sviewidshowlist
                                                                  ) AS relmgr_number_tab_t
                                                              )
                                                      FROM DUAL
                                                   ))
                      UNION
                      /* Base Views expanded */
                      SELECT vi.view_id, vi.view_name,
                             DECODE (rel.pkg_state,
                                     NULL, 0,
                                     rel.pkg_state
                                    ) AS pkg_state,
                             rel.deprecated_state, pv.pv_id, pkg.pkg_name,
                             pv.pkg_version, pv.dlocked, pv.pv_description
                        FROM environment_view rel,
                             PACKAGES pkg,
                             package_versions pv,
                             views vi
                       WHERE pv.pkg_id = pkg.pkg_id
                         AND rel.pv_id = pv.pv_id
                         AND rel.view_id = vi.view_id
                         AND rel.view_id IN (
                                SELECT *
                                  FROM THE
                                          (SELECT CAST
                                                     (in_list_number
                                                              (sviewidshowlist) AS relmgr_number_tab_t
                                                     )
                                             FROM DUAL
                                          ))
                         AND rtag_id = rtagid) ord
            ORDER BY UPPER (ord.view_name), UPPER (ord.pkg_name);
      ELSIF viewtype = 2
      THEN
         /*--- PERSONAL VIEW ---*/
         OPEN recordset FOR
            SELECT   *
                FROM (
                      /* Base Views collapsed */
                      SELECT DISTINCT vi.view_id, vi.view_name,
                                      TO_NUMBER (NULL) AS pkg_state,
                                      TO_NUMBER (NULL) AS deprecated_state,
                                      TO_NUMBER (NULL) AS pv_id,
                                      NULL AS pkg_name, NULL AS pkg_version,
                                      NULL AS dlocked, NULL AS pv_description
                                 FROM environment_view rel,
                                      view_settings vs,
                                      views vi
                                WHERE rel.view_id = vi.view_id
                                  AND vs.view_id = rel.view_id
                                  AND vs.user_id = userid
                                  AND rtag_id = rtagid
                                  AND rel.view_id NOT IN (
                                         SELECT *
                                           FROM THE
                                                   (SELECT CAST
                                                              (in_list_number
                                                                  (sviewidshowlist
                                                                  ) AS relmgr_number_tab_t
                                                              )
                                                      FROM DUAL
                        
                           ))
                      UNION
                      /* Base Views expanded */
                      SELECT vi.view_id, vi.view_name,
                             DECODE (rel.pkg_state,
                                     NULL, 0,
                                     rel.pkg_state
                                    ) AS pkg_state,
                             rel.deprecated_state, pv.pv_id, pkg.pkg_name,
                             pv.pkg_version, pv.dlocked, pv.pv_description
                        FROM environment_view rel,
                             PACKAGES pkg,
                             package_versions pv,
                             views vi,
                             view_settings vs
                       WHERE pv.pkg_id = pkg.pkg_id
                         AND rel.pv_id = pv.pv_id
                         AND rel.view_id = vi.view_id
                         AND vs.view_id = vi.view_id
                         AND vs.user_id = userid
                         AND rel.view_id IN (
                                SELECT *
                                  FROM THE
                                          (SELECT CAST
                                                     (in_list_number
                                                              (sviewidshowlist) AS relmgr_number_tab_t
                                                     )
                                             FROM DUAL
                                          ))
                         AND rtag_id = rtagid
                      UNION
                      /* Private Views collapsed */
                      SELECT vi.view_id, vi.view_name,
                             TO_NUMBER (NULL) AS pkg_state,
                             TO_NUMBER (NULL) AS deprecated_state,
                             TO_NUMBER (NULL) AS pv_id, NULL AS pkg_name,
                             NULL AS pkg_version, NULL AS dlocked,
                             NULL AS pv_description
                        FROM view_settings vs,
                             view_def vd,
                             views vi,
                             environment_view rel,
                             package_versions pv
                       WHERE vs.view_id = vi.view_id
                         AND rel.pv_id = pv.pv_id
                         AND vd.pkg_id = pv.pkg_id
                         AND vd.view_id = vi.view_id
                         AND vi.base_view = 'N'
                         AND rel.rtag_id = rtagid
                         AND vs.user_id = userid
                         AND vi.view_id NOT IN (
                                SELECT *
                                  FROM THE
                                          (SELECT CAST
                                                     (in_list_number
                                                              (sviewidshowlist) AS relmgr_number_tab_t
                                                     )
                                             FROM DUAL
                                          ))
                      UNION
                      /* Private Views expanded */
                      SELECT vi.view_id, vi.view_name,
                             DECODE (rel.pkg_state,
                                     NULL, 0,
                                     rel.pkg_state
                                    ) AS pkg_state,
                             rel.deprecated_state, pv.pv_id, pkg.pkg_name,
                             pv.pkg_version, pv.dlocked, pv.pv_description
                        FROM users usr,
                             view_settings vs,
                             view_def vd,
                             views vi,
                             environment_view rel,
                             PACKAGES pkg,
                             package_versions pv
                       WHERE vs.user_id = usr.user_id
                         AND vs.view_id = vi.view_id
                         AND vd.view_id = vi.view_id
                         AND pv.pkg_id = pkg.pkg_id
                         AND rel.pv_id = pv.pv_id
                         AND rel.rtag_id = rtagid
                         AND vd.pkg_id = pkg.pkg_id
                         AND vi.base_view = 'N'
                         AND vi.view_id IN (
                                SELECT *
                                  FROM THE
                                          (SELECT CAST
                                                     (in_list_number
                                                              (sviewidshowlist) AS relmgr_number_tab_t
                                                     )
                                             FROM DUAL
                                          ))
                         AND usr.user_id = userid) ord
            ORDER BY UPPER (ord.view_name), UPPER (ord.pkg_name);
      END IF;
   END;

/*-------------------------------------------------------------------------------------------------------*/
   PROCEDURE get_released_items (
      viewtype           IN       NUMBER,
      userid             IN       NUMBER,
      rtagid             IN       NUMBER,
      sviewidshowlist    IN       VARCHAR2,
      ntruerecordcount   OUT      NUMBER,
      recordset          OUT      typecur
   )
   IS
   BEGIN
      -- Get true record count because views can give false count
      SELECT COUNT (rc.pv_id)
        INTO ntruerecordcount
        FROM release_content rc
       WHERE rc.rtag_id = rtagid;

      IF viewtype = 1
      THEN
         /*--- GUEST VIEW ---*/
         OPEN recordset FOR
            SELECT   *
                FROM (
                      /* Base Views collapsed */
                      SELECT DISTINCT vi.view_id, vi.view_name,
                                      TO_NUMBER (NULL) AS pkg_state,
                                      TO_NUMBER (NULL) AS deprecated_state,
                                      TO_NUMBER (NULL) AS pv_id,
                                      NULL AS pkg_name, NULL AS pkg_version,
                                      NULL AS dlocked, NULL AS pv_description
                                 FROM release_content rel, views vi
                                WHERE rel.base_view_id = vi.view_id
                                  AND rtag_id = rtagid
                                  AND rel.base_view_id NOT IN (
                                         SELECT *
                                           FROM THE
                                                   (SELECT CAST
                                                              (in_list_number
                                                                  (sviewidshowlist
                                                                  ) AS relmgr_number_tab_t
                                                              )
                                                      FROM DUAL
                                                   ))
                      UNION
                      /* Base Views expanded */
                      SELECT vi.view_id, vi.view_name, rel.pkg_state,
                             rel.deprecated_state, pv.pv_id, pkg.pkg_name,
                             pv.pkg_version, pv.dlocked, pv.pv_description
                        FROM release_content rel,
                             PACKAGES pkg,
                             package_versions pv,
                             views vi
                       WHERE pv.pkg_id = pkg.pkg_id
                         AND rel.pv_id = pv.pv_id
                         AND rel.base_view_id = vi.view_id
                         AND rel.base_view_id IN (
                                SELECT *
                                  FROM THE
                                          (SELECT CAST
                                                     (in_list_number
                                                              (sviewidshowlist) AS relmgr_number_tab_t
                                                     )
                                             FROM DUAL
                            
              ))
                         AND rtag_id = rtagid) ord
            ORDER BY UPPER (ord.view_name), UPPER (ord.pkg_name);
      ELSIF viewtype = 2
      THEN
         /*--- PERSONAL VIEW ---*/
         OPEN recordset FOR
            SELECT   *
                FROM (
                      /* Base Views collapsed */
                      SELECT DISTINCT vi.view_id, vi.view_name,
                                      TO_NUMBER (NULL) AS pkg_state,
                                      TO_NUMBER (NULL) AS deprecated_state,
                                      TO_NUMBER (NULL) AS pv_id,
                                      NULL AS pkg_name, NULL AS pkg_version,
                                      NULL AS dlocked, NULL AS pv_description
                                 FROM release_content rel,
                                      view_settings vs,
                                      views vi
                                WHERE rel.base_view_id = vi.view_id
                                  AND vs.view_id = rel.base_view_id
                                  AND vs.user_id = userid
                                  AND rtag_id = rtagid
                                  AND rel.base_view_id NOT IN (
                                         SELECT *
                                           FROM THE
                                                   (SELECT CAST
                                                              (in_list_number
                                                                  (sviewidshowlist
                                                                  ) AS relmgr_number_tab_t
                                                              )
                                                      FROM DUAL
                                                   ))
                      UNION
                      /* Base Views expanded */
                      SELECT vi.view_id, vi.view_name, rel.pkg_state,
                             rel.deprecated_state, pv.pv_id, pkg.pkg_name,
                             pv.pkg_version, pv.dlocked, pv.pv_description
                        FROM release_content rel,
                             PACKAGES pkg,
                             package_versions pv,
                             views vi,
                             view_settings vs
                       WHERE pv.pkg_id = pkg.pkg_id
                         AND rel.pv_id = pv.pv_id
                         AND rel.base_view_id = vi.view_id
                         AND vs.view_id = vi.view_id
                         AND vs.user_id = userid
                         AND rel.base_view_id IN (
                                SELECT *
                                  FROM THE
                                          (SELECT CAST
                                                     (in_list_number
                                                              (sviewidshowlist) AS relmgr_number_tab_t
                                                     )
                                             FROM DUAL
                                          ))
                         AND rtag_id = rtagid
                      UNION
                      /* Private Views collapsed */
                      SELECT vi.view_id, vi.view_name,
                             TO_NUMBER (NULL) AS pkg_state,
                             TO_NUMBER (NULL) AS deprecated_state,
                             TO_NUMBER (NULL) AS pv_id, NULL AS pkg_name,
                             NULL AS pkg_version, NULL AS dlocked,
                             NULL AS pv_description
                        FROM view_settings vs,
                             view_def vd,
                             views vi,
                             release_content rel,
                             package_versions pv
                       WHERE vs.view_id = vi.view_id
                         AND rel.pv_id = pv.pv_id
                         AND vd.pkg_id = pv.pkg_id
                         AND vd.view_id = vi.view_id
                         AND vi.base_view = 'N'
                         AND rel.rtag_id = rtagid
                         AND vs.user_id = userid
                         AND vi.view_id NOT IN (
                                SELECT *
                                  FROM THE
                                          (SELECT CAST
                                                     (in_list_number
                                                              (sviewidshowlist) AS relmgr_number_tab_t
                                                     )
                                             FROM DUAL
                                          ))
                      UNION
                      /* Private Views expanded */
                      SELECT vi.view_id, vi.view_name, rel.pkg_state,
                             rel.deprecated_state, pv.pv_id, pkg.pkg_name,
                             pv.pkg_version, pv.dlocked, pv.pv_description
                        FROM users usr,
                             view_settings vs,
                             view_def vd,
                             views vi,
                             release_content rel,
                             PACKAGES pkg,
                             package_versions pv
                       WHERE vs.user_id = usr.user_id
                         AND vs.view_id = vi.view_id
                         AND vd.view_id = vi.view_id
                         AND pv.pkg_id = pkg.pkg_id
                         AND rel.pv_id = pv.pv_id
                         AND rel.rtag_id = rtagid
                         AND vd.pkg_id = pkg.pkg_id
                         AND vi.base_view = 'N'
                         AND vi.view_id IN (
                                SELECT *
                                  FROM THE
                                          (SELECT CAST
                                                     (in_list_number
                                                              (sviewidshowlist) AS relmgr_number_tab_t
                                                     )
                                             FROM DUAL
                                          ))
                         AND usr.user_id = userid) ord
            ORDER BY UPPER (ord.view_name), UPPER (ord.pkg_name);
      END IF;
   END;

/*-------------------------------------------------------------------------------------------------------*/
   PROCEDURE get_work_in_progress_items (
      viewtype           IN       NUMBER,
      userid             IN       NUMBER,
      rtagid             IN       NUMBER,
      sviewidshowlist    IN       VARCHAR2,
      ntruerecordcount   OUT      NUMBER,
      recordset          OUT      typecur
   )
   IS
   BEGIN
      -- Get true record count because views can give false count
      SELECT COUNT (wip.pv_id)
        INTO ntruerecordcount
        FROM work_in_progress wip
       WHERE wip.rtag_id = rtagid;

      IF viewtype = 1
      THEN
         /*--- GUEST VIEW ---*/
         OPEN recordset FOR
            SELECT   *
                FROM (
                      /* Base Views collapsed */
                      SELECT DISTINCT vi.view_id, vi.view_name,
                                      TO_NUMBER (NULL) AS pkg_state,
                                      TO_NUMBER (NULL) AS deprecated_state,
                                      TO_NUMBER (NULL) AS pv_id,
                                      NULL AS pkg_name, NULL AS pkg_version,
                                      NULL AS dlocked, NULL AS pv_description
                                 FROM work_in_progress rel, views vi
                                WHERE rel.view_id = vi.view_id
                                  AND rtag_id = rtagid
                                  AND rel.view_id NOT IN (
                                         SELECT *
                                           FROM THE
                                                   (SELECT CAST
                                                              (in_list_number
                                                      
            (sviewidshowlist
                                                                  ) AS relmgr_number_tab_t
                                                              )
                                                      FROM DUAL
                                                   ))
                      UNION
                      /* Base Views expanded */
                      SELECT vi.view_id, vi.view_name, 0 AS pkg_state,
                             
                             --rel.pkg_state,
                             TO_NUMBER (NULL) AS deprecated_state, pv.pv_id,
                             pkg.pkg_name, pv.pkg_version, pv.dlocked,
                             pv.pv_description
                        FROM work_in_progress rel,
                             PACKAGES pkg,
                             package_versions pv,
                             views vi
                       WHERE pv.pkg_id = pkg.pkg_id
                         AND rel.pv_id = pv.pv_id
                         AND rel.view_id = vi.view_id
                         AND rel.view_id IN (
                                SELECT *
                                  FROM THE
                                          (SELECT CAST
                                                     (in_list_number
                                                              (sviewidshowlist) AS relmgr_number_tab_t
                                                     )
                                             FROM DUAL
                                          ))
                         AND rtag_id = rtagid) ord
            ORDER BY UPPER (ord.view_name), UPPER (ord.pkg_name);
      ELSIF viewtype = 2
      THEN
         /*--- PERSONAL VIEW ---*/
         OPEN recordset FOR
            SELECT   *
                FROM (
                      /* Base Views collapsed */
                      SELECT DISTINCT vi.view_id, vi.view_name,
                                      TO_NUMBER (NULL) AS pkg_state,
                                      TO_NUMBER (NULL) AS deprecated_state,
                                      TO_NUMBER (NULL) AS pv_id,
                                      NULL AS pkg_name, NULL AS pkg_version,
                                      NULL AS dlocked, NULL AS pv_description
                                 FROM work_in_progress rel,
                                      view_settings vs,
                                      views vi
                                WHERE rel.view_id = vi.view_id
                                  AND vs.view_id = rel.view_id
                                  AND vs.user_id = userid
                                  AND rtag_id = rtagid
                                  AND rel.view_id NOT IN (
                                         SELECT *
                                           FROM THE
                                                   (SELECT CAST
                                                              (in_list_number
                                                                  (sviewidshowlist
                                                                  ) AS relmgr_number_tab_t
                                                              )
                                                      FROM DUAL
                                                   ))
                      UNION
                      /* Base Views expanded */
                      SELECT vi.view_id, vi.view_name, 0 AS pkg_state,
                             
                             --rel.pkg_state,
                             TO_NUMBER (NULL) AS deprecated_state, pv.pv_id,
                             pkg.pkg_name, pv.pkg_version, pv.dlocked,
                             pv.pv_description
                        FROM work_in_progress rel,
                             PACKAGES pkg,
                             package_versions pv,
                             views vi,
                             view_settings vs
                       WHERE pv.pkg_id = pkg.pkg_id
                         AND rel.pv_id = pv.pv_id
                         AND rel.view_id = vi.view_id
                         AND vs.view_id = vi.view_id
                         AND vs.user_id = userid
                         AND rel.view_id IN (
                                SELECT *
                                  FROM THE
                                          (SELECT CAST
                                                     (in_list_number
                                                              (sviewidshowlist) AS relmgr_number_tab_t
                                                     )
                                             FROM DUAL
                                          ))
                         AND rtag_id = rtagid
                      UNION
                      /* Private Views collapsed */
                      SELECT vi.view_id, vi.view_name,
                             TO_NUMBER (NULL) AS pkg_state,
                             TO_NUMBER (NULL) AS deprecated_state,
                             TO_NUMBER (NULL) AS pv_id, NULL AS pkg_name,
                             NULL AS pkg_version, NULL AS dlocked,
                             NULL AS pv_description
                        FROM view_settings vs,
                             view_def vd,
                             views vi,
                             work_in_progress rel,
                             package_versions pv
                       WHERE vs.view_id = vi.view_id
                         AND rel.pv_id = pv.pv_id
                         AND vd.pkg_id = pv.pkg_id
                         AND vd.view_id = vi.view_id
                         AND vi.base_view = 'N'
                         AND rel.rtag_id = rtagid
                         AND vs.user_id = userid
                         AND vi.view_id NOT IN (
                                SELECT *
                                  FROM THE
                                          (SELECT CAST
                                                     (in_list_number
                                                              (sviewidshowlist) AS relmgr_number_tab_t
                                                     )
                                             FROM DUAL
                                          ))
                      UNION
                      /* Private Views expanded */
                      SELECT vi.view_id, vi.view_name, 0 AS pkg_state,
                             
                             --rel.pkg_state,
                             TO_NUMBER (NULL) AS deprecated_state, pv.pv_id,
                             pkg.pkg_name, pv.pkg_version, pv.dlocked,
                             pv.pv_description
                        FROM users usr,
                             view_settings vs,
                             view_def vd,
                             views vi,
                             work_in_progress rel,
                             PACKAGES pkg,
                             package_versions pv
                       WHERE vs.user_id = usr.user_id
                         AND vs.view_id = vi.view_id
                         AND vd.view_id = vi.view_id
                         AND pv.pkg_id = pkg.pkg_id
                         AND rel.pv_id = pv.pv_id
                         AND rel.rtag_id = rtagid
                         AND vd.pkg_id = pkg.pkg_id
                         AND vi.base_view = 'N'
                         AND vi.view_id IN (
                                SELECT *
                                  FROM THE
                                          (SELECT CAST
                                                     (in_list_number
                                                              (sviewidshowlist) AS relmgr_number_tab_t
                                                     )
                                             FROM DUAL
                                          ))
                         AND usr.user_id = userid) ord
            ORDER BY UPPER (ord.view_name), UPPER (ord.pkg_name);
      END IF;
   
END;

/*-------------------------------------------------------------------------------------------------------*/
   PROCEDURE get_pending_items (
      viewtype           IN       NUMBER,
      userid             IN       NUMBER,
      rtagid             IN       NUMBER,
      sviewidshowlist    IN       VARCHAR2,
      ntruerecordcount   OUT      NUMBER,
      recordset          OUT      typecur
   )
   IS
   BEGIN
      -- Get true record count because views can give false count
      SELECT COUNT (pl.pv_id)
        INTO ntruerecordcount
        FROM planned pl
       WHERE pl.rtag_id = rtagid;

      IF viewtype = 1
      THEN
         /*--- GUEST VIEW ---*/
         OPEN recordset FOR
            SELECT   *
                FROM (
                      /* Base Views collapsed */
                      SELECT DISTINCT vi.view_id, vi.view_name,
                                      TO_NUMBER (NULL) AS pkg_state,
                                      TO_NUMBER (NULL) AS deprecated_state,
                                      TO_NUMBER (NULL) AS pv_id,
                                      NULL AS pkg_name, NULL AS pkg_version,
                                      NULL AS dlocked, NULL AS pv_description
                                 FROM planned rel, views vi
                                WHERE rel.view_id = vi.view_id
                                  AND rtag_id = rtagid
                                  AND rel.view_id NOT IN (
                                         SELECT *
                                           FROM THE
                                                   (SELECT CAST
                                                              (in_list_number
                                                                  (sviewidshowlist
                                                                  ) AS relmgr_number_tab_t
                                                              )
                                                      FROM DUAL
                                                   ))
                      UNION
                      /* Base Views expanded */
                      SELECT vi.view_id, vi.view_name, 0 AS pkg_state,
                             
                             --rel.pkg_state,
                             TO_NUMBER (NULL) AS deprecated_state, pv.pv_id,
                             pkg.pkg_name, pv.pkg_version, pv.dlocked,
                             pv.pv_description
                        FROM planned rel,
                             PACKAGES pkg,
                             package_versions pv,
                             views vi
                       WHERE pv.pkg_id = pkg.pkg_id
                         AND rel.pv_id = pv.pv_id
                         AND rel.view_id = vi.view_id
                         AND rel.view_id IN (
                                SELECT *
                                  FROM THE
                                          (SELECT CAST
                                                     (in_list_number
                                                              (sviewidshowlist) AS relmgr_number_tab_t
                                                     )
                                             FROM DUAL
                                          ))
                         AND rtag_id = rtagid) ord
            ORDER BY UPPER (ord.view_name), UPPER (ord.pkg_name);
      ELSIF viewtype = 2
      THEN
         /*--- PERSONAL VIEW ---*/
         OPEN recordset FOR
            SELECT   *
                FROM (
                      /* Base Views collapsed */
                      SELECT DISTINCT vi.view_id, vi.view_name,
                                      TO_NUMBER (NULL) AS pkg_state,
                                      TO_NUMBER (NULL) AS deprecated_state,
                                      TO_NUMBER (NULL) AS pv_id,
                                      NULL AS pkg_name, NULL AS pkg_version,
                                      NULL AS dlocked, NULL AS pv_description
                                 FROM planned rel, view_settings vs, views vi
                                WHERE rel.view_id = vi.view_id
                                  AND vs.view_id = rel.view_id
                                  AND vs.user_id = userid
                                  AND rtag_id = rtagid
                                  AND rel.view_id NOT IN (
                                         SELECT *
                                           FROM THE
                                                   (SELECT CAST
                                                              (in_list_number
                                                                  (sviewidshowlist
                                                                  ) AS relmgr_number_tab_t
                                                              )
                                                      FROM DUAL
                                                   ))
                      UNION
                      /* Base Views expanded */
                      SELECT vi.view_id, vi.view_name, 0 AS pkg_state,
                             
                             --rel.pkg_state,
                             TO_NUMBER (NULL) AS deprecated_state, pv.pv_id,
                             pkg.pkg_name, pv.pkg_version, pv.dlocked,
                             pv.pv_description
                        FROM planned rel,
                             PACKAGES pkg,
                             package_versions pv,
                             views vi,
                             view_settings vs
                       WHERE pv.pkg_id = pkg.pkg_id
                         AND rel.pv_id = pv.pv_id
                         AND rel.view_id = vi.view_id
                         AND vs.view_id = vi.view_id
                         AND vs.user_id = userid
                         AND rel.view_id IN (
                                SELECT *
                                  FROM THE
                                          (SELECT CAST
                                                     (in_list_number
                                                              (sviewidshowlist) AS relmgr_number_tab_t
                                                     )
                                             FROM DUAL
                                          ))
                         AND rtag_id = rtagid
                      UNION
                      /* Private Views collapsed */
                      SELECT vi.view_id, vi.view_name,
                             TO_NUMBER (NULL) AS pkg_state,
                             TO_NUMBER (NULL) AS deprecated_state,
                             TO_NUMBER (NULL) AS pv_id, NULL AS pkg_name,
                             NULL AS pkg_version, NULL AS dlocked,
                             NULL AS pv_description
                        FROM view_settings vs,
                             view_def vd,
                             views vi,
                             planned rel,
                             package_versions pv
                       WHERE vs.view_id = vi.view_id
                         AND rel.pv_id = pv.pv_id
                         AND vd.pkg_id = pv.pkg_id
                         AND vd.view_id = vi.view_id
                         AND vi.base_view = 'N'
                         AND rel.rtag_id = rtagid
                         AND vs.user_id = userid
                         AND vi.view_id NOT IN (
                                SELECT *
                                  FROM THE
                                          (SELECT CAST
                                                     (in_list_number
                                                              (sviewidshowlist) AS relmgr_number_tab_t
                                                     )
                                             FROM DUAL
                                          ))
                      UNION
                      /* Private Views expanded */
                      SELECT vi.view_id, vi.view_name, 0 AS pkg_state,
         
                    
                             --rel.pkg_state,
                             TO_NUMBER (NULL) AS deprecated_state, pv.pv_id,
                             pkg.pkg_name, pv.pkg_version, pv.dlocked,
                             pv.pv_description
                        FROM users usr,
                             view_settings vs,
                             view_def vd,
                             views vi,
                             planned rel,
                             PACKAGES pkg,
                             package_versions pv
                       WHERE vs.user_id = usr.user_id
                         AND vs.view_id = vi.view_id
                         AND vd.view_id = vi.view_id
                         AND pv.pkg_id = pkg.pkg_id
                         AND rel.pv_id = pv.pv_id
                         AND rel.rtag_id = rtagid
                         AND vd.pkg_id = pkg.pkg_id
                         AND vi.base_view = 'N'
                         AND vi.view_id IN (
                                SELECT *
                                  FROM THE
                                          (SELECT CAST
                                                     (in_list_number
                                                              (sviewidshowlist) AS relmgr_number_tab_t
                                                     )
                                             FROM DUAL
                                          ))
                         AND usr.user_id = userid) ord
            ORDER BY UPPER (ord.view_name), UPPER (ord.pkg_name);
      END IF;
   END;

/*-------------------------------------------------------------------------------------------------------*/
   PROCEDURE get_view_content (
      rtagid      IN       NUMBER,
      viewid      IN       NUMBER,
      recordset   OUT      typecur
   )
   IS
      isbaseview   CHAR (1);
   BEGIN
      -- Check if the view is BASE VIEW
      SELECT vi.base_view
        INTO isbaseview
        FROM views vi
       WHERE vi.view_id = viewid;

      IF (isbaseview = 'Y')
      THEN
         -- Get Base view content
         OPEN recordset FOR
            SELECT   DECODE (rel.pkg_state,
                             NULL, 0,
                             rel.pkg_state
                            ) AS pkg_state,
                     rel.deprecated_state, pv.pv_id, pkg.pkg_name,
                     pv.pkg_version, pv.dlocked, pv.pv_description,
                     pv.build_type
                FROM environment_view rel, PACKAGES pkg, package_versions pv
               WHERE pv.pkg_id = pkg.pkg_id
                 AND rel.pv_id = pv.pv_id
                 AND rel.view_id = viewid
                 AND rel.rtag_id = rtagid
            ORDER BY UPPER (pkg.pkg_name);
      ELSE
         -- Get non base view content
         OPEN recordset FOR
            SELECT   DECODE (rel.pkg_state,
                             NULL, 0,
                             rel.pkg_state
                            ) AS pkg_state,
                     rel.deprecated_state, pv.pv_id, pkg.pkg_name,
                     pv.pkg_version, pv.dlocked, pv.pv_description,
                     pv.build_type
                FROM environment_view rel,
                     PACKAGES pkg,
                     package_versions pv,
                     view_def vd
               WHERE pv.pkg_id = pkg.pkg_id
                 AND rel.pv_id = pv.pv_id
                 AND rel.rtag_id = rtagid
                 AND vd.view_id = viewid
                 AND vd.pkg_id = pv.pkg_id
            ORDER BY UPPER (pkg.pkg_name);
      END IF;
   END;

/*-------------------------------------------------------------------------------------------------------*/
   FUNCTION get_package_view (pvid IN NUMBER, rtagid IN NUMBER)
      RETURN NUMBER
   IS
      envtab             NUMBER;
      returnvalue        NUMBER;
      return_not_found   NUMBER := -1;
   BEGIN
      envtab := get_package_area (pvid, rtagid);

      IF envtab = 0
      THEN
         -- WORK IN PROGRESS --
         returnvalue := pk_work_in_progress.get_package_view (pvid, rtagid);
      ELSIF envtab = 1
      THEN
         -- PENDING --
         returnvalue := pk_planned.get_package_view (pvid, rtagid);
      ELSIF envtab = 2
      THEN
         -- RELEASED --
         returnvalue := pk_release.get_package_view (pvid, rtagid);
      ELSE
         -- This may be a Patch not located anywhere but unlocked
         returnvalue := return_not_found;
      END IF;

      RETURN returnvalue;
   END;

/*-------------------------------------------------------------------------------------------------------*/
   PROCEDURE make_release (pvid IN NUMBER, rtagid IN NUMBER, userid IN NUMBER)
   IS
      viewid          NUMBER;
      envtab          NUMBER;
      ispatch         CHAR (1) := NULL;
      buildtype       CHAR (1) := NULL;
      lastversionid   NUMBER;
   BEGIN
      -- Check if package is patch
      SELECT pv.is_patch, pv.build_type, pv.last_pv_id
        INTO ispatch, buildtype, lastversionid
        FROM package_versions pv
       WHERE pv.pv_id = pvid;

      -- Get ViewId
      viewid := get_package_view (pvid, rtagid);
      -- Remove from current area
      envtab := pk_environment.get_package_area (pvid, rtagid);

      -- Make sure that package was in work-in-progress or pending before makeing it release
      -- Exclude patches, ripple builds
      IF (envtab < 0)
      THEN
         -- Not found in work-in-progress or pending
         IF (ispatch IS NULL) AND (buildtype = 'M')
         THEN
            raise_application_error (-20000,
                                     'This package cannot be released here.'
                                    );
         END IF;
      END IF;

      -- Log
      log_action (pvid, 'action', userid, 'Start of Make Package Release...');

      IF envtab = 0
      THEN
         -- WORK IN PROGRESS --
         pk_work_in_progress.remove_package (pvid, rtagid, userid);
      ELSIF envtab = 1
      THEN
         -- PENDING --
         pk_planned.remove_package (pvid, rtagid, userid);
      END IF;

      -- Change package state
      pk_package.change_state (pvid, 'Y', userid);

      -- Make sure it is valid BASE VIEW
      IF viewid < 1
      THEN
         viewid := 7;                            -- This is default base view
      END IF;

      IF (ispatch IS NULL)
      THEN
         -- Add package to new area
         pk_release.add_package (pvid, viewid, rtagid, userid);
      END IF;

      -- Now do post Release Actions
      pk_release.run_post_actions (pvid, rtagid);

      -- Now delete old version from DO_NOT_RIPPLE Table if it Exists
      DELETE FROM do_not_ripple
            WHERE rtag_id = rtagid AND pv_id = lastversionid;

      -- Now delete old version from ADVISORY_RIPPLES Table if it Exists
      DELETE FROM advisory_ripple
            WHERE rtag_id = rtagid AND pv_id = lastversionid;

      -- Log
      log_action (pvid, 'action', userid, 'End of Make Package Release...');
   END;

/*-------------------------------------------------------------------------------------------------------*/
   PROCEDURE auto_make_release (
      pvid            IN   NUMBER,
      rtagid          IN   NUMBER,
      userid          IN   NUMBER,
      vext            IN   package_versions.v_ext%TYPE,
      ssv_ext         IN   package_versions.v_ext%TYPE,
      clonefrompvid   IN   NUMBER
   )
   IS
      viewid          NUMBER;
      envtab          NUMBER;
      ispatch         CHAR (1) := NULL;
      buildtype       CHAR (1) := NULL;
      lastversionid   NUMBER;
   BEGIN
      -- Check if package is patch
      SELECT pv.is_patch, pv.build_type, pv.last_pv_id
        INTO ispatch, buildtype, lastversionid
        FROM package_versions pv
       WHERE pv.pv_id = pvid;

      IF vext <> ssv_ext
      THEN
         -- Get ViewId
         viewid := get_package_view (clonefrompvid, rtagid);
         -- Remove from current area
         envtab := pk_environment.get_package_area (clonefrompvid, rtagid);
      ELSE
         -- Get ViewId
         viewid := get_package_view (pvid, rtagid);
         -- Remove 
from current area
         envtab := pk_environment.get_package_area (pvid, rtagid);
      END IF;

      -- Make sure that package was in work-in-progress or pending before makeing it release
      -- Exclude patches, ripple builds
      IF (envtab < 0)
      THEN
         -- Not found in work-in-progress or pending
         IF (ispatch IS NULL) AND (buildtype = 'M')
         THEN
            raise_application_error (-20000,
                                     'This package cannot be released here.'
                                    );
         END IF;
      END IF;

      -- Log
      log_action (pvid, 'action', userid, 'Start of Make Package Release...');

      IF vext <> ssv_ext
      THEN
         IF envtab = 0
         THEN
            -- WORK IN PROGRESS --
            pk_work_in_progress.remove_package (clonefrompvid, rtagid,
                                                userid);
         ELSIF envtab = 1
         THEN
            -- PENDING --
            pk_planned.remove_package (clonefrompvid, rtagid, userid);
         ELSIF envtab = 2
         THEN
            -- RELEASED --
            pk_release.remove_package (clonefrompvid, rtagid, userid);
         END IF;
      ELSE
         IF envtab = 0
         THEN
            -- WORK IN PROGRESS --
            pk_work_in_progress.remove_package (pvid, rtagid, userid);
         ELSIF envtab = 1
         THEN
            -- PENDING --
            pk_planned.remove_package (pvid, rtagid, userid);
         END IF;
      END IF;

      -- Change package state
      pk_package.change_state (pvid, 'Y', userid);

      -- Make sure it is valid BASE VIEW
      IF viewid < 1
      THEN
         viewid := 7;                            -- This is default base view
      END IF;

      IF (ispatch IS NULL)
      THEN
         -- Add package to new area
         pk_release.add_package (pvid, viewid, rtagid, userid);
      END IF;

      -- Now do post Release Actions
      pk_release.run_post_actions (pvid, rtagid);
      -- Now update the Dash_Board Table (DEPRECATED)
      pk_rmapi.update_dash_board (rtagid);

      -- Now delete old version from DO_NOT_RIPPLE Table if it Exists
      DELETE FROM do_not_ripple
            WHERE rtag_id = rtagid AND pv_id = lastversionid;

      -- Now delete old version from ADVISORY_RIPPLES Table if it Exists
      DELETE FROM advisory_ripple
            WHERE rtag_id = rtagid AND pv_id = lastversionid;

      -- Log
      log_action (pvid, 'action', userid, 'End of Make Package Release...');
   END;

/*-------------------------------------------------------------------------------------------------------*/
   PROCEDURE make_unrelease (pvid IN NUMBER, rtagid IN NUMBER, userid IN NUMBER)
   IS
      viewid   NUMBER;
      envtab   NUMBER;
   BEGIN
      -- Get ViewId
      --ViewId := PK_RELEASE.GET_PACKAGE_VIEW ( PvId, RtagId );
      viewid := get_package_view (pvid, rtagid);
      -- Remove from current area
      envtab := pk_environment.get_package_area (pvid, rtagid);
      -- Log
      log_action (pvid,
                  'action',
                  userid,
                  'Start of Make Package UnRelease...'
                 );

      IF envtab = 2
      THEN
         -- RELEASE AREA --
         pk_release.remove_package (pvid, rtagid, userid);
      ELSIF envtab = 1
      THEN
         -- PENDING --
         pk_planned.remove_package (pvid, rtagid, userid);
      END IF;

      -- Change package state
      pk_package.change_state (pvid, 'N', userid);

      -- Make sure it is valid BASE VIEW
      IF viewid < 1
      THEN
         viewid := 7;                            -- This is default base view
      END IF;

      -- Add package to new area
      pk_work_in_progress.add_package (pvid, viewid, rtagid, userid);
      -- Now do post Release Actions
      pk_release.run_post_actions (pvid, rtagid);
      -- Log
      log_action (pvid, 'action', userid, 'End of Make Package UnRelease...');
   END;

/*-------------------------------------------------------------------------------------------------------*/
   PROCEDURE make_pending (pvid IN NUMBER, rtagid IN NUMBER, userid IN NUMBER)
   IS
      viewid      NUMBER;
      ispatch     CHAR (1) := NULL;
      buildtype   CHAR (1) := NULL;
   BEGIN
      -- Check if package is patch
      SELECT pv.is_patch, pv.build_type
        INTO ispatch, buildtype
        FROM package_versions pv
       WHERE pv.pv_id = pvid;

      -- Get ViewId
      viewid := get_package_view (pvid, rtagid);
      --ViewId := PK_WORK_IN_PROGRESS.GET_PACKAGE_VIEW ( PvId, RtagId );

      -- Log
      log_action (pvid, 'action', userid, 'Start of Make Package Pending...');

      IF (ispatch IS NULL)
      THEN
         -- Remove from current area
         pk_work_in_progress.remove_package (pvid, rtagid, userid);
         -- Change package state
         pk_package.change_state (pvid, 'P', userid);
         -- Add package to new area
         pk_planned.add_package (pvid, viewid, rtagid, userid);
      END IF;

      -- Log
      log_action (pvid, 'action', userid, 'End of Make Package Pending...');
   END;

/*-------------------------------------------------------------------------------------------------------*/
   PROCEDURE make_approved (pvid IN NUMBER, rtagid IN NUMBER, userid IN NUMBER)
   IS
   BEGIN
      -- Log
      log_action (pvid,
                  'action',
                  userid,
                  'Start of Package Pending Approval...'
                 );
      -- Change package state
      pk_package.change_state (pvid, 'A', userid);
      -- Log
      log_action (pvid, 'action', userid,
                  'End of Package Pending Approval...');
   END;

/*-------------------------------------------------------------------------------------------------------*/
   PROCEDURE make_reject (pvid IN NUMBER, rtagid IN NUMBER, userid IN NUMBER)
   IS
      viewid   NUMBER;
   BEGIN
      -- Get ViewId
      viewid := get_package_view (pvid, rtagid);
-- ViewId := PK_PLANNED.GET_PACKAGE_VIEW ( PvId, RtagId );

      -- Log
      log_action (pvid, 'action', userid, 'Start of Reject Package...');
      -- Remove from current area
      pk_planned.remove_package (pvid, rtagid, userid);
      -- Change package state
      pk_package.change_state (pvid, 'R', userid);
      -- Add package to new area
      pk_work_in_progress.add_package (pvid, viewid, rtagid, userid);
      -- Log
      log_action (pvid, 'action', userid, 'Start of Reject Package...');
   END;

/*-------------------------------------------------------------------------------------------------------*/
   PROCEDURE change_package_view (
      pvid        IN   NUMBER,
      rtagid      IN   NUMBER,
      newviewid   IN   NUMBER
   )
   IS
      envtab   NUMBER;
   BEGIN
      envtab := pk_environment.get_package_area (pvid, rtagid);

      IF envtab = 0
      THEN
         -- WORK IN PROGRESS --
         pk_work_in_progress.change_package_view (pvid, rtagid, newviewid);
      ELSIF envtab = 1
      THEN
         -- PENDING --
         pk_planned.change_package_view (pvid, rtagid, newviewid);
      ELSIF envtab = 2
      THEN
         -- RELEASED --
         pk_release.change_package_view (pvid, rtagid, newviewid);
      END IF;
   END;

/*-------------------------------------------------------------------------------------------------------*/
   PROCEDURE find_package (
      skeyword      IN       VARCHAR2,
      nrtagid       IN       NUMBER,
      nsearcharea   IN       NUMBER,
      recordset     OUT      typecur
   )
   IS
   BEGIN
      IF nsearcharea = 0
      THEN
         /* Search Work In Progress */
         OPEN recordset FOR
            SELECT   nsearcharea AS env_area, vi.view_name, pv.dlocked,
                     pkg.pkg_name, pv.pv_id, pv.pkg_version, pv.comments,
                     pv.modified_stamp, usr.full_name, usr.user_email
                FROM views vi,
                     work_in_progress rc,
                     PACKAGES pkg,
                     package_versions pv,
                     users usr
               WHERE rc.view_id = vi.view_id
                 AND rc.pv_id = pv.pv_id
                 AND pkg.pkg_id = pv.pkg_id
  
               AND pv.modifier_id = usr.user_id
                 AND rc.rtag_id = nrtagid
                 AND UPPER (pkg.pkg_name) LIKE UPPER (skeyword)
            ORDER BY UPPER (pkg.pkg_name);
      ELSIF nsearcharea = 1
      THEN
         /* Search Pending */
         OPEN recordset FOR
            SELECT   nsearcharea AS env_area, vi.view_name, pv.dlocked,
                     pkg.pkg_name, pv.pv_id, pv.pkg_version, pv.comments,
                     pv.modified_stamp, usr.full_name, usr.user_email
                FROM views vi,
                     planned rc,
                     PACKAGES pkg,
                     package_versions pv,
                     users usr
               WHERE rc.view_id = vi.view_id
                 AND rc.pv_id = pv.pv_id
                 AND pkg.pkg_id = pv.pkg_id
                 AND pv.modifier_id = usr.user_id
                 AND rc.rtag_id = nrtagid
                 AND UPPER (pkg.pkg_name) LIKE UPPER (skeyword)
            ORDER BY UPPER (pkg.pkg_name);
      ELSIF nsearcharea = 2
      THEN
         /* Search Released */
         OPEN recordset FOR
            SELECT   nsearcharea AS env_area, vi.view_name, pv.dlocked,
                     pkg.pkg_name, pv.pv_id, pv.pkg_version, pv.comments,
                     pv.modified_stamp, usr.full_name, usr.user_email
                FROM views vi,
                     release_content rc,
                     PACKAGES pkg,
                     package_versions pv,
                     users usr
               WHERE rc.base_view_id = vi.view_id
                 AND rc.pv_id = pv.pv_id
                 AND pkg.pkg_id = pv.pkg_id
                 AND pv.modifier_id = usr.user_id
                 AND rc.rtag_id = nrtagid
                 AND UPPER (pkg.pkg_name) LIKE UPPER (skeyword)
            ORDER BY UPPER (pkg.pkg_name);
      ELSIF nsearcharea = 3
      THEN
         /* Search ALL */
         OPEN recordset FOR
            SELECT   rc.env_area, vi.view_name, pv.dlocked, pkg.pkg_name,
                     pv.pv_id, pv.pkg_version, pv.comments,
                     pv.modified_stamp, usr.full_name, usr.user_email
                FROM views vi,
                     environment_view rc,
                     PACKAGES pkg,
                     package_versions pv,
                     users usr
               WHERE rc.view_id = vi.view_id
                 AND rc.pv_id = pv.pv_id
                 AND pkg.pkg_id = pv.pkg_id
                 AND pv.modifier_id = usr.user_id
                 AND rc.rtag_id = nrtagid
                 AND UPPER (pkg.pkg_name) LIKE UPPER (skeyword)
            ORDER BY UPPER (pkg.pkg_name);
      END IF;
   END;

/*-------------------------------------------------------------------------------------------------------*/
   PROCEDURE find_file (
      skeyword      IN       VARCHAR2,
      nrtagid       IN       NUMBER,
      nsearcharea   IN       NUMBER,
      npagesize     IN       NUMBER,
      recordset     OUT      typecur
   )
   IS
   BEGIN
      IF nsearcharea = 0
      THEN
         /* Search Work In Progress */
         OPEN recordset FOR
            SELECT   qry.*
                FROM (
                      /* File search on Packages */
                      SELECT art.file_name, art.file_path, pkg.pkg_name,
                             pv.pv_id, pv.pkg_version, art.crc_cksum
                        FROM work_in_progress rc,
                             PACKAGES pkg,
                             package_versions pv,
                             release_components art
                       WHERE rc.pv_id = art.pv_id
                         AND pv.pkg_id = pkg.pkg_id
                         AND rc.pv_id = pv.pv_id
                         AND rc.rtag_id = nrtagid
                         AND UPPER (art.file_name) LIKE UPPER (skeyword)
                      UNION ALL
                      /* File search on Products */
                      SELECT art.file_name, art.file_path, pkg.pkg_name,
                             pv.pv_id, pv.pkg_version, art.crc_cksum
                        FROM work_in_progress rc,
                             PACKAGES pkg,
                             package_versions pv,
                             product_components art
                       WHERE rc.pv_id = art.pv_id
                         AND pv.pkg_id = pkg.pkg_id
                         AND rc.pv_id = pv.pv_id
                         AND rc.rtag_id = nrtagid
                         AND UPPER (art.file_name) LIKE UPPER (skeyword)) qry
               WHERE ROWNUM <= npagesize
            ORDER BY UPPER (qry.pkg_name);
      ELSIF nsearcharea = 1
      THEN
         /* Search Pending */
         OPEN recordset FOR
            SELECT   qry.*
                FROM (
                      /* File search on Packages */
                      SELECT art.file_name, art.file_path, pkg.pkg_name,
                             pv.pv_id, pv.pkg_version, art.crc_cksum
                        FROM planned rc,
                             PACKAGES pkg,
                             package_versions pv,
                             release_components art
                       WHERE rc.pv_id = art.pv_id
                         AND pv.pkg_id = pkg.pkg_id
                         AND rc.pv_id = pv.pv_id
                         AND rc.rtag_id = nrtagid
                         AND UPPER (art.file_name) LIKE UPPER (skeyword)
                      UNION ALL
                      /* File search on Products */
                      SELECT art.file_name, art.file_path, pkg.pkg_name,
                             pv.pv_id, pv.pkg_version, art.crc_cksum
                        FROM planned rc,
                             PACKAGES pkg,
                             package_versions pv,
                             product_components art
                       WHERE rc.pv_id = art.pv_id
                         AND pv.pkg_id = pkg.pkg_id
                         AND rc.pv_id = pv.pv_id
                         AND rc.rtag_id = nrtagid
                         AND UPPER (art.file_name) LIKE UPPER (skeyword)) qry
               WHERE ROWNUM <= npagesize
            ORDER BY UPPER (qry.pkg_name);
      ELSIF nsearcharea = 2
      THEN
         /* Search Released */
         OPEN recordset FOR
            SELECT   qry.*
                FROM (
                      /* File search on Packages */
                      SELECT art.file_name, art.file_path, pkg.pkg_name,
                             pv.pv_id, pv.pkg_version, art.crc_cksum,
                             NULL AS patch_id
                        FROM release_content rc,
                             PACKAGES pkg,
                             package_versions pv,
                             release_components art
                       WHERE rc.pv_id = art.pv_id
                         AND pv.pkg_id = pkg.pkg_id
                         AND rc.pv_id = pv.pv_id
                         AND rc.rtag_id = nrtagid
                         AND UPPER (art.file_name) LIKE UPPER (skeyword)
                      UNION ALL
                      /* File search on Products */
                      SELECT art.file_name, art.file_path, pkg.pkg_name,
                             pv.pv_id, pv.pkg_version, art.crc_cksum,
                             NULL AS patch_id
                        FROM release_content rc,
                             PACKAGES pkg,
                             package_versions pv,
                             product_components art
                       WHERE rc.pv_id = art.pv_id
                         AND pv.pkg_id = pkg.pkg_id
                         AND rc.pv_id = pv.pv_id
                         AND rc.rtag_id = nrtagid
                         AND UPPER (art.file_name) LIKE UPPER (skeyword)
                      UNION ALL
                      /* File search on Patches */
                      SELECT art.file_name, art.file_path, pkg.pkg_name,
                             pv.pv_id, pv.pkg_version, art.crc_cksum,
                             DECODE (art.file_path,
                                     NULL, pp.patch_id,
                                     NULL
                                
    ) AS patch_id
                        FROM release_content rc,
                             PACKAGES pkg,
                             package_versions pv,
                             release_components art,
                             package_patches pp
                       WHERE pv.pv_id = pp.pv_id
                         AND pv.pkg_id = pkg.pkg_id
                         AND rc.rtag_id = nrtagid
                         AND art.pv_id = pp.patch_id
                         AND rc.pv_id = pp.pv_id
                         AND UPPER (art.file_name) LIKE UPPER (skeyword)) qry
               WHERE ROWNUM <= npagesize
            ORDER BY UPPER (qry.pkg_name);
      ELSIF nsearcharea = 3
      THEN
         /* Search ALL */
         OPEN recordset FOR
            SELECT   qry.*
                FROM (
                      /* File search on Packages */
                      SELECT art.file_name, art.file_path, pkg.pkg_name,
                             pv.pv_id, pv.pkg_version, art.crc_cksum,
                             NULL AS patch_id
                        FROM environment_view rc,
                             PACKAGES pkg,
                             package_versions pv,
                             release_components art
                       WHERE rc.pv_id = art.pv_id
                         AND pv.pkg_id = pkg.pkg_id
                         AND rc.pv_id = pv.pv_id
                         AND rc.rtag_id = nrtagid
                         AND UPPER (art.file_name) LIKE UPPER (skeyword)
                      UNION ALL
                      /* File search on Products */
                      SELECT art.file_name, art.file_path, pkg.pkg_name,
                             pv.pv_id, pv.pkg_version, art.crc_cksum,
                             NULL AS patch_id
                        FROM environment_view rc,
                             PACKAGES pkg,
                             package_versions pv,
                             product_components art
                       WHERE rc.pv_id = art.pv_id
                         AND pv.pkg_id = pkg.pkg_id
                         AND rc.pv_id = pv.pv_id
                         AND rc.rtag_id = nrtagid
                         AND UPPER (art.file_name) LIKE UPPER (skeyword)
                      UNION ALL
                      /* File search on Patches */
                      SELECT art.file_name, art.file_path, pkg.pkg_name,
                             pv.pv_id, pv.pkg_version, art.crc_cksum,
                             DECODE (art.file_path,
                                     NULL, pp.patch_id,
                                     NULL
                                    ) AS patch_id
                        FROM release_content rc,
                             PACKAGES pkg,
                             package_versions pv,
                             release_components art,
                             package_patches pp
                       WHERE pv.pv_id = pp.pv_id
                         AND pv.pkg_id = pkg.pkg_id
                         AND rc.rtag_id = nrtagid
                         AND art.pv_id = pp.patch_id
                         AND rc.pv_id = pp.pv_id
                         AND UPPER (art.file_name) LIKE UPPER (skeyword)) qry
               WHERE ROWNUM <= npagesize
            ORDER BY UPPER (qry.pkg_name);
      END IF;
   END;

/*-------------------------------------------------------------------------------------------------------*/
   PROCEDURE get_prodrelease_items (
      rtagid             IN       NUMBER,
      ntruerecordcount   OUT      NUMBER,
      recordset          OUT      typecur
   )
   IS
   BEGIN
      -- Get true record count n the number of integration products
      SELECT COUNT (rc.pv_id)
        INTO ntruerecordcount
        FROM release_content rc
       WHERE rc.rtag_id = rtagid;

      OPEN recordset FOR
         SELECT   vi.view_id, vi.view_name, rel.pkg_state,    --rel.pkg_state,
                  rel.deprecated_state, pv.pv_id, pkg.pkg_name,
                  pv.pkg_version, pv.dlocked, pv.pv_description
             FROM release_content rel,
                  PACKAGES pkg,
                  package_versions pv,
                  views vi
            WHERE pv.pkg_id = pkg.pkg_id
              AND rel.pv_id = pv.pv_id
              AND rel.base_view_id = vi.view_id
              AND pv.is_deployable = 'Y'
              AND rtag_id = rtagid
              AND pv.pv_id NOT IN (SELECT DISTINCT prod_id
                                              FROM deployment_manager.os_contents)
              AND rel.product_state IS NULL
         ORDER BY UPPER (vi.view_name), UPPER (pkg.pkg_name);
   END;

/*-------------------------------------------------------------------------------------------------------*/
   PROCEDURE get_integration_items (
      rtagid             IN       NUMBER,
      ntruerecordcount   OUT      NUMBER,
      recordset          OUT      typecur
   )
   IS
   BEGIN
      -- Get true record count n the number of integration products
      SELECT COUNT (rc.pv_id)
        INTO ntruerecordcount
        FROM release_content rc
       WHERE rc.rtag_id = rtagid AND rc.product_state = 1;

      OPEN recordset FOR
         SELECT   vi.view_id, vi.view_name, rel.pkg_state,
                  rel.deprecated_state, pv.pv_id, pkg.pkg_name,
                  pv.pkg_version, pv.dlocked, pv.pv_description
             FROM release_content rel,
                  PACKAGES pkg,
                  package_versions pv,
                  views vi
            WHERE pv.pkg_id = pkg.pkg_id
              AND rel.pv_id = pv.pv_id
              AND rel.base_view_id = vi.view_id
              AND pv.is_deployable = 'Y'
              AND rtag_id = rtagid
              AND rel.product_state IN (1, 5)
         ORDER BY UPPER (vi.view_name), UPPER (pkg.pkg_name);
   END;

/*-------------------------------------------------------------------------------------------------------*/
   PROCEDURE get_test_items (
      rtagid             IN       NUMBER,
      ntruerecordcount   OUT      NUMBER,
      recordset          OUT      typecur
   )
   IS
   BEGIN
      -- Get true record count n the number of test products
      SELECT COUNT (rc.pv_id)
        INTO ntruerecordcount
        FROM release_content rc
       WHERE rc.rtag_id = rtagid AND rc.product_state = 2;

      OPEN recordset FOR
         SELECT   vi.view_id, vi.view_name, rel.pkg_state,
                  rel.deprecated_state, pv.pv_id, pkg.pkg_name,
                  pv.pkg_version, pv.dlocked, pv.pv_description
             FROM release_content rel,
                  PACKAGES pkg,
                  package_versions pv,
                  views vi
            WHERE pv.pkg_id = pkg.pkg_id
              AND rel.pv_id = pv.pv_id
              AND rel.base_view_id = vi.view_id
              AND pv.is_deployable = 'Y'
              AND rtag_id = rtagid
              AND rel.product_state = 2
         ORDER BY UPPER (vi.view_name), UPPER (pkg.pkg_name);
   END;

/*-------------------------------------------------------------------------------------------------------*/
   PROCEDURE get_deploy_items (
      rtagid             IN       NUMBER,
      ntruerecordcount   OUT      NUMBER,
      recordset          OUT      typecur
   )
   IS
   BEGIN
      -- Get true record count n the number of deploy products
      SELECT COUNT (rc.pv_id)
        INTO ntruerecordcount
        FROM release_content rc
       WHERE rc.rtag_id = rtagid AND rc.product_state = 3;

      OPEN recordset FOR
         SELECT   vi.view_id, vi.view_name, rel.pkg_state,
                  rel.deprecated_state, pv.pv_id, pkg.pkg_name,
                  pv.pkg_version, pv.dlocked, pv.pv_description
             FROM release_content rel,
                  PACKAGES pkg,
                  package_versions pv,
                  views vi
            WHERE pv.pkg_id = pkg.pkg_id
              AND rel.pv_id = pv.pv_id
              AND rel.base_view_id = vi.view_id
              AND pv.is_deployable = 'Y'
              AND rtag_id = rtagid
              AND rel.product_state IN (3, 5)
              AND pv.pv_id NOT IN (SELECT DISTINCT 
prod_id
                                              FROM deployment_manager.os_contents)
         ORDER BY UPPER (vi.view_name), UPPER (pkg.pkg_name);
   END;

/*-------------------------------------------------------------------------------------------------------*/
   PROCEDURE get_reject_items (
      rtagid             IN       NUMBER,
      ntruerecordcount   OUT      NUMBER,
      recordset          OUT      typecur
   )
   IS
   BEGIN
      -- Get true record count n the number of reject products
      SELECT COUNT (rc.pv_id)
        INTO ntruerecordcount
        FROM release_content rc
       WHERE rc.rtag_id = rtagid AND rc.product_state = 4;

      OPEN recordset FOR
         SELECT   vi.view_id, vi.view_name, rel.pkg_state,
                  rel.deprecated_state, pv.pv_id, pkg.pkg_name,
                  pv.pkg_version, pv.dlocked, pv.pv_description
             FROM release_content rel,
                  PACKAGES pkg,
                  package_versions pv,
                  views vi
            WHERE pv.pkg_id = pkg.pkg_id
              AND rel.pv_id = pv.pv_id
              AND rel.base_view_id = vi.view_id
              AND pv.is_deployable = 'Y'
              AND rtag_id = rtagid
              AND rel.product_state = 4
         ORDER BY UPPER (vi.view_name), UPPER (pkg.pkg_name);
   END;
/*-------------------------------------------------------------------------------------------------------*/
END pk_environment;
/
ALTER PACKAGE "RELEASE_MANAGER"."PK_ENVIRONMENT" 
  COMPILE BODY 
    PLSQL_OPTIMIZE_LEVEL=  2
    PLSQL_CODE_TYPE=  INTERPRETED
    PLSQL_DEBUG=  TRUE
 REUSE SETTINGS TIMESTAMP '2008-05-09 14:47:20'
/