Subversion Repositories DevTools

CREATE PROCEDURE "RELEASE_MANAGER"."SEED_PACKAGE_NAMES_VERSIONS" ( SSpkg_name IN PACKAGES.pkg_name%TYPE,
                                                          SSpkg_version IN PACKAGE_VERSIONS.pkg_version%TYPE,
                                                          NNuser_id IN NUMBER,
                                                          retPV_ID OUT NUMBER,
                                                                                                                  nCloneFromPvId IN NUMBER DEFAULT NULL ) IS
/* ---------------------------------------------------------------------------
    Version: 4.0
   --------------------------------------------------------------------------- */

    parPkg_id   NUMBER;
    parPv_id    NUMBER;
    cloneFrom_pv_id NUMBER;
    SSV_MM PACKAGE_VERSIONS.V_MM%TYPE;
    SSV_NMM PACKAGE_VERSIONS.V_NMM%TYPE;
    SSV_EXT PACKAGE_VERSIONS.V_EXT%TYPE;

    CURSOR packages_cur IS
        SELECT pkg_id FROM PACKAGES
        WHERE pkg_name = SSpkg_name;
    packages_rec packages_cur%ROWTYPE;

    CURSOR package_versions_cur IS
        SELECT pv_id FROM PACKAGE_VERSIONS
        WHERE pkg_id = parPkg_id
        AND pkg_version = SSpkg_version;
    package_versions_rec package_versions_cur%ROWTYPE;

    CURSOR clone_package_versions_cur IS
        SELECT MAX(pv_id) AS pv_id
          FROM PACKAGE_VERSIONS
         WHERE pkg_id = parPkg_id
           AND NVL(v_ext,'LINK_A_NULL') = NVL(SSV_EXT,'LINK_A_NULL');
    clone_package_versions_rec clone_package_versions_cur%ROWTYPE;

BEGIN
    /* -------------------------------------------- */
    /* Find if pkg_name exists and seed if required */
    /* -------------------------------------------- */
    OPEN packages_cur;
    FETCH packages_cur INTO packages_rec;

    IF packages_cur%NOTFOUND
    THEN
        /* INSERT into packages table */
        SELECT SEQ_PKG_ID.NEXTVAL INTO parPkg_id FROM DUAL;
        INSERT INTO PACKAGES ( pkg_id, pkg_name ) VALUES ( parPkg_id, SSpkg_name );

    ELSE
        parPkg_id := packages_rec.pkg_id;

    END IF;

    CLOSE packages_cur;



    /* ---------------------------------------------------- */
    /* Find if package_version exists and seed if required  */
    /* ---------------------------------------------------- */
    OPEN package_versions_cur;
    FETCH package_versions_cur INTO package_versions_rec;

    IF package_versions_cur%NOTFOUND
    THEN
        SELECT SEQ_PV_ID.NEXTVAL INTO parPv_id FROM DUAL;

        /* LOG ACTION */
        Log_Action ( parPv_id, 'new_version', NNuser_id,
                                 'New package version: '|| SSpkg_version );

        Split_Version ( SSpkg_version, SSV_MM, SSV_NMM, SSV_EXT );

        /* Find similar pkg_name + ext to clone from */
        OPEN clone_package_versions_cur;
        FETCH clone_package_versions_cur INTO clone_package_versions_rec;


        IF NOT clone_package_versions_rec.pv_id IS NULL
        THEN
            /* CLONE details from similar version  OR  from nCloneFromPvId */
                        IF ( NOT nCloneFromPvId IS NULL) THEN
                cloneFrom_pv_id := nCloneFromPvId;
                        ELSE
                                cloneFrom_pv_id := clone_package_versions_rec.pv_id;
                        END IF;

            -- Clone Package Version Details --
            INSERT INTO PACKAGE_VERSIONS ( pv_id, pkg_id, pkg_version, dlocked, created_stamp, creator_id, modified_stamp, modifier_id, V_MM, V_NMM, V_EXT,
                                           src_path, pv_description, PV_OVERVIEW, LAST_PV_ID, owner_id, BUILD_TYPE, IS_BUILD_ENV_REQUIRED, bs_id, is_autobuildable, is_deployable, ripple_field  )
                SELECT parPv_id         AS pv_id,
                       parPkg_id        AS pkg_id,
                       SSpkg_version    AS pkg_version,
                       'N'              AS dlocked,
                       Ora_Sysdate      AS created_stamp,
                       NNuser_id        AS creator_id,
                       Ora_Sysdatetime  AS modified_stamp,
                       NNuser_id        AS modifier_id,
                       SSV_MM           AS V_MM,
                       SSV_NMM          AS V_NMM,
                       SSV_EXT          AS V_EXT,
                       pv.src_path,
                       pv.pv_description,
                       pv.PV_OVERVIEW,
                       cloneFrom_pv_id  AS LAST_PV_ID,
                       pv.owner_id,
                                           pv.BUILD_TYPE,
                                           pv.IS_BUILD_ENV_REQUIRED,
                                           pv.BS_ID,
                                           pv.is_autobuildable,
                                           pv.IS_DEPLOYABLE, 
                                           pv.ripple_field
                  FROM PACKAGE_VERSIONS pv
                 WHERE pv.pv_id = cloneFrom_pv_id;

            Basic_Clone ( cloneFrom_pv_id, parPv_id, NULL, NNuser_id, parPkg_id, 0 );

        ELSE
            /* BRAND NEW version + ext */
            INSERT INTO PACKAGE_VERSIONS ( pv_id, pkg_id, pkg_version, dlocked, created_stamp, creator_id, modified_stamp, modifier_id, V_MM, V_NMM, V_EXT, owner_id, LAST_PV_ID, BUILD_TYPE, ripple_field )
                VALUES (
                    parPv_id,
                    parPkg_id,
                    SSpkg_version,
                    'N',
                    Ora_Sysdate,
                    NNuser_id,
                    Ora_Sysdatetime,
                    NNuser_id,
                    SSV_MM,
                    SSV_NMM,
                    SSV_EXT,
                    NNuser_id,
                    parPv_id,
                                        'M',
                                        'b'
                   );

        END IF;

        CLOSE clone_package_versions_cur;
        retPV_ID := parPv_id;

    ELSE
        retPV_ID := package_versions_rec.pv_id;

    END IF;

    CLOSE package_versions_cur;




END Seed_Package_Names_Versions;
/