Rev 51 | Blame | Compare with Previous | Last modification | View Log | RSS feed
CREATE TABLE PACKAGE_BUILD_ENV(PV_ID NUMBER NOT NULL,BE_ID NUMBER NOT NULL,BUILD_TYPE NUMBER)TABLESPACE "USERS"LOGGINGPCTFREE 10PCTUSED 0INITRANS 1MAXTRANS 255STORAGE(INITIAL 128KMINEXTENTS 1MAXEXTENTS 2147483645FREELISTS 1FREELIST GROUPS 1BUFFER_POOL DEFAULT);CREATE TABLE NOTIFICATION_HISTORY(RTAG_ID NUMBER NOT NULL,PV_ID NUMBER NOT NULL,USER_ID NUMBER NOT NULL,DATE_TIME_STAMP DATE NOT NULL, CONSTRAINT PK_NOTIFICATION_HISTORY PRIMARY KEY(RTAG_ID,PV_ID,USER_ID)ENABLE)TABLESPACE "USERS"LOGGINGPCTFREE 10PCTUSED 0INITRANS 1MAXTRANS 255STORAGE(INITIAL 128KMINEXTENTS 1MAXEXTENTS 2147483645FREELISTS 1FREELIST GROUPS 1BUFFER_POOL DEFAULT);CREATE TABLE TEMP_ENV_STATES(SESSION_NUM NUMBER NOT NULL,LEVEL_NUM NUMBER NOT NULL,PV_ID NUMBER NOT NULL,PKG_ID NUMBER NOT NULL,V_EXT VARCHAR2(50 BYTE),TES_STATE NUMBER NOT NULL)TABLESPACE "USERS"LOGGINGPCTFREE 10PCTUSED 0INITRANS 1MAXTRANS 255STORAGE(INITIAL 128KMINEXTENTS 1MAXEXTENTS 2147483645FREELISTS 1FREELIST GROUPS 1BUFFER_POOL DEFAULT);CREATE TABLE DO_NOT_RIPPLE(RTAG_ID NUMBER NOT NULL,PV_ID NUMBER NOT NULL)TABLESPACE "USERS"LOGGINGPCTFREE 10PCTUSED 0INITRANS 1MAXTRANS 255STORAGE(INITIAL 64KMINEXTENTS 1MAXEXTENTS 2147483645FREELISTS 1FREELIST GROUPS 1BUFFER_POOL DEFAULT);CREATE TABLE NOTE_MANAGER(NID VARCHAR2(30 BYTE) NOT NULL,LAST_USER VARCHAR2(20 BYTE),LAST_DATE DATE,DESCRIPTION VARCHAR2(4000 BYTE), CONSTRAINT PKNOTE_MANAGER1 PRIMARY KEY(NID)ENABLE)TABLESPACE "USERS"LOGGINGPCTFREE 10PCTUSED 0INITRANS 1MAXTRANS 255STORAGE(INITIAL 128KMINEXTENTS 1MAXEXTENTS 2147483645FREELISTS 1FREELIST GROUPS 1BUFFER_POOL DEFAULT);CREATE TABLE TEMP_TREE_BROWSE(SESSION_NUM NUMBER,LEVEL_NUM NUMBER,PV_ID NUMBER,PKG_ID NUMBER,V_EXT VARCHAR2(50 BYTE),DIRECTION NUMBER)TABLESPACE "USERS"LOGGINGPCTFREE 10PCTUSED 0INITRANS 1MAXTRANS 255STORAGE(INITIAL 64KMINEXTENTS 1MAXEXTENTS 2147483645FREELISTS 1FREELIST GROUPS 1BUFFER_POOL DEFAULT);CREATE TABLE IGNORE_WARNINGS(RTAG_ID NUMBER NOT NULL,PV_ID NUMBER NOT NULL,DPV_ID NUMBER NOT NULL,IS_PATCH_IGNORE CHAR(1 BYTE))TABLESPACE "USERS"LOGGINGPCTFREE 10PCTUSED 0INITRANS 1MAXTRANS 255STORAGE(INITIAL 128KMINEXTENTS 1MAXEXTENTS 2147483645FREELISTS 1FREELIST GROUPS 1BUFFER_POOL DEFAULT);CREATE TABLE DEPRECATED_PACKAGES(RTAG_ID NUMBER NOT NULL,PKG_ID NUMBER NOT NULL,COMMENTS VARCHAR2(4000 BYTE) NOT NULL,V_EXT VARCHAR2(50 BYTE))TABLESPACE "USERS"LOGGINGPCTFREE 10PCTUSED 0INITRANS 1MAXTRANS 255STORAGE(INITIAL 64KMINEXTENTS 1MAXEXTENTS 2147483645FREELISTS 1FREELIST GROUPS 1BUFFER_POOL DEFAULT);CREATE TABLE RELEASE_CONTENT(RTAG_ID NUMBER NOT NULL,PV_ID NUMBER NOT NULL,BASE_VIEW_ID NUMBER NOT NULL,INSERT_STAMP DATE NOT NULL,INSERTOR_ID NUMBER NOT NULL,PKG_STATE NUMBER NOT NULL,PKG_ID NUMBER,DEPRECATED_STATE NUMBER,PRODUCT_STATE NUMBER)TABLESPACE "SYSTEM"LOGGINGPCTFREE 10PCTUSED 40INITRANS 1MAXTRANS 255STORAGE(INITIAL 3520KNEXT 16KMINEXTENTS 1MAXEXTENTS 2147483645PCTINCREASE 0FREELISTS 1FREELIST GROUPS 1BUFFER_POOL DEFAULT);CREATE TABLE MICROSOFTDTPROPERTIES(ID NUMBER NOT NULL,OBJECTID NUMBER,PROPERTY VARCHAR2(64 BYTE) NOT NULL,VALUE VARCHAR2(255 BYTE),LVALUE LONG RAW,VERSION NUMBER DEFAULT (0) NOT NULL, CONSTRAINT MICROSOFT_PK_DTPROPERTIES PRIMARY KEY(ID,PROPERTY)ENABLE)TABLESPACE "USERS"LOGGINGPCTFREE 10PCTUSED 0INITRANS 1MAXTRANS 255STORAGE(INITIAL 128KMINEXTENTS 1MAXEXTENTS 2147483645FREELISTS 1FREELIST GROUPS 1BUFFER_POOL DEFAULT);CREATE TABLE PROCESSES(PROC_ID NUMBER NOT NULL,PROC_NAME VARCHAR2(255 BYTE) NOT NULL,PROC_DESCRIPTION VARCHAR2(255 BYTE),RUN_AS VARCHAR2(255 BYTE),PKG_OWNER VARCHAR2(4000 BYTE),IS_INTERFACE CHAR(1 BYTE), CONSTRAINT PROCESSES_PK PRIMARY KEY(PROC_ID)ENABLE)TABLESPACE "USERS"LOGGINGPCTFREE 10PCTUSED 0INITRANS 1MAXTRANS 255STORAGE(INITIAL 128KMINEXTENTS 1MAXEXTENTS 2147483645FREELISTS 1FREELIST GROUPS 1BUFFER_POOL DEFAULT);CREATE TABLE PACKAGE_DOCUMENTS(PV_ID NUMBER NOT NULL,TEST_ID NUMBER,DOC_ID NUMBER NOT NULL,DOC_NUM VARCHAR2(50 BYTE) NOT NULL,IS_LATEST CHAR(1 BYTE))TABLESPACE "USERS"LOGGINGPCTFREE 10PCTUSED 0INITRANS 1MAXTRANS 255STORAGE(INITIAL 128KMINEXTENTS 1MAXEXTENTS 2147483645FREELISTS 1FREELIST GROUPS 1BUFFER_POOL DEFAULT);CREATE TABLE RELEASE_TAGS(RTAG_ID NUMBER NOT NULL,VTREE_ID NUMBER,RTAG_NAME VARCHAR2(50 BYTE) NOT NULL,DESCRIPTION VARCHAR2(4000 BYTE),CREATED_STAMP DATE,CREATOR_ID NUMBER,OFFICIAL_STAMP DATE,RELEASOR_ID NUMBER,OFFICIAL CHAR(1 BYTE) NOT NULL,REBUILD_ENV CHAR(1 BYTE) NOT NULL,REBUILD_STAMP NUMBER NOT NULL,RTAG_VERSION VARCHAR2(4000 BYTE),RTAG_LIFE_CYCLE NUMBER,PARENT_RTAG_ID NUMBER NOT NULL,PROJ_ID NUMBER NOT NULL,DISPLAY_ORDER NUMBER,OWNER_EMAIL VARCHAR2(1000 BYTE),ASSOC_MASS_REF NUMBER,OWNER_PERSONAL_EMAIL VARCHAR2(1000 BYTE),CONFIG_SPEC_BRANCH VARCHAR2(4000 BYTE),PRODUCT_STATE_USED CHAR(1 BYTE), CONSTRAINT PK_RELEASE_TAGS PRIMARY KEY(RTAG_ID)ENABLE)TABLESPACE "SYSTEM"LOGGINGPCTFREE 10PCTUSED 40INITRANS 1MAXTRANS 255STORAGE(INITIAL 80KNEXT 16KMINEXTENTS 1MAXEXTENTS 2147483645PCTINCREASE 0FREELISTS 1FREELIST GROUPS 1BUFFER_POOL DEFAULT);CREATE TABLE BUILD_STANDARDS_ADDENDUM(BSA_ID NUMBER NOT NULL,BSA_NAME VARCHAR2(4000 BYTE) NOT NULL,BS_ID NUMBER NOT NULL, CONSTRAINT BUILD_STANDARDS_ADDENDUM_PK PRIMARY KEY(BSA_ID)ENABLE)TABLESPACE "USERS"LOGGINGPCTFREE 10PCTUSED 40INITRANS 1MAXTRANS 255STORAGE(INITIAL 64KMINEXTENTS 1MAXEXTENTS 2147483645FREELISTS 1FREELIST GROUPS 1BUFFER_POOL DEFAULT);CREATE TABLE UNIT_TESTS(TEST_ID NUMBER NOT NULL,PV_ID NUMBER NOT NULL,TEST_TYPES_FK NUMBER NOT NULL,TEST_SUMMARY VARCHAR2(4000 BYTE),COMPLETION_DATE DATE,COMPLETED_BY NUMBER,RESULTS_URL VARCHAR2(2000 BYTE),RESULTS_ATTACHMENT_NAME VARCHAR2(2000 BYTE),TEST_ACCEPTED CHAR(1 BYTE),ACCEPTANCE_DATE DATE,ACCEPTED_BY NUMBER,REVIEW_COMMENTS VARCHAR2(4000 BYTE),NUMOF_TEST VARCHAR2(50 BYTE))TABLESPACE "USERS"LOGGINGPCTFREE 10PCTUSED 0INITRANS 1MAXTRANS 255STORAGE(INITIAL 128KMINEXTENTS 1MAXEXTENTS 2147483645FREELISTS 1FREELIST GROUPS 1BUFFER_POOL DEFAULT);CREATE TABLE RM_PACKAGE_ISSUES(PKG_ID NUMBER,PKG_NAME VARCHAR2(50 BYTE),PV_ID NUMBER,PKG_VERSION VARCHAR2(50 BYTE),DPV_ID NUMBER,DPKG_NAME VARCHAR2(50 BYTE),DPKG_VERSION VARCHAR2(50 BYTE),ISS_DB NUMBER,ISS_ID NUMBER)TABLESPACE "USERS"LOGGINGPCTFREE 10PCTUSED 0INITRANS 1MAXTRANS 255STORAGE(INITIAL 64KMINEXTENTS 1MAXEXTENTS 2147483645FREELISTS 1FREELIST GROUPS 1BUFFER_POOL DEFAULT);CREATE TABLE BUILD_ENVIRONMENTS(BE_ID NUMBER NOT NULL,BE_NAME VARCHAR2(255 BYTE) NOT NULL, CONSTRAINT PK_BUILD_ENVIRONMENTS PRIMARY KEY(BE_ID)ENABLE)TABLESPACE "USERS"LOGGINGPCTFREE 10PCTUSED 0INITRANS 1MAXTRANS 255STORAGE(INITIAL 128KMINEXTENTS 1MAXEXTENTS 2147483645FREELISTS 1FREELIST GROUPS 1BUFFER_POOL DEFAULT);CREATE TABLE PACKAGE_PROCESSES(PV_ID NUMBER NOT NULL,PROC_ID NUMBER NOT NULL)TABLESPACE "USERS"LOGGINGPCTFREE 10PCTUSED 0INITRANS 1MAXTRANS 255STORAGE(INITIAL 64KMINEXTENTS 1MAXEXTENTS 2147483645FREELISTS 1FREELIST GROUPS 1BUFFER_POOL DEFAULT);CREATE TABLE RELEASE_COMPONENTS(PV_ID NUMBER NOT NULL,FILE_NAME VARCHAR2(255 BYTE),FILE_PATH VARCHAR2(2000 BYTE),BYTE_SIZE NUMBER,CRC_CKSUM VARCHAR2(50 BYTE),CRC_MODCRC VARCHAR2(50 BYTE))TABLESPACE "USERS"LOGGINGPCTFREE 10PCTUSED 0INITRANS 1MAXTRANS 255STORAGE(INITIAL 128KMINEXTENTS 1MAXEXTENTS 2147483645FREELISTS 1FREELIST GROUPS 1BUFFER_POOL DEFAULT);CREATE TABLE PACKAGE_PATCHES(PV_ID NUMBER NOT NULL,PATCH_ID NUMBER NOT NULL,INSTALL_ORDER NUMBER NOT NULL,PATCH_OBSOLETED_BY NUMBER)TABLESPACE "USERS"LOGGINGPCTFREE 10PCTUSED 0INITRANS 1MAXTRANS 255STORAGE(INITIAL 128KMINEXTENTS 1MAXEXTENTS 2147483645FREELISTS 1FREELIST GROUPS 1BUFFER_POOL DEFAULT);CREATE TABLE BUILD_ORDER(RTAG_ID NUMBER NOT NULL,STEP_NUM NUMBER NOT NULL,PV_ID NUMBER NOT NULL,UNRESOLVED CHAR(1 BYTE))TABLESPACE "USERS"LOGGINGPCTFREE 10PCTUSED 0INITRANS 1MAXTRANS 255STORAGE(INITIAL 64KMINEXTENTS 1MAXEXTENTS 2147483645FREELISTS 1FREELIST GROUPS 1BUFFER_POOL DEFAULT);CREATE TABLE PLANNED(RTAG_ID NUMBER NOT NULL,PV_ID NUMBER NOT NULL,VIEW_ID NUMBER NOT NULL)TABLESPACE "USERS"LOGGINGPCTFREE 10PCTUSED 0INITRANS 1MAXTRANS 255STORAGE(INITIAL 64KMINEXTENTS 1MAXEXTENTS 2147483645FREELISTS 1FREELIST GROUPS 1BUFFER_POOL DEFAULT);CREATE TABLE PROJECT_ACTION_LOG(USER_ID NUMBER NOT NULL,ACTION_DATETIME DATE NOT NULL,PROJ_ID NUMBER NOT NULL,DESCRIPTION VARCHAR2(4000 BYTE),ACTTYPE_ID NUMBER NOT NULL,RTAG_ID NUMBER)TABLESPACE "USERS"LOGGINGPCTFREE 10PCTUSED 0INITRANS 1MAXTRANS 255STORAGE(INITIAL 64KMINEXTENTS 1MAXEXTENTS 2147483645FREELISTS 1FREELIST GROUPS 1BUFFER_POOL DEFAULT);CREATE TABLE PACKAGE_INTEREST(PKG_ID NUMBER NOT NULL,PROJ_ID NUMBER NOT NULL,USER_ID NUMBER NOT NULL)TABLESPACE "USERS"LOGGINGPCTFREE 10PCTUSED 0INITRANS 1MAXTRANS 255STORAGE(INITIAL 64KMINEXTENTS 1MAXEXTENTS 2147483645FREELISTS 1FREELIST GROUPS 1BUFFER_POOL DEFAULT);CREATE TABLE PLATFORMS(CODE NUMBER NOT NULL,NAME VARCHAR2(255 BYTE) NOT NULL)TABLESPACE "USERS"LOGGINGPCTFREE 10PCTUSED 0INITRANS 1MAXTRANS 255STORAGE(INITIAL 64KMINEXTENTS 1MAXEXTENTS 2147483645FREELISTS 1FREELIST GROUPS 1BUFFER_POOL DEFAULT);CREATE TABLE ARCHIVE_DATA(RTAG_ID NUMBER(*, 0) NOT NULL,PV_ID NUMBER(*, 0) NOT NULL)TABLESPACE "USERS"LOGGINGPCTFREE 10PCTUSED 0INITRANS 1MAXTRANS 255STORAGE(INITIAL 64KMINEXTENTS 1MAXEXTENTS 2147483645FREELISTS 1FREELIST GROUPS 1BUFFER_POOL DEFAULT);CREATE TABLE VIEW_SETTINGS(USER_ID NUMBER NOT NULL,VIEW_ID NUMBER NOT NULL)TABLESPACE "SYSTEM"LOGGINGPCTFREE 10PCTUSED 40INITRANS 1MAXTRANS 255STORAGE(INITIAL 80KNEXT 16KMINEXTENTS 1MAXEXTENTS 2147483645PCTINCREASE 0FREELISTS 1FREELIST GROUPS 1BUFFER_POOL DEFAULT);CREATE TABLE ACTION_TYPE(ACTTYPE_ID NUMBER NOT NULL,NAME VARCHAR2(255 BYTE) NOT NULL,DESCRIPTION VARCHAR2(4000 BYTE), CONSTRAINT UNQ_ACTTYPE_ID PRIMARY KEY(ACTTYPE_ID)ENABLE)TABLESPACE "USERS"LOGGINGPCTFREE 10PCTUSED 0INITRANS 1MAXTRANS 255STORAGE(INITIAL 128KMINEXTENTS 1MAXEXTENTS 2147483645FREELISTS 1FREELIST GROUPS 1BUFFER_POOL DEFAULT);CREATE TABLE AUTOBUILD_FAILURE(GROUP_EMAIL_ID NUMBER NOT NULL,PROJ_ID NUMBER,VIEW_ID NUMBER, PRIMARY KEY(GROUP_EMAIL_ID)ENABLE)TABLESPACE "USERS"LOGGINGPCTFREE 10PCTUSED 0INITRANS 1MAXTRANS 255STORAGE(INITIAL 64KMINEXTENTS 1MAXEXTENTS 2147483645FREELISTS 1FREELIST GROUPS 1BUFFER_POOL DEFAULT);CREATE TABLE JIRA_ISSUES(PV_ID NUMBER NOT NULL,ISS_KEY VARCHAR2(4000 BYTE),DATE_TIME_STAMP DATE)TABLESPACE "SYSTEM"LOGGINGPCTFREE 10PCTUSED 40INITRANS 1MAXTRANS 255STORAGE(INITIAL 80KNEXT 16KMINEXTENTS 1MAXEXTENTS 2147483645PCTINCREASE 0FREELISTS 1FREELIST GROUPS 1BUFFER_POOL DEFAULT);CREATE TABLE VIEW_DEF(VIEW_ID NUMBER NOT NULL,PKG_ID NUMBER NOT NULL)TABLESPACE "SYSTEM"LOGGINGPCTFREE 10PCTUSED 40INITRANS 1MAXTRANS 255STORAGE(INITIAL 80KNEXT 16KMINEXTENTS 1MAXEXTENTS 2147483645PCTINCREASE 0FREELISTS 1FREELIST GROUPS 1BUFFER_POOL DEFAULT);CREATE TABLE VTREES(VTREE_ID NUMBER NOT NULL,PROJ_ID NUMBER NOT NULL,VTREE_NAME VARCHAR2(50 BYTE) NOT NULL,HIDE CHAR(1 BYTE) NOT NULL, CONSTRAINT PK_VTREES PRIMARY KEY(VTREE_ID)ENABLE)TABLESPACE "SYSTEM"LOGGINGPCTFREE 10PCTUSED 40INITRANS 1MAXTRANS 255STORAGE(INITIAL 80KNEXT 16KMINEXTENTS 1MAXEXTENTS 2147483645PCTINCREASE 0FREELISTS 1FREELIST GROUPS 1BUFFER_POOL DEFAULT);CREATE TABLE BUILD_STANDARDS(BS_ID NUMBER NOT NULL,BS_NAME VARCHAR2(20 BYTE) NOT NULL, CONSTRAINT BUILD_STANDARDS_PK PRIMARY KEY(BS_ID)ENABLE)TABLESPACE "USERS"LOGGINGPCTFREE 10PCTUSED 40INITRANS 1MAXTRANS 255STORAGE(INITIAL 64KMINEXTENTS 1MAXEXTENTS 2147483645FREELISTS 1FREELIST GROUPS 1BUFFER_POOL DEFAULT);CREATE TABLE TEMP_SASH(RTAG_ID NUMBER,PROJ_ID NUMBER)TABLESPACE "USERS"LOGGINGPCTFREE 10PCTUSED 0INITRANS 1MAXTRANS 255STORAGE(INITIAL 64KMINEXTENTS 1MAXEXTENTS 2147483645FREELISTS 1FREELIST GROUPS 1BUFFER_POOL DEFAULT);CREATE TABLE BUILD_MACHINES(BM_ID NUMBER NOT NULL,BM_NAME VARCHAR2(20 BYTE) NOT NULL, CONSTRAINT BUILD_MACHINES_PK PRIMARY KEY(BM_ID)ENABLE)TABLESPACE "USERS"LOGGINGPCTFREE 10PCTUSED 40INITRANS 1MAXTRANS 255STORAGE(INITIAL 64KMINEXTENTS 1MAXEXTENTS 2147483645FREELISTS 1FREELIST GROUPS 1BUFFER_POOL DEFAULT);CREATE TABLE WORK_IN_PROGRESS(RTAG_ID NUMBER NOT NULL,PV_ID NUMBER NOT NULL,VIEW_ID NUMBER NOT NULL)TABLESPACE "USERS"LOGGINGPCTFREE 10PCTUSED 0INITRANS 1MAXTRANS 255STORAGE(INITIAL 64KMINEXTENTS 1MAXEXTENTS 2147483645FREELISTS 1FREELIST GROUPS 1BUFFER_POOL DEFAULT);CREATE TABLE ADVISORY_RIPPLES(RTAG_ID NUMBER NOT NULL,PV_ID NUMBER NOT NULL)TABLESPACE "USERS"LOGGINGPCTFREE 10PCTUSED 40INITRANS 1MAXTRANS 255STORAGE(INITIAL 64KMINEXTENTS 1MAXEXTENTS 2147483645FREELISTS 1FREELIST GROUPS 1BUFFER_POOL DEFAULT);CREATE TABLE RELEASE_LINKS(RTAG_ID NUMBER NOT NULL,REF_RTAG_ID NUMBER NOT NULL)TABLESPACE "USERS"LOGGINGPCTFREE 10PCTUSED 0INITRANS 1MAXTRANS 255STORAGE(INITIAL 64KMINEXTENTS 1MAXEXTENTS 2147483645FREELISTS 1FREELIST GROUPS 1BUFFER_POOL DEFAULT);CREATE TABLE BUILD_ENV_DOCUMENTS(BE_ID NUMBER NOT NULL,DOC_NUM VARCHAR2(255 BYTE) NOT NULL)TABLESPACE "USERS"LOGGINGPCTFREE 10PCTUSED 0INITRANS 1MAXTRANS 255STORAGE(INITIAL 128KMINEXTENTS 1MAXEXTENTS 2147483645FREELISTS 1FREELIST GROUPS 1BUFFER_POOL DEFAULT);CREATE TABLE TEST_TYPES(TEST_TYPE_ID NUMBER NOT NULL,TEST_TYPE_NAME VARCHAR2(50 BYTE) NOT NULL,DISPLAY_ORDER NUMBER, CONSTRAINT PK_TEST_TYPES PRIMARY KEY(TEST_TYPE_ID)ENABLE)TABLESPACE "USERS"LOGGINGPCTFREE 10PCTUSED 0INITRANS 1MAXTRANS 255STORAGE(INITIAL 128KMINEXTENTS 1MAXEXTENTS 2147483645FREELISTS 1FREELIST GROUPS 1BUFFER_POOL DEFAULT);CREATE TABLE PACKAGE_DEPENDENCIES(PV_ID NUMBER NOT NULL,DPV_ID NUMBER NOT NULL,PKG_ID NUMBER NOT NULL,DPKG_ID NUMBER NOT NULL,BUILD_TYPE CHAR(1 BYTE) NOT NULL,DISPLAY_ORDER NUMBER)TABLESPACE "SYSTEM"LOGGINGPCTFREE 10PCTUSED 40INITRANS 1MAXTRANS 255STORAGE(INITIAL 24160KNEXT 16KMINEXTENTS 1MAXEXTENTS 2147483645PCTINCREASE 0FREELISTS 1FREELIST GROUPS 1BUFFER_POOL DEFAULT);CREATE TABLE PRODUCT_COMPONENTS(PV_ID NUMBER NOT NULL,OS_ID NUMBER NOT NULL,FILE_PATH VARCHAR2(4000 BYTE),FILE_NAME VARCHAR2(4000 BYTE),DESTINATION_PATH VARCHAR2(4000 BYTE),BYTE_SIZE NUMBER,CRC_CKSUM VARCHAR2(2000 BYTE))TABLESPACE "USERS"LOGGINGPCTFREE 10PCTUSED 0INITRANS 1MAXTRANS 255STORAGE(INITIAL 64KMINEXTENTS 1MAXEXTENTS 2147483645FREELISTS 1FREELIST GROUPS 1BUFFER_POOL DEFAULT);CREATE GLOBAL TEMPORARY TABLE RM_PKG_ISSUES(PKG_ID NUMBER,PKG_NAME VARCHAR2(50 BYTE),PV_ID NUMBER,PKG_VERSION VARCHAR2(50 BYTE),DPV_ID NUMBER,DPKG_NAME VARCHAR2(50 BYTE),DPKG_VERSION VARCHAR2(50 BYTE),ISS_DB NUMBER,ISS_ID NUMBER)ON COMMIT PRESERVE ROWS;CREATE TABLE PRODUCT_STATES(STATE_ID NUMBER NOT NULL,STATE VARCHAR2(4000 BYTE) NOT NULL, CONSTRAINT PRODUCT_STATES_PK PRIMARY KEY(STATE_ID)ENABLE)TABLESPACE "USERS"LOGGINGPCTFREE 10PCTUSED 40INITRANS 1MAXTRANS 255STORAGE(INITIAL 64KMINEXTENTS 1MAXEXTENTS 2147483645FREELISTS 1FREELIST GROUPS 1BUFFER_POOL DEFAULT);CREATE TABLE PACKAGE_BUILD_INFO(PV_ID NUMBER,BM_ID NUMBER,BSA_ID NUMBER)TABLESPACE "USERS"LOGGINGPCTFREE 10PCTUSED 40INITRANS 1MAXTRANS 255STORAGE(INITIAL 64KMINEXTENTS 1MAXEXTENTS 2147483645FREELISTS 1FREELIST GROUPS 1BUFFER_POOL DEFAULT);CREATE TABLE PACKAGES(PKG_ID NUMBER NOT NULL,PKG_NAME VARCHAR2(255 BYTE) NOT NULL,SUNOS_ELECTRONIC_NAME VARCHAR2(255 BYTE),WIN_ELECTRONIC_NAME VARCHAR2(255 BYTE), CONSTRAINT PK_PACKAGES PRIMARY KEY(PKG_ID)ENABLE)TABLESPACE "SYSTEM"LOGGINGPCTFREE 10PCTUSED 40INITRANS 1MAXTRANS 255STORAGE(INITIAL 160KNEXT 16KMINEXTENTS 1MAXEXTENTS 2147483645PCTINCREASE 0FREELISTS 1FREELIST GROUPS 1BUFFER_POOL DEFAULT);CREATE TABLE MEMBERS_GROUP(GROUP_EMAIL_ID NUMBER NOT NULL,USER_ID NUMBER)TABLESPACE "USERS"LOGGINGPCTFREE 10PCTUSED 0INITRANS 1MAXTRANS 255STORAGE(INITIAL 64KMINEXTENTS 1MAXEXTENTS 2147483645FREELISTS 1FREELIST GROUPS 1BUFFER_POOL DEFAULT);CREATE TABLE PROJECTS(PROJ_ID NUMBER NOT NULL,PROJ_NAME VARCHAR2(50 BYTE) NOT NULL, CONSTRAINT PK_PROJECTS PRIMARY KEY(PROJ_ID)ENABLE)TABLESPACE "SYSTEM"LOGGINGPCTFREE 10PCTUSED 40INITRANS 1MAXTRANS 255STORAGE(INITIAL 80KNEXT 16KMINEXTENTS 1MAXEXTENTS 2147483645PCTINCREASE 0FREELISTS 1FREELIST GROUPS 1BUFFER_POOL DEFAULT);CREATE TABLE WORLDS(WORLD_ID NUMBER NOT NULL,WORLD_NAME VARCHAR2(30 BYTE) NOT NULL,WORLD_DESC VARCHAR2(255 BYTE), CONSTRAINT PK_WORLDS PRIMARY KEY(WORLD_ID)ENABLE)TABLESPACE "USERS"LOGGINGPCTFREE 10PCTUSED 0INITRANS 1MAXTRANS 255STORAGE(INITIAL 128KMINEXTENTS 1MAXEXTENTS 2147483645FREELISTS 1FREELIST GROUPS 1BUFFER_POOL DEFAULT);CREATE TABLE VIEWS(VIEW_ID NUMBER NOT NULL,VIEW_NAME VARCHAR2(30 BYTE) NOT NULL,OWNER_ID NUMBER NOT NULL,BASE_VIEW CHAR(1 BYTE) NOT NULL,PUBLIC_READ CHAR(1 BYTE) NOT NULL, CONSTRAINT PK_VIEWS PRIMARY KEY(VIEW_ID)ENABLE)TABLESPACE "SYSTEM"LOGGINGPCTFREE 10PCTUSED 40INITRANS 1MAXTRANS 255STORAGE(INITIAL 80KNEXT 16KMINEXTENTS 1MAXEXTENTS 2147483645PCTINCREASE 0FREELISTS 1FREELIST GROUPS 1BUFFER_POOL DEFAULT);CREATE TABLE ACTION_LOG(USER_ID NUMBER NOT NULL,ACTION_DATETIME DATE NOT NULL,PV_ID NUMBER NOT NULL,DESCRIPTION VARCHAR2(4000 BYTE),ACTTYPE_ID NUMBER NOT NULL)TABLESPACE "USERS"LOGGINGPCTFREE 10PCTUSED 0INITRANS 1MAXTRANS 255STORAGE(INITIAL 128KMINEXTENTS 1MAXEXTENTS 2147483645FREELISTS 1FREELIST GROUPS 1BUFFER_POOL DEFAULT);CREATE TABLE RUNTIME_DEPENDENCIES(PV_ID NUMBER NOT NULL,RTD_ID NUMBER,RTD_COMMENTS VARCHAR2(2000 BYTE),RTD_URL VARCHAR2(2000 BYTE),MOD_DATE DATE NOT NULL,MOD_USER NUMBER NOT NULL)TABLESPACE "USERS"LOGGINGPCTFREE 10PCTUSED 40INITRANS 1MAXTRANS 255STORAGE(INITIAL 64KMINEXTENTS 1MAXEXTENTS 2147483645FREELISTS 1FREELIST GROUPS 1BUFFER_POOL DEFAULT);CREATE TABLE PROJECT_EXTENTIONS(PROJ_ID NUMBER NOT NULL,EXT_NAME VARCHAR2(10 BYTE) NOT NULL)TABLESPACE "SYSTEM"LOGGINGPCTFREE 10PCTUSED 40INITRANS 1MAXTRANS 255STORAGE(INITIAL 80KNEXT 16KMINEXTENTS 1MAXEXTENTS 2147483645PCTINCREASE 0FREELISTS 1FREELIST GROUPS 1BUFFER_POOL DEFAULT);CREATE TABLE ADDITIONAL_NOTES(NOTE_ID NUMBER NOT NULL,PV_ID NUMBER NOT NULL,NOTE_TITLE VARCHAR2(2000 BYTE) NOT NULL,NOTE_BODY VARCHAR2(4000 BYTE),MOD_DATE DATE NOT NULL,MOD_USER NUMBER NOT NULL)TABLESPACE "USERS"LOGGINGPCTFREE 10PCTUSED 0INITRANS 1MAXTRANS 255STORAGE(INITIAL 128KMINEXTENTS 1MAXEXTENTS 2147483645FREELISTS 1FREELIST GROUPS 1BUFFER_POOL DEFAULT);CREATE TABLE PACKAGE_VERSIONS(PV_ID NUMBER NOT NULL,PKG_ID NUMBER NOT NULL,PKG_VERSION VARCHAR2(50 BYTE) NOT NULL,DLOCKED CHAR(1 BYTE) NOT NULL,RELEASED_AT NUMBER,MODIFIED_STAMP DATE,MODIFIER_ID NUMBER NOT NULL,CREATED_STAMP DATE,CREATOR_ID NUMBER NOT NULL,COMMENTS VARCHAR2(4000 BYTE),V_MM VARCHAR2(50 BYTE),V_NMM VARCHAR2(50 BYTE),V_EXT VARCHAR2(50 BYTE),PKG_LABEL VARCHAR2(60 BYTE),SRC_PATH VARCHAR2(2000 BYTE),PV_DESCRIPTION VARCHAR2(4000 BYTE),OWNER_ID NUMBER NOT NULL,PV_OVERVIEW VARCHAR2(4000 BYTE),IS_PATCH CHAR(1 BYTE),LAST_PV_ID NUMBER NOT NULL,RELEASE_NOTES_INFO VARCHAR2(1000 BYTE),IS_DEPLOYABLE CHAR(1 BYTE),IS_BUILD_ENV_REQUIRED CHAR(1 BYTE),IS_OBSOLETE CHAR(1 BYTE),OBSOLETE_COMMENTS VARCHAR2(4000 BYTE),BUILD_TYPE CHAR(1 BYTE) NOT NULL,CHANGE_TYPE CHAR(1 BYTE),LINK VARCHAR2(4000 BYTE),PATCH_ELECTRONIC_NAME VARCHAR2(4000 BYTE),BS_ID NUMBER,IS_AUTOBUILDABLE CHAR(1 CHAR),SBOM_PRIORITY CHAR(1 BYTE),RIPPLE_FIELD CHAR(1 CHAR),MAXIMUM_BUILD_TIME NUMBER,ESTIMATED_COMPLETION_TIME DATE, CONSTRAINT PK_PACKAGE_VERSIONS PRIMARY KEY(PV_ID)ENABLE)TABLESPACE "SYSTEM"LOGGINGPCTFREE 10PCTUSED 40INITRANS 1MAXTRANS 255STORAGE(INITIAL 32120KNEXT 16KMINEXTENTS 1MAXEXTENTS 2147483645PCTINCREASE 0FREELISTS 1FREELIST GROUPS 1BUFFER_POOL DEFAULT);CREATE TABLE MESSAGE_BOARD(MSG_ID NUMBER NOT NULL,MSG_DETAILS VARCHAR2(2000 BYTE) NOT NULL,SUBMITION_DATE DATE NOT NULL,EXPIRY_DATE DATE,DUE_DATE DATE, CONSTRAINT PK_MESSAGE_BOARD PRIMARY KEY(MSG_ID)ENABLE)TABLESPACE "SYSTEM"LOGGINGPCTFREE 10PCTUSED 40INITRANS 1MAXTRANS 255STORAGE(INITIAL 80KNEXT 16KMINEXTENTS 1MAXEXTENTS 2147483645PCTINCREASE 0FREELISTS 1FREELIST GROUPS 1BUFFER_POOL DEFAULT);CREATE TABLE VTREES_WORLD(WORLD_ID NUMBER NOT NULL,VTREE_ID NUMBER NOT NULL)TABLESPACE "USERS"LOGGINGPCTFREE 10PCTUSED 0INITRANS 1MAXTRANS 255STORAGE(INITIAL 128KMINEXTENTS 1MAXEXTENTS 2147483645FREELISTS 1FREELIST GROUPS 1BUFFER_POOL DEFAULT);CREATE TABLE CODE_REVIEWS(PV_ID NUMBER NOT NULL,DATE_OF_REVIEW DATE,TIME_SPENT FLOAT(126),REVIEW_REASON VARCHAR2(4000 BYTE),RTEAM_DOMAIN_EXPERT VARCHAR2(4000 BYTE),RTEAM_LANGUAGE_EXPERT VARCHAR2(4000 BYTE),RTEAM_PEER_DEVELOPER VARCHAR2(4000 BYTE),RTEAM_AUTHOR VARCHAR2(4000 BYTE),FILES_REVIEWED VARCHAR2(4000 BYTE),REVIEW_RESULTS NUMBER,ISSUES_RAISED VARCHAR2(4000 BYTE),REVIEW_COMMENTS VARCHAR2(4000 BYTE),FNC_S_MEETS_FUNCTIONALITY CHAR(1 BYTE),FNC_C_MEETS_FUNCTIONALITY VARCHAR2(4000 BYTE),RBS_S_BOUND_COND_HANDLED CHAR(1 BYTE),RBS_C_BOUND_COND_HANDLED VARCHAR2(4000 BYTE),RBS_S_CLASS_INTERF_PRECOND CHAR(1 BYTE),RBS_C_CLASS_INTERF_PRECOND VARCHAR2(4000 BYTE),RBS_S_NO_UNITIALISED_DATE CHAR(1 BYTE),RBS_C_NO_UNITIALISED_DATE VARCHAR2(4000 BYTE),RBS_S_EXCEP_HANDING CHAR(1 BYTE),RBS_C_EXCEP_HANDING VARCHAR2(4000 BYTE),RBS_S_RESOURCE_MNG CHAR(1 BYTE),RBS_C_RESOURCE_MNG VARCHAR2(4000 BYTE),RBS_S_TRANS_COMPLET CHAR(1 BYTE),RBS_C_TRANS_COMPLET VARCHAR2(4000 BYTE),RBS_S_THREADSAFETY CHAR(1 BYTE),RBS_C_THREADSAFETY VARCHAR2(4000 BYTE),RBS_S_RET_VALS CHAR(1 BYTE),RBS_C_RET_VALS VARCHAR2(4000 BYTE),RBS_S_CORR_ERR_HANDLING CHAR(1 BYTE),RBS_C_CORR_ERR_HANDLING VARCHAR2(4000 BYTE),RBS_S_SQL_STD CHAR(1 BYTE),RBS_C_SQL_STD VARCHAR2(4000 BYTE),MNT_S_EXT_REF CHAR(1 BYTE),MNT_C_EXT_REF VARCHAR2(4000 BYTE),MNT_S_CLASS_SIZE CHAR(1 BYTE),MNT_C_CLASS_SIZE VARCHAR2(4000 BYTE),MNT_S_METHOD_SIZE CHAR(1 BYTE),MNT_C_METHOD_SIZE VARCHAR2(4000 BYTE),MNT_S_APPROP_COMM CHAR(1 BYTE),MNT_C_APPROP_COMM VARCHAR2(4000 BYTE),MNT_S_IDENT_NAME_STD CHAR(1 BYTE),MNT_C_IDENT_NAME_STD VARCHAR2(4000 BYTE),MNT_S_SWITCH_HAVE_DEFAULTS CHAR(1 BYTE),MNT_C_SWITCH_HAVE_DEFAULTS VARCHAR2(4000 BYTE),MNT_S_NO_LIT_NUM CHAR(1 BYTE),MNT_C_NO_LIT_NUM VARCHAR2(4000 BYTE),MNT_S_NO_DEAD_CODE CHAR(1 BYTE),MNT_C_NO_DEAD_CODE VARCHAR2(4000 BYTE),DEI_S_STD_DES_PATT CHAR(1 BYTE),DEI_C_STD_DES_PATT VARCHAR2(4000 BYTE),DEI_S_APPROP_ALGOR CHAR(1 BYTE),DEI_C_APPROP_ALGOR VARCHAR2(4000 BYTE),DEI_S_APPROP_OBJ CHAR(1 BYTE),DEI_C_APPROP_OBJ VARCHAR2(4000 BYTE),DEI_S_APPROP_ERR_MSG CHAR(1 BYTE),DEI_C_APPROP_ERR_MSG VARCHAR2(4000 BYTE),DEI_S_STD_FRAMEW_COMP CHAR(1 BYTE),DEI_C_STD_FRAMEW_COMP VARCHAR2(4000 BYTE),DEI_S_APPROP_LOGGING CHAR(1 BYTE),DEI_C_APPROP_LOGGING VARCHAR2(4000 BYTE))TABLESPACE "USERS"LOGGINGPCTFREE 10PCTUSED 0INITRANS 1MAXTRANS 255STORAGE(INITIAL 128KMINEXTENTS 1MAXEXTENTS 2147483645FREELISTS 1FREELIST GROUPS 1BUFFER_POOL DEFAULT);CREATE TABLE DEF_ACTION_BUTTONS(ABTN_ID NUMBER NOT NULL,ABTN_NAME VARCHAR2(1000 BYTE) NOT NULL,TEXT VARCHAR2(50 BYTE),ACTION_LINK VARCHAR2(4000 BYTE),EVENT_HANDLER VARCHAR2(4000 BYTE),IMG_ENABLED VARCHAR2(1000 BYTE),IMG_DISABLED VARCHAR2(1000 BYTE),HINT VARCHAR2(255 BYTE),VISIBLE CHAR(1 BYTE) NOT NULL,ACTIVE CHAR(1 BYTE) NOT NULL,IS_READONLY_ACTION CHAR(1 BYTE) NOT NULL, CONSTRAINT PK_DEF_ACTION_BUTTONS PRIMARY KEY(ABTN_ID)ENABLE)TABLESPACE "USERS"LOGGINGPCTFREE 10PCTUSED 0INITRANS 1MAXTRANS 255STORAGE(INITIAL 64KMINEXTENTS 1MAXEXTENTS 2147483645FREELISTS 1FREELIST GROUPS 1BUFFER_POOL DEFAULT);CREATE TABLE CQ_ISSUES(PV_ID NUMBER NOT NULL,ISS_DB NUMBER NOT NULL,ISS_ID NUMBER NOT NULL,ISS_STATE NUMBER NOT NULL,MOD_DATE DATE NOT NULL,NOTES VARCHAR2(255 BYTE))TABLESPACE "SYSTEM"LOGGINGPCTFREE 10PCTUSED 40INITRANS 1MAXTRANS 255STORAGE(INITIAL 1760KNEXT 16KMINEXTENTS 1MAXEXTENTS 2147483645PCTINCREASE 0FREELISTS 1FREELIST GROUPS 1BUFFER_POOL DEFAULT);CREATE TABLE VALIDATION_RULES(FIELD_NAME VARCHAR2(1000 BYTE) NOT NULL,IS_REQUIRED CHAR(1 BYTE) NOT NULL,IS_NUMERIC CHAR(1 BYTE),MIN_NUMERIC_VALUE NUMBER,MAX_NUMERIC_VALUE NUMBER,IS_DATE CHAR(1 BYTE),START_DATE DATE,END_DATE DATE,MIN_STRING_LENGTH NUMBER,MAX_STRING_LENGTH NUMBER,REGEXP VARCHAR2(4000 BYTE),REGEXP_DESCRIPTION VARCHAR2(50 BYTE))TABLESPACE "USERS"LOGGINGPCTFREE 10PCTUSED 0INITRANS 1MAXTRANS 255STORAGE(INITIAL 128KMINEXTENTS 1MAXEXTENTS 2147483645FREELISTS 1FREELIST GROUPS 1BUFFER_POOL DEFAULT);ALTER TABLE PLANNEDADD CONSTRAINT UNIQUE_PV_RTAG UNIQUE(PV_ID,RTAG_ID)ENABLE;ALTER TABLE PACKAGE_BUILD_ENVADD CONSTRAINT FK_PKG_BUILD_E_REF_BUILD_ENV FOREIGN KEY(BE_ID)REFERENCES BUILD_ENVIRONMENTS(BE_ID) ENABLE;ALTER TABLE PACKAGE_BUILD_ENVADD CONSTRAINT FK_PKG_BUILD_E_REF_PKG_VERSION FOREIGN KEY(PV_ID)REFERENCES PACKAGE_VERSIONS(PV_ID) ENABLE;ALTER TABLE NOTIFICATION_HISTORYADD CONSTRAINT FK_NK_PV_ID FOREIGN KEY(PV_ID)REFERENCES PACKAGE_VERSIONS(PV_ID) DISABLE;ALTER TABLE NOTIFICATION_HISTORYADD CONSTRAINT FK_NH_RTAG_ID FOREIGN KEY(RTAG_ID)REFERENCES RELEASE_TAGS(RTAG_ID) DISABLE;ALTER TABLE IGNORE_WARNINGSADD CONSTRAINT FK_IGW_RTAG_ID FOREIGN KEY(RTAG_ID)REFERENCES RELEASE_TAGS(RTAG_ID) DISABLE;ALTER TABLE IGNORE_WARNINGSADD CONSTRAINT FK_IGW_DPV_ID FOREIGN KEY(DPV_ID)REFERENCES PACKAGE_VERSIONS(PV_ID) DISABLE;ALTER TABLE IGNORE_WARNINGSADD CONSTRAINT FK_IGW_PV_ID FOREIGN KEY(PV_ID)REFERENCES PACKAGE_VERSIONS(PV_ID) DISABLE;ALTER TABLE RELEASE_CONTENTADD FOREIGN KEY(PKG_ID)REFERENCES PACKAGES(PKG_ID) DISABLE;ALTER TABLE PACKAGE_DOCUMENTSADD CONSTRAINT FK_PKGDOCS_REF_PV FOREIGN KEY(PV_ID)REFERENCES PACKAGE_VERSIONS(PV_ID) ENABLE;ALTER TABLE BUILD_STANDARDS_ADDENDUMADD CONSTRAINT BUILD_STANDARDS_ADDENDUM_R01 FOREIGN KEY(BS_ID)REFERENCES BUILD_STANDARDS(BS_ID) ENABLE;ALTER TABLE UNIT_TESTSADD CONSTRAINT FK_UT_REF_TEST_TYPES FOREIGN KEY(TEST_TYPES_FK)REFERENCES TEST_TYPES(TEST_TYPE_ID) DISABLE;ALTER TABLE UNIT_TESTSADD CONSTRAINT FK_UT_REF_PV_ID FOREIGN KEY(PV_ID)REFERENCES PACKAGE_VERSIONS(PV_ID) DISABLE;ALTER TABLE PACKAGE_PROCESSESADD CONSTRAINT PACKAGE_PROCESSES_R02 FOREIGN KEY(PROC_ID)REFERENCES PROCESSES(PROC_ID) ENABLE;ALTER TABLE PACKAGE_PROCESSESADD CONSTRAINT PACKAGE_PROCESSES_R01 FOREIGN KEY(PV_ID)REFERENCES PACKAGE_VERSIONS(PV_ID) ENABLE;ALTER TABLE RELEASE_COMPONENTSADD CONSTRAINT FK_RCONT_PV_ID FOREIGN KEY(PV_ID)REFERENCES PACKAGE_VERSIONS(PV_ID) ENABLE;ALTER TABLE PACKAGE_PATCHESADD CONSTRAINT FK_PP_PATCH_ID FOREIGN KEY(PATCH_ID)REFERENCES PACKAGE_VERSIONS(PV_ID) DISABLE;ALTER TABLE PACKAGE_PATCHESADD CONSTRAINT FK_PP_PV_ID FOREIGN KEY(PV_ID)REFERENCES PACKAGE_VERSIONS(PV_ID) ENABLE;ALTER TABLE BUILD_ORDERADD CONSTRAINT FK_BO_PV_ID FOREIGN KEY(PV_ID)REFERENCES PACKAGE_VERSIONS(PV_ID) DISABLE;ALTER TABLE BUILD_ORDERADD CONSTRAINT FK_BO_RTAG_ID FOREIGN KEY(RTAG_ID)REFERENCES RELEASE_TAGS(RTAG_ID) DISABLE;ALTER TABLE PLANNEDADD CONSTRAINT REFRELEASE_TAGS53 FOREIGN KEY(RTAG_ID)REFERENCES RELEASE_TAGS(RTAG_ID) ENABLE;ALTER TABLE PLANNEDADD CONSTRAINT REFPACKAGE_VERSIONS54 FOREIGN KEY(PV_ID)REFERENCES PACKAGE_VERSIONS(PV_ID) ENABLE;ALTER TABLE PLANNEDADD CONSTRAINT REFVIEWS55 FOREIGN KEY(VIEW_ID)REFERENCES VIEWS(VIEW_ID) ENABLE;ALTER TABLE PROJECT_ACTION_LOGADD FOREIGN KEY(RTAG_ID)REFERENCES RELEASE_TAGS(RTAG_ID) DISABLE;ALTER TABLE PROJECT_ACTION_LOGADD CONSTRAINT REFACTION_TYPE63 FOREIGN KEY(ACTTYPE_ID)REFERENCES ACTION_TYPE(ACTTYPE_ID) ENABLE;ALTER TABLE PACKAGE_INTERESTADD FOREIGN KEY(PROJ_ID)REFERENCES PROJECTS(PROJ_ID) ENABLE;ALTER TABLE AUTOBUILD_FAILUREADD FOREIGN KEY(VIEW_ID)REFERENCES VIEWS(VIEW_ID) ENABLE;ALTER TABLE AUTOBUILD_FAILUREADD FOREIGN KEY(PROJ_ID)REFERENCES PROJECTS(PROJ_ID) ENABLE;ALTER TABLE JIRA_ISSUESADD CONSTRAINT FK_JIRA_PV_ID FOREIGN KEY(PV_ID)REFERENCES PACKAGE_VERSIONS(PV_ID) ENABLE;ALTER TABLE WORK_IN_PROGRESSADD CONSTRAINT REFPACKAGE_VERSIONS51 FOREIGN KEY(PV_ID)REFERENCES PACKAGE_VERSIONS(PV_ID) ENABLE;ALTER TABLE WORK_IN_PROGRESSADD CONSTRAINT REFRELEASE_TAGS50 FOREIGN KEY(RTAG_ID)REFERENCES RELEASE_TAGS(RTAG_ID) ENABLE;ALTER TABLE WORK_IN_PROGRESSADD CONSTRAINT REFVIEWS52 FOREIGN KEY(VIEW_ID)REFERENCES VIEWS(VIEW_ID) ENABLE;ALTER TABLE ADVISORY_RIPPLESADD FOREIGN KEY(PV_ID)REFERENCES PACKAGE_VERSIONS(PV_ID) ENABLE;ALTER TABLE ADVISORY_RIPPLESADD FOREIGN KEY(RTAG_ID)REFERENCES RELEASE_TAGS(RTAG_ID) ENABLE;ALTER TABLE RELEASE_LINKSADD CONSTRAINT REFRELEASE_TAGS58 FOREIGN KEY(REF_RTAG_ID)REFERENCES RELEASE_TAGS(RTAG_ID) ENABLE;ALTER TABLE RELEASE_LINKSADD CONSTRAINT REFRELEASE_TAGS56 FOREIGN KEY(RTAG_ID)REFERENCES RELEASE_TAGS(RTAG_ID) ENABLE;ALTER TABLE PRODUCT_COMPONENTSADD CONSTRAINT REFPACKAGE_VERSIONS49 FOREIGN KEY(PV_ID)REFERENCES PACKAGE_VERSIONS(PV_ID) ENABLE;ALTER TABLE MEMBERS_GROUPADD FOREIGN KEY(GROUP_EMAIL_ID)REFERENCES AUTOBUILD_FAILURE(GROUP_EMAIL_ID) ENABLE;ALTER TABLE ACTION_LOGADD CONSTRAINT REFACTION_TYPE48 FOREIGN KEY(ACTTYPE_ID)REFERENCES ACTION_TYPE(ACTTYPE_ID) ENABLE;ALTER TABLE ADDITIONAL_NOTESADD CONSTRAINT FK_ADDITION_REF_PV_ID FOREIGN KEY(PV_ID)REFERENCES PACKAGE_VERSIONS(PV_ID) DISABLE;ALTER TABLE VTREES_WORLDADD CONSTRAINT FK_VW_REF_VTREE_ID FOREIGN KEY(VTREE_ID)REFERENCES VTREES(VTREE_ID) ENABLE;ALTER TABLE VTREES_WORLDADD CONSTRAINT FK_VW_REF_WORLD_ID FOREIGN KEY(WORLD_ID)REFERENCES WORLDS(WORLD_ID) ENABLE;ALTER TABLE CODE_REVIEWSADD CONSTRAINT FK_CODE_REV_REF_PV_ID FOREIGN KEY(PV_ID)REFERENCES PACKAGE_VERSIONS(PV_ID) DISABLE;ALTER TABLE CQ_ISSUESADD CONSTRAINT FK_CQ_PV_ID FOREIGN KEY(PV_ID)REFERENCES PACKAGE_VERSIONS(PV_ID) DISABLE;ALTER TABLE MICROSOFTDTPROPERTIESADD CONSTRAINT MICROSOFT_NN_PROPERTY CHECK("PROPERTY" IS NOT NULL)ENABLE;ALTER TABLE MICROSOFTDTPROPERTIESADD CONSTRAINT MICROSOFT_NN_ID CHECK("ID" IS NOT NULL)ENABLE;ALTER TABLE MICROSOFTDTPROPERTIESADD CONSTRAINT MICROSOFT_NN_VERSION CHECK("VERSION" IS NOT NULL)ENABLE;CREATE UNIQUE INDEX UNQ_PKG_BUILD_ENV ON PACKAGE_BUILD_ENV (PV_ID ASC, BE_ID ASC) TABLESPACE "USERS"LOGGINGPCTFREE 10INITRANS 2MAXTRANS 255STORAGE(INITIAL 128KMINEXTENTS 1MAXEXTENTS 2147483645FREELISTS 1FREELIST GROUPS 1BUFFER_POOL DEFAULT);CREATE INDEX INX_TES_PKGEXT ON TEMP_ENV_STATES (PKG_ID ASC, V_EXT ASC) TABLESPACE "USERS"NOLOGGINGPCTFREE 10INITRANS 2MAXTRANS 255STORAGE(INITIAL 128KMINEXTENTS 1MAXEXTENTS 2147483645FREELISTS 1FREELIST GROUPS 1BUFFER_POOL DEFAULT);CREATE INDEX INX_TES_PV ON TEMP_ENV_STATES (PV_ID ASC) TABLESPACE "USERS"NOLOGGINGPCTFREE 10INITRANS 2MAXTRANS 255STORAGE(INITIAL 128KMINEXTENTS 1MAXEXTENTS 2147483645FREELISTS 1FREELIST GROUPS 1BUFFER_POOL DEFAULT);CREATE INDEX INX_TES_TES_STATE ON TEMP_ENV_STATES (TES_STATE ASC) TABLESPACE "USERS"NOLOGGINGPCTFREE 10INITRANS 2MAXTRANS 255STORAGE(INITIAL 128KMINEXTENTS 1MAXEXTENTS 2147483645FREELISTS 1FREELIST GROUPS 1BUFFER_POOL DEFAULT);CREATE INDEX INX_TTB_LEVEL_NUM ON TEMP_TREE_BROWSE (LEVEL_NUM ASC) TABLESPACE "USERS"LOGGINGPCTFREE 10INITRANS 2MAXTRANS 255STORAGE(INITIAL 64KMINEXTENTS 1MAXEXTENTS 2147483645FREELISTS 1FREELIST GROUPS 1BUFFER_POOL DEFAULT);CREATE INDEX INX_TTB_PKG ON TEMP_TREE_BROWSE (PKG_ID ASC, V_EXT ASC) TABLESPACE "USERS"LOGGINGPCTFREE 10INITRANS 2MAXTRANS 255STORAGE(INITIAL 64KMINEXTENTS 1MAXEXTENTS 2147483645FREELISTS 1FREELIST GROUPS 1BUFFER_POOL DEFAULT);CREATE INDEX INX_TTB_SESSION ON TEMP_TREE_BROWSE (SESSION_NUM ASC) TABLESPACE "USERS"LOGGINGPCTFREE 10INITRANS 2MAXTRANS 255STORAGE(INITIAL 64KMINEXTENTS 1MAXEXTENTS 2147483645FREELISTS 1FREELIST GROUPS 1BUFFER_POOL DEFAULT);CREATE INDEX INX_IS_PATCH_IGNORE ON IGNORE_WARNINGS (IS_PATCH_IGNORE ASC) TABLESPACE "USERS"LOGGINGPCTFREE 10INITRANS 2MAXTRANS 255STORAGE(INITIAL 128KMINEXTENTS 1MAXEXTENTS 2147483645FREELISTS 1FREELIST GROUPS 1BUFFER_POOL DEFAULT);CREATE UNIQUE INDEX UNQ_IGW ON IGNORE_WARNINGS (RTAG_ID ASC, PV_ID ASC, DPV_ID ASC) TABLESPACE "USERS"LOGGINGPCTFREE 10INITRANS 2MAXTRANS 255STORAGE(INITIAL 128KMINEXTENTS 1MAXEXTENTS 2147483645FREELISTS 1FREELIST GROUPS 1BUFFER_POOL DEFAULT);CREATE INDEX INX_RC_BASE_VIEW_ID ON RELEASE_CONTENT (BASE_VIEW_ID ASC) TABLESPACE "USERS"NOLOGGINGPCTFREE 10INITRANS 2MAXTRANS 255STORAGE(INITIAL 128KMINEXTENTS 1MAXEXTENTS 2147483645FREELISTS 1FREELIST GROUPS 1BUFFER_POOL DEFAULT);CREATE INDEX INX_RC_PKG_STATE ON RELEASE_CONTENT (PKG_STATE ASC) TABLESPACE "USERS"NOLOGGINGPCTFREE 10INITRANS 2MAXTRANS 255STORAGE(INITIAL 128KMINEXTENTS 1MAXEXTENTS 2147483645FREELISTS 1FREELIST GROUPS 1BUFFER_POOL DEFAULT);CREATE UNIQUE INDEX UNQ_RELEASE_CONTENTS ON RELEASE_CONTENT (RTAG_ID ASC, PV_ID ASC) TABLESPACE "USERS"NOLOGGINGPCTFREE 10INITRANS 2MAXTRANS 255STORAGE(INITIAL 128KMINEXTENTS 1MAXEXTENTS 2147483645FREELISTS 1FREELIST GROUPS 1BUFFER_POOL DEFAULT);CREATE INDEX INX_PV_ID_PKGDOC ON PACKAGE_DOCUMENTS (PV_ID ASC) TABLESPACE "USERS"LOGGINGPCTFREE 10INITRANS 2MAXTRANS 255STORAGE(INITIAL 128KMINEXTENTS 1MAXEXTENTS 2147483645FREELISTS 1FREELIST GROUPS 1BUFFER_POOL DEFAULT);CREATE INDEX INX_PV_ID_TEST_ID_PKGDOC ON PACKAGE_DOCUMENTS (PV_ID ASC, TEST_ID ASC) TABLESPACE "USERS"LOGGINGPCTFREE 10INITRANS 2MAXTRANS 255STORAGE(INITIAL 128KMINEXTENTS 1MAXEXTENTS 2147483645FREELISTS 1FREELIST GROUPS 1BUFFER_POOL DEFAULT);CREATE UNIQUE INDEX UNQ_PKG_DOC ON PACKAGE_DOCUMENTS (PV_ID ASC, DOC_NUM ASC) TABLESPACE "USERS"LOGGINGPCTFREE 10INITRANS 2MAXTRANS 255STORAGE(INITIAL 128KMINEXTENTS 1MAXEXTENTS 2147483645FREELISTS 1FREELIST GROUPS 1BUFFER_POOL DEFAULT);CREATE INDEX FK_PROJ_ID_RELEASE_TAGS ON RELEASE_TAGS (PROJ_ID ASC) TABLESPACE "USERS"LOGGINGPCTFREE 10INITRANS 2MAXTRANS 255STORAGE(INITIAL 64KMINEXTENTS 1MAXEXTENTS 2147483645FREELISTS 1FREELIST GROUPS 1BUFFER_POOL DEFAULT);CREATE INDEX INX_UT_COMPLETED ON UNIT_TESTS (COMPLETION_DATE ASC, COMPLETED_BY ASC) TABLESPACE "USERS"LOGGINGPCTFREE 10INITRANS 2MAXTRANS 255STORAGE(INITIAL 128KMINEXTENTS 1MAXEXTENTS 2147483645FREELISTS 1FREELIST GROUPS 1BUFFER_POOL DEFAULT);CREATE INDEX INX_UT_PV_TEST_TYPE ON UNIT_TESTS (PV_ID ASC, TEST_TYPES_FK ASC) TABLESPACE "USERS"LOGGINGPCTFREE 10INITRANS 2MAXTRANS 255STORAGE(INITIAL 128KMINEXTENTS 1MAXEXTENTS 2147483645FREELISTS 1FREELIST GROUPS 1BUFFER_POOL DEFAULT);CREATE UNIQUE INDEX UNQ_UT_PART1 ON UNIT_TESTS (PV_ID ASC, TEST_ID ASC) TABLESPACE "USERS"LOGGINGPCTFREE 10INITRANS 2MAXTRANS 255STORAGE(INITIAL 128KMINEXTENTS 1MAXEXTENTS 2147483645FREELISTS 1FREELIST GROUPS 1BUFFER_POOL DEFAULT);CREATE UNIQUE INDEX UNQ_BUILD_ENV ON BUILD_ENVIRONMENTS (BE_NAME ASC) TABLESPACE "USERS"LOGGINGPCTFREE 10INITRANS 2MAXTRANS 255STORAGE(INITIAL 128KMINEXTENTS 1MAXEXTENTS 2147483645FREELISTS 1FREELIST GROUPS 1BUFFER_POOL DEFAULT);CREATE INDEX INX_FILE_NAME ON RELEASE_COMPONENTS (UPPER("FILE_NAME") ASC) TABLESPACE "USERS"LOGGINGPCTFREE 10INITRANS 2MAXTRANS 255STORAGE(INITIAL 64KMINEXTENTS 1MAXEXTENTS 2147483645FREELISTS 1FREELIST GROUPS 1BUFFER_POOL DEFAULT);CREATE INDEX INX_RCOMP_PV_ID ON RELEASE_COMPONENTS (PV_ID ASC) TABLESPACE "USERS"LOGGINGPCTFREE 10INITRANS 2MAXTRANS 255STORAGE(INITIAL 64KMINEXTENTS 1MAXEXTENTS 2147483645FREELISTS 1FREELIST GROUPS 1BUFFER_POOL DEFAULT);CREATE UNIQUE INDEX UNQ_REL_COMPONENTS ON RELEASE_COMPONENTS (PV_ID ASC, FILE_NAME ASC, FILE_PATH ASC) TABLESPACE "USERS"LOGGINGPCTFREE 10INITRANS 2MAXTRANS 255STORAGE(INITIAL 128KMINEXTENTS 1MAXEXTENTS 2147483645FREELISTS 1FREELIST GROUPS 1BUFFER_POOL DEFAULT);CREATE INDEX INX_PP_PV_ID ON PACKAGE_PATCHES (PV_ID ASC) TABLESPACE "USERS"LOGGINGPCTFREE 10INITRANS 2MAXTRANS 255STORAGE(INITIAL 128KMINEXTENTS 1MAXEXTENTS 2147483645FREELISTS 1FREELIST GROUPS 1BUFFER_POOL DEFAULT);CREATE UNIQUE INDEX UNQ_PACKAGE_PATCHES ON PACKAGE_PATCHES (PATCH_ID ASC) TABLESPACE "USERS"NOLOGGINGPCTFREE 10INITRANS 2MAXTRANS 255STORAGE(INITIAL 128KMINEXTENTS 1MAXEXTENTS 2147483645FREELISTS 1FREELIST GROUPS 1BUFFER_POOL DEFAULT);CREATE UNIQUE INDEX UNQ_BUILD_ORDER ON BUILD_ORDER (RTAG_ID ASC, PV_ID ASC, STEP_NUM ASC) TABLESPACE "USERS"LOGGINGPCTFREE 10INITRANS 2MAXTRANS 255STORAGE(INITIAL 64KMINEXTENTS 1MAXEXTENTS 2147483645FREELISTS 1FREELIST GROUPS 1BUFFER_POOL DEFAULT);CREATE INDEX UNQ_PLANNED ON PLANNED (RTAG_ID ASC, PV_ID ASC) TABLESPACE "USERS"LOGGINGPCTFREE 10INITRANS 2MAXTRANS 255STORAGE(INITIAL 64KMINEXTENTS 1MAXEXTENTS 2147483645FREELISTS 1FREELIST GROUPS 1BUFFER_POOL DEFAULT);CREATE INDEX INX_PROJ_ACTION_DATE_TIME ON PROJECT_ACTION_LOG (ACTION_DATETIME ASC) TABLESPACE "USERS"LOGGINGPCTFREE 10INITRANS 2MAXTRANS 255STORAGE(INITIAL 64KMINEXTENTS 1MAXEXTENTS 2147483645FREELISTS 1FREELIST GROUPS 1BUFFER_POOL DEFAULT);CREATE INDEX INX_PROJ_ACTION_LOG_PROJ ON PROJECT_ACTION_LOG (PROJ_ID ASC) TABLESPACE "USERS"LOGGINGPCTFREE 10INITRANS 2MAXTRANS 255STORAGE(INITIAL 64KMINEXTENTS 1MAXEXTENTS 2147483645FREELISTS 1FREELIST GROUPS 1BUFFER_POOL DEFAULT);CREATE UNIQUE INDEX UNQ_PLATFORM ON PLATFORMS (NAME ASC) TABLESPACE "USERS"LOGGINGPCTFREE 10INITRANS 2MAXTRANS 255STORAGE(INITIAL 64KMINEXTENTS 1MAXEXTENTS 2147483645FREELISTS 1FREELIST GROUPS 1BUFFER_POOL DEFAULT);CREATE UNIQUE INDEX UNQ_VIEW_SETTINGS ON VIEW_SETTINGS (USER_ID ASC, VIEW_ID ASC) TABLESPACE "SYSTEM"LOGGINGPCTFREE 10INITRANS 2MAXTRANS 255STORAGE(INITIAL 80KNEXT 16KMINEXTENTS 1MAXEXTENTS 2147483645PCTINCREASE 0FREELISTS 1FREELIST GROUPS 1BUFFER_POOL DEFAULT);CREATE UNIQUE INDEX UNQ_VIEW_DEF ON VIEW_DEF (VIEW_ID ASC, PKG_ID ASC) TABLESPACE "SYSTEM"LOGGINGPCTFREE 10INITRANS 2MAXTRANS 255STORAGE(INITIAL 160KNEXT 16KMINEXTENTS 1MAXEXTENTS 2147483645PCTINCREASE 0FREELISTS 1FREELIST GROUPS 1BUFFER_POOL DEFAULT);CREATE UNIQUE INDEX UNQ_VTREES ON VTREES (PROJ_ID ASC, VTREE_NAME ASC) TABLESPACE "SYSTEM"LOGGINGPCTFREE 10INITRANS 2MAXTRANS 255STORAGE(INITIAL 80KNEXT 16KMINEXTENTS 1MAXEXTENTS 2147483645PCTINCREASE 0FREELISTS 1FREELIST GROUPS 1BUFFER_POOL DEFAULT);CREATE UNIQUE INDEX UNQ_WORK_IN_PROGRESS ON WORK_IN_PROGRESS (RTAG_ID ASC, PV_ID ASC) TABLESPACE "USERS"LOGGINGPCTFREE 10INITRANS 2MAXTRANS 255STORAGE(INITIAL 64KMINEXTENTS 1MAXEXTENTS 2147483645FREELISTS 1FREELIST GROUPS 1BUFFER_POOL DEFAULT);CREATE INDEX INX_RL_REFRTAG_ID ON RELEASE_LINKS (REF_RTAG_ID ASC) TABLESPACE "USERS"LOGGINGPCTFREE 10INITRANS 2MAXTRANS 255STORAGE(INITIAL 64KMINEXTENTS 1MAXEXTENTS 2147483645FREELISTS 1FREELIST GROUPS 1BUFFER_POOL DEFAULT);CREATE INDEX INX_RL_RTAG_ID ON RELEASE_LINKS (RTAG_ID ASC) TABLESPACE "USERS"LOGGINGPCTFREE 10INITRANS 2MAXTRANS 255STORAGE(INITIAL 64KMINEXTENTS 1MAXEXTENTS 2147483645FREELISTS 1FREELIST GROUPS 1BUFFER_POOL DEFAULT);CREATE UNIQUE INDEX UNQ_RELEASE_LINKS ON RELEASE_LINKS (RTAG_ID ASC, REF_RTAG_ID ASC) TABLESPACE "USERS"LOGGINGPCTFREE 10INITRANS 2MAXTRANS 255STORAGE(INITIAL 64KMINEXTENTS 1MAXEXTENTS 2147483645FREELISTS 1FREELIST GROUPS 1BUFFER_POOL DEFAULT);CREATE UNIQUE INDEX UNQ_BUILD_ENV_DOCS ON BUILD_ENV_DOCUMENTS (BE_ID ASC, DOC_NUM ASC) TABLESPACE "USERS"LOGGINGPCTFREE 10INITRANS 2MAXTRANS 255STORAGE(INITIAL 128KMINEXTENTS 1MAXEXTENTS 2147483645FREELISTS 1FREELIST GROUPS 1BUFFER_POOL DEFAULT);CREATE UNIQUE INDEX UNQ_TEST_TYPE_NAME ON TEST_TYPES (TEST_TYPE_NAME ASC) TABLESPACE "USERS"LOGGINGPCTFREE 10INITRANS 2MAXTRANS 255STORAGE(INITIAL 128KMINEXTENTS 1MAXEXTENTS 2147483645FREELISTS 1FREELIST GROUPS 1BUFFER_POOL DEFAULT);CREATE INDEX INX_PACKAGE_DEPS_DPKG_ID ON PACKAGE_DEPENDENCIES (DPKG_ID ASC) TABLESPACE "USERS"NOLOGGINGPCTFREE 10INITRANS 2MAXTRANS 255STORAGE(INITIAL 256KMINEXTENTS 1MAXEXTENTS 2147483645FREELISTS 1FREELIST GROUPS 1BUFFER_POOL DEFAULT);CREATE INDEX INX_PACKAGE_DEPS_DPV_ID ON PACKAGE_DEPENDENCIES (DPV_ID ASC) TABLESPACE "USERS"NOLOGGINGPCTFREE 10INITRANS 2MAXTRANS 255STORAGE(INITIAL 384KMINEXTENTS 1MAXEXTENTS 2147483645FREELISTS 1FREELIST GROUPS 1BUFFER_POOL DEFAULT);CREATE INDEX INX_PACKAGE_DEPS_PKG_ID ON PACKAGE_DEPENDENCIES (PKG_ID ASC) TABLESPACE "USERS"NOLOGGINGPCTFREE 10INITRANS 2MAXTRANS 255STORAGE(INITIAL 256KMINEXTENTS 1MAXEXTENTS 2147483645FREELISTS 1FREELIST GROUPS 1BUFFER_POOL DEFAULT);CREATE INDEX INX_PACKAGE_DEPS_PV_ID ON PACKAGE_DEPENDENCIES (PV_ID ASC) TABLESPACE "USERS"NOLOGGINGPCTFREE 10INITRANS 2MAXTRANS 255STORAGE(INITIAL 384KMINEXTENTS 1MAXEXTENTS 2147483645FREELISTS 1FREELIST GROUPS 1BUFFER_POOL DEFAULT);CREATE UNIQUE INDEX UNQ_PACKAGE_DEPENDENCIES ON PACKAGE_DEPENDENCIES (PV_ID ASC, DPV_ID ASC) TABLESPACE "SYSTEM"LOGGINGPCTFREE 10INITRANS 2MAXTRANS 255STORAGE(INITIAL 23760KNEXT 16KMINEXTENTS 1MAXEXTENTS 2147483645PCTINCREASE 0FREELISTS 1FREELIST GROUPS 1BUFFER_POOL DEFAULT);CREATE INDEX INX_PCOMP_FK_PV_ID ON PRODUCT_COMPONENTS (PV_ID ASC) TABLESPACE "USERS"LOGGINGPCTFREE 10INITRANS 2MAXTRANS 255STORAGE(INITIAL 64KMINEXTENTS 1MAXEXTENTS 2147483645FREELISTS 1FREELIST GROUPS 1BUFFER_POOL DEFAULT);CREATE INDEX INX_PC_FILE_NAME ON PRODUCT_COMPONENTS (UPPER("FILE_NAME") ASC) TABLESPACE "USERS"LOGGINGPCTFREE 10INITRANS 2MAXTRANS 255STORAGE(INITIAL 64KMINEXTENTS 1MAXEXTENTS 2147483645FREELISTS 1FREELIST GROUPS 1BUFFER_POOL DEFAULT);CREATE UNIQUE INDEX UNQ_PACKAGES ON PACKAGES (PKG_NAME ASC) TABLESPACE "SYSTEM"LOGGINGPCTFREE 10INITRANS 2MAXTRANS 255STORAGE(INITIAL 160KNEXT 16KMINEXTENTS 1MAXEXTENTS 2147483645PCTINCREASE 0FREELISTS 1FREELIST GROUPS 1BUFFER_POOL DEFAULT);CREATE UNIQUE INDEX UNQ_PROJECTS ON PROJECTS (PROJ_NAME ASC) TABLESPACE "SYSTEM"LOGGINGPCTFREE 10INITRANS 2MAXTRANS 255STORAGE(INITIAL 80KNEXT 16KMINEXTENTS 1MAXEXTENTS 2147483645PCTINCREASE 0FREELISTS 1FREELIST GROUPS 1BUFFER_POOL DEFAULT);CREATE UNIQUE INDEX UNQ_VIEWS ON VIEWS (VIEW_NAME ASC, OWNER_ID ASC) TABLESPACE "SYSTEM"LOGGINGPCTFREE 10INITRANS 2MAXTRANS 255STORAGE(INITIAL 80KNEXT 16KMINEXTENTS 1MAXEXTENTS 2147483645PCTINCREASE 0FREELISTS 1FREELIST GROUPS 1BUFFER_POOL DEFAULT);CREATE INDEX INX_ACTION_DATE_TIME ON ACTION_LOG (ACTION_DATETIME ASC) TABLESPACE "USERS"LOGGINGPCTFREE 10INITRANS 2MAXTRANS 255STORAGE(INITIAL 128KMINEXTENTS 1MAXEXTENTS 2147483645FREELISTS 1FREELIST GROUPS 1BUFFER_POOL DEFAULT);CREATE INDEX INX_ACTION_LOG_PV_ID ON ACTION_LOG (PV_ID ASC) TABLESPACE "USERS"LOGGINGPCTFREE 10INITRANS 2MAXTRANS 255STORAGE(INITIAL 128KMINEXTENTS 1MAXEXTENTS 2147483645FREELISTS 1FREELIST GROUPS 1BUFFER_POOL DEFAULT);CREATE UNIQUE INDEX UNQ_PROJECT_EXT ON PROJECT_EXTENTIONS (PROJ_ID ASC, EXT_NAME ASC) TABLESPACE "SYSTEM"LOGGINGPCTFREE 10INITRANS 2MAXTRANS 255STORAGE(INITIAL 80KNEXT 16KMINEXTENTS 1MAXEXTENTS 2147483645PCTINCREASE 0FREELISTS 1FREELIST GROUPS 1BUFFER_POOL DEFAULT);CREATE UNIQUE INDEX UNQ_AN_PART1 ON ADDITIONAL_NOTES (PV_ID ASC, NOTE_ID ASC) TABLESPACE "USERS"LOGGINGPCTFREE 10INITRANS 2MAXTRANS 255STORAGE(INITIAL 128KMINEXTENTS 1MAXEXTENTS 2147483645FREELISTS 1FREELIST GROUPS 1BUFFER_POOL DEFAULT);CREATE UNIQUE INDEX UNQ_AN_PART2 ON ADDITIONAL_NOTES (PV_ID ASC, NOTE_TITLE ASC) TABLESPACE "USERS"LOGGINGPCTFREE 10INITRANS 2MAXTRANS 255STORAGE(INITIAL 128KMINEXTENTS 1MAXEXTENTS 2147483645FREELISTS 1FREELIST GROUPS 1BUFFER_POOL DEFAULT);CREATE INDEX INX_PACKAGES_PKG_NAME ON PACKAGE_VERSIONS (PKG_ID ASC) TABLESPACE "USERS"NOLOGGINGPCTFREE 10INITRANS 2MAXTRANS 255STORAGE(INITIAL 128KMINEXTENTS 1MAXEXTENTS 2147483645FREELISTS 1FREELIST GROUPS 1BUFFER_POOL DEFAULT);CREATE INDEX INX_PACKAGES_PKG_VERSION ON PACKAGE_VERSIONS (PKG_VERSION ASC) TABLESPACE "USERS"NOLOGGINGPCTFREE 10INITRANS 2MAXTRANS 255STORAGE(INITIAL 128KMINEXTENTS 1MAXEXTENTS 2147483645FREELISTS 1FREELIST GROUPS 1BUFFER_POOL DEFAULT);CREATE INDEX INX_PACKAGES_V_EXT ON PACKAGE_VERSIONS (V_EXT ASC) TABLESPACE "USERS"NOLOGGINGPCTFREE 10INITRANS 2MAXTRANS 255STORAGE(INITIAL 128KMINEXTENTS 1MAXEXTENTS 2147483645FREELISTS 1FREELIST GROUPS 1BUFFER_POOL DEFAULT);CREATE INDEX INX_PACKAGES_V_MM ON PACKAGE_VERSIONS (V_MM ASC) TABLESPACE "USERS"NOLOGGINGPCTFREE 10INITRANS 2MAXTRANS 255STORAGE(INITIAL 128KMINEXTENTS 1MAXEXTENTS 2147483645FREELISTS 1FREELIST GROUPS 1BUFFER_POOL DEFAULT);CREATE INDEX INX_PACKAGES_V_NMM ON PACKAGE_VERSIONS (V_NMM ASC) TABLESPACE "USERS"NOLOGGINGPCTFREE 10INITRANS 2MAXTRANS 255STORAGE(INITIAL 128KMINEXTENTS 1MAXEXTENTS 2147483645FREELISTS 1FREELIST GROUPS 1BUFFER_POOL DEFAULT);CREATE INDEX INX_PV_OWNER_ID ON PACKAGE_VERSIONS (OWNER_ID ASC) TABLESPACE "USERS"LOGGINGPCTFREE 10INITRANS 2MAXTRANS 255STORAGE(INITIAL 128KMINEXTENTS 1MAXEXTENTS 2147483645FREELISTS 1FREELIST GROUPS 1BUFFER_POOL DEFAULT);CREATE UNIQUE INDEX UNQ_PACKAGE_VERSIONS ON PACKAGE_VERSIONS (PKG_ID ASC, PKG_VERSION ASC) TABLESPACE "SYSTEM"LOGGINGPCTFREE 10INITRANS 2MAXTRANS 255STORAGE(INITIAL 2960KNEXT 16KMINEXTENTS 1MAXEXTENTS 2147483645PCTINCREASE 0FREELISTS 1FREELIST GROUPS 1BUFFER_POOL DEFAULT);CREATE INDEX INX_MSGBOARD ON MESSAGE_BOARD (EXPIRY_DATE ASC, DUE_DATE ASC) TABLESPACE "SYSTEM"LOGGINGPCTFREE 10INITRANS 2MAXTRANS 255STORAGE(INITIAL 80KNEXT 16KMINEXTENTS 1MAXEXTENTS 2147483645PCTINCREASE 0FREELISTS 1FREELIST GROUPS 1BUFFER_POOL DEFAULT);CREATE UNIQUE INDEX UNQ_VTREE_WORLD ON VTREES_WORLD (WORLD_ID ASC, VTREE_ID ASC) TABLESPACE "USERS"LOGGINGPCTFREE 10INITRANS 2MAXTRANS 255STORAGE(INITIAL 128KMINEXTENTS 1MAXEXTENTS 2147483645FREELISTS 1FREELIST GROUPS 1BUFFER_POOL DEFAULT);CREATE UNIQUE INDEX INX_CODE_REVIEW ON CODE_REVIEWS (PV_ID ASC) TABLESPACE "USERS"LOGGINGPCTFREE 10INITRANS 2MAXTRANS 255STORAGE(INITIAL 128KMINEXTENTS 1MAXEXTENTS 2147483645FREELISTS 1FREELIST GROUPS 1BUFFER_POOL DEFAULT);CREATE UNIQUE INDEX UNQ_ABTN_DEF ON DEF_ACTION_BUTTONS (ABTN_NAME ASC) TABLESPACE "USERS"LOGGINGPCTFREE 10INITRANS 2MAXTRANS 255STORAGE(INITIAL 64KMINEXTENTS 1MAXEXTENTS 2147483645FREELISTS 1FREELIST GROUPS 1BUFFER_POOL DEFAULT);CREATE UNIQUE INDEX UNQ_CQ ON CQ_ISSUES (PV_ID ASC, ISS_ID ASC, ISS_DB ASC) TABLESPACE "USERS"LOGGINGPCTFREE 10INITRANS 2MAXTRANS 255STORAGE(INITIAL 128KMINEXTENTS 1MAXEXTENTS 2147483645FREELISTS 1FREELIST GROUPS 1BUFFER_POOL DEFAULT);CREATE UNIQUE INDEX UNQ_VALIDATION_RULES ON VALIDATION_RULES (FIELD_NAME ASC) TABLESPACE "USERS"LOGGINGPCTFREE 10INITRANS 2MAXTRANS 255STORAGE(INITIAL 128KMINEXTENTS 1MAXEXTENTS 2147483645FREELISTS 1FREELIST GROUPS 1BUFFER_POOL DEFAULT);CREATE OR REPLACE PROCEDURE ADD_VIEW_MEMBERS (nGroupId IN NUMBER, sUserList IN VARCHAR2) IS/******************************************************************************NAME: ADD_VIEW_MEMBERSPURPOSE:REVISIONS:Ver Date Author Description--------- ---------- --------------- ------------------------------------1.0 11/04/2006 1. Created this procedure.NOTES:Automatically available Auto Replace Keywords:Object Name: ADD_VIEW_MEMBERSSysdate: 11/04/2006Date and Time: 11/04/2006, 9:52:58 AM, and 11/04/2006 9:52:58 AMUsername: (set in TOAD Options, Procedure Editor)Table Name: (set in the "New PL/SQL Object" dialog)******************************************************************************/CURSOR user_cur ISSELECT u.USER_IDFROM USERS uWHERE u.USER_ID IN (SELECT *FROM THE ( SELECT CAST( IN_LIST_NUMBER( sUserList ) AS RELMGR_NUMBER_TAB_t ) FROM dual ));user_rec user_cur%ROWTYPE;BEGINOPEN user_cur;FETCH user_cur INTO user_rec;WHILE user_cur%FOUNDLOOPINSERT INTO MEMBERS_GROUP (GROUP_EMAIL_ID, USER_ID)VALUES ( nGroupId, user_rec.USER_ID);FETCH user_cur INTO user_rec;END LOOP;END ADD_VIEW_MEMBERS;/CREATE OR REPLACE PACKAGE PK_WORK_IN_PROGRESS IS/*------------------------------|| Last Modified: S.Vukovic|| Modified Date: 2/May/2005|| Spec Version: 1.0------------------------------*/TYPE typeCur IS REF CURSOR;/*================================================================================================*/PROCEDURE ADD_PACKAGE ( newPvId IN NUMBER, ViewId IN NUMBER, RtagId IN NUMBER, UserId IN NUMBER );PROCEDURE REMOVE_PACKAGE ( PvId IN NUMBER, RtagId IN NUMBER, UserId IN NUMBER );PROCEDURE REMOVE_PACKAGE_BULK ( PvIdList IN VARCHAR2, RtagId IN NUMBER, UserId IN NUMBER );FUNCTION GET_PACKAGE_VIEW ( PvId IN NUMBER, RtagId IN NUMBER ) RETURN NUMBER;PROCEDURE GET_VIEW_CONTENT ( RtagId IN NUMBER, ViewId IN NUMBER, RecordSet OUT typeCur );PROCEDURE CHANGE_PACKAGE_VIEW ( PvId IN NUMBER, RtagId IN NUMBER, NewViewId IN NUMBER );/*================================================================================================*/END PK_WORK_IN_PROGRESS;/CREATE OR REPLACE PROCEDURE Level_N_Conflicts ( NNrtag_id IN NUMBER,NNsession_num IN NUMBER,NNrowCnt OUT NUMBER,NNiteration IN NUMBER ) IS/* ---------------------------------------------------------------------------Version: 3.0.1--------------------------------------------------------------------------- */prevIteration NUMBER := NNiteration - 1;BEGIN/* --------- LEVEL 1 CONFILCTS -----------|| Following states are used:|| 0 -> NOT FOUND|| 1 -> MAJOR|| 2 -> MINOR MINOR*/INSERT INTO temp_env_statesSELECT DISTINCTNNsession_num AS session_num,NNiteration AS level_num, pv.pv_id, pv.pkg_id, pv.v_ext,DECODE(tes.tes_state,2, 2,1) AS messageFROM package_dependencies dep,package_versions dpv,package_versions pv,release_content rel,temp_env_states tesWHERE rel.pv_id = dep.pv_idAND rel.rtag_id = NNrtag_idAND dep.pv_id = pv.pv_idAND dep.dpv_id = dpv.pv_idAND dpv.pkg_id = tes.pkg_idAND NVL(dpv.v_ext,'|LINK_A_NULL|') = NVL(tes.v_ext,'|LINK_A_NULL|')AND tes.session_num = NNsession_numAND tes.level_num = prevIterationAND (dep.pv_id, dep.dpv_id) IN/* Remove packages listed in IGNORE_WARNINGS table and|| packages already stored in Temp Table.*/(SELECT pd.pv_id, pd.dpv_idFROM package_dependencies pd,release_content rcWHERE pd.pv_id = rc.pv_idAND rc.rtag_id = NNrtag_id--AND pd.pv_id NOT IN ( SELECT pv_id FROM temp_env_states WHERE session_num = NNsession_num AND level_num > 5 )MINUSSELECT igw.pv_id, igw.dpv_idFROM ignore_warnings igwWHERE igw.rtag_id = NNrtag_id);NNrowCnt := SQL%ROWCOUNT;END Level_N_Conflicts;/CREATE OR REPLACE PROCEDURE Remove_Release_Content ( sPvIdList IN VARCHAR2,sNotPvIdList IN VARCHAR2,nRTagId IN RELEASE_CONTENT.RTAG_ID%TYPE ) IS/* ---------------------------------------------------------------------------Version: 3.0--------------------------------------------------------------------------- */oPvIdCollector RELMGR_NUMBER_TAB_t := RELMGR_NUMBER_TAB_t();oNotPvIdCollector RELMGR_NUMBER_TAB_t := RELMGR_NUMBER_TAB_t();BEGINoPvIdCollector := IN_LIST_NUMBER ( sPvIdList );oNotPvIdCollector := IN_LIST_NUMBER ( sNotPvIdList );-- Delete From Release ContentDELETEFROM RELEASE_CONTENTWHERE rtag_id = nRTagIdAND pv_id IN ( SELECT * FROM TABLE ( CAST ( oPvIdCollector AS RELMGR_NUMBER_TAB_t ) ) )AND NOT pv_id IN ( SELECT * FROM TABLE ( CAST ( oNotPvIdCollector AS RELMGR_NUMBER_TAB_t ) ) );END Remove_Release_Content;/CREATE OR REPLACE PROCEDURE Level_One_Conflicts ( NNrtag_id IN NUMBER,NNsession_num IN NUMBER ) IS/* ---------------------------------------------------------------------------Version: 3.0.0--------------------------------------------------------------------------- */BEGIN/* --------- LEVEL 1 CONFILCTS -----------|| Following states are used:|| 0 -> NOT FOUND|| 1 -> MAJOR|| 2 -> MINOR MINOR*/INSERT INTO temp_env_statesSELECT DISTINCTNNsession_num AS session_num,1 AS level_num, err.pv_id,errpkg.pkg_id, errpkg.v_ext,DECODE(frc.v_nmm,NULL, 0,errpv.v_nmm, DECODE(frc.v_mm, errpv.v_mm, NULL, 2),1) AS messageFROM(/* Full Release Contents used for reference*/SELECT rpv.pkg_id, NVL(rpv.v_ext, '|LINK_A_NULL|') AS v_ext, rpv.pkg_version, rpv.v_nmm, rpv.v_mmFROM release_content rel, package_versions rpvWHERE rel.pv_id = rpv.pv_id AND rtag_id = NNrtag_id) frc,(/* DPV_IDs not fount in release*/SELECT dep.pv_id, dep.dpv_id AS err_dpvFROM package_dependencies depWHERE dep.pv_id IN ( SELECT pv_id FROM release_content WHERE rtag_id = NNrtag_id )AND NOT dep.dpv_id IN ( SELECT pv_id FROM release_content WHERE rtag_id = NNrtag_id )MINUS/* MINUS Dependencies to be ignored */SELECT igw.pv_id, igw.dpv_id AS err_dpvFROM ignore_warnings igwWHERE rtag_id = NNrtag_id) err,package_versions errpkg,package_versions errpvWHERE err.err_dpv = errpv.pv_idAND errpv.pkg_id = frc.pkg_id(+)AND NVL(errpv.v_ext, '|LINK_A_NULL|') = frc.v_ext(+)AND err.pv_id = errpkg.pv_id;END Level_One_Conflicts;/CREATE OR REPLACE FUNCTION GET_AUTOMATED_LABEL ( nPvId IN NUMBER ) RETURN VARCHAR2 IS/* ---------------------------------------------------------------------------Version: 3.0.0--------------------------------------------------------------------------- */sPkgName VARCHAR2(4000);sVext VARCHAR2(4000);BEGIN-- Get package detailsSELECT pkg.PKG_NAME, pv.V_EXT INTO sPkgName, sVextFROM PACKAGE_VERSIONS pv,PACKAGES pkgWHERE pv.PKG_ID = pkg.PKG_IDAND pv.PV_ID = nPvId;-- Generate Label for automated buildRETURN UPPER( sPkgName ||'.'|| nPvId || sVext ||'.WIP' );END GET_AUTOMATED_LABEL;/CREATE OR REPLACE PROCEDURE CLONED_PROCESSESISproc_id NUMBER;/******************************************************************************NAME: DELETE_DO_NOT_RIPPLEPURPOSE: TO DELETE ENTRIES FROM THE DO_NOT_RIPPLE TABLE WHEN A PACKAGEIS RELEASEDREVISIONS:Ver Date Author Description--------- ---------- --------------- ------------------------------------1.0 21/04/2006 Rupesh Solanki 1. Created this procedure.NOTES:Automatically available Auto Replace Keywords:Object Name: DELETE_DO_NOT_RIPPLESysdate: 21/04/2006Date and Time: 21/04/2006, 3:21:55 PM, and 21/04/2006 3:21:55 PMUsername: (set in TOAD Options, Procedure Editor)Table Name: (set in the "New PL/SQL Object" dialog)******************************************************************************/CURSOR ripple_curISselect distinct pkg_health_tag, cmd_interface, pkg_owner, is_interface from deployment_manager.processes_config;ripple_rec ripple_cur%ROWTYPE;BEGINOPEN ripple_cur;FETCH ripple_curINTO ripple_rec;WHILE ripple_cur%FOUNDLOOPselect deployment_manager.seq_proc_id.nextval into proc_id from dual;insert into processes (PROC_ID, PROC_NAME, RUN_AS, PKG_OWNER, IS_INTERFACE)values(proc_id, ripple_rec.pkg_health_tag, ripple_rec.cmd_interface, ripple_rec.pkg_owner, ripple_rec.is_interface);FETCH ripple_curINTO ripple_rec;END LOOP;END CLONED_PROCESSES;/CREATE OR REPLACE PACKAGE pk_packageIS/*------------------------------|| Last Modified: Rupesh Solanki|| Modified Date: 18/05/2006||------------------------------*/TYPE typecur IS REF CURSOR;/*================================================================================================*/PROCEDURE new_version (nlastpvid IN NUMBER,snewpkgversion IN VARCHAR2 DEFAULT NULL,cbuildtype IN CHAR,nsettopvid IN NUMBER DEFAULT NULL,nrtagid IN NUMBER,nuserid IN NUMBER,enumissues_state_imported IN NUMBER,returnpvid OUT NUMBER);PROCEDURE change_state (pvid IN NUMBER,newstate IN package_versions.dlocked%TYPE,userid IN NUMBER);PROCEDURE new_patch (snewpatchversion IN package_versions.pkg_version%TYPE,nparentpvid IN NUMBER,spatchidlist IN VARCHAR2,nuserid IN NUMBER,returnpatchid OUT NUMBER);PROCEDURE obsolete_patch (patchid IN NUMBER,isobsolete IN CHAR,obsoletecomments IN VARCHAR2,userid IN NUMBER);PROCEDURE obsolete_patches (spatchidlist IN VARCHAR2,isobsolete IN CHAR,obsoletecomments IN VARCHAR2,userid IN NUMBER);PROCEDURE destroy_package (pvid IN NUMBER,overridewarnings IN CHAR DEFAULT 'N',problemstring OUT VARCHAR2);PROCEDURE add_process (nprocid IN processes.proc_id%TYPE,shealthtag IN processes.proc_name%TYPE,sprocdesc IN processes.proc_description%TYPE,scmdinterface IN processes.run_as%TYPE,spkgowner IN processes.pkg_owner%TYPE,sisinterface IN processes.is_interface%TYPE,npvid IN package_processes.pv_id%TYPE,nuserid IN NUMBER);PROCEDURE add_package_process (nprocidlist IN VARCHAR2,npvid IN package_processes.pv_id%TYPE,nuserid IN NUMBER);PROCEDURE remove_process (nprocid IN package_processes.proc_id%TYPE,npvid IN package_processes.pv_id%TYPE,nuserid IN NUMBER);PROCEDURE move_package (npvid IN package_versions.pv_id%TYPE,nrtagid IN release_tags.rtag_id%TYPE,nnewrtagid IN release_tags.rtag_id%TYPE,nuserid IN NUMBER);/*================================================================================================*/END pk_package;/CREATE OR REPLACE PROCEDURE Add_Runtime_Dependency ( NNpv_id IN NUMBER,SSrtd_name IN PACKAGES.pkg_name%TYPE,SSrtd_version IN PACKAGE_VERSIONS.pkg_version%TYPE,NNuser_id IN NUMBER) IS/* ---------------------------------------------------------------------------Version: 3.1--------------------------------------------------------------------------- */retRTD_ID NUMBER;CURSOR rtd_cur ISSELECT rtd_id FROM runtime_dependencies WHERE pv_id = NNpv_id AND rtd_id = retRTD_ID;rtd_rec rtd_cur%ROWTYPE;BEGIN--- Seed database with package_name and version if required ---Seed_Package_Names_Versions ( SSrtd_name, SSrtd_version, NNuser_id, retRTD_ID );OPEN rtd_cur;FETCH rtd_cur INTO rtd_rec;IF rtd_cur%NOTFOUNDTHEN/* Make sure it does not exists already as runtime dependency */--- Add new Runtime Dependency ---INSERT INTO runtime_dependencies ( pv_id, rtd_id, mod_date, mod_user )VALUES ( NNpv_id, retRTD_ID, ORA_SYSDATE , NNuser_id );/* LOG ACTION */Log_Action ( NNpv_id, 'runtime_add', NNuser_id,'Runtime package: '|| SSrtd_name ||' '|| SSrtd_version );END IF;END Add_Runtime_Dependency;/CREATE OR REPLACE PROCEDURE ADD_PACKAGE_INTEREST (nProjId IN PROJECTS.PROJ_ID%TYPE,nPkgId IN PACKAGES.PKG_ID%TYPE,nUserId IN NUMBER) IS/******************************************************************************NAME: ADD_PACKAGE_INTERESTPURPOSE:REVISIONS:Ver Date Author Description--------- ---------- --------------- ------------------------------------1.0 12/05/2006 Rupesh Solanki 1. Created this procedure.NOTES:Automatically available Auto Replace Keywords:Object Name: ADD_PACKAGE_INTERESTSysdate: 12/05/2006Date and Time: 12/05/2006, 9:11:12 AM, and 12/05/2006 9:11:12 AMUsername: (set in TOAD Options, Procedure Editor)Table Name: (set in the "New PL/SQL Object" dialog)******************************************************************************/BEGIN--- Insert into PACKAGE_INTEREST TABLEINSERT INTO PACKAGE_INTEREST (PROJ_ID, PKG_ID, USER_ID)VALUES(nProjId, nPkgId, nUserId);END ADD_PACKAGE_INTEREST;/CREATE OR REPLACE FUNCTION CAN_EDIT_PKG_IN_PROJECT ( nPvId IN NUMBER, nRtagId IN NUMBER ) RETURN NUMBER IS/* ---------------------------------------------------------------------------Version: 3.0.0--------------------------------------------------------------------------- */VExt PACKAGE_VERSIONS.V_EXT%TYPE;ProjId NUMBER;RowCount NUMBER;cReleaseMode CHAR(1);BEGIN--RETURN 1;/*--------------- Business Rules Here -------------------*/IF (nRtagId IS NULL) OR (nRtagId < 1)THENRETURN 0;END IF;/*-------------------------------------------------------*//*-- First Check. See if package is used through release reference --*/SELECT COUNT(rc.PV_ID) INTO RowCountFROM (SELECT rl.REF_RTAG_IDFROM RELEASE_LINKS rlWHERE rl.RTAG_ID = nRtagId) rl,RELEASE_CONTENT rcWHERE rc.RTAG_ID = rl.REF_RTAG_IDAND rc.PV_ID = nPvId;-- Decide if package can be editedIF RowCount > 0 THEN-- Package is referenced from other release, hence cannot be editedRETURN 0;ELSE-- Check is only done for releases in restrictive modeSELECT rt.OFFICIAL INTO cReleaseModeFROM RELEASE_TAGS rtWHERE rt.RTAG_ID = nRtagId;IF cReleaseMode = 'N'OR cReleaseMode = 'R' THEN-- Do not do any firther checking,-- Package is editable hereRETURN 1;END IF;END IF;/*-- Further checking --*/-- Get proj_idSELECT rt.PROJ_ID INTO ProjIdFROM RELEASE_TAGS rtWHERE rt.RTAG_ID = nRtagId;BEGIN-- Get v_extSELECT pv.V_EXT INTO VextFROM PACKAGE_VERSIONS pvWHERE pv.PV_ID = nPvId;EXCEPTIONWHEN NO_DATA_FOUND THENVext := NULL;END;--Temp Hack for Step ProjectIF ProjId != 281 THEN-- Find if package can be edited in this projectSELECT COUNT(pe.EXT_NAME) INTO RowCountFROM PROJECT_EXTENTIONS peWHERE pe.PROJ_ID != ProjIdAND pe.EXT_NAME = VExt;END IF;-- Decide if package can be editedIF RowCount > 0 THEN-- Package extension is found in other projects, hece NOT EDITABLERETURN 0;ELSERETURN 1;END IF;END CAN_EDIT_PKG_IN_PROJECT;/CREATE OR REPLACE PACKAGE pk_archiveIS/*------------------------------|| Author: Rupesh Solanki|| Date: 30 October 2006|| Version: 1.0------------------------------*//*================================================================================================*/PROCEDURE restore_and_flush_release_data (nrtagid IN release_tags.rtag_id%TYPE);PROCEDURE write_action_log (nuserid IN NUMBER,nrtagid IN release_tags.rtag_id%TYPE);/*================================================================================================*/END pk_archive;/CREATE OR REPLACE PROCEDURE Rebuild_Environment ( NNrtag_id IN NUMBER ) IS/* ---------------------------------------------------------------------------Version: 3.3--------------------------------------------------------------------------- */rowCnt NUMBER := 0;iteration NUMBER := 2; -- Iterations countermaxIterations NUMBER := 50; -- Maximum number of iterations allowed.-- This will prevent infinite loops if cyrcular dependencies are foundsessionNum NUMBER;BEGINSELECT SEQ_session_num.nextval INTO sessionNum FROM DUAL;-- Redo Patch Ignore warningsIgnore_Dependency_Warnings ( NNrtag_id, NULL, NULL, TRUE );Level_One_Conflicts ( NNrtag_id, sessionNum );LOOPLevel_N_Conflicts ( NNrtag_id, sessionNum, rowCnt, iteration );iteration := iteration + 1;EXIT WHEN (rowCnt < 1) OR (iteration > maxIterations);END LOOP;Update_Package_States ( NNrtag_id, sessionNum );DELETE FROM temp_env_states WHERE temp_env_states.session_num = sessionNum;-- Flag Packages with New Patches AvailableCheck_New_Patches ( NNrtag_id );/* Circular Dependency Flag *//*IF iteration > maxIterationsTHENUPDATE release_tags SETcircular_dependency = 'Y'WHERE rtag_id = NNrtag_id;ELSEUPDATE release_tags SETcircular_dependency = NULLWHERE rtag_id = NNrtag_id;END IF;*/END Rebuild_Environment;/CREATE OR REPLACE PROCEDURE Shift_Install_Order ( nPatchId IN PACKAGE_PATCHES.PATCH_ID%TYPE,nPvId IN PACKAGE_PATCHES.PV_ID%TYPE,nToInstallOrder IN PACKAGE_PATCHES.INSTALL_ORDER%TYPE ) IS/* ---------------------------------------------------------------------------Version: 3.0--------------------------------------------------------------------------- */currInstallOrder NUMBER;FromInstallOrder NUMBER;BEGIN-- Get Current Install OrderSELECT pp.INSTALL_ORDER INTO currInstallOrderFROM PACKAGE_PATCHES ppWHERE pp.PV_ID = nPvIdAND pp.PATCH_ID = nPatchId;IF currInstallOrder > nToInstallOrderTHENFromInstallOrder := nToInstallOrder;-- Shift others UpUPDATE PACKAGE_PATCHES pp SETpp.INSTALL_ORDER = pp.INSTALL_ORDER + 1WHERE pp.PV_ID = nPvIdAND pp.PATCH_ID != nPatchIdAND pp.INSTALL_ORDER BETWEEN FromInstallOrder AND currInstallOrder - 1;ELSIF currInstallOrder < nToInstallOrderTHENFromInstallOrder := currInstallOrder + 1;-- Shift others DownUPDATE PACKAGE_PATCHES pp SETpp.INSTALL_ORDER = pp.INSTALL_ORDER - 1WHERE pp.PV_ID = nPvIdAND pp.PATCH_ID != nPatchIdAND pp.INSTALL_ORDER BETWEEN FromInstallOrder AND nToInstallOrder;END IF;-- Move Patch to new install orderUPDATE PACKAGE_PATCHES pp SETpp.INSTALL_ORDER = nToInstallOrderWHERE pp.PV_ID = nPvIdAND pp.PATCH_ID = nPatchId;END Shift_Install_Order;/CREATE OR REPLACE PROCEDURE Basic_Clone ( nFROMpv_id IN NUMBER,nTOpv_id IN NUMBER,nRtag_id IN NUMBER,nUser_id IN NUMBER,nTOpkg_id IN NUMBER DEFAULT NULL,enumISSUES_STATE_IMPORTED IN NUMBER DEFAULT NULL ) IS/* ---------------------------------------------------------------------------Version: 3.5--------------------------------------------------------------------------- */FromVersion PACKAGE_VERSIONS.PKG_VERSION%TYPE;BEGIN------------------------------------- Clone Dependencies ---------------------------------------------------IF NOT nRtag_id IS NULLTHEN-- Auto Update Dependencies --INSERT INTO PACKAGE_DEPENDENCIESSELECT nTOpv_id AS pv_id,DECODE(nUser_id,frc.modifier_id,frc.pv_id,DECODE(frc.dlocked,'Y',frc.pv_id,dep.dpv_id)) AS dpv_id,nTOpkg_id AS pkg_id,dep.dpkg_id,dep.build_type,dep.display_orderFROM PACKAGE_DEPENDENCIES dep,PACKAGE_VERSIONS pv,(/* Full Release Contents used for reference*/SELECT rpv.pv_id, rpv.pkg_id, NVL(rpv.v_ext, '|LINK_A_NULL|') AS v_ext, rpv.modifier_id, rpv.dlockedFROM RELEASE_CONTENT rel, PACKAGE_VERSIONS rpvWHERE rel.pv_id = rpv.pv_id AND rtag_id = nRtag_id) frcWHERE dep.pv_id = nFROMpv_idAND dep.dpv_id = pv.pv_idAND pv.pkg_id = frc.pkg_id(+)AND NVL(pv.v_ext, '|LINK_A_NULL|') = frc.v_ext(+);ELSE-- Clone Dependencies --INSERT INTO PACKAGE_DEPENDENCIESSELECT nTOpv_id AS pv_id,dep.dpv_id,nTOpkg_id AS pkg_id,dep.dpkg_id,dep.build_type,dep.display_orderFROM PACKAGE_DEPENDENCIES depWHERE dep.pv_id = nFROMpv_id;END IF;----------------------------------------- Clone Issues -------------------------------------------------------IF enumISSUES_STATE_IMPORTED IS NULLTHEN/* All Issues */INSERT INTO CQ_ISSUES ( pv_id, iss_db, iss_id, iss_state, mod_date, notes )SELECT nTOpv_id AS pv_id, iss_db, iss_id, iss_state, mod_date, notesFROM CQ_ISSUESWHERE pv_id = nFROMpv_id;ELSE/* Outstanding Issues Only */INSERT INTO CQ_ISSUES ( pv_id, iss_db, iss_id, iss_state, mod_date, notes )SELECT nTOpv_id AS pv_id, iss_db, iss_id, iss_state, mod_date, notesFROM CQ_ISSUESWHERE pv_id = nFROMpv_idAND iss_state = enumISSUES_STATE_IMPORTED;END IF;------------------------------------ Clone Runtime Dependencies -----------------------------------------------INSERT INTO RUNTIME_DEPENDENCIES (pv_id, rtd_id, rtd_url, rtd_comments, mod_date, mod_user)SELECT nTOpv_id AS pv_id, rtd_id, rtd_url, rtd_comments, mod_date, mod_userFROM RUNTIME_DEPENDENCIES WHERE pv_id = nFROMpv_id;--------------------------------------- Clone Additional Notes ------------------------------------------------INSERT INTO ADDITIONAL_NOTES ( NOTE_ID, PV_ID, NOTE_TITLE, NOTE_BODY, MOD_DATE, MOD_USER )SELECT an.NOTE_ID,nTOpv_id AS PV_ID,an.NOTE_TITLE,an.NOTE_BODY,an.MOD_DATE,an.MOD_USERFROM ADDITIONAL_NOTES anWHERE an.PV_ID = nFROMpv_id;-------------------------------------------- Clone Unit Tests --------------------------------------------------- Clone only Automatic unit tests --INSERT INTO UNIT_TESTS (TEST_ID,PV_ID,TEST_TYPES_FK,TEST_SUMMARY,COMPLETION_DATE,COMPLETED_BY,RESULTS_URL,RESULTS_ATTACHMENT_NAME,NUMOF_TEST)SELECT ut.TEST_ID,nTOpv_id AS PV_ID,ut.TEST_TYPES_FK,ut.TEST_SUMMARY,Ora_Sysdate AS COMPLETION_DATE,nUser_id AS COMPLETED_BY,ut.RESULTS_URL,ut.RESULTS_ATTACHMENT_NAME,ut.NUMOF_TESTFROM UNIT_TESTS utWHERE ut.PV_ID = nFROMpv_idAND ut.TEST_TYPES_FK IN ( 5, 7 );-- Clone only Interactive Unit Tests --INSERT INTO UNIT_TESTS (TEST_ID,PV_ID,TEST_TYPES_FK,TEST_SUMMARY)SELECT ut.TEST_ID,nTOpv_id AS PV_ID,ut.TEST_TYPES_FK,ut.TEST_SUMMARYFROM UNIT_TESTS utWHERE ut.PV_ID = nFROMpv_idAND ut.TEST_TYPES_FK IN (6);-------------------------------------------- Clone Package Documents ------------------------------------------INSERT INTO PACKAGE_DOCUMENTS ( PV_ID, TEST_ID, DOC_NUM, DOC_ID, IS_LATEST )SELECT nTOpv_id AS PV_ID,pd.test_id,pd.doc_num,pd.doc_id,pd.IS_LATESTFROM PACKAGE_DOCUMENTS pdWHERE pd.PV_ID = nFROMpv_id;-------------------------------------------- Clone Build Environments -----------------------------------------INSERT INTO PACKAGE_BUILD_ENV ( PV_ID, BE_ID, BUILD_TYPE )SELECT nTOpv_id AS PV_ID,pkgbe.BE_ID,pkgbe.BUILD_TYPEFROM PACKAGE_BUILD_ENV pkgbeWHERE pkgbe.PV_ID = nFROMpv_id;---------------------------------------------Clone Package Version Processes-----------------------------------INSERT INTO PACKAGE_PROCESSES ( PV_ID, PROC_ID )SELECT nTOpv_id AS PV_ID,pp.PROC_IDFROM PACKAGE_PROCESSES ppWHERE pp.PV_ID = nFROMpv_id;---------------------------------------------------------------------------------------------------------------/* LOG ACTION */SELECT pv.PKG_VERSION INTO FromVersionFROM PACKAGE_VERSIONS pvWHERE pv.PV_ID = nFROMpv_id;Log_Action ( nTOpv_id, 'clone_from', nUser_id,'Details cloned from: '|| FromVersion );---------------------------------------------------------------------------------------------------------------END Basic_Clone;/CREATE OR REPLACE PROCEDURE Remove_Components ( nPvId IN RELEASE_COMPONENTS.PV_ID%TYPE ) IS/* ---------------------------------------------------------------------------Version: 3.0.0--------------------------------------------------------------------------- */BEGIN--- Delete From RELEASE_COMPONENTSDELETE FROM RELEASE_COMPONENTSWHERE PV_ID = nPvId;END Remove_Components;/CREATE OR REPLACE PROCEDURE check_new_patches (nrtagid IN release_content.rtag_id%TYPE)IS/* ---------------------------------------------------------------------------Version: 3.0--------------------------------------------------------------------------- */BEGIN/*--------------- Business Rules Here -------------------*//*IF nRtagId NOT IN (SELECT RTAG_ID FROM RELEASE_TAGS ) THENRAISE_APPLICATION_ERROR (-20000, 'Please contact the person in charge of Release Manager now.' );END IF/*-------------------------------------------------------*/UPDATE release_content rcSET rc.pkg_state = 5 -- enumPKG_STATE_NEW_PATCHWHERE rc.pv_id IN (SELECT prod.pv_idFROM (SELECT pp.pv_id AS orig_parent_id,COUNT (*) AS num_of_patchesFROM release_content rc, package_patches ppWHERE rc.pv_id = pp.pv_id AND rc.rtag_id = nrtagidGROUP BY pp.pv_id) orig,(SELECT prodpp.pv_id, pp.pv_id AS parent_id,COUNT (*) AS num_of_patchesFROM release_content rc,package_patches pp,package_dependencies dep,package_versions pv,package_patches prodppWHERE rc.pv_id = pp.pv_idAND rc.rtag_id = nrtagidAND pp.patch_id = dep.dpv_idAND dep.pv_id = pv.pv_idAND pv.is_patch = 'Y'AND pv.dlocked = 'Y'AND prodpp.patch_id = dep.pv_idGROUP BY prodpp.pv_id, pp.pv_id) prod,release_content rcWHERE orig.orig_parent_id = prod.pv_idAND orig.num_of_patches != prod.num_of_patchesAND rc.rtag_id = nrtagidAND rc.pv_id = prod.pv_idAND rc.pkg_state = 0UNIONSELECT prodpp.pv_idFROM release_content rc,package_patches pp,package_dependencies dep,package_versions pv,package_patches prodppWHERE rc.pv_id = pp.pv_idAND rc.rtag_id = nrtagidAND pp.patch_id = dep.dpv_idAND dep.pv_id = pv.pv_idAND pv.is_patch = 'Y'AND pv.dlocked = 'Y'AND prodpp.patch_id = dep.pv_idGROUP BY prodpp.pv_id, pp.pv_idMINUSSELECT pp.pv_idFROM release_content rc, package_patches ppWHERE rc.pv_id = pp.pv_id AND rc.rtag_id = nrtagidGROUP BY pp.pv_id);END check_new_patches;/CREATE OR REPLACE PROCEDURE Sync_Projects_New_Version ( nPv_id IN NUMBER,sSyncRtagsInList IN VARCHAR2,nUser_id IN NUMBER ) IS/* ---------------------------------------------------------------------------Version: 3.0.0--------------------------------------------------------------------------- */CURSOR sync_rtags_cur ISSELECT rc.RTAG_ID, rc.BASE_VIEW_ID, pv.pv_idFROM release_content rc,package_versions pv,package_versions opvWHERE rc.pv_id = pv.pv_idAND opv.pkg_id = pv.pkg_idAND NVL(opv.v_ext, '|LINK_A_NULL|') = NVL(pv.v_ext, '|LINK_A_NULL|')AND opv.pv_id = nPv_idAND rtag_id IN ( SELECT *FROM THE (SELECT CAST( IN_LIST_NUMBER( sSyncRtagsInList ) AS RELMGR_NUMBER_TAB_t )FROM dual));sync_rtags_rec sync_rtags_cur%ROWTYPE;BEGINOPEN sync_rtags_cur;FETCH sync_rtags_cur INTO sync_rtags_rec;WHILE sync_rtags_cur%FOUNDLOOPDELETE FROM RELEASE_CONTENTWHERE rtag_id = sync_rtags_rec.rtag_idAND pv_id = sync_rtags_rec.pv_id;INSERT INTO RELEASE_CONTENT ( RTAG_ID, PV_ID, BASE_VIEW_ID, INSERT_STAMP, INSERTOR_ID, PKG_STATE )VALUES ( sync_rtags_rec.rtag_id,nPv_id,sync_rtags_rec.BASE_VIEW_ID,Ora_Sysdate,nUser_id,0);-- UPDATE RELEASE_CONTENT-- SET pv_id = nPv_id,-- insert_stamp = Ora_Sysdate,-- insertor_id = nUser_id-- WHERE rtag_id = sync_rtags_rec.rtag_id-- AND pv_id = sync_rtags_rec.pv_id;FETCH sync_rtags_cur INTO sync_rtags_rec;END LOOP;CLOSE sync_rtags_cur;END Sync_Projects_New_Version;/CREATE OR REPLACE PROCEDURE Update_Package_Dependency ( NNpv_id IN NUMBER,SSdep_name IN PACKAGES.pkg_name%TYPE,SSdep_version IN PACKAGE_VERSIONS.pkg_version%TYPE,CCbuild_type IN PACKAGE_DEPENDENCIES.build_type%TYPE,NNuser_id IN NUMBER,NNdelete_old_dependency IN NUMBER) IS/* ---------------------------------------------------------------------------Version: 3.3--------------------------------------------------------------------------- */retPV_ID NUMBER;SSV_EXT PACKAGE_VERSIONS.v_ext%TYPE;DepIsPatch PACKAGE_VERSIONS.is_patch%TYPE;PvIsPatch PACKAGE_VERSIONS.is_patch%TYPE;NNpkg_id NUMBER;NNdpkg_id NUMBER;BEGIN--- Seed database with package_name and version if required ---Seed_Package_Names_Versions ( SSdep_name, SSdep_version, NNuser_id, retPV_ID );-- get v_ext,pkg_id of current dependencySELECT v_ext, pkg_id, is_patch INTO SSV_EXT, NNdpkg_id, DepIsPatchFROM PACKAGE_VERSIONSWHERE pv_id = retPV_ID;-- get pkg_id of parent packageSELECT pkg_id, is_patch INTO NNpkg_id, PvIsPatchFROM PACKAGE_VERSIONSWHERE pv_id = NNpv_id;IF ( (PvIsPatch IS NULL) AND (DepIsPatch IS NULL) ) OR( (PvIsPatch IS NOT NULL) )THENIF NNdelete_old_dependency = 1 THEN/* Used for CUSTOM dependency add/edit */--- Remove old dependency ---IF (PvIsPatch IS NULL) THEN-- Do it for PackagesDELETE FROM PACKAGE_DEPENDENCIESWHERE ( pv_id, dpv_id, pkg_id, dpkg_id, build_type, display_order ) IN(SELECT dep.*FROM PACKAGE_DEPENDENCIES dep,PACKAGE_VERSIONS dpv,PACKAGE_VERSIONS pvWHERE dep.dpv_id = dpv.pv_idAND dep.pv_id = NNpv_idAND pv.pv_id = retPV_IDAND dpv.pkg_id = pv.pkg_idAND NVL(dpv.v_ext, '|LINK_A_NULL|') = NVL(SSV_EXT, '|LINK_A_NULL|'));ELSE-- Do it for PatchesDELETE FROM PACKAGE_DEPENDENCIESWHERE ( pv_id, dpv_id, pkg_id, dpkg_id, build_type, display_order ) IN(SELECT dep.*FROM PACKAGE_DEPENDENCIES depWHERE dep.dpv_id = retPV_IDAND dep.pv_id = NNpv_id);END IF;END IF;/* NOTE: for JATS, ANTS dependency update, it is expected that all dependencies are removed. */--- Add new dependency ---INSERT INTO PACKAGE_DEPENDENCIES ( pv_id, dpv_id, pkg_id, dpkg_id, build_type )VALUES ( NNpv_id, retPV_ID, NNpkg_id, NNdpkg_id, CCbuild_type );END IF;END Update_Package_Dependency;/CREATE OR REPLACE FUNCTION DT_ADDUSEROBJECT RETURN NUMBER IS NEWOBJECTID NUMBER; BEGIN /* GET THE NEXT VALUE FROM THE SEQUENCE FOR THE OBJECT ID */ SELECT MICROSOFTSEQDTPROPERTIES.NEXTVAL INTO NEWOBJECTID FROM DUAL; /* ADD THE PARENT NODE FOR THE NEW OBJECT */ INSERT INTO MICROSOFTDTPROPERTIES ( ID, OBJECTID, PROPERTY ) VALUES ( NEWOBJECTID, NEWOBJECTID, 'DtgSchemaOBJECT' ); RETURN( NEWOBJECTID ); END DT_ADDUSEROBJECT;/CREATE OR REPLACE PACKAGE PK_APP_UTILS IS/*------------------------------|| Last Modified: S.Vukovic|| Modified Date: 26/Apr/2005|| Spec Version: 1.0------------------------------*/TYPE typeCur IS REF CURSOR;/*================================================================================================*/PROCEDURE GET_CURRENT_PARAMETERS ( ProjId IN VARCHAR2, RtagId IN VARCHAR2, records OUT typeCur);/*================================================================================================*/END PK_APP_UTILS;/CREATE OR REPLACE PROCEDURE Log_Project_Action ( nProjId IN NUMBER,enumActionTypeName IN VARCHAR2,nUserId IN NUMBER,sAdditionalComments IN VARCHAR2 DEFAULT NULL,nRtagId IN NUMBER ) IS/* ---------------------------------------------------------------------------Version: 3.0--------------------------------------------------------------------------- */ActionTypeId NUMBER;BEGIN-- Get Action Type FKSELECT act.ACTTYPE_ID INTO ActionTypeIdFROM ACTION_TYPE actWHERE act.NAME = enumActionTypeName;INSERT INTO PROJECT_ACTION_LOG ( USER_ID, ACTION_DATETIME, PROJ_ID, DESCRIPTION, ACTTYPE_ID, RTAG_ID)VALUES ( nUserId, ORA_SYSDATETIME, nProjId, sAdditionalComments, ActionTypeId, nRtagId);END Log_Project_Action;/CREATE OR REPLACE PROCEDURE RELEASE_MANAGER_MAILOUT (sender IN VARCHAR2,recipient IN VARCHAR2,ccrecipient IN VARCHAR2,subject IN VARCHAR2,message IN VARCHAR2) IScrlf VARCHAR2(2):= UTL_TCP.CRLF;connection utl_smtp.connection;mailhost VARCHAR2(30) := 'aupera03.aupera.erggroup.com';header VARCHAR2(1000);BEGIN---- Start the connection.--connection := utl_smtp.open_connection(mailhost,25);header:= 'Date: '||TO_CHAR(SYSDATE,'dd Mon yy hh24:mi:ss')||crlf||'From: '||sender||''||crlf||'Subject: '||subject||crlf||'To: '||recipient||crlf||'CC: '||ccrecipient;---- Handshake with the SMTP server--utl_smtp.helo(connection, mailhost);utl_smtp.mail(connection, sender);utl_smtp.rcpt(connection, recipient);utl_smtp.rcpt(connection, ccrecipient);utl_smtp.open_data(connection);---- Write the header--utl_smtp.write_data(connection, header);---- The crlf is required to distinguish that what comes next is not simply part of the header..--utl_smtp.write_data(connection, crlf ||message);utl_smtp.close_data(connection);utl_smtp.quit(connection);EXCEPTIONWHEN UTL_SMTP.INVALID_OPERATION THENdbms_output.put_line(' Invalid Operation in SMTP transaction.');WHEN UTL_SMTP.TRANSIENT_ERROR THENdbms_output.put_line(' Temporary problems with sending email - try again later.');WHEN UTL_SMTP.PERMANENT_ERROR THENdbms_output.put_line(' Errors in code for SMTP transaction.');END RELEASE_MANAGER_MAILOUT;/CREATE OR REPLACE FUNCTION IN_LIST_VARCHAR2 ( sInList IN VARCHAR2, cSeparator IN VARCHAR2 ) RETURN RELMGR_VARCHAR2_TAB_t IS/* ---------------------------------------------------------------------------Version: 3.1--------------------------------------------------------------------------- */cItemCollection RELMGR_VARCHAR2_TAB_t := RELMGR_VARCHAR2_TAB_t();pos NUMBER;in_list VARCHAR2(4000) := sInList || cSeparator;val VARCHAR2(4000);BEGINIF NOT sInList IS NULLTHENLOOPEXIT WHEN in_list IS NULL;pos := INSTR ( in_list, cSeparator );val := LTRIM ( RTRIM ( SUBSTR ( in_list, 1, pos-1 ) ) );IF (NOT val IS NULL) THENcItemCollection.extend;cItemCollection(cItemCollection.count) := val;END IF;in_list := SUBSTR ( in_list, pos+1 );END LOOP;END IF;RETURN cItemCollection;END IN_LIST_VARCHAR2;/CREATE OR REPLACE PROCEDURE New_Package_Name ( SSpkg_name IN PACKAGES.pkg_name%TYPE,retPKG_ID OUT NUMBER ) IS/* ---------------------------------------------------------------------------Version: 3.0.0--------------------------------------------------------------------------- */CURSOR packages_cur ISSELECT pkg.pkg_idFROM packages pkgWHERE pkg.pkg_name = SSpkg_name;packages_rec packages_cur%ROWTYPE;BEGIN/* ---------------------------------------------------- *//* Find if package name exists *//* ---------------------------------------------------- */OPEN packages_cur;FETCH packages_cur INTO packages_rec;IF packages_cur%NOTFOUNDTHEN-- Create new pkg_name --SELECT SEQ_PKG_ID.nextval INTO retPKG_ID FROM DUAL;INSERT INTO packages ( pkg_id, pkg_name )VALUES ( retPKG_ID, SSpkg_name );ELSEretPKG_ID := packages_rec.pkg_id;END IF;CLOSE packages_cur;END New_Package_Name;/CREATE OR REPLACE PROCEDURE UPDATE_PROCESSESISproc_id NUMBER;/******************************************************************************NAME: DELETE_DO_NOT_RIPPLEPURPOSE: TO DELETE ENTRIES FROM THE DO_NOT_RIPPLE TABLE WHEN A PACKAGEIS RELEASEDREVISIONS:Ver Date Author Description--------- ---------- --------------- ------------------------------------1.0 21/04/2006 Rupesh Solanki 1. Created this procedure.NOTES:Automatically available Auto Replace Keywords:Object Name: DELETE_DO_NOT_RIPPLESysdate: 21/04/2006Date and Time: 21/04/2006, 3:21:55 PM, and 21/04/2006 3:21:55 PMUsername: (set in TOAD Options, Procedure Editor)Table Name: (set in the "New PL/SQL Object" dialog)******************************************************************************/CURSOR ripple_curISselect proc_id, proc_name, run_as, pkg_owner, is_interface from processes;ripple_rec ripple_cur%ROWTYPE;BEGINOPEN ripple_cur;FETCH ripple_curINTO ripple_rec;WHILE ripple_cur%FOUNDLOOPupdate deployment_manager.processes_configset proc_id = ripple_rec.proc_idwhere pkg_health_tag = ripple_rec.proc_nameand cmd_interface IS NULLand pkg_owner IS NULLand is_interface IS NULL;FETCH ripple_curINTO ripple_rec;END LOOP;END UPDATE_PROCESSES;/CREATE OR REPLACE PROCEDURE Build_Tree ( nRtag_id IN NUMBER,retSessionNum OUT NUMBER ) IS/* ---------------------------------------------------------------------------Version: 3.0.0--------------------------------------------------------------------------- */rowCnt NUMBER := 0; -- Iterations counteriteration NUMBER := 1; -- Maximum number of iterations allowed.maxIterations NUMBER := 50; -- This will prevent infinite loops if cyrcular dependencies are foundUP_THE_TREE CONSTANT NUMBER DEFAULT 1;DOWN_THE_TREE CONSTANT NUMBER DEFAULT -1;sessionNum NUMBER;levelNum NUMBER;BEGINSELECT SEQ_session_num.nextval INTO sessionNum FROM DUAL;/*|| Start UP THE TREE*//* Packages with no dependencies */INSERT INTO TEMP_TREE_BROWSE (SESSION_NUM, LEVEL_NUM, DIRECTION, PV_ID, PKG_ID, V_EXT )SELECT sessionNum AS SESSION_NUM,iteration AS LEVEL_NUM,UP_THE_TREE AS DIRECTION,rc.pv_id, pv.pkg_id, pv.v_extFROM release_content rc,package_versions pvWHERE rc.rtag_id = nRtag_idAND rc.pv_id = pv.pv_idMINUSSELECT sessionNum AS SESSION_NUM,iteration AS LEVEL_NUM,UP_THE_TREE AS DIRECTION,dep.pv_id, pv.pkg_id, pv.v_extFROM package_dependencies dep,package_versions pvWHERE dep.pv_id IN ( SELECT pv_id FROM release_content WHERE rtag_id = nRtag_id )AND dep.pv_id = pv.pv_id;/* Browse UP the build tree */iteration := iteration + 1;LOOPINSERT INTO TEMP_TREE_BROWSE (SESSION_NUM, LEVEL_NUM, DIRECTION, PV_ID, PKG_ID, V_EXT )SELECT DISTINCTsessionNum AS SESSION_NUM,iteration AS LEVEL_NUM,UP_THE_TREE AS DIRECTION,rdep.pv_id, rdep.pkg_id, rdep.v_extFROM (SELECT dep.pv_id, pv.pkg_id, pv.v_ext, dep.dpv_id, dpv.pkg_id AS dpkg_id, dpv.v_ext AS dv_extFROM package_dependencies dep,release_content rc,package_versions pv,package_versions dpvWHERE dep.pv_id = rc.pv_idAND rc.rtag_id = nRtag_idAND dep.pv_id = pv.pv_idAND dep.dpv_id = dpv.pv_id) rdep,temp_tree_browse ttbWHERE rdep.dpkg_id = ttb.pkg_idAND NVL(rdep.dv_ext ,'|LINK_A_NULL|') = NVL(ttb.v_ext,'|LINK_A_NULL|')AND ttb.SESSION_NUM = sessionNumAND ttb.LEVEL_NUM = iteration - 1MINUS/* Packages with all depencencies NOT matched */SELECT DISTINCTsessionNum AS SESSION_NUM,iteration AS LEVEL_NUM,UP_THE_TREE AS DIRECTION,rdep.pv_id, rdep.pkg_id, rdep.v_extFROM (SELECT dep.pv_id, pv.pkg_id, pv.v_ext, dep.dpv_id, dpv.pkg_id AS dpkg_id, dpv.v_ext AS dv_extFROM package_dependencies dep,release_content rc,package_versions pv,package_versions dpvWHERE dep.pv_id = rc.pv_idAND rc.rtag_id = nRtag_idAND dep.pv_id = pv.pv_idAND dep.dpv_id = dpv.pv_id) rdep,(SELECT tb.*FROM temp_tree_browse tbWHERE tb.SESSION_NUM = sessionNum) ttbWHERE rdep.dpkg_id = ttb.pkg_id (+)AND NVL(rdep.dv_ext ,'|LINK_A_NULL|') = NVL(ttb.v_ext (+),'|LINK_A_NULL|')AND ttb.SESSION_NUM IS NULL;rowCnt := SQL%ROWCOUNT;IF rowCnt > 0 THENiteration := iteration + 1;END IF;EXIT WHEN (rowCnt < 1) OR (iteration > maxIterations);END LOOP;/*---------------------------------------------------------------------------------------------------------------------*//* Check for unresolved dependencies||*//* UNRESOLVED */SELECT COUNT(*) INTO rowCntFROM (SELECT pv.pv_id, pv.pkg_id, pv.v_extFROM release_content rc,package_versions pvWHERE rc.rtag_id = nRtag_idAND rc.pv_id = pv.pv_idMINUSSELECT ttb.pv_id, ttb.pkg_id, ttb.v_extFROM temp_tree_browse ttbWHERE ttb.session_num = sessionNum);IF rowCnt > 0THEN/* Circular dependencies detected.|| Try to resolve build order from the top now.|| Start DOWN THE TREE*/iteration := 0;/* Top Level packages */INSERT INTO TEMP_TREE_BROWSE (SESSION_NUM, LEVEL_NUM, DIRECTION, PV_ID, PKG_ID, V_EXT )SELECT sessionNum AS SESSION_NUM,iteration AS LEVEL_NUM,DOWN_THE_TREE AS DIRECTION,pv.pv_id, pv.pkg_id, pv.v_extFROM (/* Packages no one depends on ( Top level packages )*/(/* All parents*/SELECT pv.pkg_id, pv.v_extFROM package_dependencies dep,release_content rc,package_versions pvWHERE dep.pv_id = rc.pv_idAND rc.rtag_id = nRtag_idAND dep.pv_id = pv.pv_idMINUS/* All children */SELECT dpv.pkg_id, dpv.v_extFROM package_dependencies dep,release_content rc,package_versions dpvWHERE dep.pv_id = rc.pv_idAND rc.rtag_id = nRtag_idAND dep.dpv_id = dpv.pv_id)MINUS/* Packages with resolved dependencies from UP THE TREE */SELECT ttb.pkg_id, ttb.v_extFROM temp_tree_browse ttbWHERE ttb.session_num = sessionNum) tpkg,package_versions pv,release_content rcWHERE rc.rtag_id = nRtag_idAND rc.pv_id = pv.pv_idAND tpkg.pkg_id = pv.pkg_idAND NVL(tpkg.v_ext,'|LINK_A_NULL|') = NVL(pv.v_ext,'|LINK_A_NULL|');/* Keep taking packages which no one depende on */iteration := iteration - 1;LOOPINSERT INTO TEMP_TREE_BROWSE (SESSION_NUM, LEVEL_NUM, DIRECTION, PV_ID, PKG_ID, V_EXT )SELECT sessionNum AS SESSION_NUM,iteration AS LEVEL_NUM,DOWN_THE_TREE AS DIRECTION,pv.pv_id, pv.pkg_id, pv.v_extFROM (/* All Unresolved */(SELECT pv.pkg_id, pv.v_extFROM release_content rc,package_versions pvWHERE rc.rtag_id = nRtag_idAND rc.pv_id = pv.pv_idMINUSSELECT ttb.pkg_id, ttb.v_extFROM temp_tree_browse ttbWHERE ttb.session_num = sessionNum)MINUS(/* Children of Unresolved */SELECT dpv.pkg_id, dpv.V_EXTFROM (SELECT pv.pv_id, pv.pkg_id, pv.v_extFROM release_content rc,package_versions pvWHERE rc.rtag_id = nRtag_idAND rc.pv_id = pv.pv_idMINUSSELECT ttb.pv_id, ttb.pkg_id, ttb.v_extFROM temp_tree_browse ttbWHERE ttb.session_num = sessionNum) unr,package_dependencies dep,package_versions dpvWHERE unr.pv_id = dep.pv_idAND dep.dpv_id = dpv.pv_id)) tpkg,package_versions pv,release_content rcWHERE rc.rtag_id = nRtag_idAND rc.pv_id = pv.pv_idAND tpkg.pkg_id = pv.pkg_idAND NVL(tpkg.v_ext,'|LINK_A_NULL|') = NVL(pv.v_ext,'|LINK_A_NULL|');rowCnt := SQL%ROWCOUNT;IF rowCnt > 0 THENiteration := iteration - 1;END IF;EXIT WHEN (rowCnt < 1);END LOOP;END IF;/*---------------------------------------------------------------------------------------------------------------------*//*|| Save results from temp table*//* Clean up build_order table */DELETE FROM BUILD_ORDER WHERE rtag_id = nRtag_id;/* Save UP THE TREE */INSERT INTO BUILD_ORDER ( RTAG_ID, STEP_NUM, PV_ID )SELECT nRtag_id AS rtag_id,ttb.level_num AS step_num,ttb.PV_IDFROM temp_tree_browse ttbWHERE ttb.session_num = sessionNumAND ttb.direction = UP_THE_TREE;/*Get last step_num */SELECT MAX(ttb.level_num) + 1 INTO levelNumFROM temp_tree_browse ttb WHERE ttb.session_num = sessionNum AND ttb.DIRECTION = UP_THE_TREE;/* UNRESOLVED */SELECT COUNT(*) INTO rowCntFROM (SELECT pv.pv_id, pv.pkg_id, pv.v_extFROM release_content rc,package_versions pvWHERE rc.rtag_id = nRtag_idAND rc.pv_id = pv.pv_idMINUSSELECT ttb.pv_id, ttb.pkg_id, ttb.v_extFROM temp_tree_browse ttbWHERE ttb.session_num = sessionNum);IF rowCnt > 0THEN/* Save unresolved packages */INSERT INTO BUILD_ORDER ( RTAG_ID, STEP_NUM, PV_ID, UNRESOLVED )SELECT nRtag_id AS rtag_id,levelNum AS step_num,upv.PV_ID,'Y' AS UNRESOLVEDFROM (SELECT pv.pv_id, pv.pkg_id, pv.v_extFROM release_content rc,package_versions pvWHERE rc.rtag_id = nRtag_idAND rc.pv_id = pv.pv_idMINUSSELECT ttb.pv_id, ttb.pkg_id, ttb.v_extFROM temp_tree_browse ttbWHERE ttb.session_num = sessionNum) upv;END IF;/* Save DOWN THE TREE */levelNum := 1000;INSERT INTO BUILD_ORDER ( RTAG_ID, STEP_NUM, PV_ID )SELECT nRtag_id AS rtag_id,levelNum + ttb.level_num AS step_num,ttb.PV_IDFROM temp_tree_browse ttbWHERE ttb.session_num = sessionNumAND ttb.direction = DOWN_THE_TREE;/*---------------------------------------------------------------------------------------------------------------------*//* Clean up temp table */DELETE FROM TEMP_TREE_BROWSE WHERE session_num = sessionNum;retSessionNum := sessionNum;END Build_Tree;/CREATE OR REPLACE PACKAGE PK_BUILDAPI IS/*------------------------------|| Last Modified: S.Vukovic|| Modified Date: 30/Mar/2005|| Spec Version: 1.0------------------------------*//*================================================================================================*/PROCEDURE Add_Product_Component ( nPvId IN NUMBER,sOsName IN VARCHAR2,sOrigFilePath IN VARCHAR2,sFileName IN VARCHAR2,sDestFilePath IN VARCHAR2,nByteSize IN NUMBER,sCRCcksum IN VARCHAR2 );PROCEDURE Remove_All_Product_Components ( nPvId IN NUMBER, sOsName IN VARCHAR2 );/*================================================================================================*/FUNCTION Get_OsId ( sOsName IN VARCHAR2 ) RETURN NUMBER;/*================================================================================================*/END PK_BUILDAPI;/CREATE OR REPLACE PROCEDURE Remove_Patch ( nPatchId IN PACKAGE_PATCHES.PATCH_ID%TYPE,nPvId IN PACKAGE_PATCHES.PV_ID%TYPE,nUserId IN NUMBER ) IS/* ---------------------------------------------------------------------------Version: 4.0--------------------------------------------------------------------------- */PatchVersion PACKAGE_VERSIONS.PKG_VERSION%TYPE;CURSOR curPatch ISSELECT pp.PV_ID,pp.PATCH_ID,ROWNUM AS NEW_INSTALL_ORDERFROM PACKAGE_PATCHES ppWHERE pp.PV_ID = nPvIdORDER BY pp.INSTALL_ORDER;recPatch curPatch%ROWTYPE;BEGIN-- Delete PatchDELETEFROM PACKAGE_PATCHES ppWHERE pp.PV_ID = nPvIdAND pp.PATCH_ID = nPatchId;-- Redo Install OrderOPEN curPatch;FETCH curPatch INTO recPatch;WHILE curPatch%FOUNDLOOPUPDATE PACKAGE_PATCHES pp SETpp.INSTALL_ORDER = recPatch.NEW_INSTALL_ORDERWHERE pp.PV_ID = nPvIdAND pp.PATCH_ID = recPatch.PATCH_ID;FETCH curPatch INTO recPatch;END LOOP;CLOSE curPatch;/* LOG ACTION */SELECT pv.PKG_VERSION INTO PatchVersionFROM PACKAGE_VERSIONS pvWHERE pv.PV_ID = nPatchId;Log_Action ( nPvId, 'patch_remove', nUserId,'Version: '|| PatchVersion );END Remove_Patch;/CREATE OR REPLACE TYPE "RELMGR_NUMBER_TAB_T" as TABLE of NUMBER/CREATE OR REPLACE PROCEDURE Add_Component ( nPvId IN RELEASE_COMPONENTS.PV_ID%TYPE,sFileName IN RELEASE_COMPONENTS.FILE_NAME%TYPE,sFilePath IN RELEASE_COMPONENTS.FILE_PATH%TYPE DEFAULT NULL,nByteSize IN RELEASE_COMPONENTS.BYTE_SIZE%TYPE,sCRCcksum IN RELEASE_COMPONENTS.CRC_CKSUM%TYPE DEFAULT NULL,sCRCmodcrc IN RELEASE_COMPONENTS.CRC_MODCRC%TYPE DEFAULT NULL) IS/* ---------------------------------------------------------------------------Version: 3.0.0--------------------------------------------------------------------------- */BEGIN--- Insert into RELEASE_COMPONENTSINSERT INTO RELEASE_COMPONENTS ( PV_ID, FILE_NAME, FILE_PATH, BYTE_SIZE, CRC_CKSUM, CRC_MODCRC )VALUES ( nPvId, sFileName, sFilePath, nByteSize, sCRCcksum, sCRCmodcrc );END Add_Component;/CREATE OR REPLACE FUNCTION ORA_SYSDATETIMERETURN DATEIS/* ---------------------------------------------------------------------------Version: 3.0.0--------------------------------------------------------------------------- */BEGINRETURN TO_DATE( TO_CHAR( SYSDATE,'DD-MON-YYYY HH24:MI:SS' ),'DD-MON-YYYY HH24:MI:SS' );END ORA_SYSDATETIME;/CREATE OR REPLACE PROCEDURE New_Additional_Note ( pnPv_id IN NUMBER,psNote_title IN ADDITIONAL_NOTES.NOTE_TITLE%TYPE,psNote_body IN ADDITIONAL_NOTES.NOTE_BODY%TYPE,pnUser_id IN NUMBER,outErrCode OUT NUMBER) IS/* ---------------------------------------------------------------------------Version: 3.0.0--------------------------------------------------------------------------- */newID NUMBER;CURSOR an_duplicate_cur ISSELECT note_idFROM ADDITIONAL_NOTESWHERE pv_id = pnPv_idAND note_title = psNote_title;an_duplicate_rec an_duplicate_cur%ROWTYPE;BEGINoutErrCode := -1; -- Set default return error code to ERROR stateOPEN an_duplicate_cur;FETCH an_duplicate_cur INTO an_duplicate_rec;IF an_duplicate_cur%NOTFOUNDTHEN/* No duplicate titles */-- Get new ID --SELECT SEQ_ADDITIONAL_NOTES.NEXTVAL INTO newID FROM DUAL;--- Add Additional Note ---INSERT INTO ADDITIONAL_NOTES ( note_id, pv_id, note_title, note_body, mod_date, mod_user )VALUES ( newID, pnPv_id, psNote_title, psNote_body, Ora_Sysdate, pnUser_id );outErrCode := 0; -- Set return to SUCCESSEND IF;CLOSE an_duplicate_cur;END New_Additional_Note;/CREATE OR REPLACE PROCEDURE REMOVE_PROJECT_VIEW_OWNER (nProjId IN NUMBER,nViewId IN NUMBER,sUserIdList IN VARCHAR2,nUserId IN NUMBER ) ISGroupId NUMBER;/******************************************************************************NAME: UPDATE_VIEWPURPOSE:REVISIONS:Ver Date Author Description--------- ---------- --------------- ------------------------------------1.0 10/04/2006 Rupesh Solanki 1. Created this procedure.NOTES:Automatically available Auto Replace Keywords:Object Name: UPDATE_VIEWSysdate: 10/04/2006Date and Time: 10/04/2006, 1:55:19 PM, and 10/04/2006 1:55:19 PMUsername: (set in TOAD Options, Procedure Editor)Table Name: (set in the "New PL/SQL Object" dialog)******************************************************************************/BEGINSELECT GROUP_EMAIL_ID into GroupIdFROM AUTOBUILD_FAILUREWHERE PROJ_ID = nProjIdAND VIEW_ID = nViewId;DELETE FROM MEMBERS_GROUPWHERE GROUP_EMAIL_ID = GroupIdAND USER_ID IN (SELECT * FROM THE ( SELECT CAST( IN_LIST_NUMBER( sUserIdList ) AS RELMGR_NUMBER_TAB_t ) FROM dual ));END REMOVE_PROJECT_VIEW_OWNER;/CREATE OR REPLACE PROCEDURE New_Patch ( SSpatch_version IN PACKAGE_VERSIONS.pkg_version%TYPE,NNparent_id IN NUMBER,sPatchIdList IN VARCHAR2,NNuser_id IN NUMBER ) IS/* ---------------------------------------------------------------------------Version: 3.5--------------------------------------------------------------------------- */patchPv_id NUMBER;parPkg_id NUMBER;LastInstallOrder NUMBER;isPatchDlocked PACKAGE_VERSIONS.DLOCKED%TYPE;SSV_MM PACKAGE_VERSIONS.V_MM%TYPE;SSV_NMM PACKAGE_VERSIONS.V_NMM%TYPE;SSV_EXT PACKAGE_VERSIONS.V_EXT%TYPE;oPatchDepCollector RELMGR_NUMBER_TAB_t := RELMGR_NUMBER_TAB_t();CURSOR parent_cur ISSELECT pv.*, pkg.pkg_nameFROM package_versions pv,packages pkgWHERE pv.pv_id = NNparent_idAND pv.pkg_id = pkg.pkg_id;parent_rec parent_cur%ROWTYPE;CURSOR patch_cur ISSELECT pv.*, pg.pkg_nameFROM package_versions pv,packages pgWHERE pv.pkg_id = parPkg_idAND pv.pkg_version = SSpatch_versionAND pv.pkg_id = pg.pkg_id;patch_rec patch_cur%ROWTYPE;CURSOR releases_cur ISSELECT rc.pv_idFROM release_content rcWHERE rc.pv_id = patch_rec.pv_id;releases_rec releases_cur%ROWTYPE;BEGIN-- Get Last Install OrderSELECT Count(*) INTO LastInstallOrderFROM PACKAGE_PATCHES ppWHERE pp.PV_ID = NNparent_id;-- Get parent detailsOPEN parent_cur;FETCH parent_cur INTO parent_rec;parPkg_id := parent_rec.pkg_id;-- Find if patch exists in databaseOPEN patch_cur;FETCH patch_cur INTO patch_rec;-- Parent must be officialIF parent_rec.dlocked = 'Y' THENIF patch_cur%NOTFOUNDTHENisPatchDlocked := 'N';-- Create new patch version --SELECT SEQ_PV_ID.nextval INTO patchPv_id FROM DUAL;Split_version ( SSpatch_version, SSV_MM, SSV_NMM, SSV_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, src_path, pv_description, owner_id, is_patch, LAST_PV_ID )VALUES (patchPv_id,parPkg_id,SSpatch_version,isPatchDlocked,ORA_SYSDATE,NNuser_id,ORA_SYSDATETIME,NNuser_id,SSV_MM,SSV_NMM,SSV_EXT,parent_rec.src_path,'This is a patch to ' || parent_rec.pkg_name || ' ' || parent_rec.pkg_version,NNuser_id,'Y',patchPv_id);INSERT INTO package_patches ( pv_id, patch_id, INSTALL_ORDER )( SELECT NNparent_id AS pv_id,pv.pv_id AS patch_id,LastInstallOrder + 1 AS INSTALL_ORDERFROM package_versions pvWHERE pv.pv_id = patchPv_idAND pv.is_patch = 'Y' );/* LOG ACTION */Log_Action ( patchPv_id, 'new_version', NNuser_id,'Patch version created: '|| SSpatch_version );Log_Action ( NNparent_id, 'patch_add', NNuser_id,'New patch created and attached: '|| SSpatch_version );ELSEpatchPv_id := patch_rec.pv_id;isPatchDlocked := patch_rec.dlocked;-- Find if pv_id exists in release content (i.e. it cannot be a patch)OPEN releases_cur;FETCH releases_cur INTO releases_rec;IF releases_cur%NOTFOUNDTHEN-- This pv_id is trully a patch, hence add Y to column IS_PATCHUPDATE package_versions SETis_patch = 'Y'WHERE pv_id = patchPv_id;INSERT INTO package_patches ( pv_id, patch_id, INSTALL_ORDER )( SELECT NNparent_id AS pv_id,pv.pv_id AS patch_id,LastInstallOrder + 1 AS INSTALL_ORDERFROM package_versions pvWHERE pv.pv_id = patchPv_idAND pv.is_patch = 'Y' );END IF;CLOSE releases_cur;/* LOG ACTION */Log_Action ( NNparent_id, 'patch_add', NNuser_id,'Patch version was found and attached: '|| SSpatch_version );END IF;END IF;/* Create Patch Dependencies */oPatchDepCollector := IN_LIST_NUMBER ( sPatchIdList );-- Make sure patch is unofficial before altering its dependenciesIF (oPatchDepCollector.COUNT > 0) AND (isPatchDlocked = 'N') THEN-- Delete Existing DependenciesDELETEFROM PACKAGE_DEPENDENCIES depWHERE dep.PV_ID = patchPv_id;-- Insert new dependenciesINSERT INTO PACKAGE_DEPENDENCIES ( PV_ID, DPV_ID, PKG_ID, DPKG_ID, BUILD_TYPE )SELECT patchPv_id AS PV_ID,pv.PV_ID AS DPV_ID,parPkg_id AS PKG_ID,pv.PKG_ID AS DPKG_ID,'L' AS BUILD_TYPEFROM PACKAGE_VERSIONS pvWHERE pv.PV_ID IN ( SELECT * FROM TABLE ( CAST ( oPatchDepCollector AS RELMGR_NUMBER_TAB_t ) ) );END IF;CLOSE parent_cur;CLOSE patch_cur;END New_Patch;/CREATE OR REPLACE PROCEDURE testISproc_id NUMBER;/******************************************************************************NAME: DELETE_DO_NOT_RIPPLEPURPOSE: TO DELETE ENTRIES FROM THE DO_NOT_RIPPLE TABLE WHEN A PACKAGEIS RELEASEDREVISIONS:Ver Date Author Description--------- ---------- --------------- ------------------------------------1.0 21/04/2006 Rupesh Solanki 1. Created this procedure.NOTES:Automatically available Auto Replace Keywords:Object Name: DELETE_DO_NOT_RIPPLESysdate: 21/04/2006Date and Time: 21/04/2006, 3:21:55 PM, and 21/04/2006 3:21:55 PMUsername: (set in TOAD Options, Procedure Editor)Table Name: (set in the "New PL/SQL Object" dialog)******************************************************************************/CURSOR ripple_curISselect distinct pkg_health_tag, cmd_interface, pkg_owner, is_interface from deployment_manager.processes_config;ripple_rec ripple_cur%ROWTYPE;BEGINOPEN ripple_cur;FETCH ripple_curINTO ripple_rec;WHILE ripple_cur%FOUNDLOOPselect deployment_manager.seq_proc_id.nextval into proc_id from dual;insert into processes (PROC_ID, PROC_NAME, RUN_AS, PKG_OWNER, IS_INTERFACE)values(proc_id, ripple_rec.pkg_health_tag, ripple_rec.cmd_interface, ripple_rec.pkg_owner, ripple_rec.is_interface);FETCH ripple_curINTO ripple_rec;END LOOP;END test;/CREATE OR REPLACE PROCEDURE Seed_Package_Names_Versions_Te ( 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;VExt PACKAGE_VERSIONS.V_EXT%TYPE;CURSOR packages_cur ISSELECT pkg_id FROM PACKAGESWHERE pkg_name = SSpkg_name;packages_rec packages_cur%ROWTYPE;CURSOR package_versions_cur ISSELECT pv_id FROM PACKAGE_VERSIONSWHERE pkg_id = parPkg_idAND pkg_version = SSpkg_version;package_versions_rec package_versions_cur%ROWTYPE;CURSOR clone_package_versions_cur ISSELECT MAX(pv_id) AS pv_idFROM PACKAGE_VERSIONSWHERE pkg_id = parPkg_idAND 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%NOTFOUNDTHEN/* 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 );ELSEparPkg_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%NOTFOUNDTHENSELECT 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 OR VExt <> SSV_EXTTHEN/* CLONE details from similar version OR from nCloneFromPvId */IF ( NOT nCloneFromPvId IS NULL) THENcloneFrom_pv_id := nCloneFromPvId;ELSEcloneFrom_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 )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_TYPEFROM PACKAGE_VERSIONS pvWHERE 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 )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');END IF;CLOSE clone_package_versions_cur;retPV_ID := parPv_id;ELSEretPV_ID := package_versions_rec.pv_id;END IF;CLOSE package_versions_cur;END Seed_Package_Names_Versions_Te;/CREATE OR REPLACE PROCEDURE 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;BEGINIF (NOT bDoPatchIgnore) THEN/* Manual Ignore Warnings */oIgnoreIdCollector := IN_LIST_NUMBER ( sIgnoreIdList );/* Log Action */-- Get Release LocationSELECT proj.PROJ_NAME ||' / '|| rt.RTAG_NAME INTO ReleaseLocationFROM PROJECTS proj,RELEASE_TAGS rtWHERE rt.PROJ_ID = proj.PROJ_IDAND rt.RTAG_ID = nRtagId;-- Get Action Type Id for IGNORE_ONSELECT act.ACTTYPE_ID INTO ActionTypeIdFROM ACTION_TYPE actWHERE 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, ActionTypeIdFROM (SELECT TO_NUMBER(nRtagId) AS RTAG_ID, TO_NUMBER(nPvId), pv.PV_ID AS DPV_IDFROM PACKAGE_VERSIONS pvWHERE pv.PV_ID IN ( SELECT * FROM TABLE ( CAST ( oIgnoreIdCollector AS RELMGR_NUMBER_TAB_t ) ) )MINUSSELECT igw.RTAG_ID, igw.PV_ID, igw.DPV_IDFROM IGNORE_WARNINGS igwWHERE igw.RTAG_ID = nRtagIdAND igw.PV_ID = nPvId) qry,PACKAGE_VERSIONS pv,PACKAGES pkg,RELEASE_CONTENT rc,PACKAGE_VERSIONS rpvWHERE pv.PKG_ID = pkg.PKG_IDAND rc.RTAG_ID = nRtagIdAND rc.PV_ID = rpv.PV_IDAND rpv.PKG_ID = pv.PKG_IDAND 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_OFFSELECT act.ACTTYPE_ID INTO ActionTypeIdFROM ACTION_TYPE actWHERE 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, ActionTypeIdFROM (SELECT igw.RTAG_ID, igw.PV_ID, igw.DPV_IDFROM IGNORE_WARNINGS igwWHERE igw.RTAG_ID = nRtagIdAND igw.PV_ID = nPvIdMINUSSELECT TO_NUMBER(nRtagId) AS RTAG_ID, TO_NUMBER(nPvId), pv.PV_ID AS DPV_IDFROM PACKAGE_VERSIONS pvWHERE 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 rpvWHERE pv.PKG_ID = pkg.PKG_IDAND rc.RTAG_ID = nRtagIdAND rc.PV_ID = rpv.PV_IDAND rpv.PKG_ID = pv.PKG_IDAND NVL( rpv.V_EXT, 'LINK_A_NULL' ) = NVL( pv.V_EXT, 'LINK_A_NULL' )AND qry.DPV_ID = pv.PV_ID;-- Delete Current Ignore WarningsDELETEFROM IGNORE_WARNINGS igwWHERE igw.RTAG_ID = nRtagIdAND igw.PV_ID = nPvIdAND igw.IS_PATCH_IGNORE IS NULL;IF (oIgnoreIdCollector.COUNT > 0) THEN-- Insert Ignore WarningsINSERT INTO IGNORE_WARNINGS igw ( RTAG_ID, PV_ID, DPV_ID )SELECT nRtagId,nPvId,pv.PV_ID AS DPV_IDFROM PACKAGE_VERSIONS pvWHERE pv.PV_ID IN ( SELECT * FROM TABLE ( CAST ( oIgnoreIdCollector AS RELMGR_NUMBER_TAB_t ) ) );END IF;-- Touch Release if package is in Release AreaIF PK_ENVIRONMENT.GET_PACKAGE_AREA ( nPvId, nRtagId ) = 2 THENTouch_Release (nRtagId);END IF;ELSE/* Patch Ignore Warnings */-- Delete Current Patch Ignore WarningsDELETEFROM IGNORE_WARNINGS igwWHERE igw.RTAG_ID = nRtagIdAND igw.IS_PATCH_IGNORE = 'Y';-- Delete Manual Ignores that need to be Patch IgnoresDELETEFROM IGNORE_WARNINGS igwWHERE ( RTAG_ID, PV_ID, DPV_ID ) IN(SELECT DISTINCTnRtagId,err.PV_ID,err.ERR_DPV AS DPV_IDFROM(/* 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_mmFROM release_content rel, package_versions rpvWHERE rel.pv_id = rpv.pv_idAND rtag_id = nRtagId) frc,(/* DPV_IDs not fount in release*/SELECT dep.pv_id, dep.dpv_id AS err_dpvFROM package_dependencies depWHERE 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_IDFROM PACKAGE_PATCHES pp,PACKAGE_DEPENDENCIES dep,RELEASE_CONTENT rcWHERE rc.RTAG_ID = nRtagIdAND rc.PV_ID = pp.PV_IDAND dep.PV_ID = pp.PATCH_ID) pp,package_versions errpkg,package_versions errpvWHERE err.err_dpv = errpv.pv_idAND errpv.pkg_id = frc.pkg_id(+)AND NVL(errpv.v_ext, '|LINK_A_NULL|') = frc.v_ext(+)AND err.pv_id = errpkg.pv_idAND err.PV_ID = pp.PV_IDAND frc.PV_ID = pp.DPV_ID);/*----------------------------------------------------- Make sure that select statement above and below are same---------------------------------------------------*/-- Insert Patch IgnoresINSERT INTO IGNORE_WARNINGS igw ( RTAG_ID, PV_ID, DPV_ID, IS_PATCH_IGNORE )SELECT DISTINCTnRtagId,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_mmFROM release_content rel, package_versions rpvWHERE rel.pv_id = rpv.pv_idAND rtag_id = nRtagId) frc,(/* DPV_IDs not fount in release*/SELECT dep.pv_id, dep.dpv_id AS err_dpvFROM package_dependencies depWHERE 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_IDFROM PACKAGE_PATCHES pp,PACKAGE_DEPENDENCIES dep,RELEASE_CONTENT rcWHERE rc.RTAG_ID = nRtagIdAND rc.PV_ID = pp.PV_IDAND dep.PV_ID = pp.PATCH_ID) pp,package_versions errpkg,package_versions errpvWHERE err.err_dpv = errpv.pv_idAND errpv.pkg_id = frc.pkg_id(+)AND NVL(errpv.v_ext, '|LINK_A_NULL|') = frc.v_ext(+)AND err.pv_id = errpkg.pv_idAND err.PV_ID = pp.PV_IDAND frc.PV_ID = pp.DPV_ID;END IF;END Ignore_Dependency_Warnings;/CREATE OR REPLACE PROCEDURE Change_Package_State ( nPvId IN PACKAGE_VERSIONS.PV_ID%TYPE,nRtagId IN RELEASE_TAGS.RTAG_ID%TYPE ) IS/* ---------------------------------------------------------------------------Version: 4.0--------------------------------------------------------------------------- */BEGIN-- Unlock PackageUPDATE RELEASE_CONTENT rc SETrc.PKG_STATE = 0WHERE rc.PV_ID = nPvIdAND rc.RTAG_ID = nRtagId;/* LOG ACTION *//* Log_Action ( nPvId, 'unlock_package', nUserId );*/END Change_Package_State;/CREATE OR REPLACE PROCEDURE New_Unit_Test ( nPv_id IN NUMBER,nTestTypeId IN UNIT_TESTS.TEST_TYPES_FK%TYPE,sTest_summary IN UNIT_TESTS.TEST_SUMMARY%TYPE,sNumOfTest IN UNIT_TESTS.NUMOF_TEST%TYPE,sDpkg_path IN VARCHAR2,sResults IN VARCHAR2,sCompletion_date IN VARCHAR2,nCompleted_by IN NUMBER,enumTEST_TYPE_AUTOMATIC IN NUMBER,enumTEST_TYPE_NOT_DONE IN NUMBER,outFileName OUT VARCHAR2) IS/* ---------------------------------------------------------------------------Version: 3.2.0--------------------------------------------------------------------------- */FILENAME_PREFIX CONSTANT VARCHAR2(50) := 'RM_UNIT_TEST';newID NUMBER;ResultsURL UNIT_TESTS.RESULTS_URL%TYPE DEFAULT NULL;ResultsAttachment UNIT_TESTS.RESULTS_ATTACHMENT_NAME%TYPE DEFAULT NULL;BEGIN-- Get new ID --SELECT SEQ_UNIT_TESTS.NEXTVAL INTO newID FROM DUAL;IF nTestTypeId = enumTEST_TYPE_AUTOMATIC THENResultsURL := sResults;ELSEIF (sResults <> '') OR ( NOT sResults IS NULL ) THENoutFileName := FILENAME_PREFIX || newID ||'_'|| sResults;ResultsAttachment := sDpkg_path || '/' || outFileName;END IF;END IF;-- Remove NOT_DONE entry if existsDELETE FROM UNIT_TESTSWHERE pv_id = nPv_idAND test_types_fk = enumTEST_TYPE_NOT_DONE;--- Add Additional Note ---INSERT INTO UNIT_TESTS ( TEST_ID,PV_ID,TEST_TYPES_FK,TEST_SUMMARY,COMPLETION_DATE,COMPLETED_BY,RESULTS_URL,RESULTS_ATTACHMENT_NAME,NUMOF_TEST )VALUES (newID,nPv_id,nTestTypeId,sTest_summary,TO_DATE( sCompletion_date,'DD/MM/YYYY' ),nCompleted_by,ResultsURL,ResultsAttachment,sNumOfTest );END New_Unit_Test;/CREATE OR REPLACE PROCEDURE UnRipple_Package ( nPvId IN PACKAGE_VERSIONS.PV_ID%TYPE,nRtagId IN RELEASE_TAGS.RTAG_ID%TYPE,nUserId IN NUMBER ) IS/* ---------------------------------------------------------------------------Version: 4.0--------------------------------------------------------------------------- */BEGIN-- Unlock PackageINSERT INTO DO_NOT_RIPPLE (RTAG_ID, PV_ID)VALUES (nRtagId, nPvId);/* LOG ACTION */Log_Action ( nPvId, 'unripple_package', nUserId );END UnRipple_Package;/CREATE OR REPLACE PROCEDURE Ripple_Package ( nPvId IN PACKAGE_VERSIONS.PV_ID%TYPE,nRtagId IN RELEASE_TAGS.RTAG_ID%TYPE,nUserId IN NUMBER ) IS/* ---------------------------------------------------------------------------Version: 4.0--------------------------------------------------------------------------- */BEGIN-- Lock PackageDELETE FROM DO_NOT_RIPPLEWHERE PV_ID = nPvIdAND RTAG_ID = nRtagId;/* LOG ACTION */Log_Action ( nPvId, 'ripple_package', nUserId );END Ripple_Package;/CREATE OR REPLACE PROCEDURE Set_Package_Build_Env ( nPvId IN PACKAGE_BUILD_ENV.PV_ID%TYPE,sBuildEnvIdList IN VARCHAR2 ) IS/* ---------------------------------------------------------------------------Version: 3.0.0--------------------------------------------------------------------------- */BEGIN-- Delete Current Build Env settingsDELETE FROM PACKAGE_BUILD_ENVWHERE pv_id = nPvId;-- Reset flag to NUPDATE PACKAGE_VERSIONS SETIS_BUILD_ENV_REQUIRED = 'N'WHERE PV_ID = nPvId;-- Set new Build EnvIF NOT sBuildEnvIdList IS NULL THEN-- Insert into PACKAGE_BUILD_ENVINSERT INTO PACKAGE_BUILD_ENV ( PV_ID, BE_ID )SELECT nPvId AS PV_ID,be.BE_IDFROM BUILD_ENVIRONMENTS beWHERE be.BE_ID IN (SELECT *FROM THE ( SELECT CAST( IN_LIST_NUMBER( sBuildEnvIdList ) AS RELMGR_NUMBER_TAB_t ) FROM dual ));-- Set flag to NUPDATE PACKAGE_VERSIONS SETIS_BUILD_ENV_REQUIRED = 'Y'WHERE PV_ID = nPvId;END IF;END Set_Package_Build_Env;/CREATE OR REPLACE PROCEDURE Split_Version ( SSpkg_version IN PACKAGE_VERSIONS.pkg_version%TYPE,SSV_MM OUT PACKAGE_VERSIONS.V_MM%TYPE,SSV_NMM OUT PACKAGE_VERSIONS.V_NMM%TYPE,SSV_EXT OUT PACKAGE_VERSIONS.V_EXT%TYPE ) IS/* ---------------------------------------------------------------------------Version: 3.0.0--------------------------------------------------------------------------- */TYPE VERSION_COMPONENTS_TYPE IS TABLE OF VARCHAR2(50)INDEX BY BINARY_INTEGER;version_components VERSION_COMPONENTS_TYPE;lastDot NUMBER := 0;currDot NUMBER := 0;BEGINcurrDot := INSTR ( SSpkg_version, '.', -1 ); -- Find 1st dot from the rightIF ( currDot > 0 )THEN-- YES dot separator found --SSV_EXT := GET_V_EXT( SSpkg_version );IF NOT SSV_EXT IS NULLTHENlastDot := currDot;currDot := INSTR ( SSpkg_version, '.', -1, 2 ); -- Find 2nd dot from the rightIF ( currDot > 0 )THEN-- XXXX.M.ESSV_MM := SUBSTR ( SSpkg_version, currDot + 1, lastDot - currDot - 1);SSV_NMM := SUBSTR ( SSpkg_version, 0, currDot ) || SSV_EXT;ELSE-- XXXX.ESSV_MM := NULL;SSV_NMM := SSpkg_version;END IF;ELSE-- XXXX.MSSV_MM := SUBSTR ( SSpkg_version, currDot + 1, LENGTH(SSpkg_version) - currDot );SSV_NMM := SUBSTR ( SSpkg_version, 0, currDot );END IF;ELSE-- NO dot separator found ---- XXXXXXSSV_MM := NULL;SSV_NMM := SSpkg_version;SSV_EXT := NULL;END IF;END Split_Version;/CREATE OR REPLACE FUNCTION RELEASE_MODE ( nRtagId IN NUMBER ) RETURN CHAR IS/* ---------------------------------------------------------------------------Version: 3.0.0--------------------------------------------------------------------------- */returnValue CHAR(1);BEGIN/*--------------- Business Rules Here -------------------*//*-------------------------------------------------------*/-- Get release modeSELECT rt.OFFICIAL INTO returnValueFROM RELEASE_TAGS rtWHERE rt.RTAG_ID = nRtagId;RETURN returnValue;END RELEASE_MODE;/CREATE OR REPLACE PROCEDURE Unlock_Package ( nPvId IN PACKAGE_VERSIONS.PV_ID%TYPE,nUserId IN NUMBER ) IS/* ---------------------------------------------------------------------------Author: Rupesh SolankiVersion: 4.0--------------------------------------------------------------------------- */STATE CHAR;BEGINSELECT DLOCKED INTO STATEFROM PACKAGE_VERSIONSWHERE PV_ID = nPvId;IF STATE = 'A' THEN --It was made official for autobuilds-- Unlock PackageUPDATE PACKAGE_VERSIONS pv SETpv.DLOCKED = 'P'WHERE pv.PV_ID = nPvId;ELSE-- Unlock PackageUPDATE PACKAGE_VERSIONS pv SETpv.DLOCKED = 'N'WHERE pv.PV_ID = nPvId;END IF;/* LOG ACTION */Log_Action ( nPvId, 'unlock_package', nUserId );END Unlock_Package;/CREATE OR REPLACE PROCEDURE Touch_Release_Bulk ( nRtagIdList IN VARCHAR2 ) IS/* ---------------------------------------------------------------------------Version: 3.0--------------------------------------------------------------------------- */oRTagIdCollector RELMGR_NUMBER_TAB_t := RELMGR_NUMBER_TAB_t();BEGINoRTagIdCollector := IN_LIST_NUMBER ( nRtagIdList );-- Touch Release for RebuildUPDATE RELEASE_TAGS rt SETrt.REBUILD_ENV = 'Y',rt.REBUILD_STAMP = 0WHERE rt.RTAG_ID IN ( SELECT * FROM TABLE ( CAST ( oRTagIdCollector AS RELMGR_NUMBER_TAB_t ) ) );END Touch_Release_Bulk;/CREATE OR REPLACE PACKAGE PK_PLANNED IS/*------------------------------|| Last Modified: S.Vukovic|| Modified Date: 6/May/2005|| Spec Version: 1.0------------------------------*/TYPE typeCur IS REF CURSOR;/*================================================================================================*/PROCEDURE ADD_PACKAGE ( newPvId IN NUMBER, ViewId IN NUMBER, RtagId IN NUMBER, UserId IN NUMBER );PROCEDURE REMOVE_PACKAGE ( PvId IN NUMBER, RtagId IN NUMBER, UserId IN NUMBER );FUNCTION GET_PACKAGE_VIEW ( PvId IN NUMBER, RtagId IN NUMBER ) RETURN NUMBER;PROCEDURE GET_VIEW_CONTENT ( RtagId IN NUMBER, ViewId IN NUMBER, RecordSet OUT typeCur );PROCEDURE CHANGE_PACKAGE_VIEW ( PvId IN NUMBER, RtagId IN NUMBER, NewViewId IN NUMBER );/*================================================================================================*/END PK_PLANNED;/CREATE OR REPLACE PROCEDURE Pending_Package ( nPvId IN PACKAGE_VERSIONS.PV_ID%TYPE,nUserId IN NUMBER ) IS/* ---------------------------------------------------------------------------Version: 4.0--------------------------------------------------------------------------- */BEGIN-- Lock PackageUPDATE PACKAGE_VERSIONS pv SETpv.DLOCKED = 'A'WHERE pv.PV_ID = nPvId;/* LOG ACTION */Log_Action ( nPvId, 'lock_package', nUserId );END Pending_Package;/CREATE OR REPLACE PROCEDURE Seed_Package_Names_Versions2 ( 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 ISSELECT pkg_id FROM PACKAGESWHERE pkg_name = SSpkg_name;packages_rec packages_cur%ROWTYPE;CURSOR package_versions_cur ISSELECT pv_id FROM PACKAGE_VERSIONSWHERE pkg_id = parPkg_idAND pkg_version = SSpkg_version;package_versions_rec package_versions_cur%ROWTYPE;CURSOR clone_package_versions_cur ISSELECT MAX(pv_id) AS pv_idFROM PACKAGE_VERSIONSWHERE pkg_id = parPkg_idAND 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%NOTFOUNDTHEN/* 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 );ELSEparPkg_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%NOTFOUNDTHENSELECT 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 );/* CLONE details from similar version OR from nCloneFromPvId */IF ( NOT nCloneFromPvId IS NULL) THENcloneFrom_pv_id := nCloneFromPvId;ELSEcloneFrom_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 )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_REQUIREDFROM PACKAGE_VERSIONS pvWHERE pv.pv_id = cloneFrom_pv_id;Basic_Clone ( cloneFrom_pv_id, parPv_id, NULL, NNuser_id, parPkg_id, 0 );retPV_ID := parPv_id;ELSEretPV_ID := package_versions_rec.pv_id;END IF;CLOSE package_versions_cur;END Seed_Package_Names_Versions2;/CREATE OR REPLACE PROCEDURE delete_do_not_rippleIS/******************************************************************************NAME: DELETE_DO_NOT_RIPPLEPURPOSE: TO DELETE ENTRIES FROM THE DO_NOT_RIPPLE TABLE WHEN A PACKAGEIS RELEASEDREVISIONS:Ver Date Author Description--------- ---------- --------------- ------------------------------------1.0 21/04/2006 Rupesh Solanki 1. Created this procedure.NOTES:Automatically available Auto Replace Keywords:Object Name: DELETE_DO_NOT_RIPPLESysdate: 21/04/2006Date and Time: 21/04/2006, 3:21:55 PM, and 21/04/2006 3:21:55 PMUsername: (set in TOAD Options, Procedure Editor)Table Name: (set in the "New PL/SQL Object" dialog)******************************************************************************/CURSOR ripple_cur /*Create a record set to store the ripple data*/ISSELECT dnr.rtag_id, dnr.pv_id AS last_pv_id, pv.pv_id AS new_pv_idFROM release_content rc, do_not_ripple dnr, package_versions pvWHERE rc.pv_id = pv.pv_idAND rc.rtag_id = dnr.rtag_idAND pv.dlocked = 'Y'AND pv.last_pv_id = dnr.pv_idAND pv.pv_id != dnr.pv_idORDER BY last_pv_id;ripple_rec ripple_cur%ROWTYPE;BEGINOPEN ripple_cur;FETCH ripple_curINTO ripple_rec;WHILE ripple_cur%FOUNDLOOPDELETE FROM do_not_rippleWHERE rtag_id = ripple_rec.rtag_idAND pv_id = ripple_rec.last_pv_id;FETCH ripple_curINTO ripple_rec;END LOOP;END delete_do_not_ripple;/CREATE OR REPLACE FUNCTION IN_LIST_NUMBER ( sInList IN VARCHAR2 ) RETURN RELMGR_NUMBER_TAB_t IS/* ---------------------------------------------------------------------------Version: 3.0.0--------------------------------------------------------------------------- */sync_rtags RELMGR_NUMBER_TAB_t := RELMGR_NUMBER_TAB_t();pos NUMBER;in_list VARCHAR2(4000) := sInList || ',';BEGINIF NOT sInList IS NULLTHENLOOPEXIT WHEN in_list IS NULL;pos := INSTR ( in_list, ',' );sync_rtags.extend;sync_rtags(sync_rtags.count) := LTRIM ( RTRIM ( SUBSTR ( in_list, 1, pos-1 ) ) );in_list := SUBSTR ( in_list, pos+1 );END LOOP;END IF;RETURN sync_rtags;END IN_LIST_NUMBER;/CREATE OR REPLACE PROCEDURE delete_autobuild_failure_infoIS/******************************************************************************NAME: DELETE_AUTOBUILD_FAILURE_INFOPURPOSE:REVISIONS:Ver Date Author Description--------- ---------- --------------- ------------------------------------1.0 2/06/2006 Rupesh Solanki 1. Created this procedure.NOTES:Automatically available Auto Replace Keywords:Object Name: DELETE_AUTOBUILD_FAILURE_INFOSysdate: 2/06/2006Date and Time: 2/06/2006, 8:44:25 AM, and 2/06/2006 8:44:25 AMUsername: (set in TOAD Options, Procedure Editor)Table Name: (set in the "New PL/SQL Object" dialog)******************************************************************************/BEGINDELETE FROM autobuild_failureWHERE group_email_id NOT IN (SELECT group_email_idFROM members_group);END delete_autobuild_failure_info;/CREATE OR REPLACE PROCEDURE PAOLO_Build_Tree ( nRtag_id IN NUMBER ) ISsessionNumber NUMBER := 0;iteration NUMBER := 1;rowCnt NUMBER := 0;maxIterations NUMBER := 50;BEGININSERT INTO TEMP_TREE_BROWSE ( SESSION_NUM, LEVEL_NUM, PV_ID )SELECT sessionNumber, iteration, rc.PV_IDFROM RELEASE_CONTENT rcWHERE rc.RTAG_ID = nRtag_id;iteration := iteration + 1;LOOPINSERT INTO TEMP_TREE_BROWSE ( SESSION_NUM, LEVEL_NUM, PV_ID )SELECT sessionNumber,iteration,dep.DPV_IDFROM TEMP_TREE_BROWSE ttb,PACKAGE_DEPENDENCIES depWHERE dep.PV_ID = ttb.PV_IDAND ttb.LEVEL_NUM = iteration - 1MINUSSELECT sessionNumber, iteration, ttb.PV_IDFROM TEMP_TREE_BROWSE ttb;rowCnt := SQL%ROWCOUNT;IF rowCnt > 0 THENiteration := iteration + 1;END IF;EXIT WHEN (rowCnt < 1) OR (iteration > maxIterations);END LOOP;END PAOLO_Build_Tree;/CREATE OR REPLACE PROCEDURE Lock_Package ( nPvId IN PACKAGE_VERSIONS.PV_ID%TYPE,nUserId IN NUMBER ) IS/* ---------------------------------------------------------------------------Version: 4.0--------------------------------------------------------------------------- */BEGIN-- Lock PackageUPDATE PACKAGE_VERSIONS pv SETpv.DLOCKED = 'Y'WHERE pv.PV_ID = nPvId;/* LOG ACTION */Log_Action ( nPvId, 'lock_package', nUserId );END Lock_Package;/CREATE OR REPLACE PROCEDURE Log_Action_Bulk ( nPvIdList IN VARCHAR2,enumActionTypeName IN ACTION_TYPE.NAME%TYPE,nUserId IN ACTION_LOG.USER_ID%TYPE,sAdditionalComments IN ACTION_LOG.DESCRIPTION%TYPE DEFAULT NULL ) IS/* ---------------------------------------------------------------------------Version: 3.0--------------------------------------------------------------------------- */ActionTypeId NUMBER;oPvIdCollector RELMGR_NUMBER_TAB_t := RELMGR_NUMBER_TAB_t();BEGINoPvIdCollector := IN_LIST_NUMBER ( nPvIdList );-- Get Action Type FKSELECT act.ACTTYPE_ID INTO ActionTypeIdFROM ACTION_TYPE actWHERE act.NAME = enumActionTypeName;-- Log ActionINSERT INTO ACTION_LOG ( USER_ID, ACTION_DATETIME, PV_ID, DESCRIPTION, ACTTYPE_ID )SELECT nUserId,ORA_SYSDATETIME,pv.PV_ID,sAdditionalComments,ActionTypeIdFROM PACKAGE_VERSIONS pvWHERE pv.PV_ID IN ( SELECT * FROM TABLE ( CAST ( oPvIdCollector AS RELMGR_NUMBER_TAB_t ) ) );END Log_Action_Bulk;/CREATE OR REPLACE PROCEDURE Rename_Package_Version ( NNpv_id IN NUMBER,SSpkg_version IN PACKAGE_VERSIONS.pkg_version%TYPE,cBuildType IN CHAR,NNuser_id IN NUMBER,errMessage OUT VARCHAR2 ) IS/* ---------------------------------------------------------------------------Version: 3.2--------------------------------------------------------------------------- */sPackageVersion VARCHAR2(4000);sLabel VARCHAR2(4000) := NULL;OldPkgVersion PACKAGE_VERSIONS.PKG_VERSION%TYPE;SSV_MM PACKAGE_VERSIONS.V_MM%TYPE;SSV_NMM PACKAGE_VERSIONS.V_NMM%TYPE;SSV_EXT PACKAGE_VERSIONS.V_EXT%TYPE;CURSOR package_versions_cur ISSELECT pv_idFROM package_versionsWHERE pkg_id IN ( SELECT pkg_id FROM package_versions WHERE pv_id = NNpv_id )AND pkg_version = SSpkg_version;package_versions_rec package_versions_cur%ROWTYPE;BEGIN/* ---------------------------------------------------- *//* Find if package_version exists *//* ---------------------------------------------------- */errMessage := NULL;-- Get previous versionSELECT pv.PKG_VERSION INTO OldPkgVersionFROM PACKAGE_VERSIONS pvWHERE pv.PV_ID = NNpv_id;sPackageVersion := SSpkg_version;IF OldPkgVersion != sPackageVersion THENOPEN package_versions_cur;FETCH package_versions_cur INTO package_versions_rec;IF package_versions_cur%NOTFOUNDTHEN-- Split current version in partsSplit_Version ( SSpkg_version, SSV_MM, SSV_NMM, SSV_EXT );-- Automated built configIF (cBuildType = 'A') THENsPackageVersion := '('|| NNpv_id ||')'|| SSV_EXT; -- Make sure that version is still uniqueEND IF;-- Packge version not found, hence rename it.UPDATE package_versionsSET pkg_version = sPackageVersion,v_mm = SSV_MM,v_nmm = SSV_NMM,v_ext = SSV_EXT,modified_stamp = ORA_SYSDATETIME,modifier_id = NNuser_id,build_type = cBuildType,pkg_label = NULLWHERE pv_id = NNpv_id;IF (cBuildType = 'A') THENsLabel := GET_AUTOMATED_LABEL( NNpv_id );UPDATE PACKAGE_VERSIONS pv SETpv.PKG_LABEL = sLabelWHERE pv_id = NNpv_id;END IF;/* LOG ACTION */Log_Action ( NNpv_id, 'rename', NNuser_id, 'From '|| OldPkgVersion ||' to '|| SSpkg_version );ELSE-- Package version exists. Cannot proceed.errMessage := 'enum_MSG_VERSION_EXISTS';END IF;CLOSE package_versions_cur;END IF;END Rename_Package_Version;/CREATE OR REPLACE FUNCTION IS_VERSION_EXTENSION (SSV_EXT PACKAGE_VERSIONS.V_EXT%TYPE) RETURN BOOLEANIS/* ---------------------------------------------------------------------------Version: 3.0.0--------------------------------------------------------------------------- */BEGINIF LENGTH ( TRIM (TRANSLATE (SSV_EXT, '.ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz',' ') ) ) > 0THENRETURN FALSE;ELSERETURN TRUE;END IF;END IS_VERSION_EXTENSION;/CREATE OR REPLACE PROCEDURE CLONED_PACKAGE_PROCESSESISproc_id NUMBER;/******************************************************************************NAME: DELETE_DO_NOT_RIPPLEPURPOSE: TO DELETE ENTRIES FROM THE DO_NOT_RIPPLE TABLE WHEN A PACKAGEIS RELEASEDREVISIONS:Ver Date Author Description--------- ---------- --------------- ------------------------------------1.0 21/04/2006 Rupesh Solanki 1. Created this procedure.NOTES:Automatically available Auto Replace Keywords:Object Name: DELETE_DO_NOT_RIPPLESysdate: 21/04/2006Date and Time: 21/04/2006, 3:21:55 PM, and 21/04/2006 3:21:55 PMUsername: (set in TOAD Options, Procedure Editor)Table Name: (set in the "New PL/SQL Object" dialog)******************************************************************************/CURSOR ripple_curISselect distinct proc_id, prod_id from deployment_manager.processes_config;ripple_rec ripple_cur%ROWTYPE;BEGINOPEN ripple_cur;FETCH ripple_curINTO ripple_rec;WHILE ripple_cur%FOUNDLOOPinsert into package_processes (PROC_ID, PV_ID)values( ripple_rec.proc_id, ripple_rec.prod_id);FETCH ripple_curINTO ripple_rec;END LOOP;END CLONED_PACKAGE_PROCESSES;/CREATE OR REPLACE PROCEDURE DT_SETPROPERTYBYID ( PARAM_ID IN NUMBER, PARAM_PROPERTY IN VARCHAR2, PARAM_VALUE IN VARCHAR2, PARAM_LVALUE IN LONG RAW ) AS BEGIN DECLARE X NUMBER(40);BEGIN SELECT COUNT(*) INTO X FROM MICROSOFTDTPROPERTIES WHERE OBJECTID=PARAM_ID AND PROPERTY=PARAM_PROPERTY;IF X = 0 THEN INSERT INTO MICROSOFTDTPROPERTIES (ID, PROPERTY, OBJECTID, VALUE, LVALUE, VERSION) VALUES (MICROSOFTSEQDTPROPERTIES.NEXTVAL, PARAM_PROPERTY, PARAM_ID, PARAM_VALUE, PARAM_LVALUE, 0);ELSE UPDATE MICROSOFTDTPROPERTIES SET VALUE=PARAM_VALUE, LVALUE=PARAM_LVALUE, VERSION=VERSION+1 WHERE OBJECTID=PARAM_ID AND PROPERTY=PARAM_PROPERTY;END IF;END;END DT_SETPROPERTYBYID;/CREATE OR REPLACE PROCEDURE 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 ISSELECT pkg_id FROM PACKAGESWHERE pkg_name = SSpkg_name;packages_rec packages_cur%ROWTYPE;CURSOR package_versions_cur ISSELECT pv_id FROM PACKAGE_VERSIONSWHERE pkg_id = parPkg_idAND pkg_version = SSpkg_version;package_versions_rec package_versions_cur%ROWTYPE;CURSOR clone_package_versions_cur ISSELECT MAX(pv_id) AS pv_idFROM PACKAGE_VERSIONSWHERE pkg_id = parPkg_idAND 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%NOTFOUNDTHEN/* 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 );ELSEparPkg_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%NOTFOUNDTHENSELECT 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 NULLTHEN/* CLONE details from similar version OR from nCloneFromPvId */IF ( NOT nCloneFromPvId IS NULL) THENcloneFrom_pv_id := nCloneFromPvId;ELSEcloneFrom_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 )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_REQUIREDFROM PACKAGE_VERSIONS pvWHERE 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 )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');END IF;CLOSE clone_package_versions_cur;retPV_ID := parPv_id;ELSEretPV_ID := package_versions_rec.pv_id;END IF;CLOSE package_versions_cur;END Seed_Package_Names_Versions;/CREATE OR REPLACE PROCEDURE Remove_Runtime ( nPvId IN PACKAGE_VERSIONS.PV_ID%TYPE,nRuntimeId IN RUNTIME_DEPENDENCIES.RTD_ID%TYPE,nUserId IN NUMBER ) IS/* ---------------------------------------------------------------------------Version: 3.0--------------------------------------------------------------------------- */RuntimeDependency VARCHAR2(4000);BEGIN-- Get Runtime dependencySELECT pkg.PKG_NAME ||' '|| pv.PKG_VERSION INTO RuntimeDependencyFROM RUNTIME_DEPENDENCIES rtd,PACKAGES pkg,PACKAGE_VERSIONS pvWHERE rtd.PV_ID = nPvIdAND pv.PKG_ID = pkg.PKG_IDAND rtd.RTD_ID = pv.PV_IDAND rtd.RTD_ID = nRuntimeId;-- Delete DocumentDELETEFROM RUNTIME_DEPENDENCIES rtdWHERE rtd.PV_ID = nPvIdAND rtd.RTD_ID = nRuntimeId;/* LOG ACTION */Log_Action ( nPvId, 'runtime_remove', nUserId,'Runtime package: '|| RuntimeDependency );END Remove_Runtime;/CREATE OR REPLACE PROCEDURE 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) ISext VARCHAR2(50);BEGIN--Extract the package version extensionSELECT V_EXT into extFROM PACKAGE_VERSIONSWHERE PV_ID = nPvId;-- Deprecate PackageINSERT INTO DEPRECATED_PACKAGES (RTAG_ID, PKG_ID, COMMENTS, V_EXT)VALUES (nRtagId, nPkgId, sComments, ext);IF ext IS NOT NULL THENUPDATE RELEASE_CONTENTSET PKG_ID = nPkgId, DEPRECATED_STATE = 6WHERE RTAG_ID = nRtagIdAND PV_ID IN (SELECT PV.PV_IDFROM PACKAGE_VERSIONS PV, PACKAGES PKGWHERE PKG.PKG_ID = PV.PKG_IDAND PV.V_EXT = extAND PKG.PKG_ID = nPkgId);ELSEUPDATE RELEASE_CONTENTSET PKG_ID = nPkgId, DEPRECATED_STATE = 6WHERE RTAG_ID = nRtagIdAND PV_ID IN (SELECT PV.PV_IDFROM PACKAGE_VERSIONS PV, PACKAGES PKGWHERE PKG.PKG_ID = PV.PKG_IDAND PV.V_EXT IS NULLAND PKG.PKG_ID = nPkgId);END IF;UPDATE RELEASE_CONTENTSET PKG_ID = nPkgId, DEPRECATED_STATE = 7WHERE RTAG_ID = nRtagIdAND PV_ID IN ( SELECT DISTINCTqry.PV_IDFROM (SELECT dep.*,LEVEL AS LEVEL_NUMFROM PACKAGE_DEPENDENCIES depSTART WITH dep.DPV_ID IN ( nPvId )CONNECT BY PRIOR dep.PV_ID = dep.DPV_ID) qry,PACKAGES pkg,PACKAGE_VERSIONS pv,RELEASE_CONTENT rcWHERE qry.PV_ID = pv.PV_ID AND pv.PKG_ID = pkg.PKG_IDAND rc.RTAG_ID = nRtagId AND rc.PV_ID = qry.PV_IDAND rc.DEPRECATED_STATE IS NULL);/* LOG ACTION *//*Log_Action ( nPkgId, 'deprecated_package', nUserId );*/END Deprecate_Package;/CREATE OR REPLACE PROCEDURE add_autobuild_failure_info (nprojid IN NUMBER,nviewid IN NUMBER,suserlist IN VARCHAR2)ISgroupid NUMBER;/******************************************************************************NAME: ADD_AUTOBUILD_FAILUREPURPOSE:REVISIONS:Ver Date Author Description--------- ---------- --------------- ------------------------------------1.0 11/04/2006 Rupesh Solanki Created this procedure.NOTES:Automatically available Auto Replace Keywords:Object Name: ADD_AUTOBUILD_FAILURESysdate: 11/04/2006Date and Time: 11/04/2006, 9:28:28 AM, and 11/04/2006 9:28:28 AMUsername: (set in TOAD Options, Procedure Editor)Table Name: (set in the "New PL/SQL Object" dialog)******************************************************************************/BEGINBEGINSELECT group_email_idINTO groupidFROM autobuild_failureWHERE proj_id = nprojid AND view_id = nviewid;EXCEPTIONWHEN NO_DATA_FOUNDTHEN-- Create Next Sequence Id ---SELECT seq_group_email_id.NEXTVALINTO groupidFROM DUAL;INSERT INTO autobuild_failure(group_email_id, proj_id, view_id)VALUES (groupid, nprojid, nviewid);END;add_view_members (groupid, suserlist);END add_autobuild_failure_info;/CREATE OR REPLACE PROCEDURE Update_Package_States ( NNrtag_id IN NUMBER,NNsession_num IN NUMBER ) IS/* ---------------------------------------------------------------------------Version: 3.0.0--------------------------------------------------------------------------- *//* ============= STATE RULES =================== *//*MNR MRR MN MR | pkg_state------------------------------------0 0 0 0 | OK0 0 0 1 | MAJOR0 0 1 0 | MINOR0 0 1 1 | MAJOR------------------------------------0 1 0 0 | MAJOR_READY0 1 0 1 | MAJOR0 1 1 0 | MAJOR0 1 1 1 | MAJOR------------------------------------1 0 0 0 | MINOR_READY1 0 0 1 | MAJOR1 0 1 0 | MINOR1 0 1 1 | MAJOR------------------------------------1 1 0 0 | MAJOR_READY1 1 0 1 | MAJOR1 1 1 0 | MAJOR1 1 1 1 | MAJOR------------------------------------*/BEGIN/*----------------------------------------------|| MINOR READY*/----------------------------------------------UPDATE release_contentSET pkg_state = 4WHERE rtag_id = NNrtag_idAND pv_id IN(SELECT DISTINCT pv_idFROM temp_env_statesWHERE session_num = NNsession_numAND level_num = 1AND tes_state = 2);/*----------------------------------------------|| MAJOR READY*/----------------------------------------------UPDATE release_contentSET pkg_state = 3WHERE rtag_id = NNrtag_idAND pv_id IN(SELECT DISTINCT pv_idFROM temp_env_statesWHERE session_num = NNsession_numAND level_num = 1AND tes_state IN (0,1));/*----------------------------------------------|| MINOR*/----------------------------------------------UPDATE release_contentSET pkg_state = 2WHERE rtag_id = NNrtag_idAND pv_id IN(SELECT DISTINCT pv_idFROM temp_env_statesWHERE session_num = NNsession_numAND level_num >= 2AND tes_state = 2MINUSSELECT pv_idFROM release_contentWHERE rtag_id = NNrtag_idAND pkg_state = 3);/*----------------------------------------------|| MAJOR*/----------------------------------------------UPDATE release_contentSET pkg_state = 1WHERE rtag_id = NNrtag_idAND pv_id IN(SELECT DISTINCT pv_idFROM temp_env_statesWHERE session_num = NNsession_numAND level_num >= 2AND tes_state IN (0,1)/* MAJOR EXCEPTION - When package has Major Ready and Minor */UNIONSELECT DISTINCT tes.pv_idFROM temp_env_states tes,release_content rcWHERE tes.session_num = NNsession_numAND tes.level_num >= 2AND tes.tes_state = 2AND rtag_id = NNrtag_idAND rc.pv_id = tes.pv_idAND rc.pkg_state = 3);/*----------------------------------------------|| OK*/----------------------------------------------UPDATE release_contentSET pkg_state = 0WHERE rtag_id = NNrtag_idAND pv_id IN(SELECT rc.pv_idFROM release_content rcWHERE rc.rtag_id = NNrtag_idAND NOT rc.pv_id IN(SELECT DISTINCT pv_idFROM temp_env_states WHERE session_num = NNsession_num));END Update_Package_States;/CREATE OR REPLACE function PERL_DBD_TESTFUNC(a in integer, b in integer) return integer is c integer; begin if b is null then c := 0; else c := b; end if; return a * c + 1; end;/CREATE OR REPLACE TYPE "RELMGR_VARCHAR2_TAB_T" as TABLE of VARCHAR2(4000)/CREATE OR REPLACE PROCEDURE CHANGE_RELEASE_MODE ( nModeCode IN NUMBER, nRtagId IN NUMBER, UserId IN NUMBER ) IS/* ---------------------------------------------------------------------------Version: 3.0.0--------------------------------------------------------------------------- */cMode CHAR(1) := NULL;nProjId NUMBER;BEGIN/*Author: Rupesh SolankiModified: 24th October 2006Reason: Added the archive mode state into Release Manager|| 1 - Open Mode|| 2 - Restrictive Mode|| 3 - Closed Mode|| 4 - CCB Mode|| 5 - Archive Mode*/-- Get project IdSELECT rt.PROJ_ID INTO nProjIdFROM RELEASE_TAGS rtWHERE rt.RTAG_ID = nRtagId;IF nModeCode = 1 THEN-- Open ModecMode := 'N';/* LOG ACTION */Log_Project_Action ( nProjId, 'set_to_open_mode', UserId, 'Release is set to Open Mode', nRtagId );ELSIF nModeCode = 2 THEN-- Restrictive ModecMode := 'R';/* LOG ACTION */Log_Project_Action ( nProjId, 'set_to_restrictive_mode', UserId, 'Release is set to Restrictive Mode', nRtagId );ELSIF nModeCode = 3 THEN-- Closed ModecMode := 'Y';/* LOG ACTION */Log_Project_Action ( nProjId, 'set_to_closed_mode', UserId, 'Release is set to Closed Mode', nRtagId );ELSIF nModeCode = 4 THEN-- CCB ModecMode := 'C';/* LOG ACTION */Log_Project_Action ( nProjId, 'set_to_ccb_mode', UserId, 'Release is set to CCB Mode', nRtagId );ELSIF nModeCode = 5 THEN-- Archive ModecMode := 'A';/* LOG ACTION */Log_Project_Action ( nProjId, 'set_to_archive_mode', UserId, 'Release is set to Archive Mode', nRtagId );END IF;-- Now update tableIF NOT cMode IS NULL THENUPDATE RELEASE_TAGS rt SETrt.OFFICIAL = cModeWHERE rt.RTAG_ID = nRtagId;END IF;END CHANGE_RELEASE_MODE;/CREATE OR REPLACE PROCEDURE Remove_Document ( nPvId IN PACKAGE_VERSIONS.PV_ID%TYPE,nDocId IN PACKAGE_DOCUMENTS.DOC_ID%TYPE,nUserId IN NUMBER ) IS/* ---------------------------------------------------------------------------Version: 3.0--------------------------------------------------------------------------- */DocNumber PACKAGE_DOCUMENTS.DOC_NUM%TYPE;BEGIN-- Get Doc NumSELECT pd.DOC_NUM INTO DocNumberFROM PACKAGE_DOCUMENTS pdWHERE pd.PV_ID = nPvIdAND pd.DOC_ID = nDocId;-- Delete DocumentDELETEFROM PACKAGE_DOCUMENTS pdWHERE pd.PV_ID = nPvIdAND pd.DOC_ID = nDocId;/* LOG ACTION */Log_Action ( nPvId, 'document_remove', nUserId,'Document number: '|| DocNumber );END Remove_Document;/CREATE OR REPLACE PROCEDURE Update_Unit_Test ( nTest_id IN NUMBER,nTestTypeId IN UNIT_TESTS.TEST_TYPES_FK%TYPE,nPv_id IN NUMBER,nUserId IN NUMBER,sTest_summary IN UNIT_TESTS.TEST_SUMMARY%TYPE,sNumof_test IN UNIT_TESTS.NUMOF_TEST%TYPE,sCompletion_date IN VARCHAR2,sDpkg_path IN VARCHAR2,enumTEST_TYPE_AUTOMATIC IN NUMBER,sResults IN VARCHAR2,outFileName OUT VARCHAR2) IS/* ---------------------------------------------------------------------------Version: 3.1.0--------------------------------------------------------------------------- */FILENAME_PREFIX CONSTANT VARCHAR2(50) := 'RM_UNIT_TEST';ResultsURL UNIT_TESTS.RESULTS_URL%TYPE DEFAULT NULL;ResultsAttachment UNIT_TESTS.RESULTS_ATTACHMENT_NAME%TYPE DEFAULT NULL;BEGINIF nTestTypeId = enumTEST_TYPE_AUTOMATIC THENResultsURL := sResults;ELSEIF (sResults <> '') OR ( NOT sResults IS NULL ) THENoutFileName := FILENAME_PREFIX || nTest_id ||'_'|| sResults;ResultsAttachment := sDpkg_path || '/' || outFileName;END IF;END IF;--- Update Unit Test ---UPDATE UNIT_TESTS SETTEST_SUMMARY = sTest_summary,NUMOF_TEST = sNumof_test,COMPLETION_DATE = TO_DATE( sCompletion_date,'DD/MM/YYYY' ),COMPLETED_BY = nUserId,RESULTS_URL = ResultsURL,RESULTS_ATTACHMENT_NAME = ResultsAttachmentWHERE TEST_ID = nTest_idAND PV_ID = nPv_id;END Update_Unit_Test;/CREATE OR REPLACE PROCEDURE Update_Unit_Test_Test ( nTest_id IN NUMBER,nTestTypeId IN UNIT_TESTS.TEST_TYPES_FK%TYPE,nPv_id IN NUMBER,nUserId IN NUMBER,sTest_summary IN UNIT_TESTS.TEST_SUMMARY%TYPE,sNumof_test IN UNIT_TESTS.NUMOF_TEST%TYPE,sCompletion_date IN VARCHAR2,sDpkg_path IN VARCHAR2,enumTEST_TYPE_AUTOMATIC IN NUMBER,sResults IN VARCHAR2,outFileName OUT VARCHAR2) IS/* ---------------------------------------------------------------------------Version: 3.1.0--------------------------------------------------------------------------- */FILENAME_PREFIX CONSTANT VARCHAR2(50) := 'RM_UNIT_TEST';ResultsURL UNIT_TESTS.RESULTS_URL%TYPE DEFAULT NULL;ResultsAttachment UNIT_TESTS.RESULTS_ATTACHMENT_NAME%TYPE DEFAULT NULL;BEGINIF nTestTypeId = enumTEST_TYPE_AUTOMATIC THENResultsURL := sResults;ELSEIF (sResults <> '') OR ( NOT sResults IS NULL ) THENoutFileName := FILENAME_PREFIX || nTest_id ||'_'|| sResults;ResultsAttachment := sDpkg_path || '/' || outFileName;END IF;END IF;/* IF (sResults <> '') OR ( NOT sResults IS NULL ) THENoutFileName := FILENAME_PREFIX || newID ||'_'|| sResults;sResults_url:= 'doc' || '/' ||;END IF;*/--- Update Unit Test ---UPDATE UNIT_TESTS SETTEST_SUMMARY = sTest_summary,NUMOF_TEST = sNumof_test,COMPLETION_DATE = TO_DATE( sCompletion_date,'DD/MM/YYYY' ),COMPLETED_BY = nUserId,RESULTS_URL = ResultsURL,RESULTS_ATTACHMENT_NAME = ResultsAttachmentWHERE TEST_ID = nTest_idAND PV_ID = nPv_id;END Update_Unit_Test_Test;/CREATE OR REPLACE PROCEDURE Update_Additional_Note ( pnNote_id IN NUMBER,pnPv_id IN NUMBER,psNote_title IN ADDITIONAL_NOTES.NOTE_TITLE%TYPE,psNote_body IN ADDITIONAL_NOTES.NOTE_BODY%TYPE,pnUser_id IN NUMBER,outErrCode OUT NUMBER) IS/* ---------------------------------------------------------------------------Version: 3.0.1--------------------------------------------------------------------------- */newID NUMBER;/* Disallow duplicate Note Titles */CURSOR an_duplicate_cur ISSELECT COUNT(*) AS cnt_noteFROM ADDITIONAL_NOTESWHERE pv_id = pnPv_idAND note_id != pnNote_idAND note_title = psNote_title;an_duplicate_rec an_duplicate_cur%ROWTYPE;BEGINoutErrCode := -1; -- Set default return error code to ERROR stateOPEN an_duplicate_cur;FETCH an_duplicate_cur INTO an_duplicate_rec;IF an_duplicate_rec.cnt_note < 1THEN--- Update Additional Note ---UPDATE ADDITIONAL_NOTES SETnote_title = psNote_title,note_body = psNote_body,mod_date = Ora_Sysdate,mod_user = pnUser_idWHERE note_id = pnNote_idAND pv_id = pnPv_id;outErrCode := 0; -- Set return to SUCCESSEND IF;CLOSE an_duplicate_cur;END Update_Additional_Note;/CREATE OR REPLACE PROCEDURE DT_DROPUSEROBJECTBYID ( PARAM_ID IN NUMBER ) AS BEGIN DELETE FROM MICROSOFTDTPROPERTIES WHERE OBJECTID = PARAM_ID; END DT_DROPUSEROBJECTBYID;/CREATE OR REPLACE FUNCTION ORA_SYSDATERETURN DATEIS/* ---------------------------------------------------------------------------Version: 3.0.0--------------------------------------------------------------------------- */BEGINRETURN TO_DATE( TO_CHAR( SYSDATE,'DD-MON-YYYY' ),'DD-MON-YYYY' );END ORA_SYSDATE;/CREATE OR REPLACE PROCEDURE 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) ISext VARCHAR2(50);PvIdList VARCHAR2(32767);nIdCollector RELMGR_NUMBER_TAB_t := RELMGR_NUMBER_TAB_t();PvId NUMBER;sComments VARCHAR2(32767);BEGIN--Extract the package extensionSELECT V_EXT into extFROM PACKAGE_VERSIONSWHERE 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 PackageDELETE FROM DEPRECATED_PACKAGESWHERE RTAG_ID = nRtagIdAND PKG_ID = nPkgIdAND V_EXT = ext;UPDATE RELEASE_CONTENTSET PKG_ID = NULL, DEPRECATED_STATE = NULLWHERE RTAG_ID = nRtagIdAND PV_ID IN (SELECT PV.PV_IDFROM PACKAGE_VERSIONS PV, PACKAGES PKGWHERE PKG.PKG_ID = PV.PKG_IDAND PKG.PKG_ID = nPkgIdAND PV.V_EXT = extUNIONSELECT DISTINCTqry.PV_IDFROM (SELECT dep.*,LEVEL AS LEVEL_NUMFROM PACKAGE_DEPENDENCIES depSTART 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 rcWHERE qry.PV_ID = pv.PV_ID AND pv.PKG_ID = pkg.PKG_IDAND rc.RTAG_ID = nRtagId AND rc.PV_ID = qry.PV_ID);ELSE-- Undeprecate PackageDELETE FROM DEPRECATED_PACKAGESWHERE RTAG_ID = nRtagIdAND PKG_ID = nPkgIdAND V_EXT IS NULL;UPDATE RELEASE_CONTENTSET PKG_ID = NULL, DEPRECATED_STATE = NULLWHERE RTAG_ID = nRtagIdAND PV_ID IN (SELECT PV.PV_IDFROM PACKAGE_VERSIONS PV, PACKAGES PKGWHERE PKG.PKG_ID = PV.PKG_IDAND PKG.PKG_ID = nPkgIdAND PV.V_EXT IS NULLUNIONSELECT DISTINCTqry.PV_IDFROM (SELECT dep.*,LEVEL AS LEVEL_NUMFROM PACKAGE_DEPENDENCIES depSTART 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 rcWHERE qry.PV_ID = pv.PV_ID AND pv.PKG_ID = pkg.PKG_IDAND 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;/CREATE OR REPLACE PROCEDURE Update_Unit_Test_Acceptance ( pnTest_id IN NUMBER,pnPv_id IN NUMBER,sAccepted IN UNIT_TESTS.TEST_ACCEPTED%TYPE,sAcceptanceDate IN VARCHAR2,sAcceptedBy IN NUMBER,sReviewComments IN UNIT_TESTS.REVIEW_COMMENTS%TYPE,cUpdateAcceptedStateOnly IN CHAR) IS/* ---------------------------------------------------------------------------Version: 3.0.0--------------------------------------------------------------------------- */BEGINIF cUpdateAcceptedStateOnly = 'Y'THEN--- Update Accepted State Only ---IF ( sAccepted IS NULL )THEN-- Clear alleptanceUPDATE UNIT_TESTS SETTEST_ACCEPTED = NULL,ACCEPTANCE_DATE = NULL,ACCEPTED_BY = NULL,REVIEW_COMMENTS = NULLWHERE TEST_ID = pnTest_idAND PV_ID = pnPv_id;ELSEUPDATE UNIT_TESTS SETTEST_ACCEPTED = sAccepted,ACCEPTANCE_DATE = TO_DATE( sAcceptanceDate,'DD/MM/YYYY' ),ACCEPTED_BY = sAcceptedByWHERE TEST_ID = pnTest_idAND PV_ID = pnPv_id;END IF;ELSE--- Update Unit Test Acceptance ---UPDATE UNIT_TESTS SETTEST_ACCEPTED = sAccepted,REVIEW_COMMENTS = sReviewComments,ACCEPTANCE_DATE = TO_DATE( sAcceptanceDate,'DD/MM/YYYY' ),ACCEPTED_BY = sAcceptedByWHERE TEST_ID = pnTest_idAND PV_ID = pnPv_id;END IF;END Update_Unit_Test_Acceptance;/CREATE OR REPLACE PROCEDURE New_Version ( NNorig_pv_id IN NUMBER,SSpkg_version IN PACKAGE_VERSIONS.pkg_version%TYPE DEFAULT NULL,NNsetto_pv_id IN NUMBER DEFAULT NULL,NNrtag_id IN NUMBER,NNuser_id IN NUMBER,enumISSUES_STATE_IMPORTED IN NUMBER,retPv_id OUT NUMBER ) ISTO BE REMOVED !!!/* ---------------------------------------------------------------------------Version: 3.3--------------------------------------------------------------------------- */ReleaseLocation VARCHAR2(4000);origPkg_id PACKAGE_VERSIONS.pkg_id%TYPE;origDlocked PACKAGE_VERSIONS.dlocked%TYPE;SSV_MM PACKAGE_VERSIONS.V_MM%TYPE;SSV_NMM PACKAGE_VERSIONS.V_NMM%TYPE;SSV_EXT PACKAGE_VERSIONS.V_EXT%TYPE;nIssuesTypes NUMBER;CURSOR package_versions_cur ISSELECT pv.pv_id, pv.is_patchFROM PACKAGE_VERSIONS pvWHERE pv.pkg_version = SSpkg_versionAND pv.pkg_id IN ( SELECT DISTINCT origpv.pkg_id FROM PACKAGE_VERSIONS origpv WHERE origpv.pv_id = NNorig_pv_id );package_versions_rec package_versions_cur%ROWTYPE;CURSOR clone_package_versions_cur ISSELECT DISTINCT pkg_id, dlocked FROM PACKAGE_VERSIONS WHERE pv_id = NNorig_pv_id;clone_package_versions_rec clone_package_versions_cur%ROWTYPE;BEGINIF NNsetto_pv_id IS NULLTHEN-- SetToPv_id is not supplied, hence proceed./* ---------------------------------------------------- *//* Find id package_version exists *//* ---------------------------------------------------- */OPEN package_versions_cur;FETCH package_versions_cur INTO package_versions_rec;IF package_versions_cur%NOTFOUNDTHENSELECT SEQ_PV_ID.NEXTVAL INTO retPv_id FROM DUAL;Split_Version ( SSpkg_version, SSV_MM, SSV_NMM, SSV_EXT );OPEN clone_package_versions_cur;FETCH clone_package_versions_cur INTO clone_package_versions_rec;origPkg_id := clone_package_versions_rec.pkg_id;origDlocked := clone_package_versions_rec.dlocked;CLOSE clone_package_versions_cur;-- 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, IS_DEPLOYABLE, IS_BUILD_ENV_REQUIRED )SELECT retPv_id AS pv_id,origPkg_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,NNorig_pv_id AS LAST_PV_ID,pv.owner_id,pv.IS_DEPLOYABLE,pv.IS_BUILD_ENV_REQUIREDFROM PACKAGE_VERSIONS pvWHERE pv.pv_id = NNorig_pv_id;-- Set Issues Type for cloning ---IF origDlocked = 'Y'THENnIssuesTypes := enumISSUES_STATE_IMPORTED;ELSEnIssuesTypes := NULL;END IF;Basic_Clone ( NNorig_pv_id, retPv_id, NNrtag_id, NNuser_id, origPkg_id, nIssuesTypes );ELSEretPv_id := package_versions_rec.pv_id;END IF;CLOSE package_versions_cur;ELSEretPv_id := NNsetto_pv_id;END IF;/* ---------------------------------------------------- *//* Insert into Release Contents (EXCLUDE PATCHES) *//* ---------------------------------------------------- */IF (package_versions_rec.is_patch != 'Y') OR (package_versions_rec.is_patch IS NULL) THENUPDATE RELEASE_CONTENTSET pv_id = retPv_id,insert_stamp = Ora_Sysdate,insertor_id = NNuser_idWHERE rtag_id = NNrtag_idAND pv_id = NNorig_pv_id;/* LOG ACTION */SELECT proj.PROJ_NAME ||' / '|| vt.VTREE_NAME ||' / '|| rt.RTAG_NAME INTO ReleaseLocationFROM PROJECTS proj,VTREES vt,RELEASE_TAGS rtWHERE rt.VTREE_ID = vt.VTREE_IDAND vt.PROJ_ID = proj.PROJ_IDAND rt.RTAG_ID = NNrtag_id;Log_Action ( NNorig_pv_id, 'replaced_with', NNuser_id, 'Replacing version: '|| SSpkg_version ||' at '|| ReleaseLocation );Log_Action ( retPv_id, 'add', NNuser_id, 'Location: '|| ReleaseLocation );END IF;END New_Version;/CREATE OR REPLACE PROCEDURE Reset_Ignore_Warnings ( sPvIdList IN VARCHAR2,nRTagId IN RELEASE_CONTENT.RTAG_ID%TYPE ) IS/* ---------------------------------------------------------------------------Version: 5.0--------------------------------------------------------------------------- */oPvIdCollector RELMGR_NUMBER_TAB_t := RELMGR_NUMBER_TAB_t();BEGINoPvIdCollector := IN_LIST_NUMBER ( sPvIdList );-- Remove Ignore WarningsDELETEFROM ignore_warningsWHERE (rtag_id, pv_id, dpv_id) IN(SELECT igw.rtag_id, igw.pv_id, igw.dpv_idFROM ignore_warnings igw,package_versions dpv,package_versions pvWHERE igw.dpv_id = dpv.pv_idAND igw.rtag_id = nRTagIdAND dpv.pkg_id = pv.pkg_idAND NVL(dpv.v_ext,'|LINK_A_NULL|') = NVL(pv.v_ext,'|LINK_A_NULL|')AND pv.pv_id IN ( SELECT * FROM TABLE ( CAST ( oPvIdCollector AS RELMGR_NUMBER_TAB_t ) ) ));END Reset_Ignore_Warnings;/CREATE OR REPLACE FUNCTION GET_V_EXT (SSpkg_version IN PACKAGE_VERSIONS.pkg_version%TYPE) RETURN VARCHAR2 IS/* ---------------------------------------------------------------------------Version: 3.0.0--------------------------------------------------------------------------- */SSV_EXT PACKAGE_VERSIONS.V_EXT%TYPE;lastDot NUMBER;BEGINlastDot := INSTR (SSpkg_version, '.', -1);IF ( lastDot > 0 ) AND ( lastDot != LENGTH(SSpkg_version) )THEN-- YES dot separator found --SSV_EXT := SUBSTR (SSpkg_version, lastDot, LENGTH(SSpkg_version)-lastDot+1);IF IS_VERSION_EXTENSION ( SSV_EXT )THENRETURN SSV_EXT;ELSERETURN NULL;END IF;ELSE-- NO dot separator found --RETURN NULL;END IF;END GET_V_EXT;/CREATE OR REPLACE PROCEDURE REMOVE_PACKAGE_INTEREST(nProjId IN PROJECTS.PROJ_ID%TYPE,nPkgId IN PACKAGES.PKG_ID%TYPE,nUserId IN NUMBER) IS/******************************************************************************NAME: REMOVE_PACKAGE_INTERESTPURPOSE: To remove interest in a given package per projectREVISIONS:Ver Date Author Description--------- ---------- --------------- ------------------------------------1.0 12/05/2006 Rupesh Solanki 1. Created this procedure.NOTES:Automatically available Auto Replace Keywords:Object Name: REMOVE_PACKAGE_INTERESTSysdate: 12/05/2006Date and Time: 12/05/2006, 11:28:50 AM, and 12/05/2006 11:28:50 AMUsername: (set in TOAD Options, Procedure Editor)Table Name: (set in the "New PL/SQL Object" dialog)******************************************************************************/BEGIN--Delete from PACKAGE_INTERESTDELETE FROM PACKAGE_INTERESTWHERE PROJ_ID = nProjIdAND PKG_ID = nPkgIdAND USER_ID = nUserId;END REMOVE_PACKAGE_INTEREST;/CREATE OR REPLACE PROCEDURE Touch_Release ( nRtagId IN RELEASE_TAGS.RTAG_ID%TYPE ) IS/* ---------------------------------------------------------------------------Version: 3.0--------------------------------------------------------------------------- */BEGIN-- Touch Release for RebuildUPDATE RELEASE_TAGS rt SETrt.REBUILD_ENV = 'Y',rt.REBUILD_STAMP = 0WHERE rt.RTAG_ID = nRtagId;COMMIT;END Touch_Release;/CREATE OR REPLACE PROCEDURE Update_Runtime_Dependency ( NNpv_id IN RUNTIME_DEPENDENCIES.pv_id%TYPE,NNrtd_id IN RUNTIME_DEPENDENCIES.rtd_id%TYPE,SSrtd_version IN PACKAGE_VERSIONS.pkg_version%TYPE,SSrtd_url IN RUNTIME_DEPENDENCIES.rtd_url%TYPE,SSrtd_comments IN RUNTIME_DEPENDENCIES.rtd_comments%TYPE,NNuser_id IN NUMBER ) IS/* ---------------------------------------------------------------------------Version: 3.0.1--------------------------------------------------------------------------- */retRTD_ID NUMBER;CURSOR rtd_cur ISSELECT pv.pkg_version, pkg.pkg_nameFROM runtime_dependencies rtd,package_versions pv,packages pkgWHERE rtd.rtd_id = pv.pv_idAND pv.pkg_id = pkg.pkg_idAND rtd.pv_id = NNpv_idAND rtd.rtd_id = NNrtd_id;rtd_rec rtd_cur%ROWTYPE;CURSOR old_rtd_cur ISSELECT pv.pv_idFROM package_versions pvWHERE pv.pkg_id IN ( SELECT rtdpv.pkg_idFROM package_versions rtdpvWHERE rtdpv.pv_id = NNrtd_id )AND pv.pkg_version = SSrtd_version;old_rtd_rec old_rtd_cur%ROWTYPE;BEGIN-- Get current runtime dependency detailsOPEN rtd_cur;FETCH rtd_cur INTO rtd_rec;IF rtd_rec.pkg_version != SSrtd_version THEN-- Version has changed, hence create new runtime dependency --/* NOTE: You must create new version as updating just a version will affectall packages using this runtime dependency and user does not expect that.It is safer to create new version */OPEN old_rtd_cur;FETCH old_rtd_cur INTO old_rtd_rec;IF old_rtd_cur%NOTFOUNDTHEN-- Version not found, hence Create New version --Seed_Package_Names_Versions ( rtd_rec.pkg_name, SSrtd_version, NNuser_id, retRTD_ID );-- Update runtime dependency table --UPDATE runtime_dependencies SETrtd_id = retRTD_ID,rtd_url = SSrtd_url,rtd_comments = SSrtd_commentsWHERE pv_id = NNpv_idAND rtd_id = NNrtd_id;ELSE-- Update runtime dependency table --UPDATE runtime_dependencies SETrtd_id = old_rtd_rec.pv_id,rtd_url = SSrtd_url,rtd_comments = SSrtd_commentsWHERE pv_id = NNpv_idAND rtd_id = NNrtd_id;END IF;CLOSE old_rtd_cur;ELSE-- Version has not changed, hence update everithing except version --UPDATE runtime_dependencies SETrtd_url = SSrtd_url,rtd_comments = SSrtd_commentsWHERE pv_id = NNpv_idAND rtd_id = NNrtd_id;END IF;CLOSE rtd_cur;END Update_Runtime_Dependency;/CREATE OR REPLACE PROCEDURE Log_Action ( nPvId IN PACKAGE_VERSIONS.PV_ID%TYPE,enumActionTypeName IN ACTION_TYPE.NAME%TYPE,nUserId IN ACTION_LOG.USER_ID%TYPE,sAdditionalComments IN ACTION_LOG.DESCRIPTION%TYPE DEFAULT NULL ) IS/* ---------------------------------------------------------------------------Version: 3.0--------------------------------------------------------------------------- */ActionTypeId NUMBER;BEGIN-- Get Action Type FKSELECT act.ACTTYPE_ID INTO ActionTypeIdFROM ACTION_TYPE actWHERE act.NAME = enumActionTypeName;INSERT INTO ACTION_LOG ( USER_ID, ACTION_DATETIME, PV_ID, DESCRIPTION, ACTTYPE_ID )VALUES ( nUserId, ORA_SYSDATETIME, nPvId, sAdditionalComments, ActionTypeId );END Log_Action;/CREATE OR REPLACE PACKAGE BODY PK_WORK_IN_PROGRESS IS/*------------------------------|| Last Modified: S.Vukovic|| Modified Date: 2/May/2005|| Body Version: 1.0------------------------------*//*-------------------------------------------------------------------------------------------------------*/PROCEDURE ADD_PACKAGE ( newPvId IN NUMBER, ViewId IN NUMBER, RtagId IN NUMBER, UserId IN NUMBER ) ISoldPvId NUMBER;ReleaseLocation VARCHAR2(4000);sLocation VARCHAR2(4000) := NULL;BEGIN/*--------------- Business Rules Here -------------------*//*-------------------------------------------------------*/BEGIN-- Check if Exists in "Work in progress" anywhere in the world, except "Closed mode" releasesSELECT proj.PROJ_NAME ||' > '|| rt.RTAG_NAME INTO sLocationFROM WORK_IN_PROGRESS wip,RELEASE_TAGS rt,PROJECTS projWHERE wip.PV_ID = newPvIdAND wip.RTAG_ID = rt.RTAG_IDAND rt.OFFICIAL != 'Y'AND rt.PROJ_ID = proj.PROJ_ID;EXCEPTIONWHEN NO_DATA_FOUND THENsLocation := NULL;END;IF (sLocation IS NULL) THEN-- Add to "Work in progress"INSERT INTO WORK_IN_PROGRESS ( RTAG_ID, PV_ID, VIEW_ID )VALUES( RtagId, newPvId, ViewId );/* LOG ACTION */SELECT proj.PROJ_NAME ||' / '|| rt.RTAG_NAME INTO ReleaseLocationFROM PROJECTS proj,RELEASE_TAGS rtWHERE rt.PROJ_ID = proj.PROJ_IDAND rt.RTAG_ID = RtagId;Log_Action ( newPvId, 'work_in_progress', UserId, 'Location: '|| ReleaseLocation );ELSERAISE_APPLICATION_ERROR (-20000, 'This version is already in Work-In-Progress Area at '|| sLocation ||'.' );END IF;END;/*-------------------------------------------------------------------------------------------------------*/PROCEDURE REMOVE_PACKAGE ( PvId IN NUMBER, RtagId IN NUMBER, UserId IN NUMBER ) ISReleaseLocation VARCHAR2(4000);BEGIN/*--------------- Business Rules Here -------------------*//*-------------------------------------------------------*/-- Get release location for logging pusposesSELECT proj.PROJ_NAME ||' / '|| rt.RTAG_NAME INTO ReleaseLocationFROM PROJECTS proj,RELEASE_TAGS rtWHERE rt.PROJ_ID = proj.PROJ_IDAND rt.RTAG_ID = RtagId;-- Delete from Work In ProgressDELETEFROM WORK_IN_PROGRESS wipWHERE wip.RTAG_ID = RtagIdAND wip.PV_ID = PvId;Log_Action ( PvId, 'delete_from_wip', UserId, 'Location: '|| ReleaseLocation );END;/*-------------------------------------------------------------------------------------------------------*/PROCEDURE REMOVE_PACKAGE_BULK ( PvIdList IN VARCHAR2, RtagId IN NUMBER, UserId IN NUMBER ) ISnIdCollector RELMGR_NUMBER_TAB_t := RELMGR_NUMBER_TAB_t();ReleaseLocation VARCHAR2(4000);PvId NUMBER;BEGIN/*--------------- Business Rules Here -------------------*/IF (PvIdList IS NULL)THENRAISE_APPLICATION_ERROR (-20000, 'Please select at least one package.' );END IF;/*-------------------------------------------------------*/nIdCollector := IN_LIST_NUMBER ( PvIdList );-- Get release location for logging pusposesSELECT proj.PROJ_NAME ||' / '|| rt.RTAG_NAME INTO ReleaseLocationFROM PROJECTS proj,RELEASE_TAGS rtWHERE rt.PROJ_ID = proj.PROJ_IDAND rt.RTAG_ID = RtagId;FOR i IN 1..nIdCollector.COUNTLOOPPvId := nIdCollector(i);-- Delete from Work In ProgressDELETEFROM WORK_IN_PROGRESS wipWHERE wip.RTAG_ID = RtagIdAND wip.PV_ID = PvId;Log_Action ( PvId, 'remove_from_work_in_progress', UserId, 'Location: '|| ReleaseLocation );END LOOP;END;/*-------------------------------------------------------------------------------------------------------*/FUNCTION GET_PACKAGE_VIEW ( PvId IN NUMBER, RtagId IN NUMBER ) RETURN NUMBER ISReturnValue NUMBER;BEGINSELECT wip.VIEW_ID INTO ReturnValueFROM WORK_IN_PROGRESS wipWHERE wip.RTAG_ID = RtagIdAND wip.PV_ID = PvId;RETURN ReturnValue;END;/*-------------------------------------------------------------------------------------------------------*/PROCEDURE GET_VIEW_CONTENT ( RtagId IN NUMBER, ViewId IN NUMBER, RecordSet OUT typeCur ) ISIsBaseView CHAR(1);BEGIN-- Check if the view is BASE VIEWSELECT vi.BASE_VIEW INTO IsBaseViewFROM VIEWS viWHERE vi.VIEW_ID = ViewId;IF (IsBaseView = 'Y') THEN-- Get Base view contentOPEN RecordSet FORSELECT 0 AS PKG_STATE,NULL AS DEPRECATED_STATE,pv.pv_id,pkg.pkg_name,pv.pkg_version,pv.dlocked,pv.pv_description,pv.BUILD_TYPEFROM WORK_IN_PROGRESS rel,packages pkg,package_versions pvWHERE pv.pkg_id = pkg.pkg_idAND rel.pv_id = pv.pv_idAND rel.VIEW_ID = ViewIdAND rel.RTAG_ID = RtagIdORDER BY UPPER(pkg.PKG_NAME);ELSE-- Get non base view contentOPEN RecordSet FORSELECT 0 AS PKG_STATE,NULL AS DEPRECATED_STATE,pv.pv_id,pkg.pkg_name,pv.pkg_version,pv.dlocked,pv.pv_description,pv.BUILD_TYPEFROM WORK_IN_PROGRESS rel,packages pkg,package_versions pv,VIEW_DEF vdWHERE pv.pkg_id = pkg.pkg_idAND rel.pv_id = pv.pv_idAND vd.VIEW_ID = ViewIdAND vd.PKG_ID = pv.PKG_IDAND rel.RTAG_ID = RtagIdORDER BY UPPER(pkg.PKG_NAME);END IF;END;/*-------------------------------------------------------------------------------------------------------*/PROCEDURE CHANGE_PACKAGE_VIEW ( PvId IN NUMBER, RtagId IN NUMBER, NewViewId IN NUMBER ) ISBEGINUPDATE WORK_IN_PROGRESS wip SETwip.VIEW_ID = NewViewIdWHERE wip.PV_ID = PvIdAND wip.RTAG_ID = RtagId;END;/*-------------------------------------------------------------------------------------------------------*/END PK_WORK_IN_PROGRESS;/CREATE OR REPLACE PACKAGE BODY pk_packageIS/*------------------------------|| Last Modified: R. Solanki|| Modified Date: 09/03/2006|| Body Version: 1.7------------------------------*//*-------------------------------------------------------------------------------------------------------*/PROCEDURE new_version (nlastpvid IN NUMBER,snewpkgversion IN VARCHAR2 DEFAULT NULL,cbuildtype IN CHAR,nsettopvid IN NUMBER DEFAULT NULL,nrtagid IN NUMBER,nuserid IN NUMBER,enumissues_state_imported IN NUMBER,returnpvid OUT NUMBER)ISorigpkg_id package_versions.pkg_id%TYPE;origdlocked package_versions.dlocked%TYPE;ssv_mm package_versions.v_mm%TYPE;ssv_nmm package_versions.v_nmm%TYPE;ssv_ext package_versions.v_ext%TYPE;spackageversion VARCHAR2 (4000);nissuestypes NUMBER;nviewid NUMBER;reccount NUMBER;isreleased package_versions.dlocked%TYPE := 'N';slabel VARCHAR2 (4000) := NULL;CURSOR package_versions_curISSELECT pv.pv_id, pv.is_patch, pv.dlockedFROM package_versions pvWHERE pv.pkg_version = snewpkgversionAND pv.pkg_id IN (SELECT DISTINCT origpv.pkg_idFROM package_versions origpvWHERE origpv.pv_id = nlastpvid);package_versions_rec package_versions_cur%ROWTYPE;CURSOR clone_package_versions_curISSELECT DISTINCT pkg_id, dlockedFROM package_versionsWHERE pv_id = nlastpvid;clone_package_versions_rec clone_package_versions_cur%ROWTYPE;BEGINspackageversion := snewpkgversion;IF nsettopvid IS NULLTHEN-- SetToPv_id is not supplied, hence proceed./* ---------------------------------------------------- *//* Find id package_version exists *//* ---------------------------------------------------- */OPEN package_versions_cur;FETCH package_versions_curINTO package_versions_rec;IF package_versions_cur%NOTFOUNDTHEN--- Create brand new package ---SELECT seq_pv_id.NEXTVALINTO returnpvidFROM DUAL;-- Split Version to get extention + othersplit_version (spackageversion, ssv_mm, ssv_nmm, ssv_ext);-- Get previous package to clone fromOPEN clone_package_versions_cur;FETCH clone_package_versions_curINTO clone_package_versions_rec;origpkg_id := clone_package_versions_rec.pkg_id;origdlocked := clone_package_versions_rec.dlocked;CLOSE clone_package_versions_cur;-- Automated built configIF (cbuildtype = 'A')THENspackageversion := '(' || returnpvid || ')' || ssv_ext;-- Make sure that version is still uniqueEND 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, is_deployable,is_build_env_required, build_type, bs_id)SELECT returnpvid AS pv_id, origpkg_id AS pkg_id,spackageversion AS pkg_version, 'N' AS dlocked,ora_sysdate AS created_stamp, nuserid AS creator_id,ora_sysdatetime AS modified_stamp,nuserid 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,nlastpvid AS last_pv_id, pv.owner_id, pv.is_deployable,pv.is_build_env_required, cbuildtype, pv.bs_idFROM package_versions pvWHERE pv.pv_id = nlastpvid;-- Set Issues Type for cloning ---IF origdlocked = 'Y'THENnissuestypes := enumissues_state_imported;ELSEnissuestypes := NULL;END IF;-- Update Label for automated builtIF (cbuildtype = 'A')THENslabel := get_automated_label (returnpvid);UPDATE package_versions pvSET pv.pkg_label = slabelWHERE pv.pv_id = returnpvid;END IF;basic_clone (nlastpvid,returnpvid,nrtagid,nuserid,origpkg_id,nissuestypes);ELSE--- Package already exists, hence reuse ---returnpvid := package_versions_rec.pv_id;isreleased := package_versions_rec.dlocked;END IF;CLOSE package_versions_cur;ELSEreturnpvid := nsettopvid;END IF;END new_version;/*-------------------------------------------------------------------------------------------------------*/PROCEDURE change_state (pvid IN NUMBER,newstate IN package_versions.dlocked%TYPE,userid IN NUMBER)ISBEGIN-- Set package in Released modeUPDATE package_versions pvSET pv.dlocked = newstate,pv.modified_stamp = ora_sysdatetime,pv.modifier_id = useridWHERE pv.pv_id = pvid;-- Log action --IF newstate = 'Y'THEN-- RELEASED --log_action (pvid,'makeofficial',userid,'Package state change to: Released');ELSIF newstate = 'N'THEN-- UNLOCKED --log_action (pvid,'makeunofficial',userid,'Package state change to: Ulocked');ELSIF newstate = 'P'THEN-- PENDING APPROVAL --log_action (pvid,'add_to_planned',userid,'Package state change to: Pending Approval');ELSIF newstate = 'R'THEN-- REJECTED --log_action (pvid,'reject_package',userid,'Package state change to: Rejected');ELSIF newstate = 'A'THEN-- APPROVED --log_action (pvid,'approve_package',userid,'Package state change to: Approved');END IF;END;/*-------------------------------------------------------------------------------------------------------*/PROCEDURE destroy_package (pvid IN NUMBER,overridewarnings IN CHAR DEFAULT 'N',problemstring OUT VARCHAR2)ISLOCKED CHAR;pkgid NUMBER;ROWCOUNT NUMBER;BEGIN/*|| This will destroy all package details from database.|| It will only be used to remove unwanted work in progress packages,|| or mestaken versions*//*--------------- Business Rules Here -------------------*/problemstring := NULL;IF overridewarnings = 'N'THEN-- Package must not be officialSELECT pv.dlockedINTO LOCKEDFROM package_versions pvWHERE pv.pv_id = pvid;IF LOCKED = 'Y'THENproblemstring :=problemstring|| '- Package is locked and released.'|| UTL_TCP.crlf;END IF;-- Cannot remove if used in BOMsSELECT COUNT (osc.prod_id)INTO ROWCOUNTFROM deployment_manager.os_contents oscWHERE osc.prod_id = pvid;IF ROWCOUNT > 0THENproblemstring :=problemstring|| '- Package is part of Bill-Of-Material (BOM) in Deployment Manager.'|| UTL_TCP.crlf;END IF;-- Cannot remove if Referenced as build dependencySELECT COUNT (dep.pv_id)INTO ROWCOUNTFROM package_dependencies depWHERE dep.dpv_id = pvid;IF ROWCOUNT > 0THENproblemstring :=problemstring|| '- Package is referenced by other package as build dependency.'|| UTL_TCP.crlf;END IF;-- Cannot remove if Referenced as runtime dependencySELECT COUNT (rd.pv_id)INTO ROWCOUNTFROM runtime_dependencies rdWHERE rd.rtd_id = pvid;IF ROWCOUNT > 0THENproblemstring :=problemstring|| '- Package is referenced by other package as runtime dependency.'|| UTL_TCP.crlf;END IF;-- Cannot remove if Referenced as patchSELECT COUNT (pp.pv_id)INTO ROWCOUNTFROM package_patches ppWHERE pp.patch_id = pvid;IF ROWCOUNT > 0THENproblemstring :=problemstring|| '- Package is used as patch by other package.'|| UTL_TCP.crlf;END IF;END IF;/*-------------------------------------------------------*/IF (problemstring IS NULL)THEN--- Remove From Work in ProgressDELETE FROM work_in_progress wipWHERE wip.pv_id = pvid;--- Remove From PendingDELETE FROM planned plWHERE pl.pv_id = pvid;--- Remove From Released areaDELETE FROM release_content rcWHERE rc.pv_id = pvid;---Remove From Package ProcessesDELETE FROM package_processes ppWHERE pp.pv_id = pvid;--- Remove DependenciesDELETE FROM package_dependencies depWHERE dep.pv_id = pvid;DELETE FROM package_dependencies depWHERE dep.dpv_id = pvid;--- Remove Runtime dependenciesDELETE FROM runtime_dependencies rtdWHERE rtd.pv_id = pvid;DELETE FROM runtime_dependencies rtdWHERE rtd.rtd_id = pvid;--- Remove componentsDELETE FROM product_components pcWHERE pc.pv_id = pvid;DELETE FROM release_components rcWHERE rc.pv_id = pvid;--- Remove From Notification HistoryDELETE FROM notification_history nhWHERE nh.pv_id = pvid;--- Remove From Ignore WarningsDELETE FROM ignore_warnings iwWHERE iw.pv_id = pvid;--- Remove From Additional NotesDELETE FROM additional_notes anWHERE an.pv_id = pvid;--- Remove From CQ IssuesDELETE FROM cq_issues cqWHERE cq.pv_id = pvid;--- Remove from Package PatchesDELETE FROM package_patches ppWHERE pp.pv_id = pvid;DELETE FROM package_patches ppWHERE pp.patch_id = pvid;--- Remove From Package DocumentsDELETE FROM package_documents pdWHERE pd.pv_id = pvid;--- Remove from Code ReviewDELETE FROM code_reviews crWHERE cr.pv_id = pvid;--- Remove from Unit TestsDELETE FROM unit_tests utWHERE ut.pv_id = pvid;--- Remove from Package BuildEnvDELETE FROM package_build_env pbeWHERE pbe.pv_id = pvid;--- Remove from Build OrderDELETE FROM build_order boWHERE bo.pv_id = pvid;--- Remove from Note ManagerDELETE FROM note_manager nmWHERE nm.nid = pvid;--- Remove from Action logDELETE FROM action_log alWHERE al.pv_id = pvid;--- Remove from Do Not RippleDELETE FROM DO_NOT_RIPPLE dnrWHERE dnr.PV_ID = pvid;--- Remove from Jira IssuesDELETE FROM JIRA_ISSUES jiraWHERE jira.PV_ID = pvid;--- Finally Remove From Package Versions--- Get Package nameSELECT pv.pkg_idINTO pkgidFROM package_versions pvWHERE pv.pv_id = pvid;DELETE FROM package_versions pvWHERE pv.pv_id = pvid;--- Remove package name if not used any moreSELECT COUNT (pv.pv_id)INTO ROWCOUNTFROM package_versions pvWHERE pv.pkg_id = pkgid;IF ROWCOUNT < 1THENDELETE FROM PACKAGES pkgWHERE pkg.pkg_id = pkgid;END IF;END IF;END;/*-------------------------------------------------------------------------------------------------------*/PROCEDURE new_patch (snewpatchversion IN package_versions.pkg_version%TYPE,nparentpvid IN NUMBER,spatchidlist IN VARCHAR2,nuserid IN NUMBER,returnpatchid OUT NUMBER)ISpatchpv_id NUMBER;parpkg_id NUMBER;lastinstallorder NUMBER;ispatchdlocked package_versions.dlocked%TYPE;ssv_mm package_versions.v_mm%TYPE;ssv_nmm package_versions.v_nmm%TYPE;ssv_ext package_versions.v_ext%TYPE;opatchdepcollector relmgr_number_tab_t := relmgr_number_tab_t();CURSOR parent_curISSELECT pv.*, pkg.pkg_nameFROM package_versions pv, PACKAGES pkgWHERE pv.pv_id = nparentpvid AND pv.pkg_id = pkg.pkg_id;parent_rec parent_cur%ROWTYPE;CURSOR patch_curISSELECT pv.*, pg.pkg_nameFROM package_versions pv, PACKAGES pgWHERE pv.pkg_id = parpkg_idAND pv.pkg_version = snewpatchversionAND pv.pkg_id = pg.pkg_id;patch_rec patch_cur%ROWTYPE;CURSOR releases_curISSELECT rc.pv_idFROM release_content rcWHERE rc.pv_id = patch_rec.pv_id;releases_rec releases_cur%ROWTYPE;BEGIN-- Get Last Install OrderSELECT COUNT (*)INTO lastinstallorderFROM package_patches ppWHERE pp.pv_id = nparentpvid;-- Get parent detailsOPEN parent_cur;FETCH parent_curINTO parent_rec;parpkg_id := parent_rec.pkg_id;-- Find if patch exists in databaseOPEN patch_cur;FETCH patch_curINTO patch_rec;-- Parent must be officialIF parent_rec.dlocked = 'Y'THENIF patch_cur%NOTFOUNDTHENispatchdlocked := 'N';-- Create new patch version --SELECT seq_pv_id.NEXTVALINTO patchpv_idFROM DUAL;split_version (snewpatchversion, ssv_mm, ssv_nmm, ssv_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,src_path,pv_description,owner_id, is_patch, last_pv_id, build_type)VALUES (patchpv_id, parpkg_id, snewpatchversion,ispatchdlocked, ora_sysdate, nuserid,ora_sysdatetime, nuserid, ssv_mm, ssv_nmm, ssv_ext,parent_rec.src_path,'This is a patch to '|| parent_rec.pkg_name|| ' '|| parent_rec.pkg_version,nuserid, 'Y', patchpv_id, 'M');INSERT INTO package_patches(pv_id, patch_id, install_order)(SELECT nparentpvid AS pv_id, pv.pv_id AS patch_id,lastinstallorder + 1 AS install_orderFROM package_versions pvWHERE pv.pv_id = patchpv_id AND pv.is_patch = 'Y');/* LOG ACTION */log_action (patchpv_id,'new_version',nuserid,'Patch version created: ' || snewpatchversion);log_action (nparentpvid,'patch_add',nuserid,'New patch created and attached: ' || snewpatchversion);ELSEpatchpv_id := patch_rec.pv_id;ispatchdlocked := patch_rec.dlocked;-- Find if pv_id exists in release content (i.e. it cannot be a patch)OPEN releases_cur;FETCH releases_curINTO releases_rec;IF releases_cur%NOTFOUNDTHEN-- This pv_id is trully a patch, hence add Y to column IS_PATCHUPDATE package_versionsSET is_patch = 'Y'WHERE pv_id = patchpv_id;INSERT INTO package_patches(pv_id, patch_id, install_order)(SELECT nparentpvid AS pv_id, pv.pv_id AS patch_id,lastinstallorder + 1 AS install_orderFROM package_versions pvWHERE pv.pv_id = patchpv_id AND pv.is_patch = 'Y');END IF;CLOSE releases_cur;/* LOG ACTION */log_action (nparentpvid,'patch_add',nuserid,'Patch version was found and attached: '|| snewpatchversion);END IF;END IF;/* Create Patch Dependencies */opatchdepcollector := in_list_number (spatchidlist);-- Make sure patch is unofficial before altering its dependenciesIF (opatchdepcollector.COUNT > 0) AND (ispatchdlocked = 'N')THEN-- Delete Existing DependenciesDELETE FROM package_dependencies depWHERE dep.pv_id = patchpv_id;-- Insert new dependenciesINSERT INTO package_dependencies(pv_id, dpv_id, pkg_id, dpkg_id, build_type)SELECT patchpv_id AS pv_id, pv.pv_id AS dpv_id,parpkg_id AS pkg_id, pv.pkg_id AS dpkg_id,'L' AS build_typeFROM package_versions pvWHERE pv.pv_id IN (SELECT *FROM TABLE(CAST(opatchdepcollector AS relmgr_number_tab_t)));END IF;-- Return patch_idreturnpatchid := patchpv_id;CLOSE parent_cur;CLOSE patch_cur;EXCEPTIONWHEN DUP_VAL_ON_INDEXTHENraise_application_error (-20000,'Patch version '|| snewpatchversion|| ' already exist.');END;/*-------------------------------------------------------------------------------------------------------*/PROCEDURE obsolete_patch (patchid IN NUMBER,isobsolete IN CHAR,obsoletecomments IN VARCHAR2,userid IN NUMBER)ISBEGIN-- Update patchUPDATE package_versions pvSET pv.is_obsolete = isobsolete,pv.obsolete_comments = obsoletecommentsWHERE pv.pv_id = patchid;/*-- Update patch childrenUPDATE PACKAGE_VERSIONS pv SETpv.IS_OBSOLETE = IsObsolete,pv.OBSOLETE_COMMENTS = ObsoleteCommentsWHERE pv.PV_ID IN (SELECT DISTINCT dep.DPV_IDFROM PACKAGE_DEPENDENCIES depWHERE dep.PV_ID = PatchId);-- Update patch parentUPDATE PACKAGE_VERSIONS pv SETpv.IS_OBSOLETE = IsObsolete,pv.OBSOLETE_COMMENTS = ObsoleteCommentsWHERE pv.PV_ID IN (SELECT DISTINCT dep.PV_IDFROM PACKAGE_DEPENDENCIES depWHERE dep.DPV_ID = PatchId);*//* LOG ACTION */IF isobsolete IS NOT NULLTHENlog_action (patchid,'patch_obsolete',userid,'Obsolete patch. ' || obsoletecomments);ELSElog_action (patchid,'patch_obsolete',userid,'Undo patch obsolete.');END IF;END;/*-------------------------------------------------------------------------------------------------------*/PROCEDURE obsolete_patches (spatchidlist IN VARCHAR2,isobsolete IN CHAR,obsoletecomments IN VARCHAR2,userid IN NUMBER)ISBEGIN/*--------------- Business Rules Here -------------------*/IF spatchidlist IS NULLTHENraise_application_error (-20000,'Please select one or more Patches.');END IF;/*-------------------------------------------------------*/-- Update patchUPDATE package_versions pvSET pv.is_obsolete = isobsolete,pv.obsolete_comments = obsoletecommentsWHERE pv.pv_id IN (SELECT *FROM THE(SELECT CAST(in_list_number (spatchidlist) AS relmgr_number_tab_t)FROM DUAL));/*-- Update patch childrenUPDATE PACKAGE_VERSIONS pv SETpv.IS_OBSOLETE = IsObsolete,pv.OBSOLETE_COMMENTS = ObsoleteCommentsWHERE pv.PV_ID IN (SELECT DISTINCT dep.DPV_IDFROM PACKAGE_DEPENDENCIES depWHERE dep.PV_ID = PatchId);-- Update patch parentUPDATE PACKAGE_VERSIONS pv SETpv.IS_OBSOLETE = IsObsolete,pv.OBSOLETE_COMMENTS = ObsoleteCommentsWHERE pv.PV_ID IN (SELECT DISTINCT dep.PV_IDFROM PACKAGE_DEPENDENCIES depWHERE dep.DPV_ID = PatchId);*//* LOG ACTIONIF IsObsolete IS NOT NULL THENLog_Action ( PatchId, 'patch_obsolete', UserId,'Obsolete patch. '|| ObsoleteComments );ELSELog_Action ( PatchId, 'patch_obsolete', UserId,'Undo patch obsolete.' );END IF; */END;/*-------------------------------------------------------------------------------------------------------*/PROCEDURE add_process (nprocid IN processes.proc_id%TYPE,shealthtag IN processes.proc_name%TYPE,sprocdesc IN processes.proc_description%TYPE,scmdinterface IN processes.run_as%TYPE,spkgowner IN processes.pkg_owner%TYPE,sisinterface IN processes.is_interface%TYPE,npvid IN package_processes.pv_id%TYPE,nuserid IN NUMBER)ISpkgname VARCHAR2 (100);/*Rupesh Release on 17/05/2006*/BEGININSERT INTO processes(proc_id, proc_name, proc_description, run_as, pkg_owner,is_interface)VALUES (nprocid, shealthtag, sprocdesc, scmdinterface, spkgowner,sisinterface);pk_package.add_package_process (nprocid, npvid, nuserid);END;/*-------------------------------------------------------------------------------------------------------*/PROCEDURE add_package_process (nprocidlist IN VARCHAR2,npvid IN package_processes.pv_id%TYPE,nuserid IN NUMBER)ISprocessname VARCHAR2 (4000);/*Rupesh Release on 17/05/2006*/CURSOR proc_curISSELECT prc.proc_idFROM processes prcWHERE prc.proc_id IN (SELECT *FROM THE(SELECT CAST(in_list_number (nprocidlist) AS relmgr_number_tab_t)FROM DUAL));proc_rec proc_cur%ROWTYPE;BEGINOPEN proc_cur;FETCH proc_curINTO proc_rec;WHILE proc_cur%FOUNDLOOPINSERT INTO package_processes(proc_id, pv_id)VALUES (proc_rec.proc_id, npvid);SELECT prc.proc_nameINTO processnameFROM processes prcWHERE prc.proc_id = proc_rec.proc_id;-- Log Action --log_action (npvid,'process_add',nuserid,'Added process with health tag ' || processname);FETCH proc_curINTO proc_rec;END LOOP;END;/*-------------------------------------------------------------------------------------------------------*/PROCEDURE remove_process (nprocid IN package_processes.proc_id%TYPE,npvid IN package_processes.pv_id%TYPE,nuserid IN NUMBER)ISprocessname VARCHAR2 (4000);/* Rupesh Release on 17/05/2006*/BEGINSELECT prc.proc_nameINTO processnameFROM processes prcWHERE prc.proc_id = nprocid;DELETE FROM package_processesWHERE proc_id = nprocid AND pv_id = npvid;-- Log Action --log_action (npvid,'process_remove',nuserid,'Removed process with health tag ' || processname);END;/*-------------------------------------------------------------------------------------------------------*//*Author: Rupesh SolankiPurpose: To move package versions from one release to anotherRelease: 4th September 2006*/PROCEDURE move_package (npvid IN package_versions.pv_id%TYPE,nrtagid IN release_tags.rtag_id%TYPE,nnewrtagid IN release_tags.rtag_id%TYPE,nuserid IN NUMBER)ISoldrtag_name VARCHAR2(4000);newrtag_name VARCHAR2(4000);BEGINSELECT rtag_name into oldrtag_nameFROM RELEASE_TAGSWHERE rtag_id = nrtagid;SELECT rtag_name into newrtag_nameFROM RELEASE_TAGSWHERE rtag_id = nnewrtagid;/* Table Work In Progress*/UPDATE WORK_IN_PROGRESSSET RTAG_ID = nnewrtagidWHERE RTAG_ID = nrtagidAND PV_ID = npvid;/* Table PLANNED*/UPDATE PLANNEDSET RTAG_ID = nnewrtagidWHERE RTAG_ID = nrtagidAND PV_ID = npvid;-- Log Action --log_action (npvid,'move_package_version',nuserid,'Moved package version from ' || oldrtag_name || ' to ' || newrtag_name);END;/*-------------------------------------------------------------------------------------------------------*/END pk_package;/CREATE OR REPLACE PACKAGE BODY pk_archiveIS/*------------------------------|| Author: Rupesh Solanki|| Date: 26 October 2006|| Version: 1.0------------------------------*//*-------------------------------------------------------------------------------------------------------*/PROCEDURE restore_and_flush_release_data (nrtagid IN release_tags.rtag_id%TYPE)ISBEGIN/* MIGRATION - PACKAGE_VERSIONS (do not delete the package versions atthe moment) */INSERT INTO release_manager.package_versionsSELECT *FROM package_versionsWHERE pv_id IN (SELECT pv_idFROM release_contentWHERE rtag_id = nrtagid);/* MIGRATION - DO_NOT_RIPPLE */INSERT INTO release_manager.do_not_rippleSELECT *FROM do_not_ripple dnpWHERE pv_id IN (SELECT pv_idFROM release_contentWHERE rtag_id = nrtagid);/* PURGE DATA FROM DO_NOT_RIPPLE */DELETE FROM do_not_rippleWHERE rtag_id = nrtagid AND pv_id IN (SELECT pv_idFROM release_contentWHERE rtag_id = nrtagid);/* MIGRATION - PACKAGE_BUILD_ENV */INSERT INTO release_manager.package_build_envSELECT *FROM package_build_envWHERE pv_id IN (SELECT pv_idFROM release_contentWHERE rtag_id = nrtagid);/* PURGE DATA FROM PACKAGE_BUILD_ENV */DELETE FROM package_build_envWHERE pv_id IN (SELECT pv_idFROM release_contentWHERE rtag_id = nrtagid);/* MIGRATION - PACKAGE_BUILD_INFO */INSERT INTO release_manager.package_build_infoSELECT *FROM package_build_infoWHERE pv_id IN (SELECT pv_idFROM release_contentWHERE rtag_id = nrtagid);/* PURGE DATA FROM PACKAGE_BUILD_INFO */DELETE FROM package_build_infoWHERE pv_id IN (SELECT pv_idFROM release_contentWHERE rtag_id = nrtagid);/* MIGRATION - UNIT_TESTS */INSERT INTO release_manager.unit_testsSELECT *FROM unit_testsWHERE pv_id IN (SELECT pv_idFROM release_contentWHERE rtag_id = nrtagid);/* PURGE DATA FROM UNIT_TESTS*/DELETE FROM unit_testsWHERE pv_id IN (SELECT pv_idFROM release_contentWHERE rtag_id = nrtagid);/* MIGRATION - PACKAGE_PROCESSES */INSERT INTO release_manager.package_processesSELECT *FROM package_processesWHERE pv_id IN (SELECT pv_idFROM release_contentWHERE rtag_id = nrtagid);/* PURGE DATA FROM PACKAGE_PROCESSES*/DELETE FROM package_processesWHERE pv_id IN (SELECT pv_idFROM release_contentWHERE rtag_id = nrtagid);/* MIGRATION - PACKAGE_DEPENDENCIES */INSERT INTO release_manager.package_dependenciesSELECT *FROM package_dependenciesWHERE pv_id IN (SELECT pv_idFROM release_contentWHERE rtag_id = nrtagid);/* PURGE DATA FROM PACKAGE_DEPENDENCIES*/DELETE FROM package_dependenciesWHERE pv_id IN (SELECT pv_idFROM release_contentWHERE rtag_id = nrtagid);/* MIGRATION - CODE_REVIEWS */INSERT INTO release_manager.code_reviewsSELECT *FROM code_reviewsWHERE pv_id IN (SELECT pv_idFROM release_contentWHERE rtag_id = nrtagid);/* PURGE DATA FROM CODE_REVIEWS*/DELETE FROM code_reviewsWHERE pv_id IN (SELECT pv_idFROM release_contentWHERE rtag_id = nrtagid);/* MIGRATION - RUNTIME_DEPENDENCIES*/INSERT INTO release_manager.runtime_dependenciesSELECT *FROM runtime_dependenciesWHERE pv_id IN (SELECT pv_idFROM release_contentWHERE rtag_id = nrtagid);/* PURGE DATA FROM RUNTIME_DEPENDENCIES*/DELETE FROM runtime_dependenciesWHERE pv_id IN (SELECT pv_idFROM release_contentWHERE rtag_id = nrtagid);/* MIGRATION - PACKAGE_DOCUMENTS */INSERT INTO release_manager.package_documentsSELECT *FROM package_documentsWHERE pv_id IN (SELECT pv_idFROM release_contentWHERE rtag_id = nrtagid);/* PURGE DATA FROM PACKAGE_DOCUMENTS*/DELETE FROM package_documentsWHERE pv_id IN (SELECT pv_idFROM release_contentWHERE rtag_id = nrtagid);/* MIGRATION - PACKAGE_PATCHES */INSERT INTO release_manager.package_patchesSELECT *FROM package_patchesWHERE pv_id IN (SELECT pv_idFROM release_contentWHERE rtag_id = nrtagid);/* PURGE DATA FROM PACKAGE_PATCHES*/DELETE FROM package_patchesWHERE pv_id IN (SELECT pv_idFROM release_contentWHERE rtag_id = nrtagid);/* MIGRATION - CQ_ISSUES */INSERT INTO release_manager.cq_issuesSELECT *FROM cq_issuesWHERE pv_id IN (SELECT pv_idFROM release_contentWHERE rtag_id = nrtagid);/* PURGE DATA FROM CQ_ISSUES*/DELETE FROM cq_issuesWHERE pv_id IN (SELECT pv_idFROM release_contentWHERE rtag_id = nrtagid);/* MIGRATION - ADDITIONAL_NOTES */INSERT INTO release_manager.additional_notesSELECT *FROM additional_notesWHERE pv_id IN (SELECT pv_idFROM release_contentWHERE rtag_id = nrtagid);/* PURGE DATA FROM ADDITIONAL_NOTES*/DELETE FROM additional_notesWHERE pv_id IN (SELECT pv_idFROM release_contentWHERE rtag_id = nrtagid);/* MIGRATION - RELEASE_COMPONENTS */INSERT INTO release_manager.release_componentsSELECT *FROM release_componentsWHERE pv_id IN (SELECT pv_idFROM release_contentWHERE rtag_id = nrtagid);/* PURGE DATA FROM RELEASE_COMPONENTS*/DELETE FROM release_componentsWHERE pv_id IN (SELECT pv_idFROM release_contentWHERE rtag_id = nrtagid);/* MIGRATION - IGNORE_WARNINGS */INSERT INTO release_manager.ignore_warningsSELECT *FROM ignore_warningsWHERE pv_id IN (SELECT pv_idFROM release_contentWHERE rtag_id = nrtagid);/* PURGE DATA FROM IGNORE_WARNINGS*/DELETE FROM ignore_warningsWHERE pv_id IN (SELECT pv_idFROM release_contentWHERE rtag_id = nrtagid);/* MIGRATION - NOTIFICATION_HISTORY */INSERT INTO release_manager.notification_historySELECT *FROM notification_historyWHERE pv_id IN (SELECT pv_idFROM release_contentWHERE rtag_id = nrtagid);/* PURGE DATA FROM NOTIFICATION_HISTORY*/DELETE FROM notification_historyWHERE pv_id IN (SELECT pv_idFROM release_contentWHERE rtag_id = nrtagid);/* MIGRATION - BUILD_ORDER */INSERT INTO release_manager.build_orderSELECT *FROM build_orderWHERE pv_id IN (SELECT pv_idFROM release_contentWHERE rtag_id = nrtagid);/* PURGE DATA FROM BUILD_ORDER*/DELETE FROM build_orderWHERE pv_id IN (SELECT pv_idFROM release_contentWHERE rtag_id = nrtagid);/* PURGE DATA FROM WORK_IN_PROGRESSDELETE FROM work_in_progressWHERE rtag_id = nrtagid;*//* PURGE DATA FROM PLANNEDDELETE FROM plannedWHERE rtag_id = nrtagid;*//* MIGRATION - JIRA_ISSUES */INSERT INTO release_manager.jira_issuesSELECT *FROM jira_issuesWHERE pv_id IN (SELECT pv_idFROM release_contentWHERE rtag_id = nrtagid);/* PURGE DATA FROM JIRA_ISSUES*/DELETE FROM jira_issuesWHERE pv_id IN (SELECT pv_idFROM release_contentWHERE rtag_id = nrtagid);/* MIGRATION - PRODUCT_COMPONENTS */INSERT INTO release_manager.product_componentsSELECT *FROM product_componentsWHERE pv_id IN (SELECT pv_idFROM release_contentWHERE rtag_id = nrtagid);/* PURGE DATA FROM PRODUCT_COMPONENTS*/DELETE FROM product_componentsWHERE pv_id IN (SELECT pv_idFROM release_contentWHERE rtag_id = nrtagid);/* MIGRATION - ACTION_LOG */INSERT INTO release_manager.action_logSELECT *FROM action_logWHERE pv_id IN (SELECT pv_idFROM release_contentWHERE rtag_id = nrtagid);/* PURGE DATA FROM ACTION_LOG*/DELETE FROM action_logWHERE pv_id IN (SELECT pv_idFROM release_contentWHERE rtag_id = nrtagid);/* MIGRATION - PROJECT_ACTION_LOG */INSERT INTO release_manager.project_action_logSELECT *FROM project_action_logWHERE rtag_id = nrtagid;/* PURGE DATA FROM PROJECT_ACTION_LOG */DELETE FROM project_action_logWHERE rtag_id = nrtagid;/* MIGRATION - DEPRECATED_PACKAGES */INSERT INTO release_manager.deprecated_packagesSELECT *FROM deprecated_packagesWHERE rtag_id = nrtagid;/* PURGE DATA FROM DEPRECATED_PACKAGES */DELETE FROM deprecated_packagesWHERE rtag_id = nrtagid;/* MIGRATION - RELEASE_CONTENT */INSERT INTO release_manager.release_contentSELECT *FROM release_contentWHERE rtag_id = nrtagid;/* PURGE DATA FROM PACKAGE_VERSIONS (delete package versions now asall child records wiped out)*/DELETE FROM package_versionsWHERE pv_id IN (SELECT pv_idFROM release_contentWHERE rtag_id = nrtagid);/* PURGE DATA FROM RELEASE_CONTENT*/DELETE FROM release_contentWHERE rtag_id = nrtagid;/* PURGE DATA FROM RELEASE_TAGS*/DELETE FROM release_tagsWHERE rtag_id = nrtagid;END;/*-------------------------------------------------------------------------------------------------------*/PROCEDURE write_action_log (nuserid IN NUMBER,nrtagid IN release_tags.rtag_id%TYPE)ISBEGIN/* Write Action Log Information */INSERT INTO release_manager.archive_action_log(user_id, date_time_stamp, rtag_id,description)VALUES (nuserid, ora_sysdatetime, nrtagid,'Release has been restored to the RELEASE_MANAGER schema');END;/*-------------------------------------------------------------------------------------------------------*/END pk_archive;/CREATE OR REPLACE PACKAGE BODY PK_APP_UTILS IS/*------------------------------|| Last Modified: S.Vukovic|| Modified Date: 26/Apr/2005|| Body Version: 1.0------------------------------*//*-------------------------------------------------------------------------------------------------------*/PROCEDURE GET_CURRENT_PARAMETERS ( ProjId IN VARCHAR2, RtagId IN VARCHAR2, records OUT typeCur) ISBEGINIF (NOT RtagId IS NULL) THEN-- Case with RTAG_ID presentOPEN records FORSELECT rt.PROJ_ID,rt.RTAG_ID AS RTAG_IDFROM RELEASE_TAGS rtWHERE rt.RTAG_ID = TO_NUMBER(RtagId);ELSIF (NOT ProjId IS NULL) THEN-- Case with PROJ_ID presentOPEN records FORSELECT TO_NUMBER(ProjId) AS PROJ_ID,-1 AS RTAG_IDFROM DUAL;ELSE-- Case with none presentOPEN records FORSELECT -1 AS PROJ_ID,-1 AS RTAG_IDFROM DUAL;END IF;--RETURN records;END;/*-------------------------------------------------------------------------------------------------------*/END PK_APP_UTILS;/CREATE OR REPLACE PACKAGE BODY PK_BUILDAPI IS/*------------------------------|| Last Modified: S.Vukovic|| Modified Date: 30/Mar/2005|| Body Version: 1.0------------------------------*//*-------------------------------------------------------------------------------------------------------*/PROCEDURE Add_Product_Component ( nPvId IN NUMBER,sOsName IN VARCHAR2,sOrigFilePath IN VARCHAR2,sFileName IN VARCHAR2,sDestFilePath IN VARCHAR2,nByteSize IN NUMBER,sCRCcksum IN VARCHAR2 ) ISnOsId NUMBER;BEGIN/*--------------- Business Rules Here -------------------*/-- OS Name Requirements --IF (sOsName IS NULL) THENRAISE_APPLICATION_ERROR (-20000, 'OsName cannot be NULL.');END IF;-- File Requirements --IF (NOT sFileName IS NULL) THENIF (nByteSize IS NULL) THENRAISE_APPLICATION_ERROR (-20000, 'Byte Size cannot be NULL.');ELSIF (sCRCcksum IS NULL) THENRAISE_APPLICATION_ERROR (-20000, 'CRC CKSUM cannot be NULL.');END IF;END IF;-- Folder Requirements ---- No requirements for now./*-------------------------------------------------------*/-- Get OsIdnOsId := Get_OsId ( sOsName );-- Insert component entryINSERT INTO PRODUCT_COMPONENTS ( PV_ID, OS_ID, FILE_PATH, FILE_NAME, DESTINATION_PATH, BYTE_SIZE, CRC_CKSUM )VALUES (nPvId,nOsId,sOrigFilePath,sFileName,sDestFilePath,nByteSize,sCRCcksum);EXCEPTIONWHEN DUP_VAL_ON_INDEXTHENRAISE_APPLICATION_ERROR (-20000, 'Cannot have duplicate product components.');END;/*-------------------------------------------------------------------------------------------------------*/PROCEDURE Remove_All_Product_Components ( nPvId IN NUMBER, sOsName IN VARCHAR2 ) ISnOsId NUMBER;BEGIN/*--------------- Business Rules Here -------------------*//*-------------------------------------------------------*/-- Get OsIdnOsId := Get_OsId ( sOsName );-- Delete component entryDELETEFROM PRODUCT_COMPONENTS pcWHERE pc.PV_ID = nPvIdAND pc.OS_ID = nOsId;END;/*-------------------------------------------------------------------------------------------------------*/FUNCTION Get_OsId ( sOsName IN VARCHAR2 ) RETURN NUMBER ISCode NUMBER;BEGIN-- Get Platform Code --SELECT pf.CODE INTO CodeFROM PLATFORMS pfWHERE UPPER( pf.NAME ) = UPPER( sOsName );RETURN Code;EXCEPTIONWHEN NO_DATA_FOUND THENRAISE_APPLICATION_ERROR (-20000, 'Platform '|| sOsName ||' is not valid. It needs to be added to PLATFORMS table in Release Manager.' );RAISE;END;/*-------------------------------------------------------------------------------------------------------*/END PK_BUILDAPI;/CREATE OR REPLACE PACKAGE BODY 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 ) ISoldPvId 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" releasesSELECT proj.PROJ_NAME ||' > '|| rt.RTAG_NAME, rt.RTAG_ID INTO sLocation, nRtagIdLocationFROM PLANNED pl,RELEASE_TAGS rt,PROJECTS projWHERE pl.PV_ID = newPvIdAND pl.RTAG_ID = rt.RTAG_IDAND rt.OFFICIAL != 'Y'AND rt.PROJ_ID = proj.PROJ_ID;EXCEPTIONWHEN NO_DATA_FOUND THENsLocation := NULL;END;IF (sLocation IS NULL) OR (nRtagIdLocation = RtagId) THEN-- Add to "Pending" areaINSERT INTO PLANNED ( RTAG_ID, PV_ID, VIEW_ID )VALUES( RtagId, newPvId, ViewId );/* LOG ACTION */SELECT proj.PROJ_NAME ||' / '|| rt.RTAG_NAME INTO ReleaseLocationFROM PROJECTS proj,RELEASE_TAGS rtWHERE rt.PROJ_ID = proj.PROJ_IDAND rt.RTAG_ID = RtagId;Log_Action ( newPvId, 'add_to_planned', UserId, 'Location: '|| ReleaseLocation );ELSERAISE_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 ) ISReleaseLocation VARCHAR2(4000);BEGIN/*--------------- Business Rules Here -------------------*//*-------------------------------------------------------*/-- Get release location for logging pusposesSELECT proj.PROJ_NAME ||' / '|| rt.RTAG_NAME INTO ReleaseLocationFROM PROJECTS proj,RELEASE_TAGS rtWHERE rt.PROJ_ID = proj.PROJ_IDAND rt.RTAG_ID = RtagId;-- Delete from Work In ProgressDELETEFROM PLANNED plWHERE pl.RTAG_ID = RtagIdAND 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 ISReturnValue NUMBER;BEGINSELECT pl.VIEW_ID INTO ReturnValueFROM PLANNED plWHERE pl.RTAG_ID = RtagIdAND pl.PV_ID = PvId;RETURN ReturnValue;END;/*-------------------------------------------------------------------------------------------------------*/PROCEDURE GET_VIEW_CONTENT ( RtagId IN NUMBER, ViewId IN NUMBER, RecordSet OUT typeCur ) ISIsBaseView CHAR(1);BEGIN-- Check if the view is BASE VIEWSELECT vi.BASE_VIEW INTO IsBaseViewFROM VIEWS viWHERE vi.VIEW_ID = ViewId;IF (IsBaseView = 'Y') THEN-- Get Base view contentOPEN RecordSet FORSELECT 0 AS PKG_STATE,NULL AS DEPRECATED_STATE,pv.pv_id,pkg.pkg_name,pv.pkg_version,pv.dlocked,pv.pv_description,pv.BUILD_TYPEFROM PLANNED rel,packages pkg,package_versions pvWHERE pv.pkg_id = pkg.pkg_idAND rel.pv_id = pv.pv_idAND rel.VIEW_ID = ViewIdAND rel.RTAG_ID = RtagIdORDER BY UPPER(pkg.PKG_NAME);ELSE-- Get non base view contentOPEN RecordSet FORSELECT 0 AS PKG_STATE,NULL AS DEPRECATED_STATE,pv.pv_id,pkg.pkg_name,pv.pkg_version,pv.dlocked,pv.pv_description,pv.BUILD_TYPEFROM PLANNED rel,packages pkg,package_versions pv,VIEW_DEF vdWHERE pv.pkg_id = pkg.pkg_idAND rel.pv_id = pv.pv_idAND rel.RTAG_ID = RtagIdAND vd.VIEW_ID = ViewIdAND vd.PKG_ID = pv.PKG_IDORDER BY UPPER(pkg.PKG_NAME);END IF;END;/*-------------------------------------------------------------------------------------------------------*/PROCEDURE CHANGE_PACKAGE_VIEW ( PvId IN NUMBER, RtagId IN NUMBER, NewViewId IN NUMBER ) ISBEGINUPDATE PLANNED pl SETpl.VIEW_ID = NewViewIdWHERE pl.PV_ID = PvIdAND pl.RTAG_ID = RtagId;END;/*-------------------------------------------------------------------------------------------------------*/END PK_PLANNED;/