Subversion Repositories DevTools

CREATE PACKAGE BODY "RELEASE_MANAGER"."PK_RMAPI" 
IS
/*
------------------------------
||  Last Modified:  Jeremy Tweddle
||  Modified Date:  08/Feb/2008
||  Body Version:   3.3
------------------------------
*/

   /*-------------------------------------------------------------------------------------------------------*/
   FUNCTION package_dependencies (pkgname VARCHAR2, pkgversion VARCHAR2)
      RETURN typecur
   IS
      npvid     NUMBER  := 0;
      RECORDS   typecur;
   BEGIN
      BEGIN
         -- Get PV_ID --
         SELECT pv.pv_id
           INTO npvid
           FROM PACKAGES pkg, package_versions pv
          WHERE pv.pkg_id = pkg.pkg_id
            AND pkg.pkg_name = pkgname
            AND pv.pkg_version = pkgversion;
      EXCEPTION
         WHEN NO_DATA_FOUND
         THEN
            raise_application_error (-20000, 'Package Not Found!');
            --WHEN OTHERS THEN
                  -- Consider logging the error and then re-raise
            RAISE;
      END;

      -- Finally get package dependencies --
      OPEN RECORDS FOR
         SELECT dpv.pv_id, dpkg.pkg_name, dpv.pkg_version
           FROM package_dependencies dep, PACKAGES dpkg, package_versions dpv
          WHERE dep.pv_id = npvid
            AND dpv.pkg_id = dpkg.pkg_id
            AND dpv.pv_id = dep.dpv_id;

      RETURN RECORDS;
   END;

/*-------------------------------------------------------------------------------------------------------*/
   FUNCTION wip_iteration_package (
      projname        VARCHAR2,
      iterationname   VARCHAR2,
      pkgname         VARCHAR2
   )
      RETURN typecur
   IS
      nrtagid   NUMBER  := 0;
      RECORDS   typecur;
   BEGIN
      BEGIN
         -- Get latest rtag_id --
         SELECT rt.rtag_id
           INTO nrtagid
           FROM projects proj, release_tags rt
          WHERE rt.proj_id = proj.proj_id
            AND UPPER (proj.proj_name) = UPPER (projname)
            AND UPPER (rt.rtag_name) = UPPER (iterationname);

         IF (nrtagid IS NULL)
         THEN
            raise_application_error (-20000,
                                     'Work In Progress is Not Found!');
         END IF;
      EXCEPTION
         WHEN NO_DATA_FOUND
         THEN
            raise_application_error (-20000,
                                     'Work In Progress is Not Found!');
            RAISE;
      END;

      -- Finally get package dependencies --
      OPEN RECORDS FOR
         SELECT pv.pkg_version, pv.dlocked AS is_official, pv.pkg_label,
                pv.src_path
           FROM PACKAGES pkg, package_versions pv, work_in_progress wip
          WHERE pv.pkg_id = pkg.pkg_id
            AND wip.pv_id = pv.pv_id
            AND wip.rtag_id = nrtagid
            AND pkg.pkg_name = pkgname;

      RETURN RECORDS;
   END;

/*-------------------------------------------------------------------------------------------------------*/
   FUNCTION auto_make_release (
      rtagid                   IN   NUMBER,
      pkgname                  IN   VARCHAR2,
      vext                     IN   VARCHAR2,
      newpkgversion            IN   VARCHAR2,
      label                    IN   VARCHAR2,
      dependenciesimportlist   IN   VARCHAR2,
      isrippled                IN   NUMBER,
      username                 IN   VARCHAR2
   )
      RETURN NUMBER
   IS
      pkgid                           NUMBER;
      pvid                            NUMBER                        := 0;
      userid                          NUMBER;
      dlocked                         VARCHAR2 (20)                 := NULL;
      clonefrompvid                   NUMBER;
      ssv_mm                          package_versions.v_mm%TYPE;
      ssv_nmm                         package_versions.v_nmm%TYPE;
      ssv_ext                         package_versions.v_ext%TYPE;
      return_package_not_found        NUMBER                        := -1;
      return_package_already_exists   NUMBER                        := -2;
      return_not_approved             NUMBER                        := -3;
   BEGIN
      /*--------------- Business Rules Here -------------------*/
      IF (rtagid IS NULL)
      THEN
         raise_application_error (-20000, 'RtagId must be supplied.');
      END IF;

      IF (pkgname IS NULL)
      THEN
         raise_application_error (-20000, 'PkgName must be supplied.');
      END IF;

      IF (newpkgversion IS NULL)
      THEN
         raise_application_error (-20000, 'PkgVersion must be supplied.');
      END IF;

      IF (label IS NULL)
      THEN
         raise_application_error (-20000, 'Label must be supplied.');
      END IF;

      IF (isrippled IS NULL) OR (isrippled < 0) OR (isrippled > 1)
      THEN
         raise_application_error
            (-20000,
             'IsRippled must be set to 1 (Is rippled build) or 0 (Is planned build).'
            );
      END IF;

      IF (username IS NULL)
      THEN
         raise_application_error (-20000, 'UserName must be supplied.');
      END IF;

      -- Get user_id
      BEGIN
         SELECT usr.user_id
           INTO userid
           FROM users usr
          WHERE UPPER (usr.user_name) = UPPER (username)
            AND usr.is_disabled IS NULL;
      EXCEPTION
         WHEN NO_DATA_FOUND
         THEN
            raise_application_error (-20000,
                                        'UserName '
                                     || username
                                     || ' is not valid or disabled.'
                                    );
      END;

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

      -- Create package if necessary
      IF isrippled = 1
      THEN
         /* Ripple Build */
         BEGIN
            -- Make sure that package does not exist
            SELECT pv.pv_id
              INTO pvid
              FROM package_versions pv, PACKAGES pkg
             WHERE pv.pkg_id = pkg.pkg_id
               AND pkg.pkg_name = pkgname
               AND pv.pkg_version = newpkgversion;
         EXCEPTION
            WHEN NO_DATA_FOUND
            THEN
               pvid := 0;
         END;

         IF (pvid = 0)
         THEN
            -- Split current version in parts
            split_version (newpkgversion, ssv_mm, ssv_nmm, ssv_ext);

            BEGIN
               -- Find package to be replaced with thie ripple package
               IF NVL (vext, '|LINK_A_NULL|') = NVL (ssv_ext, '|LINK_A_NULL|')
               THEN
                  SELECT pv.pv_id
                    INTO clonefrompvid
                    FROM PACKAGES pkg, package_versions pv,
                         release_content rc
                   WHERE rc.pv_id = pv.pv_id
                     AND pv.pkg_id = pkg.pkg_id
                     AND rc.rtag_id = rtagid
                     AND pkg.pkg_name = pkgname
                     AND NVL (pv.v_ext, '|LINK_A_NULL|') = NVL (ssv_ext, '|LINK_A_NULL|');
               ELSE
                  SELECT pv.pv_id
                    INTO clonefrompvid
                    FROM PACKAGES pkg, package_versions pv,
                         release_content rc
                   WHERE rc.pv_id = pv.pv_id
                     AND pv.pkg_id = pkg.pkg_id
                     AND rc.rtag_id = rtagid
                     AND pkg.pkg_name = pkgname
                     AND NVL (pv.v_ext, '|LINK_A_NULL|') = NVL (vext, '|LINK_A_NULL|');
               END IF;
            EXCEPTION
               WHEN NO_DATA_FOUND
               THEN
                  raise_application_error
                                        (-20000,
                                            'Cannot get CloneFromPvId. VExt='
                                         || vext
                                         || ', RtagId='
                                         || rtagid
                                         || ', PkgName='
                                         || pkgname
                                         || ', SSV_EXT='
                                         || ssv_ext
                                        );
            END;

            IF NVL (vext, '|LINK_A_NULL|') = NVL (ssv_ext, '|LINK_A_NULL|')
            THEN
               
-- Create package
               seed_package_names_versions (pkgname,
                                            newpkgversion,
                                            userid,
                                            pvid,
                                            clonefrompvid
                                           );
            ELSE
               -- Create package
               seed_package_names_versions2 (pkgname,
                                             newpkgversion,
                                             userid,
                                             pvid,
                                             clonefrompvid
                                            );
            END IF;

            -- Update Package reason for release
            UPDATE package_versions pv
               SET pv.comments = 'Rippled Build.',
                   pv.build_type = 'Y'
             WHERE pv.pv_id = pvid;
         ELSE
            -- Package already exists, hence cannot be used for ripple build
            RETURN return_package_already_exists;
         END IF;
      ELSE
         /* Auto build from Pending area */

         -- Find package in pending area
         BEGIN
            SELECT pv.pv_id, pv.dlocked
              INTO pvid, dlocked
              FROM planned pl, package_versions pv, PACKAGES pkg
             WHERE pl.pv_id = pv.pv_id
               AND pv.pkg_id = pkg.pkg_id
               AND pl.rtag_id = rtagid
               AND pkg.pkg_name = pkgname
               AND pv.dlocked = 'A'
               AND NVL (pv.v_ext, '|LINK_A_NULL|') = NVL (vext, '|LINK_A_NULL|');
         EXCEPTION
            WHEN NO_DATA_FOUND
            THEN
               pvid := 0;
         END;

         IF (pvid = 0)
         THEN
            -- Package does not exist in pending area, hence report it
            RETURN return_package_not_found;
         ELSIF (dlocked != 'A')
         THEN
            -- Package is not approved for autobuild
            RETURN return_not_approved;
         END IF;
      END IF;

      BEGIN
         -- Import Dependencies
         import_dependencies (pvid, dependenciesimportlist, userid);
      END;

      BEGIN
         -- Split current version in parts
         split_version (newpkgversion, ssv_mm, ssv_nmm, ssv_ext);

         -- Update Package Details
         UPDATE package_versions pv
            SET pv.pkg_version = newpkgversion,
                pv.v_ext = ssv_ext,
                pv.v_mm = ssv_mm,
                pv.v_nmm = ssv_nmm,
                pv.pkg_label = label
          WHERE pv.pv_id = pvid;
      EXCEPTION
         WHEN DUP_VAL_ON_INDEX
         THEN
            -- Package already exists, hence cannot be used for ripple build
            RETURN return_package_already_exists;
      END;

      -- Update the is_autobuildable
      UPDATE package_versions
         SET is_autobuildable = 'Y'
       WHERE pv_id = pvid;

      -- Now release package
      pk_environment.auto_make_release (pvid,
                                        rtagid,
                                        userid,
                                        vext,
                                        ssv_ext,
                                        clonefrompvid
                                       );

      --Now clean the PLANNED_VERSIONS table
      SELECT pkg_id
        INTO pkgid
        FROM PACKAGES
       WHERE pkg_name = pkgname;

      DELETE FROM planned_versions
            WHERE pkg_id = pkgid AND pkg_version = newpkgversion;

      RETURN pvid;
   END;

/*-------------------------------------------------------------------------------------------------------*/
   PROCEDURE import_dependencies (
      pvid                     IN   NUMBER,
      dependenciesimportlist   IN   VARCHAR2,
      userid                   IN   NUMBER
   )
   IS
/*
|| DependenciesImportList Format:
|| "pkgA","1.0.0";"pkgB","2.0.0";
||  OR 'pkgA','1.0.0';'pkgB','2.0.0';
*/
      TYPE tdictionary IS TABLE OF VARCHAR2 (4000)
         INDEX BY VARCHAR2 (4000);

      seperator         VARCHAR2 (2)           := '||';
      pkgname           VARCHAR2 (4000);
      pkgversion        VARCHAR2 (4000);
      buildtype         VARCHAR2 (50);
      pkgid             NUMBER;
      vext              VARCHAR2 (4000);
      dpvid             NUMBER;
      slist             VARCHAR2 (4000);
      cbuildtypes       tdictionary;
      dependencyrow     NUMBER;
      sdependency       VARCHAR2 (4000);
      first_pos         VARCHAR2 (4000);
      second_pos        VARCHAR2 (4000);
      third_pos         VARCHAR2 (4000);
      forth_pos         VARCHAR2 (4000);
      citemcollection   relmgr_varchar2_tab_t  := relmgr_varchar2_tab_t ();

      CURSOR curbuildtype
      IS
         SELECT dpv.pkg_id || seperator || dpv.v_ext AS pkgid_ext,
                dep.build_type
           FROM package_dependencies dep, package_versions dpv
          WHERE dep.pv_id = pvid AND dep.dpv_id = dpv.pv_id;

      recbuildtype      curbuildtype%ROWTYPE;
   BEGIN
      slist := dependenciesimportlist;

      -- Preformat String
      IF NOT slist IS NULL
      THEN
         slist := REPLACE (slist, ' ');                      -- Remove spaces
         slist := REPLACE (slist, UTL_TCP.crlf);
         -- Remove new line and carriage-return characters
         slist := REPLACE (slist, '''', '"');             -- Replace ' with "
      END IF;

      -- Get Current Dependencies
      OPEN curbuildtype;

      FETCH curbuildtype
       INTO recbuildtype;

      WHILE curbuildtype%FOUND
      LOOP
         cbuildtypes (recbuildtype.pkgid_ext) := recbuildtype.build_type;

         FETCH curbuildtype
          INTO recbuildtype;
      END LOOP;

      CLOSE curbuildtype;

      -- Separate dependencies with ; separator
      citemcollection := in_list_varchar2 (slist, ';');

      BEGIN
         -- Remove old dependencies
         DELETE FROM package_dependencies dep
               WHERE dep.pv_id = pvid;

         -- Loop through dependencies
         FOR dependencyrow IN 1 .. citemcollection.COUNT
         LOOP
            -- Extract pkg_name and pkg_version
            sdependency := citemcollection (dependencyrow);
            first_pos := INSTR (sdependency, '"', 1, 1);
            second_pos := INSTR (sdependency, '"', 1, 2);
            third_pos := INSTR (sdependency, '"', 1, 3);
            forth_pos := INSTR (sdependency, '"', 1, 4);
            pkgname :=
               SUBSTR (sdependency,
                       (first_pos + 1),
                       (second_pos - first_pos - 1)
                      );
            pkgversion :=
               SUBSTR (sdependency,
                       (third_pos + 1),
                       (forth_pos - third_pos - 1)
                      );

            -- Dependency must exits to be linked against
            BEGIN
               SELECT pv.pv_id, pv.pkg_id, pv.v_ext
                 INTO dpvid, pkgid, vext
                 FROM package_versions pv, PACKAGES pkg
                WHERE pv.pkg_id = pkg.pkg_id
                  AND pkg.pkg_name = pkgname
                  AND pv.pkg_version = pkgversion;
            EXCEPTION
               WHEN NO_DATA_FOUND
               THEN
                  raise_application_error
                                  (-20000,
                                      'Dependency ['
                                   || pkgname
                                   || ' '
                                   || pkgversion
                                   || '] does not exist yet and cannot be used!'
                                  );
            END;

            -- Get Build Type (i.e. BuildPackageArchive or LinkPackageArchive)
            BEGIN
               buildtype := cbuildtypes (pkgid || seperator || vext);

               IF buildtype IS NULL
               THEN
                  -- Set build type to LinkPackageArchive by default
                  buildtype := 'L';
               END IF;
            EXCEPTION
               WHEN NO_DATA_FOUND
               THEN
                  buildtype := 'L';
            END;

            -- Insert Dependencies
            
update_package_dependency (pvid,
                                       pkgname,
                                       pkgversion,
                                       buildtype,
                                       userid,
                                       0
                                      );
         END LOOP;
      END;
   END;

/*-------------------------------------------------------------------------------------------------------*/
   FUNCTION return_last_package_version (pkgname IN VARCHAR2, rtagid IN NUMBER)
      RETURN VARCHAR2
   IS
      pkgversion   VARCHAR2 (4000);
   BEGIN
      BEGIN
         SELECT pv.pkg_version
           INTO pkgversion
           FROM PACKAGES pkg, release_content rc, package_versions pv
          WHERE pv.pv_id = rc.pv_id
            AND pkg.pkg_id = pv.pkg_id
            AND pkg.pkg_name = pkgname
            AND rc.rtag_id = rtagid;

         RETURN pkgversion;
      END;
   END;

/*-------------------------------------------------------------------------------------------------------*/
   FUNCTION return_wip (pkgname IN VARCHAR2, rtagid IN NUMBER)
      RETURN VARCHAR2
   IS
      pkgversion   VARCHAR2 (4000);
   BEGIN
      BEGIN
         SELECT pv.pkg_version
           INTO pkgversion
           FROM PACKAGES pkg, work_in_progress wip, package_versions pv
          WHERE pv.pv_id = wip.pv_id
            AND pkg.pkg_id = pv.pkg_id
            AND pkg.pkg_name = pkgname
            AND wip.rtag_id = rtagid;

         IF pkgversion IS NULL
         THEN
            SELECT pv.pkg_version
              INTO pkgversion
              FROM PACKAGES pkg, planned pl, package_versions pv
             WHERE pv.pv_id = pl.pv_id
               AND pkg.pkg_id = pv.pkg_id
               AND pkg.pkg_name = pkgname
               AND pl.rtag_id = rtagid;
         END IF;

         RETURN pkgversion;
      END;
   END;

/*-------------------------------------------------------------------------------------------------------*/
   PROCEDURE update_dash_board (rtagid IN NUMBER)
   IS
      projid       NUMBER;
      projiddb     NUMBER;
      total        NUMBER;
      auto_total   NUMBER;
      rtagiddb     NUMBER;
   BEGIN
      SELECT COUNT (*)
        INTO total
        FROM release_content rc, package_versions pv
       WHERE pv.pv_id = rc.pv_id AND rc.rtag_id = rtagid;

      SELECT COUNT (*)
        INTO auto_total
        FROM release_content rc, package_versions pv
       WHERE pv.pv_id = rc.pv_id
         AND pv.is_autobuildable = 'Y'
         AND rc.rtag_id = rtagid;

      BEGIN
         SELECT rtag_id
           INTO rtagiddb
           FROM dash_board
          WHERE rtag_id = rtagid;

         SELECT proj_id
           INTO projiddb
           FROM dash_board
          WHERE rtag_id = rtagid;
      EXCEPTION
         WHEN NO_DATA_FOUND
         THEN
            rtagiddb := '';
      END;

      IF rtagiddb IS NULL
      THEN
         SELECT proj_id
           INTO projid
           FROM release_tags
          WHERE rtag_id = rtagid;

         INSERT INTO dash_board
                     (proj_id, rtag_id, last_build_time, automated_packages,
                      total_packages
                     )
              VALUES (projid, rtagid, ora_sysdatetime, auto_total,
                      total
                     );
      ELSE
         UPDATE dash_board
            SET last_build_time = ora_sysdatetime,
                automated_packages = auto_total,
                total_packages = total
          WHERE proj_id = projiddb AND rtag_id = rtagiddb;
      END IF;
   END;

/*-------------------------------------------------------------------------------------------------------*/
   FUNCTION exclude_from_build (
      pvid          IN   NUMBER,
      spkgversion   IN   VARCHAR2,
      rtagid        IN   NUMBER,
      username      IN   VARCHAR2
   )
      RETURN NUMBER
   IS
      userid              NUMBER;
      outerrcode          NUMBER;
      pkgid               NUMBER;

      CURSOR dnr_duplicate_cur
      IS
         SELECT *
           FROM do_not_ripple
          WHERE pv_id = pvid AND rtag_id = rtagid;

      dnr_duplicate_rec   dnr_duplicate_cur%ROWTYPE;
   BEGIN
      outerrcode := -1;       -- Set default return error code to ERROR state

      /*--------------- Business Rules Here -------------------*/
      IF (rtagid IS NULL)
      THEN
         RETURN outerrcode;
--         raise_application_error (-20000, 'RtagId must be supplied.');
      END IF;

      IF (pvid IS NULL)
      THEN
         RETURN outerrcode;
--         raise_application_error (-20000, 'PvId must be supplied.');
      END IF;

      IF (username IS NULL)
      THEN
         RETURN outerrcode;
--         raise_application_error (-20000, 'UserName must be supplied.');
      END IF;

      -- Get user_id
      BEGIN
         SELECT usr.user_id
           INTO userid
           FROM users usr
          WHERE UPPER (usr.user_name) = UPPER (username)
            AND usr.is_disabled IS NULL;
      EXCEPTION
         WHEN NO_DATA_FOUND
         THEN
            RETURN outerrcode;
--            raise_application_error (-20000,
--                                        'UserName '
--                                     || username
--                                     || ' is not valid or disabled.'
--                                    );
      END;

      OPEN dnr_duplicate_cur;

      FETCH dnr_duplicate_cur
       INTO dnr_duplicate_rec;

      IF dnr_duplicate_cur%FOUND
      THEN
         outerrcode := 0;
      END IF;

      IF dnr_duplicate_cur%NOTFOUND
      THEN
         /* No duplicate recordset */
         unripple_package (pvid, rtagid, userid);
         outerrcode := 0;                            -- Set return to SUCCESS
      END IF;

      CLOSE dnr_duplicate_cur;

      SELECT pkg_id
        INTO pkgid
        FROM package_versions
       WHERE pv_id = pvid;

      DELETE FROM planned_versions
            WHERE pkg_id = pkgid AND pkg_version = spkgversion;

      RETURN outerrcode;
   END;

/*-------------------------------------------------------------------------------------------------------*/
   PROCEDURE insert_abt_actionlog (rconid IN NUMBER, action IN VARCHAR2)
   IS
   BEGIN
      INSERT INTO abt_action_log
                  (rcon_id, action_datetime, action
                  )
           VALUES (rconid, ora_sysdatetime, action
                  );
   END;

/*-------------------------------------------------------------------------------------------------------*/
   FUNCTION insert_package_metrics (
      rtagid                   IN   NUMBER,
      pkgname                  IN   VARCHAR2,
      vext                     IN   VARCHAR2,
      metricstring             IN   VARCHAR2
   )
      RETURN NUMBER
   IS
      citemcollection          relmgr_varchar2_tab_t  := relmgr_varchar2_tab_t ();
      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;
      branchlist               VARCHAR2(4000);
      codefiles                NUMBER;
      ignoredfiles             NUMBER;
      directories              NUMBER;
      directorydepth           NUMBER;
      totalfiles               NUMBER;
      makefiles                NUMBER;
      blanklines               NUMBER;
      codelines                NUMBER;
      commentlines             NUMBER;

   BEGIN
      /*--------------- Business Rules Here -------------------*/
      IF (rtagid IS NULL)
      THEN
         RETURN return_insert_error;
      END IF;

      IF (pkgname IS NULL)
      THEN
         RETURN return_insert_error;
      END IF;

      IF (metricstring IS NULL)
      THEN
         RETURN return_insert_error;
      END IF;

      BEGIN
         SELECT pv.pv_id
           INTO 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;
      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;
         ELSIF metricname = 'ccbranch.list' THEN branchlist := metricvalue;
         ELSIF metricname = 'code.files' THEN codefiles := metricvalue;
         ELSIF metricname = 'code.ignored' THEN ignoredfiles := metricvalue;
         ELSIF metricname = 'count.dir' THEN directories := metricvalue;
         ELSIF metricname = 'count.dirdepth' THEN directorydepth := metricvalue;
         ELSIF metricname = 'count.file' THEN totalfiles := metricvalue;
         ELSIF metricname = 'count.makefile' THEN makefiles := metricvalue;
         ELSIF metricname = 'lines.blank' THEN blanklines := metricvalue;
         ELSIF metricname = 'lines.code' THEN codelines := metricvalue;
         ELSIF metricname = 'lines.comment' THEN commentlines := metricvalue;
         END IF;
      END LOOP;

      IF (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;
         
         -- 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,
                      totalfiles, makefiles, blanklines, codelines, 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;
      autobuilt           NUMBER;
      linesofcode         NUMBER;
      unittested          NUMBER;
      autotested          NUMBER;
      numOfbranches       NUMBER;
      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
           FROM release_content rc,
                package_versions pv,
                (
                 SELECT rc.pv_id
                   FROM release_content rc,
                        package_versions pv
                  WHERE pv.is_autobuildable = 'Y'
                    AND pv.pv_id = rc.pv_id
                    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
           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),
                COUNT(DISTINCT autotest_qry.pv_id)
           INTO unittested, autotested
           FROM unit_tests ut,
                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;
         
         -- Count the number of unique branches in the packages in this release.
--         SELECT COUNT(DISTINCT branch) INTO 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
--                  FROM (
--                       SELECT pm.pv_id,
--                              ','||pm.branch_list str
--                         FROM package_metrics pm,
--                              release_content rc
--                        WHERE pm.pv_id = rc.pv_id
--                          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,
                rm.autobuilt = autobuilt,
                rm.lines_of_code = linesofcode,
                rm.unit_tested = unittested,
                rm.autotested = autotested,
--                rm.branches = numOfbranches,
                rm.last_build_time = 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,
                         autotested, 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,
--                         autotested, numOfbranches, lastbuildtime
--                        );
--         END IF;
--      ELSE
--         raise_application_error (-20000, 'RtagId must be supplied.');
      END IF;
   END;
/*-------------------------------------------------------------------------------------------------------*/
END pk_rmapi; 
/
ALTER PACKAGE "RELEASE_MANAGER"."PK_RMAPI" 
  COMPILE BODY 
    PLSQL_OPTIMIZE_LEVEL=  2
    PLSQL_CODE_TYPE=  INTERPRETED
    PLSQL_DEBUG=  TRUE
 REUSE SETTINGS TIMESTAMP '2008-05-09 14:47:11'
/