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