Subversion Repositories DevTools

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;