WebSVN – DevTools – Diff – /ManagerSuite/Manager_Suite_VM/trunk/src/Release Manager.sql

 CREATE TABLE PLANNED
+(
   RTAG_ID NUMBER NOT NULL,
   PV_ID NUMBER NOT NULL,
-  VIEW_ID NUMBER NOT NULL
+  VIEW_ID NUMBER NOT NULL,
+  OPERATION CHAR(1 BYTE)
+)
   TABLESPACE "USERS"
   LOGGING
   PCTFREE 10
   PCTUSED 40
 RTAG_ID
 ) ENABLE
+;
 ALTER TABLE PLANNED
-ADD CONSTRAINT REFPACKAGE_VERSIONS54 FOREIGN KEY
+ADD CONSTRAINT REFRELEASE_TAGS53 FOREIGN KEY
+(
-  PV_ID
+  RTAG_ID
+)
-REFERENCES PACKAGE_VERSIONS
+REFERENCES RELEASE_TAGS
+(
-PV_ID
+RTAG_ID
 ) ENABLE
+;
 ALTER TABLE PLANNED
-ADD CONSTRAINT REFRELEASE_TAGS53 FOREIGN KEY
+ADD CONSTRAINT REFPACKAGE_VERSIONS54 FOREIGN KEY
+(
-  RTAG_ID
+  PV_ID
+)
-REFERENCES RELEASE_TAGS
+REFERENCES PACKAGE_VERSIONS
+(
-RTAG_ID
+PV_ID
 ) ENABLE
+;
 ALTER TABLE PLANNED
 ADD CONSTRAINT REFVIEWS55 FOREIGN KEY
     rc.RTAG_ID,
     rc.BASE_VIEW_ID AS VIEW_ID,
     rc.PKG_STATE,
  rc.DEPRECATED_STATE,
     rc.INSERTOR_ID,
-    rc.INSERT_STAMP
+    rc.INSERT_STAMP,
+    ' ' AS OPERATION
   FROM RELEASE_CONTENT rc
 UNION
 SELECT 0 AS ENV_AREA,
     wip.PV_ID,
     wip.RTAG_ID,
     wip.VIEW_ID,
     NULL AS PKG_STATE,
  NULL AS DEPRECATED_STATE,
     NULL AS INSERTOR_ID,
-    NULL AS INSERT_STAMP
+    NULL AS INSERT_STAMP,
+    ' ' AS OPERATION
   FROM WORK_IN_PROGRESS wip
 UNION
 SELECT 1 AS ENV_AREA,
     pl.PV_ID,
     pl.RTAG_ID,
     pl.VIEW_ID,
     NULL AS PKG_STATE,
  NULL AS DEPRECATED_STATE,
     NULL AS INSERTOR_ID,
-    NULL AS INSERT_STAMP
+    NULL AS INSERT_STAMP,
+    pl.operation
-  FROM PLANNED pl;
+FROM PLANNED pl;
 CREATE UNIQUE INDEX UNQ_PKG_BUILD_ENV ON PACKAGE_BUILD_ENV (PV_ID ASC, BE_ID ASC)  TABLESPACE "USERS"
   LOGGING
   PCTFREE 10
   INITRANS 2
          WHERE group_email_id NOT IN (SELECT group_email_id
                                         FROM members_group);
 END delete_autobuild_failure_info;
+/
-CREATE OR REPLACE PACKAGE PK_ENVIRONMENT IS
+CREATE OR REPLACE PACKAGE                   "PK_ENVIRONMENT" IS
 /*
 ------------------------------
-||  Last Modified:  Rupesh Solanki
+||  Last Modified:  G.Huddy
-||  Modified Date:  29/Jan/2007
+||  Modified Date:  28/May/2008
-||  Spec Version:   1.1
+||  Spec Version:   1.2
 ------------------------------
 */
 	TYPE typeCur IS REF CURSOR;
-    /*================================================================================================*/
+	/*================================================================================================*/
 	FUNCTION SELECT_ENVIRONMENT_AREA ( cDlock IN PACKAGE_VERSIONS.DLOCKED%TYPE ) RETURN NUMBER;
 	FUNCTION GET_PACKAGE_AREA ( PvId IN NUMBER, RtagId IN NUMBER ) RETURN NUMBER;
 	FUNCTION GET_VIEW_LOCATION ( PvId IN NUMBER, RtagId IN NUMBER ) RETURN NUMBER;
 	PROCEDURE ADD_PACKAGE ( PvId IN NUMBER, ViewId IN NUMBER, RtagId IN NUMBER, UserId IN NUMBER );
 	PROCEDURE ADD_PACKAGE_BULK ( PvIdList IN VARCHAR2, ViewId IN NUMBER, RtagId IN NUMBER, UserId IN NUMBER );
 	PROCEDURE REPLACE_PACKAGE ( newPvId IN NUMBER, oldPvId IN NUMBER, RtagId IN NUMBER, UserId IN NUMBER );
 	FUNCTION REMOVE_PACKAGE ( PvId IN NUMBER, RtagId IN NUMBER, UserId IN NUMBER, ForceRemove IN CHAR ) RETURN NUMBER;
 	PROCEDURE GET_ENVIRONMENT_ITEMS ( ViewType IN NUMBER, UserId IN NUMBER, RtagId IN NUMBER,  sViewIdShowList IN VARCHAR2, nTrueRecordCount OUT NUMBER, RecordSet OUT typeCur );
 	PROCEDURE GET_RELEASED_ITEMS ( ViewType IN NUMBER, UserId IN NUMBER, RtagId IN NUMBER,  sViewIdShowList IN VARCHAR2, nTrueRecordCount OUT NUMBER, RecordSet OUT typeCur );
 	PROCEDURE GET_PENDING_ITEMS ( ViewType IN NUMBER, UserId IN NUMBER, RtagId IN NUMBER,  sViewIdShowList IN VARCHAR2, nTrueRecordCount OUT NUMBER, RecordSet OUT typeCur );
 	PROCEDURE GET_WORK_IN_PROGRESS_ITEMS ( ViewType IN NUMBER, UserId IN NUMBER, RtagId IN NUMBER,  sViewIdShowList IN VARCHAR2, nTrueRecordCount OUT NUMBER, RecordSet OUT typeCur );
 	PROCEDURE GET_VIEW_CONTENT ( RtagId IN NUMBER, ViewId IN NUMBER, RecordSet OUT typeCur );
 	FUNCTION GET_PACKAGE_VIEW ( PvId IN NUMBER, RtagId IN NUMBER ) RETURN NUMBER;
 	PROCEDURE MAKE_RELEASE ( PvId IN NUMBER, RtagId IN NUMBER, UserId IN NUMBER );
 	PROCEDURE MAKE_UNRELEASE ( PvId IN NUMBER, RtagId IN NUMBER, UserId IN NUMBER );
 	PROCEDURE MAKE_PENDING ( PvId IN NUMBER, RtagId IN NUMBER, UserId IN NUMBER );
 	PROCEDURE MAKE_APPROVED ( PvId IN NUMBER, RtagId IN NUMBER, UserId IN NUMBER );
 	PROCEDURE MAKE_REJECT ( PvId IN NUMBER, RtagId IN NUMBER, UserId IN NUMBER );
+	PROCEDURE APPROVE_MERGE ( PvId IN NUMBER, RtagId IN NUMBER, UserId IN NUMBER );
 	PROCEDURE CHANGE_PACKAGE_VIEW ( PvId IN NUMBER, RtagId IN NUMBER, NewViewId IN NUMBER );
 	PROCEDURE FIND_PACKAGE ( sKeyword IN VARCHAR2, nRtagId IN NUMBER, nSearchArea IN NUMBER, RecordSet OUT typeCur );
 	PROCEDURE FIND_FILE ( sKeyword IN VARCHAR2, nRtagId IN NUMBER, nSearchArea IN NUMBER, nPageSize IN NUMBER, RecordSet OUT typeCur );
 	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,
+							 PACKAGE_VERSIONS.V_EXT%TYPE, SSV_EXT IN PACKAGE_VERSIONS.V_EXT%TYPE,
 							 CloneFromPvId IN NUMBER );
 	PROCEDURE GET_PRODRELEASE_ITEMS ( RtagId IN NUMBER, nTrueRecordCount OUT NUMBER, RecordSet OUT typeCur );
 	PROCEDURE GET_INTEGRATION_ITEMS ( RtagId IN NUMBER, nTrueRecordCount OUT NUMBER, RecordSet OUT typeCur );
 	PROCEDURE GET_TEST_ITEMS ( RtagId IN NUMBER, nTrueRecordCount OUT NUMBER, RecordSet OUT typeCur );
-	PROCEDURE GET_DEPLOY_ITEMS ( RtagId IN NUMBER, nTrueRecordCount OUT NUMBER, RecordSet OUT typeCur );
+	PROCEDURE GET_DEPLOY_ITEMS ( RtagId IN NUMBER, nTrueRecordCount OUT NUMBER, RecordSet OUT typeCur );
 	PROCEDURE GET_REJECT_ITEMS ( RtagId IN NUMBER, nTrueRecordCount OUT NUMBER, RecordSet OUT typeCur );
 	/*================================================================================================*/
 END PK_ENVIRONMENT;
+/
    	Log_Action ( nPvId, 'unlock_package', nUserId );
 END Unlock_Package;
+/
-CREATE OR REPLACE PACKAGE PK_PLANNED IS
+CREATE OR REPLACE PACKAGE                   "PK_PLANNED" IS
 /*
 ------------------------------
-||  Last Modified:  S.Vukovic
+||  Last Modified:  G.Huddy
-||  Modified Date:  6/May/2005
+||  Modified Date:  28/May/2008
-||  Spec Version:   1.0
+||  Spec Version:   1.1
 ------------------------------
 */
 	TYPE typeCur IS REF CURSOR;
     /*================================================================================================*/
 	PROCEDURE ADD_PACKAGE ( newPvId IN NUMBER, ViewId IN NUMBER, RtagId IN NUMBER, UserId IN NUMBER );
 	PROCEDURE REMOVE_PACKAGE ( PvId IN NUMBER, RtagId IN NUMBER, UserId IN NUMBER );
 	FUNCTION GET_PACKAGE_VIEW ( PvId IN NUMBER, RtagId IN NUMBER ) RETURN NUMBER;
 	PROCEDURE GET_VIEW_CONTENT ( RtagId IN NUMBER, ViewId IN NUMBER, RecordSet OUT typeCur );
 	PROCEDURE CHANGE_PACKAGE_VIEW ( PvId IN NUMBER, RtagId IN NUMBER, NewViewId IN NUMBER );
+	PROCEDURE MERGE_PACKAGE ( newPvId IN NUMBER, ViewId IN NUMBER, RtagId IN NUMBER, UserId IN NUMBER, mergeOperation IN CHAR);
 	/*================================================================================================*/
 END PK_PLANNED;
+/
 		 	   WHERE ar.PV_ID = pvid;
 		 --- Remove from Jira Issues
 		 DELETE FROM JIRA_ISSUES jira
 		 	   WHERE jira.PV_ID = pvid;
+         --- Remove from Package Metrics
+         DELETE FROM package_metrics pm
+              WHERE pm.pv_id = pvid;
          --- Finally Remove From Package Versions
          --- Get Package name
          SELECT pv.pkg_id
            INTO pkgid
            FROM package_versions pv
 END;
 /*-------------------------------------------------------------------------------------------------------*/
 END PK_BUILDAPI_TEST;
+/
-CREATE OR REPLACE PACKAGE BODY pk_environment
+CREATE OR REPLACE PACKAGE BODY                   "PK_ENVIRONMENT"
 IS
 /*
 ------------------------------
-||  Last Modified:  Rupesh Solanki
+||  Last Modified:  G.Huddy
-||  Modified Date:  30/Aug/2007
+||  Modified Date:  28/May/2008
-||  Body Version:   1.2
+||  Body Version:   1.3
 ------------------------------
 */
    /*-------------------------------------------------------------------------------------------------------*/
    FUNCTION select_environment_area (cdlock IN package_versions.dlocked%TYPE)
-      RETURN NUMBER
+	  RETURN NUMBER
    IS
    BEGIN
-       /*
+	   /*
-      || N - unlocked
+	  || N - unlocked
-      || Y - release and locked
+	  || Y - release and locked
-      || P - penging approval
+	  || P - penging approval
-      || A - approved package ready for auto-build
+	  || A - approved package ready for auto-build
-      */
+	  */
-      IF cdlock = 'N' OR cdlock = 'R'
+	  IF cdlock = 'N' OR cdlock = 'R'
-      THEN
+	  THEN
-         -- WORK IN PROGRESS --
+		 -- WORK IN PROGRESS --
-         RETURN 0;
+		 RETURN 0;
-      ELSIF cdlock = 'P' OR cdlock = 'A'
+	  ELSIF cdlock = 'P' OR cdlock = 'A'
-      THEN
+	  THEN
-         -- PENDING --
+		 -- PENDING --
-         RETURN 1;
+		 RETURN 1;
-      ELSIF cdlock = 'Y'
+	  ELSIF cdlock = 'Y'
-      THEN
+	  THEN
-         -- RELEASED --
+		 -- RELEASED --
-         RETURN 2;
+		 RETURN 2;
-      ELSE
+	  ELSE
-         -- NOT FOUND --
+		 -- NOT FOUND --
-         raise_application_error
+		 raise_application_error
-                         (-20000,
+						 (-20000,
-                             'Cannot decide where to place package. [cDlock='
+							 'Cannot decide where to place package. [cDlock='
-                          || cdlock
+						  || cdlock
-                          || ']'
+						  || ']'
-                         );
+						 );
-      END IF;
+	  END IF;
    END;
 /*-------------------------------------------------------------------------------------------------------*/
    FUNCTION get_package_area (pvid IN NUMBER, rtagid IN NUMBER)
-      RETURN NUMBER
+	  RETURN NUMBER
    IS
-      envtab    NUMBER            := -1;
+	  envtab	NUMBER			:= -1;
-      CURSOR curarea
+	  CURSOR curarea
-      IS
+	  IS
-         SELECT 2 AS envtab
+		 SELECT 2 AS envtab
-           FROM release_content rc
+		   FROM release_content rc
-          WHERE rc.rtag_id = rtagid AND rc.pv_id = pvid
+		  WHERE rc.rtag_id = rtagid AND rc.pv_id = pvid
-         UNION
+		 UNION
-         SELECT 0 AS envtab
+		 SELECT 0 AS envtab
-           FROM work_in_progress wip
+		   FROM work_in_progress wip
-          WHERE wip.rtag_id = rtagid AND wip.pv_id = pvid
+		  WHERE wip.rtag_id = rtagid AND wip.pv_id = pvid
-         UNION
+		 UNION
-         SELECT 1 AS envtab
+		 SELECT 1 AS envtab
-           FROM planned pl
+		   FROM planned pl
-          WHERE pl.rtag_id = rtagid AND pl.pv_id = pvid;
+		  WHERE pl.rtag_id = rtagid AND pl.pv_id = pvid;
-      recarea   curarea%ROWTYPE;
+	  recarea   curarea%ROWTYPE;
    BEGIN
-      OPEN curarea;
+	  OPEN curarea;
-      FETCH curarea
+	  FETCH curarea
-       INTO recarea;
+	   INTO recarea;
-      IF curarea%FOUND
+	  IF curarea%FOUND
-      THEN
+	  THEN
-         envtab := recarea.envtab;
+		 envtab := recarea.envtab;
-      END IF;
+	  END IF;
-      CLOSE curarea;
+	  CLOSE curarea;
-      RETURN envtab;
+	  RETURN envtab;
    END;
 /*-------------------------------------------------------------------------------------------------------*/
    FUNCTION get_view_location (pvid IN NUMBER, rtagid IN NUMBER)
-      RETURN NUMBER
+	  RETURN NUMBER
    IS
-      ispatch   package_versions.dlocked%TYPE;
+	  ispatch   package_versions.dlocked%TYPE;
-      viewid    NUMBER                          := -1;
+	  viewid	NUMBER						  := -1;
-      CURSOR curview
+	  CURSOR curview
-      IS
+	  IS
-         SELECT rc.base_view_id AS view_id
+		 SELECT rc.base_view_id AS view_id
-           FROM release_content rc
+		   FROM release_content rc
-          WHERE rc.rtag_id = rtagid AND rc.pv_id = pvid
+		  WHERE rc.rtag_id = rtagid AND rc.pv_id = pvid
-         UNION
+		 UNION
-         SELECT wip.view_id AS view_id
+		 SELECT wip.view_id AS view_id
-           FROM work_in_progress wip
+		   FROM work_in_progress wip
-          WHERE wip.rtag_id = rtagid AND wip.pv_id = pvid
+		  WHERE wip.rtag_id = rtagid AND wip.pv_id = pvid
-         UNION
+		 UNION
-         SELECT pl.view_id AS view_id
+		 SELECT pl.view_id AS view_id
-           FROM planned pl
+		   FROM planned pl
-          WHERE pl.rtag_id = rtagid AND pl.pv_id = pvid;
+		  WHERE pl.rtag_id = rtagid AND pl.pv_id = pvid;
-      recview   curview%ROWTYPE;
+	  recview   curview%ROWTYPE;
    BEGIN
-      -- Get dlock state
+	  -- Get dlock state
-      SELECT pv.is_patch
+	  SELECT pv.is_patch
-        INTO ispatch
+		INTO ispatch
-        FROM package_versions pv
+		FROM package_versions pv
-       WHERE pv.pv_id = pvid;
+	   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;
-      -- 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;
+	  RETURN viewid;
    END;
 /*-------------------------------------------------------------------------------------------------------*/
    PROCEDURE add_package (
-      pvid     IN   NUMBER,
+	  pvid	 IN   NUMBER,
-      viewid   IN   NUMBER,
+	  viewid   IN   NUMBER,
-      rtagid   IN   NUMBER,
+	  rtagid   IN   NUMBER,
-      userid   IN   NUMBER
+	  userid   IN   NUMBER
+   )
    IS
-      dlocked   package_versions.dlocked%TYPE;
+	  dlocked   package_versions.dlocked%TYPE;
-      envtab    NUMBER;
+	  envtab	NUMBER;
    BEGIN
-      IF can_edit_pkg_in_project (pvid, rtagid) = 1
+	  IF can_edit_pkg_in_project (pvid, rtagid) = 1
-      THEN
+	  THEN
-         -- Get dlock state
+		 -- Get dlock state
-         SELECT pv.dlocked
+		 SELECT pv.dlocked
-           INTO dlocked
+		   INTO dlocked
-           FROM package_versions pv
+		   FROM package_versions pv
-          WHERE pv.pv_id = pvid;
+		  WHERE pv.pv_id = pvid;
-         -- Get which area should go under
+		 -- Get which area should go under
-         envtab := select_environment_area (dlocked);
+		 envtab := select_environment_area (dlocked);
-         -- Log
+		 -- Log
-         log_action (pvid, 'action', userid, 'Start of Package Add...');
+		 log_action (pvid, 'action', userid, 'Start of Package Add...');
-         -- Remove Package
+		 -- Remove Package
-         IF envtab = 0
+		 IF envtab = 0
-         THEN
+		 THEN
-            -- WORK IN PROGRESS --
+			-- WORK IN PROGRESS --
-            pk_work_in_progress.add_package (pvid, viewid, rtagid, userid);
+			pk_work_in_progress.add_package (pvid, viewid, rtagid, userid);
-         ELSIF envtab = 1
+		 ELSIF envtab = 1
-         THEN
+		 THEN
-            -- PENDING --
+			-- PENDING --
-            pk_planned.add_package (pvid, viewid, rtagid, userid);
+			pk_planned.add_package (pvid, viewid, rtagid, userid);
-         ELSIF envtab = 2
+		 ELSIF envtab = 2
-         THEN
+		 THEN
-            -- RELEASED --
+			-- RELEASED --
-            -- NOTE: this package will be replaced with matching package
+			-- NOTE: this package will be replaced with matching package
-            pk_release.add_package (pvid, viewid, rtagid, userid);
+			pk_release.add_package (pvid, viewid, rtagid, userid);
-            -- Now do post Release Actions
+			-- Now do post Release Actions
-            pk_release.run_post_actions (pvid, rtagid);
+			pk_release.run_post_actions (pvid, rtagid);
-         END IF;
+		 END IF;
-         -- Log
+		 -- Log
-         log_action (pvid, 'action', userid, 'End of Package Add...');
+		 log_action (pvid, 'action', userid, 'End of Package Add...');
-      END IF;
+	  END IF;
    END;
 /*-------------------------------------------------------------------------------------------------------*/
    PROCEDURE add_package_bulk (
-      pvidlist   IN   VARCHAR2,
+	  pvidlist   IN   VARCHAR2,
-      viewid     IN   NUMBER,
+	  viewid	 IN   NUMBER,
-      rtagid     IN   NUMBER,
+	  rtagid	 IN   NUMBER,
-      userid     IN   NUMBER
+	  userid	 IN   NUMBER
+   )
    IS
-      nidcollector   relmgr_number_tab_t            := relmgr_number_tab_t
+	  nidcollector   relmgr_number_tab_t			:= relmgr_number_tab_t
-                                                                          ();
+																		  ();
-      dlocked        package_versions.dlocked%TYPE;
+	  dlocked		package_versions.dlocked%TYPE;
-      pvid           NUMBER;
+	  pvid		   NUMBER;
-      envtab         NUMBER;
+	  envtab		 NUMBER;
    BEGIN
-      /*--------------- Business Rules Here -------------------*/
+	  /*--------------- Business Rules Here -------------------*/
-      IF (pvidlist IS NULL)
+	  IF (pvidlist IS NULL)
-      THEN
+	  THEN
-         raise_application_error (-20000,
+		 raise_application_error (-20000,
-                                  'Please select at least one package.'
+								  'Please select at least one package.'
-                                 );
+								 );
-      END IF;
+	  END IF;
 /*-------------------------------------------------------*/
-      nidcollector := in_list_number (pvidlist);
+	  nidcollector := in_list_number (pvidlist);
-      FOR i IN 1 .. nidcollector.COUNT
+	  FOR i IN 1 .. nidcollector.COUNT
-      LOOP
+	  LOOP
-         pvid := nidcollector (i);
+		 pvid := nidcollector (i);
-         add_package (pvid, viewid, rtagid, userid);
+		 add_package (pvid, viewid, rtagid, userid);
-      END LOOP;
+	  END LOOP;
    END;
 /*-------------------------------------------------------------------------------------------------------*/
    PROCEDURE replace_package (
-      newpvid   IN   NUMBER,
+	  newpvid   IN   NUMBER,
-      oldpvid   IN   NUMBER,
+	  oldpvid   IN   NUMBER,
-      rtagid    IN   NUMBER,
+	  rtagid	IN   NUMBER,
-      userid    IN   NUMBER
+	  userid	IN   NUMBER
+   )
    IS
-      dlocked        package_versions.dlocked%TYPE;
+	  dlocked		package_versions.dlocked%TYPE;
-      viewid         NUMBER;
+	  viewid		 NUMBER;
-      envtab         NUMBER;
+	  envtab		 NUMBER;
-      ROWCOUNT       NUMBER;
+	  ROWCOUNT	   NUMBER;
-      creleasemode   CHAR (1);
+	  creleasemode   CHAR (1);
-      npkgid         NUMBER;
+	  npkgid		 NUMBER;
    BEGIN
-      /*--------------- Business Rules Here -------------------*/
+	  /*--------------- Business Rules Here -------------------*/
-      -- Check if oldPvId exists. It could have been removed
+	  -- Check if oldPvId exists. It could have been removed
-      SELECT COUNT (pv.pv_id)
+	  SELECT COUNT (pv.pv_id)
-        INTO ROWCOUNT
+		INTO ROWCOUNT
-        FROM package_versions pv
+		FROM package_versions pv
-       WHERE pv.pv_id = oldpvid;
+	   WHERE pv.pv_id = oldpvid;
 /*-------------------------------------------------------*/
-      /* This procedure is usually used by "History" option in Release Manager */
+	  /* 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...');
+	  -- 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
+	  -- Replace package
-      IF envtab = 0
+	  IF envtab = 0
-      THEN
+	  THEN
-         -- WORK IN PROGRESS --
+		 -- WORK IN PROGRESS --
-         -- Delete old package
+		 -- Delete old package
-         pk_work_in_progress.remove_package (oldpvid, rtagid, userid);
+		 pk_work_in_progress.remove_package (oldpvid, rtagid, userid);
-         -- Add new package
+		 -- Add new package
-         pk_work_in_progress.add_package (newpvid, viewid, rtagid, userid);
+		 pk_work_in_progress.add_package (newpvid, viewid, rtagid, userid);
-      ELSIF envtab = 1
+	  ELSIF envtab = 1
-      THEN
+	  THEN
-         -- PENDING --
+		 -- 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;
-         -- 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
-      log_action (oldpvid, 'action', userid, 'End of Package Replace...');
+	  log_action (oldpvid, 'action', userid, 'End of Package Replace...');
    END;
 /*-------------------------------------------------------------------------------------------------------*/
    FUNCTION remove_package (
-      pvid          IN   NUMBER,
+	  pvid		  IN   NUMBER,
-      rtagid        IN   NUMBER,
+	  rtagid		IN   NUMBER,
-      userid        IN   NUMBER,
+	  userid		IN   NUMBER,
-      forceremove   IN   CHAR
+	  forceremove   IN   CHAR
+   )
-      RETURN NUMBER
+	  RETURN NUMBER
    IS
-      envtab        NUMBER;
+	  envtab		NUMBER;
-      isused        BOOLEAN;
+	  isused		BOOLEAN;
-      recordcount   NUMBER;
+	  recordcount   NUMBER;
    BEGIN
 /*--------------- Business Rules Here -------------------*/
 /*-------------------------------------------------------*/
-      -- Find location of package
+	  -- Find location of package
-      envtab := get_package_area (pvid, rtagid);
+	  envtab := get_package_area (pvid, rtagid);
-      -- Remove Package
+	  -- Remove Package
-      IF envtab = 0
+	  IF envtab = 0
-      THEN
+	  THEN
-         -- WORK IN PROGRESS --
+		 -- WORK IN PROGRESS --
-         -- Delete package
+		 -- Delete package
-         pk_work_in_progress.remove_package (pvid, rtagid, userid);
+		 pk_work_in_progress.remove_package (pvid, rtagid, userid);
-         RETURN 0;
+		 RETURN 0;
-      ELSIF envtab = 1
+	  ELSIF envtab = 1
-      THEN
+	  THEN
-         -- PENDING --
+		 -- PENDING --
-         -- Delete package
+		 -- Delete package
-         pk_planned.remove_package (pvid, rtagid, userid);
+		 pk_planned.remove_package (pvid, rtagid, userid);
-         RETURN 0;
+		 RETURN 0;
-      ELSIF envtab = 2
+	  ELSIF envtab = 2
-      THEN
+	  THEN
-         -- RELEASED --
+		 -- RELEASED --
-         -- Check if is used by other packages
+		 -- Check if is used by other packages
-         isused := TRUE;
+		 isused := TRUE;
-         IF forceremove = 'N'
+		 IF forceremove = 'N'
-         THEN
+		 THEN
-            SELECT COUNT (pv.pv_id)
+			SELECT COUNT (pv.pv_id)
-              INTO recordcount
+			  INTO recordcount
-              FROM (SELECT dpv.pkg_id, dpv.v_ext
+			  FROM (SELECT dpv.pkg_id, dpv.v_ext
-                      FROM release_content rc,
+					  FROM release_content rc,
-                           package_dependencies dep,
+						   package_dependencies dep,
-                           package_versions dpv
+						   package_versions dpv
-                     WHERE rc.rtag_id = rtagid
+					 WHERE rc.rtag_id = rtagid
-                       AND rc.pv_id = dep.pv_id
+					   AND rc.pv_id = dep.pv_id
-                       AND dep.dpv_id = dpv.pv_id) rdep,
+					   AND dep.dpv_id = dpv.pv_id) rdep,
-                   package_versions pv
+				   package_versions pv
-             WHERE pv.pkg_id = rdep.pkg_id
+			 WHERE pv.pkg_id = rdep.pkg_id
-               AND NVL (pv.v_ext, '|LINK_A_NULL|') =
+			   AND NVL (pv.v_ext, '|LINK_A_NULL|') =
-                                             NVL (rdep.v_ext, '|LINK_A_NULL|')
+											 NVL (rdep.v_ext, '|LINK_A_NULL|')
-               AND pv.pv_id = pvid;
+			   AND pv.pv_id = pvid;
-            IF recordcount > 0
+			IF recordcount > 0
-            THEN
+			THEN
-               RETURN 1;                    -- Return 1 as package being used
+			   RETURN 1;					-- Return 1 as package being used
-            ELSE
+			ELSE
-               isused := FALSE;
+			   isused := FALSE;
-            END IF;
+			END IF;
-         END IF;
+		 END IF;
-         IF forceremove = 'Y' OR NOT isused
+		 IF forceremove = 'Y' OR NOT isused
-         THEN
+		 THEN
-            -- Delete old package
+			-- Delete old package
-            pk_release.remove_package (pvid, rtagid, userid);
+			pk_release.remove_package (pvid, rtagid, userid);
-            -- Now do post Release Actions
+			-- Now do post Release Actions
-            pk_release.run_post_actions (pvid, rtagid);
+			pk_release.run_post_actions (pvid, rtagid);
-            RETURN 0;
+			RETURN 0;
-         END IF;
+		 END IF;
-      END IF;
+	  END IF;
    END;
 /*-------------------------------------------------------------------------------------------------------*/
+   -- DEVI-45275 added the return of the new "operation" column in the recordset.
    PROCEDURE get_environment_items (
-      viewtype           IN       NUMBER,
+	  viewtype		   IN	   NUMBER,
-      userid             IN       NUMBER,
+	  userid			 IN	   NUMBER,
-      rtagid             IN       NUMBER,
+	  rtagid			 IN	   NUMBER,
-      sviewidshowlist    IN       VARCHAR2,
+	  sviewidshowlist	IN	   VARCHAR2,
-      ntruerecordcount   OUT      NUMBER,
+	  ntruerecordcount   OUT	  NUMBER,
-      recordset          OUT      typecur
+	  recordset		  OUT	  typecur
+   )
    IS
    BEGIN
-      -- Get true record count because views can give false count
+	  -- Get true record count because views can give false count
-      SELECT COUNT (pl.pv_id)
+	  SELECT COUNT (pl.pv_id)
-        INTO ntruerecordcount
+		INTO ntruerecordcount
-        FROM environment_view pl
+		FROM environment_view pl
-       WHERE pl.rtag_id = rtagid;
+	   WHERE pl.rtag_id = rtagid;
-      IF viewtype = 1
+	  IF viewtype = 1
-      THEN
+	  THEN
-         /*--- GUEST VIEW ---*/
+		 /*--- GUEST VIEW ---*/
-         OPEN recordset FOR
+		 OPEN recordset FOR
-            SELECT   *
+			SELECT   *
-                FROM (
+				FROM (
-                      /* Base Views collapsed */
+					  /* Base Views collapsed */
-                      SELECT DISTINCT vi.view_id, vi.view_name,
+					  SELECT DISTINCT vi.view_id, vi.view_name,
-                                      TO_NUMBER (NULL) AS pkg_state,
+									  TO_NUMBER (NULL) AS pkg_state,
-                                      TO_NUMBER (NULL) AS deprecated_state,
+									  TO_NUMBER (NULL) AS deprecated_state,
-                                      TO_NUMBER (NULL) AS pv_id,
+									  TO_NUMBER (NULL) AS pv_id,
-                                      NULL AS pkg_name, NULL AS pkg_version,
+									  NULL AS pkg_name, NULL AS pkg_version,
-                                      NULL AS dlocked, NULL AS pv_description
+									  NULL AS dlocked, NULL AS pv_description,
-                                 FROM environment_view rel, views vi
+									  rel.operation
-                                WHERE rel.view_id = vi.view_id
+								 FROM environment_view rel, views vi
-                                  AND rtag_id = rtagid
+								WHERE rel.view_id = vi.view_id
-                                  AND rel.view_id NOT IN (
+								  AND rtag_id = rtagid
-                                         SELECT *
+								  AND rel.view_id NOT IN (
-                                           FROM THE
+										 SELECT *
-                                                   (SELECT CAST
+										   FROM THE
-                                                              (in_list_number
+												   (SELECT CAST
-                                                                  (sviewidshowlist
+															  (in_list_number
-                                                                  ) AS relmgr_number_tab_t
+																  (sviewidshowlist
-                                                              )
+																  ) AS relmgr_number_tab_t
-                                                      FROM DUAL
+															  )
-                                                   ))
+													  FROM DUAL
-                      UNION
+												   ))
-                      /* Base Views expanded */
+					  UNION
-                      SELECT vi.view_id, vi.view_name,
+					  /* Base Views expanded */
-                             DECODE (rel.pkg_state,
+					  SELECT vi.view_id, vi.view_name,
-                                     NULL, 0,
+							 DECODE (rel.pkg_state,
-                                     rel.pkg_state
+									 NULL, 0,
-                                    ) AS pkg_state,
+									 rel.pkg_state
-                             rel.deprecated_state, pv.pv_id, pkg.pkg_name,
+									) AS pkg_state,
-                             pv.pkg_version, pv.dlocked, pv.pv_description
+							 rel.deprecated_state, pv.pv_id, pkg.pkg_name,
-                        FROM environment_view rel,
+							 pv.pkg_version, pv.dlocked, pv.pv_description,
-                             PACKAGES pkg,
+							 rel.operation
-                             package_versions pv,
+						FROM environment_view rel,
-                             views vi
+							 PACKAGES pkg,
-                       WHERE pv.pkg_id = pkg.pkg_id
+							 package_versions pv,
-                         AND rel.pv_id = pv.pv_id
+							 views vi
-                         AND rel.view_id = vi.view_id
+					   WHERE pv.pkg_id = pkg.pkg_id
-                         AND rel.view_id IN (
+						 AND rel.pv_id = pv.pv_id
-                                SELECT *
+						 AND rel.view_id = vi.view_id
-                                  FROM THE
+						 AND rel.view_id IN (
-                                          (SELECT CAST
+								SELECT *
-                                                     (in_list_number
+								  FROM THE
-                                                              (sviewidshowlist) AS relmgr_number_tab_t
+										  (SELECT CAST
-                                                     )
+													 (in_list_number
-                                             FROM DUAL
+															  (sviewidshowlist) AS relmgr_number_tab_t
-                                          ))
+													 )
-                         AND rtag_id = rtagid) ord
+											 FROM DUAL
-            ORDER BY UPPER (ord.view_name), UPPER (ord.pkg_name);
+										  ))
-      ELSIF viewtype = 2
+						 AND rtag_id = rtagid) ord
-      THEN
+			ORDER BY UPPER (ord.view_name), UPPER (ord.pkg_name);
-         /*--- PERSONAL VIEW ---*/
+	  ELSIF viewtype = 2
-         OPEN recordset FOR
+	  THEN
-            SELECT   *
+		 /*--- PERSONAL VIEW ---*/
-                FROM (
+		 OPEN recordset FOR
-                      /* Base Views collapsed */
+			SELECT   *
-                      SELECT DISTINCT vi.view_id, vi.view_name,
+				FROM (
-                                      TO_NUMBER (NULL) AS pkg_state,
+					  /* Base Views collapsed */
-                                      TO_NUMBER (NULL) AS deprecated_state,
+					  SELECT DISTINCT vi.view_id, vi.view_name,
-                                      TO_NUMBER (NULL) AS pv_id,
+									  TO_NUMBER (NULL) AS pkg_state,
-                                      NULL AS pkg_name, NULL AS pkg_version,
+									  TO_NUMBER (NULL) AS deprecated_state,
-                                      NULL AS dlocked, NULL AS pv_description
+									  TO_NUMBER (NULL) AS pv_id,
-                                 FROM environment_view rel,
+									  NULL AS pkg_name, NULL AS pkg_version,
-                                      view_settings vs,
+									  NULL AS dlocked, NULL AS pv_description,
-                                      views vi
+									  rel.operation
-                                WHERE rel.view_id = vi.view_id
+								 FROM environment_view rel,
-                                  AND vs.view_id = rel.view_id
+									  view_settings vs,
-                                  AND vs.user_id = userid
+									  views vi
-                                  AND rtag_id = rtagid
+								WHERE rel.view_id = vi.view_id
-                                  AND rel.view_id NOT IN (
+								  AND vs.view_id = rel.view_id
-                                         SELECT *
+								  AND vs.user_id = userid
-                                           FROM THE
+								  AND rtag_id = rtagid
-                                                   (SELECT CAST
+								  AND rel.view_id NOT IN (
-                                                              (in_list_number
+										 SELECT *
-                                                                  (sviewidshowlist
+										   FROM THE
-                                                                  ) AS relmgr_number_tab_t
+												   (SELECT CAST
-                                                              )
+															  (in_list_number
-                                                      FROM DUAL
+																  (sviewidshowlist
-                                                   ))
+																  ) AS relmgr_number_tab_t
-                      UNION
+															  )
-                      /* Base Views expanded */
+													  FROM DUAL
-                      SELECT vi.view_id, vi.view_name,
+												   ))
-                             DECODE (rel.pkg_state,
+					  UNION
-                                     NULL, 0,
+					  /* Base Views expanded */
-                                     rel.pkg_state
+					  SELECT vi.view_id, vi.view_name,
-                                    ) AS pkg_state,
+							 DECODE (rel.pkg_state,
-                             rel.deprecated_state, pv.pv_id, pkg.pkg_name,
+									 NULL, 0,
-                             pv.pkg_version, pv.dlocked, pv.pv_description
+									 rel.pkg_state
-                        FROM environment_view rel,
+									) AS pkg_state,
-                             PACKAGES pkg,
+							 rel.deprecated_state, pv.pv_id, pkg.pkg_name,
-                             package_versions pv,
+							 pv.pkg_version, pv.dlocked, pv.pv_description,
-                             views vi,
+							 rel.operation
-                             view_settings vs
+						FROM environment_view rel,
-                       WHERE pv.pkg_id = pkg.pkg_id
+							 PACKAGES pkg,
-                         AND rel.pv_id = pv.pv_id
+							 package_versions pv,
-                         AND rel.view_id = vi.view_id
+							 views vi,
-                         AND vs.view_id = vi.view_id
+							 view_settings vs
-                         AND vs.user_id = userid
+					   WHERE pv.pkg_id = pkg.pkg_id
-                         AND rel.view_id IN (
+						 AND rel.pv_id = pv.pv_id
-                                SELECT *
+						 AND rel.view_id = vi.view_id
-                                  FROM THE
+						 AND vs.view_id = vi.view_id
-                                          (SELECT CAST
+						 AND vs.user_id = userid
-                                                     (in_list_number
+						 AND rel.view_id IN (
-                                                              (sviewidshowlist) AS relmgr_number_tab_t
+								SELECT *
-                                                     )
+								  FROM THE
-                                             FROM DUAL
+										  (SELECT CAST
-                                          ))
+													 (in_list_number
-                         AND rtag_id = rtagid
+															  (sviewidshowlist) AS relmgr_number_tab_t
-                      UNION
+													 )
-                      /* Private Views collapsed */
+											 FROM DUAL
-                      SELECT vi.view_id, vi.view_name,
+										  ))
-                             TO_NUMBER (NULL) AS pkg_state,
+						 AND rtag_id = rtagid
-                             TO_NUMBER (NULL) AS deprecated_state,
+					  UNION
-                             TO_NUMBER (NULL) AS pv_id, NULL AS pkg_name,
+					  /* Private Views collapsed */
-                             NULL AS pkg_version, NULL AS dlocked,
+					  SELECT vi.view_id, vi.view_name,
-                             NULL AS pv_description
+							 TO_NUMBER (NULL) AS pkg_state,
-                        FROM view_settings vs,
+							 TO_NUMBER (NULL) AS deprecated_state,
-                             view_def vd,
+							 TO_NUMBER (NULL) AS pv_id, NULL AS pkg_name,
-                             views vi,
+							 NULL AS pkg_version, NULL AS dlocked,
-                             environment_view rel,
+							 NULL AS pv_description,
-                             package_versions pv
+							 rel.operation
-                       WHERE vs.view_id = vi.view_id
+						FROM view_settings vs,
-                         AND rel.pv_id = pv.pv_id
+							 view_def vd,
-                         AND vd.pkg_id = pv.pkg_id
+							 views vi,
-                         AND vd.view_id = vi.view_id
+							 environment_view rel,
-                         AND vi.base_view = 'N'
+							 package_versions pv
-                         AND rel.rtag_id = rtagid
+					   WHERE vs.view_id = vi.view_id
-                         AND vs.user_id = userid
+						 AND rel.pv_id = pv.pv_id
-                         AND vi.view_id NOT IN (
+						 AND vd.pkg_id = pv.pkg_id
-                                SELECT *
+						 AND vd.view_id = vi.view_id
-                                  FROM THE
+						 AND vi.base_view = 'N'
-                                          (SELECT CAST
+						 AND rel.rtag_id = rtagid
-                                                     (in_list_number
+						 AND vs.user_id = userid
-                                                              (sviewidshowlist) AS relmgr_number_tab_t
+						 AND vi.view_id NOT IN (
-                                                     )
+								SELECT *
-                                             FROM DUAL
+								  FROM THE
-                                          ))
+										  (SELECT CAST
-                      UNION
+													 (in_list_number
-                      /* Private Views expanded */
+															  (sviewidshowlist) AS relmgr_number_tab_t
-                      SELECT vi.view_id, vi.view_name,
+													 )
-                             DECODE (rel.pkg_state,
+											 FROM DUAL
-                                     NULL, 0,
+										  ))
-                                     rel.pkg_state
+					  UNION
-                                    ) AS pkg_state,
+					  /* Private Views expanded */
-                             rel.deprecated_state, pv.pv_id, pkg.pkg_name,
+					  SELECT vi.view_id, vi.view_name,
-                             pv.pkg_version, pv.dlocked, pv.pv_description
+							 DECODE (rel.pkg_state,
-                        FROM users usr,
+									 NULL, 0,
-                             view_settings vs,
+									 rel.pkg_state
-                             view_def vd,
+									) AS pkg_state,
-                             views vi,
+							 rel.deprecated_state, pv.pv_id, pkg.pkg_name,
-                             environment_view rel,
+							 pv.pkg_version, pv.dlocked, pv.pv_description,
-                             PACKAGES pkg,
+							 rel.operation
-                             package_versions pv
+						FROM users usr,
-                       WHERE vs.user_id = usr.user_id
+							 view_settings vs,
-                         AND vs.view_id = vi.view_id
+							 view_def vd,
-                         AND vd.view_id = vi.view_id
+							 views vi,
-                         AND pv.pkg_id = pkg.pkg_id
+							 environment_view rel,
-                         AND rel.pv_id = pv.pv_id
+							 PACKAGES pkg,
-                         AND rel.rtag_id = rtagid
+							 package_versions pv
-                         AND vd.pkg_id = pkg.pkg_id
+					   WHERE vs.user_id = usr.user_id
-                         AND vi.base_view = 'N'
+						 AND vs.view_id = vi.view_id
-                         AND vi.view_id IN (
+						 AND vd.view_id = vi.view_id
-                                SELECT *
+						 AND pv.pkg_id = pkg.pkg_id
-                                  FROM THE
+						 AND rel.pv_id = pv.pv_id
-                                          (SELECT CAST
+						 AND rel.rtag_id = rtagid
-                                                     (in_list_number
+						 AND vd.pkg_id = pkg.pkg_id
-                                                              (sviewidshowlist) AS relmgr_number_tab_t
+						 AND vi.base_view = 'N'
-                                                     )
+						 AND vi.view_id IN (
-                                             FROM DUAL
+								SELECT *
-                                          ))
+								  FROM THE
-                         AND usr.user_id = userid) ord
+										  (SELECT CAST
-            ORDER BY UPPER (ord.view_name), UPPER (ord.pkg_name);
+													 (in_list_number
-      END IF;
+															  (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,
+	  viewtype		   IN	   NUMBER,
-      userid             IN       NUMBER,
+	  userid			 IN	   NUMBER,
-      rtagid             IN       NUMBER,
+	  rtagid			 IN	   NUMBER,
-      sviewidshowlist    IN       VARCHAR2,
+	  sviewidshowlist	IN	   VARCHAR2,
-      ntruerecordcount   OUT      NUMBER,
+	  ntruerecordcount   OUT	  NUMBER,
-      recordset          OUT      typecur
+	  recordset		  OUT	  typecur
+   )
    IS
    BEGIN
-      -- Get true record count because views can give false count
+	  -- Get true record count because views can give false count
-      SELECT COUNT (rc.pv_id)
+	  SELECT COUNT (rc.pv_id)
-        INTO ntruerecordcount
+		INTO ntruerecordcount
-        FROM release_content rc
+		FROM release_content rc
-       WHERE rc.rtag_id = rtagid;
+	   WHERE rc.rtag_id = rtagid;
-      IF viewtype = 1
+	  IF viewtype = 1
-      THEN
+	  THEN
-         /*--- GUEST VIEW ---*/
+		 /*--- GUEST VIEW ---*/
-         OPEN recordset FOR
+		 OPEN recordset FOR
-            SELECT   *
+			SELECT   *
-                FROM (
+				FROM (
-                      /* Base Views collapsed */
+					  /* Base Views collapsed */
-                      SELECT DISTINCT vi.view_id, vi.view_name,
+					  SELECT DISTINCT vi.view_id, vi.view_name,
-                                      TO_NUMBER (NULL) AS pkg_state,
+									  TO_NUMBER (NULL) AS pkg_state,
-                                      TO_NUMBER (NULL) AS deprecated_state,
+									  TO_NUMBER (NULL) AS deprecated_state,
-                                      TO_NUMBER (NULL) AS pv_id,
+									  TO_NUMBER (NULL) AS pv_id,
-                                      NULL AS pkg_name, NULL AS pkg_version,
+									  NULL AS pkg_name, NULL AS pkg_version,
-                                      NULL AS dlocked, NULL AS pv_description
+									  NULL AS dlocked, NULL AS pv_description
-                                 FROM release_content rel, views vi
+								 FROM release_content rel, views vi
-                                WHERE rel.base_view_id = vi.view_id
+								WHERE rel.base_view_id = vi.view_id
-                                  AND rtag_id = rtagid
+								  AND rtag_id = rtagid
-                                  AND rel.base_view_id NOT IN (
+								  AND rel.base_view_id NOT IN (
-                                         SELECT *
+										 SELECT *
-                                           FROM THE
+										   FROM THE
-                                                   (SELECT CAST
+												   (SELECT CAST
-                                                              (in_list_number
+															  (in_list_number
-                                                                  (sviewidshowlist
+																  (sviewidshowlist
-                                                                  ) AS relmgr_number_tab_t
+																  ) AS relmgr_number_tab_t
-                                                              )
+															  )
-                                                      FROM DUAL
+													  FROM DUAL
-                                                   ))
+												   ))
-                      UNION
+					  UNION
-                      /* Base Views expanded */
+					  /* Base Views expanded */
-                      SELECT vi.view_id, vi.view_name, rel.pkg_state,
+					  SELECT vi.view_id, vi.view_name, rel.pkg_state,
-                             rel.deprecated_state, pv.pv_id, pkg.pkg_name,
+							 rel.deprecated_state, pv.pv_id, pkg.pkg_name,
-                             pv.pkg_version, pv.dlocked, pv.pv_description
+							 pv.pkg_version, pv.dlocked, pv.pv_description
-                        FROM release_content rel,
+						FROM release_content rel,
-                             PACKAGES pkg,
+							 PACKAGES pkg,
-                             package_versions pv,
+							 package_versions pv,
-                             views vi
+							 views vi
-                       WHERE pv.pkg_id = pkg.pkg_id
+					   WHERE pv.pkg_id = pkg.pkg_id
-                         AND rel.pv_id = pv.pv_id
+						 AND rel.pv_id = pv.pv_id
-                         AND rel.base_view_id = vi.view_id
+						 AND rel.base_view_id = vi.view_id
-                         AND rel.base_view_id IN (
+						 AND rel.base_view_id IN (
-                                SELECT *
+								SELECT *
-                                  FROM THE
+								  FROM THE
-                                          (SELECT CAST
+										  (SELECT CAST
-                                                     (in_list_number
+													 (in_list_number
-                                                              (sviewidshowlist) AS relmgr_number_tab_t
+															  (sviewidshowlist) AS relmgr_number_tab_t
-                                                     )
+													 )
-                                             FROM DUAL
+											 FROM DUAL
-                                          ))
+										  ))
-                         AND rtag_id = rtagid) ord
+						 AND rtag_id = rtagid) ord
-            ORDER BY UPPER (ord.view_name), UPPER (ord.pkg_name);
+			ORDER BY UPPER (ord.view_name), UPPER (ord.pkg_name);
-      ELSIF viewtype = 2
+	  ELSIF viewtype = 2
-      THEN
+	  THEN
-         /*--- PERSONAL VIEW ---*/
+		 /*--- PERSONAL VIEW ---*/
-         OPEN recordset FOR
+		 OPEN recordset FOR
-            SELECT   *
+			SELECT   *
-                FROM (
+				FROM (
-                      /* Base Views collapsed */
+					  /* Base Views collapsed */
-                      SELECT DISTINCT vi.view_id, vi.view_name,
+					  SELECT DISTINCT vi.view_id, vi.view_name,
-                                      TO_NUMBER (NULL) AS pkg_state,
+									  TO_NUMBER (NULL) AS pkg_state,
-                                      TO_NUMBER (NULL) AS deprecated_state,
+									  TO_NUMBER (NULL) AS deprecated_state,
-                                      TO_NUMBER (NULL) AS pv_id,
+									  TO_NUMBER (NULL) AS pv_id,
-                                      NULL AS pkg_name, NULL AS pkg_version,
+									  NULL AS pkg_name, NULL AS pkg_version,
-                                      NULL AS dlocked, NULL AS pv_description
+									  NULL AS dlocked, NULL AS pv_description
-                                 FROM release_content rel,
+								 FROM release_content rel,
-                                      view_settings vs,
+									  view_settings vs,
-                                      views vi
+									  views vi
-                                WHERE rel.base_view_id = vi.view_id
+								WHERE rel.base_view_id = vi.view_id
-                                  AND vs.view_id = rel.base_view_id
+								  AND vs.view_id = rel.base_view_id
-                                  AND vs.user_id = userid
+								  AND vs.user_id = userid
-                                  AND rtag_id = rtagid
+								  AND rtag_id = rtagid
-                                  AND rel.base_view_id NOT IN (
+								  AND rel.base_view_id NOT IN (
-                                         SELECT *
+										 SELECT *
-                                           FROM THE
+										   FROM THE
-                                                   (SELECT CAST
+												   (SELECT CAST
-                                                              (in_list_number
+															  (in_list_number
-                                                                  (sviewidshowlist
+																  (sviewidshowlist
-                                                                  ) AS relmgr_number_tab_t
+																  ) AS relmgr_number_tab_t
-                                                              )
+															  )
-                                                      FROM DUAL
+													  FROM DUAL
-                                                   ))
+												   ))
-                      UNION
+					  UNION
-                      /* Base Views expanded */
+					  /* Base Views expanded */
-                      SELECT vi.view_id, vi.view_name, rel.pkg_state,
+					  SELECT vi.view_id, vi.view_name, rel.pkg_state,
-                             rel.deprecated_state, pv.pv_id, pkg.pkg_name,
+							 rel.deprecated_state, pv.pv_id, pkg.pkg_name,
-                             pv.pkg_version, pv.dlocked, pv.pv_description
+							 pv.pkg_version, pv.dlocked, pv.pv_description
-                        FROM release_content rel,
+						FROM release_content rel,
-                             PACKAGES pkg,
+							 PACKAGES pkg,
-                             package_versions pv,
+							 package_versions pv,
-                             views vi,
+							 views vi,
-                             view_settings vs
+							 view_settings vs
-                       WHERE pv.pkg_id = pkg.pkg_id
+					   WHERE pv.pkg_id = pkg.pkg_id
-                         AND rel.pv_id = pv.pv_id
+						 AND rel.pv_id = pv.pv_id
-                         AND rel.base_view_id = vi.view_id
+						 AND rel.base_view_id = vi.view_id
-                         AND vs.view_id = vi.view_id
+						 AND vs.view_id = vi.view_id
-                         AND vs.user_id = userid
+						 AND vs.user_id = userid
-                         AND rel.base_view_id IN (
+						 AND rel.base_view_id IN (
-                                SELECT *
+								SELECT *
-                                  FROM THE
+								  FROM THE
-                                          (SELECT CAST
+										  (SELECT CAST
-                                                     (in_list_number
+													 (in_list_number
-                                                              (sviewidshowlist) AS relmgr_number_tab_t
+															  (sviewidshowlist) AS relmgr_number_tab_t
-                                                     )
+													 )
-                                             FROM DUAL
+											 FROM DUAL
-                                          ))
+										  ))
-                         AND rtag_id = rtagid
+						 AND rtag_id = rtagid
-                      UNION
+					  UNION
-                      /* Private Views collapsed */
+					  /* Private Views collapsed */
-                      SELECT vi.view_id, vi.view_name,
+					  SELECT vi.view_id, vi.view_name,
-                             TO_NUMBER (NULL) AS pkg_state,
+							 TO_NUMBER (NULL) AS pkg_state,
-                             TO_NUMBER (NULL) AS deprecated_state,
+							 TO_NUMBER (NULL) AS deprecated_state,
-                             TO_NUMBER (NULL) AS pv_id, NULL AS pkg_name,
+							 TO_NUMBER (NULL) AS pv_id, NULL AS pkg_name,
-                             NULL AS pkg_version, NULL AS dlocked,
+							 NULL AS pkg_version, NULL AS dlocked,
-                             NULL AS pv_description
+							 NULL AS pv_description
-                        FROM view_settings vs,
+						FROM view_settings vs,
-                             view_def vd,
+							 view_def vd,
-                             views vi,
+							 views vi,
-                             release_content rel,
+							 release_content rel,
-                             package_versions pv
+							 package_versions pv
-                       WHERE vs.view_id = vi.view_id
+					   WHERE vs.view_id = vi.view_id
-                         AND rel.pv_id = pv.pv_id
+						 AND rel.pv_id = pv.pv_id
-                         AND vd.pkg_id = pv.pkg_id
+						 AND vd.pkg_id = pv.pkg_id
-                         AND vd.view_id = vi.view_id
+						 AND vd.view_id = vi.view_id
-                         AND vi.base_view = 'N'
+						 AND vi.base_view = 'N'
-                         AND rel.rtag_id = rtagid
+						 AND rel.rtag_id = rtagid
-                         AND vs.user_id = userid
+						 AND vs.user_id = userid
-                         AND vi.view_id NOT IN (
+						 AND vi.view_id NOT IN (
-                                SELECT *
+								SELECT *
-                                  FROM THE
+								  FROM THE
-                                          (SELECT CAST
+										  (SELECT CAST
-                                                     (in_list_number
+													 (in_list_number
-                                                              (sviewidshowlist) AS relmgr_number_tab_t
+															  (sviewidshowlist) AS relmgr_number_tab_t
-                                                     )
+													 )
-                                             FROM DUAL
+											 FROM DUAL
-                                          ))
+										  ))
-                      UNION
+					  UNION
-                      /* Private Views expanded */
+					  /* Private Views expanded */
-                      SELECT vi.view_id, vi.view_name, rel.pkg_state,
+					  SELECT vi.view_id, vi.view_name, rel.pkg_state,
-                             rel.deprecated_state, pv.pv_id, pkg.pkg_name,
+							 rel.deprecated_state, pv.pv_id, pkg.pkg_name,
-                             pv.pkg_version, pv.dlocked, pv.pv_description
+							 pv.pkg_version, pv.dlocked, pv.pv_description
-                        FROM users usr,
+						FROM users usr,
-                             view_settings vs,
+							 view_settings vs,
-                             view_def vd,
+							 view_def vd,
-                             views vi,
+							 views vi,
-                             release_content rel,
+							 release_content rel,
-                             PACKAGES pkg,
+							 PACKAGES pkg,
-                             package_versions pv
+							 package_versions pv
-                       WHERE vs.user_id = usr.user_id
+					   WHERE vs.user_id = usr.user_id
-                         AND vs.view_id = vi.view_id
+						 AND vs.view_id = vi.view_id
-                         AND vd.view_id = vi.view_id
+						 AND vd.view_id = vi.view_id
-                         AND pv.pkg_id = pkg.pkg_id
+						 AND pv.pkg_id = pkg.pkg_id
-                         AND rel.pv_id = pv.pv_id
+						 AND rel.pv_id = pv.pv_id
-                         AND rel.rtag_id = rtagid
+						 AND rel.rtag_id = rtagid
-                         AND vd.pkg_id = pkg.pkg_id
+						 AND vd.pkg_id = pkg.pkg_id
-                         AND vi.base_view = 'N'
+						 AND vi.base_view = 'N'
-                         AND vi.view_id IN (
+						 AND vi.view_id IN (
-                                SELECT *
+								SELECT *
-                                  FROM THE
+								  FROM THE
-                                          (SELECT CAST
+										  (SELECT CAST
-                                                     (in_list_number
+													 (in_list_number
-                                                              (sviewidshowlist) AS relmgr_number_tab_t
+															  (sviewidshowlist) AS relmgr_number_tab_t
-                                                     )
+													 )
-                                             FROM DUAL
+											 FROM DUAL
-                                          ))
+										  ))
-                         AND usr.user_id = userid) ord
+						 AND usr.user_id = userid) ord
-            ORDER BY UPPER (ord.view_name), UPPER (ord.pkg_name);
+			ORDER BY UPPER (ord.view_name), UPPER (ord.pkg_name);
-      END IF;
+	  END IF;
    END;
 /*-------------------------------------------------------------------------------------------------------*/
    PROCEDURE get_work_in_progress_items (
-      viewtype           IN       NUMBER,
+	  viewtype		   IN	   NUMBER,
-      userid             IN       NUMBER,
+	  userid			 IN	   NUMBER,
-      rtagid             IN       NUMBER,
+	  rtagid			 IN	   NUMBER,
-      sviewidshowlist    IN       VARCHAR2,
+	  sviewidshowlist	IN	   VARCHAR2,
-      ntruerecordcount   OUT      NUMBER,
+	  ntruerecordcount   OUT	  NUMBER,
-      recordset          OUT      typecur
+	  recordset		  OUT	  typecur
+   )
    IS
    BEGIN
-      -- Get true record count because views can give false count
+	  -- Get true record count because views can give false count
-      SELECT COUNT (wip.pv_id)
+	  SELECT COUNT (wip.pv_id)
-        INTO ntruerecordcount
+		INTO ntruerecordcount
-        FROM work_in_progress wip
+		FROM work_in_progress wip
-       WHERE wip.rtag_id = rtagid;
+	   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,
-      IF viewtype = 1
+							 --rel.pkg_state,
-      THEN
+							 TO_NUMBER (NULL) AS deprecated_state, pv.pv_id,
-         /*--- GUEST VIEW ---*/
+							 pkg.pkg_name, pv.pkg_version, pv.dlocked,
-         OPEN recordset FOR
+							 pv.pv_description
-            SELECT   *
+						FROM work_in_progress rel,
-                FROM (
+							 PACKAGES pkg,
-                      /* Base Views collapsed */
+							 package_versions pv,
-                      SELECT DISTINCT vi.view_id, vi.view_name,
+							 views vi
-                                      TO_NUMBER (NULL) AS pkg_state,
+					   WHERE pv.pkg_id = pkg.pkg_id
-                                      TO_NUMBER (NULL) AS deprecated_state,
+						 AND rel.pv_id = pv.pv_id
-                                      TO_NUMBER (NULL) AS pv_id,
+						 AND rel.view_id = vi.view_id
-                                      NULL AS pkg_name, NULL AS pkg_version,
+						 AND rel.view_id IN (
-                                      NULL AS dlocked, NULL AS pv_description
+								SELECT *
-                                 FROM work_in_progress rel, views vi
+								  FROM THE
-                                WHERE rel.view_id = vi.view_id
+										  (SELECT CAST
-                                  AND rtag_id = rtagid
+													 (in_list_number
-                                  AND rel.view_id NOT IN (
+															  (sviewidshowlist) AS relmgr_number_tab_t
-                                         SELECT *
+													 )
-                                           FROM THE
+											 FROM DUAL
-                                                   (SELECT CAST
+										  ))
-                                                              (in_list_number
+						 AND rtag_id = rtagid) ord
-                                                                  (sviewidshowlist
+			ORDER BY UPPER (ord.view_name), UPPER (ord.pkg_name);
-                                                                  ) AS relmgr_number_tab_t
+	  ELSIF viewtype = 2
-                                                              )
+	  THEN
-                                                      FROM DUAL
+		 /*--- PERSONAL VIEW ---*/
-                                                   ))
+		 OPEN recordset FOR
-                      UNION
+			SELECT   *
-                      /* Base Views expanded */
+				FROM (
-                      SELECT vi.view_id, vi.view_name, 0 AS pkg_state,
+					  /* Base Views collapsed */
+					  SELECT DISTINCT vi.view_id, vi.view_name,
-                             --rel.pkg_state,
+									  TO_NUMBER (NULL) AS pkg_state,
-                             TO_NUMBER (NULL) AS deprecated_state, pv.pv_id,
+									  TO_NUMBER (NULL) AS deprecated_state,
-                             pkg.pkg_name, pv.pkg_version, pv.dlocked,
+									  TO_NUMBER (NULL) AS pv_id,
-                             pv.pv_description
+									  NULL AS pkg_name, NULL AS pkg_version,
-                        FROM work_in_progress rel,
+									  NULL AS dlocked, NULL AS pv_description
-                             PACKAGES pkg,
+								 FROM work_in_progress rel,
-                             package_versions pv,
+									  view_settings vs,
-                             views vi
+									  views vi
-                       WHERE pv.pkg_id = pkg.pkg_id
+								WHERE rel.view_id = vi.view_id
-                         AND rel.pv_id = pv.pv_id
+								  AND vs.view_id = rel.view_id
-                         AND rel.view_id = vi.view_id
+								  AND vs.user_id = userid
-                         AND rel.view_id IN (
+								  AND rtag_id = rtagid
-                                SELECT *
+								  AND rel.view_id NOT IN (
-                                  FROM THE
+										 SELECT *
-                                          (SELECT CAST
+										   FROM THE
-                                                     (in_list_number
+												   (SELECT CAST
-                                                              (sviewidshowlist) AS relmgr_number_tab_t
+															  (in_list_number
-                                                     )
+																  (sviewidshowlist
-                                             FROM DUAL
+																  ) AS relmgr_number_tab_t
-                                          ))
+															  )
-                         AND rtag_id = rtagid) ord
+													  FROM DUAL
-            ORDER BY UPPER (ord.view_name), UPPER (ord.pkg_name);
+												   ))
-      ELSIF viewtype = 2
+					  UNION
-      THEN
+					  /* Base Views expanded */
-         /*--- PERSONAL VIEW ---*/
+					  SELECT vi.view_id, vi.view_name, 0 AS pkg_state,
-         OPEN recordset FOR
-            SELECT   *
+							 --rel.pkg_state,
-                FROM (
+							 TO_NUMBER (NULL) AS deprecated_state, pv.pv_id,
-                      /* Base Views collapsed */
+							 pkg.pkg_name, pv.pkg_version, pv.dlocked,
-                      SELECT DISTINCT vi.view_id, vi.view_name,
+							 pv.pv_description
-                                      TO_NUMBER (NULL) AS pkg_state,
+						FROM work_in_progress rel,
-                                      TO_NUMBER (NULL) AS deprecated_state,
+							 PACKAGES pkg,
-                                      TO_NUMBER (NULL) AS pv_id,
+							 package_versions pv,
-                                      NULL AS pkg_name, NULL AS pkg_version,
+							 views vi,
-                                      NULL AS dlocked, NULL AS pv_description
+							 view_settings vs
-                                 FROM work_in_progress rel,
+					   WHERE pv.pkg_id = pkg.pkg_id
-                                      view_settings vs,
+						 AND rel.pv_id = pv.pv_id
-                                      views vi
+						 AND rel.view_id = vi.view_id
-                                WHERE rel.view_id = vi.view_id
+						 AND vs.view_id = vi.view_id
-                                  AND vs.view_id = rel.view_id
+						 AND vs.user_id = userid
-                                  AND vs.user_id = userid
+						 AND rel.view_id IN (
-                                  AND rtag_id = rtagid
+								SELECT *
-                                  AND rel.view_id NOT IN (
+								  FROM THE
-                                         SELECT *
+										  (SELECT CAST
-                                           FROM THE
+													 (in_list_number
-                                                   (SELECT CAST
+															  (sviewidshowlist) AS relmgr_number_tab_t
-                                                              (in_list_number
+													 )
-                                                                  (sviewidshowlist
+											 FROM DUAL
-                                                                  ) AS relmgr_number_tab_t
+										  ))
-                                                              )
+						 AND rtag_id = rtagid
-                                                      FROM DUAL
+					  UNION
-                                                   ))
+					  /* Private Views collapsed */
-                      UNION
+					  SELECT vi.view_id, vi.view_name,
-                      /* Base Views expanded */
+							 TO_NUMBER (NULL) AS pkg_state,
-                      SELECT vi.view_id, vi.view_name, 0 AS pkg_state,
+							 TO_NUMBER (NULL) AS deprecated_state,
+							 TO_NUMBER (NULL) AS pv_id, NULL AS pkg_name,
-                             --rel.pkg_state,
+							 NULL AS pkg_version, NULL AS dlocked,
-                             TO_NUMBER (NULL) AS deprecated_state, pv.pv_id,
+							 NULL AS pv_description
-                             pkg.pkg_name, pv.pkg_version, pv.dlocked,
+						FROM view_settings vs,
-                             pv.pv_description
+							 view_def vd,
-                        FROM work_in_progress rel,
+							 views vi,
-                             PACKAGES pkg,
+							 work_in_progress rel,
-                             package_versions pv,
+							 package_versions pv
-                             views vi,
+					   WHERE vs.view_id = vi.view_id
-                             view_settings vs
+						 AND rel.pv_id = pv.pv_id
-                       WHERE pv.pkg_id = pkg.pkg_id
+						 AND vd.pkg_id = pv.pkg_id
-                         AND rel.pv_id = pv.pv_id
+						 AND vd.view_id = vi.view_id
-                         AND rel.view_id = vi.view_id
+						 AND vi.base_view = 'N'
-                         AND vs.view_id = vi.view_id
+						 AND rel.rtag_id = rtagid
-                         AND vs.user_id = userid
+						 AND vs.user_id = userid
-                         AND rel.view_id IN (
+						 AND vi.view_id NOT IN (
-                                SELECT *
+								SELECT *
-                                  FROM THE
+								  FROM THE
-                                          (SELECT CAST
+										  (SELECT CAST
-                                                     (in_list_number
+													 (in_list_number
-                                                              (sviewidshowlist) AS relmgr_number_tab_t
+															  (sviewidshowlist) AS relmgr_number_tab_t
-                                                     )
+													 )
-                                             FROM DUAL
+											 FROM DUAL
-                                          ))
+										  ))
-                         AND rtag_id = rtagid
+					  UNION
-                      UNION
+					  /* Private Views expanded */
-                      /* Private Views collapsed */
+					  SELECT vi.view_id, vi.view_name, 0 AS pkg_state,
-                      SELECT vi.view_id, vi.view_name,
-                             TO_NUMBER (NULL) AS pkg_state,
+							 --rel.pkg_state,
-                             TO_NUMBER (NULL) AS deprecated_state,
+							 TO_NUMBER (NULL) AS deprecated_state, pv.pv_id,
-                             TO_NUMBER (NULL) AS pv_id, NULL AS pkg_name,
+							 pkg.pkg_name, pv.pkg_version, pv.dlocked,
-                             NULL AS pkg_version, NULL AS dlocked,
+							 pv.pv_description
-                             NULL AS pv_description
+						FROM users usr,
-                        FROM view_settings vs,
+							 view_settings vs,
-                             view_def vd,
+							 view_def vd,
-                             views vi,
+							 views vi,
-                             work_in_progress rel,
+							 work_in_progress rel,
-                             package_versions pv
+							 PACKAGES pkg,
-                       WHERE vs.view_id = vi.view_id
+							 package_versions pv
-                         AND rel.pv_id = pv.pv_id
+					   WHERE vs.user_id = usr.user_id
-                         AND vd.pkg_id = pv.pkg_id
+						 AND vs.view_id = vi.view_id
-                         AND vd.view_id = vi.view_id
+						 AND vd.view_id = vi.view_id
-                         AND vi.base_view = 'N'
+						 AND pv.pkg_id = pkg.pkg_id
-                         AND rel.rtag_id = rtagid
+						 AND rel.pv_id = pv.pv_id
-                         AND vs.user_id = userid
+						 AND rel.rtag_id = rtagid
-                         AND vi.view_id NOT IN (
+						 AND vd.pkg_id = pkg.pkg_id
-                                SELECT *
+						 AND vi.base_view = 'N'
-                                  FROM THE
+						 AND vi.view_id IN (
-                                          (SELECT CAST
+								SELECT *
-                                                     (in_list_number
+								  FROM THE
-                                                              (sviewidshowlist) AS relmgr_number_tab_t
+										  (SELECT CAST
-                                                     )
+													 (in_list_number
-                                             FROM DUAL
+															  (sviewidshowlist) AS relmgr_number_tab_t
-                                          ))
+													 )
-                      UNION
+											 FROM DUAL
-                      /* Private Views expanded */
+										  ))
-                      SELECT vi.view_id, vi.view_name, 0 AS pkg_state,
+						 AND usr.user_id = userid) ord
+			ORDER BY UPPER (ord.view_name), UPPER (ord.pkg_name);
-                             --rel.pkg_state,
+	  END IF;
-                             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;
 /*-------------------------------------------------------------------------------------------------------*/
+   -- DEVI-45275 added the return of the new "operation" column in the recordset.
    PROCEDURE get_pending_items (
-      viewtype           IN       NUMBER,
+	  viewtype		   IN	   NUMBER,
-      userid             IN       NUMBER,
+	  userid			 IN	   NUMBER,
-      rtagid             IN       NUMBER,
+	  rtagid			 IN	   NUMBER,
-      sviewidshowlist    IN       VARCHAR2,
+	  sviewidshowlist	IN	   VARCHAR2,
-      ntruerecordcount   OUT      NUMBER,
+	  ntruerecordcount   OUT	  NUMBER,
-      recordset          OUT      typecur
+	  recordset		  OUT	  typecur
+   )
    IS
    BEGIN
-      -- Get true record count because views can give false count
+	  -- Get true record count because views can give false count
-      SELECT COUNT (pl.pv_id)
+	  SELECT COUNT (pl.pv_id)
-        INTO ntruerecordcount
+		INTO ntruerecordcount
-        FROM planned pl
+		FROM planned pl
-       WHERE pl.rtag_id = rtagid;
+	   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,
+									  rel.operation
+								 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,
-      IF viewtype = 1
+							 --rel.pkg_state,
-      THEN
+							 TO_NUMBER (NULL) AS deprecated_state, pv.pv_id,
-         /*--- GUEST VIEW ---*/
+							 pkg.pkg_name, pv.pkg_version, pv.dlocked,
-         OPEN recordset FOR
+							 pv.pv_description,
-            SELECT   *
+							 rel.operation
-                FROM (
+						FROM planned rel,
-                      /* Base Views collapsed */
+							 PACKAGES pkg,
-                      SELECT DISTINCT vi.view_id, vi.view_name,
+							 package_versions pv,
-                                      TO_NUMBER (NULL) AS pkg_state,
+							 views vi
-                                      TO_NUMBER (NULL) AS deprecated_state,
+					   WHERE pv.pkg_id = pkg.pkg_id
-                                      TO_NUMBER (NULL) AS pv_id,
+						 AND rel.pv_id = pv.pv_id
-                                      NULL AS pkg_name, NULL AS pkg_version,
+						 AND rel.view_id = vi.view_id
-                                      NULL AS dlocked, NULL AS pv_description
+						 AND rel.view_id IN (
-                                 FROM planned rel, views vi
+								SELECT *
-                                WHERE rel.view_id = vi.view_id
+								  FROM THE
-                                  AND rtag_id = rtagid
+										  (SELECT CAST
-                                  AND rel.view_id NOT IN (
+													 (in_list_number
-                                         SELECT *
+															  (sviewidshowlist) AS relmgr_number_tab_t
-                                           FROM THE
+													 )
-                                                   (SELECT CAST
+											 FROM DUAL
-                                                              (in_list_number
+										  ))
-                                                                  (sviewidshowlist
+						 AND rtag_id = rtagid) ord
-                                                                  ) AS relmgr_number_tab_t
+			ORDER BY UPPER (ord.view_name), UPPER (ord.pkg_name);
-                                                              )
+	  ELSIF viewtype = 2
-                                                      FROM DUAL
+	  THEN
-                                                   ))
+		 /*--- PERSONAL VIEW ---*/
-                      UNION
+		 OPEN recordset FOR
-                      /* Base Views expanded */
+			SELECT   *
-                      SELECT vi.view_id, vi.view_name, 0 AS pkg_state,
+				FROM (
+					  /* Base Views collapsed */
-                             --rel.pkg_state,
+					  SELECT DISTINCT vi.view_id, vi.view_name,
-                             TO_NUMBER (NULL) AS deprecated_state, pv.pv_id,
+									  TO_NUMBER (NULL) AS pkg_state,
-                             pkg.pkg_name, pv.pkg_version, pv.dlocked,
+									  TO_NUMBER (NULL) AS deprecated_state,
-                             pv.pv_description
+									  TO_NUMBER (NULL) AS pv_id,
-                        FROM planned rel,
+									  NULL AS pkg_name, NULL AS pkg_version,
-                             PACKAGES pkg,
+									  NULL AS dlocked, NULL AS pv_description,
-                             package_versions pv,
+									  rel.operation
-                             views vi
+								 FROM planned rel, view_settings vs, views vi
-                       WHERE pv.pkg_id = pkg.pkg_id
+								WHERE rel.view_id = vi.view_id
-                         AND rel.pv_id = pv.pv_id
+								  AND vs.view_id = rel.view_id
-                         AND rel.view_id = vi.view_id
+								  AND vs.user_id = userid
-                         AND rel.view_id IN (
+								  AND rtag_id = rtagid
-                                SELECT *
+								  AND rel.view_id NOT IN (
-                                  FROM THE
+										 SELECT *
-                                          (SELECT CAST
+										   FROM THE
-                                                     (in_list_number
+												   (SELECT CAST
-                                                              (sviewidshowlist) AS relmgr_number_tab_t
+															  (in_list_number
-                                                     )
+																  (sviewidshowlist
-                                             FROM DUAL
+																  ) AS relmgr_number_tab_t
-                                          ))
+															  )
-                         AND rtag_id = rtagid) ord
+													  FROM DUAL
-            ORDER BY UPPER (ord.view_name), UPPER (ord.pkg_name);
+												   ))
-      ELSIF viewtype = 2
+					  UNION
-      THEN
+					  /* Base Views expanded */
-         /*--- PERSONAL VIEW ---*/
+					  SELECT vi.view_id, vi.view_name, 0 AS pkg_state,
-         OPEN recordset FOR
-            SELECT   *
+							 --rel.pkg_state,
-                FROM (
+							 TO_NUMBER (NULL) AS deprecated_state, pv.pv_id,
-                      /* Base Views collapsed */
+							 pkg.pkg_name, pv.pkg_version, pv.dlocked,
-                      SELECT DISTINCT vi.view_id, vi.view_name,
+							 pv.pv_description,
-                                      TO_NUMBER (NULL) AS pkg_state,
+							 rel.operation
-                                      TO_NUMBER (NULL) AS deprecated_state,
+						FROM planned rel,
-                                      TO_NUMBER (NULL) AS pv_id,
+							 PACKAGES pkg,
-                                      NULL AS pkg_name, NULL AS pkg_version,
+							 package_versions pv,
-                                      NULL AS dlocked, NULL AS pv_description
+							 views vi,
-                                 FROM planned rel, view_settings vs, views vi
+							 view_settings vs
-                                WHERE rel.view_id = vi.view_id
+					   WHERE pv.pkg_id = pkg.pkg_id
-                                  AND vs.view_id = rel.view_id
+						 AND rel.pv_id = pv.pv_id
-                                  AND vs.user_id = userid
+						 AND rel.view_id = vi.view_id
-                                  AND rtag_id = rtagid
+						 AND vs.view_id = vi.view_id
-                                  AND rel.view_id NOT IN (
+						 AND vs.user_id = userid
-                                         SELECT *
+						 AND rel.view_id IN (
-                                           FROM THE
+								SELECT *
-                                                   (SELECT CAST
+								  FROM THE
-                                                              (in_list_number
+										  (SELECT CAST
-                                                                  (sviewidshowlist
+													 (in_list_number
-                                                                  ) AS relmgr_number_tab_t
+															  (sviewidshowlist) AS relmgr_number_tab_t
-                                                              )
+													 )
-                                                      FROM DUAL
+											 FROM DUAL
-                                                   ))
+										  ))
-                      UNION
+						 AND rtag_id = rtagid
-                      /* Base Views expanded */
+					  UNION
-                      SELECT vi.view_id, vi.view_name, 0 AS pkg_state,
+					  /* Private Views collapsed */
+					  SELECT vi.view_id, vi.view_name,
-                             --rel.pkg_state,
+							 TO_NUMBER (NULL) AS pkg_state,
-                             TO_NUMBER (NULL) AS deprecated_state, pv.pv_id,
+							 TO_NUMBER (NULL) AS deprecated_state,
-                             pkg.pkg_name, pv.pkg_version, pv.dlocked,
+							 TO_NUMBER (NULL) AS pv_id, NULL AS pkg_name,
-                             pv.pv_description
+							 NULL AS pkg_version, NULL AS dlocked,
-                        FROM planned rel,
+							 NULL AS pv_description,
-                             PACKAGES pkg,
+							 rel.operation
-                             package_versions pv,
+						FROM view_settings vs,
-                             views vi,
+							 view_def vd,
-                             view_settings vs
+							 views vi,
-                       WHERE pv.pkg_id = pkg.pkg_id
+							 planned rel,
-                         AND rel.pv_id = pv.pv_id
+							 package_versions pv
-                         AND rel.view_id = vi.view_id
+					   WHERE vs.view_id = vi.view_id
-                         AND vs.view_id = vi.view_id
+						 AND rel.pv_id = pv.pv_id
-                         AND vs.user_id = userid
+						 AND vd.pkg_id = pv.pkg_id
-                         AND rel.view_id IN (
+						 AND vd.view_id = vi.view_id
-                                SELECT *
+						 AND vi.base_view = 'N'
-                                  FROM THE
+						 AND rel.rtag_id = rtagid
-                                          (SELECT CAST
+						 AND vs.user_id = userid
-                                                     (in_list_number
+						 AND vi.view_id NOT IN (
-                                                              (sviewidshowlist) AS relmgr_number_tab_t
+								SELECT *
-                                                     )
+								  FROM THE
-                                             FROM DUAL
+										  (SELECT CAST
-                                          ))
+													 (in_list_number
-                         AND rtag_id = rtagid
+															  (sviewidshowlist) AS relmgr_number_tab_t
-                      UNION
+													 )
-                      /* Private Views collapsed */
+											 FROM DUAL
-                      SELECT vi.view_id, vi.view_name,
+										  ))
-                             TO_NUMBER (NULL) AS pkg_state,
+					  UNION
-                             TO_NUMBER (NULL) AS deprecated_state,
+					  /* Private Views expanded */
-                             TO_NUMBER (NULL) AS pv_id, NULL AS pkg_name,
+					  SELECT vi.view_id, vi.view_name, 0 AS pkg_state,
-                             NULL AS pkg_version, NULL AS dlocked,
-                             NULL AS pv_description
+							 --rel.pkg_state,
-                        FROM view_settings vs,
+							 TO_NUMBER (NULL) AS deprecated_state, pv.pv_id,
-                             view_def vd,
+							 pkg.pkg_name, pv.pkg_version, pv.dlocked,
-                             views vi,
+							 pv.pv_description,
-                             planned rel,
+							 rel.operation
-                             package_versions pv
+						FROM users usr,
-                       WHERE vs.view_id = vi.view_id
+							 view_settings vs,
-                         AND rel.pv_id = pv.pv_id
+							 view_def vd,
-                         AND vd.pkg_id = pv.pkg_id
+							 views vi,
-                         AND vd.view_id = vi.view_id
+							 planned rel,
-                         AND vi.base_view = 'N'
+							 PACKAGES pkg,
-                         AND rel.rtag_id = rtagid
+							 package_versions pv
-                         AND vs.user_id = userid
+					   WHERE vs.user_id = usr.user_id
-                         AND vi.view_id NOT IN (
+						 AND vs.view_id = vi.view_id
-                                SELECT *
+						 AND vd.view_id = vi.view_id
-                                  FROM THE
+						 AND pv.pkg_id = pkg.pkg_id
-                                          (SELECT CAST
+						 AND rel.pv_id = pv.pv_id
-                                                     (in_list_number
+						 AND rel.rtag_id = rtagid
-                                                              (sviewidshowlist) AS relmgr_number_tab_t
+						 AND vd.pkg_id = pkg.pkg_id
-                                                     )
+						 AND vi.base_view = 'N'
-                                             FROM DUAL
+						 AND vi.view_id IN (
-                                          ))
+								SELECT *
-                      UNION
+								  FROM THE
-                      /* Private Views expanded */
+										  (SELECT CAST
-                      SELECT vi.view_id, vi.view_name, 0 AS pkg_state,
+													 (in_list_number
+															  (sviewidshowlist) AS relmgr_number_tab_t
-                             --rel.pkg_state,
+													 )
-                             TO_NUMBER (NULL) AS deprecated_state, pv.pv_id,
+											 FROM DUAL
-                             pkg.pkg_name, pv.pkg_version, pv.dlocked,
+										  ))
-                             pv.pv_description
+						 AND usr.user_id = userid) ord
-                        FROM users usr,
+			ORDER BY UPPER (ord.view_name), UPPER (ord.pkg_name);
-                             view_settings vs,
+	  END IF;
-                             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,
+	  rtagid	  IN	   NUMBER,
-      viewid      IN       NUMBER,
+	  viewid	  IN	   NUMBER,
-      recordset   OUT      typecur
+	  recordset   OUT	  typecur
+   )
    IS
-      isbaseview   CHAR (1);
+	  isbaseview   CHAR (1);
    BEGIN
-      -- Check if the view is BASE VIEW
+	  -- Check if the view is BASE VIEW
-      SELECT vi.base_view
+	  SELECT vi.base_view
-        INTO isbaseview
+		INTO isbaseview
-        FROM views vi
+		FROM views vi
-       WHERE vi.view_id = viewid;
+	   WHERE vi.view_id = viewid;
-      IF (isbaseview = 'Y')
+	  IF (isbaseview = 'Y')
-      THEN
+	  THEN
-         -- Get Base view content
+		 -- Get Base view content
-         OPEN recordset FOR
+		 OPEN recordset FOR
-            SELECT   DECODE (rel.pkg_state,
+			SELECT   DECODE (rel.pkg_state,
-                             NULL, 0,
+							 NULL, 0,
-                             rel.pkg_state
+							 rel.pkg_state
-                            ) AS pkg_state,
+							) AS pkg_state,
-                     rel.deprecated_state, pv.pv_id, pkg.pkg_name,
+					 rel.deprecated_state, pv.pv_id, pkg.pkg_name,
-                     pv.pkg_version, pv.dlocked, pv.pv_description,
+					 pv.pkg_version, pv.dlocked, pv.pv_description,
-                     pv.build_type
+					 pv.build_type,
+					 rel.operation
-                FROM environment_view rel, PACKAGES pkg, package_versions pv
+				FROM environment_view rel, PACKAGES pkg, package_versions pv
-               WHERE pv.pkg_id = pkg.pkg_id
+			   WHERE pv.pkg_id = pkg.pkg_id
-                 AND rel.pv_id = pv.pv_id
+				 AND rel.pv_id = pv.pv_id
-                 AND rel.view_id = viewid
+				 AND rel.view_id = viewid
-                 AND rel.rtag_id = rtagid
+				 AND rel.rtag_id = rtagid
-            ORDER BY UPPER (pkg.pkg_name);
+			ORDER BY UPPER (pkg.pkg_name);
-      ELSE
+	  ELSE
-         -- Get non base view content
+		 -- Get non base view content
-         OPEN recordset FOR
+		 OPEN recordset FOR
-            SELECT   DECODE (rel.pkg_state,
+			SELECT   DECODE (rel.pkg_state,
-                             NULL, 0,
+							 NULL, 0,
-                             rel.pkg_state
+							 rel.pkg_state
-                            ) AS pkg_state,
+							) AS pkg_state,
-                     rel.deprecated_state, pv.pv_id, pkg.pkg_name,
+					 rel.deprecated_state, pv.pv_id, pkg.pkg_name,
-                     pv.pkg_version, pv.dlocked, pv.pv_description,
+					 pv.pkg_version, pv.dlocked, pv.pv_description,
-                     pv.build_type
+					 pv.build_type,
+					 rel.operation
-                FROM environment_view rel,
+				FROM environment_view rel,
-                     PACKAGES pkg,
+					 PACKAGES pkg,
-                     package_versions pv,
+					 package_versions pv,
-                     view_def vd
+					 view_def vd
-               WHERE pv.pkg_id = pkg.pkg_id
+			   WHERE pv.pkg_id = pkg.pkg_id
-                 AND rel.pv_id = pv.pv_id
+				 AND rel.pv_id = pv.pv_id
-                 AND rel.rtag_id = rtagid
+				 AND rel.rtag_id = rtagid
-                 AND vd.view_id = viewid
+				 AND vd.view_id = viewid
-                 AND vd.pkg_id = pv.pkg_id
+				 AND vd.pkg_id = pv.pkg_id
-            ORDER BY UPPER (pkg.pkg_name);
+			ORDER BY UPPER (pkg.pkg_name);
-      END IF;
+	  END IF;
    END;
 /*-------------------------------------------------------------------------------------------------------*/
    FUNCTION get_package_view (pvid IN NUMBER, rtagid IN NUMBER)
-      RETURN NUMBER
+	  RETURN NUMBER
    IS
-      envtab             NUMBER;
+	  envtab			 NUMBER;
-      returnvalue        NUMBER;
+	  returnvalue		NUMBER;
-      return_not_found   NUMBER := -1;
+	  return_not_found   NUMBER := -1;
    BEGIN
-      envtab := get_package_area (pvid, rtagid);
+	  envtab := get_package_area (pvid, rtagid);
-      IF envtab = 0
+	  IF envtab = 0
-      THEN
+	  THEN
-         -- WORK IN PROGRESS --
+		 -- WORK IN PROGRESS --
-         returnvalue := pk_work_in_progress.get_package_view (pvid, rtagid);
+		 returnvalue := pk_work_in_progress.get_package_view (pvid, rtagid);
-      ELSIF envtab = 1
+	  ELSIF envtab = 1
-      THEN
+	  THEN
-         -- PENDING --
+		 -- PENDING --
-         returnvalue := pk_planned.get_package_view (pvid, rtagid);
+		 returnvalue := pk_planned.get_package_view (pvid, rtagid);
-      ELSIF envtab = 2
+	  ELSIF envtab = 2
-      THEN
+	  THEN
-         -- RELEASED --
+		 -- RELEASED --
-         returnvalue := pk_release.get_package_view (pvid, rtagid);
+		 returnvalue := pk_release.get_package_view (pvid, rtagid);
-      ELSE
+	  ELSE
-         -- This may be a Patch not located anywhere but unlocked
+		 -- This may be a Patch not located anywhere but unlocked
-         returnvalue := return_not_found;
+		 returnvalue := return_not_found;
-      END IF;
+	  END IF;
-      RETURN returnvalue;
+	  RETURN returnvalue;
    END;
 /*-------------------------------------------------------------------------------------------------------*/
    PROCEDURE make_release (pvid IN NUMBER, rtagid IN NUMBER, userid IN NUMBER)
    IS
-      viewid          NUMBER;
+	  viewid		  NUMBER;
-      envtab          NUMBER;
+	  envtab		  NUMBER;
-      ispatch         CHAR (1) := NULL;
+	  ispatch		 CHAR (1) := NULL;
-      buildtype       CHAR (1) := NULL;
+	  buildtype	   CHAR (1) := NULL;
-      lastversionid   NUMBER;
+	  lastversionid   NUMBER;
    BEGIN
-      -- Check if package is patch
+	  -- Check if package is patch
-      SELECT pv.is_patch, pv.build_type, pv.last_pv_id
+	  SELECT pv.is_patch, pv.build_type, pv.last_pv_id
-        INTO ispatch, buildtype, lastversionid
+		INTO ispatch, buildtype, lastversionid
-        FROM package_versions pv
+		FROM package_versions pv
-       WHERE pv.pv_id = pvid;
+	   WHERE pv.pv_id = pvid;
-      -- Get ViewId
+	  -- Get ViewId
-      viewid := get_package_view (pvid, rtagid);
+	  viewid := get_package_view (pvid, rtagid);
-      -- Remove from current area
+	  -- Remove from current area
-      envtab := pk_environment.get_package_area (pvid, rtagid);
+	  envtab := pk_environment.get_package_area (pvid, rtagid);
-      -- Make sure that package was in work-in-progress or pending before makeing it release
+	  -- Make sure that package was in work-in-progress or pending before makeing it release
-      -- Exclude patches, ripple builds
+	  -- Exclude patches, ripple builds
-      IF (envtab < 0)
+	  IF (envtab < 0)
-      THEN
+	  THEN
-         -- Not found in work-in-progress or pending
+		 -- Not found in work-in-progress or pending
-         IF (ispatch IS NULL) AND (buildtype = 'M')
+		 IF (ispatch IS NULL) AND (buildtype = 'M')
-         THEN
+		 THEN
-            raise_application_error (-20000,
+			raise_application_error (-20000,
-                                     'This package cannot be released here.'
+									 'This package cannot be released here.'
-                                    );
+									);
-         END IF;
+		 END IF;
-      END IF;
+	  END IF;
-      -- Log
+	  -- Log
-      log_action (pvid, 'action', userid, 'Start of Make Package Release...');
+	  log_action (pvid, 'action', userid, 'Start of Make Package Release...');
-      IF envtab = 0
+	  IF envtab = 0
-      THEN
+	  THEN
-         -- WORK IN PROGRESS --
+		 -- WORK IN PROGRESS --
-         pk_work_in_progress.remove_package (pvid, rtagid, userid);
+		 pk_work_in_progress.remove_package (pvid, rtagid, userid);
-      ELSIF envtab = 1
+	  ELSIF envtab = 1
-      THEN
+	  THEN
-         -- PENDING --
+		 -- PENDING --
-         pk_planned.remove_package (pvid, rtagid, userid);
+		 pk_planned.remove_package (pvid, rtagid, userid);
-      END IF;
+	  END IF;
-      -- Change package state
+	  -- Change package state
-      pk_package.change_state (pvid, 'Y', userid);
+	  pk_package.change_state (pvid, 'Y', userid);
-      -- Make sure it is valid BASE VIEW
+	  -- Make sure it is valid BASE VIEW
-      IF viewid < 1
+	  IF viewid < 1
-      THEN
+	  THEN
-         viewid := 7;                            -- This is default base view
+		 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;
+	  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;
-      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
-      log_action (pvid, 'action', userid, 'End of Make Package Release...');
+	  log_action (pvid, 'action', userid, 'End of Make Package Release...');
    END;
 /*-------------------------------------------------------------------------------------------------------*/
    PROCEDURE auto_make_release (
-      pvid            IN   NUMBER,
+	  pvid			IN   NUMBER,
-      rtagid          IN   NUMBER,
+	  rtagid		  IN   NUMBER,
-      userid          IN   NUMBER,
+	  userid		  IN   NUMBER,
-      vext            IN   package_versions.v_ext%TYPE,
+	  vext			IN   package_versions.v_ext%TYPE,
-      ssv_ext         IN   package_versions.v_ext%TYPE,
+	  ssv_ext		 IN   package_versions.v_ext%TYPE,
-      clonefrompvid   IN   NUMBER
+	  clonefrompvid   IN   NUMBER
+   )
    IS
-      viewid          NUMBER;
+	  viewid		  NUMBER;
-      envtab          NUMBER;
+	  envtab		  NUMBER;
-      ispatch         CHAR (1) := NULL;
+	  ispatch		 CHAR (1) := NULL;
-      buildtype       CHAR (1) := NULL;
+	  buildtype	   CHAR (1) := NULL;
-      lastversionid   NUMBER;
+	  lastversionid   NUMBER;
    BEGIN
-      -- Check if package is patch
+	  -- Check if package is patch
-      SELECT pv.is_patch, pv.build_type, pv.last_pv_id
+	  SELECT pv.is_patch, pv.build_type, pv.last_pv_id
-        INTO ispatch, buildtype, lastversionid
+		INTO ispatch, buildtype, lastversionid
-        FROM package_versions pv
+		FROM package_versions pv
-       WHERE pv.pv_id = pvid;
+	   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;
-      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
-      log_action (pvid, 'action', userid, 'End of Make Package Release...');
+	  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;
+	  viewid   NUMBER;
-      envtab   NUMBER;
+	  envtab   NUMBER;
    BEGIN
-      -- Get ViewId
+	  -- Get ViewId
-      --ViewId := PK_RELEASE.GET_PACKAGE_VIEW ( PvId, RtagId );
+	  --ViewId := PK_RELEASE.GET_PACKAGE_VIEW ( PvId, RtagId );
-      viewid := get_package_view (pvid, rtagid);
+	  viewid := get_package_view (pvid, rtagid);
-      -- Remove from current area
+	  -- Remove from current area
-      envtab := pk_environment.get_package_area (pvid, rtagid);
+	  envtab := pk_environment.get_package_area (pvid, rtagid);
-      -- Log
+	  -- Log
-      log_action (pvid,
+	  log_action (pvid,
-                  'action',
+				  'action',
-                  userid,
+				  userid,
-                  'Start of Make Package UnRelease...'
+				  'Start of Make Package UnRelease...'
-                 );
+				 );
-      IF envtab = 2
+	  IF envtab = 2
-      THEN
+	  THEN
-         -- RELEASE AREA --
+		 -- RELEASE AREA --
-         pk_release.remove_package (pvid, rtagid, userid);
+		 pk_release.remove_package (pvid, rtagid, userid);
-      ELSIF envtab = 1
+	  ELSIF envtab = 1
-      THEN
+	  THEN
-         -- PENDING --
+		 -- PENDING --
-         pk_planned.remove_package (pvid, rtagid, userid);
+		 pk_planned.remove_package (pvid, rtagid, userid);
-      END IF;
+	  END IF;
-      -- Change package state
+	  -- Change package state
-      pk_package.change_state (pvid, 'N', userid);
+	  pk_package.change_state (pvid, 'N', userid);
-      -- Make sure it is valid BASE VIEW
+	  -- Make sure it is valid BASE VIEW
-      IF viewid < 1
+	  IF viewid < 1
-      THEN
+	  THEN
-         viewid := 7;                            -- This is default base view
+		 viewid := 7;							-- This is default base view
-      END IF;
+	  END IF;
-      -- Add package to new area
+	  -- Add package to new area
-      pk_work_in_progress.add_package (pvid, viewid, rtagid, userid);
+	  pk_work_in_progress.add_package (pvid, viewid, rtagid, userid);
-      -- Now do post Release Actions
+	  -- Now do post Release Actions
-      pk_release.run_post_actions (pvid, rtagid);
+	  pk_release.run_post_actions (pvid, rtagid);
-      -- Log
+	  -- Log
-      log_action (pvid, 'action', userid, 'End of Make Package UnRelease...');
+	  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;
+	  viewid	  NUMBER;
-      ispatch     CHAR (1) := NULL;
+	  ispatch	 CHAR (1) := NULL;
-      buildtype   CHAR (1) := NULL;
+	  buildtype   CHAR (1) := NULL;
    BEGIN
-      -- Check if package is patch
+	  -- Check if package is patch
-      SELECT pv.is_patch, pv.build_type
+	  SELECT pv.is_patch, pv.build_type
-        INTO ispatch, buildtype
+		INTO ispatch, buildtype
-        FROM package_versions pv
+		FROM package_versions pv
-       WHERE pv.pv_id = pvid;
+	   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;
-      -- Get ViewId
-      viewid := get_package_view (pvid, rtagid);
-      --ViewId := PK_WORK_IN_PROGRESS.GET_PACKAGE_VIEW ( PvId, RtagId );
-      -- Log
+	  -- 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...');
+	  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
-      log_action (pvid,
+	  log_action (pvid,
-                  'action',
+				  'action',
-                  userid,
+				  userid,
-                  'Start of Package Pending Approval...'
+				  'Start of Package Pending Approval...'
-                 );
+				 );
-      -- Change package state
+	  -- Change package state
-      pk_package.change_state (pvid, 'A', userid);
+	  pk_package.change_state (pvid, 'A', userid);
-      -- Log
+	  -- Log
-      log_action (pvid, 'action', userid,
+	  log_action (pvid, 'action', userid,
-                  'End of Package Pending Approval...');
+				  'End of Package Pending Approval...');
    END;
 /*-------------------------------------------------------------------------------------------------------*/
    PROCEDURE make_reject (pvid IN NUMBER, rtagid IN NUMBER, userid IN NUMBER)
    IS
-      viewid   NUMBER;
+	  viewid   NUMBER;
+	  operation CHAR;
    BEGIN
-      -- Get ViewId
+	  -- Get ViewId
-      viewid := get_package_view (pvid, rtagid);
+	  viewid := get_package_view (pvid, rtagid);
--- ViewId := PK_PLANNED.GET_PACKAGE_VIEW ( PvId, RtagId );
+	  -- ViewId := PK_PLANNED.GET_PACKAGE_VIEW ( PvId, RtagId );
+	  -- DEVI-45275
+	  -- If the package version is an official version that is to be rejected from a merge operation
+	  -- then find out in order to prevent it being added back to work-in-progress table . This will
+	  -- be denoted by the operation field in the planned table being A or S
+	  operation := ' ';
+	  SELECT
+	  (CASE WHEN pl.operation IS NOT NULL THEN pl.operation ELSE ' ' END) INTO operation
+	  FROM planned pl
+	  WHERE pl.PV_ID = pvid
+	  AND pl.RTAG_ID = rtagid;
-      -- Log
+	  -- Log
-      log_action (pvid, 'action', userid, 'Start of Reject Package...');
+	  log_action (pvid, 'action', userid, 'Start of Reject Package...');
-      -- Remove from current area
+	  -- Remove from current area
-      pk_planned.remove_package (pvid, rtagid, userid);
+	  pk_planned.remove_package (pvid, rtagid, userid);
+	  -- DEVI-45275 - Prevent merge candidate from being added back to work-in-progress table .
+	  IF operation <> 'A' AND operation <> 'S'
+	  THEN
-      -- Change package state
+	  -- Change package state
-      pk_package.change_state (pvid, 'R', userid);
+	  pk_package.change_state (pvid, 'R', userid);
-      -- Add package to new area
+	  -- Add package to new area
-      pk_work_in_progress.add_package (pvid, viewid, rtagid, userid);
+	  pk_work_in_progress.add_package (pvid, viewid, rtagid, userid);
+	  ELSE
+		log_action (pvid, 'action', userid, 'Rejected Package Merge');
+	  END IF;
-      -- Log
+	  -- Log
-      log_action (pvid, 'action', userid, 'Start of Reject Package...');
+	  log_action (pvid, 'action', userid, 'End of Reject Package...');
    END;
 /*-------------------------------------------------------------------------------------------------------*/
    PROCEDURE change_package_view (
-      pvid        IN   NUMBER,
+	  pvid		IN   NUMBER,
-      rtagid      IN   NUMBER,
+	  rtagid	  IN   NUMBER,
-      newviewid   IN   NUMBER
+	  newviewid   IN   NUMBER
+   )
    IS
-      envtab   NUMBER;
+	  envtab   NUMBER;
    BEGIN
-      envtab := pk_environment.get_package_area (pvid, rtagid);
+	  envtab := pk_environment.get_package_area (pvid, rtagid);
-      IF envtab = 0
+	  IF envtab = 0
-      THEN
+	  THEN
-         -- WORK IN PROGRESS --
+		 -- WORK IN PROGRESS --
-         pk_work_in_progress.change_package_view (pvid, rtagid, newviewid);
+		 pk_work_in_progress.change_package_view (pvid, rtagid, newviewid);
-      ELSIF envtab = 1
+	  ELSIF envtab = 1
-      THEN
+	  THEN
-         -- PENDING --
+		 -- PENDING --
-         pk_planned.change_package_view (pvid, rtagid, newviewid);
+		 pk_planned.change_package_view (pvid, rtagid, newviewid);
-      ELSIF envtab = 2
+	  ELSIF envtab = 2
-      THEN
+	  THEN
-         -- RELEASED --
+		 -- RELEASED --
-         pk_release.change_package_view (pvid, rtagid, newviewid);
+		 pk_release.change_package_view (pvid, rtagid, newviewid);
-      END IF;
+	  END IF;
    END;
 /*-------------------------------------------------------------------------------------------------------*/
    PROCEDURE find_package (
-      skeyword      IN       VARCHAR2,
+	  skeyword	  IN	   VARCHAR2,
-      nrtagid       IN       NUMBER,
+	  nrtagid	   IN	   NUMBER,
-      nsearcharea   IN       NUMBER,
+	  nsearcharea   IN	   NUMBER,
-      recordset     OUT      typecur
+	  recordset	 OUT	  typecur
+   )
    IS
    BEGIN
-      IF nsearcharea = 0
+	  IF nsearcharea = 0
-      THEN
+	  THEN
-         /* Search Work In Progress */
+		 /* Search Work In Progress */
-         OPEN recordset FOR
+		 OPEN recordset FOR
-            SELECT   nsearcharea AS env_area, vi.view_name, pv.dlocked,
+			SELECT   nsearcharea AS env_area, vi.view_name, pv.dlocked,
-                     pkg.pkg_name, pv.pv_id, pv.pkg_version, pv.comments,
+					 pkg.pkg_name, pv.pv_id, pv.pkg_version, pv.comments,
-                     pv.modified_stamp, usr.full_name, usr.user_email
+					 pv.modified_stamp, usr.full_name, usr.user_email
-                FROM views vi,
+				FROM views vi,
-                     work_in_progress rc,
+					 work_in_progress rc,
-                     PACKAGES pkg,
+					 PACKAGES pkg,
-                     package_versions pv,
+					 package_versions pv,
-                     users usr
+					 users usr
-               WHERE rc.view_id = vi.view_id
+			   WHERE rc.view_id = vi.view_id
-                 AND rc.pv_id = pv.pv_id
+				 AND rc.pv_id = pv.pv_id
-                 AND pkg.pkg_id = pv.pkg_id
+				 AND pkg.pkg_id = pv.pkg_id
-                 AND pv.modifier_id = usr.user_id
+				 AND pv.modifier_id = usr.user_id
-                 AND rc.rtag_id = nrtagid
+				 AND rc.rtag_id = nrtagid
-                 AND UPPER (pkg.pkg_name) LIKE UPPER (skeyword)
+				 AND UPPER (pkg.pkg_name) LIKE UPPER (skeyword)
-            ORDER BY UPPER (pkg.pkg_name);
+			ORDER BY UPPER (pkg.pkg_name);
-      ELSIF nsearcharea = 1
+	  ELSIF nsearcharea = 1
-      THEN
+	  THEN
-         /* Search Pending */
+		 /* Search Pending */
-         OPEN recordset FOR
+		 OPEN recordset FOR
-            SELECT   nsearcharea AS env_area, vi.view_name, pv.dlocked,
+			SELECT   nsearcharea AS env_area, vi.view_name, pv.dlocked,
-                     pkg.pkg_name, pv.pv_id, pv.pkg_version, pv.comments,
+					 pkg.pkg_name, pv.pv_id, pv.pkg_version, pv.comments,
-                     pv.modified_stamp, usr.full_name, usr.user_email
+					 pv.modified_stamp, usr.full_name, usr.user_email
-                FROM views vi,
+				FROM views vi,
-                     planned rc,
+					 planned rc,
-                     PACKAGES pkg,
+					 PACKAGES pkg,
-                     package_versions pv,
+					 package_versions pv,
-                     users usr
+					 users usr
-               WHERE rc.view_id = vi.view_id
+			   WHERE rc.view_id = vi.view_id
-                 AND rc.pv_id = pv.pv_id
+				 AND rc.pv_id = pv.pv_id
-                 AND pkg.pkg_id = pv.pkg_id
+				 AND pkg.pkg_id = pv.pkg_id
-                 AND pv.modifier_id = usr.user_id
+				 AND pv.modifier_id = usr.user_id
-                 AND rc.rtag_id = nrtagid
+				 AND rc.rtag_id = nrtagid
-                 AND UPPER (pkg.pkg_name) LIKE UPPER (skeyword)
+				 AND UPPER (pkg.pkg_name) LIKE UPPER (skeyword)
-            ORDER BY UPPER (pkg.pkg_name);
+			ORDER BY UPPER (pkg.pkg_name);
-      ELSIF nsearcharea = 2
+	  ELSIF nsearcharea = 2
-      THEN
+	  THEN
-         /* Search Released */
+		 /* Search Released */
-         OPEN recordset FOR
+		 OPEN recordset FOR
-            SELECT   nsearcharea AS env_area, vi.view_name, pv.dlocked,
+			SELECT   nsearcharea AS env_area, vi.view_name, pv.dlocked,
-                     pkg.pkg_name, pv.pv_id, pv.pkg_version, pv.comments,
+					 pkg.pkg_name, pv.pv_id, pv.pkg_version, pv.comments,
-                     pv.modified_stamp, usr.full_name, usr.user_email
+					 pv.modified_stamp, usr.full_name, usr.user_email
-                FROM views vi,
+				FROM views vi,
-                     release_content rc,
+					 release_content rc,
-                     PACKAGES pkg,
+					 PACKAGES pkg,
-                     package_versions pv,
+					 package_versions pv,
-                     users usr
+					 users usr
-               WHERE rc.base_view_id = vi.view_id
+			   WHERE rc.base_view_id = vi.view_id
-                 AND rc.pv_id = pv.pv_id
+				 AND rc.pv_id = pv.pv_id
-                 AND pkg.pkg_id = pv.pkg_id
+				 AND pkg.pkg_id = pv.pkg_id
-                 AND pv.modifier_id = usr.user_id
+				 AND pv.modifier_id = usr.user_id
-                 AND rc.rtag_id = nrtagid
+				 AND rc.rtag_id = nrtagid
-                 AND UPPER (pkg.pkg_name) LIKE UPPER (skeyword)
+				 AND UPPER (pkg.pkg_name) LIKE UPPER (skeyword)
-            ORDER BY UPPER (pkg.pkg_name);
+			ORDER BY UPPER (pkg.pkg_name);
-      ELSIF nsearcharea = 3
+	  ELSIF nsearcharea = 3
-      THEN
+	  THEN
-         /* Search ALL */
+		 /* Search ALL */
-         OPEN recordset FOR
+		 OPEN recordset FOR
-            SELECT   rc.env_area, vi.view_name, pv.dlocked, pkg.pkg_name,
+			SELECT   rc.env_area, vi.view_name, pv.dlocked, pkg.pkg_name,
-                     pv.pv_id, pv.pkg_version, pv.comments,
+					 pv.pv_id, pv.pkg_version, pv.comments,
-                     pv.modified_stamp, usr.full_name, usr.user_email
+					 pv.modified_stamp, usr.full_name, usr.user_email
-                FROM views vi,
+				FROM views vi,
-                     environment_view rc,
+					 environment_view rc,
-                     PACKAGES pkg,
+					 PACKAGES pkg,
-                     package_versions pv,
+					 package_versions pv,
-                     users usr
+					 users usr
-               WHERE rc.view_id = vi.view_id
+			   WHERE rc.view_id = vi.view_id
-                 AND rc.pv_id = pv.pv_id
+				 AND rc.pv_id = pv.pv_id
-                 AND pkg.pkg_id = pv.pkg_id
+				 AND pkg.pkg_id = pv.pkg_id
-                 AND pv.modifier_id = usr.user_id
+				 AND pv.modifier_id = usr.user_id
-                 AND rc.rtag_id = nrtagid
+				 AND rc.rtag_id = nrtagid
-                 AND UPPER (pkg.pkg_name) LIKE UPPER (skeyword)
+				 AND UPPER (pkg.pkg_name) LIKE UPPER (skeyword)
-            ORDER BY UPPER (pkg.pkg_name);
+			ORDER BY UPPER (pkg.pkg_name);
-      END IF;
+	  END IF;
    END;
 /*-------------------------------------------------------------------------------------------------------*/
    PROCEDURE find_file (
-      skeyword      IN       VARCHAR2,
+	  skeyword	  IN	   VARCHAR2,
-      nrtagid       IN       NUMBER,
+	  nrtagid	   IN	   NUMBER,
-      nsearcharea   IN       NUMBER,
+	  nsearcharea   IN	   NUMBER,
-      npagesize     IN       NUMBER,
+	  npagesize	 IN	   NUMBER,
-      recordset     OUT      typecur
+	  recordset	 OUT	  typecur
+   )
    IS
    BEGIN
-      IF nsearcharea = 0
+	  IF nsearcharea = 0
-      THEN
+	  THEN
-         /* Search Work In Progress */
+		 /* Search Work In Progress */
-         OPEN recordset FOR
+		 OPEN recordset FOR
-            SELECT   qry.*
+			SELECT   qry.*
-                FROM (
+				FROM (
-                      /* File search on Packages */
+					  /* File search on Packages */
-                      SELECT art.file_name, art.file_path, pkg.pkg_name,
+					  SELECT art.file_name, art.file_path, pkg.pkg_name,
-                             pv.pv_id, pv.pkg_version, art.crc_cksum
+							 pv.pv_id, pv.pkg_version, art.crc_cksum
-                        FROM work_in_progress rc,
+						FROM work_in_progress rc,
-                             PACKAGES pkg,
+							 PACKAGES pkg,
-                             package_versions pv,
+							 package_versions pv,
-                             release_components art
+							 release_components art
-                       WHERE rc.pv_id = art.pv_id
+					   WHERE rc.pv_id = art.pv_id
-                         AND pv.pkg_id = pkg.pkg_id
+						 AND pv.pkg_id = pkg.pkg_id
-                         AND rc.pv_id = pv.pv_id
+						 AND rc.pv_id = pv.pv_id
-                         AND rc.rtag_id = nrtagid
+						 AND rc.rtag_id = nrtagid
-                         AND UPPER (art.file_name) LIKE UPPER (skeyword)
+						 AND UPPER (art.file_name) LIKE UPPER (skeyword)
-                      UNION ALL
+					  UNION ALL
-                      /* File search on Products */
+					  /* File search on Products */
-                      SELECT art.file_name, art.file_path, pkg.pkg_name,
+					  SELECT art.file_name, art.file_path, pkg.pkg_name,
-                             pv.pv_id, pv.pkg_version, art.crc_cksum
+							 pv.pv_id, pv.pkg_version, art.crc_cksum
-                        FROM work_in_progress rc,
+						FROM work_in_progress rc,
-                             PACKAGES pkg,
+							 PACKAGES pkg,
-                             package_versions pv,
+							 package_versions pv,
-                             product_components art
+							 product_components art
-                       WHERE rc.pv_id = art.pv_id
+					   WHERE rc.pv_id = art.pv_id
-                         AND pv.pkg_id = pkg.pkg_id
+						 AND pv.pkg_id = pkg.pkg_id
-                         AND rc.pv_id = pv.pv_id
+						 AND rc.pv_id = pv.pv_id
-                         AND rc.rtag_id = nrtagid
+						 AND rc.rtag_id = nrtagid
-                         AND UPPER (art.file_name) LIKE UPPER (skeyword)) qry
+						 AND UPPER (art.file_name) LIKE UPPER (skeyword)) qry
-               WHERE ROWNUM <= npagesize
+			   WHERE ROWNUM <= npagesize
-            ORDER BY UPPER (qry.pkg_name);
+			ORDER BY UPPER (qry.pkg_name);
-      ELSIF nsearcharea = 1
+	  ELSIF nsearcharea = 1
-      THEN
+	  THEN
-         /* Search Pending */
+		 /* Search Pending */
-         OPEN recordset FOR
+		 OPEN recordset FOR
-            SELECT   qry.*
+			SELECT   qry.*
-                FROM (
+				FROM (
-                      /* File search on Packages */
+					  /* File search on Packages */
-                      SELECT art.file_name, art.file_path, pkg.pkg_name,
+					  SELECT art.file_name, art.file_path, pkg.pkg_name,
-                             pv.pv_id, pv.pkg_version, art.crc_cksum
+							 pv.pv_id, pv.pkg_version, art.crc_cksum
-                        FROM planned rc,
+						FROM planned rc,
-                             PACKAGES pkg,
+							 PACKAGES pkg,
-                             package_versions pv,
+							 package_versions pv,
-                             release_components art
+							 release_components art
-                       WHERE rc.pv_id = art.pv_id
+					   WHERE rc.pv_id = art.pv_id
-                         AND pv.pkg_id = pkg.pkg_id
+						 AND pv.pkg_id = pkg.pkg_id
-                         AND rc.pv_id = pv.pv_id
+						 AND rc.pv_id = pv.pv_id
-                         AND rc.rtag_id = nrtagid
+						 AND rc.rtag_id = nrtagid
-                         AND UPPER (art.file_name) LIKE UPPER (skeyword)
+						 AND UPPER (art.file_name) LIKE UPPER (skeyword)
-                      UNION ALL
+					  UNION ALL
-                      /* File search on Products */
+					  /* File search on Products */
-                      SELECT art.file_name, art.file_path, pkg.pkg_name,
+					  SELECT art.file_name, art.file_path, pkg.pkg_name,
-                             pv.pv_id, pv.pkg_version, art.crc_cksum
+							 pv.pv_id, pv.pkg_version, art.crc_cksum
-                        FROM planned rc,
+						FROM planned rc,
-                             PACKAGES pkg,
+							 PACKAGES pkg,
-                             package_versions pv,
+							 package_versions pv,
-                             product_components art
+							 product_components art
-                       WHERE rc.pv_id = art.pv_id
+					   WHERE rc.pv_id = art.pv_id
-                         AND pv.pkg_id = pkg.pkg_id
+						 AND pv.pkg_id = pkg.pkg_id
-                         AND rc.pv_id = pv.pv_id
+						 AND rc.pv_id = pv.pv_id
-                         AND rc.rtag_id = nrtagid
+						 AND rc.rtag_id = nrtagid
-                         AND UPPER (art.file_name) LIKE UPPER (skeyword)) qry
+						 AND UPPER (art.file_name) LIKE UPPER (skeyword)) qry
-               WHERE ROWNUM <= npagesize
+			   WHERE ROWNUM <= npagesize
-            ORDER BY UPPER (qry.pkg_name);
+			ORDER BY UPPER (qry.pkg_name);
-      ELSIF nsearcharea = 2
+	  ELSIF nsearcharea = 2
-      THEN
+	  THEN
-         /* Search Released */
+		 /* Search Released */
-         OPEN recordset FOR
+		 OPEN recordset FOR
-            SELECT   qry.*
+			SELECT   qry.*
-                FROM (
+				FROM (
-                      /* File search on Packages */
+					  /* File search on Packages */
-                      SELECT art.file_name, art.file_path, pkg.pkg_name,
+					  SELECT art.file_name, art.file_path, pkg.pkg_name,
-                             pv.pv_id, pv.pkg_version, art.crc_cksum,
+							 pv.pv_id, pv.pkg_version, art.crc_cksum,
-                             NULL AS patch_id
+							 NULL AS patch_id
-                        FROM release_content rc,
+						FROM release_content rc,
-                             PACKAGES pkg,
+							 PACKAGES pkg,
-                             package_versions pv,
+							 package_versions pv,
-                             release_components art
+							 release_components art
-                       WHERE rc.pv_id = art.pv_id
+					   WHERE rc.pv_id = art.pv_id
-                         AND pv.pkg_id = pkg.pkg_id
+						 AND pv.pkg_id = pkg.pkg_id
-                         AND rc.pv_id = pv.pv_id
+						 AND rc.pv_id = pv.pv_id
-                         AND rc.rtag_id = nrtagid
+						 AND rc.rtag_id = nrtagid
-                         AND UPPER (art.file_name) LIKE UPPER (skeyword)
+						 AND UPPER (art.file_name) LIKE UPPER (skeyword)
-                      UNION ALL
+					  UNION ALL
-                      /* File search on Products */
+					  /* File search on Products */
-                      SELECT art.file_name, art.file_path, pkg.pkg_name,
+					  SELECT art.file_name, art.file_path, pkg.pkg_name,
-                             pv.pv_id, pv.pkg_version, art.crc_cksum,
+							 pv.pv_id, pv.pkg_version, art.crc_cksum,
-                             NULL AS patch_id
+							 NULL AS patch_id
-                        FROM release_content rc,
+						FROM release_content rc,
-                             PACKAGES pkg,
+							 PACKAGES pkg,
-                             package_versions pv,
+							 package_versions pv,
-                             product_components art
+							 product_components art
-                       WHERE rc.pv_id = art.pv_id
+					   WHERE rc.pv_id = art.pv_id
-                         AND pv.pkg_id = pkg.pkg_id
+						 AND pv.pkg_id = pkg.pkg_id
-                         AND rc.pv_id = pv.pv_id
+						 AND rc.pv_id = pv.pv_id
-                         AND rc.rtag_id = nrtagid
+						 AND rc.rtag_id = nrtagid
-                         AND UPPER (art.file_name) LIKE UPPER (skeyword)
+						 AND UPPER (art.file_name) LIKE UPPER (skeyword)
-                      UNION ALL
+					  UNION ALL
-                      /* File search on Patches */
+					  /* File search on Patches */
-                      SELECT art.file_name, art.file_path, pkg.pkg_name,
+					  SELECT art.file_name, art.file_path, pkg.pkg_name,
-                             pv.pv_id, pv.pkg_version, art.crc_cksum,
+							 pv.pv_id, pv.pkg_version, art.crc_cksum,
-                             DECODE (art.file_path,
+							 DECODE (art.file_path,
-                                     NULL, pp.patch_id,
+									 NULL, pp.patch_id,
-                                     NULL
+									 NULL
-                                    ) AS patch_id
+									) AS patch_id
-                        FROM release_content rc,
+						FROM release_content rc,
-                             PACKAGES pkg,
+							 PACKAGES pkg,
-                             package_versions pv,
+							 package_versions pv,
-                             release_components art,
+							 release_components art,
-                             package_patches pp
+							 package_patches pp
-                       WHERE pv.pv_id = pp.pv_id
+					   WHERE pv.pv_id = pp.pv_id
-                         AND pv.pkg_id = pkg.pkg_id
+						 AND pv.pkg_id = pkg.pkg_id
-                         AND rc.rtag_id = nrtagid
+						 AND rc.rtag_id = nrtagid
-                         AND art.pv_id = pp.patch_id
+						 AND art.pv_id = pp.patch_id
-                         AND rc.pv_id = pp.pv_id
+						 AND rc.pv_id = pp.pv_id
-                         AND UPPER (art.file_name) LIKE UPPER (skeyword)) qry
+						 AND UPPER (art.file_name) LIKE UPPER (skeyword)) qry
-               WHERE ROWNUM <= npagesize
+			   WHERE ROWNUM <= npagesize
-            ORDER BY UPPER (qry.pkg_name);
+			ORDER BY UPPER (qry.pkg_name);
-      ELSIF nsearcharea = 3
+	  ELSIF nsearcharea = 3
-      THEN
+	  THEN
-         /* Search ALL */
+		 /* Search ALL */
-         OPEN recordset FOR
+		 OPEN recordset FOR
-            SELECT   qry.*
+			SELECT   qry.*
-                FROM (
+				FROM (
-                      /* File search on Packages */
+					  /* File search on Packages */
-                      SELECT art.file_name, art.file_path, pkg.pkg_name,
+					  SELECT art.file_name, art.file_path, pkg.pkg_name,
-                             pv.pv_id, pv.pkg_version, art.crc_cksum,
+							 pv.pv_id, pv.pkg_version, art.crc_cksum,
-                             NULL AS patch_id
+							 NULL AS patch_id
-                        FROM environment_view rc,
+						FROM environment_view rc,
-                             PACKAGES pkg,
+							 PACKAGES pkg,
-                             package_versions pv,
+							 package_versions pv,
-                             release_components art
+							 release_components art
-                       WHERE rc.pv_id = art.pv_id
+					   WHERE rc.pv_id = art.pv_id
-                         AND pv.pkg_id = pkg.pkg_id
+						 AND pv.pkg_id = pkg.pkg_id
-                         AND rc.pv_id = pv.pv_id
+						 AND rc.pv_id = pv.pv_id
-                         AND rc.rtag_id = nrtagid
+						 AND rc.rtag_id = nrtagid
-                         AND UPPER (art.file_name) LIKE UPPER (skeyword)
+						 AND UPPER (art.file_name) LIKE UPPER (skeyword)
-                      UNION ALL
+					  UNION ALL
-                      /* File search on Products */
+					  /* File search on Products */
-                      SELECT art.file_name, art.file_path, pkg.pkg_name,
+					  SELECT art.file_name, art.file_path, pkg.pkg_name,
-                             pv.pv_id, pv.pkg_version, art.crc_cksum,
+							 pv.pv_id, pv.pkg_version, art.crc_cksum,
-                             NULL AS patch_id
+							 NULL AS patch_id
-                        FROM environment_view rc,
+						FROM environment_view rc,
-                             PACKAGES pkg,
+							 PACKAGES pkg,
-                             package_versions pv,
+							 package_versions pv,
-                             product_components art
+							 product_components art
-                       WHERE rc.pv_id = art.pv_id
+					   WHERE rc.pv_id = art.pv_id
-                         AND pv.pkg_id = pkg.pkg_id
+						 AND pv.pkg_id = pkg.pkg_id
-                         AND rc.pv_id = pv.pv_id
+						 AND rc.pv_id = pv.pv_id
-                         AND rc.rtag_id = nrtagid
+						 AND rc.rtag_id = nrtagid
-                         AND UPPER (art.file_name) LIKE UPPER (skeyword)
+						 AND UPPER (art.file_name) LIKE UPPER (skeyword)
-                      UNION ALL
+					  UNION ALL
-                      /* File search on Patches */
+					  /* File search on Patches */
-                      SELECT art.file_name, art.file_path, pkg.pkg_name,
+					  SELECT art.file_name, art.file_path, pkg.pkg_name,
-                             pv.pv_id, pv.pkg_version, art.crc_cksum,
+							 pv.pv_id, pv.pkg_version, art.crc_cksum,
-                             DECODE (art.file_path,
+							 DECODE (art.file_path,
-                                     NULL, pp.patch_id,
+									 NULL, pp.patch_id,
-                                     NULL
+									 NULL
-                                    ) AS patch_id
+									) AS patch_id
-                        FROM release_content rc,
+						FROM release_content rc,
-                             PACKAGES pkg,
+							 PACKAGES pkg,
-                             package_versions pv,
+							 package_versions pv,
-                             release_components art,
+							 release_components art,
-                             package_patches pp
+							 package_patches pp
-                       WHERE pv.pv_id = pp.pv_id
+					   WHERE pv.pv_id = pp.pv_id
-                         AND pv.pkg_id = pkg.pkg_id
+						 AND pv.pkg_id = pkg.pkg_id
-                         AND rc.rtag_id = nrtagid
+						 AND rc.rtag_id = nrtagid
-                         AND art.pv_id = pp.patch_id
+						 AND art.pv_id = pp.patch_id
-                         AND rc.pv_id = pp.pv_id
+						 AND rc.pv_id = pp.pv_id
-                         AND UPPER (art.file_name) LIKE UPPER (skeyword)) qry
+						 AND UPPER (art.file_name) LIKE UPPER (skeyword)) qry
-               WHERE ROWNUM <= npagesize
+			   WHERE ROWNUM <= npagesize
-            ORDER BY UPPER (qry.pkg_name);
+			ORDER BY UPPER (qry.pkg_name);
-      END IF;
+	  END IF;
    END;
 /*-------------------------------------------------------------------------------------------------------*/
    PROCEDURE get_prodrelease_items (
-      rtagid             IN       NUMBER,
+	  rtagid			 IN	   NUMBER,
-      ntruerecordcount   OUT      NUMBER,
+	  ntruerecordcount   OUT	  NUMBER,
-      recordset          OUT      typecur
+	  recordset		  OUT	  typecur
+   )
    IS
    BEGIN
-      -- Get true record count n the number of integration products
+	  -- Get true record count n the number of integration products
-      SELECT COUNT (rc.pv_id)
+	  SELECT COUNT (rc.pv_id)
-        INTO ntruerecordcount
+		INTO ntruerecordcount
-        FROM release_content rc
+		FROM release_content rc
-       WHERE rc.rtag_id = rtagid;
+	   WHERE rc.rtag_id = rtagid;
-      OPEN recordset FOR
+	  OPEN recordset FOR
-         SELECT   vi.view_id, vi.view_name, rel.pkg_state,    --rel.pkg_state,
+		 SELECT   vi.view_id, vi.view_name, rel.pkg_state,	--rel.pkg_state,
-                  rel.deprecated_state, pv.pv_id, pkg.pkg_name,
+				  rel.deprecated_state, pv.pv_id, pkg.pkg_name,
-                  pv.pkg_version, pv.dlocked, pv.pv_description
+				  pv.pkg_version, pv.dlocked, pv.pv_description
-             FROM release_content rel,
+			 FROM release_content rel,
-                  PACKAGES pkg,
+				  PACKAGES pkg,
-                  package_versions pv,
+				  package_versions pv,
-                  views vi
+				  views vi
-            WHERE pv.pkg_id = pkg.pkg_id
+			WHERE pv.pkg_id = pkg.pkg_id
-              AND rel.pv_id = pv.pv_id
+			  AND rel.pv_id = pv.pv_id
-              AND rel.base_view_id = vi.view_id
+			  AND rel.base_view_id = vi.view_id
-              AND pv.is_deployable = 'Y'
+			  AND pv.is_deployable = 'Y'
-              AND rtag_id = rtagid
+			  AND rtag_id = rtagid
-              AND pv.pv_id NOT IN (SELECT DISTINCT prod_id
+			  AND pv.pv_id NOT IN (SELECT DISTINCT prod_id
-                                              FROM deployment_manager.os_contents)
+											  FROM deployment_manager.os_contents)
-              AND rel.product_state IS NULL
+			  AND rel.product_state IS NULL
-         ORDER BY UPPER (vi.view_name), UPPER (pkg.pkg_name);
+		 ORDER BY UPPER (vi.view_name), UPPER (pkg.pkg_name);
    END;
 /*-------------------------------------------------------------------------------------------------------*/
    PROCEDURE get_integration_items (
-      rtagid             IN       NUMBER,
+	  rtagid			 IN	   NUMBER,
-      ntruerecordcount   OUT      NUMBER,
+	  ntruerecordcount   OUT	  NUMBER,
-      recordset          OUT      typecur
+	  recordset		  OUT	  typecur
+   )
    IS
    BEGIN
-      -- Get true record count n the number of integration products
+	  -- Get true record count n the number of integration products
-      SELECT COUNT (rc.pv_id)
+	  SELECT COUNT (rc.pv_id)
-        INTO ntruerecordcount
+		INTO ntruerecordcount
-        FROM release_content rc
+		FROM release_content rc
-       WHERE rc.rtag_id = rtagid AND rc.product_state = 1;
+	   WHERE rc.rtag_id = rtagid AND rc.product_state = 1;
-      OPEN recordset FOR
+	  OPEN recordset FOR
-         SELECT   vi.view_id, vi.view_name, rel.pkg_state,
+		 SELECT   vi.view_id, vi.view_name, rel.pkg_state,
-                  rel.deprecated_state, pv.pv_id, pkg.pkg_name,
+				  rel.deprecated_state, pv.pv_id, pkg.pkg_name,
-                  pv.pkg_version, pv.dlocked, pv.pv_description
+				  pv.pkg_version, pv.dlocked, pv.pv_description
-             FROM release_content rel,
+			 FROM release_content rel,
-                  PACKAGES pkg,
+				  PACKAGES pkg,
-                  package_versions pv,
+				  package_versions pv,
-                  views vi
+				  views vi
-            WHERE pv.pkg_id = pkg.pkg_id
+			WHERE pv.pkg_id = pkg.pkg_id
-              AND rel.pv_id = pv.pv_id
+			  AND rel.pv_id = pv.pv_id
-              AND rel.base_view_id = vi.view_id
+			  AND rel.base_view_id = vi.view_id
-              AND pv.is_deployable = 'Y'
+			  AND pv.is_deployable = 'Y'
-              AND rtag_id = rtagid
+			  AND rtag_id = rtagid
-              AND rel.product_state IN (1, 5)
+			  AND rel.product_state IN (1, 5)
-         ORDER BY UPPER (vi.view_name), UPPER (pkg.pkg_name);
+		 ORDER BY UPPER (vi.view_name), UPPER (pkg.pkg_name);
    END;
 /*-------------------------------------------------------------------------------------------------------*/
    PROCEDURE get_test_items (
-      rtagid             IN       NUMBER,
+	  rtagid			 IN	   NUMBER,
-      ntruerecordcount   OUT      NUMBER,
+	  ntruerecordcount   OUT	  NUMBER,
-      recordset          OUT      typecur
+	  recordset		  OUT	  typecur
+   )
    IS
    BEGIN
-      -- Get true record count n the number of test products
+	  -- Get true record count n the number of test products
-      SELECT COUNT (rc.pv_id)
+	  SELECT COUNT (rc.pv_id)
-        INTO ntruerecordcount
+		INTO ntruerecordcount
-        FROM release_content rc
+		FROM release_content rc
-       WHERE rc.rtag_id = rtagid AND rc.product_state = 2;
+	   WHERE rc.rtag_id = rtagid AND rc.product_state = 2;
-      OPEN recordset FOR
+	  OPEN recordset FOR
-         SELECT   vi.view_id, vi.view_name, rel.pkg_state,
+		 SELECT   vi.view_id, vi.view_name, rel.pkg_state,
-                  rel.deprecated_state, pv.pv_id, pkg.pkg_name,
+				  rel.deprecated_state, pv.pv_id, pkg.pkg_name,
-                  pv.pkg_version, pv.dlocked, pv.pv_description
+				  pv.pkg_version, pv.dlocked, pv.pv_description
-             FROM release_content rel,
+			 FROM release_content rel,
-                  PACKAGES pkg,
+				  PACKAGES pkg,
-                  package_versions pv,
+				  package_versions pv,
-                  views vi
+				  views vi
-            WHERE pv.pkg_id = pkg.pkg_id
+			WHERE pv.pkg_id = pkg.pkg_id
-              AND rel.pv_id = pv.pv_id
+			  AND rel.pv_id = pv.pv_id
-              AND rel.base_view_id = vi.view_id
+			  AND rel.base_view_id = vi.view_id
-              AND pv.is_deployable = 'Y'
+			  AND pv.is_deployable = 'Y'
-              AND rtag_id = rtagid
+			  AND rtag_id = rtagid
-              AND rel.product_state = 2
+			  AND rel.product_state = 2
-         ORDER BY UPPER (vi.view_name), UPPER (pkg.pkg_name);
+		 ORDER BY UPPER (vi.view_name), UPPER (pkg.pkg_name);
    END;
 /*-------------------------------------------------------------------------------------------------------*/
    PROCEDURE get_deploy_items (
-      rtagid             IN       NUMBER,
+	  rtagid			 IN	   NUMBER,
-      ntruerecordcount   OUT      NUMBER,
+	  ntruerecordcount   OUT	  NUMBER,
-      recordset          OUT      typecur
+	  recordset		  OUT	  typecur
+   )
    IS
    BEGIN
-      -- Get true record count n the number of deploy products
+	  -- Get true record count n the number of deploy products
-      SELECT COUNT (rc.pv_id)
+	  SELECT COUNT (rc.pv_id)
-        INTO ntruerecordcount
+		INTO ntruerecordcount
-        FROM release_content rc
+		FROM release_content rc
-       WHERE rc.rtag_id = rtagid AND rc.product_state = 3;
+	   WHERE rc.rtag_id = rtagid AND rc.product_state = 3;
-      OPEN recordset FOR
+	  OPEN recordset FOR
-         SELECT   vi.view_id, vi.view_name, rel.pkg_state,
+		 SELECT   vi.view_id, vi.view_name, rel.pkg_state,
-                  rel.deprecated_state, pv.pv_id, pkg.pkg_name,
+				  rel.deprecated_state, pv.pv_id, pkg.pkg_name,
-                  pv.pkg_version, pv.dlocked, pv.pv_description
+				  pv.pkg_version, pv.dlocked, pv.pv_description
-             FROM release_content rel,
+			 FROM release_content rel,
-                  PACKAGES pkg,
+				  PACKAGES pkg,
-                  package_versions pv,
+				  package_versions pv,
-                  views vi
+				  views vi
-            WHERE pv.pkg_id = pkg.pkg_id
+			WHERE pv.pkg_id = pkg.pkg_id
-              AND rel.pv_id = pv.pv_id
+			  AND rel.pv_id = pv.pv_id
-              AND rel.base_view_id = vi.view_id
+			  AND rel.base_view_id = vi.view_id
-              AND pv.is_deployable = 'Y'
+			  AND pv.is_deployable = 'Y'
-              AND rtag_id = rtagid
+			  AND rtag_id = rtagid
-              AND rel.product_state IN (3, 5)
+			  AND rel.product_state IN (3, 5)
-              AND pv.pv_id NOT IN (SELECT DISTINCT prod_id
+			  AND pv.pv_id NOT IN (SELECT DISTINCT prod_id
-                                              FROM deployment_manager.os_contents)
+											  FROM deployment_manager.os_contents)
-         ORDER BY UPPER (vi.view_name), UPPER (pkg.pkg_name);
+		 ORDER BY UPPER (vi.view_name), UPPER (pkg.pkg_name);
    END;
 /*-------------------------------------------------------------------------------------------------------*/
    PROCEDURE get_reject_items (
-      rtagid             IN       NUMBER,
+	  rtagid			 IN	   NUMBER,
-      ntruerecordcount   OUT      NUMBER,
+	  ntruerecordcount   OUT	  NUMBER,
-      recordset          OUT      typecur
+	  recordset		  OUT	  typecur
+   )
    IS
    BEGIN
-      -- Get true record count n the number of reject products
+	  -- Get true record count n the number of reject products
-      SELECT COUNT (rc.pv_id)
+	  SELECT COUNT (rc.pv_id)
-        INTO ntruerecordcount
+		INTO ntruerecordcount
-        FROM release_content rc
+		FROM release_content rc
-       WHERE rc.rtag_id = rtagid AND rc.product_state = 4;
+	   WHERE rc.rtag_id = rtagid AND rc.product_state = 4;
-      OPEN recordset FOR
+	  OPEN recordset FOR
-         SELECT   vi.view_id, vi.view_name, rel.pkg_state,
+		 SELECT   vi.view_id, vi.view_name, rel.pkg_state,
-                  rel.deprecated_state, pv.pv_id, pkg.pkg_name,
+				  rel.deprecated_state, pv.pv_id, pkg.pkg_name,
-                  pv.pkg_version, pv.dlocked, pv.pv_description
+				  pv.pkg_version, pv.dlocked, pv.pv_description
-             FROM release_content rel,
+			 FROM release_content rel,
-                  PACKAGES pkg,
+				  PACKAGES pkg,
-                  package_versions pv,
+				  package_versions pv,
-                  views vi
+				  views vi
-            WHERE pv.pkg_id = pkg.pkg_id
+			WHERE pv.pkg_id = pkg.pkg_id
-              AND rel.pv_id = pv.pv_id
+			  AND rel.pv_id = pv.pv_id
-              AND rel.base_view_id = vi.view_id
+			  AND rel.base_view_id = vi.view_id
-              AND pv.is_deployable = 'Y'
+			  AND pv.is_deployable = 'Y'
-              AND rtag_id = rtagid
+			  AND rtag_id = rtagid
-              AND rel.product_state = 4
+			  AND rel.product_state = 4
-         ORDER BY UPPER (vi.view_name), UPPER (pkg.pkg_name);
+		 ORDER BY UPPER (vi.view_name), UPPER (pkg.pkg_name);
    END;
 /*-------------------------------------------------------------------------------------------------------*/
+   -- DEVI-45275 - this procedure performs the merge of an existing package version in the planned table
+   -- to the release_content table.
+  PROCEDURE approve_merge (
+	PvId	IN NUMBER,
+	RtagId  IN NUMBER,
+	UserId  IN NUMBER
+  )
+  IS
+	viewid		  NUMBER;
+	envtab		  NUMBER;
+	oper			CHAR;
+  BEGIN
+	   -- Get ViewId
+	  viewid := get_package_view (pvid, rtagid);
+	  -- Get the view tab
+	  envtab := pk_environment.get_package_area (pvid, rtagid);
+	  -- Make sure that package was in pending before merging it to released
+	  IF (envtab <> 1)
+	  THEN
+		 -- Not found in pending
+		 raise_application_error (-20000,
+								  'This package cannot be released here.'
+								 );
+	  END IF;
+	  -- Log
+	  log_action (pvid, 'action', userid, 'Start of Package Merge...');
+	  -- Get the planned operation type A=Add, S=Subtract
+	  SELECT ev.operation INTO oper
+	  FROM environment_view ev
+	  WHERE ev.rtag_id = RtagId
+	  AND ev.pv_id = PvId
+	  AND (ev.operation = 'A' OR ev.operation = 'S');
+	  -- Remove from Pending
+	  pk_planned.remove_package (PvId, RtagId, UserId);
+	  -- Either add to the release, or remove from the release as the operation commands
+	  IF oper = 'A'
+	  THEN
+		pk_release.add_package(PvId, viewId, RtagId, UserId);
+	  ELSIF oper = 'S'
+	  THEN
+		  pk_release.remove_package(PvId, RtagId, UserId);
+	  END IF;
+	  touch_release(RtagId);
+	  -- Log
+	  log_action (pvid, 'action', userid, 'End of Package Merge...');
+  END;
+/*-------------------------------------------------------------------------------------------------------*/
 END pk_environment;
+/
 CREATE OR REPLACE PACKAGE BODY PK_WORK_IN_PROGRESS IS
 	END;
 END Rm_Issues;
+/
-CREATE OR REPLACE PACKAGE BODY PK_PLANNED IS
+CREATE OR REPLACE PACKAGE BODY                   "PK_PLANNED" IS
 /*
 ------------------------------
-||  Last Modified:  S.Vukovic
+||  Last Modified:  G.Huddy
-||  Modified Date:  6/May/2005
+||  Modified Date:  28/May/2008
-||  Body Version:   1.0
+||  Body Version:   1.1
 ------------------------------
 */
 /*-------------------------------------------------------------------------------------------------------*/
 	oldPvId NUMBER;
 	ReleaseLocation VARCHAR2(4000);
 	IsPatch CHAR(1) := NULL;
 	sLocation VARCHAR2(4000) := NULL;
 	nRtagIdLocation NUMBER;
 BEGIN
 	/*--------------- Business Rules Here -------------------*/
 	/*-------------------------------------------------------*/
 	BEGIN
 		-- Check if Exists in "Work in progress" anywhere in the world, except "Closed mode" releases
 		SELECT proj.PROJ_NAME ||' > '|| rt.RTAG_NAME, rt.RTAG_ID  INTO sLocation, nRtagIdLocation
 		  FROM PLANNED pl,
-		  	   RELEASE_TAGS rt,
+			   RELEASE_TAGS rt,
 			   PROJECTS proj
 		 WHERE pl.PV_ID = newPvId
 		   AND pl.RTAG_ID = rt.RTAG_ID
 		   AND rt.OFFICIAL != 'Y'
 		   AND rt.PROJ_ID = proj.PROJ_ID;
 		EXCEPTION
-	    	WHEN NO_DATA_FOUND THEN
+			WHEN NO_DATA_FOUND THEN
-	       		sLocation := NULL;
+				sLocation := NULL;
 	END;
 	IF (sLocation IS NULL) OR (nRtagIdLocation = RtagId) THEN
 		-- Add to "Pending" area
-		INSERT INTO PLANNED ( RTAG_ID, PV_ID, VIEW_ID )
+		INSERT INTO PLANNED ( RTAG_ID, PV_ID, VIEW_ID, OPERATION )
-		VALUES( RtagId, newPvId, ViewId );
+		VALUES( RtagId, newPvId, ViewId, 'R' );
-	    /* LOG ACTION */
+		/* LOG ACTION */
 		SELECT proj.PROJ_NAME ||' / '|| rt.RTAG_NAME INTO ReleaseLocation
 		  FROM PROJECTS proj,
-		  	   RELEASE_TAGS rt
+			   RELEASE_TAGS rt
 		 WHERE rt.PROJ_ID = proj.PROJ_ID
 		   AND rt.RTAG_ID = RtagId;
 		Log_Action ( newPvId, 'add_to_planned', UserId, 'Location: '|| ReleaseLocation );
 	ELSE
 		RAISE_APPLICATION_ERROR (-20000, 'This version is already in Pending Area at '|| sLocation ||'.' );
 	END IF;
 END;
 /*-------------------------------------------------------------------------------------------------------*/
 	-- Get release location for logging pusposes
 	SELECT proj.PROJ_NAME  ||' / '|| rt.RTAG_NAME INTO ReleaseLocation
 	  FROM PROJECTS proj,
-	  	   RELEASE_TAGS rt
+		   RELEASE_TAGS rt
 	 WHERE rt.PROJ_ID = proj.PROJ_ID
 	   AND rt.RTAG_ID = RtagId;
 	-- Delete from Work In Progress
 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 0 AS PKG_STATE,
 			   NULL AS DEPRECATED_STATE,
 			   pv.pv_id,
 			   pkg.pkg_name,
 			   pv.pkg_version,
 			   pv.dlocked,
 			   pv.pv_description,
-			   pv.BUILD_TYPE
+			   pv.BUILD_TYPE,
+			   rel.operation
 		  FROM PLANNED rel,
-		       packages pkg,
+			   packages pkg,
-		       package_versions pv
+			   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
+		-- Get non base view content
 		OPEN RecordSet FOR
 		SELECT 0 AS PKG_STATE,
 			   NULL AS DEPRECATED_STATE,
 			   pv.pv_id,
 			   pkg.pkg_name,
 			   pv.pkg_version,
 			   pv.dlocked,
 			   pv.pv_description,
-			   pv.BUILD_TYPE
+			   pv.BUILD_TYPE,
+			   rel.operation
 		  FROM PLANNED rel,
-		       packages pkg,
+			   packages pkg,
-		       package_versions pv,
+			   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;
 /*-------------------------------------------------------------------------------------------------------*/
 PROCEDURE CHANGE_PACKAGE_VIEW ( PvId IN NUMBER, RtagId IN NUMBER, NewViewId IN NUMBER ) IS
 BEGIN
 	UPDATE PLANNED pl SET
 	pl.VIEW_ID = NewViewId
 	WHERE pl.PV_ID = PvId
 	  AND pl.RTAG_ID = RtagId;
+END;
+/*-------------------------------------------------------------------------------------------------------*/
+-- DEVI-45275 This new function supports the ability to insert into the planned table, and existing
+-- version of a package as part of a release-to-release merge operation carried out within release manager.
+PROCEDURE MERGE_PACKAGE ( newPvId IN NUMBER, ViewId IN NUMBER, RtagId IN NUMBER, UserId IN NUMBER, mergeOperation IN CHAR ) IS
+	ReleaseLocation VARCHAR2(4000);
+BEGIN
+	/*--------------- Business Rules Here -------------------*/
+	/*-------------------------------------------------------*/
+	-- Add to "Pending" area
+	INSERT INTO PLANNED ( RTAG_ID, PV_ID, VIEW_ID, OPERATION )
+	VALUES( RtagId, newPvId, ViewId, mergeOperation );
+	/* LOG ACTION */
+	SELECT proj.PROJ_NAME ||' / '|| rt.RTAG_NAME INTO ReleaseLocation
+	  FROM PROJECTS proj,
+		   RELEASE_TAGS rt
+	 WHERE rt.PROJ_ID = proj.PROJ_ID
+	   AND rt.RTAG_ID = RtagId;
+	Log_Action ( newPvId, 'add_to_planned', UserId, 'Location: '|| ReleaseLocation );
 END;
 /*-------------------------------------------------------------------------------------------------------*/
 END PK_PLANNED;
+/

Subversion Repositories DevTools

(root)/ManagerSuite/Manager_Suite_VM/trunk/src/Release Manager.sql – Rev 51 → 53