Subversion Repositories DevTools

CREATE PROCEDURE "RELEASE_MANAGER"."UNDEPRECATE_PACKAGE" (nPvId IN PACKAGE_VERSIONS.PV_ID%TYPE,
                                                                                                 nRtagId IN DEPRECATED_PACKAGES.RTAG_ID%TYPE,
                                                                                                 nPkgId IN DEPRECATED_PACKAGES.PKG_ID%TYPE,
                                                                                                 nUserId IN NUMBER) IS
                                                                                                 
ext VARCHAR2(50);
PvIdList VARCHAR2(32767);
nIdCollector RELMGR_NUMBER_TAB_t := RELMGR_NUMBER_TAB_t();
PvId NUMBER;
sComments VARCHAR2(32767);
                                                                                                 
BEGIN



        --Extract the package extension
        SELECT V_EXT into ext 
        FROM PACKAGE_VERSIONS 
        WHERE PV_ID = nPvId;
         
        --SELECT COMMENTS into sComments 
        --FROM DEPRECATED_PACKAGES
        --WHERE RTAG_ID = nRtagId
        --AND V_EXT = ext
        --AND PKG_ID = nPkgId;
        
        --SELECT PV_ID into PvIdList FROM
        --RELEASE_CONTENT WHERE RTAG_ID = nRtagId
        --AND PKG_STATE = 6
        --AND PV_ID NOT IN nPvId;


        IF ext IS NOT NULL THEN
       -- Undeprecate Package
       DELETE FROM DEPRECATED_PACKAGES 
           WHERE RTAG_ID = nRtagId 
           AND PKG_ID = nPkgId
           AND V_EXT = ext;     

                UPDATE RELEASE_CONTENT 
                SET PKG_ID = NULL, DEPRECATED_STATE = NULL
                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 PKG.PKG_ID = nPkgId
                                          AND PV.V_EXT = ext
                                          UNION
                                          SELECT DISTINCT
                                                           qry.PV_ID
                                                                FROM (
                                                                                  SELECT dep.*,
                                                                                  LEVEL AS LEVEL_NUM
                                                                                  FROM PACKAGE_DEPENDENCIES dep
                                                                        START WITH dep.DPV_ID IN ( nPvId )
                                                                        --AND dep.DPV_ID NOT IN ( SELECT PV_ID FROM RELEASE_CONTENT WHERE RTAG_ID = nRtagId AND PKG_STATE = 6 )
                                                                        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
                                                                 
                                         );      
                   
        ELSE
       -- Undeprecate Package
       DELETE FROM DEPRECATED_PACKAGES 
           WHERE RTAG_ID = nRtagId 
           AND PKG_ID = nPkgId
           AND V_EXT IS NULL;   
           
                UPDATE RELEASE_CONTENT 
                SET PKG_ID = NULL, DEPRECATED_STATE = NULL
                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 PKG.PKG_ID = nPkgId
                                          AND PV.V_EXT IS NULL
                                          UNION
                                          SELECT DISTINCT
                                                           qry.PV_ID
                                                                FROM (
                                                                                  SELECT dep.*,
                                                                                  LEVEL AS LEVEL_NUM
                                                                                  FROM PACKAGE_DEPENDENCIES dep
                                                                        START WITH dep.DPV_ID IN ( nPvId )
                                                                        --AND dep.DPV_ID NOT IN ( SELECT PV_ID FROM RELEASE_CONTENT WHERE RTAG_ID = nRtagId AND PKG_STATE = 6 )
                                                                        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
                                                                 
                                         );                
        
        END IF;
        
        --IF PvIdList IS NOT NULL THEN
        
         --  nIdCollector := IN_LIST_NUMBER ( PvIdList );
        
         --  FOR i IN 1..nIdCollector.COUNT
         --  LOOP
        --         PvId := nIdCollector(i);
                

                
        --              Deprecate_Package (nPvId, nRtagId, nPkgId, sComments, nUserId );
        --              END LOOP;
        --END IF;
        
        Rebuild_environment(nRtagId);                    
                                 
                                                                                                         

END Undeprecate_Package;
/