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

     FREELIST GROUPS 1
     BUFFER_POOL DEFAULT
+  )
+;
+CREATE TABLE LICENCING
+(
+  PV_ID NUMBER NOT NULL,
+  LICENCE NUMBER NOT NULL
+)
+  TABLESPACE "USERS"
+  LOGGING
+  PCTFREE 10
+  PCTUSED 40
+  INITRANS 1
+  MAXTRANS 255
+  STORAGE
+  (
+    INITIAL 64K
+    MINEXTENTS 1
+    MAXEXTENTS 2147483645
+    FREELISTS 1
+    FREELIST GROUPS 1
+    BUFFER_POOL DEFAULT
+  )
+;
 CREATE TABLE DEPRECATED_PACKAGES
+(
   RTAG_ID NUMBER NOT NULL,
   PKG_ID NUMBER NOT NULL,
   COMMENTS VARCHAR2(4000 BYTE) NOT NULL,
     FREELIST GROUPS 1
     BUFFER_POOL DEFAULT
+  )
+;
+CREATE TABLE LICENCES
+(
+  LICENCE NUMBER NOT NULL,
+  NAME VARCHAR2(50 BYTE) NOT NULL
+, CONSTRAINT PK_LICENCES PRIMARY KEY
+  (
+    LICENCE
+  )
+  ENABLE
+)
+  TABLESPACE "USERS"
+  LOGGING
+  PCTFREE 10
+  PCTUSED 40
+  INITRANS 1
+  MAXTRANS 255
+  STORAGE
+  (
+    INITIAL 64K
+    MINEXTENTS 1
+    MAXEXTENTS 2147483645
+    FREELISTS 1
+    FREELIST GROUPS 1
+    BUFFER_POOL DEFAULT
+  )
+;
 CREATE TABLE RELEASE_COMPONENTS
+(
   PV_ID NUMBER NOT NULL,
   FILE_NAME VARCHAR2(255 BYTE),
   FILE_PATH VARCHAR2(2000 BYTE),
+(
 PV_ID
 ) ENABLE
+;
+ALTER TABLE LICENCING
+ADD FOREIGN KEY
+(
+  PV_ID
+)
+REFERENCES PACKAGE_VERSIONS
+(
+PV_ID
+) ENABLE
+;
+ALTER TABLE LICENCING
+ADD FOREIGN KEY
+(
+  LICENCE
+)
+REFERENCES LICENCES
+(
+LICENCE
+) ENABLE
+;
 ALTER TABLE PACKAGE_DOCUMENTS
 ADD CONSTRAINT FK_PKGDOCS_REF_PV FOREIGN KEY
+(
   PV_ID
+)
     NULL AS PKG_STATE,
  NULL AS DEPRECATED_STATE,
     NULL AS INSERTOR_ID,
     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
 EXCEPTION WHEN VALUE_ERROR THEN
    RETURN false;
 END IS_NUMBER;
+/
-CREATE OR REPLACE PROCEDURE Basic_Clone ( nFROMpv_id IN NUMBER,
+CREATE OR REPLACE PROCEDURE                   "BASIC_CLONE" ( nFROMpv_id IN NUMBER,
-										  nTOpv_id IN NUMBER,
+                        nTOpv_id IN NUMBER,
-                                          nRtag_id IN NUMBER,
+                        nRtag_id IN NUMBER,
-                                          nUser_id IN NUMBER,
+                        nUser_id IN NUMBER,
-                                          nTOpkg_id IN NUMBER DEFAULT NULL,
+                        nTOpkg_id IN NUMBER DEFAULT NULL,
-                                          enumISSUES_STATE_IMPORTED IN NUMBER DEFAULT NULL ) IS
+                        enumISSUES_STATE_IMPORTED IN NUMBER DEFAULT NULL ) IS
-/* ---------------------------------------------------------------------------
+   /* ---------------------------------------------------------------------------
-    Version: 3.5
+   ||  Last Modified:  G.Huddy
+   ||  Modified Date:  19/Aug/2008
+   ||  Version      :  3.6 (added licencing cloning)
    --------------------------------------------------------------------------- */
    FromVersion PACKAGE_VERSIONS.PKG_VERSION%TYPE;
 BEGIN
 ------------------------------------- Clone Dependencies ---------------------------------------------------
-	IF NOT nRtag_id IS NULL
+   IF NOT nRtag_id IS NULL
-    THEN
+   THEN
-		-- Auto Update Dependencies --
+      -- Auto Update Dependencies --
-		INSERT INTO PACKAGE_DEPENDENCIES
+      INSERT INTO PACKAGE_DEPENDENCIES
-		    SELECT nTOpv_id AS pv_id,
+           SELECT nTOpv_id AS pv_id,
-		           DECODE(nUser_id,
+                  DECODE(nUser_id,
-		                  frc.modifier_id,
+                         frc.modifier_id,
-		                  frc.pv_id,
+                         frc.pv_id,
-		                  DECODE(frc.dlocked,
+                         DECODE(frc.dlocked,
-		                         'Y',
+                                'Y',
-		                         frc.pv_id,
+                                frc.pv_id,
-		                         dep.dpv_id)
+                                dep.dpv_id)
-		                  ) AS dpv_id,
+                         ) AS dpv_id,
-		           nTOpkg_id AS pkg_id,
+                  nTOpkg_id AS pkg_id,
-		           dep.dpkg_id,
+                  dep.dpkg_id,
-		           dep.build_type,
+                  dep.build_type,
-				   dep.display_order
+                  dep.display_order
-		      FROM PACKAGE_DEPENDENCIES dep,
+             FROM PACKAGE_DEPENDENCIES dep,
-		           PACKAGE_VERSIONS pv,
+                  PACKAGE_VERSIONS pv,
-		           (
+                  (
-		           /* Full Release Contents used for reference*/
+                  /* Full Release Contents used for reference*/
-		           SELECT rpv.pv_id, rpv.pkg_id, NVL(rpv.v_ext, '|LINK_A_NULL|') AS v_ext, rpv.modifier_id, rpv.dlocked
+                  SELECT rpv.pv_id, rpv.pkg_id, NVL(rpv.v_ext, '|LINK_A_NULL|') AS v_ext, rpv.modifier_id, rpv.dlocked
-		           FROM RELEASE_CONTENT rel, PACKAGE_VERSIONS rpv
+                  FROM RELEASE_CONTENT rel, PACKAGE_VERSIONS rpv
-		           WHERE rel.pv_id = rpv.pv_id AND rtag_id = nRtag_id
+                  WHERE rel.pv_id = rpv.pv_id AND rtag_id = nRtag_id
-		           ) frc
+                  ) frc
-		     WHERE dep.pv_id = nFROMpv_id
+            WHERE dep.pv_id = nFROMpv_id
-		       AND dep.dpv_id = pv.pv_id
+              AND dep.dpv_id = pv.pv_id
-		       AND pv.pkg_id = frc.pkg_id(+)
+              AND pv.pkg_id = frc.pkg_id(+)
-		       AND NVL(pv.v_ext, '|LINK_A_NULL|') = frc.v_ext(+);
+              AND NVL(pv.v_ext, '|LINK_A_NULL|') = frc.v_ext(+);
-	ELSE
+   ELSE
-		-- Clone Dependencies --
+      -- Clone Dependencies --
-		INSERT INTO PACKAGE_DEPENDENCIES
+      INSERT INTO PACKAGE_DEPENDENCIES
-		    SELECT nTOpv_id         AS pv_id,
+           SELECT nTOpv_id         AS pv_id,
-		           dep.dpv_id,
+                  dep.dpv_id,
-		           nTOpkg_id        AS pkg_id,
+                  nTOpkg_id        AS pkg_id,
-		           dep.dpkg_id,
+                  dep.dpkg_id,
-		           dep.build_type,
+                  dep.build_type,
-				   dep.display_order
+                  dep.display_order
-		      FROM PACKAGE_DEPENDENCIES dep
+             FROM PACKAGE_DEPENDENCIES dep
-		     WHERE dep.pv_id = nFROMpv_id;
+            WHERE dep.pv_id = nFROMpv_id;
-	END IF;
+   END IF;
 ----------------------------------------- Clone Issues -------------------------------------------------------
-	IF enumISSUES_STATE_IMPORTED IS NULL
+   IF enumISSUES_STATE_IMPORTED IS NULL
-	THEN
+   THEN
         /* All Issues */
-	    INSERT INTO CQ_ISSUES ( pv_id, iss_db, iss_id, iss_state, mod_date, notes )
+       INSERT INTO CQ_ISSUES ( pv_id, iss_db, iss_id, iss_state, mod_date, notes )
-	         SELECT nTOpv_id AS pv_id, iss_db, iss_id, iss_state, mod_date, notes
+            SELECT nTOpv_id AS pv_id, iss_db, iss_id, iss_state, mod_date, notes
-	           FROM CQ_ISSUES
+              FROM CQ_ISSUES
-              WHERE pv_id = nFROMpv_id;
+             WHERE pv_id = nFROMpv_id;
-	ELSE
+   ELSE
-    	/* Outstanding Issues Only */
+       /* Outstanding Issues Only */
-	    INSERT INTO CQ_ISSUES ( pv_id, iss_db, iss_id, iss_state, mod_date, notes )
+       INSERT INTO CQ_ISSUES ( pv_id, iss_db, iss_id, iss_state, mod_date, notes )
-	        SELECT nTOpv_id AS pv_id, iss_db, iss_id, iss_state, mod_date, notes
+            SELECT nTOpv_id AS pv_id, iss_db, iss_id, iss_state, mod_date, notes
-	          FROM CQ_ISSUES
+              FROM CQ_ISSUES
              WHERE pv_id = nFROMpv_id
                AND iss_state = enumISSUES_STATE_IMPORTED;
-	END IF;
+   END IF;
 ------------------------------------ Clone Runtime Dependencies -----------------------------------------------
-	INSERT INTO RUNTIME_DEPENDENCIES (pv_id, rtd_id, rtd_url, rtd_comments, mod_date, mod_user)
+   INSERT INTO RUNTIME_DEPENDENCIES (pv_id, rtd_id, rtd_url, rtd_comments, mod_date, mod_user)
-	    SELECT nTOpv_id AS pv_id, rtd_id, rtd_url, rtd_comments, mod_date, mod_user
+       SELECT nTOpv_id AS pv_id, rtd_id, rtd_url, rtd_comments, mod_date, mod_user
-	    FROM RUNTIME_DEPENDENCIES WHERE pv_id = nFROMpv_id;
+       FROM RUNTIME_DEPENDENCIES WHERE pv_id = nFROMpv_id;
 --------------------------------------- Clone Additional Notes ------------------------------------------------
-	INSERT INTO ADDITIONAL_NOTES ( NOTE_ID, PV_ID, NOTE_TITLE, NOTE_BODY, MOD_DATE, MOD_USER )
+   INSERT INTO ADDITIONAL_NOTES ( NOTE_ID, PV_ID, NOTE_TITLE, NOTE_BODY, MOD_DATE, MOD_USER )
-    	 SELECT an.NOTE_ID,
+        SELECT an.NOTE_ID,
-                nTOpv_id AS PV_ID,
+               nTOpv_id AS PV_ID,
-                an.NOTE_TITLE,
+               an.NOTE_TITLE,
-                an.NOTE_BODY,
+               an.NOTE_BODY,
-                an.MOD_DATE,
+               an.MOD_DATE,
-                an.MOD_USER
+               an.MOD_USER
-           FROM ADDITIONAL_NOTES an
+          FROM ADDITIONAL_NOTES an
-          WHERE an.PV_ID = nFROMpv_id;
+         WHERE an.PV_ID = nFROMpv_id;
 -------------------------------------------- Clone Unit Tests -------------------------------------------------
-	-- Clone only Automatic unit tests --
+   -- Clone only Automatic unit tests --
-	INSERT INTO UNIT_TESTS (
+   INSERT INTO UNIT_TESTS (
-							TEST_ID,
+                           TEST_ID,
-							PV_ID,
+                           PV_ID,
-							TEST_TYPES_FK,
+                           TEST_TYPES_FK,
-							TEST_SUMMARY,
+                           TEST_SUMMARY,
-							COMPLETION_DATE,
+                           COMPLETION_DATE,
-							COMPLETED_BY,
+                           COMPLETED_BY,
-							RESULTS_URL,
+                           RESULTS_URL,
-							RESULTS_ATTACHMENT_NAME,
+                           RESULTS_ATTACHMENT_NAME,
-							NUMOF_TEST
+                           NUMOF_TEST
-							)
+                          )
-    	 SELECT ut.TEST_ID,
+        SELECT ut.TEST_ID,
-                nTOpv_id AS PV_ID,
+               nTOpv_id AS PV_ID,
-                ut.TEST_TYPES_FK,
+               ut.TEST_TYPES_FK,
-                ut.TEST_SUMMARY,
+               ut.TEST_SUMMARY,
-                Ora_Sysdate AS COMPLETION_DATE,
+               Ora_Sysdate AS COMPLETION_DATE,
-                nUser_id AS COMPLETED_BY,
+               nUser_id AS COMPLETED_BY,
-                ut.RESULTS_URL,
+               ut.RESULTS_URL,
-				ut.RESULTS_ATTACHMENT_NAME,
+               ut.RESULTS_ATTACHMENT_NAME,
-				ut.NUMOF_TEST
+               ut.NUMOF_TEST
-           FROM UNIT_TESTS ut
+          FROM UNIT_TESTS ut
-          WHERE ut.PV_ID = nFROMpv_id
+         WHERE ut.PV_ID = nFROMpv_id
-            AND ut.TEST_TYPES_FK IN ( 5, 7 );
+           AND ut.TEST_TYPES_FK IN ( 5, 7 );
-	-- Clone only Interactive Unit Tests --
+    -- Clone only Interactive Unit Tests --
-	INSERT INTO UNIT_TESTS (
+    INSERT INTO UNIT_TESTS (
-		   				    TEST_ID,
+                            TEST_ID,
-							PV_ID,
+                            PV_ID,
-							TEST_TYPES_FK,
+                            TEST_TYPES_FK,
-							TEST_SUMMARY
+                            TEST_SUMMARY
-							)
+                           )
-		SELECT ut.TEST_ID,
+       SELECT ut.TEST_ID,
-			   nTOpv_id AS PV_ID,
+              nTOpv_id AS PV_ID,
-			   ut.TEST_TYPES_FK,
+              ut.TEST_TYPES_FK,
-			   ut.TEST_SUMMARY
+              ut.TEST_SUMMARY
-		  FROM UNIT_TESTS ut
+         FROM UNIT_TESTS ut
-		 WHERE ut.PV_ID = nFROMpv_id
+        WHERE ut.PV_ID = nFROMpv_id
-		   AND ut.TEST_TYPES_FK IN (6);
+          AND ut.TEST_TYPES_FK IN (6);
 -------------------------------------------- Clone Package Documents ------------------------------------------
-	INSERT INTO PACKAGE_DOCUMENTS ( PV_ID, TEST_ID, DOC_NUM, DOC_ID, IS_LATEST )
+   INSERT INTO PACKAGE_DOCUMENTS ( PV_ID, TEST_ID, DOC_NUM, DOC_ID, IS_LATEST )
-    	 SELECT nTOpv_id AS PV_ID,
+        SELECT nTOpv_id AS PV_ID,
-		 		pd.test_id,
+               pd.test_id,
-				pd.doc_num,
+               pd.doc_num,
-				pd.doc_id,
+               pd.doc_id,
-                pd.IS_LATEST
+               pd.IS_LATEST
-           FROM PACKAGE_DOCUMENTS pd
+          FROM PACKAGE_DOCUMENTS pd
-          WHERE pd.PV_ID = nFROMpv_id;
+         WHERE pd.PV_ID = nFROMpv_id;
 -------------------------------------------- Clone Build Environments -----------------------------------------
     INSERT INTO PACKAGE_BUILD_ENV ( PV_ID, BE_ID, BUILD_TYPE )
-	SELECT nTOpv_id AS PV_ID,
+    SELECT nTOpv_id AS PV_ID,
-		   pkgbe.BE_ID,
+           pkgbe.BE_ID,
-		   pkgbe.BUILD_TYPE
+           pkgbe.BUILD_TYPE
-	  FROM PACKAGE_BUILD_ENV pkgbe
+      FROM PACKAGE_BUILD_ENV pkgbe
-	 WHERE pkgbe.PV_ID = nFROMpv_id;
+     WHERE pkgbe.PV_ID = nFROMpv_id;
 ---------------------------------------------Clone Package Build Info------------------------------------------
     INSERT INTO PACKAGE_BUILD_INFO (PV_ID, BM_ID, BSA_ID)
-	SELECT nTOpv_id AS PV_ID,
+    SELECT nTOpv_id AS PV_ID,
-		   pkgbinfo.BM_ID,
+           pkgbinfo.BM_ID,
-		   pkgbinfo.BSA_ID
+           pkgbinfo.BSA_ID
-	  FROM PACKAGE_BUILD_INFO pkgbinfo
+      FROM PACKAGE_BUILD_INFO pkgbinfo
-	 WHERE pkgbinfo.PV_ID = nFROMpv_id;
+     WHERE pkgbinfo.PV_ID = nFROMpv_id;
 ---------------------------------------------Clone Package Version Processes-----------------------------------
     INSERT INTO PACKAGE_PROCESSES ( PV_ID, PROC_ID )
-	SELECT nTOpv_id AS PV_ID,
+    SELECT nTOpv_id AS PV_ID,
-		   pp.PROC_ID
+           pp.PROC_ID
-	  FROM PACKAGE_PROCESSES pp
+      FROM PACKAGE_PROCESSES pp
-	  WHERE pp.PV_ID = nFROMpv_id;
+     WHERE pp.PV_ID = nFROMpv_id;
+---------------------------------------------Clone Licencing Associations-----------------------------------
+    PK_LICENCING.CLONE_LICENCING(nFROMpv_id, nTOpv_id, nUser_id);
 ---------------------------------------------------------------------------------------------------------------
-	/* LOG ACTION */
+    /* LOG ACTION */
     SELECT pv.PKG_VERSION INTO FromVersion
       FROM PACKAGE_VERSIONS pv
      WHERE pv.PV_ID = nFROMpv_id;
     Log_Action ( nTOpv_id, 'clone_from', nUser_id,
-    			 'Details cloned from: '|| FromVersion );
+                 'Details cloned from: '|| FromVersion );
 ---------------------------------------------------------------------------------------------------------------
 END Basic_Clone;
+/
        INTO ripple_rec;
    END LOOP;
 END CLONED_PACKAGE_PROCESSES;
+/
-CREATE OR REPLACE PACKAGE                 pk_rmapi
+CREATE OR REPLACE PACKAGE                   "PK_RMAPI"
 IS
 /*
 ------------------------------
 ||  Last Modified:  Jeremy Tweddle
 ||  Modified Date:  08/Feb/2008
       rootfile      IN   VARCHAR2
+   )
       RETURN NUMBER;
 /*================================================================================================*/
    PROCEDURE insert_abt_actionlog (rconid IN NUMBER, action IN VARCHAR2);
    FUNCTION insert_package_metrics (
       rtagid IN NUMBER,
       pkgname IN VARCHAR2,
       vext IN VARCHAR2,
       metricstring IN VARCHAR2
+   )
       RETURN NUMBER;
    PROCEDURE update_release_metrics (rtagid IN NUMBER);
 END pk_rmapi;
+/
 CREATE OR REPLACE PROCEDURE DT_SETPROPERTYBYID ( PARAM_ID   IN NUMBER, PARAM_PROPERTY IN VARCHAR2, PARAM_VALUE  IN VARCHAR2, PARAM_LVALUE  IN LONG RAW ) AS BEGIN DECLARE X NUMBER(40); BEGIN SELECT COUNT(*) INTO X FROM MICROSOFTDTPROPERTIES WHERE OBJECTID=PARAM_ID AND PROPERTY=PARAM_PROPERTY; IF X = 0 THEN INSERT INTO MICROSOFTDTPROPERTIES (ID, PROPERTY, OBJECTID, VALUE, LVALUE, VERSION) VALUES (MICROSOFTSEQDTPROPERTIES.NEXTVAL, PARAM_PROPERTY, PARAM_ID, PARAM_VALUE, PARAM_LVALUE, 0); ELSE UPDATE MICROSOFTDTPROPERTIES SET VALUE=PARAM_VALUE, LVALUE=PARAM_LVALUE, VERSION=VERSION+1 WHERE OBJECTID=PARAM_ID AND PROPERTY=PARAM_PROPERTY; END IF; END; END DT_SETPROPERTYBYID;
+/
+/
 CREATE OR REPLACE PROCEDURE DT_DROPUSEROBJECTBYID ( PARAM_ID IN NUMBER ) AS BEGIN DELETE FROM MICROSOFTDTPROPERTIES WHERE OBJECTID = PARAM_ID; END DT_DROPUSEROBJECTBYID;
+/
+CREATE OR REPLACE PACKAGE                   "PK_LICENCING" IS
+/*
+------------------------------
+||  Last Modified:  G.Huddy
+||  Modified Date:  19/Aug/2008
+||  Spec Version:   1.0
+------------------------------
+*/
+   TYPE typeCur IS REF CURSOR;
+    /*================================================================================================*/
+   PROCEDURE ADD_LICENCE ( PvId IN NUMBER, licenceId IN NUMBER, UserId IN NUMBER );
+   PROCEDURE REMOVE_LICENCE ( PvId IN NUMBER, licenceId IN NUMBER, UserId IN NUMBER );
+   PROCEDURE CLONE_LICENCING( fromPvId IN NUMBER, toPvId IN NUMBER, UserId IN NUMBER );
+   FUNCTION  IS_LICENCED( PvId IN NUMBER, licenceId IN NUMBER) RETURN NUMBER;
+   PROCEDURE REMOVE_ALL_LICENCING( PvId IN NUMBER, UserId IN NUMBER );
+   /*================================================================================================*/
+END PK_LICENCING;
+/
 CREATE OR REPLACE FUNCTION ORA_SYSDATE
 RETURN DATE
 IS
 /* ---------------------------------------------------------------------------
     Version: 3.0.0
 END Log_Action;
+/
-CREATE OR REPLACE PACKAGE BODY pk_package
+CREATE OR REPLACE PACKAGE BODY                   "PK_PACKAGE"
 IS
-/*
+   /*
-------------------------------
+   ------------------------------
-||  Last Modified:  J. Tweddle
+   ||  Last Modified:  G.Huddy
-||  Modified Date:  24/08/2007
+   ||  Modified Date:  20/08/2008
-||  Body Version:   1.8
+   ||  Body Version:   1.9 - added licencing removal to destroy_package()
-------------------------------
+   ------------------------------
-*/
+   */
    /*-------------------------------------------------------------------------------------------------------*/
    PROCEDURE new_version (
       nlastpvid                   IN       NUMBER,
       snewpkgversion              IN       VARCHAR2 DEFAULT NULL,
       nuserid                     IN       NUMBER,
       enumissues_state_imported   IN       NUMBER,
       returnpvid                  OUT      NUMBER
+   )
    IS
-      origpkg_id                   package_versions.pkg_id%TYPE;
+      origpkg_id                  package_versions.pkg_id%TYPE;
-      origdlocked                  package_versions.dlocked%TYPE;
+      origdlocked                 package_versions.dlocked%TYPE;
-      ssv_mm                       package_versions.v_mm%TYPE;
+      ssv_mm                      package_versions.v_mm%TYPE;
-      ssv_nmm                      package_versions.v_nmm%TYPE;
+      ssv_nmm                     package_versions.v_nmm%TYPE;
-      ssv_ext                      package_versions.v_ext%TYPE;
+      ssv_ext                     package_versions.v_ext%TYPE;
-      spackageversion              VARCHAR2 (4000);
+      spackageversion             VARCHAR2 (4000);
-      nissuestypes                 NUMBER;
+      nissuestypes                NUMBER;
-      nviewid                      NUMBER;
+      nviewid                     NUMBER;
-      reccount                     NUMBER;
+      reccount                    NUMBER;
-      isreleased                   package_versions.dlocked%TYPE       := 'N';
+      isreleased                  package_versions.dlocked%TYPE := 'N';
-      slabel                       VARCHAR2 (4000)                    := NULL;
+      slabel                      VARCHAR2 (4000)               := NULL;
       CURSOR package_versions_cur
       IS
          SELECT pv.pv_id, pv.is_patch, pv.dlocked
            FROM package_versions pv
           WHERE pv.pkg_version = snewpkgversion
             AND pv.pkg_id IN (SELECT DISTINCT origpv.pkg_id
                                          FROM package_versions origpv
                                         WHERE origpv.pv_id = nlastpvid);
-      package_versions_rec         package_versions_cur%ROWTYPE;
+      package_versions_rec package_versions_cur%ROWTYPE;
       CURSOR clone_package_versions_cur
       IS
          SELECT DISTINCT pkg_id, dlocked
                     FROM package_versions
                    WHERE pv_id = nlastpvid;
-      clone_package_versions_rec   clone_package_versions_cur%ROWTYPE;
+      clone_package_versions_rec clone_package_versions_cur%ROWTYPE;
    BEGIN
       spackageversion := snewpkgversion;
       IF nsettopvid IS NULL
       THEN
          -- SetToPv_id is not supplied, hence proceed.
          /* ---------------------------------------------------- */
-/* Find id package_version exists                       */
+         /* Find id package_version exists                       */
-/* ---------------------------------------------------- */
+         /* ---------------------------------------------------- */
          OPEN package_versions_cur;
          FETCH package_versions_cur
           INTO package_versions_rec;
             -- Automated built config
             IF (cbuildtype = 'A')
             THEN
                spackageversion := '(' || returnpvid || ')' || ssv_ext;
-                                    -- Make sure that version is still unique
+               -- Make sure that version is still unique
             END IF;
             -- Clone Package Version Details --
-            INSERT INTO package_versions
-                        (pv_id, pkg_id, pkg_version, dlocked, created_stamp,
+            INSERT INTO package_versions(pv_id, pkg_id, pkg_version, dlocked, created_stamp,
-                         creator_id, modified_stamp, modifier_id, v_mm, v_nmm,
+                                         creator_id, modified_stamp, modifier_id, v_mm, v_nmm,
-                         v_ext, src_path, pv_description, pv_overview,
+                                         v_ext, src_path, pv_description, pv_overview,
-                         last_pv_id, owner_id, is_deployable,
+                                         last_pv_id, owner_id, is_deployable,
-                         is_build_env_required, build_type, bs_id, is_autobuildable, ripple_field)
+                                         is_build_env_required, build_type, bs_id, is_autobuildable, ripple_field)
-               SELECT returnpvid AS pv_id, origpkg_id AS pkg_id,
+                 SELECT returnpvid AS pv_id, origpkg_id AS pkg_id,
-                      spackageversion AS pkg_version, 'N' AS dlocked,
+                        spackageversion AS pkg_version, 'N' AS dlocked,
-                      ora_sysdate AS created_stamp, nuserid AS creator_id,
+                        ora_sysdate AS created_stamp, nuserid AS creator_id,
-                      ora_sysdatetime AS modified_stamp,
+                        ora_sysdatetime AS modified_stamp,
-                      nuserid AS modifier_id, ssv_mm AS v_mm,
+                        nuserid AS modifier_id, ssv_mm AS v_mm,
-                      ssv_nmm AS v_nmm, ssv_ext AS v_ext, pv.src_path,
+                        ssv_nmm AS v_nmm, ssv_ext AS v_ext, pv.src_path,
-                      pv.pv_description, pv.pv_overview,
+                        pv.pv_description, pv.pv_overview,
-                      nlastpvid AS last_pv_id, pv.owner_id, pv.is_deployable,
+                        nlastpvid AS last_pv_id, pv.owner_id, pv.is_deployable,
-                      pv.is_build_env_required, cbuildtype, pv.bs_id, pv.is_autobuildable, pv.ripple_field
+                        pv.is_build_env_required, cbuildtype, pv.bs_id, pv.is_autobuildable, pv.ripple_field
-                 FROM package_versions pv
+                   FROM package_versions pv
-                WHERE pv.pv_id = nlastpvid;
+                  WHERE pv.pv_id = nlastpvid;
             -- Set Issues Type for cloning ---
             IF origdlocked = 'Y'
             THEN
                nissuestypes := enumissues_state_imported;
                WHERE pd.pv_id = pvid;
          --- Remove from Code Review
          DELETE FROM code_reviews cr
                WHERE cr.pv_id = pvid;
          --- Remove from Code Review URL
          DELETE FROM code_review_url cru
                WHERE cru.pv_id = pvid;
          --- Remove from Unit Tests
                WHERE ut.pv_id = pvid;
          --- Remove from Package BuildEnv
          DELETE FROM package_build_env pbe
                WHERE pbe.pv_id = pvid;
-		 --- Remove from Package Build Info
+         --- Remove from Package Build Info
-		 DELETE FROM package_build_info pbi
+         DELETE FROM package_build_info pbi
-		 	   WHERE pbi.pv_id = pvid;
+               WHERE pbi.pv_id = pvid;
          --- Remove from Build Order
          DELETE FROM build_order bo
                WHERE bo.pv_id = pvid;
+         --- Remove from Licencing
+         PK_LICENCING.REMOVE_ALL_LICENCING( pvid, 0 );
          --- Remove from Note Manager
          DELETE FROM note_manager nm
                WHERE nm.nid = pvid;
          --- Remove from Action log
          DELETE FROM action_log al
                WHERE al.pv_id = pvid;
-		 --- Remove from Do Not Ripple
+         --- Remove from Do Not Ripple
-		 DELETE FROM DO_NOT_RIPPLE dnr
+         DELETE FROM DO_NOT_RIPPLE dnr
-		 	   WHERE dnr.PV_ID = pvid;
+               WHERE dnr.PV_ID = pvid;
-		 --- Remove from Advisory Ripple
+         --- Remove from Advisory Ripple
-		 DELETE FROM ADVISORY_RIPPLE ar
+         DELETE FROM ADVISORY_RIPPLE ar
-		 	   WHERE ar.PV_ID = pvid;
+               WHERE ar.PV_ID = pvid;
-		 --- Remove from Jira Issues
+         --- Remove from Jira Issues
-		 DELETE FROM JIRA_ISSUES jira
+         DELETE FROM JIRA_ISSUES jira
-		 	   WHERE jira.PV_ID = pvid;
+               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
       lastinstallorder     NUMBER;
       ispatchdlocked       package_versions.dlocked%TYPE;
       ssv_mm               package_versions.v_mm%TYPE;
       ssv_nmm              package_versions.v_nmm%TYPE;
       ssv_ext              package_versions.v_ext%TYPE;
-      opatchdepcollector   relmgr_number_tab_t      := relmgr_number_tab_t
+      opatchdepcollector   relmgr_number_tab_t  := relmgr_number_tab_t ();
-                                                                          ();
       CURSOR parent_cur
       IS
          SELECT pv.*, pkg.pkg_name
            FROM package_versions pv, PACKAGES pkg
           WHERE pv.pv_id = nparentpvid AND pv.pkg_id = pkg.pkg_id;
-      parent_rec           parent_cur%ROWTYPE;
+      parent_rec parent_cur%ROWTYPE;
       CURSOR patch_cur
       IS
          SELECT pv.*, pg.pkg_name
            FROM package_versions pv, PACKAGES pg
           WHERE pv.pkg_id = parpkg_id
             AND pv.pkg_version = snewpatchversion
             AND pv.pkg_id = pg.pkg_id;
-      patch_rec            patch_cur%ROWTYPE;
+      patch_rec  patch_cur%ROWTYPE;
       CURSOR releases_cur
       IS
          SELECT rc.pv_id
            FROM release_content rc
           WHERE rc.pv_id = patch_rec.pv_id;
-      releases_rec         releases_cur%ROWTYPE;
+      releases_rec releases_cur%ROWTYPE;
    BEGIN
       -- Get Last Install Order
       SELECT COUNT (*)
         INTO lastinstallorder
         FROM package_patches pp
                          dlocked, created_stamp, creator_id,
                          modified_stamp, modifier_id, v_mm, v_nmm, v_ext,
                          src_path,
                          pv_description,
                          owner_id, is_patch, last_pv_id, build_type, is_build_env_required, bs_id,
-						 is_autobuildable, ripple_field
+                         is_autobuildable, ripple_field
+                        )
                  VALUES (patchpv_id, parpkg_id, snewpatchversion,
                          ispatchdlocked, ora_sysdate, nuserid,
                          ora_sysdatetime, nuserid, ssv_mm, ssv_nmm, ssv_ext,
                          parent_rec.src_path,
                          nuserid, 'Y', patchpv_id, 'M', 'N', 3, 'N', parent_rec.ripple_field
                         );
             INSERT INTO package_patches
                         (pv_id, patch_id, install_order)
-               (SELECT nparentpvid AS pv_id, pv.pv_id AS patch_id,
+                (SELECT nparentpvid AS pv_id, pv.pv_id AS patch_id,
-                       lastinstallorder + 1 AS install_order
+                        lastinstallorder + 1 AS install_order
-                  FROM package_versions pv
+                   FROM package_versions pv
-                 WHERE pv.pv_id = patchpv_id AND pv.is_patch = 'Y');
+                  WHERE pv.pv_id = patchpv_id AND pv.is_patch = 'Y');
             /* LOG ACTION */
             log_action (patchpv_id,
                         'new_version',
                         nuserid,
                   SET is_patch = 'Y'
                 WHERE pv_id = patchpv_id;
                INSERT INTO package_patches
                            (pv_id, patch_id, install_order)
-                  (SELECT nparentpvid AS pv_id, pv.pv_id AS patch_id,
+                   (SELECT nparentpvid AS pv_id, pv.pv_id AS patch_id,
-                          lastinstallorder + 1 AS install_order
+                           lastinstallorder + 1 AS install_order
-                     FROM package_versions pv
+                      FROM package_versions pv
-                    WHERE pv.pv_id = patchpv_id AND pv.is_patch = 'Y');
+                     WHERE pv.pv_id = patchpv_id AND pv.is_patch = 'Y');
             END IF;
             CLOSE releases_cur;
             /* LOG ACTION */
                WHERE dep.pv_id = patchpv_id;
          -- Insert new dependencies
          INSERT INTO package_dependencies
                      (pv_id, dpv_id, pkg_id, dpkg_id, build_type)
-            SELECT patchpv_id AS pv_id, pv.pv_id AS dpv_id,
+              SELECT patchpv_id AS pv_id, pv.pv_id AS dpv_id,
-                   parpkg_id AS pkg_id, pv.pkg_id AS dpkg_id,
+                     parpkg_id AS pkg_id, pv.pkg_id AS dpkg_id,
-                   'L' AS build_type
+                     'L' AS build_type
-              FROM package_versions pv
+                FROM package_versions pv
-             WHERE pv.pv_id IN (
+               WHERE pv.pv_id IN (
                       SELECT *
                         FROM TABLE
                                 (CAST
                                     (opatchdepcollector AS relmgr_number_tab_t)
                                 ));
                      SELECT DISTINCT dep.PV_ID
                        FROM PACKAGE_DEPENDENCIES dep
                       WHERE dep.DPV_ID = PatchId
                      );
-         */
+      */
       /* LOG ACTION */
       IF isobsolete IS NOT NULL
       THEN
          log_action (patchid,
                           (SELECT CAST
                                      (in_list_number (spatchidlist) AS relmgr_number_tab_t
+                                     )
                              FROM DUAL
                           ));
-   /*
+      /*
-   -- Update patch children
+      -- Update patch children
-   UPDATE PACKAGE_VERSIONS pv SET
+      UPDATE PACKAGE_VERSIONS pv SET
-   pv.IS_OBSOLETE = IsObsolete,
+      pv.IS_OBSOLETE = IsObsolete,
-   pv.OBSOLETE_COMMENTS = ObsoleteComments
+      pv.OBSOLETE_COMMENTS = ObsoleteComments
-   WHERE pv.PV_ID IN (
+      WHERE pv.PV_ID IN (
-                  SELECT DISTINCT dep.DPV_ID
+                     SELECT DISTINCT dep.DPV_ID
-                    FROM PACKAGE_DEPENDENCIES dep
+                       FROM PACKAGE_DEPENDENCIES dep
-                   WHERE dep.PV_ID = PatchId
+                      WHERE dep.PV_ID = PatchId
-                  );
+                     );
-   -- Update patch parent
+      -- Update patch parent
-   UPDATE PACKAGE_VERSIONS pv SET
+      UPDATE PACKAGE_VERSIONS pv SET
-   pv.IS_OBSOLETE = IsObsolete,
+      pv.IS_OBSOLETE = IsObsolete,
-   pv.OBSOLETE_COMMENTS = ObsoleteComments
+      pv.OBSOLETE_COMMENTS = ObsoleteComments
-   WHERE pv.PV_ID IN (
+      WHERE pv.PV_ID IN (
-                  SELECT DISTINCT dep.PV_ID
+                     SELECT DISTINCT dep.PV_ID
-                    FROM PACKAGE_DEPENDENCIES dep
+                       FROM PACKAGE_DEPENDENCIES dep
-                   WHERE dep.DPV_ID = PatchId
+                      WHERE dep.DPV_ID = PatchId
-                  );
+                     );
       */
-   /* LOG ACTION
+      /* LOG ACTION
-   IF IsObsolete IS NOT NULL THEN
+      IF IsObsolete IS NOT NULL THEN
          Log_Action ( PatchId, 'patch_obsolete', UserId,
-                  'Obsolete patch. '|| ObsoleteComments );
+                     'Obsolete patch. '|| ObsoleteComments );
-   ELSE
+      ELSE
-      Log_Action ( PatchId, 'patch_obsolete', UserId,
+         Log_Action ( PatchId, 'patch_obsolete', UserId,
-                  'Undo patch obsolete.' );
+                     'Undo patch obsolete.' );
-   END IF;  */
+      END IF;  */
    END;
 /*-------------------------------------------------------------------------------------------------------*/
    PROCEDURE add_process (
       nprocid         IN   processes.proc_id%TYPE,
       npvid           IN   package_processes.pv_id%TYPE,
       nuserid         IN   NUMBER
+   )
    IS
       pkgname   VARCHAR2 (100);
-/*Rupesh Release on 17/05/2006*/
    BEGIN
       INSERT INTO processes
                   (proc_id, proc_name, proc_description, run_as, pkg_owner,
                    is_interface
+                  )
       nuserid       IN   NUMBER
+   )
    IS
       processname   VARCHAR2 (4000);
-/*Rupesh Release on 17/05/2006*/
       CURSOR proc_cur
       IS
          SELECT prc.proc_id
            FROM processes prc
           WHERE prc.proc_id IN (
                                         (in_list_number (nprocidlist) AS relmgr_number_tab_t
+                                        )
                                 FROM DUAL
                              ));
-      proc_rec      proc_cur%ROWTYPE;
+      proc_rec  proc_cur%ROWTYPE;
    BEGIN
       OPEN proc_cur;
       FETCH proc_cur
        INTO proc_rec;
       WHILE proc_cur%FOUND
       LOOP
-         INSERT INTO package_processes
+         INSERT INTO package_processes (proc_id, pv_id)
-                     (proc_id, pv_id
-                     )
-              VALUES (proc_rec.proc_id, npvid
+              VALUES (proc_rec.proc_id, npvid);
-                     );
          SELECT prc.proc_name
            INTO processname
            FROM processes prc
           WHERE prc.proc_id = proc_rec.proc_id;
       npvid     IN   package_processes.pv_id%TYPE,
       nuserid   IN   NUMBER
+   )
    IS
       processname   VARCHAR2 (4000);
-/* Rupesh Release on 17/05/2006*/
    BEGIN
       SELECT prc.proc_name
         INTO processname
         FROM processes prc
        WHERE prc.proc_id = nprocid;
                   nuserid,
                   'Removed process with health tag ' || processname
                  );
    END;
 /*-------------------------------------------------------------------------------------------------------*/
-  /*
+   /*
-  Author: Rupesh Solanki
+   ** Author: Rupesh Solanki
-  Purpose: To move package versions from one release to another
+   ** Purpose: To move package versions from one release to another
-  Release: 4th September 2006
+   ** Release: 4th September 2006
-  */
+   */
-  PROCEDURE move_package (
+   PROCEDURE move_package (
-   	  npvid  			  IN package_versions.pv_id%TYPE,
+      npvid        IN package_versions.pv_id%TYPE,
-	  nrtagid 			  IN release_tags.rtag_id%TYPE,
+      nrtagid      IN release_tags.rtag_id%TYPE,
-	  nnewrtagid 		  IN release_tags.rtag_id%TYPE,
+      nnewrtagid   IN release_tags.rtag_id%TYPE,
-	  nuserid			  IN NUMBER
+      nuserid      IN NUMBER
-  )
+   )
-  IS
+   IS
-  oldrtag_name VARCHAR2(4000);
+      oldrtag_name VARCHAR2(4000);newrtag_name VARCHAR2(4000);
+   BEGIN
+      SELECT rtag_name into oldrtag_name
+      FROM RELEASE_TAGS
+      WHERE rtag_id = nrtagid;
-  newrtag_name VARCHAR2(4000);
+      SELECT rtag_name into newrtag_name
+      FROM RELEASE_TAGS
+      WHERE rtag_id = nnewrtagid;
+      /* Table Work In Progress*/
+      UPDATE WORK_IN_PROGRESS
+      SET RTAG_ID = nnewrtagid
+      WHERE RTAG_ID = nrtagid
+      AND PV_ID = npvid;
+      /* Table PLANNED*/
+      UPDATE PLANNED
+      SET RTAG_ID = nnewrtagid
+      WHERE RTAG_ID = nrtagid
+      AND PV_ID = npvid;
-  BEGIN
-  SELECT rtag_name into oldrtag_name
-  FROM RELEASE_TAGS
-  WHERE rtag_id = nrtagid;
-  SELECT rtag_name into newrtag_name
-  FROM RELEASE_TAGS
-  WHERE rtag_id = nnewrtagid;
-  /* Table Work In Progress*/
-  	   UPDATE WORK_IN_PROGRESS
-	   SET RTAG_ID = nnewrtagid
-	   WHERE RTAG_ID = nrtagid
-	   AND PV_ID = npvid;
-  /* Table PLANNED*/
-  	   UPDATE PLANNED
-	   SET RTAG_ID = nnewrtagid
-	   WHERE RTAG_ID = nrtagid
-	   AND PV_ID = npvid;
       -- Log Action --
       log_action (npvid,
                   'move_package_version',
                   nuserid,
                   'Moved package version from ' || oldrtag_name || ' to ' || newrtag_name
                  );
+   END;
-  END;
 /*-------------------------------------------------------------------------------------------------------*/
+   /*
+   ** Author: Rupesh Solanki
+   ** Purpose: To modify the product state from integration to test to deployment
+   ** Release: 25th January 2006
+   */
-PROCEDURE modify_product_state (
+   PROCEDURE modify_product_state (
-   	  npvid IN package_versions.pv_id%TYPE,
+      npvid    IN package_versions.pv_id%TYPE,
-	  nrtagid IN release_tags.rtag_id%TYPE,
+      nrtagid  IN release_tags.rtag_id%TYPE,
-	  nstateid IN product_states.state_id%TYPE,
+      nstateid IN product_states.state_id%TYPE,
-	  nuserid IN NUMBER
+      nuserid  IN NUMBER
    ) IS
-   sStateName VARCHAR2(4000);
+      sStateName VARCHAR2(4000);
-   sRtagName VARCHAR2(4000);
+      sRtagName VARCHAR2(4000);
-  /*
-  Author: Rupesh Solanki
-  Purpose: To modify the product state from integration to test to deployment
-  Release: 25th January 2006
-  */
-  BEGIN
+   BEGIN
-  UPDATE RELEASE_CONTENT
+      UPDATE RELEASE_CONTENT
-  SET PRODUCT_STATE = nstateid
+      SET PRODUCT_STATE = nstateid
-  WHERE PV_ID = npvid
+      WHERE PV_ID = npvid
-  AND RTAG_ID = nrtagid;
+      AND RTAG_ID = nrtagid;
-  SELECT STATE INTO sStateName
+      SELECT STATE INTO sStateName
-  FROM PRODUCT_STATES
+      FROM PRODUCT_STATES
-  WHERE STATE_ID = nstateid;
+      WHERE STATE_ID = nstateid;
-  SELECT RTAG_NAME into sRtagName
+      SELECT RTAG_NAME into sRtagName
-  FROM RELEASE_TAGS
+      FROM RELEASE_TAGS
-  WHERE RTAG_ID = nrtagid;
+      WHERE RTAG_ID = nrtagid;
-  -- Log Action --
+      -- Log Action --
-  log_action (npvid,
+      log_action (npvid,
-              'modify_product_state',
+                  'modify_product_state',
-               nuserid,
+                     nuserid,
-               sStateName || ' in '|| sRtagName
+                     sStateName || ' in '|| sRtagName
-              );
+                  );
+   END;
-  END;
 /*-------------------------------------------------------------------------------------------------------*/
+   /*
+   ** Author: Jeremy Tweddle
+   ** Date: 24/Aug/2007
+   */
-  PROCEDURE add_code_review_url (
+   PROCEDURE add_code_review_url (
-        npvid           IN    NUMBER,
+      npvid           IN    NUMBER,
-        nprojid         IN    NUMBER,
+      nprojid         IN    NUMBER,
-        surl            IN    VARCHAR2,
+      surl            IN    VARCHAR2,
-        sreason         IN    VARCHAR2,
+      sreason         IN    VARCHAR2,
-        ddateofreview   IN    DATE
+      ddateofreview   IN    DATE
-  ) IS
+   ) IS
-  ncrid NUMBER;
+      ncrid NUMBER;
-  /*
-  Author: Jeremy Tweddle
-  Date: 24/Aug/2007
-  */
-  BEGIN
+   BEGIN
       SELECT seq_cr_id.NEXTVAL INTO ncrid FROM DUAL;
       INSERT INTO code_review_url ( cr_id, pv_id, proj_id, url, reason, date_of_review,
                                     last_modified )
       VALUES ( ncrid, npvid, nprojid, surl, sreason, ddateofreview, ora_sysdate );
-  END;
+   END;
 /*-------------------------------------------------------------------------------------------------------*/
+   /*
+   ** Author: Jeremy Tweddle
+   ** Date: 22/Aug/2007
+   */
-  PROCEDURE update_code_review_url (
+   PROCEDURE update_code_review_url (
-        ncrid           IN    NUMBER,
+      ncrid           IN    NUMBER,
-        nprojid         IN    NUMBER,
+      nprojid         IN    NUMBER,
-        surl            IN    VARCHAR2,
+      surl            IN    VARCHAR2,
-        sreason         IN    VARCHAR2
+      sreason         IN    VARCHAR2
-  ) IS
+   ) IS
-  /*
-  Author: Jeremy Tweddle
-  Date: 22/Aug/2007
-  */
-  BEGIN
+   BEGIN
       UPDATE code_review_url
       SET url = surl,
           proj_id = nprojid,
           reason = sreason,
           last_modified = ora_sysdate
       WHERE cr_id = ncrid;
-  END;
+   END;
 /*-------------------------------------------------------------------------------------------------------*/
-  PROCEDURE remove_code_review_url (
-        ncrid           IN    NUMBER
-  ) IS
-  /*
+   /*
-  Author: Jeremy Tweddle
+   ** Author: Jeremy Tweddle
-  Date: 22/Aug/2007
+   ** Date: 22/Aug/2007
-  */
+   */
+   PROCEDURE remove_code_review_url (
+      ncrid  IN    NUMBER
+   ) IS
-  BEGIN
+   BEGIN
       DELETE FROM code_review_url
       WHERE cr_id = ncrid;
-  END;
+   END;
 /*-------------------------------------------------------------------------------------------------------*/
 END pk_package;
+/
 CREATE OR REPLACE PACKAGE BODY                 pk_archive
 CREATE OR REPLACE PACKAGE BODY                   "PK_PLANNED" IS
 /*
 ------------------------------
 ||  Last Modified:  G.Huddy
-||  Modified Date:  05/Aug/2008
+||  Modified Date:  28/May/2008
-||  Body Version:   1.2
+||  Body Version:   1.1
 ------------------------------
 */
 /*-------------------------------------------------------------------------------------------------------*/
 PROCEDURE ADD_PACKAGE ( newPvId IN NUMBER, ViewId IN NUMBER, RtagId IN NUMBER, UserId IN NUMBER ) IS
-    oldPvId NUMBER;
+	oldPvId NUMBER;
-    ReleaseLocation VARCHAR2(4000);
+	ReleaseLocation VARCHAR2(4000);
-    IsPatch CHAR(1) := NULL;
+	IsPatch CHAR(1) := NULL;
-    sLocation VARCHAR2(4000) := NULL;
+	sLocation VARCHAR2(4000) := NULL;
-    nRtagIdLocation NUMBER;
+	nRtagIdLocation NUMBER;
 BEGIN
-    /*--------------- Business Rules Here -------------------*/
+	/*--------------- Business Rules Here -------------------*/
-    /*-------------------------------------------------------*/
+	/*-------------------------------------------------------*/
-    BEGIN
+	BEGIN
-        -- Check if Exists in "Work in progress" anywhere in the world, except "Closed mode" releases
+		-- 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
+		SELECT proj.PROJ_NAME ||' > '|| rt.RTAG_NAME, rt.RTAG_ID  INTO sLocation, nRtagIdLocation
-          FROM PLANNED pl,
+		  FROM PLANNED pl,
-               RELEASE_TAGS rt,
+			   RELEASE_TAGS rt,
-               PROJECTS proj
+			   PROJECTS proj
-         WHERE pl.PV_ID = newPvId
+		 WHERE pl.PV_ID = newPvId
-           AND pl.RTAG_ID = rt.RTAG_ID
+		   AND pl.RTAG_ID = rt.RTAG_ID
-           AND rt.OFFICIAL != 'Y'
+		   AND rt.OFFICIAL != 'Y'
-           AND rt.PROJ_ID = proj.PROJ_ID;
+		   AND rt.PROJ_ID = proj.PROJ_ID;
-        EXCEPTION
-            WHEN NO_DATA_FOUND THEN
-                sLocation := NULL;
-    END;
+		EXCEPTION
+			WHEN NO_DATA_FOUND THEN
+				sLocation := NULL;
+	END;
-    IF (sLocation IS NULL) OR (nRtagIdLocation = RtagId) THEN
-        -- Add to "Pending" area
-        INSERT INTO PLANNED ( RTAG_ID, PV_ID, VIEW_ID, OPERATION )
-        VALUES( RtagId, newPvId, ViewId, 'R' );
+	IF (sLocation IS NULL) OR (nRtagIdLocation = RtagId) THEN
-        /* LOG ACTION */
+		-- Add to "Pending" area
-        SELECT proj.PROJ_NAME ||' / '|| rt.RTAG_NAME INTO ReleaseLocation
+		INSERT INTO PLANNED ( RTAG_ID, PV_ID, VIEW_ID, OPERATION )
-          FROM PROJECTS proj,
-               RELEASE_TAGS rt
-         WHERE rt.PROJ_ID = proj.PROJ_ID
-           AND rt.RTAG_ID = RtagId;
+		VALUES( RtagId, newPvId, ViewId, 'R' );
-        Log_Action ( newPvId, 'add_to_planned', UserId, 'Location: '|| ReleaseLocation );
+		/* LOG ACTION */
+		SELECT proj.PROJ_NAME ||' / '|| rt.RTAG_NAME INTO ReleaseLocation
+		  FROM PROJECTS proj,
-    ELSE
+			   RELEASE_TAGS rt
+		 WHERE rt.PROJ_ID = proj.PROJ_ID
+		   AND rt.RTAG_ID = RtagId;
-        RAISE_APPLICATION_ERROR (-20000, 'This version is already in Pending Area at '|| sLocation ||'.' );
+		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 IF;
 END;
 /*-------------------------------------------------------------------------------------------------------*/
 PROCEDURE REMOVE_PACKAGE ( PvId IN NUMBER, RtagId IN NUMBER, UserId IN NUMBER ) IS
-    ReleaseLocation VARCHAR2(4000);
+	ReleaseLocation VARCHAR2(4000);
 BEGIN
-    /*--------------- Business Rules Here -------------------*/
+	/*--------------- Business Rules Here -------------------*/
-    /*-------------------------------------------------------*/
+	/*-------------------------------------------------------*/
-    -- Get release location for logging pusposes
+	-- Get release location for logging pusposes
-    SELECT proj.PROJ_NAME  ||' / '|| rt.RTAG_NAME INTO ReleaseLocation
+	SELECT proj.PROJ_NAME  ||' / '|| rt.RTAG_NAME INTO ReleaseLocation
-      FROM PROJECTS proj,
+	  FROM PROJECTS proj,
-           RELEASE_TAGS rt
+		   RELEASE_TAGS rt
-     WHERE rt.PROJ_ID = proj.PROJ_ID
+	 WHERE rt.PROJ_ID = proj.PROJ_ID
-       AND rt.RTAG_ID = RtagId;
+	   AND rt.RTAG_ID = RtagId;
-    -- Delete from Work In Progress
+	-- Delete from Work In Progress
-    DELETE
+	DELETE
-      FROM PLANNED pl
+	  FROM PLANNED pl
-     WHERE pl.RTAG_ID = RtagId
+	 WHERE pl.RTAG_ID = RtagId
-       AND pl.PV_ID = PvId;
+	   AND pl.PV_ID = PvId;
-    Log_Action ( PvId, 'deleted_from_pending', UserId, 'Location: '|| ReleaseLocation );
+	Log_Action ( PvId, 'deleted_from_pending', UserId, 'Location: '|| ReleaseLocation );
 END;
 /*-------------------------------------------------------------------------------------------------------*/
 FUNCTION GET_PACKAGE_VIEW ( PvId IN NUMBER, RtagId IN NUMBER ) RETURN NUMBER IS
-    ReturnValue NUMBER;
+	ReturnValue NUMBER;
 BEGIN
-    SELECT pl.VIEW_ID INTO ReturnValue
+	SELECT pl.VIEW_ID INTO ReturnValue
-      FROM PLANNED pl
+	  FROM PLANNED pl
-     WHERE pl.RTAG_ID = RtagId
+	 WHERE pl.RTAG_ID = RtagId
-       AND pl.PV_ID = PvId;
+	   AND pl.PV_ID = PvId;
-    RETURN ReturnValue;
+	RETURN ReturnValue;
 END;
 /*-------------------------------------------------------------------------------------------------------*/
 PROCEDURE GET_VIEW_CONTENT ( RtagId IN NUMBER, ViewId IN NUMBER, 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 INTO IsBaseView
+	SELECT vi.BASE_VIEW INTO IsBaseView
-      FROM VIEWS vi
+	  FROM VIEWS vi
-     WHERE vi.VIEW_ID = ViewId;
+	 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,
-               rel.operation
-          FROM PLANNED rel,
-               packages pkg,
-               package_versions pv
-         WHERE pv.pkg_id = pkg.pkg_id
-           AND rel.pv_id = pv.pv_id
-           AND rel.VIEW_ID = ViewId
-           AND rel.RTAG_ID = RtagId
-         ORDER BY UPPER(pkg.PKG_NAME);
-    ELSE
+	IF (IsBaseView = 'Y') THEN
-        -- Get non base view content
+		-- Get Base view content
-        OPEN RecordSet FOR
+		OPEN RecordSet FOR
-        SELECT 0 AS PKG_STATE,
+		SELECT 0 AS PKG_STATE,
-               NULL AS DEPRECATED_STATE,
+			   NULL AS DEPRECATED_STATE,
-               pv.pv_id,
+			   pv.pv_id,
-               pkg.pkg_name,
+			   pkg.pkg_name,
-               pv.pkg_version,
+			   pv.pkg_version,
-               pv.dlocked,
+			   pv.dlocked,
-               pv.pv_description,
+			   pv.pv_description,
-               pv.BUILD_TYPE,
+			   pv.BUILD_TYPE,
-               rel.operation
+			   rel.operation
-          FROM PLANNED rel,
+		  FROM PLANNED rel,
-               packages pkg,
+			   packages pkg,
-               package_versions pv,
+			   package_versions pv
-               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 vd.VIEW_ID = ViewId
+		   AND rel.VIEW_ID = ViewId
-           AND vd.PKG_ID = pv.PKG_ID
+		   AND rel.RTAG_ID = RtagId
-         ORDER BY UPPER(pkg.PKG_NAME);
+		 ORDER BY UPPER(pkg.PKG_NAME);
+	ELSE
+		-- 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,
+			   rel.operation
+		  FROM PLANNED rel,
+			   packages pkg,
+			   package_versions pv,
+			   VIEW_DEF vd
+		 WHERE pv.pkg_id = pkg.pkg_id
+		   AND rel.pv_id = pv.pv_id
+		   AND rel.RTAG_ID = RtagId
+		   AND vd.VIEW_ID = ViewId
+		   AND vd.PKG_ID = pv.PKG_ID
+		 ORDER BY UPPER(pkg.PKG_NAME);
-    END IF;
+	END IF;
 END;
 /*-------------------------------------------------------------------------------------------------------*/
 PROCEDURE CHANGE_PACKAGE_VIEW ( PvId IN NUMBER, RtagId IN NUMBER, NewViewId IN NUMBER ) IS
 BEGIN
-    UPDATE PLANNED pl SET
+	UPDATE PLANNED pl SET
-    pl.VIEW_ID = NewViewId
+	pl.VIEW_ID = NewViewId
-    WHERE pl.PV_ID = PvId
+	WHERE pl.PV_ID = PvId
-      AND pl.RTAG_ID = RtagId;
+	  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.
 END;
 /*-------------------------------------------------------------------------------------------------------*/
 END PK_PLANNED;
+/
-CREATE OR REPLACE PACKAGE BODY  pk_rmapi
+CREATE OR REPLACE PACKAGE BODY                   "PK_RMAPI"
 IS
 /*
 ------------------------------
-||  Last Modified:  Jeremy Tweddle
+||  Last Modified:  Gordon Huddy
-||  Modified Date:  08/Feb/2008
+||  Modified Date:  08/Aug/2008
-||  Body Version:   3.3
+||  Body Version:   3.4
 ------------------------------
 */
    /*-------------------------------------------------------------------------------------------------------*/
    FUNCTION package_dependencies (pkgname VARCHAR2, pkgversion VARCHAR2)
       metricstring             IN   VARCHAR2
+   )
       RETURN NUMBER
    IS
       citemcollection          relmgr_varchar2_tab_t  := relmgr_varchar2_tab_t ();
-      pvid                     NUMBER                 := 0;
+      lv_pvid                  NUMBER                 := 0;
       rownumber                NUMBER;
       rowcontent               VARCHAR2(4000);
       metricname               VARCHAR2(1000);
       metricvalue              VARCHAR2(4000);
       return_insert_error      NUMBER                 := -1;
       return_insert_success    NUMBER                 := 0;
       /* Metrics */
-      branches                 NUMBER;
+      lv_branches                 NUMBER;
-      branchlist               VARCHAR2(4000);
+      lv_branchlist               VARCHAR2(4000);
-      codefiles                NUMBER;
+      lv_codefiles                NUMBER;
-      ignoredfiles             NUMBER;
+      lv_ignoredfiles             NUMBER;
-      directories              NUMBER;
+      lv_directories              NUMBER;
-      directorydepth           NUMBER;
+      lv_directorydepth           NUMBER;
-      totalfiles               NUMBER;
+      lv_totalfiles               NUMBER;
-      makefiles                NUMBER;
+      lv_makefiles                NUMBER;
-      blanklines               NUMBER;
+      lv_blanklines               NUMBER;
-      codelines                NUMBER;
+      lv_codelines                NUMBER;
-      commentlines             NUMBER;
+      lv_commentlines             NUMBER;
    BEGIN
       /*--------------- Business Rules Here -------------------*/
       IF (rtagid IS NULL)
       THEN
          RETURN return_insert_error;
       END IF;
       BEGIN
          SELECT pv.pv_id
-           INTO pvid
+           INTO lv_pvid
            FROM package_versions pv, packages pkg, release_content rc
           WHERE pv.pkg_id = pkg.pkg_id
             AND rc.rtag_id = rtagid
             AND pv.pv_id = rc.pv_id
             AND pkg.pkg_name = pkgname
             AND NVL (pv.v_ext, '|LINK_A_NULL|') = NVL (vext, '|LINK_A_NULL|');
       EXCEPTION
            WHEN NO_DATA_FOUND
            THEN
-                pvid := 0;
+                lv_pvid := 0;
       END;
       citemcollection := in_list_varchar2 (metricstring, ';');
       FOR rownumber IN 1 .. citemcollection.COUNT
       LOOP
          rowcontent := citemcollection(rownumber);
          metricvalue := SUBSTR(rowcontent, (INSTR(rowcontent, '=') + 1));
          metricname := REGEXP_REPLACE(rowcontent, '=.*$', '');
-         IF    metricname = 'ccbranch.count' THEN branches := metricvalue;
+         IF    metricname = 'ccbranch.count' THEN lv_branches := metricvalue;
-         ELSIF metricname = 'ccbranch.list' THEN branchlist := metricvalue;
+         ELSIF metricname = 'ccbranch.list' THEN lv_branchlist := metricvalue;
-         ELSIF metricname = 'code.files' THEN codefiles := metricvalue;
+         ELSIF metricname = 'code.files' THEN lv_codefiles := metricvalue;
-         ELSIF metricname = 'code.ignored' THEN ignoredfiles := metricvalue;
+         ELSIF metricname = 'code.ignored' THEN lv_ignoredfiles := metricvalue;
-         ELSIF metricname = 'count.dir' THEN directories := metricvalue;
+         ELSIF metricname = 'count.dir' THEN lv_directories := metricvalue;
-         ELSIF metricname = 'count.dirdepth' THEN directorydepth := metricvalue;
+         ELSIF metricname = 'count.dirdepth' THEN lv_directorydepth := metricvalue;
-         ELSIF metricname = 'count.file' THEN totalfiles := metricvalue;
+         ELSIF metricname = 'count.file' THEN lv_totalfiles := metricvalue;
-         ELSIF metricname = 'count.makefile' THEN makefiles := metricvalue;
+         ELSIF metricname = 'count.makefile' THEN lv_makefiles := metricvalue;
-         ELSIF metricname = 'lines.blank' THEN blanklines := metricvalue;
+         ELSIF metricname = 'lines.blank' THEN lv_blanklines := metricvalue;
-         ELSIF metricname = 'lines.code' THEN codelines := metricvalue;
+         ELSIF metricname = 'lines.code' THEN lv_codelines := metricvalue;
-         ELSIF metricname = 'lines.comment' THEN commentlines := metricvalue;
+         ELSIF metricname = 'lines.comment' THEN lv_commentlines := metricvalue;
          END IF;
       END LOOP;
-      IF (pvid > 0)
+      IF (lv_pvid > 0)
       THEN
          -- Delete any existing entries for this package version to makes sure our data is untainted
          DELETE FROM package_metrics pm
-         WHERE pm.pv_id = pvid;
+         WHERE pm.pv_id = lv_pvid;
          -- Insert the new data into the metrics table
          INSERT INTO package_metrics
                      (pv_id, branches, branch_list, code_files, ignored_files, directories, directory_depth,
                       total_files, makefiles, blank_lines, code_lines, comment_lines, created_stamp
+                     )
-              VALUES (pvid, branches, branchlist, codefiles, ignoredfiles, directories, directorydepth,
+              VALUES (lv_pvid, lv_branches, lv_branchlist, lv_codefiles, lv_ignoredfiles, lv_directories, lv_directorydepth,
-                      totalfiles, makefiles, blanklines, codelines, commentlines, ora_sysdatetime
+                      lv_totalfiles, lv_makefiles, lv_blanklines, lv_codelines, lv_commentlines, ora_sysdatetime
                      );
          -- Now update the Release_Metrics Table
          update_release_metrics(rtagid);
          RETURN return_insert_success;
       ELSE
          RETURN return_insert_error;
       END IF;
    END;
 /*-------------------------------------------------------------------------------------------------------*/
    PROCEDURE update_release_metrics (rtagid IN NUMBER)
    IS
-      totalpackages       NUMBER;
+      lv_totalpackages       NUMBER;
-      autobuilt           NUMBER;
+      lv_autobuilt           NUMBER;
-      linesofcode         NUMBER;
+      lv_linesofcode         NUMBER;
-      unittested          NUMBER;
+      lv_unittested          NUMBER;
-      autotested          NUMBER;
+      lv_autotested          NUMBER;
-      numOfbranches       NUMBER;
+      lv_numOfbranches       NUMBER;
-      lastbuildtime       DATE;
+      lv_lastbuildtime       DATE;
    BEGIN
       IF (rtagid > 0)
       THEN
          -- Get the total number of packages in this release and the number of
          -- those that are autobuilt
          SELECT COUNT (DISTINCT rc.pv_id),
                 COUNT (DISTINCT autobuilt_qry.pv_id)
-           INTO totalpackages, autobuilt
+           INTO lv_totalpackages, lv_autobuilt
            FROM release_content rc,
                 package_versions pv,
+                (
                  SELECT rc.pv_id
                    FROM release_content rc,
                     AND rc.rtag_id = rtagid
                 ) autobuilt_qry
           WHERE pv.pv_id = rc.pv_id
             AND rc.rtag_id = rtagid
             AND autobuilt_qry.pv_id (+) = rc.pv_id;
          -- Get the build time of the last package built in this release and the
          -- total number of lines of code
          SELECT MAX(pm.created_stamp),
                 SUM(pm.code_lines)
-           INTO lastbuildtime, linesofcode
+           INTO lv_lastbuildtime, lv_linesofcode
            FROM package_metrics pm, release_content rc
           WHERE pm.pv_id = rc.pv_id
             AND rc.rtag_id = rtagid;
          -- Get the number of packages with unit tests in this release and the
          -- number of those that are autotested
-         SELECT COUNT(DISTINCT ut.pv_id),
+         SELECT COUNT(DISTINCT ut.pv_id) INTO lv_unittested
-                COUNT(DISTINCT autotest_qry.pv_id)
-           INTO unittested, autotested
            FROM unit_tests ut,
-                release_content rc,
+                release_content rc
-                (
-                 SELECT ut.pv_id
-                   FROM unit_tests ut, release_content rc
-                  WHERE ut.pv_id = rc.pv_id
-                    AND rc.rtag_id = rtagid
-                    AND ut.test_types_fk = 7
-                ) autotest_qry
           WHERE ut.pv_id = rc.pv_id
             AND rc.rtag_id = rtagid
-            AND autotest_qry.pv_id (+) = ut.pv_id;
+        AND ut.test_types_fk != 1;
+        SELECT COUNT(DISTINCT ut.pv_id) INTO lv_autotested
+          FROM unit_tests ut,
+               release_content rc
+         WHERE ut.pv_id = rc.pv_id
+           AND rc.rtag_id = rtagid
+           AND ut.test_types_fk = 7;
          -- Count the number of unique branches in the packages in this release.
---         SELECT COUNT(DISTINCT branch) INTO numOfbranches
+--         SELECT COUNT(DISTINCT branch) INTO lv_numOfbranches
 --           FROM (
 --                SELECT pv_id,
 --                       regexp_substr(str, '[^,]+', 1, level) branch,
 --                       level lv,
 --                       lag(level, 1, 0) over (partition by pv_id order by level) lg
 --                          AND rc.rtag_id = rtagid
 --                       )
 --                CONNECT BY regexp_substr(str, '[^,]+', 1, LEVEL) IS NOT NULL
 --                )
 --          WHERE lv != lg;
          UPDATE release_metrics rm
-            SET rm.total_packages = totalpackages,
+            SET rm.total_packages = lv_totalpackages,
-                rm.autobuilt = autobuilt,
+                rm.autobuilt = lv_autobuilt,
-                rm.lines_of_code = linesofcode,
+                rm.lines_of_code = lv_linesofcode,
-                rm.unit_tested = unittested,
+                rm.unit_tested = lv_unittested,
-                rm.autotested = autotested,
+                rm.autotested = lv_autotested,
---                rm.branches = numOfbranches,
+--                rm.branches = lv_numOfbranches,
-                rm.last_build_time = lastbuildtime
+                rm.last_build_time = lv_lastbuildtime
           WHERE rtag_id = rtagid;
          IF (SQL%ROWCOUNT = 0)
          THEN
             INSERT INTO release_metrics
                         (rtag_id, total_packages, autobuilt, lines_of_code, unit_tested,
                          autotested, last_build_time
+                        )
-                 VALUES (rtagid, totalpackages, autobuilt, linesofcode, unittested,
+                 VALUES (rtagid, lv_totalpackages, lv_autobuilt, lv_linesofcode, lv_unittested,
-                         autotested, lastbuildtime
+                         lv_autotested, lv_lastbuildtime
                         );
          END IF;
 --         IF (SQL%ROWCOUNT = 0)
 --         THEN
 --            INSERT INTO release_metrics
 --                        (rtag_id, total_packages, autobuilt, lines_of_code, unit_tested,
 --                         autotested, branches, last_build_time
 --                        )
---                 VALUES (rtagid, totalpackages, autobuilt, linesofcode, unittested,
+--                 VALUES (rtagid, lv_totalpackages, lv_autobuilt, lv_linesofcode, lv_unittested,
---                         autotested, numOfbranches, lastbuildtime
+--                         lv_autotested, lv_numOfbranches, lv_lastbuildtime
 --                        );
 --         END IF;
 --      ELSE
 --         raise_application_error (-20000, 'RtagId must be supplied.');
       END IF;
    END;
 /*-------------------------------------------------------------------------------------------------------*/
 END pk_environment_test;
+/
+CREATE OR REPLACE PACKAGE BODY                   "PK_LICENCING" IS
+/*
+------------------------------
+||  Last Modified:  G.Huddy
+||  Modified Date:  19/Aug/2008
+||  Body Version:   1.0
+------------------------------
+*/
+   PROCEDURE ADD_LICENCE ( PvId IN NUMBER, licenceId IN NUMBER, UserId IN NUMBER ) IS
+   BEGIN
+      IF IS_LICENCED(PvId, licenceId) = 0 THEN
+         -- Add entry to licencing table
+         INSERT INTO licencing (pv_id, licence)
+         VALUES (PvId, licenceId);
+         -- log action
+         -- TODO
+      END IF;
+   END;
+/*-------------------------------------------------------------------------------------------------------*/
+   PROCEDURE REMOVE_LICENCE ( PvId IN NUMBER, licenceId IN NUMBER, UserId IN NUMBER ) IS
+   BEGIN
+      IF NOT IS_LICENCED(PvId, licenceId) = 0 THEN
+         -- Delete entry from licencing table
+         DELETE FROM licencing
+         WHERE pv_id = PvId
+         AND licence = licenceId;
+         -- log action
+         -- TODO
+      END IF;
+   END;
+/*-------------------------------------------------------------------------------------------------------*/
+   PROCEDURE CLONE_LICENCING( fromPvId IN NUMBER, toPvId IN NUMBER, UserId IN NUMBER ) IS
+      CURSOR licencing_cur
+      IS
+         SELECT *
+         FROM licencing
+         WHERE pv_id = fromPvId;
+      licencing_rec licencing_cur%ROWTYPE;
+   BEGIN
+      -- For each licence association that the Last PV_ID had, create a similar
+      -- arrangement for the new PV_ID
+      -- NOTE that any additional licencing associations the new PV_ID has that the old one
+      -- does not have, are retained. The assumption is that the user has made those and so we do not
+      -- have any right to destroy them without asking them.
+      OPEN licencing_cur;
+      FETCH licencing_cur
+      INTO licencing_rec;
+      WHILE licencing_cur%FOUND
+      LOOP
+         ADD_LICENCE ( toPvId, licencing_rec.licence, UserId );
+         FETCH licencing_cur
+         INTO licencing_rec;
+      END LOOP;
+   END;
+/*-------------------------------------------------------------------------------------------------------*/
+   FUNCTION IS_LICENCED( PvId IN NUMBER, licenceId IN NUMBER) RETURN NUMBER IS
+      found NUMBER;
+   BEGIN
+      SELECT COUNT(*) INTO found
+      FROM licencing
+      WHERE pv_id = PvId
+      AND licence = LicenceId;
+      RETURN found;
+   END;
+/*-------------------------------------------------------------------------------------------------------*/
+   PROCEDURE REMOVE_ALL_LICENCING( PvId IN NUMBER, UserId IN NUMBER ) IS
+      CURSOR licencing_cur
+      IS
+         SELECT *
+         FROM licencing
+         WHERE pv_id = PvId;
+      licencing_rec licencing_cur%ROWTYPE;
+   BEGIN
+      -- For each licence association that the PV_ID had, remove it
+      OPEN licencing_cur;
+      FETCH licencing_cur
+      INTO licencing_rec;
+      WHILE licencing_cur%FOUND
+      LOOP
+         REMOVE_LICENCE ( PvId, licencing_rec.licence, UserId );
+         FETCH licencing_cur
+         INTO licencing_rec;
+      END LOOP;
+   END;
+/*-------------------------------------------------------------------------------------------------------*/
+END PK_LICENCING;
+/

Subversion Repositories DevTools

(root)/ManagerSuite/Manager_Suite_VM/trunk/src/Release Manager.sql – Rev 55 → 57