Subversion Repositories DevTools

CREATE PROCEDURE "RELEASE_MANAGER"."DEPLOY_TO_RELEASE" IS

/******************************************************************************

******************************************************************************/

	CURSOR curInfo IS

    SELECT DISTINCT

        qry.DPV_ID

     FROM (

             SELECT dep.*,

                    LEVEL AS LEVEL_NUM

               FROM PACKAGE_DEPENDENCIES dep

             START WITH dep.PV_ID IN ( 

                        69124,63479,60048,49566,49562,54351,63560,54360,46053,59753,97060,97061,100883,78236,75241,92371,92372,105682,135506,127204,135615,136498,137571,61534,76540,96787,133102,96770,96771,116692,133768,61537,88935,110216,96797,69469,98123,111626,96802,100289,97233,97623,135801,69700,77808,43026,77809,43027,100825,126854,105589,96405,102105,133770,134408,91177,91179,96822,96824,48031,78965,78936,78935,79114,76620,125164,61804,96826,61977,67592,72594,134415,135804,65097,64850,64851,64879,64881,64882,64884,67612,67613,67614,67615,68501,68502,68503,68516,68518,68519,68521,71974,68659,69764,68662,68661,68757,69765,69766,69767,71975,71976,71977,71978,71979,71980,75149,71982,71983,75153,75158,71986,75160,75161,75162,72418,72419,71987,71988,71990,75164,72420,72421,72423,72424,72425,75165,75181,75182,83285,75184,75201,75220,75240,75260,75280,75242,76320,75320,76340,76321,82031,76341,76342,76344,76345,76806,76350,76810,76811,76360,76361,76362,76380,76637,76638,76639,76647,76649,76812,76650,78261,76815,77603,76818,76846,76847,76848,76849,77604,77606,77016,77607,77608,77609,78518,77671,77610,78519,78520,78205,78206,77795,78262,78263,78666,78521,78669,78767,78768,78770,78771,82032,79471,78772,82033,80856,80210,80898,82449,80211,80212,80214,80215,80216,80611,80217,82034,82035,80749,80750,82036,94266,82037,82038,82039,82040,82041,82042,82043,82044,82045,82046,82450,82296,82297,82451,82047,82298,82299,82300,82452,82453,82454,82455,82456,82457,82769,82458,82459,82770,82460,82461,82771,83286,85069,83543,83544,83287,85070,84959,85071,85072,84960,85073,85074,84975,85194,85075,85195,85196,85197,85198,85199,94267,85231,85237,94268,94269,94270,94271,94273,94274,94275,94276,94277,94278,94279,94280,94281,94282,94283,94284,94285,94286,94287,94288,94289,94290,94291,94292,94293,94294,94295,94296,94297,94298,94299,94300,94301,94302,94303,94304,94305,94306,94307,94308,94309,94310,94311,94312,94313,94314,94315,94316,94634,94317,94635,97035,94319,94320,94636,94637,94322,96536,96552,96554,96555,96556,96568,96569,96570,96571,96572,96573,96574,96575,96576,96577,96578,96579,96580,96581,96582,96583,96584,96585,96586,96818,97435,96819,96820,97599,97436,97437,97438,97439,97440,97434,97723,97640,97641,97642,97724,97725,97729,97743,97825,98097,98098,98099,98100,97999,98049,98101,98102,98103,99506,98268,98207,99507,99508,99509,99159,99510,99511,99512,102883,99513,99514,100243,99515,99880,99562,99563,99676,99677,99678,99881,99882,99883,99884,100244,100245,100623,100247,100624,100625,100626,100885,100719,100886,100887,100888,100889,100890,100990,100991,101301,101603,110065,101605,102180,102627,102181,102182,102183,102184,102185,102186,102187,102188,102189,102190,102191,104378,102192,104379,102193,102379,102380,102381,102884,102410,102411,102628,102465,102629,102630,102885,102886,102887,104380,104381,104382,104383,104384,104385,104387,110063,104389,106409,104561,104594,104660,105088,105089,105090,105091,105093,105097,105673,105768,106410,109314,106173,110064,137890,106433,106960,109080,111665,122015,110954,116763,110066,116764,110123,111867,110713,116765,111666,116869,111667,111668,116766,116767,115142,115143,121477,113924,114594,116768,115144,116769,115170,115172,115171,115644,120042,120043,116092,116870,116871,118382,116872,116876,120044,118026,119364,121485,120045,120416,121478,120417,122016,124577,129495,123602,123600,124578,124579,124580,124581,124784,125238,127096,127097,129496,129497,128036,128415,129498,129499,129914,130063,130064,130680,130681,131333,131488,133886,132219,132477,132478,133887,136537,136552,136538,136539,136540,136553,136554,136555,137869,85076,80857,62418,64889,64890,64891,64893,68666,68667,68668,68670,68671,68672,68673,72019,72020,72023,72024,72227,77745,77746,77747,77748,77749,77750,77751,77798,78773,80751,82464,82465,83288,92874,92875,92876,92877,92878,94122,94123,96290,96882,96883,97554,97514,97726,98008,98009,98051,99193,100248,99608,100249,100250,100670,100671,100672,101059,101061,101607,102194,102195,102412,102413,102633,104390,104391,106421,104560,104595,105767,106174,106426,109081,122017,137851,110712,116873,115145,115146,114597,115645,116095,120046,123551,122018,124584,124585,129500,129501,129502,128417,129503,129915,130066,131487,133897,133882,133883,136559,85078,100673,63475,78264,78265,78266,78267,78775,80753,94118,94119,94120,94121,96888,100251,100252,102631,102590,102632,65229,43366,69139,132551,59764,127129,101196,96830,61535,105433,96833,96835,96839,83795,77723,126868,99146,70188,117523,117524,117525,99237,100854,100855,137314,137315,96868,138232,138233,69709,96907,130854,119208,69520,124772,96878,60049,47875,70354,59371,54558,59849,33893,43087,43134,33894,60054,24345,49563,49558,97474,84982,31770,30950,49926,60050,52670,59846,59891,30966,59744,61988,59745,59881,59746,59741,47449,59747,64101,63480,63562,105382,105383,60052,48955,49561,49556,47401,65896,65906,65909,65985,108670,68697,68698,59754,59752,59751,62526,59805,59799,66981,46050,59756,46044,59806,46051,59807,82290,66980,59808,46046,61536,88834,70260,111081,105409,66093,66095,66094,47440,65778,59750,56748,99152,78409

                        ) 

 			CONNECT BY PRIOR dep.DPV_ID = dep.PV_ID

 		) qry,

 		PACKAGES pkg,

		PACKAGE_VERSIONS pv

 	 WHERE qry.PV_ID = pv.PV_ID AND pv.PKG_ID = pkg.PKG_ID

         and (pv.is_patch is null or pv.is_patch = 'N');

    recInfo curInfo%ROWTYPE;

BEGIN

	OPEN curInfo;

    FETCH curInfo INTO recInfo;

	WHILE curInfo%FOUND

	LOOP

	insert into release_content(RTAG_ID, PV_ID, BASE_VIEW_ID, INSERT_STAMP, INSERTOR_ID, PKG_STATE)

    VALUES(8027, recInfo.dpv_id, 7, ora_sysdatetime, 0, 0);

    FETCH curInfo INTO recInfo;

	END LOOP;

	CLOSE curInfo;

END DEPLOY_TO_RELEASE; /

CREATE PROCEDURE "RELEASE_MANAGER"."DEPRECATE_PACKAGE" (nPvId IN PACKAGE_VERSIONS.PV_ID%TYPE,

	   	  		  							   nRtagId IN DEPRECATED_PACKAGES.RTAG_ID%TYPE,

	   	  		  							   nPkgId IN DEPRECATED_PACKAGES.PKG_ID%TYPE,

											   sComments IN DEPRECATED_PACKAGES.COMMENTS%TYPE,

											   nUserId IN NUMBER) IS

ext VARCHAR2(50);

BEGIN

	--Extract the package version extension

	SELECT V_EXT into ext

	FROM PACKAGE_VERSIONS

	WHERE PV_ID = nPvId; 

    -- Deprecate Package

    INSERT INTO DEPRECATED_PACKAGES (RTAG_ID, PKG_ID, COMMENTS, V_EXT)

	VALUES (nRtagId, nPkgId, sComments, ext);

	IF ext IS NOT NULL THEN

	   UPDATE RELEASE_CONTENT 

	   SET PKG_ID = nPkgId, DEPRECATED_STATE = 6

	   WHERE RTAG_ID = nRtagId

	   AND PV_ID IN (SELECT PV.PV_ID 

		   	  	  	FROM PACKAGE_VERSIONS PV, PACKAGES PKG

				  	WHERE PKG.PKG_ID = PV.PKG_ID

				  	AND PV.V_EXT = ext

				  	AND PKG.PKG_ID = nPkgId

				 	);						

	ELSE

	   UPDATE RELEASE_CONTENT 

	   SET PKG_ID = nPkgId, DEPRECATED_STATE = 6

	   WHERE RTAG_ID = nRtagId

	   AND PV_ID IN (SELECT PV.PV_ID 

		   	  	  	FROM PACKAGE_VERSIONS PV, PACKAGES PKG

				  	WHERE PKG.PKG_ID = PV.PKG_ID

				  	AND PV.V_EXT IS NULL

				  	AND PKG.PKG_ID = nPkgId

				 	);

	END IF;

	UPDATE RELEASE_CONTENT

	SET PKG_ID = nPkgId, DEPRECATED_STATE = 7

	WHERE RTAG_ID = nRtagId

	AND PV_ID IN (		   SELECT DISTINCT

 	 		  	 		   qry.PV_ID

						        FROM (

								 	  SELECT dep.*,

									  LEVEL AS LEVEL_NUM

									  FROM PACKAGE_DEPENDENCIES dep 

 								START WITH dep.DPV_ID IN ( nPvId ) 

								CONNECT BY PRIOR dep.PV_ID = dep.DPV_ID

 								) qry,

								PACKAGES pkg,

								PACKAGE_VERSIONS pv,

								RELEASE_CONTENT rc

						 	 WHERE qry.PV_ID = pv.PV_ID AND pv.PKG_ID = pkg.PKG_ID

							 AND rc.RTAG_ID = nRtagId AND rc.PV_ID = qry.PV_ID

							 AND rc.DEPRECATED_STATE IS NULL

				 );			 

    /* LOG ACTION */

   	/*Log_Action ( nPkgId, 'deprecated_package', nUserId );*/

END Deprecate_Package;

/