WebSVN – DevTools – /ManagerSuite/Release_Manager_Schema/trunk/package/package_body/pk_release_test.sql

CREATE PACKAGE BODY "RELEASE_MANAGER"."PK_RELEASE_TEST" IS


/*
------------------------------
||  Last Modified:  S.Vukovic
||  Modified Date:  12/Sep/2005
||  Body Version:   3.0
------------------------------
*/


/*-------------------------------------------------------------------------------------------------------*/
PROCEDURE New_Release ( sReleaseName IN VARCHAR2, sReleaseComments IN VARCHAR2, nProjId IN NUMBER, nSourceRtagId IN NUMBER, sIsBranched IN CHAR, nUserId IN NUMBER ) IS

RtagId NUMBER;
ParentRtagId NUMBER;
RecCount NUMBER := 0;

                                                                                         
BEGIN
        /*--------------- Business Rules Here -------------------*/
        -- Check for missing parameters
        IF (sReleaseName IS NULL) OR (nUserId IS NULL)
        THEN
                RAISE_APPLICATION_ERROR (-20000, 'Missing some parameters! sReleaseName= '|| sReleaseName ||', nUserId='|| nUserId );
                
        END IF;
        
        
        -- Check for duplicate Release Names
        SELECT COUNT(rt.RTAG_ID) INTO RecCount
          FROM RELEASE_TAGS rt
         WHERE UPPER( rt.RTAG_NAME ) = UPPER( sReleaseName )
           AND rt.PROJ_ID = nProjId;
           
        IF (RecCount > 0) THEN
                RAISE_APPLICATION_ERROR (-20000, 'Release Name '|| sReleaseName ||' is Already Used in this Project.'); 
        END IF;
        /*-------------------------------------------------------*/
        
        
        -- Get rtag_id
        SELECT SEQ_RTAG_ID.NEXTVAL INTO RtagId FROM DUAL;
        
        
        
        -- Get Parent RtagId
        ParentRtagId := GET_PARENT_RTAG ( RtagId, nSourceRtagId, sIsBranched );
        
        
        -- Create new release
        INSERT INTO RELEASE_TAGS ( RTAG_ID, RTAG_NAME, DESCRIPTION, CREATED_STAMP, CREATOR_ID, 
                                                           OFFICIAL, REBUILD_ENV, REBUILD_STAMP, 
                                                           PARENT_RTAG_ID, PROJ_ID )
        VALUES ( RtagId, 
                         sReleaseName, 
                         sReleaseComments,
                         ORA_SYSDATE,
                         nUserId,
                         'N',
                         'N',
                         0,
                         ParentRtagId,
                         nProjId );
        
        -- Update display Order
        UPDATE_DISPLAY_ORDER ( nProjId );
        
        /* Log Project Action */
        Log_Project_Action ( nProjId, 'new_release', nUserId, sReleaseName, RtagId );
        
        
        -- Import Release Contents
        IF (NOT nSourceRtagId IS NULL) THEN
                Import_Release_Contents ( nSourceRtagId, RtagId, nUserId );
                
        END IF;
        
                
                        
END;
/*-------------------------------------------------------------------------------------------------------*/
PROCEDURE UPDATE_DISPLAY_ORDER ( nProjId IN NUMBER ) IS

                                                                                         
BEGIN
        
        
        /*--------------- Business Rules Here -------------------*/
        /*-------------------------------------------------------*/
        
        UPDATE RELEASE_TAGS urt SET
        urt.DISPLAY_ORDER = (
                                                SELECT qry.REC_NUM
                                                  FROM (        
                                                                SELECT rel.*, ROWNUM AS REC_NUM
                                                                  FROM (
                                                                  
                                                                                SELECT rt.RTAG_ID, 
                                                                                           rt.DISPLAY_ORDER,
                                                                                       DECODE( rt.PARENT_RTAG_ID, 
                                                                                                           rt.RTAG_ID, 0, rt.PARENT_RTAG_ID ) AS PARENT_RTAG_ID
                                                                                  FROM RELEASE_TAGS rt
                                                                                 WHERE rt.PROJ_ID = nProjId 
                                                                         
                                                                                ) rel
                                                                START WITH rel.PARENT_RTAG_ID = 0
                                                                CONNECT BY PRIOR rel.RTAG_ID = rel.PARENT_RTAG_ID   
                                                                ORDER SIBLINGS BY rel.DISPLAY_ORDER
                                                                ) qry
                                              WHERE qry.RTAG_ID = urt.RTAG_ID                                                           
                                                )
        WHERE urt.PROJ_ID = nProjId;
                        
END;
/*-------------------------------------------------------------------------------------------------------*/
PROCEDURE MOVE_RELEASE ( sRtagIdList IN VARCHAR2, nProjId IN NUMBER, nUserId IN NUMBER  ) IS

        nIdCollector RELMGR_NUMBER_TAB_t := RELMGR_NUMBER_TAB_t();                                               
        SeqNum NUMBER;
                                                                                                 
BEGIN
        
        
        /*--------------- Business Rules Here -------------------*/
        /*-------------------------------------------------------*/
        
        nIdCollector := IN_LIST_NUMBER ( sRtagIdList );
        
        -- Set start sequence number
        SeqNum := 1;
        
        FOR i IN 1..nIdCollector.COUNT
        LOOP
        
                -- Update new display order
                UPDATE RELEASE_TAGS rt SET
                        rt.DISPLAY_ORDER = SeqNum
                 WHERE rt.PROJ_ID = nProjId
                   AND rt.RTAG_ID = nIdCollector(i);
                   
                SeqNum := SeqNum + 1;
                           
        END LOOP;
                        
END;
/*-------------------------------------------------------------------------------------------------------*/
PROCEDURE Update_Release ( nRtagId IN NUMBER, sReleaseName IN VARCHAR2, sReleaseComments IN VARCHAR2, nParentRtagId IN NUMBER, nMASSRtagId IN NUMBER, nConfigSpecBranch IN VARCHAR2, nUserId IN NUMBER ) IS

RecCount NUMBER := 0;
ProjId NUMBER;
                                                                                         
BEGIN
        
        
        /*--------------- Business Rules Here -------------------*/
        -- Check for missing parameters
        IF (sReleaseName IS NULL) OR (nUserId IS NULL) OR (nRtagId IS NULL)
        THEN
                RAISE_APPLICATION_ERROR (-20000, 'Missing some parameters! sReleaseName= '|| sReleaseName ||', nUserId='|| nUserId ||', nRtagId='|| nRtagId);
                
        END IF;
        
        
        -- Check for duplicate Release Names
        SELECT rt.PROJ_ID INTO ProjId
          FROM RELEASE_TAGS rt
         WHERE rt.RTAG_ID = nRtagId;
         
        SELECT COUNT(rt.RTAG_ID) INTO RecCount
          FROM RELEASE_TAGS rt
         WHERE UPPER( rt.RTAG_NAME ) = UPPER( sReleaseName )
           AND rt.RTAG_ID != nRtagId
           AND rt.PROJ_ID = ProjId;
           
           
        IF (RecCount > 0) THEN
                RAISE_APPLICATION_ERROR (-20000, 'Release Name '|| sReleaseName ||' is Already Used in this Project.'); 
        END IF;
        /*-------------------------------------------------------*/
        
        
        
        -- Update release details
        UPDATE RELEASE_TAGS rt SET
        rt.RTAG_NAME = sReleaseName,
        rt.DESCRIPTION = sReleaseComments,
        rt.PARENT_RTAG_ID = nParentRtagId,
        rt.ASSOC_MASS_REF = nMASSRtagId,
        rt.CONFIG_SPEC_BRANCH = nConfigSpecBranch
        WHERE rt.RTAG_ID = nRtagId; 
        
        -- Update display Order
        UPDATE_DISPLAY_ORDER ( ProjId );
        
        /* Log Project Action */
        Log_Project_Action ( ProjId, 'update_release', nUserId, 'Release Name: <br>Release Comments:', nRtagId );
        
        
                
                        
END;
/*-------------------------------------------------------------------------------------------------------*/
PROCEDURE Destroy_Release ( nRtagId IN NUMBER, nUserId IN NUMBER ) IS

        RecCount NUMBER := 0;
        sReleaseId VARCHAR2(4000);
        ProjId NUMBER;
        
BEGIN
        /*--------------- Business Rules Here -------------------*/
        IF (nRtagId IS NULL) 
        THEN
                RAISE_APPLICATION_ERROR (-20000, 'Missing some parameters! nRtagId= '|| nRtagId  );
        END IF;
        
        
        
        -- Check for any kids
        SELECT COUNT( rt.RTAG_ID ) INTO RecCount
          FROM RELEASE_TAGS rt
         WHERE rt.PARENT_RTAG_ID = nRtagId
           AND rt.RTAG_ID != rt.PARENT_RTAG_ID;
        
        IF (RecCount > 0) THEN
                RAISE_APPLICATION_ERROR (-20000, 'Cannot destroy this release. You need to destroy its children releases first.'  );
        END IF; 
        /*-------------------------------------------------------*/
        
        -- Clear the release contents
        Clean_Release_Contents ( nRtagId, nUserId );
        
        /* Log Project Action */
        SELECT rt.PROJ_ID, rt.RTAG_NAME ||'['|| rt.RTAG_VERSION ||'.'|| rt.RTAG_LIFE_CYCLE ||']' INTO ProjId, sReleaseId
          FROM RELEASE_TAGS rt
         WHERE rt.RTAG_ID = nRtagId;
         
        Log_Project_Action ( ProjId, 'destroy_release', nUserId, sReleaseId, nRtagId );
        
        
        -- Remove Release
        DELETE 
          FROM RELEASE_TAGS rt
         WHERE rt.RTAG_ID = nRtagId;
         
                
                        
END;
/*-------------------------------------------------------------------------------------------------------*/
PROCEDURE Import_Release_Contents ( nSourceRtagId IN NUMBER, nTargetRtagId IN NUMBER, nUserId IN NUMBER  ) IS

        sSourceLocation VARCHAR2(4000);
        sTargetLocation VARCHAR2(4000);
        ProjId NUMBER;
        cReleaseMode CHAR(1);
        
        
BEGIN
        /*--------------- Business Rules Here -------------------*/
        IF (nSourceRtagId IS NULL) OR (nTargetRtagId IS NULL)
        THEN
                RAISE_APPLICATION_ERROR (-20000, 'Missing some parameters! nSourceRtagId= '|| nSourceRtagId ||', nTargetRtagId='|| nTargetRtagId );
        END IF;
        
        IF (nSourceRtagId = nTargetRtagId) THEN
                RAISE_APPLICATION_ERROR (-20000, 'Cannot import release contents from same release.' );
        END IF;
        
        
        -- Check if Target release is in OPEN mode
        SELECT rt.OFFICIAL  INTO  cReleaseMode
          FROM RELEASE_TAGS rt
         WHERE rt.RTAG_ID = nTargetRtagId;

        IF (cReleaseMode != 'N') THEN
                RAISE_APPLICATION_ERROR (-20000, 'Release must be in OPEN mode to import the contents.' );
        END IF;  
         
        /*-------------------------------------------------------*/
        
        -- Clean target contents
        Clean_Release_Contents ( nTargetRtagId, nUserId );
        
        
        -- Import Released Area Contents
        INSERT INTO RELEASE_CONTENT ( RTAG_ID, PV_ID, BASE_VIEW_ID, INSERT_STAMP, INSERTOR_ID, 
PKG_STATE ) 
        SELECT nTargetRtagId AS RTAG_ID, 
                   rc.PV_ID, 
                   rc.BASE_VIEW_ID, 
                   rc.INSERT_STAMP, 
                   rc.INSERTOR_ID, 
                   rc.PKG_STATE
          FROM RELEASE_CONTENT rc
         WHERE rc.RTAG_ID = nSourceRtagId;
         
         
        -- Import Ignore Warning states
        INSERT INTO IGNORE_WARNINGS ( RTAG_ID, PV_ID, DPV_ID )
        SELECT nTargetRtagId AS RTAG_ID,
               igw.PV_ID,
               igw.DPV_ID
          FROM IGNORE_WARNINGS igw
         WHERE igw.rtag_id = nSourceRtagId;
         

        /* Log Project Action */
        -- Get Source Location
        SELECT pr.PROJ_NAME ||' > '|| rt.RTAG_NAME ||' ['|| rt.RTAG_VERSION ||'.'|| rt.RTAG_LIFE_CYCLE ||']'  INTO sSourceLocation
          FROM RELEASE_TAGS rt,
                   PROJECTS pr
         WHERE rt.PROJ_ID = pr.PROJ_ID
           AND rt.RTAG_ID = nSourceRtagId;
           
        -- Get Target Location
        SELECT pr.PROJ_NAME ||' > '|| rt.RTAG_NAME ||' ['|| rt.RTAG_VERSION ||'.'|| rt.RTAG_LIFE_CYCLE ||']'  INTO sTargetLocation
          FROM RELEASE_TAGS rt,
                   PROJECTS pr
         WHERE rt.PROJ_ID = pr.PROJ_ID
           AND rt.RTAG_ID = nTargetRtagId;         
           
        -- Get project id   
        SELECT rt.PROJ_ID  INTO  ProjId
          FROM RELEASE_TAGS rt
         WHERE rt.RTAG_ID = nTargetRtagId;   
         
        Log_Project_Action ( ProjId, 'import_release_contents', nUserId, 'SOURCE: '|| sSourceLocation ||'   TARGET: '|| sTargetLocation, nTargetRtagId );       
                
                        
END;
/*-------------------------------------------------------------------------------------------------------*/
PROCEDURE Clean_Release_Contents ( nRtagId IN NUMBER, nUserId IN NUMBER ) IS

        RecCount NUMBER;
        
BEGIN
        /*--------------- Business Rules Here -------------------*/
        IF (nRtagId IS NULL) 
        THEN
                RAISE_APPLICATION_ERROR (-20000, 'Missing some parameters! nRtagId= '|| nRtagId  );
        END IF;
        
        
        -- Check if other release references this release
        SELECT COUNT( rl.RTAG_ID ) INTO RecCount
          FROM RELEASE_LINKS rl
         WHERE rl.REF_RTAG_ID = nRtagId;
        
        IF (RecCount > 0) THEN
                RAISE_APPLICATION_ERROR (-20000, 'This release is referenced by other release and cannot be destroyed.'  );
        END IF;         
        /*-------------------------------------------------------*/
        
        -- Delete Release links
        DELETE 
          FROM RELEASE_LINKS rl
         WHERE rl.RTAG_ID = nRtagId;
        
        -- Delete ignore warning
        DELETE 
          FROM IGNORE_WARNINGS iw
         WHERE iw.RTAG_ID = nRtagId;
         
        
        -- Delete Build Order cached calculations         
        DELETE
          FROM BUILD_ORDER bo
         WHERE bo.RTAG_ID = nRtagId;
         
         
        -- Delete Notification History
        DELETE
          FROM NOTIFICATION_HISTORY nh
         WHERE nh.RTAG_ID = nRtagId;
         
         
        -- Delete Released Area Contents
        DELETE
          FROM RELEASE_CONTENT rc
         WHERE rc.RTAG_ID = nRtagId;
         

        -- Delete Work In Progress Area Contents
        DELETE
          FROM WORK_IN_PROGRESS wip
         WHERE wip.RTAG_ID = nRtagId;
         

        -- Delete Pending Area Contents
        DELETE 
          FROM PLANNED pl
         WHERE pl.RTAG_ID = nRtagId;
                
                        
END;
/*-------------------------------------------------------------------------------------------------------*/
FUNCTION GET_PARENT_RTAG ( nRtagId IN NUMBER, nSourceRtagId IN NUMBER, cIsBranch IN CHAR )  RETURN NUMBER IS
                                                        
        nSourceParentRtagId NUMBER;
                                                        
BEGIN
        /*--------------- Business Rules Here -------------------*/
        IF (nRtagId IS NULL) OR (cIsBranch IS NULL)  
        THEN
                RAISE_APPLICATION_ERROR (-20000, 'Missing some parameters! nRtagId= '|| nRtagId ||', cIsBranch= '|| cIsBranch );
        END IF;
        /*-------------------------------------------------------*/
        
        
        IF ( nSourceRtagId IS NULL ) THEN
                -- Create new on main branch
                RETURN nRtagId;
                
        ELSE
                -- Create from source rtag_id
                
                -- Find parent of Source RtagId
                SELECT rt.PARENT_RTAG_ID INTO nSourceParentRtagId
                  FROM RELEASE_TAGS rt
                 WHERE rt.RTAG_ID = nSourceRtagId;
                 
                IF (UPPER(cIsBranch) = 'Y') THEN
                        RETURN nSourceRtagId;
                ELSE
                        IF (nSourceRtagId = nSourceParentRtagId) THEN
                                RETURN nRtagId;
                        ELSE
                                RETURN nSourceParentRtagId;
                        END IF;
                END IF; 
                
        
        END IF;
        
                
END;
/*-------------------------------------------------------------------------------------------------------*/

-- PROCEDURE Get_Next_Version ( nSourceRtagId IN NUMBER,
--                                                       sNextVersion OUT VARCHAR2,
--                                                       nNextLifeCycle OUT NUMBER,
--                                                       nParentRtagId OUT NUMBER,
--                                                       nProjId IN NUMBER ) IS
--                                                      
-- SourceBranchCount NUMBER;
-- ProjRootVersion VARCHAR2(4000);
-- LastLifeCycle NUMBER;
-- ROOT_VERSION_NOT_FOUND NUMBER := -1;
-- SET_PARENT_AS_YOURSELF NUMBER := -1;
-- 
-- SourceProjId NUMBER; 
-- SourceVersion RELEASE_TAGS.RTAG_VERSION%TYPE;
-- SourceLifeCycle RELEASE_TAGS.RTAG_LIFE_CYCLE%TYPE;
-- 
-- BEGIN
--      /*--------------- Business Rules Here -------------------*/
--      /*-------------------------------------------------------*/
--      
--         
--      BEGIN
--              -- Get Project root version
--              SELECT rt.RTAG_VERSION INTO ProjRootVersion
--                FROM RELEASE_TAGS rt
--               WHERE rt.PROJ_ID = nProjId
--                 AND rt.RTAG_ID = rt.PARENT_RTAG_ID;
--         
--              EXCEPTION
--              WHEN NO_DATA_FOUND THEN
--              ProjRootVersion := ROOT_VERSION_NOT_FOUND;
--      END;               
--      
--      
--      
--       
--      BEGIN
--              -- Get info for source rtagid
--              SELECT rt.PROJ_ID, rt.RTAG_VERSION, rt.RTAG_LIFE_CYCLE  INTO  SourceProjId, SourceVersion, SourceLifeCycle
--                FROM RELEASE_TAGS rt
--               WHERE rt.RTAG_ID = nSourceRtagId;      
--         
--              EXCEPTION
--              WHEN NO_DATA_FOUND THEN
--              SourceProjId := NULL;
--              SourceVersion := NULL;
--              SourceLifeCycle := NULL;
--      END;      
--      
--      
--      IF (nSourceRtagId IS NULL)  OR  ( SourceProjId != nProjId ) THEN
--      
--              /* Blank Release Required  OR  Release imported from other project */
--              
--              IF (ProjRootVersion = ROOT_VERSION_NOT_FOUND) THEN
--                      /* No Releases found in the project, hence calculate next version available.  */
--                      
--                      -- Set parent rtag
--                      nParentRtagId := SET_PARENT_AS_YOURSELF;
--                      
--                      
--                      BEGIN
--                              -- Get Next Available Global Root Version 
--                              SELECT MAX( TO_NUMBER( rt.RTAG_VERSION ) )  INTO  ProjRootVersion
--                                FROM RELEASE_TAGS rt
--                               WHERE rt.RTAG_ID = rt.PARENT_RTAG_ID;
--                              
--                              -- Set Next Rtag Version
--                              sNextVersion := CAST( ProjRootVersion + 1 AS VARCHAR2 );
--                              
--                              -- Restart Lifecycle
--                              nNextLifeCycle := 0;
--                              
--                              
--                              EXCEPTION
--                              WHEN NO_DATA_FOUND THEN
--                              -- Release Manager has no releases, hence start from 1
--                              sNextVersion := '1';
--                              nNextLifeCycle := 0;
--                              
--                      END;
--                      
--                      
--                                      
--              ELSE
--                      /* Releases found in this project. */
--                                                         
--                      SELECT qry.RTAG_ID, qry.RTAG_LIFE_CYCLE  INTO nParentRtagId, LastLifeCycle
--                        FROM (
--                                      SELECT rt.RTAG_ID, rt.RTAG_LIFE_CYCLE
--                                        FROM RELEASE_TAGS rt
--                                       WHERE rt.PROJ_ID = nProjId
--                                         AND rt.RTAG_VERSION = ProjRootVersion 
--                                       ORDER BY rt.RTAG_LIFE_CYCLE DESC
--                                      ) qry
--                       WHERE ROWNUM = 1;              
-- 
--                      
--                      -- Set same Rtag Version
--                      sNextVersion := ProjRootVersion;
--                      
--                      -- Increase Lifecycle
--                      nNextLifeCycle := LastLifeCycle + 1;
--              
--              END IF;
--                              
--      
--              
--      ELSE
--              /* Create Release From other release within this project */
--              
--              -- Set parent id
--              nParentRtagId := nSourceRtagId;
--              
--              
--              -- Get number of source branches
--              SELECT COUNT(rt.RTAG_ID) INTO SourceBranchCount
--                FROM RELEASE_TAGS rt
--               WHERE rt.PROJ_ID = nProjId
--                 AND rt.PARENT_RTAG_ID = nSourceRtagId;                  
--                 
--                 
--              IF SourceBranchCount = 0 THEN
--                      /* Release is Head (i.e. Tip on its branch ) */
--                      
--                      -- Set Next Rtag Version
--                      sNextVersion := SourceVersion;
--                      
--                      -- Increase Lifecycle
--                      nNextLifeCycle := SourceLifeCycle + 1;
--                      
--                      
--              ELSIF SourceBranchCount = 1 THEN
--                      /* Release IS NOT Head, but can be branched */
--                      
--                      -- Set Next Rtag Version
--                      sNextVersion := SourceVersion ||'.'|| SourceLifeCycle;
--                      
--                      -- Reset Lifecycle to 1
--                      nNextLifeCycle := 1;
--                      
--                      
--              ELSE    
--                      -- Limit of two branches is reached
--                      RAISE_APPLICATION_ERROR (-20000, 'This release is already branched and cannot be branched again. ');            
--                      
--              END IF; 
--                      
--                      
--              
--              
--              
--      
--      END IF;
--      
--              
-- 
END;

/*-------------------------------------------------------------------------------------------------------*/
PROCEDURE GET_VIEW_CONTENT ( RtagId IN NUMBER, ViewId IN NUMBER, RecordSet OUT typeCur ) IS

        IsBaseView CHAR(1);
                
BEGIN

        -- Check if the view is BASE VIEW
        SELECT vi.BASE_VIEW INTO IsBaseView
          FROM VIEWS vi
         WHERE vi.VIEW_ID = ViewId;
         
        IF (IsBaseView = 'Y') THEN 
                -- Get Base view content
                OPEN RecordSet FOR
                SELECT DECODE ( rel.pkg_state, NULL, 0, rel.pkg_state ) AS PKG_STATE,
                           pv.pv_id, 
                           pkg.pkg_name, 
                           pv.pkg_version, 
                           pv.dlocked, 
                           pv.pv_description,
                           pv.BUILD_TYPE
                  FROM release_content rel,
                       packages pkg,
                       package_versions pv
                 WHERE pv.pkg_id = pkg.pkg_id
                   AND rel.pv_id = pv.pv_id
                   AND rel.BASE_VIEW_ID = ViewId
                   AND rel.RTAG_ID = RtagId
                 ORDER BY UPPER(pkg.PKG_NAME);

        ELSE     
         
                -- Get non base view content
                OPEN RecordSet FOR
                SELECT DECODE ( rel.pkg_state, NULL, 0, rel.pkg_state ) AS PKG_STATE,
                           pv.pv_id, 
                           pkg.pkg_name, 
                           pv.pkg_version, 
                           pv.dlocked, 
                           pv.pv_description,
                           pv.BUILD_TYPE
                  FROM release_content rel,
                       packages pkg,
                       package_versions pv,
                           VIEW_DEF vd
                 WHERE pv.pkg_id = pkg.pkg_id
                   AND rel.pv_id = pv.pv_id
                   AND vd.VIEW_ID = ViewId
                   AND vd.PKG_ID = pv.PKG_ID
                   AND rel.RTAG_ID = RtagId
                 ORDER BY UPPER(pkg.PKG_NAME);           
         
        END IF;          
        
        
         

END;
/*-------------------------------------------------------------------------------------------------------*/
PROCEDURE ADD_PACKAGE ( newPvId IN NUMBER, ViewId IN NUMBER, RtagId IN NUMBER, UserId IN NUMBER ) IS

        ReleaseLocation VARCHAR2(4000);
        sPkgVersion VARCHAR2(4000);
        nPkgId NUMBER;
        sVExt VARCHAR2(4000);
        IsPatch PACKAGE_VERSIONS.IS_PATCH%TYPE := 'N';

        -- Find package for replacement
        CURSOR curReplacePkg IS
    SELECT pv.PV_ID
          FROM RELEASE_CONTENT rc,
                   PACKAGE_VERSIONS pv
         WHERE rc.PV_ID = pv.PV_ID
           AND rc.RTAG_ID = RtagId
           AND pv.PKG_ID = nPkgId
           AND NVL( pv.V_EXT, 'LINK_A_NULL' ) = NVL( sVExt, 'LINK_A_NULL' );
    recReplacePkg curReplacePkg%ROWTYPE;

BEGIN

        -- Get is_patch, pkg_id and v_ext
        SELECT pv.IS_PATCH, pv.PKG_ID, pv.V_EXT INTO IsPatch, nPkgId, sVExt
          FROM PACKAGE_VERSIONS pv
         WHERE pv.PV_ID = newPvId;


        -- Never put patch in relesed area
        IF (IsPatch != 'Y') OR (IsPatch IS NULL) THEN

                -- Try to get a package to be replaced with this new one.
                -- Use unique constraint of PKG_ID and V_EXT
                OPEN curReplacePkg;
            FETCH curReplacePkg INTO recReplacePkg;

            IF curReplacePkg%FOUND THEN
                        -- Replace package
                        REPLACE_PACKAGE ( newPvId, recReplacePkg.PV_ID, RtagId, UserId );

                ELSE
                        -- Add new package
                        INSERT INTO RELEASE_CONTENT ( RTAG_ID, PV_ID, BASE_VIEW_ID, INSERT_STAMP, INSERTOR_ID, PKG_STATE )
                        VALUES( RtagId, newPvId, ViewId, Ora_Sysdate, UserId, 0);


                    /* LOG ACTION */
                        SELECT proj.PROJ_NAME ||' / '|| rt.RTAG_NAME INTO ReleaseLocation
                          FROM PROJECTS proj,
                                   RELEASE_TAGS rt
                         WHERE rt.PROJ_ID = proj.PROJ_ID
                           AND rt.RTAG_ID = RtagId;

                        SELECT pv.PKG_VERSION INTO sPkgVersion
                          FROM PACKAGE_VERSIONS pv
                         WHERE pv.PV_ID = newPvId;

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


                END IF;

                CLOSE curReplacePkg;

        END IF;

END;
/*-------------------------------------------------------------------------------------------------------*/
PROCEDURE REPLACE_PACKAGE ( newPvId IN NUMBER, oldPvId IN NUMBER, RtagId IN NUMBER, UserId IN NUMBER ) IS

        ReleaseLocation VARCHAR2(4000);
        sPkgVersion VARCHAR2(4000);

BEGIN

        -- Replace Package
    UPDATE RELEASE_CONTENT
       SET pv_id = newPvId,
           insert_stamp = Ora_Sysdate,
           insertor_id = UserId
     WHERE rtag_id = RtagId
       AND pv_id = oldPvId;


    /* LOG ACTION */
        SELECT proj.PROJ_NAME ||' / '|| rt.RTAG_NAME INTO ReleaseLocation
          FROM PROJECTS proj,
                   RELEASE_TAGS rt
         WHERE rt.PROJ_ID = proj.PROJ_ID
           AND rt.RTAG_ID = RtagId;

        SELECT pv.PKG_VERSION INTO sPkgVersion
          FROM PACKAGE_VERSIONS pv
         WHERE pv.PV_ID = newPvId;

        Log_Action ( oldPvId, 'replaced_with', UserId, 'Replacing with: '|| sPkgVersion ||' at '|| ReleaseLocation );
        Log_Action ( newPvId, 'add', UserId, 'Location: '|| ReleaseLocation );

END;
/*-------------------------------------------------------------------------------------------------------*/
PROCEDURE REMOVE_PACKAGE ( PvId IN NUMBER, RtagId IN NUMBER, UserId IN NUMBER ) IS
        ReleaseLocation VARCHAR2(4000);

BEGIN
        
                
        -- Delete old package
        DELETE
          FROM RELEASE_CONTENT rc
         WHERE rc.PV_ID = PvId
           AND rc.RTAG_ID = RtagId;


        /* LOG ACTION */
        SELECT proj.PROJ_NAME ||' / '|| rt.RTAG_NAME INTO ReleaseLocation
          FROM PROJECTS proj,
               RELEASE_TAGS rt
         WHERE rt.PROJ_ID = proj.PROJ_ID
           AND rt.RTAG_ID = RtagId;

        Log_Action ( PvId, 'delete_from_released', UserId, 'Location: '|| ReleaseLocation );


END;
/*-------------------------------------------------------------------------------------------------------*/
PROCEDURE REMOVE_MATCHING_PACKAGE ( PvId IN NUMBER, RtagId IN NUMBER, UserId IN NUMBER ) IS
        ReleaseLocation VARCHAR2(4000);

        CURSOR curMatchingPackage IS
    SELECT mpv.PV_ID
          FROM RELEASE_CONTENT rc,
                   PACKAGE_VERSIONS mpv,
                   PACKAGE_VERSIONS pv
         WHERE rc.PV_ID = mpv.PV_ID
           AND rc.RTAG_ID = RtagId
           AND pv.PV_ID = PvId
           AND pv.PKG_ID = mpv.PKG_ID
           AND NVL( pv.V_EXT, '|LINK_A_NULL|' ) = NVL( mpv.V_EXT, '|LINK_A_NULL|' );
    recMatchingPackage curMatchingPackage%ROWTYPE;

BEGIN

        OPEN curMatchingPackage;
    FETCH curMatchingPackage INTO recMatchingPackage;

    IF curMatchingPackage%FOUND THEN
                -- Delete old package
                DELETE
                  FROM RELEASE_CONTENT rc
                 WHERE rc.PV_ID = recMatchingPackage.PV_ID
                   AND rc.RTAG_ID = RtagId;


                /* LOG ACTION */
                SELECT proj.PROJ_NAME ||' / '|| rt.RTAG_NAME INTO ReleaseLocation
                  FROM PROJECTS proj,
                           RELEASE_TAGS rt
                 WHERE rt.PROJ_ID = proj.PROJ_ID
                   AND rt.RTAG_ID = RtagId;

                Log_Action ( recMatchingPackage.PV_ID, 'delete_from_released', UserId, 'Location: '|| ReleaseLocation );

        END IF;


        CLOSE curMatchingPackage;




END;
/*-------------------------------------------------------------------------------------------------------*/
FUNCTION GET_PACKAGE_VIEW ( PvId IN NUMBER, RtagId IN NUMBER ) RETURN NUMBER IS

        ReturnValue NUMBER;

BEGIN
        SELECT rc.BASE_VIEW_ID INTO ReturnValue
          FROM RELEASE_CONTENT rc
         WHERE rc.RTAG_ID = RtagId
           AND rc.PV_ID = PvId;

        RETURN ReturnValue;
END;
/*-------------------------------------------------------------------------------------------------------*/
PROCEDURE RUN_POST_ACTIONS ( PvId IN NUMBER, RtagId IN NUMBER ) IS
        
BEGIN
        -- Reset Ignore warnings up-the-tree
        RESET_IGNORE_WARNINGS ( TO_CHAR(PvId), RtagId );

        -- Refresh Package states
        TOUCH_RELEASE ( RtagId );

END;
/*-------------------------------------------------------------------------------------------------------*/
PROCEDURE RUN_POST_ACTIONS_BULK ( PvId IN NUMBER ) IS
        CURSOR curReleases IS
    SELECT rc.RTAG_ID
          FROM RELEASE_CONTENT rc
         WHERE rc.PV_ID = PvId;
    recReleases curReleases%ROWTYPE;
        
BEGIN
        
        OPEN curReleases;
    FETCH curReleases INTO recReleases;
        
        WHILE curReleases%FOUND
        LOOP

                RUN_POST_ACTIONS ( PvId, recReleases.RTAG_ID );

                FETCH curReleases INTO recReleases;
        END LOOP;

        CLOSE curReleases;      

END;
/*-------------------------------------------------------------------------------------------------------*/
PROCEDURE CHANGE_PACKAGE_VIEW ( PvId IN NUMBER, RtagId IN NUMBER, NewViewId IN NUMBER ) IS
        
BEGIN
        
        UPDATE RELEASE_CONTENT rc SET
        rc.BASE_VIEW_ID = NewViewId
        WHERE rc.PV_ID = PvId
          AND rc.RTAG_ID = RtagId;
        
END;
/*-------------------------------------------------------------------------------------------------------*/
PROCEDURE ADD_RELEASE_REFERENCE ( nRtagId IN NUMBER, nRefRtagId IN NUMBER, ProblemString OUT VARCHAR2 ) IS
        
        RowCount NUMBER;
        
        
        CURSOR curPackageClash IS
        SELECT pkg.PKG_NAME,
                   er.*
          FROM (
          
                        /* Get existing referenced packages */
                        SELECT pv.PKG_ID,
                               pv.V_EXT
                          FROM RELEASE_LINKS rl,
                                   RELEASE_CONTENT rc,
                                   
PACKAGE_VERSIONS pv
                         WHERE rl.RTAG_ID = nRtagId
                           AND rl.REF_RTAG_ID = rc.RTAG_ID
                           AND rc.PV_ID = pv.PV_ID
                           
                    ) er,
                        (
                        
                        /* Get current reference packages */
                        SELECT pv.PKG_ID,
                               pv.V_EXT
                          FROM RELEASE_CONTENT rc,
                                   PACKAGE_VERSIONS pv
                         WHERE rc.RTAG_ID = nRefRtagId
                           AND rc.PV_ID = pv.PV_ID    
                        
                        ) cr,
                        PACKAGES PKG
         WHERE er.PKG_ID = cr.PKG_ID
           AND NVL(er.V_EXT, '|LINK_A_NULL|') = NVL(cr.V_EXT, '|LINK_A_NULL|')
           AND er.PKG_ID = pkg.PKG_ID;
           
        recPackageClash curPackageClash%ROWTYPE;           
                
BEGIN
        /*--------------- Business Rules Here -------------------*/
        IF (nRtagId = 0) OR ( nRtagId IS NULL ) THEN
                RAISE_APPLICATION_ERROR (-20000, 'nRtagId is not supplied. [nRtagId='|| nRtagId ||']' );
        END IF;
        
        IF (nRefRtagId = 0) OR ( nRefRtagId IS NULL ) THEN
                RAISE_APPLICATION_ERROR (-20000, 'nRefRtagId is not supplied. [nRefRtagId='|| nRefRtagId ||']' );
        END IF;
        /*-------------------------------------------------------*/
        ProblemString := NULL;
        
        
        -- Check if release already has references
        SELECT COUNT(rl.RTAG_ID)  INTO  RowCount
          FROM RELEASE_LINKS rl
         WHERE rl.RTAG_ID = nRtagId;    
         
         
        IF RowCount > 0 THEN
                -- Found existing references
                
                -- Make sure there is no package clashes
                OPEN curPackageClash;
            FETCH curPackageClash INTO recPackageClash;
                
                IF curPackageClash%FOUND THEN
                        
                        ProblemString := ProblemString ||'Following is partial list of package already referenced from other release:'|| UTL_TCP.CRLF;
                        
                        WHILE curPackageClash%FOUND
                        LOOP
                                EXIT WHEN LENGTH( ProblemString || '- '|| recPackageClash.PKG_NAME ||' '|| recPackageClash.V_EXT  || UTL_TCP.CRLF ) > 200;      -- Do not allow variable overflow 
                                
                                ProblemString := ProblemString || '- '|| recPackageClash.PKG_NAME ||' '|| recPackageClash.V_EXT  || UTL_TCP.CRLF;
                                
                                FETCH curPackageClash INTO recPackageClash;
                        END LOOP;
                        
                END IF;
                
                CLOSE curPackageClash;
                 
                
        ELSE
                -- No references found, hence reference a release
                
                
                -- Remove exising package from this release to be referenced
                REMOVE_RELEASE_REFERENCE ( nRtagId, nRefRtagId);        
                
                
                -- Add new linked packages to release
                INSERT INTO RELEASE_CONTENT rc (RTAG_ID, PV_ID, BASE_VIEW_ID, INSERT_STAMP, INSERTOR_ID, PKG_STATE)
                SELECT nRtagId,
                           rc.PV_ID,
                           rc.BASE_VIEW_ID, 
                           rc.INSERT_STAMP, 
                           rc.INSERTOR_ID, 
                           rc.PKG_STATE
                  FROM RELEASE_CONTENT rc
                 WHERE rc.RTAG_ID = nRefRtagId;
                 
                 
                 
                -- Copy ignore warnings for referenced packages
                INSERT INTO IGNORE_WARNINGS iw (RTAG_ID, PV_ID, DPV_ID, IS_PATCH_IGNORE)
                SELECT nRtagId, 
                           iw.PV_ID, 
                           iw.DPV_ID, 
                           iw.IS_PATCH_IGNORE
                  FROM IGNORE_WARNINGS iw,
                           RELEASE_CONTENT rc
                 WHERE iw.RTAG_ID = rc.RTAG_ID 
                   AND iw.PV_ID = rc.PV_ID
                   AND rc.RTAG_ID = nRefRtagId;  
                 
                
                
                -- Reference release 
                INSERT INTO RELEASE_LINKS (RTAG_ID, REF_RTAG_ID)
                VALUES ( nRtagId, nRefRtagId );          
                
        
                -- Refresh Package states
                TOUCH_RELEASE ( nRtagId );
                
        END IF;  
                
        
END;
/*-------------------------------------------------------------------------------------------------------*/
PROCEDURE REMOVE_RELEASE_REFERENCE ( nRtagId IN NUMBER, nRefRtagId IN NUMBER ) IS
        
                
BEGIN
        /*--------------- Business Rules Here -------------------*/
        IF (nRtagId = 0) OR ( nRtagId IS NULL ) THEN
                RAISE_APPLICATION_ERROR (-20000, 'nRtagId is not supplied. [nRtagId='|| nRtagId ||']' );
        END IF;
        
        IF (nRefRtagId = 0) OR ( nRefRtagId IS NULL ) THEN
                RAISE_APPLICATION_ERROR (-20000, 'nRefRtagId is not supplied. [nRefRtagId='|| nRefRtagId ||']' );
        END IF;
        
        /*-------------------------------------------------------*/
        
        
        -- Remove ignore warnings for those packages who use referenced packages
        DELETE FROM IGNORE_WARNINGS iw
         WHERE iw.RTAG_ID = nRtagId
           AND iw.DPV_ID IN (
                                                        SELECT dep.DPV_ID
                                                          FROM (
                                                                        
                                                                        /* Get referenced packages */
                                                                        SELECT pv.PKG_ID,
                                                                               pv.V_EXT
                                                                          FROM RELEASE_CONTENT rc,
                                                                                   PACKAGE_VERSIONS pv
                                                                         WHERE rc.RTAG_ID = nRefRtagId
                                                                           AND rc.PV_ID = pv.PV_ID    
                                                                        
                                                                        ) cr,
                                                                        (
                                                                        
                                                                        /* Get all dependencies for current release */
                                                                        SELECT DISTINCT 
                                                                                   pv.PKG_ID,
                                                                                   pv.V_EXT,
                                                                                   dep.DPV_ID
                                                                          FROM RELEASE_CONTENT rc,
                                                                                   PACKAGE_DEPENDENCIES dep,
                                                                                   PACKAGE_VERSIONS pv
                                                                         WHERE rc.RTAG_ID = nRtagId
                                                                           AND rc.PV_ID = dep.PV_ID             
                                                                           AND dep.PV_ID = pv.PV_ID        
                                                                        
                                                                        ) dep
                                                         WHERE dep.PKG_ID = cr.PKG_ID
                                                           AND NVL(dep.V_EXT, '|LINK_A_NULL|') = NVL(cr.V_EXT, '|LINK_A_NULL|')
                                                );  
        
        
        
        
        -- Remove ignore warnings for those package which are going to be replaced with the reference
        DELETE FROM IGNORE_WARNINGS iw
         WHERE iw.RTAG_ID = nRtagId
           AND iw.PV_ID IN (
                                                SELECT pv.PV_ID
                                                  FROM (
                                                                
                                                                /* Get referenced packages */
                                                                SELECT pv.PKG_ID,
                                                                       pv.V_EXT
                                                                  FROM RELEASE_CONTENT rc,
                                                                           PACKAGE_VERSIONS pv
                                                                 WHERE rc.RTAG_ID = nRefRtagId
                                                                   AND rc.PV_ID = pv.PV_ID    
                                                                
                                                                ) cr,
                                                                RELEASE_CONTENT rc,
                                                                PACKAGE_VERSIONS pv
                                                 WHERE pv.PKG_ID = cr.PKG_ID
                                                   AND NVL(pv.V_EXT, '|LINK_A_NULL|') = NVL(cr.V_EXT, '|LINK_A_NULL|')
                                                   AND rc.RTAG_ID = nRtagId
                                                   AND rc.PV_ID = pv.PV_ID
                                                );                      
        
        
        
        
        
        -- Remove matching packages from release
        DELETE FROM RELEASE_CONTENT rc
         WHERE rc.RTAG_ID = nRtagId
           AND rc.PV_ID IN (
                                                SELECT pv.PV_ID
                                                  FROM (
                                                                
                                                                /* Get referenced packages */
                                                                SELECT pv.PKG_ID,
                                                                       pv.V_EXT
                                                                  FROM RELEASE_CONTENT rc,
                                                                           PACKAGE_VERSIONS pv
                                                                 WHERE rc.RTAG_ID = nRefRtagId
                                                                   AND rc.PV_ID = pv.PV_ID    
                                                                
                                                                ) cr,
                                                                RELEASE_CONTENT rc,
                                                                PACKAGE_VERSIONS pv
                                                 WHERE pv.PKG_ID = cr.PKG_ID
                                                   AND NVL(pv.V_EXT, '|LINK_A_NULL|') = NVL(cr.V_EXT, '|LINK_A_NULL|')
                                                   AND rc.RTAG_ID = nRtagId
                                                   AND rc.PV_ID = pv.PV_ID
                                                );      
        
        
        
        
                
        -- Remove Reference release 
        DELETE 
          FROM RELEASE_LINKS rl
         WHERE rl.RTAG_ID = nRtagId
           AND rl.REF_RTAG_ID = nRefRtagId;
        

        -- Refresh Package states
        TOUCH_RELEASE ( nRtagId );
        
        
END;
/*-------------------------------------------------------------------------------------------------------*/
PROCEDURE CAN_USER_WRITE_TO_RELEASE ( RtagId IN NUMBER, PvId IN NUMBER, Permission IN CHAR ) IS

        cReleaseMode CHAR(1);
        cPvMode CHAR(1);
                
BEGIN

        -- Get Release Mode
        SELECT rt.OFFICIAL INTO cReleaseMode
          FROM RELEASE_TAGS rt
         WHERE rt.RTAG_ID = RtagId;
         
         
        -- Get Package Mode
        SELECT pv.DLOCKED INTO cPvMode
          FROM PACKAGE_VERSIONS pv
         WHERE pv.PV_ID = PvId;  
         
        -- Only check if package is locked 
        IF (cPvMode = 'Y') THEN
         
                IF (cReleaseMode != 'N') AND (Permission != 'Y')
                THEN
                        RAISE_APPLICATION_ERROR (-20000, 'Cannot add package to this release. Release needs to be in Open Mode.' );
                        
                END IF;
                
        END IF; 
        
        
END;
/*-------------------------------------------------------------------------------------------------------*/
END PK_RELEASE_TEST;
/
ALTER PACKAGE "RELEASE_MANAGER"."PK_RELEASE_TEST" 
  COMPILE BODY 
    PLSQL_OPTIMIZE_LEVEL=  2
    PLSQL_CODE_TYPE=  INTERPRETED
    PLSQL_DEBUG=  FALSE
 REUSE SETTINGS TIMESTAMP '2007-08-29 16:25:16'
/
Subversion Repositories DevTools

(root)/ManagerSuite/Release_Manager_Schema/trunk/package/package_body/pk_release_test.sql – Rev 221
