CREATE PACKAGE BODY "RELEASE_MANAGER"."PK_PLANNED" IS

/*
------------------------------
||  Last Modified:  S.Vukovic
||  Modified Date:  6/May/2005
||  Body Version:   1.0
------------------------------
*/


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

	oldPvId NUMBER;
	ReleaseLocation VARCHAR2(4000);
	IsPatch CHAR(1) := NULL;
	sLocation VARCHAR2(4000) := NULL;
	nRtagIdLocation NUMBER;
	


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

	BEGIN
		-- Check if Exists in "Work in progress" anywhere in the world, except "Closed mode" releases
		SELECT proj.PROJ_NAME ||' > '|| rt.RTAG_NAME, rt.RTAG_ID  INTO sLocation, nRtagIdLocation
		  FROM PLANNED pl,
		  	   RELEASE_TAGS rt,
			   PROJECTS proj
		 WHERE pl.PV_ID = newPvId
		   AND pl.RTAG_ID = rt.RTAG_ID
		   AND rt.OFFICIAL != 'Y'
		   AND rt.PROJ_ID = proj.PROJ_ID;

		EXCEPTION
	    	WHEN NO_DATA_FOUND THEN
	       		sLocation := NULL;
	END;	   	     



	IF (sLocation IS NULL) OR (nRtagIdLocation = RtagId) THEN

		-- Add to "Pending" area
		INSERT INTO PLANNED ( RTAG_ID, PV_ID, VIEW_ID )
		VALUES( RtagId, newPvId, ViewId );


	    /* 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 ( newPvId, 'add_to_planned', UserId, 'Location: '|| ReleaseLocation );
	
	ELSE
		
		RAISE_APPLICATION_ERROR (-20000, 'This version is already in Pending Area at '|| sLocation ||'.' );		

	END IF;

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

	ReleaseLocation VARCHAR2(4000);

BEGIN

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


	-- Get release location for logging pusposes
	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;


	-- Delete from Work In Progress
	DELETE
	  FROM PLANNED pl
	 WHERE pl.RTAG_ID = RtagId
	   AND pl.PV_ID = PvId;

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




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

	ReturnValue NUMBER;

BEGIN
	SELECT pl.VIEW_ID INTO ReturnValue
	  FROM PLANNED pl
	 WHERE pl.RTAG_ID = RtagId
	   AND pl.PV_ID = PvId;

	RETURN ReturnValue;
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 0 AS PKG_STATE,
			   NULL AS DEPRECATED_STATE,
			   pv.pv_id, 
			   pkg.pkg_name, 
			   pv.pkg_version, 
			   pv.dlocked, 
			   pv.pv_description,
			   pv.BUILD_TYPE
		  FROM PLANNED rel,
		       packages pkg,
		       package_versions pv
		 WHERE pv.pkg_id = pkg.pkg_id
		   AND rel.pv_id = pv.pv_id
		   AND rel.VIEW_ID = ViewId
		   AND rel.RTAG_ID = RtagId
		 ORDER BY UPPER(pkg.PKG_NAME);

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

END;
/*-------------------------------------------------------------------------------------------------------*/
PROCEDURE CHANGE_PACKAGE_VIEW ( PvId IN NUMBER, RtagId IN NUMBER, NewViewId IN NUMBER ) IS
	
BEGIN
	
	UPDATE PLANNED pl SET
	pl.VIEW_ID = NewViewId
	WHERE pl.PV_ID = PvId
	  AND pl.RTAG_ID = RtagId;
	
END;
/*-------------------------------------------------------------------------------------------------------*/
END PK_PLANNED;
/
ALTER PACKAGE "RELEASE_MANAGER"."PK_PLANNED" 
  COMPILE BODY 
    PLSQL_OPTIMIZE_LEVEL=  2
    PLSQL_CODE_TYPE=  INTERPRETED
    PLSQL_DEBUG=  FALSE
 REUSE SETTINGS TIMESTAMP '2007-08-23 16:29:22'
/
