Subversion Repositories DevTools

CREATE PROCEDURE "RELEASE_MANAGER"."IGNORE_DEPENDENCY_WARNINGS" ( nRtagId IN IGNORE_WARNINGS.RTAG_ID%TYPE,
                                                                                                                 nPvId IN IGNORE_WARNINGS.PV_ID%TYPE,
                                                         sIgnoreIdList IN VARCHAR2,
                                                         bDoPatchIgnore IN BOOLEAN DEFAULT FALSE,
                                                         nUserId IN NUMBER DEFAULT NULL ) IS
/* ---------------------------------------------------------------------------
    Version: 5.0
   --------------------------------------------------------------------------- */

oIgnoreIdCollector RELMGR_NUMBER_TAB_t := RELMGR_NUMBER_TAB_t();
ReleaseLocation VARCHAR2(4000);
ActionTypeId NUMBER;

BEGIN


        IF (NOT bDoPatchIgnore) THEN
        /* Manual Ignore Warnings */
        oIgnoreIdCollector := IN_LIST_NUMBER ( sIgnoreIdList );


        /* Log Action */
        -- Get Release Location
        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 = nRtagId;

        -- Get Action Type Id for IGNORE_ON
        SELECT act.ACTTYPE_ID INTO ActionTypeId
              FROM ACTION_TYPE act
             WHERE act.NAME = 'ignore_on';

        -- Get Ignored (Current MINUS Old)
        INSERT INTO ACTION_LOG ( USER_ID, ACTION_DATETIME, PV_ID, DESCRIPTION, ACTTYPE_ID )
                SELECT nUserId, ORA_SYSDATETIME, nPvId, pkg.PKG_NAME ||' '|| rpv.PKG_VERSION ||' at '|| ReleaseLocation, ActionTypeId
                  FROM (
                      SELECT TO_NUMBER(nRtagId) AS RTAG_ID, TO_NUMBER(nPvId), pv.PV_ID AS DPV_ID
                        FROM PACKAGE_VERSIONS pv
                       WHERE pv.PV_ID IN ( SELECT * FROM TABLE ( CAST ( oIgnoreIdCollector AS RELMGR_NUMBER_TAB_t ) ) )
                      MINUS
                      SELECT igw.RTAG_ID, igw.PV_ID, igw.DPV_ID
                        FROM IGNORE_WARNINGS igw
                       WHERE igw.RTAG_ID = nRtagId
                         AND igw.PV_ID = nPvId
                       ) qry,
                       PACKAGE_VERSIONS pv,
                       PACKAGES pkg,
                       RELEASE_CONTENT rc,
                       PACKAGE_VERSIONS rpv
                 WHERE pv.PKG_ID = pkg.PKG_ID
                   AND rc.RTAG_ID = nRtagId
                   AND rc.PV_ID = rpv.PV_ID
                   AND rpv.PKG_ID = pv.PKG_ID
                   AND NVL( rpv.V_EXT, 'LINK_A_NULL' ) = NVL( pv.V_EXT, 'LINK_A_NULL' )
                   AND qry.DPV_ID = pv.PV_ID;


        -- Get Action Type Id for IGNORE_OFF
        SELECT act.ACTTYPE_ID INTO ActionTypeId
              FROM ACTION_TYPE act
             WHERE act.NAME = 'ignore_off';

        -- Get UnIgnored (Old MINUS Current)
        INSERT INTO ACTION_LOG ( USER_ID, ACTION_DATETIME, PV_ID, DESCRIPTION, ACTTYPE_ID )
                SELECT nUserId, ORA_SYSDATETIME, nPvId, pkg.PKG_NAME ||' '|| rpv.PKG_VERSION ||' at '|| ReleaseLocation, ActionTypeId
                  FROM (
                      SELECT igw.RTAG_ID, igw.PV_ID, igw.DPV_ID
                          FROM IGNORE_WARNINGS igw
                         WHERE igw.RTAG_ID = nRtagId
                           AND igw.PV_ID = nPvId
                MINUS
                SELECT TO_NUMBER(nRtagId) AS RTAG_ID, TO_NUMBER(nPvId), pv.PV_ID AS DPV_ID
                          FROM PACKAGE_VERSIONS pv
                         WHERE pv.PV_ID IN ( SELECT * FROM TABLE ( CAST ( oIgnoreIdCollector AS RELMGR_NUMBER_TAB_t ) ) )
                       ) qry,
                       PACKAGE_VERSIONS pv,
                       PACKAGES pkg,
                       RELEASE_CONTENT rc,
                       PACKAGE_VERSIONS rpv
                 WHERE pv.PKG_ID = pkg.PKG_ID
                   AND rc.RTAG_ID = nRtagId
                   AND rc.PV_ID = rpv.PV_ID
                   AND rpv.PKG_ID = pv.PKG_ID
                   AND NVL( rpv.V_EXT, 'LINK_A_NULL' ) = NVL( pv.V_EXT, 'LINK_A_NULL' )
                   AND qry.DPV_ID = pv.PV_ID;



        -- Delete Current Ignore Warnings
            DELETE
              FROM IGNORE_WARNINGS igw
             WHERE igw.RTAG_ID = nRtagId
               AND igw.PV_ID = nPvId
               AND igw.IS_PATCH_IGNORE IS NULL;


        IF (oIgnoreIdCollector.COUNT > 0) THEN
                    -- Insert Ignore Warnings
                    INSERT INTO IGNORE_WARNINGS igw ( RTAG_ID, PV_ID, DPV_ID )
                    SELECT nRtagId,
                           nPvId,
                       pv.PV_ID AS DPV_ID
                  FROM PACKAGE_VERSIONS pv
                 WHERE pv.PV_ID IN ( SELECT * FROM TABLE ( CAST ( oIgnoreIdCollector AS RELMGR_NUMBER_TAB_t ) ) );
        END IF;


                -- Touch Release if package is in Release Area
                IF PK_ENVIRONMENT.GET_PACKAGE_AREA ( nPvId, nRtagId ) = 2 THEN
                        Touch_Release (nRtagId);
                END IF;

        ELSE
        /* Patch Ignore Warnings */

        -- Delete Current Patch Ignore Warnings
            DELETE
              FROM IGNORE_WARNINGS igw
             WHERE igw.RTAG_ID = nRtagId
               AND igw.IS_PATCH_IGNORE = 'Y';


        -- Delete Manual Ignores that need to be Patch Ignores
            DELETE
              FROM IGNORE_WARNINGS igw
             WHERE ( RTAG_ID, PV_ID, DPV_ID ) IN
                (
                                SELECT DISTINCT
                       nRtagId,
                                           err.PV_ID,
                       err.ERR_DPV AS DPV_ID
                                  FROM
                                       (
                                       /* Full Release Contents used for reference*/
                                       SELECT rpv.PV_ID, rpv.pkg_id, NVL(rpv.v_ext, '|LINK_A_NULL|') AS v_ext --, rpv.pkg_version, rpv.v_nmm, rpv.v_mm
                                         FROM release_content rel, package_versions rpv
                                        WHERE rel.pv_id = rpv.pv_id
                                          AND rtag_id = nRtagId
                                       ) frc,
                                       (
                                        /* DPV_IDs not fount in release*/
                                        SELECT dep.pv_id, dep.dpv_id AS err_dpv
                                          FROM package_dependencies dep
                                         WHERE dep.pv_id IN ( SELECT pv_id FROM release_content WHERE rtag_id = nRtagId )
                                           AND NOT dep.dpv_id IN ( SELECT pv_id FROM release_content WHERE rtag_id = nRtagId )
                                       ) err,
                                       (
                                        SELECT DISTINCT pp.PV_ID, dep.DPV_ID
                                          FROM PACKAGE_PATCHES pp,
                                                   PACKAGE_DEPENDENCIES dep,
                                                   RELEASE_CONTENT rc
                                         WHERE rc.RTAG_ID = nRtagId
                                           AND rc.PV_ID = pp.PV_ID
                                           AND dep.PV_ID = pp.PATCH_ID
                                       ) pp,
                                       package_versions errpkg,
                                       package_versions errpv
                                 WHERE err.err_dpv = errpv.pv_id
                                   AND errpv.pkg_id = frc.pkg_id(+)
                                   AND NVL(errpv.v_ext, '|LINK_A_NULL|') = frc.v_ext(+)
                                   AND err.pv_id = errpkg.pv_id
                                   AND err.PV_ID = pp.PV_ID
                                   AND frc.PV_ID = pp.DPV_ID
                        );

        /*
        ---------------------------------------------------
        --  Make sure that select statement above and below are same
        ---------------------------------------------------
        */


                -- Insert Patch Ignores
                INSERT INTO IGNORE_WARNINGS igw ( RTAG_ID, PV_ID, DPV_ID, IS_PATCH_IGNORE )
                SELECT DISTINCT
                           nRtagId,
                       err.PV_ID,
                       err.ERR_DPV AS DPV_ID,
                       'Y'
                  FROM
                       (
                       /* Full Release Contents used for reference*/
                       SELECT rpv.PV_ID, rpv.pkg_id, NVL(rpv.v_ext, '|LINK_A_NULL|') AS v_ext --, rpv.pkg_version, rpv.v_nmm, rpv.v_mm
                         FROM release_content rel, package_versions rpv
                        WHERE rel.pv_id = rpv.pv_id
                          AND rtag_id = nRtagId
                       ) frc,
                       (
                        /* DPV_IDs not fount in release*/
                        SELECT dep.pv_id, dep.dpv_id AS err_dpv
                          FROM package_dependencies dep
                         WHERE dep.pv_id IN ( SELECT pv_id FROM release_content WHERE rtag_id = nRtagId )
                           AND NOT dep.dpv_id IN ( SELECT pv_id FROM release_content WHERE rtag_id = nRtagId )
                       ) err,
                       (
                        SELECT DISTINCT pp.PV_ID, dep.DPV_ID
                          FROM PACKAGE_PATCHES pp,
                                   PACKAGE_DEPENDENCIES dep,
                                   RELEASE_CONTENT rc
                         WHERE rc.RTAG_ID = nRtagId
                           AND rc.PV_ID = pp.PV_ID
                           AND dep.PV_ID = pp.PATCH_ID
                       ) pp,
                       package_versions errpkg,
                       package_versions errpv
                 WHERE err.err_dpv = errpv.pv_id
                   AND errpv.pkg_id = frc.pkg_id(+)
                   AND NVL(errpv.v_ext, '|LINK_A_NULL|') = frc.v_ext(+)
                   AND err.pv_id = errpkg.pv_id
                   AND err.PV_ID = pp.PV_ID
                   AND frc.PV_ID = pp.DPV_ID;


    END IF;


END Ignore_Dependency_Warnings;
/