Subversion Repositories DevTools

--  DDL for Table CQ_HISTORY

--------------------------------------------------------

  CREATE TABLE "CQ_HISTORY" 

   (	"DBID" NUMBER(10,0), 

	"ENTITY_DBID" NUMBER(10,0), 

	"ENTITYDEF_ID" NUMBER(10,0), 

	"ENTITYDEF_NAME" VARCHAR2(30 CHAR), 

	"ACTION_TIMESTAMP" DATE, 

	"USER_NAME" VARCHAR2(30 CHAR), 

	"ACTION_NAME" VARCHAR2(30 CHAR), 

	"OLD_STATE" VARCHAR2(30 CHAR), 

	"NEW_STATE" VARCHAR2(30 CHAR), 

	"EXPIRED_TIMESTAMP" DATE

   ) ;

/

--------------------------------------------------------

--------------------------------------------------------

--  DDL for Table CQ_SOFTWARE_ISSUE

--------------------------------------------------------

  CREATE TABLE "CQ_SOFTWARE_ISSUE" 

   (	"RATL_MASTERSHIP" NUMBER(10,0), 

	"DBID" NUMBER(10,0), 

	"IS_ACTIVE" NUMBER(10,0), 

	"ID" VARCHAR2(13 CHAR), 

	"STATE" NUMBER(10,0), 

	"VERSION" NUMBER(10,0), 

	"LOCK_VERSION" NUMBER(10,0), 

	"LOCKED_BY" NUMBER(10,0), 

	"IS_DUPLICATE" NUMBER(10,0), 

	"UNDUPLICATE_STATE" VARCHAR2(50 CHAR), 

	"HEADLINE" VARCHAR2(150 CHAR), 

	"DESCRIPTION" CLOB, 

	"PRIORITY" VARCHAR2(50 CHAR), 

	"SUBMITTER" NUMBER(10,0), 

	"SUBMIT_DATE" DATE, 

	"OWNER" NUMBER(10,0), 

	"APPROVER" NUMBER(10,0), 

	"NOTE_ENTRY" CLOB, 

	"NOTES_LOG" CLOB, 

	"RESOLUTION_DATE" DATE, 

	"ISSUE_TYPE" VARCHAR2(50 CHAR), 

	"PACKAGE_REF" VARCHAR2(50 CHAR), 

	"SOLUTION_APPROVED_DATE" DATE, 

	"CLOSE_DATE" DATE, 

	"BUILD_NUMBER" VARCHAR2(50 CHAR), 

	"NEW_NUM" VARCHAR2(50 CHAR), 

	"FOR_INFORMATION" CLOB, 

	"PROP_SOL_DATE" DATE, 

	"ARTIFACTS_TO_BE_UPDATED_1" CLOB, 

	"ISSUE_ID" NUMBER(10,0), 

	"SOLUTION_DATE" DATE, 

	"KPI_SOLUTION_DATE" DATE, 

	"TITLE" VARCHAR2(80 CHAR), 

	"PROJECT" VARCHAR2(50 CHAR), 

	"ORIGINATING_AREA" VARCHAR2(50 CHAR), 

	"ESTIMATED_TIME_INHOURS" NUMBER(10,0), 

	"RELEASE_PART1" NUMBER(10,0), 

	"RELEASE_PART2" NUMBER(10,0), 

	"RELEASE_PART3" NUMBER(10,0), 

	"SOLUTION_RELEASE_PART1" NUMBER(10,0), 

	"SOLUTION_RELEASE_PART2" NUMBER(10,0), 

	"SOLUTION_RELEASE_PART3" NUMBER(10,0), 

	"ORIGINAL_IMPLEMENT_DATE" DATE, 

	"ISSUE_LABEL" VARCHAR2(50 CHAR), 

	"PRODUCT" VARCHAR2(50 CHAR), 

	"ORIGIN" VARCHAR2(50 CHAR), 

	"CLONE_NOTE" CLOB, 

	"SOLUTION_LABEL" VARCHAR2(50 CHAR), 

	"REQUIRED_BY" DATE, 

	"ITERATION_AFFECTED" VARCHAR2(50 CHAR), 

	"MERGE_OLD_ID" VARCHAR2(150 CHAR), 

	"COMMENTS" CLOB, 

	"CHANGE_CONTROL" VARCHAR2(50 CHAR), 

	"MERGE_COMMENTS" CLOB, 

	"MERGE_CHANGE_CONTROL" VARCHAR2(50 CHAR), 

	"AWAITING_INFO_DESC" CLOB, 

	"REQUEST_CLOSE_DESC" CLOB, 

	"ROOT_CAUSE" VARCHAR2(50 CHAR), 

	"FLAGA" VARCHAR2(50 CHAR), 

	"FLAGB" VARCHAR2(50 CHAR), 

	"FLAGC" VARCHAR2(50 CHAR), 

	"SCHEDULED_RELEASE" VARCHAR2(60 CHAR), 

	"SEVERITY" VARCHAR2(50 CHAR), 

	"CUSTOM_FIELD_01" CLOB, 

	"CUSTOM_FIELD_02" CLOB, 

	"CUSTOM_FIELD_03" CLOB, 

	"CUSTOMER_REFERENCE" VARCHAR2(254 CHAR), 

	"TEAMLEADER" NUMBER(10,0), 

	"WIP" CLOB, 

	"RELEASEINFO" CLOB, 

	"SBOM" VARCHAR2(254 CHAR), 

	"NEW_EXTERNAL_NOTE" CLOB, 

	"EXTERNAL_NOTE_LOG" CLOB, 

	"CUSTOMER_REFERENCE_2" VARCHAR2(50 CHAR), 

	"CUSTOMER_REFERENCE_3" VARCHAR2(50 CHAR), 

	"CUSTOMER_REFERENCE_4" VARCHAR2(50 CHAR), 

	"CUSTOMER_REFERENCE_5" VARCHAR2(50 CHAR), 

	"TEST_CASES" CLOB, 

	"INTEGRATION_NOTES" CLOB, 

	"UPDATED_DESIGN_NOTES" VARCHAR2(50 CHAR), 

	"RELATED_DATABASE_PATCHES" VARCHAR2(50 CHAR), 

	"AFFECTED_PACKAGES" VARCHAR2(50 CHAR), 

	"CODEREVIEW_REFERENCE" CLOB, 

	"RISK" VARCHAR2(50 CHAR), 

	"DEPENDANT_DEVIS" VARCHAR2(50 CHAR), 

	"CUSTOM_FIELD_04" CLOB, 

	"CUSTOM_FIELD_05" CLOB, 

	"ITERATION_FOUND" VARCHAR2(50 CHAR)

   ) ;

/

--------------------------------------------------------

--  DDL for Table CQ_STATEDEF

--------------------------------------------------------

  CREATE TABLE "CQ_STATEDEF" 

   (	"ID" NUMBER(10,0), 

	"PACKAGE_OWNERSHIP" NUMBER(10,0), 

	"NAME" VARCHAR2(255 CHAR), 

	"ENTITYDEF_ID" NUMBER(10,0), 

	"ORDINAL" NUMBER(10,0)

   ) ;

/

--------------------------------------------------------

--  DDL for Table CQ_USERS

--------------------------------------------------------

  CREATE TABLE "CQ_USERS" 

   (	"DBID" VARCHAR2(8), 

	"LOGIN_NAME" VARCHAR2(17), 

	"FULLNAME" VARCHAR2(30)

   ) ;

/

  PROCEDURE ADD_RIPPLE_PACKAGE ( newPvId IN NUMBER, basePvid IN NUMBER, RtagId IN NUMBER, UserId IN NUMBER );

       FUNCTION auto_make_vcsrelease2 (

      rtagid                   IN   NUMBER,

      pkgname                  IN   VARCHAR2,

      vext                     IN   VARCHAR2,

      newpkgversion            IN   VARCHAR2,

      vcstag                   IN   VARCHAR2,

      dependenciesimportlist   IN   VARCHAR2,

      isrippled                IN   NUMBER,

      username                 IN   VARCHAR2

   )

      RETURN NUMBER;

   PROCEDURE make_release (

     pvid          IN   NUMBER,                         -- NEW  PKG pvid

     userid        IN   NUMBER                          -- User performing Release

   )

     -- Set dlocked state to mark the package a 'Released and Locked'

  --  Release a specified package into a specified release

  --

  --  Similar to 'make_release', but it attempts to handle the case where a package-version

  --  is being replaced with one with a different package-suffix, but it doesn't

  --  handle all the cases well. 

  --

  --  The current build system cannot do this automatically - yet

  --

  --

     clone_removal NUMBER := 0;

     clone_viewid  NUMBER := -1;

     clone_envtab  NUMBER := -1;

     --

     -- Determine the viewid and environment for the new package

     --

     viewid := get_package_view (pvid, rtagid);

     envtab := pk_environment.get_package_area (pvid, rtagid);

     --

     -- If changing pkgExt, then we need to delete the orig package, not just replace it

     --

     --

     --

     --

     -- Remove the package from the WIP or PENDING tab

     --     This will be handled later with a process that

     --     will do a 'replace' so that meta data is not lost

    IF clone_removal = 1

        ELSIF clone_envtab = 2

        THEN

            -- RELEASED --

            pk_release.remove_package (clonefrompvid, rtagid, userid);

     -- Set dlocked state to mark the package a 'Released and Locked'

     -- If its not specified, give it a default (7) view

        IF clone_viewid >= 0

        THEN

        ELSE

            viewid := 7;

        END IF;

PROCEDURE ADD_RIPPLE_PACKAGE ( newPvId IN NUMBER, basePvid IN NUMBER, RtagId IN NUMBER, UserId IN NUMBER ) IS

    oldPvId NUMBER;

    ReleaseLocation VARCHAR2(4000);

    IsPatch CHAR(1) := NULL;

    sLocation VARCHAR2(4000) := NULL;

    nRtagIdLocation NUMBER;

    ViewId NUMBER := -1;

/*  ADD_RIPPLE_PACKAGE is used by the auto build process to add a rippled package into the

    Pending tab as a part of the Release process.

    Done mainly to assist in error handling and build restart

*/

BEGIN

    /*--------------- Business Rules Here -------------------*/

    BEGIN

        -- Ensure that the Base package exists in the Release

        -- Determine the Base Package View Id

        select BASE_VIEW_ID into ViewId 

        from release_content rc

        where rc.pv_id = basePvid and rc.rtag_id = RtagId;

        exception

            when NO_DATA_FOUND then

                ViewId := -1;

    END; 

    SELECT proj.PROJ_NAME ||' / '|| rt.RTAG_NAME INTO ReleaseLocation

      FROM PROJECTS proj,

           RELEASE_TAGS rt

     WHERE rt.PROJ_ID = proj.PROJ_ID

       AND rt.RTAG_ID = RtagId;

    /* Core operation */

    IF ViewId != -1 THEN

        -- Add to "Pending" area

        INSERT INTO PLANNED ( RTAG_ID, PV_ID, VIEW_ID, OPERATION )

        VALUES( RtagId, newPvId, ViewId, 'R' );

        /* LOG ACTION */

        Log_Action ( newPvId, 'add_to_planned', UserId, 'Location: '|| ReleaseLocation );

    ELSE

        RAISE_APPLICATION_ERROR (-20000, 'Base version not in release '|| sLocation ||'.' );

    END IF;

END;

  -- Add or Replace a package to the specified release

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

-- Use:

--   Used by the Automated Build System to release a new package

--   Will replace auto_make_vcsrelease

-- Returns:

--    Use application errors to indicate programming errors

--    Use negative return codes to indicate correctable user problems

--    Positive return code is the new PVID of the package

--  Similar to auto_make_vcsrelease, but

--      Cleaned up and simplified

--      Does not Release the package, it only preps it for release

--      Adds Rippled Package into the Pending Tab

--

FUNCTION auto_make_vcsrelease2 (

      rtagid                   IN   NUMBER,

      pkgname                  IN   VARCHAR2,

      vext                     IN   VARCHAR2,

      newpkgversion            IN   VARCHAR2,

      vcstag                   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;

      return_migrated_pkg             NUMBER                        := -4;

      return_svn_path_changed         NUMBER                        := -5;

      x_vcstypeid                     NUMBER;

      x_tag                           VARCHAR2(32);

      x_label                         VARCHAR2(60);

      x_srcpath                       VARCHAR2(2000);

      p_srcpath                       VARCHAR2(2000);

      -- Regular expression constituents

      Token         VARCHAR2(16) := '([^:]+)';  -- a token is anything except a ':' character

      Seperator     VARCHAR2(4)  := '::';       -- tokens will be seperated by '::'

      BOL           VARCHAR2(4)  := '^';

      Anything      VARCHAR2(4)  := '.*';

      ReplaceToken1 VARCHAR2(4)  := '\1';

      ReplaceToken2 VARCHAR2(4)  := '\2';

      ReplaceToken3 VARCHAR2(4)  := '\3';

      iprev_modifier_id               NUMBER;

   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 (vcstag IS NULL)

      THEN

         raise_application_error (-20000, 'PkgVcsTag 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;

      -- Parse the PkgVcsTag

      -- Sets up

      --   x_tag, x_label, x_srcpath

       BEGIN

          -- Extract the first token - this will be the short tag name for the VCS (eg. 'CC', 'SVN', etc)

          x_tag := REGEXP_REPLACE(vcstag, BOL || Token || Seperator || Anything, ReplaceToken1);

          -- Decode the short tag

          CASE x_tag

             WHEN 'CC' THEN

                -- extract the 2nd and 3rd tokens, those being the source path and label respectively

                x_srcpath := REGEXP_REPLACE(vcstag, BOL || Token || Seperator || Token || Seperator || Anything, ReplaceToken2);

                x_label   := REGEXP_REPLACE(vcstag, BOL || Token || Seperator || Token || Seperator || Token, ReplaceToken3);

                -- NOTE: if reg-expr parsing/replacement fails, the x_... variable may receive the entire input string (vcstag), so

                -- check for that error as well as the obvious zero-length string conditions.

                IF LENGTH(x_srcpath) = 0 OR LENGTH(x_label) = 0 OR x_srcpath = vcstag OR x_label = vcstag THEN

                   raise_application_error (-20000, 'VCS Tag contains insufficient or malformed data!');

                END IF;

                IF isSVNbyName(pkgname) > 0 THEN

                    -- Don't allow migrated packages to be released from a CC label

                    return return_migrated_pkg;

                END IF;

             WHEN 'SVN' THEN

                -- extract the 2nd token, that being the subversion tag

                x_srcpath := REGEXP_REPLACE(vcstag, BOL || Token || Seperator || Token || Seperator || Anything, ReplaceToken2);

                x_label   := REGEXP_REPLACE(vcstag, BOL || Token || Seperator || Token || Seperator || Token, ReplaceToken3);

                -- NOTE: if reg-expr parsing/replacement fails, the x_... variable may receive the entire input string (vcstag), so

                -- check for that error as well as the obvious zero-length string conditions.

                IF LENGTH(x_srcpath) = 0 OR LENGTH(x_label) = 0 OR x_srcpath = vcstag OR x_label = vcstag THEN

                   raise_application_error (-20000, 'VCS Tag contains insufficient or malformed data!');

                END IF;

             WHEN 'UC' THEN

                -- nothing to extract, just assign defaults

                x_srcpath := REGEXP_REPLACE(vcstag, BOL || Token || Seperator || Token || Seperator || Anything, ReplaceToken2);

                x_label   := 'N/A';

             ELSE

                raise_application_error (-20000, 'Unrecognised VCS Tag!');

          END CASE;

       END;

      -- Get the VCS_TYPE_ID for the version control system.

      BEGIN

         SELECT vt.vcs_type_id

         INTO x_vcstypeid

         FROM VCS_TYPE vt

         WHERE vt.tag = x_tag;

         EXCEPTION

            WHEN NO_DATA_FOUND THEN

               raise_application_error (-20000, 'Database is missing a version control system tag (' || x_tag || ')!' );

      END;

      -- 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 the named version 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

            BEGIN

               -- Find package to be replaced with the ripple package

               SELECT pv.pv_id, pv.src_path

                 INTO clonefrompvid, p_srcpath

                 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|');

            EXCEPTION

               WHEN NO_DATA_FOUND

               THEN

                  raise_application_error

                                        (-20000,

                                            'Cannot get CloneFromPvId. VExt='

                                         || vext

                                         || ', RtagId='

                                         || rtagid

                                         || ', PkgName='

                                         || pkgname

                                        );

            END;

              -- Ensure Source Path does not change

              --    Ripple:   Will only change if the labeling process is messed up

              --

              IF ( x_tag = 'SVN' AND p_srcpath != x_srcpath )

              THEN

                return return_svn_path_changed;

              END IF;

            -- Create new Package Version, based on existing package

            seed_package_names_versions (pkgname,

                                         newpkgversion,

                                         userid,

                                         pvid,

                                         clonefrompvid

                                        );

             -- Insert new Version into the 'Planned' tab of the Release

             PK_PLANNED.ADD_RIPPLE_PACKAGE ( pvid, clonefrompvid , rtagid, userid );

            -- Update Package reason for release

            -- Force to be Approved for Autobuild - will control visability inn Pending Tab

            UPDATE package_versions pv

               SET pv.comments = 'Rippled Build.',

                   pv.build_type = 'Y',

                   pv.dlocked = 'A'

             WHERE pv.pv_id = pvid;

         ELSE   -- PVID != 0. 

            --

            -- Package already exists, it was built previously, and has just been re-built again probably due to the

            -- fact that the archive was cleaned of the original build artifacts at some point, and those artifacts

            -- are now needed once more. As such, we need to ensure that a new release note generation occurs so

            -- reset the package_versions[pvid].release_notes_info field to enable that to happen.

            UPDATE package_versions pv

               SET pv.release_notes_info = null,

                   pv.modified_stamp = ora_sysdatetime

             WHERE pv.pv_id = pvid;

            --DEVI-066151

            log_action (pvid, 'package_rebuilt', userid, 'Package Re-built...');

            RETURN return_package_already_exists;

         END IF;

      -- -----------------------------------------------------------------------

      --    Package is NOT a ripple

      --    Package is a planned release

      --    Package MUST exist in the Pending Area

      --

      ELSE

         -- Find package in pending area

         BEGIN

            SELECT pv.pv_id, pv.dlocked, pv.src_path

              INTO pvid, dlocked, p_srcpath

              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|')

               AND rownum = 1

             ORDER BY pv.modified_stamp;

         EXCEPTION

            WHEN NO_DATA_FOUND

            THEN

               pvid := 0;

         END;

         IF (pvid = 0)

         THEN

            -- Package does not exist in pending area, hence report it

            -- It may have been reject while were building it

            RETURN return_package_not_found;

         END IF;

         IF (dlocked != 'A')

         THEN

            -- Package is not approved for autobuild

            -- Will no longer get here, due to check in the previous select

            -- This is intentional. We need the force approved package

            RETURN return_not_approved;

         END IF;

          -- Ensure Source Path does not change

          -- Do not expect the SVN development branch to change from the previous version

          --    Planned:  Will only change if user has incorrect metadata

          --

          IF ( x_tag = 'SVN' AND p_srcpath != x_srcpath )

          THEN

            return return_svn_path_changed;

          END IF;

      END IF;

      -- ----------------------------------------------------------------------

      -- Common Processing for ripple and planned(non-ripple) builds

      --

      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.src_path = x_srcpath,

                pv.pkg_label = x_label,

                pv.vcs_type_id = x_vcstypeid

          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;

      -- DEVI-063601

      -- For planned builds the modifier_id shouldn't be set to buildadm.

      -- The auto_make_release() will set it to the 'buildadm' 

      -- Save and restore the modifier_id

      -- SELECT pv.modifier_id INTO iprev_modifier_id

      -- FROM package_versions pv WHERE pv.pv_id = pvid;

      -- Now release package

      -- pk_environment.auto_make_release (pvid,

      --                                  rtagid,

      --                                  userid,

      --                                  vext,

      --                                  ssv_ext,

      --                                  clonefrompvid

      --                                 );

      -- DEVI-063601

      -- if it is a planned build then restore the modifier_id to the previous modifier

      -- UPDATE package_versions pv SET pv.modifier_id = iprev_modifier_id

      -- WHERE pv.pv_id = pvid;