CREATE TABLE PACKAGE_BUILD_ENV
(
  PV_ID NUMBER NOT NULL,
  BE_ID NUMBER NOT NULL,
  BUILD_TYPE NUMBER
)
  TABLESPACE "USERS"
  LOGGING 
  PCTFREE 10
  PCTUSED 0
  INITRANS 1
  MAXTRANS 255
  STORAGE
  (
    INITIAL 128K
    MINEXTENTS 1
    MAXEXTENTS 2147483645
    FREELISTS 1
    FREELIST GROUPS 1
    BUFFER_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"
  LOGGING 
  PCTFREE 10
  PCTUSED 0
  INITRANS 1
  MAXTRANS 255
  STORAGE
  (
    INITIAL 128K
    MINEXTENTS 1
    MAXEXTENTS 2147483645
    FREELISTS 1
    FREELIST GROUPS 1
    BUFFER_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"
  LOGGING 
  PCTFREE 10
  PCTUSED 0
  INITRANS 1
  MAXTRANS 255
  STORAGE
  (
    INITIAL 128K
    MINEXTENTS 1
    MAXEXTENTS 2147483645
    FREELISTS 1
    FREELIST GROUPS 1
    BUFFER_POOL DEFAULT
  )
;

CREATE TABLE DO_NOT_RIPPLE
(
  RTAG_ID NUMBER NOT NULL,
  PV_ID NUMBER NOT NULL
)
  TABLESPACE "USERS"
  LOGGING 
  PCTFREE 10
  PCTUSED 0
  INITRANS 1
  MAXTRANS 255
  STORAGE
  (
    INITIAL 64K
    MINEXTENTS 1
    MAXEXTENTS 2147483645
    FREELISTS 1
    FREELIST GROUPS 1
    BUFFER_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"
  LOGGING 
  PCTFREE 10
  PCTUSED 0
  INITRANS 1
  MAXTRANS 255
  STORAGE
  (
    INITIAL 128K
    MINEXTENTS 1
    MAXEXTENTS 2147483645
    FREELISTS 1
    FREELIST GROUPS 1
    BUFFER_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"
  LOGGING 
  PCTFREE 10
  PCTUSED 0
  INITRANS 1
  MAXTRANS 255
  STORAGE
  (
    INITIAL 64K
    MINEXTENTS 1
    MAXEXTENTS 2147483645
    FREELISTS 1
    FREELIST GROUPS 1
    BUFFER_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"
  LOGGING 
  PCTFREE 10
  PCTUSED 0
  INITRANS 1
  MAXTRANS 255
  STORAGE
  (
    INITIAL 128K
    MINEXTENTS 1
    MAXEXTENTS 2147483645
    FREELISTS 1
    FREELIST GROUPS 1
    BUFFER_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"
  LOGGING 
  PCTFREE 10
  PCTUSED 0
  INITRANS 1
  MAXTRANS 255
  STORAGE
  (
    INITIAL 64K
    MINEXTENTS 1
    MAXEXTENTS 2147483645
    FREELISTS 1
    FREELIST GROUPS 1
    BUFFER_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"
  LOGGING 
  PCTFREE 10
  PCTUSED 40
  INITRANS 1
  MAXTRANS 255
  STORAGE
  (
    INITIAL 3520K
    NEXT 16K
    MINEXTENTS 1
    MAXEXTENTS 2147483645
    PCTINCREASE 0
    FREELISTS 1
    FREELIST GROUPS 1
    BUFFER_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"
  LOGGING 
  PCTFREE 10
  PCTUSED 0
  INITRANS 1
  MAXTRANS 255
  STORAGE
  (
    INITIAL 128K
    MINEXTENTS 1
    MAXEXTENTS 2147483645
    FREELISTS 1
    FREELIST GROUPS 1
    BUFFER_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"
  LOGGING 
  PCTFREE 10
  PCTUSED 0
  INITRANS 1
  MAXTRANS 255
  STORAGE
  (
    INITIAL 128K
    MINEXTENTS 1
    MAXEXTENTS 2147483645
    FREELISTS 1
    FREELIST GROUPS 1
    BUFFER_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"
  LOGGING 
  PCTFREE 10
  PCTUSED 0
  INITRANS 1
  MAXTRANS 255
  STORAGE
  (
    INITIAL 128K
    MINEXTENTS 1
    MAXEXTENTS 2147483645
    FREELISTS 1
    FREELIST GROUPS 1
    BUFFER_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"
  LOGGING 
  PCTFREE 10
  PCTUSED 40
  INITRANS 1
  MAXTRANS 255
  STORAGE
  (
    INITIAL 80K
    NEXT 16K
    MINEXTENTS 1
    MAXEXTENTS 2147483645
    PCTINCREASE 0
    FREELISTS 1
    FREELIST GROUPS 1
    BUFFER_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"
  LOGGING 
  PCTFREE 10
  PCTUSED 40
  INITRANS 1
  MAXTRANS 255
  STORAGE
  (
    INITIAL 64K
    MINEXTENTS 1
    MAXEXTENTS 2147483645
    FREELISTS 1
    FREELIST GROUPS 1
    BUFFER_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"
  LOGGING 
  PCTFREE 10
  PCTUSED 0
  INITRANS 1
  MAXTRANS 255
  STORAGE
  (
    INITIAL 128K
    MINEXTENTS 1
    MAXEXTENTS 2147483645
    FREELISTS 1
    FREELIST GROUPS 1
    BUFFER_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"
  LOGGING 
  PCTFREE 10
  PCTUSED 0
  INITRANS 1
  MAXTRANS 255
  STORAGE
  (
    INITIAL 64K
    MINEXTENTS 1
    MAXEXTENTS 2147483645
    FREELISTS 1
    FREELIST GROUPS 1
    BUFFER_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"
  LOGGING 
  PCTFREE 10
  PCTUSED 0
  INITRANS 1
  MAXTRANS 255
  STORAGE
  (
    INITIAL 128K
    MINEXTENTS 1
    MAXEXTENTS 2147483645
    FREELISTS 1
    FREELIST GROUPS 1
    BUFFER_POOL DEFAULT
  )
;

CREATE TABLE PACKAGE_PROCESSES
(
  PV_ID NUMBER NOT NULL,
  PROC_ID NUMBER NOT NULL
)
  TABLESPACE "USERS"
  LOGGING 
  PCTFREE 10
  PCTUSED 0
  INITRANS 1
  MAXTRANS 255
  STORAGE
  (
    INITIAL 64K
    MINEXTENTS 1
    MAXEXTENTS 2147483645
    FREELISTS 1
    FREELIST GROUPS 1
    BUFFER_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"
  LOGGING 
  PCTFREE 10
  PCTUSED 0
  INITRANS 1
  MAXTRANS 255
  STORAGE
  (
    INITIAL 128K
    MINEXTENTS 1
    MAXEXTENTS 2147483645
    FREELISTS 1
    FREELIST GROUPS 1
    BUFFER_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"
  LOGGING 
  PCTFREE 10
  PCTUSED 0
  INITRANS 1
  MAXTRANS 255
  STORAGE
  (
    INITIAL 128K
    MINEXTENTS 1
    MAXEXTENTS 2147483645
    FREELISTS 1
    FREELIST GROUPS 1
    BUFFER_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"
  LOGGING 
  PCTFREE 10
  PCTUSED 0
  INITRANS 1
  MAXTRANS 255
  STORAGE
  (
    INITIAL 64K
    MINEXTENTS 1
    MAXEXTENTS 2147483645
    FREELISTS 1
    FREELIST GROUPS 1
    BUFFER_POOL DEFAULT
  )
;

CREATE TABLE PLANNED
(
  RTAG_ID NUMBER NOT NULL,
  PV_ID NUMBER NOT NULL,
  VIEW_ID NUMBER NOT NULL
)
  TABLESPACE "USERS"
  LOGGING 
  PCTFREE 10
  PCTUSED 0
  INITRANS 1
  MAXTRANS 255
  STORAGE
  (
    INITIAL 64K
    MINEXTENTS 1
    MAXEXTENTS 2147483645
    FREELISTS 1
    FREELIST GROUPS 1
    BUFFER_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"
  LOGGING 
  PCTFREE 10
  PCTUSED 0
  INITRANS 1
  MAXTRANS 255
  STORAGE
  (
    INITIAL 64K
    MINEXTENTS 1
    MAXEXTENTS 2147483645
    FREELISTS 1
    FREELIST GROUPS 1
    BUFFER_POOL DEFAULT
  )
;

CREATE TABLE PACKAGE_INTEREST
(
  PKG_ID NUMBER NOT NULL,
  PROJ_ID NUMBER NOT NULL,
  USER_ID NUMBER NOT NULL
)
  TABLESPACE "USERS"
  LOGGING 
  PCTFREE 10
  PCTUSED 0
  INITRANS 1
  MAXTRANS 255
  STORAGE
  (
    INITIAL 64K
    MINEXTENTS 1
    MAXEXTENTS 2147483645
    FREELISTS 1
    FREELIST GROUPS 1
    BUFFER_POOL DEFAULT
  )
;

CREATE TABLE PLATFORMS
(
  CODE NUMBER NOT NULL,
  NAME VARCHAR2(255 BYTE) NOT NULL
)
  TABLESPACE "USERS"
  LOGGING 
  PCTFREE 10
  PCTUSED 0
  INITRANS 1
  MAXTRANS 255
  STORAGE
  (
    INITIAL 64K
    MINEXTENTS 1
    MAXEXTENTS 2147483645
    FREELISTS 1
    FREELIST GROUPS 1
    BUFFER_POOL DEFAULT
  )
;

CREATE TABLE ARCHIVE_DATA
(
  RTAG_ID NUMBER(*, 0) NOT NULL,
  PV_ID NUMBER(*, 0) NOT NULL
)
  TABLESPACE "USERS"
  LOGGING 
  PCTFREE 10
  PCTUSED 0
  INITRANS 1
  MAXTRANS 255
  STORAGE
  (
    INITIAL 64K
    MINEXTENTS 1
    MAXEXTENTS 2147483645
    FREELISTS 1
    FREELIST GROUPS 1
    BUFFER_POOL DEFAULT
  )
;

CREATE TABLE VIEW_SETTINGS
(
  USER_ID NUMBER NOT NULL,
  VIEW_ID NUMBER NOT NULL
)
  TABLESPACE "SYSTEM"
  LOGGING 
  PCTFREE 10
  PCTUSED 40
  INITRANS 1
  MAXTRANS 255
  STORAGE
  (
    INITIAL 80K
    NEXT 16K
    MINEXTENTS 1
    MAXEXTENTS 2147483645
    PCTINCREASE 0
    FREELISTS 1
    FREELIST GROUPS 1
    BUFFER_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"
  LOGGING 
  PCTFREE 10
  PCTUSED 0
  INITRANS 1
  MAXTRANS 255
  STORAGE
  (
    INITIAL 128K
    MINEXTENTS 1
    MAXEXTENTS 2147483645
    FREELISTS 1
    FREELIST GROUPS 1
    BUFFER_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"
  LOGGING 
  PCTFREE 10
  PCTUSED 0
  INITRANS 1
  MAXTRANS 255
  STORAGE
  (
    INITIAL 64K
    MINEXTENTS 1
    MAXEXTENTS 2147483645
    FREELISTS 1
    FREELIST GROUPS 1
    BUFFER_POOL DEFAULT
  )
;

CREATE TABLE JIRA_ISSUES
(
  PV_ID NUMBER NOT NULL,
  ISS_KEY VARCHAR2(4000 BYTE),
  DATE_TIME_STAMP DATE
)
  TABLESPACE "SYSTEM"
  LOGGING 
  PCTFREE 10
  PCTUSED 40
  INITRANS 1
  MAXTRANS 255
  STORAGE
  (
    INITIAL 80K
    NEXT 16K
    MINEXTENTS 1
    MAXEXTENTS 2147483645
    PCTINCREASE 0
    FREELISTS 1
    FREELIST GROUPS 1
    BUFFER_POOL DEFAULT
  )
;

CREATE TABLE VIEW_DEF
(
  VIEW_ID NUMBER NOT NULL,
  PKG_ID NUMBER NOT NULL
)
  TABLESPACE "SYSTEM"
  LOGGING 
  PCTFREE 10
  PCTUSED 40
  INITRANS 1
  MAXTRANS 255
  STORAGE
  (
    INITIAL 80K
    NEXT 16K
    MINEXTENTS 1
    MAXEXTENTS 2147483645
    PCTINCREASE 0
    FREELISTS 1
    FREELIST GROUPS 1
    BUFFER_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"
  LOGGING 
  PCTFREE 10
  PCTUSED 40
  INITRANS 1
  MAXTRANS 255
  STORAGE
  (
    INITIAL 80K
    NEXT 16K
    MINEXTENTS 1
    MAXEXTENTS 2147483645
    PCTINCREASE 0
    FREELISTS 1
    FREELIST GROUPS 1
    BUFFER_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"
  LOGGING 
  PCTFREE 10
  PCTUSED 40
  INITRANS 1
  MAXTRANS 255
  STORAGE
  (
    INITIAL 64K
    MINEXTENTS 1
    MAXEXTENTS 2147483645
    FREELISTS 1
    FREELIST GROUPS 1
    BUFFER_POOL DEFAULT
  )
;

CREATE TABLE TEMP_SASH
(
  RTAG_ID NUMBER,
  PROJ_ID NUMBER
)
  TABLESPACE "USERS"
  LOGGING 
  PCTFREE 10
  PCTUSED 0
  INITRANS 1
  MAXTRANS 255
  STORAGE
  (
    INITIAL 64K
    MINEXTENTS 1
    MAXEXTENTS 2147483645
    FREELISTS 1
    FREELIST GROUPS 1
    BUFFER_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"
  LOGGING 
  PCTFREE 10
  PCTUSED 40
  INITRANS 1
  MAXTRANS 255
  STORAGE
  (
    INITIAL 64K
    MINEXTENTS 1
    MAXEXTENTS 2147483645
    FREELISTS 1
    FREELIST GROUPS 1
    BUFFER_POOL DEFAULT
  )
;

CREATE TABLE WORK_IN_PROGRESS
(
  RTAG_ID NUMBER NOT NULL,
  PV_ID NUMBER NOT NULL,
  VIEW_ID NUMBER NOT NULL
)
  TABLESPACE "USERS"
  LOGGING 
  PCTFREE 10
  PCTUSED 0
  INITRANS 1
  MAXTRANS 255
  STORAGE
  (
    INITIAL 64K
    MINEXTENTS 1
    MAXEXTENTS 2147483645
    FREELISTS 1
    FREELIST GROUPS 1
    BUFFER_POOL DEFAULT
  )
;

CREATE TABLE ADVISORY_RIPPLES
(
  RTAG_ID NUMBER NOT NULL,
  PV_ID NUMBER NOT NULL
)
  TABLESPACE "USERS"
  LOGGING 
  PCTFREE 10
  PCTUSED 40
  INITRANS 1
  MAXTRANS 255
  STORAGE
  (
    INITIAL 64K
    MINEXTENTS 1
    MAXEXTENTS 2147483645
    FREELISTS 1
    FREELIST GROUPS 1
    BUFFER_POOL DEFAULT
  )
;

CREATE TABLE RELEASE_LINKS
(
  RTAG_ID NUMBER NOT NULL,
  REF_RTAG_ID NUMBER NOT NULL
)
  TABLESPACE "USERS"
  LOGGING 
  PCTFREE 10
  PCTUSED 0
  INITRANS 1
  MAXTRANS 255
  STORAGE
  (
    INITIAL 64K
    MINEXTENTS 1
    MAXEXTENTS 2147483645
    FREELISTS 1
    FREELIST GROUPS 1
    BUFFER_POOL DEFAULT
  )
;

CREATE TABLE BUILD_ENV_DOCUMENTS
(
  BE_ID NUMBER NOT NULL,
  DOC_NUM VARCHAR2(255 BYTE) NOT NULL
)
  TABLESPACE "USERS"
  LOGGING 
  PCTFREE 10
  PCTUSED 0
  INITRANS 1
  MAXTRANS 255
  STORAGE
  (
    INITIAL 128K
    MINEXTENTS 1
    MAXEXTENTS 2147483645
    FREELISTS 1
    FREELIST GROUPS 1
    BUFFER_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"
  LOGGING 
  PCTFREE 10
  PCTUSED 0
  INITRANS 1
  MAXTRANS 255
  STORAGE
  (
    INITIAL 128K
    MINEXTENTS 1
    MAXEXTENTS 2147483645
    FREELISTS 1
    FREELIST GROUPS 1
    BUFFER_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"
  LOGGING 
  PCTFREE 10
  PCTUSED 40
  INITRANS 1
  MAXTRANS 255
  STORAGE
  (
    INITIAL 24160K
    NEXT 16K
    MINEXTENTS 1
    MAXEXTENTS 2147483645
    PCTINCREASE 0
    FREELISTS 1
    FREELIST GROUPS 1
    BUFFER_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"
  LOGGING 
  PCTFREE 10
  PCTUSED 0
  INITRANS 1
  MAXTRANS 255
  STORAGE
  (
    INITIAL 64K
    MINEXTENTS 1
    MAXEXTENTS 2147483645
    FREELISTS 1
    FREELIST GROUPS 1
    BUFFER_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"
  LOGGING 
  PCTFREE 10
  PCTUSED 40
  INITRANS 1
  MAXTRANS 255
  STORAGE
  (
    INITIAL 64K
    MINEXTENTS 1
    MAXEXTENTS 2147483645
    FREELISTS 1
    FREELIST GROUPS 1
    BUFFER_POOL DEFAULT
  )
;

CREATE TABLE PACKAGE_BUILD_INFO
(
  PV_ID NUMBER,
  BM_ID NUMBER,
  BSA_ID NUMBER
)
  TABLESPACE "USERS"
  LOGGING 
  PCTFREE 10
  PCTUSED 40
  INITRANS 1
  MAXTRANS 255
  STORAGE
  (
    INITIAL 64K
    MINEXTENTS 1
    MAXEXTENTS 2147483645
    FREELISTS 1
    FREELIST GROUPS 1
    BUFFER_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"
  LOGGING 
  PCTFREE 10
  PCTUSED 40
  INITRANS 1
  MAXTRANS 255
  STORAGE
  (
    INITIAL 160K
    NEXT 16K
    MINEXTENTS 1
    MAXEXTENTS 2147483645
    PCTINCREASE 0
    FREELISTS 1
    FREELIST GROUPS 1
    BUFFER_POOL DEFAULT
  )
;

CREATE TABLE MEMBERS_GROUP
(
  GROUP_EMAIL_ID NUMBER NOT NULL,
  USER_ID NUMBER
)
  TABLESPACE "USERS"
  LOGGING 
  PCTFREE 10
  PCTUSED 0
  INITRANS 1
  MAXTRANS 255
  STORAGE
  (
    INITIAL 64K
    MINEXTENTS 1
    MAXEXTENTS 2147483645
    FREELISTS 1
    FREELIST GROUPS 1
    BUFFER_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"
  LOGGING 
  PCTFREE 10
  PCTUSED 40
  INITRANS 1
  MAXTRANS 255
  STORAGE
  (
    INITIAL 80K
    NEXT 16K
    MINEXTENTS 1
    MAXEXTENTS 2147483645
    PCTINCREASE 0
    FREELISTS 1
    FREELIST GROUPS 1
    BUFFER_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"
  LOGGING 
  PCTFREE 10
  PCTUSED 0
  INITRANS 1
  MAXTRANS 255
  STORAGE
  (
    INITIAL 128K
    MINEXTENTS 1
    MAXEXTENTS 2147483645
    FREELISTS 1
    FREELIST GROUPS 1
    BUFFER_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"
  LOGGING 
  PCTFREE 10
  PCTUSED 40
  INITRANS 1
  MAXTRANS 255
  STORAGE
  (
    INITIAL 80K
    NEXT 16K
    MINEXTENTS 1
    MAXEXTENTS 2147483645
    PCTINCREASE 0
    FREELISTS 1
    FREELIST GROUPS 1
    BUFFER_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"
  LOGGING 
  PCTFREE 10
  PCTUSED 0
  INITRANS 1
  MAXTRANS 255
  STORAGE
  (
    INITIAL 128K
    MINEXTENTS 1
    MAXEXTENTS 2147483645
    FREELISTS 1
    FREELIST GROUPS 1
    BUFFER_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"
  LOGGING 
  PCTFREE 10
  PCTUSED 40
  INITRANS 1
  MAXTRANS 255
  STORAGE
  (
    INITIAL 64K
    MINEXTENTS 1
    MAXEXTENTS 2147483645
    FREELISTS 1
    FREELIST GROUPS 1
    BUFFER_POOL DEFAULT
  )
;

CREATE TABLE PROJECT_EXTENTIONS
(
  PROJ_ID NUMBER NOT NULL,
  EXT_NAME VARCHAR2(10 BYTE) NOT NULL
)
  TABLESPACE "SYSTEM"
  LOGGING 
  PCTFREE 10
  PCTUSED 40
  INITRANS 1
  MAXTRANS 255
  STORAGE
  (
    INITIAL 80K
    NEXT 16K
    MINEXTENTS 1
    MAXEXTENTS 2147483645
    PCTINCREASE 0
    FREELISTS 1
    FREELIST GROUPS 1
    BUFFER_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"
  LOGGING 
  PCTFREE 10
  PCTUSED 0
  INITRANS 1
  MAXTRANS 255
  STORAGE
  (
    INITIAL 128K
    MINEXTENTS 1
    MAXEXTENTS 2147483645
    FREELISTS 1
    FREELIST GROUPS 1
    BUFFER_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"
  LOGGING 
  PCTFREE 10
  PCTUSED 40
  INITRANS 1
  MAXTRANS 255
  STORAGE
  (
    INITIAL 32120K
    NEXT 16K
    MINEXTENTS 1
    MAXEXTENTS 2147483645
    PCTINCREASE 0
    FREELISTS 1
    FREELIST GROUPS 1
    BUFFER_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"
  LOGGING 
  PCTFREE 10
  PCTUSED 40
  INITRANS 1
  MAXTRANS 255
  STORAGE
  (
    INITIAL 80K
    NEXT 16K
    MINEXTENTS 1
    MAXEXTENTS 2147483645
    PCTINCREASE 0
    FREELISTS 1
    FREELIST GROUPS 1
    BUFFER_POOL DEFAULT
  )
;

CREATE TABLE VTREES_WORLD
(
  WORLD_ID NUMBER NOT NULL,
  VTREE_ID NUMBER NOT NULL
)
  TABLESPACE "USERS"
  LOGGING 
  PCTFREE 10
  PCTUSED 0
  INITRANS 1
  MAXTRANS 255
  STORAGE
  (
    INITIAL 128K
    MINEXTENTS 1
    MAXEXTENTS 2147483645
    FREELISTS 1
    FREELIST GROUPS 1
    BUFFER_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"
  LOGGING 
  PCTFREE 10
  PCTUSED 0
  INITRANS 1
  MAXTRANS 255
  STORAGE
  (
    INITIAL 128K
    MINEXTENTS 1
    MAXEXTENTS 2147483645
    FREELISTS 1
    FREELIST GROUPS 1
    BUFFER_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"
  LOGGING 
  PCTFREE 10
  PCTUSED 0
  INITRANS 1
  MAXTRANS 255
  STORAGE
  (
    INITIAL 64K
    MINEXTENTS 1
    MAXEXTENTS 2147483645
    FREELISTS 1
    FREELIST GROUPS 1
    BUFFER_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"
  LOGGING 
  PCTFREE 10
  PCTUSED 40
  INITRANS 1
  MAXTRANS 255
  STORAGE
  (
    INITIAL 1760K
    NEXT 16K
    MINEXTENTS 1
    MAXEXTENTS 2147483645
    PCTINCREASE 0
    FREELISTS 1
    FREELIST GROUPS 1
    BUFFER_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"
  LOGGING 
  PCTFREE 10
  PCTUSED 0
  INITRANS 1
  MAXTRANS 255
  STORAGE
  (
    INITIAL 128K
    MINEXTENTS 1
    MAXEXTENTS 2147483645
    FREELISTS 1
    FREELIST GROUPS 1
    BUFFER_POOL DEFAULT
  )
;

ALTER TABLE PLANNED
ADD CONSTRAINT UNIQUE_PV_RTAG UNIQUE
(
  PV_ID,
  RTAG_ID
)
 ENABLE
;

ALTER TABLE PACKAGE_BUILD_ENV
ADD CONSTRAINT FK_PKG_BUILD_E_REF_BUILD_ENV FOREIGN KEY
(
  BE_ID
)
REFERENCES BUILD_ENVIRONMENTS
(
BE_ID
) ENABLE
;

ALTER TABLE PACKAGE_BUILD_ENV
ADD CONSTRAINT FK_PKG_BUILD_E_REF_PKG_VERSION FOREIGN KEY
(
  PV_ID
)
REFERENCES PACKAGE_VERSIONS
(
PV_ID
) ENABLE
;

ALTER TABLE NOTIFICATION_HISTORY
ADD CONSTRAINT FK_NK_PV_ID FOREIGN KEY
(
  PV_ID
)
REFERENCES PACKAGE_VERSIONS
(
PV_ID
) DISABLE
;

ALTER TABLE NOTIFICATION_HISTORY
ADD CONSTRAINT FK_NH_RTAG_ID FOREIGN KEY
(
  RTAG_ID
)
REFERENCES RELEASE_TAGS
(
RTAG_ID
) DISABLE
;

ALTER TABLE IGNORE_WARNINGS
ADD CONSTRAINT FK_IGW_RTAG_ID FOREIGN KEY
(
  RTAG_ID
)
REFERENCES RELEASE_TAGS
(
RTAG_ID
) DISABLE
;

ALTER TABLE IGNORE_WARNINGS
ADD CONSTRAINT FK_IGW_DPV_ID FOREIGN KEY
(
  DPV_ID
)
REFERENCES PACKAGE_VERSIONS
(
PV_ID
) DISABLE
;

ALTER TABLE IGNORE_WARNINGS
ADD CONSTRAINT FK_IGW_PV_ID FOREIGN KEY
(
  PV_ID
)
REFERENCES PACKAGE_VERSIONS
(
PV_ID
) DISABLE
;

ALTER TABLE RELEASE_CONTENT
ADD FOREIGN KEY
(
  PKG_ID
)
REFERENCES PACKAGES
(
PKG_ID
) DISABLE
;

ALTER TABLE PACKAGE_DOCUMENTS
ADD CONSTRAINT FK_PKGDOCS_REF_PV FOREIGN KEY
(
  PV_ID
)
REFERENCES PACKAGE_VERSIONS
(
PV_ID
) ENABLE
;

ALTER TABLE BUILD_STANDARDS_ADDENDUM
ADD CONSTRAINT BUILD_STANDARDS_ADDENDUM_R01 FOREIGN KEY
(
  BS_ID
)
REFERENCES BUILD_STANDARDS
(
BS_ID
) ENABLE
;

ALTER TABLE UNIT_TESTS
ADD CONSTRAINT FK_UT_REF_TEST_TYPES FOREIGN KEY
(
  TEST_TYPES_FK
)
REFERENCES TEST_TYPES
(
TEST_TYPE_ID
) DISABLE
;

ALTER TABLE UNIT_TESTS
ADD CONSTRAINT FK_UT_REF_PV_ID FOREIGN KEY
(
  PV_ID
)
REFERENCES PACKAGE_VERSIONS
(
PV_ID
) DISABLE
;

ALTER TABLE PACKAGE_PROCESSES
ADD CONSTRAINT PACKAGE_PROCESSES_R02 FOREIGN KEY
(
  PROC_ID
)
REFERENCES PROCESSES
(
PROC_ID
) ENABLE
;

ALTER TABLE PACKAGE_PROCESSES
ADD CONSTRAINT PACKAGE_PROCESSES_R01 FOREIGN KEY
(
  PV_ID
)
REFERENCES PACKAGE_VERSIONS
(
PV_ID
) ENABLE
;

ALTER TABLE RELEASE_COMPONENTS
ADD CONSTRAINT FK_RCONT_PV_ID FOREIGN KEY
(
  PV_ID
)
REFERENCES PACKAGE_VERSIONS
(
PV_ID
) ENABLE
;

ALTER TABLE PACKAGE_PATCHES
ADD CONSTRAINT FK_PP_PATCH_ID FOREIGN KEY
(
  PATCH_ID
)
REFERENCES PACKAGE_VERSIONS
(
PV_ID
) DISABLE
;

ALTER TABLE PACKAGE_PATCHES
ADD CONSTRAINT FK_PP_PV_ID FOREIGN KEY
(
  PV_ID
)
REFERENCES PACKAGE_VERSIONS
(
PV_ID
) ENABLE
;

ALTER TABLE BUILD_ORDER
ADD CONSTRAINT FK_BO_PV_ID FOREIGN KEY
(
  PV_ID
)
REFERENCES PACKAGE_VERSIONS
(
PV_ID
) DISABLE
;

ALTER TABLE BUILD_ORDER
ADD CONSTRAINT FK_BO_RTAG_ID FOREIGN KEY
(
  RTAG_ID
)
REFERENCES RELEASE_TAGS
(
RTAG_ID
) DISABLE
;

ALTER TABLE PLANNED
ADD CONSTRAINT REFRELEASE_TAGS53 FOREIGN KEY
(
  RTAG_ID
)
REFERENCES RELEASE_TAGS
(
RTAG_ID
) ENABLE
;

ALTER TABLE PLANNED
ADD CONSTRAINT REFPACKAGE_VERSIONS54 FOREIGN KEY
(
  PV_ID
)
REFERENCES PACKAGE_VERSIONS
(
PV_ID
) ENABLE
;

ALTER TABLE PLANNED
ADD CONSTRAINT REFVIEWS55 FOREIGN KEY
(
  VIEW_ID
)
REFERENCES VIEWS
(
VIEW_ID
) ENABLE
;

ALTER TABLE PROJECT_ACTION_LOG
ADD FOREIGN KEY
(
  RTAG_ID
)
REFERENCES RELEASE_TAGS
(
RTAG_ID
) DISABLE
;

ALTER TABLE PROJECT_ACTION_LOG
ADD CONSTRAINT REFACTION_TYPE63 FOREIGN KEY
(
  ACTTYPE_ID
)
REFERENCES ACTION_TYPE
(
ACTTYPE_ID
) ENABLE
;

ALTER TABLE PACKAGE_INTEREST
ADD FOREIGN KEY
(
  PROJ_ID
)
REFERENCES PROJECTS
(
PROJ_ID
) ENABLE
;

ALTER TABLE AUTOBUILD_FAILURE
ADD FOREIGN KEY
(
  VIEW_ID
)
REFERENCES VIEWS
(
VIEW_ID
) ENABLE
;

ALTER TABLE AUTOBUILD_FAILURE
ADD FOREIGN KEY
(
  PROJ_ID
)
REFERENCES PROJECTS
(
PROJ_ID
) ENABLE
;

ALTER TABLE JIRA_ISSUES
ADD CONSTRAINT FK_JIRA_PV_ID FOREIGN KEY
(
  PV_ID
)
REFERENCES PACKAGE_VERSIONS
(
PV_ID
) ENABLE
;

ALTER TABLE WORK_IN_PROGRESS
ADD CONSTRAINT REFPACKAGE_VERSIONS51 FOREIGN KEY
(
  PV_ID
)
REFERENCES PACKAGE_VERSIONS
(
PV_ID
) ENABLE
;

ALTER TABLE WORK_IN_PROGRESS
ADD CONSTRAINT REFRELEASE_TAGS50 FOREIGN KEY
(
  RTAG_ID
)
REFERENCES RELEASE_TAGS
(
RTAG_ID
) ENABLE
;

ALTER TABLE WORK_IN_PROGRESS
ADD CONSTRAINT REFVIEWS52 FOREIGN KEY
(
  VIEW_ID
)
REFERENCES VIEWS
(
VIEW_ID
) ENABLE
;

ALTER TABLE ADVISORY_RIPPLES
ADD FOREIGN KEY
(
  PV_ID
)
REFERENCES PACKAGE_VERSIONS
(
PV_ID
) ENABLE
;

ALTER TABLE ADVISORY_RIPPLES
ADD FOREIGN KEY
(
  RTAG_ID
)
REFERENCES RELEASE_TAGS
(
RTAG_ID
) ENABLE
;

ALTER TABLE RELEASE_LINKS
ADD CONSTRAINT REFRELEASE_TAGS58 FOREIGN KEY
(
  REF_RTAG_ID
)
REFERENCES RELEASE_TAGS
(
RTAG_ID
) ENABLE
;

ALTER TABLE RELEASE_LINKS
ADD CONSTRAINT REFRELEASE_TAGS56 FOREIGN KEY
(
  RTAG_ID
)
REFERENCES RELEASE_TAGS
(
RTAG_ID
) ENABLE
;

ALTER TABLE PRODUCT_COMPONENTS
ADD CONSTRAINT REFPACKAGE_VERSIONS49 FOREIGN KEY
(
  PV_ID
)
REFERENCES PACKAGE_VERSIONS
(
PV_ID
) ENABLE
;

ALTER TABLE MEMBERS_GROUP
ADD FOREIGN KEY
(
  GROUP_EMAIL_ID
)
REFERENCES AUTOBUILD_FAILURE
(
GROUP_EMAIL_ID
) ENABLE
;

ALTER TABLE ACTION_LOG
ADD CONSTRAINT REFACTION_TYPE48 FOREIGN KEY
(
  ACTTYPE_ID
)
REFERENCES ACTION_TYPE
(
ACTTYPE_ID
) ENABLE
;

ALTER TABLE ADDITIONAL_NOTES
ADD CONSTRAINT FK_ADDITION_REF_PV_ID FOREIGN KEY
(
  PV_ID
)
REFERENCES PACKAGE_VERSIONS
(
PV_ID
) DISABLE
;

ALTER TABLE VTREES_WORLD
ADD CONSTRAINT FK_VW_REF_VTREE_ID FOREIGN KEY
(
  VTREE_ID
)
REFERENCES VTREES
(
VTREE_ID
) ENABLE
;

ALTER TABLE VTREES_WORLD
ADD CONSTRAINT FK_VW_REF_WORLD_ID FOREIGN KEY
(
  WORLD_ID
)
REFERENCES WORLDS
(
WORLD_ID
) ENABLE
;

ALTER TABLE CODE_REVIEWS
ADD CONSTRAINT FK_CODE_REV_REF_PV_ID FOREIGN KEY
(
  PV_ID
)
REFERENCES PACKAGE_VERSIONS
(
PV_ID
) DISABLE
;

ALTER TABLE CQ_ISSUES
ADD CONSTRAINT FK_CQ_PV_ID FOREIGN KEY
(
  PV_ID
)
REFERENCES PACKAGE_VERSIONS
(
PV_ID
) DISABLE
;

ALTER TABLE MICROSOFTDTPROPERTIES
ADD CONSTRAINT MICROSOFT_NN_PROPERTY CHECK
("PROPERTY" IS NOT NULL)
 ENABLE
;

ALTER TABLE MICROSOFTDTPROPERTIES
ADD CONSTRAINT MICROSOFT_NN_ID CHECK
("ID" IS NOT NULL)
 ENABLE
;

ALTER TABLE MICROSOFTDTPROPERTIES
ADD 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"
  LOGGING 
  PCTFREE 10
  INITRANS 2
  MAXTRANS 255
  STORAGE
  (
    INITIAL 128K
    MINEXTENTS 1
    MAXEXTENTS 2147483645
    FREELISTS 1
    FREELIST GROUPS 1
    BUFFER_POOL DEFAULT
  )
;

CREATE INDEX INX_TES_PKGEXT ON TEMP_ENV_STATES (PKG_ID ASC, V_EXT ASC)  TABLESPACE "USERS"
  NOLOGGING 
  PCTFREE 10
  INITRANS 2
  MAXTRANS 255
  STORAGE
  (
    INITIAL 128K
    MINEXTENTS 1
    MAXEXTENTS 2147483645
    FREELISTS 1
    FREELIST GROUPS 1
    BUFFER_POOL DEFAULT
  )
;

CREATE INDEX INX_TES_PV ON TEMP_ENV_STATES (PV_ID ASC)  TABLESPACE "USERS"
  NOLOGGING 
  PCTFREE 10
  INITRANS 2
  MAXTRANS 255
  STORAGE
  (
    INITIAL 128K
    MINEXTENTS 1
    MAXEXTENTS 2147483645
    FREELISTS 1
    FREELIST GROUPS 1
    BUFFER_POOL DEFAULT
  )
;

CREATE INDEX INX_TES_TES_STATE ON TEMP_ENV_STATES (TES_STATE ASC)  TABLESPACE "USERS"
  NOLOGGING 
  PCTFREE 10
  INITRANS 2
  MAXTRANS 255
  STORAGE
  (
    INITIAL 128K
    MINEXTENTS 1
    MAXEXTENTS 2147483645
    FREELISTS 1
    FREELIST GROUPS 1
    BUFFER_POOL DEFAULT
  )
;

CREATE INDEX INX_TTB_LEVEL_NUM ON TEMP_TREE_BROWSE (LEVEL_NUM ASC)  TABLESPACE "USERS"
  LOGGING 
  PCTFREE 10
  INITRANS 2
  MAXTRANS 255
  STORAGE
  (
    INITIAL 64K
    MINEXTENTS 1
    MAXEXTENTS 2147483645
    FREELISTS 1
    FREELIST GROUPS 1
    BUFFER_POOL DEFAULT
  )
;

CREATE INDEX INX_TTB_PKG ON TEMP_TREE_BROWSE (PKG_ID ASC, V_EXT ASC)  TABLESPACE "USERS"
  LOGGING 
  PCTFREE 10
  INITRANS 2
  MAXTRANS 255
  STORAGE
  (
    INITIAL 64K
    MINEXTENTS 1
    MAXEXTENTS 2147483645
    FREELISTS 1
    FREELIST GROUPS 1
    BUFFER_POOL DEFAULT
  )
;

CREATE INDEX INX_TTB_SESSION ON TEMP_TREE_BROWSE (SESSION_NUM ASC)  TABLESPACE "USERS"
  LOGGING 
  PCTFREE 10
  INITRANS 2
  MAXTRANS 255
  STORAGE
  (
    INITIAL 64K
    MINEXTENTS 1
    MAXEXTENTS 2147483645
    FREELISTS 1
    FREELIST GROUPS 1
    BUFFER_POOL DEFAULT
  )
;

CREATE INDEX INX_IS_PATCH_IGNORE ON IGNORE_WARNINGS (IS_PATCH_IGNORE ASC)  TABLESPACE "USERS"
  LOGGING 
  PCTFREE 10
  INITRANS 2
  MAXTRANS 255
  STORAGE
  (
    INITIAL 128K
    MINEXTENTS 1
    MAXEXTENTS 2147483645
    FREELISTS 1
    FREELIST GROUPS 1
    BUFFER_POOL DEFAULT
  )
;

CREATE UNIQUE INDEX UNQ_IGW ON IGNORE_WARNINGS (RTAG_ID ASC, PV_ID ASC, DPV_ID ASC)  TABLESPACE "USERS"
  LOGGING 
  PCTFREE 10
  INITRANS 2
  MAXTRANS 255
  STORAGE
  (
    INITIAL 128K
    MINEXTENTS 1
    MAXEXTENTS 2147483645
    FREELISTS 1
    FREELIST GROUPS 1
    BUFFER_POOL DEFAULT
  )
;

CREATE INDEX INX_RC_BASE_VIEW_ID ON RELEASE_CONTENT (BASE_VIEW_ID ASC)  TABLESPACE "USERS"
  NOLOGGING 
  PCTFREE 10
  INITRANS 2
  MAXTRANS 255
  STORAGE
  (
    INITIAL 128K
    MINEXTENTS 1
    MAXEXTENTS 2147483645
    FREELISTS 1
    FREELIST GROUPS 1
    BUFFER_POOL DEFAULT
  )
;

CREATE INDEX INX_RC_PKG_STATE ON RELEASE_CONTENT (PKG_STATE ASC)  TABLESPACE "USERS"
  NOLOGGING 
  PCTFREE 10
  INITRANS 2
  MAXTRANS 255
  STORAGE
  (
    INITIAL 128K
    MINEXTENTS 1
    MAXEXTENTS 2147483645
    FREELISTS 1
    FREELIST GROUPS 1
    BUFFER_POOL DEFAULT
  )
;

CREATE UNIQUE INDEX UNQ_RELEASE_CONTENTS ON RELEASE_CONTENT (RTAG_ID ASC, PV_ID ASC)  TABLESPACE "USERS"
  NOLOGGING 
  PCTFREE 10
  INITRANS 2
  MAXTRANS 255
  STORAGE
  (
    INITIAL 128K
    MINEXTENTS 1
    MAXEXTENTS 2147483645
    FREELISTS 1
    FREELIST GROUPS 1
    BUFFER_POOL DEFAULT
  )
;

CREATE INDEX INX_PV_ID_PKGDOC ON PACKAGE_DOCUMENTS (PV_ID ASC)  TABLESPACE "USERS"
  LOGGING 
  PCTFREE 10
  INITRANS 2
  MAXTRANS 255
  STORAGE
  (
    INITIAL 128K
    MINEXTENTS 1
    MAXEXTENTS 2147483645
    FREELISTS 1
    FREELIST GROUPS 1
    BUFFER_POOL DEFAULT
  )
;

CREATE INDEX INX_PV_ID_TEST_ID_PKGDOC ON PACKAGE_DOCUMENTS (PV_ID ASC, TEST_ID ASC)  TABLESPACE "USERS"
  LOGGING 
  PCTFREE 10
  INITRANS 2
  MAXTRANS 255
  STORAGE
  (
    INITIAL 128K
    MINEXTENTS 1
    MAXEXTENTS 2147483645
    FREELISTS 1
    FREELIST GROUPS 1
    BUFFER_POOL DEFAULT
  )
;

CREATE UNIQUE INDEX UNQ_PKG_DOC ON PACKAGE_DOCUMENTS (PV_ID ASC, DOC_NUM ASC)  TABLESPACE "USERS"
  LOGGING 
  PCTFREE 10
  INITRANS 2
  MAXTRANS 255
  STORAGE
  (
    INITIAL 128K
    MINEXTENTS 1
    MAXEXTENTS 2147483645
    FREELISTS 1
    FREELIST GROUPS 1
    BUFFER_POOL DEFAULT
  )
;

CREATE INDEX FK_PROJ_ID_RELEASE_TAGS ON RELEASE_TAGS (PROJ_ID ASC)  TABLESPACE "USERS"
  LOGGING 
  PCTFREE 10
  INITRANS 2
  MAXTRANS 255
  STORAGE
  (
    INITIAL 64K
    MINEXTENTS 1
    MAXEXTENTS 2147483645
    FREELISTS 1
    FREELIST GROUPS 1
    BUFFER_POOL DEFAULT
  )
;

CREATE INDEX INX_UT_COMPLETED ON UNIT_TESTS (COMPLETION_DATE ASC, COMPLETED_BY ASC)  TABLESPACE "USERS"
  LOGGING 
  PCTFREE 10
  INITRANS 2
  MAXTRANS 255
  STORAGE
  (
    INITIAL 128K
    MINEXTENTS 1
    MAXEXTENTS 2147483645
    FREELISTS 1
    FREELIST GROUPS 1
    BUFFER_POOL DEFAULT
  )
;

CREATE INDEX INX_UT_PV_TEST_TYPE ON UNIT_TESTS (PV_ID ASC, TEST_TYPES_FK ASC)  TABLESPACE "USERS"
  LOGGING 
  PCTFREE 10
  INITRANS 2
  MAXTRANS 255
  STORAGE
  (
    INITIAL 128K
    MINEXTENTS 1
    MAXEXTENTS 2147483645
    FREELISTS 1
    FREELIST GROUPS 1
    BUFFER_POOL DEFAULT
  )
;

CREATE UNIQUE INDEX UNQ_UT_PART1 ON UNIT_TESTS (PV_ID ASC, TEST_ID ASC)  TABLESPACE "USERS"
  LOGGING 
  PCTFREE 10
  INITRANS 2
  MAXTRANS 255
  STORAGE
  (
    INITIAL 128K
    MINEXTENTS 1
    MAXEXTENTS 2147483645
    FREELISTS 1
    FREELIST GROUPS 1
    BUFFER_POOL DEFAULT
  )
;

CREATE UNIQUE INDEX UNQ_BUILD_ENV ON BUILD_ENVIRONMENTS (BE_NAME ASC)  TABLESPACE "USERS"
  LOGGING 
  PCTFREE 10
  INITRANS 2
  MAXTRANS 255
  STORAGE
  (
    INITIAL 128K
    MINEXTENTS 1
    MAXEXTENTS 2147483645
    FREELISTS 1
    FREELIST GROUPS 1
    BUFFER_POOL DEFAULT
  )
;

CREATE INDEX INX_FILE_NAME ON RELEASE_COMPONENTS (UPPER("FILE_NAME") ASC)  TABLESPACE "USERS"
  LOGGING 
  PCTFREE 10
  INITRANS 2
  MAXTRANS 255
  STORAGE
  (
    INITIAL 64K
    MINEXTENTS 1
    MAXEXTENTS 2147483645
    FREELISTS 1
    FREELIST GROUPS 1
    BUFFER_POOL DEFAULT
  )
;

CREATE INDEX INX_RCOMP_PV_ID ON RELEASE_COMPONENTS (PV_ID ASC)  TABLESPACE "USERS"
  LOGGING 
  PCTFREE 10
  INITRANS 2
  MAXTRANS 255
  STORAGE
  (
    INITIAL 64K
    MINEXTENTS 1
    MAXEXTENTS 2147483645
    FREELISTS 1
    FREELIST GROUPS 1
    BUFFER_POOL DEFAULT
  )
;

CREATE UNIQUE INDEX UNQ_REL_COMPONENTS ON RELEASE_COMPONENTS (PV_ID ASC, FILE_NAME ASC, FILE_PATH ASC)  TABLESPACE "USERS"
  LOGGING 
  PCTFREE 10
  INITRANS 2
  MAXTRANS 255
  STORAGE
  (
    INITIAL 128K
    MINEXTENTS 1
    MAXEXTENTS 2147483645
    FREELISTS 1
    FREELIST GROUPS 1
    BUFFER_POOL DEFAULT
  )
;

CREATE INDEX INX_PP_PV_ID ON PACKAGE_PATCHES (PV_ID ASC)  TABLESPACE "USERS"
  LOGGING 
  PCTFREE 10
  INITRANS 2
  MAXTRANS 255
  STORAGE
  (
    INITIAL 128K
    MINEXTENTS 1
    MAXEXTENTS 2147483645
    FREELISTS 1
    FREELIST GROUPS 1
    BUFFER_POOL DEFAULT
  )
;

CREATE UNIQUE INDEX UNQ_PACKAGE_PATCHES ON PACKAGE_PATCHES (PATCH_ID ASC)  TABLESPACE "USERS"
  NOLOGGING 
  PCTFREE 10
  INITRANS 2
  MAXTRANS 255
  STORAGE
  (
    INITIAL 128K
    MINEXTENTS 1
    MAXEXTENTS 2147483645
    FREELISTS 1
    FREELIST GROUPS 1
    BUFFER_POOL DEFAULT
  )
;

CREATE UNIQUE INDEX UNQ_BUILD_ORDER ON BUILD_ORDER (RTAG_ID ASC, PV_ID ASC, STEP_NUM ASC)  TABLESPACE "USERS"
  LOGGING 
  PCTFREE 10
  INITRANS 2
  MAXTRANS 255
  STORAGE
  (
    INITIAL 64K
    MINEXTENTS 1
    MAXEXTENTS 2147483645
    FREELISTS 1
    FREELIST GROUPS 1
    BUFFER_POOL DEFAULT
  )
;

CREATE INDEX UNQ_PLANNED ON PLANNED (RTAG_ID ASC, PV_ID ASC)  TABLESPACE "USERS"
  LOGGING 
  PCTFREE 10
  INITRANS 2
  MAXTRANS 255
  STORAGE
  (
    INITIAL 64K
    MINEXTENTS 1
    MAXEXTENTS 2147483645
    FREELISTS 1
    FREELIST GROUPS 1
    BUFFER_POOL DEFAULT
  )
;

CREATE INDEX INX_PROJ_ACTION_DATE_TIME ON PROJECT_ACTION_LOG (ACTION_DATETIME ASC)  TABLESPACE "USERS"
  LOGGING 
  PCTFREE 10
  INITRANS 2
  MAXTRANS 255
  STORAGE
  (
    INITIAL 64K
    MINEXTENTS 1
    MAXEXTENTS 2147483645
    FREELISTS 1
    FREELIST GROUPS 1
    BUFFER_POOL DEFAULT
  )
;

CREATE INDEX INX_PROJ_ACTION_LOG_PROJ ON PROJECT_ACTION_LOG (PROJ_ID ASC)  TABLESPACE "USERS"
  LOGGING 
  PCTFREE 10
  INITRANS 2
  MAXTRANS 255
  STORAGE
  (
    INITIAL 64K
    MINEXTENTS 1
    MAXEXTENTS 2147483645
    FREELISTS 1
    FREELIST GROUPS 1
    BUFFER_POOL DEFAULT
  )
;

CREATE UNIQUE INDEX UNQ_PLATFORM ON PLATFORMS (NAME ASC)  TABLESPACE "USERS"
  LOGGING 
  PCTFREE 10
  INITRANS 2
  MAXTRANS 255
  STORAGE
  (
    INITIAL 64K
    MINEXTENTS 1
    MAXEXTENTS 2147483645
    FREELISTS 1
    FREELIST GROUPS 1
    BUFFER_POOL DEFAULT
  )
;

CREATE UNIQUE INDEX UNQ_VIEW_SETTINGS ON VIEW_SETTINGS (USER_ID ASC, VIEW_ID ASC)  TABLESPACE "SYSTEM"
  LOGGING 
  PCTFREE 10
  INITRANS 2
  MAXTRANS 255
  STORAGE
  (
    INITIAL 80K
    NEXT 16K
    MINEXTENTS 1
    MAXEXTENTS 2147483645
    PCTINCREASE 0
    FREELISTS 1
    FREELIST GROUPS 1
    BUFFER_POOL DEFAULT
  )
;

CREATE UNIQUE INDEX UNQ_VIEW_DEF ON VIEW_DEF (VIEW_ID ASC, PKG_ID ASC)  TABLESPACE "SYSTEM"
  LOGGING 
  PCTFREE 10
  INITRANS 2
  MAXTRANS 255
  STORAGE
  (
    INITIAL 160K
    NEXT 16K
    MINEXTENTS 1
    MAXEXTENTS 2147483645
    PCTINCREASE 0
    FREELISTS 1
    FREELIST GROUPS 1
    BUFFER_POOL DEFAULT
  )
;

CREATE UNIQUE INDEX UNQ_VTREES ON VTREES (PROJ_ID ASC, VTREE_NAME ASC)  TABLESPACE "SYSTEM"
  LOGGING 
  PCTFREE 10
  INITRANS 2
  MAXTRANS 255
  STORAGE
  (
    INITIAL 80K
    NEXT 16K
    MINEXTENTS 1
    MAXEXTENTS 2147483645
    PCTINCREASE 0
    FREELISTS 1
    FREELIST GROUPS 1
    BUFFER_POOL DEFAULT
  )
;

CREATE UNIQUE INDEX UNQ_WORK_IN_PROGRESS ON WORK_IN_PROGRESS (RTAG_ID ASC, PV_ID ASC)  TABLESPACE "USERS"
  LOGGING 
  PCTFREE 10
  INITRANS 2
  MAXTRANS 255
  STORAGE
  (
    INITIAL 64K
    MINEXTENTS 1
    MAXEXTENTS 2147483645
    FREELISTS 1
    FREELIST GROUPS 1
    BUFFER_POOL DEFAULT
  )
;

CREATE INDEX INX_RL_REFRTAG_ID ON RELEASE_LINKS (REF_RTAG_ID ASC)  TABLESPACE "USERS"
  LOGGING 
  PCTFREE 10
  INITRANS 2
  MAXTRANS 255
  STORAGE
  (
    INITIAL 64K
    MINEXTENTS 1
    MAXEXTENTS 2147483645
    FREELISTS 1
    FREELIST GROUPS 1
    BUFFER_POOL DEFAULT
  )
;

CREATE INDEX INX_RL_RTAG_ID ON RELEASE_LINKS (RTAG_ID ASC)  TABLESPACE "USERS"
  LOGGING 
  PCTFREE 10
  INITRANS 2
  MAXTRANS 255
  STORAGE
  (
    INITIAL 64K
    MINEXTENTS 1
    MAXEXTENTS 2147483645
    FREELISTS 1
    FREELIST GROUPS 1
    BUFFER_POOL DEFAULT
  )
;

CREATE UNIQUE INDEX UNQ_RELEASE_LINKS ON RELEASE_LINKS (RTAG_ID ASC, REF_RTAG_ID ASC)  TABLESPACE "USERS"
  LOGGING 
  PCTFREE 10
  INITRANS 2
  MAXTRANS 255
  STORAGE
  (
    INITIAL 64K
    MINEXTENTS 1
    MAXEXTENTS 2147483645
    FREELISTS 1
    FREELIST GROUPS 1
    BUFFER_POOL DEFAULT
  )
;

CREATE UNIQUE INDEX UNQ_BUILD_ENV_DOCS ON BUILD_ENV_DOCUMENTS (BE_ID ASC, DOC_NUM ASC)  TABLESPACE "USERS"
  LOGGING 
  PCTFREE 10
  INITRANS 2
  MAXTRANS 255
  STORAGE
  (
    INITIAL 128K
    MINEXTENTS 1
    MAXEXTENTS 2147483645
    FREELISTS 1
    FREELIST GROUPS 1
    BUFFER_POOL DEFAULT
  )
;

CREATE UNIQUE INDEX UNQ_TEST_TYPE_NAME ON TEST_TYPES (TEST_TYPE_NAME ASC)  TABLESPACE "USERS"
  LOGGING 
  PCTFREE 10
  INITRANS 2
  MAXTRANS 255
  STORAGE
  (
    INITIAL 128K
    MINEXTENTS 1
    MAXEXTENTS 2147483645
    FREELISTS 1
    FREELIST GROUPS 1
    BUFFER_POOL DEFAULT
  )
;

CREATE INDEX INX_PACKAGE_DEPS_DPKG_ID ON PACKAGE_DEPENDENCIES (DPKG_ID ASC)  TABLESPACE "USERS"
  NOLOGGING 
  PCTFREE 10
  INITRANS 2
  MAXTRANS 255
  STORAGE
  (
    INITIAL 256K
    MINEXTENTS 1
    MAXEXTENTS 2147483645
    FREELISTS 1
    FREELIST GROUPS 1
    BUFFER_POOL DEFAULT
  )
;

CREATE INDEX INX_PACKAGE_DEPS_DPV_ID ON PACKAGE_DEPENDENCIES (DPV_ID ASC)  TABLESPACE "USERS"
  NOLOGGING 
  PCTFREE 10
  INITRANS 2
  MAXTRANS 255
  STORAGE
  (
    INITIAL 384K
    MINEXTENTS 1
    MAXEXTENTS 2147483645
    FREELISTS 1
    FREELIST GROUPS 1
    BUFFER_POOL DEFAULT
  )
;

CREATE INDEX INX_PACKAGE_DEPS_PKG_ID ON PACKAGE_DEPENDENCIES (PKG_ID ASC)  TABLESPACE "USERS"
  NOLOGGING 
  PCTFREE 10
  INITRANS 2
  MAXTRANS 255
  STORAGE
  (
    INITIAL 256K
    MINEXTENTS 1
    MAXEXTENTS 2147483645
    FREELISTS 1
    FREELIST GROUPS 1
    BUFFER_POOL DEFAULT
  )
;

CREATE INDEX INX_PACKAGE_DEPS_PV_ID ON PACKAGE_DEPENDENCIES (PV_ID ASC)  TABLESPACE "USERS"
  NOLOGGING 
  PCTFREE 10
  INITRANS 2
  MAXTRANS 255
  STORAGE
  (
    INITIAL 384K
    MINEXTENTS 1
    MAXEXTENTS 2147483645
    FREELISTS 1
    FREELIST GROUPS 1
    BUFFER_POOL DEFAULT
  )
;

CREATE UNIQUE INDEX UNQ_PACKAGE_DEPENDENCIES ON PACKAGE_DEPENDENCIES (PV_ID ASC, DPV_ID ASC)  TABLESPACE "SYSTEM"
  LOGGING 
  PCTFREE 10
  INITRANS 2
  MAXTRANS 255
  STORAGE
  (
    INITIAL 23760K
    NEXT 16K
    MINEXTENTS 1
    MAXEXTENTS 2147483645
    PCTINCREASE 0
    FREELISTS 1
    FREELIST GROUPS 1
    BUFFER_POOL DEFAULT
  )
;

CREATE INDEX INX_PCOMP_FK_PV_ID ON PRODUCT_COMPONENTS (PV_ID ASC)  TABLESPACE "USERS"
  LOGGING 
  PCTFREE 10
  INITRANS 2
  MAXTRANS 255
  STORAGE
  (
    INITIAL 64K
    MINEXTENTS 1
    MAXEXTENTS 2147483645
    FREELISTS 1
    FREELIST GROUPS 1
    BUFFER_POOL DEFAULT
  )
;

CREATE INDEX INX_PC_FILE_NAME ON PRODUCT_COMPONENTS (UPPER("FILE_NAME") ASC)  TABLESPACE "USERS"
  LOGGING 
  PCTFREE 10
  INITRANS 2
  MAXTRANS 255
  STORAGE
  (
    INITIAL 64K
    MINEXTENTS 1
    MAXEXTENTS 2147483645
    FREELISTS 1
    FREELIST GROUPS 1
    BUFFER_POOL DEFAULT
  )
;

CREATE UNIQUE INDEX UNQ_PACKAGES ON PACKAGES (PKG_NAME ASC)  TABLESPACE "SYSTEM"
  LOGGING 
  PCTFREE 10
  INITRANS 2
  MAXTRANS 255
  STORAGE
  (
    INITIAL 160K
    NEXT 16K
    MINEXTENTS 1
    MAXEXTENTS 2147483645
    PCTINCREASE 0
    FREELISTS 1
    FREELIST GROUPS 1
    BUFFER_POOL DEFAULT
  )
;

CREATE UNIQUE INDEX UNQ_PROJECTS ON PROJECTS (PROJ_NAME ASC)  TABLESPACE "SYSTEM"
  LOGGING 
  PCTFREE 10
  INITRANS 2
  MAXTRANS 255
  STORAGE
  (
    INITIAL 80K
    NEXT 16K
    MINEXTENTS 1
    MAXEXTENTS 2147483645
    PCTINCREASE 0
    FREELISTS 1
    FREELIST GROUPS 1
    BUFFER_POOL DEFAULT
  )
;

CREATE UNIQUE INDEX UNQ_VIEWS ON VIEWS (VIEW_NAME ASC, OWNER_ID ASC)  TABLESPACE "SYSTEM"
  LOGGING 
  PCTFREE 10
  INITRANS 2
  MAXTRANS 255
  STORAGE
  (
    INITIAL 80K
    NEXT 16K
    MINEXTENTS 1
    MAXEXTENTS 2147483645
    PCTINCREASE 0
    FREELISTS 1
    FREELIST GROUPS 1
    BUFFER_POOL DEFAULT
  )
;

CREATE INDEX INX_ACTION_DATE_TIME ON ACTION_LOG (ACTION_DATETIME ASC)  TABLESPACE "USERS"
  LOGGING 
  PCTFREE 10
  INITRANS 2
  MAXTRANS 255
  STORAGE
  (
    INITIAL 128K
    MINEXTENTS 1
    MAXEXTENTS 2147483645
    FREELISTS 1
    FREELIST GROUPS 1
    BUFFER_POOL DEFAULT
  )
;

CREATE INDEX INX_ACTION_LOG_PV_ID ON ACTION_LOG (PV_ID ASC)  TABLESPACE "USERS"
  LOGGING 
  PCTFREE 10
  INITRANS 2
  MAXTRANS 255
  STORAGE
  (
    INITIAL 128K
    MINEXTENTS 1
    MAXEXTENTS 2147483645
    FREELISTS 1
    FREELIST GROUPS 1
    BUFFER_POOL DEFAULT
  )
;

CREATE UNIQUE INDEX UNQ_PROJECT_EXT ON PROJECT_EXTENTIONS (PROJ_ID ASC, EXT_NAME ASC)  TABLESPACE "SYSTEM"
  LOGGING 
  PCTFREE 10
  INITRANS 2
  MAXTRANS 255
  STORAGE
  (
    INITIAL 80K
    NEXT 16K
    MINEXTENTS 1
    MAXEXTENTS 2147483645
    PCTINCREASE 0
    FREELISTS 1
    FREELIST GROUPS 1
    BUFFER_POOL DEFAULT
  )
;

CREATE UNIQUE INDEX UNQ_AN_PART1 ON ADDITIONAL_NOTES (PV_ID ASC, NOTE_ID ASC)  TABLESPACE "USERS"
  LOGGING 
  PCTFREE 10
  INITRANS 2
  MAXTRANS 255
  STORAGE
  (
    INITIAL 128K
    MINEXTENTS 1
    MAXEXTENTS 2147483645
    FREELISTS 1
    FREELIST GROUPS 1
    BUFFER_POOL DEFAULT
  )
;

CREATE UNIQUE INDEX UNQ_AN_PART2 ON ADDITIONAL_NOTES (PV_ID ASC, NOTE_TITLE ASC)  TABLESPACE "USERS"
  LOGGING 
  PCTFREE 10
  INITRANS 2
  MAXTRANS 255
  STORAGE
  (
    INITIAL 128K
    MINEXTENTS 1
    MAXEXTENTS 2147483645
    FREELISTS 1
    FREELIST GROUPS 1
    BUFFER_POOL DEFAULT
  )
;

CREATE INDEX INX_PACKAGES_PKG_NAME ON PACKAGE_VERSIONS (PKG_ID ASC)  TABLESPACE "USERS"
  NOLOGGING 
  PCTFREE 10
  INITRANS 2
  MAXTRANS 255
  STORAGE
  (
    INITIAL 128K
    MINEXTENTS 1
    MAXEXTENTS 2147483645
    FREELISTS 1
    FREELIST GROUPS 1
    BUFFER_POOL DEFAULT
  )
;

CREATE INDEX INX_PACKAGES_PKG_VERSION ON PACKAGE_VERSIONS (PKG_VERSION ASC)  TABLESPACE "USERS"
  NOLOGGING 
  PCTFREE 10
  INITRANS 2
  MAXTRANS 255
  STORAGE
  (
    INITIAL 128K
    MINEXTENTS 1
    MAXEXTENTS 2147483645
    FREELISTS 1
    FREELIST GROUPS 1
    BUFFER_POOL DEFAULT
  )
;

CREATE INDEX INX_PACKAGES_V_EXT ON PACKAGE_VERSIONS (V_EXT ASC)  TABLESPACE "USERS"
  NOLOGGING 
  PCTFREE 10
  INITRANS 2
  MAXTRANS 255
  STORAGE
  (
    INITIAL 128K
    MINEXTENTS 1
    MAXEXTENTS 2147483645
    FREELISTS 1
    FREELIST GROUPS 1
    BUFFER_POOL DEFAULT
  )
;

CREATE INDEX INX_PACKAGES_V_MM ON PACKAGE_VERSIONS (V_MM ASC)  TABLESPACE "USERS"
  NOLOGGING 
  PCTFREE 10
  INITRANS 2
  MAXTRANS 255
  STORAGE
  (
    INITIAL 128K
    MINEXTENTS 1
    MAXEXTENTS 2147483645
    FREELISTS 1
    FREELIST GROUPS 1
    BUFFER_POOL DEFAULT
  )
;

CREATE INDEX INX_PACKAGES_V_NMM ON PACKAGE_VERSIONS (V_NMM ASC)  TABLESPACE "USERS"
  NOLOGGING 
  PCTFREE 10
  INITRANS 2
  MAXTRANS 255
  STORAGE
  (
    INITIAL 128K
    MINEXTENTS 1
    MAXEXTENTS 2147483645
    FREELISTS 1
    FREELIST GROUPS 1
    BUFFER_POOL DEFAULT
  )
;

CREATE INDEX INX_PV_OWNER_ID ON PACKAGE_VERSIONS (OWNER_ID ASC)  TABLESPACE "USERS"
  LOGGING 
  PCTFREE 10
  INITRANS 2
  MAXTRANS 255
  STORAGE
  (
    INITIAL 128K
    MINEXTENTS 1
    MAXEXTENTS 2147483645
    FREELISTS 1
    FREELIST GROUPS 1
    BUFFER_POOL DEFAULT
  )
;

CREATE UNIQUE INDEX UNQ_PACKAGE_VERSIONS ON PACKAGE_VERSIONS (PKG_ID ASC, PKG_VERSION ASC)  TABLESPACE "SYSTEM"
  LOGGING 
  PCTFREE 10
  INITRANS 2
  MAXTRANS 255
  STORAGE
  (
    INITIAL 2960K
    NEXT 16K
    MINEXTENTS 1
    MAXEXTENTS 2147483645
    PCTINCREASE 0
    FREELISTS 1
    FREELIST GROUPS 1
    BUFFER_POOL DEFAULT
  )
;

CREATE INDEX INX_MSGBOARD ON MESSAGE_BOARD (EXPIRY_DATE ASC, DUE_DATE ASC)  TABLESPACE "SYSTEM"
  LOGGING 
  PCTFREE 10
  INITRANS 2
  MAXTRANS 255
  STORAGE
  (
    INITIAL 80K
    NEXT 16K
    MINEXTENTS 1
    MAXEXTENTS 2147483645
    PCTINCREASE 0
    FREELISTS 1
    FREELIST GROUPS 1
    BUFFER_POOL DEFAULT
  )
;

CREATE UNIQUE INDEX UNQ_VTREE_WORLD ON VTREES_WORLD (WORLD_ID ASC, VTREE_ID ASC)  TABLESPACE "USERS"
  LOGGING 
  PCTFREE 10
  INITRANS 2
  MAXTRANS 255
  STORAGE
  (
    INITIAL 128K
    MINEXTENTS 1
    MAXEXTENTS 2147483645
    FREELISTS 1
    FREELIST GROUPS 1
    BUFFER_POOL DEFAULT
  )
;

CREATE UNIQUE INDEX INX_CODE_REVIEW ON CODE_REVIEWS (PV_ID ASC)  TABLESPACE "USERS"
  LOGGING 
  PCTFREE 10
  INITRANS 2
  MAXTRANS 255
  STORAGE
  (
    INITIAL 128K
    MINEXTENTS 1
    MAXEXTENTS 2147483645
    FREELISTS 1
    FREELIST GROUPS 1
    BUFFER_POOL DEFAULT
  )
;

CREATE UNIQUE INDEX UNQ_ABTN_DEF ON DEF_ACTION_BUTTONS (ABTN_NAME ASC)  TABLESPACE "USERS"
  LOGGING 
  PCTFREE 10
  INITRANS 2
  MAXTRANS 255
  STORAGE
  (
    INITIAL 64K
    MINEXTENTS 1
    MAXEXTENTS 2147483645
    FREELISTS 1
    FREELIST GROUPS 1
    BUFFER_POOL DEFAULT
  )
;

CREATE UNIQUE INDEX UNQ_CQ ON CQ_ISSUES (PV_ID ASC, ISS_ID ASC, ISS_DB ASC)  TABLESPACE "USERS"
  LOGGING 
  PCTFREE 10
  INITRANS 2
  MAXTRANS 255
  STORAGE
  (
    INITIAL 128K
    MINEXTENTS 1
    MAXEXTENTS 2147483645
    FREELISTS 1
    FREELIST GROUPS 1
    BUFFER_POOL DEFAULT
  )
;

CREATE UNIQUE INDEX UNQ_VALIDATION_RULES ON VALIDATION_RULES (FIELD_NAME ASC)  TABLESPACE "USERS"
  LOGGING 
  PCTFREE 10
  INITRANS 2
  MAXTRANS 255
  STORAGE
  (
    INITIAL 128K
    MINEXTENTS 1
    MAXEXTENTS 2147483645
    FREELISTS 1
    FREELIST GROUPS 1
    BUFFER_POOL DEFAULT
  )
;

CREATE OR REPLACE PROCEDURE ADD_VIEW_MEMBERS (nGroupId IN NUMBER, sUserList IN VARCHAR2) IS

/******************************************************************************
   NAME:       ADD_VIEW_MEMBERS
   PURPOSE:    

   REVISIONS:
   Ver        Date        Author           Description
   ---------  ----------  ---------------  ------------------------------------
   1.0        11/04/2006          1. Created this procedure.

   NOTES:

   Automatically available Auto Replace Keywords:
      Object Name:     ADD_VIEW_MEMBERS
      Sysdate:         11/04/2006
      Date and Time:   11/04/2006, 9:52:58 AM, and 11/04/2006 9:52:58 AM
      Username:         (set in TOAD Options, Procedure Editor)
      Table Name:       (set in the "New PL/SQL Object" dialog)

******************************************************************************/
CURSOR user_cur IS
 SELECT u.USER_ID
   FROM USERS u
  WHERE 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;


BEGIN

  OPEN user_cur;
  FETCH user_cur INTO user_rec;
  
  WHILE user_cur%FOUND
  LOOP
    INSERT 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_states
    SELECT DISTINCT
           NNsession_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 message
      FROM package_dependencies dep,
           package_versions dpv,
           package_versions pv,
           release_content rel,
           temp_env_states tes
     WHERE rel.pv_id = dep.pv_id
       AND rel.rtag_id = NNrtag_id
       AND dep.pv_id = pv.pv_id
       AND dep.dpv_id = dpv.pv_id
       AND dpv.pkg_id = tes.pkg_id
       AND NVL(dpv.v_ext,'|LINK_A_NULL|') = NVL(tes.v_ext,'|LINK_A_NULL|')
       AND tes.session_num = NNsession_num
       AND tes.level_num = prevIteration
       AND (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_id
             FROM package_dependencies pd,
                  release_content rc
            WHERE pd.pv_id = rc.pv_id
              AND 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 )
           MINUS
           SELECT igw.pv_id, igw.dpv_id
             FROM ignore_warnings igw
            WHERE 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();

BEGIN
 oPvIdCollector := IN_LIST_NUMBER ( sPvIdList );
    oNotPvIdCollector := IN_LIST_NUMBER ( sNotPvIdList );

    -- Delete From Release Content
    DELETE
      FROM RELEASE_CONTENT
  WHERE rtag_id = nRTagId
    AND 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_states
        SELECT DISTINCT
               NNsession_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 message
          FROM
               (
               /* 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_mm
               FROM release_content rel, package_versions rpv
               WHERE 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_dpv
                  FROM package_dependencies dep
                 WHERE 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_dpv
                  FROM ignore_warnings igw
                 WHERE rtag_id = NNrtag_id
                ) err,
               package_versions errpkg,
               package_versions errpv
         WHERE err.err_dpv = errpv.pv_id
           AND errpv.pkg_id = frc.pkg_id(+)
           AND NVL(errpv.v_ext, '|LINK_A_NULL|') = frc.v_ext(+)
           AND err.pv_id = errpkg.pv_id;




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 details
 SELECT pkg.PKG_NAME, pv.V_EXT INTO sPkgName, sVext
   FROM PACKAGE_VERSIONS pv,
       PACKAGES pkg
  WHERE pv.PKG_ID = pkg.PKG_ID
    AND pv.PV_ID = nPvId;


 -- Generate Label for automated build
 RETURN UPPER( sPkgName ||'.'|| nPvId || sVext ||'.WIP' );

END GET_AUTOMATED_LABEL;
/

CREATE OR REPLACE PROCEDURE CLONED_PROCESSES
IS

proc_id NUMBER;
/******************************************************************************
   NAME:       DELETE_DO_NOT_RIPPLE
   PURPOSE:    TO DELETE ENTRIES FROM THE DO_NOT_RIPPLE TABLE WHEN A PACKAGE
               IS RELEASED

   REVISIONS:
   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_RIPPLE
      Sysdate:         21/04/2006
      Date and Time:   21/04/2006, 3:21:55 PM, and 21/04/2006 3:21:55 PM
      Username:         (set in TOAD Options, Procedure Editor)
      Table Name:       (set in the "New PL/SQL Object" dialog)

******************************************************************************/
   CURSOR ripple_cur
   IS
      select distinct pkg_health_tag, cmd_interface, pkg_owner, is_interface from deployment_manager.processes_config;

   ripple_rec   ripple_cur%ROWTYPE;
BEGIN
   OPEN ripple_cur;

   FETCH ripple_cur
    INTO ripple_rec;

   WHILE ripple_cur%FOUND
   LOOP
   
   select 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_cur
       INTO ripple_rec;
   END LOOP;
END CLONED_PROCESSES;
/

CREATE OR REPLACE PACKAGE pk_package
IS
/*
------------------------------
||  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 IS
        SELECT 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%NOTFOUND
    THEN
  /* 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_INTEREST
   PURPOSE:    

   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_INTEREST
      Sysdate:         12/05/2006
      Date and Time:   12/05/2006, 9:11:12 AM, and 12/05/2006 9:11:12 AM
      Username:         (set in TOAD Options, Procedure Editor)
      Table Name:       (set in the "New PL/SQL Object" dialog)

******************************************************************************/
BEGIN

  --- Insert into PACKAGE_INTEREST TABLE
  INSERT 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)THEN
  RETURN 0;
 END IF;
 /*-------------------------------------------------------*/
 
  
  
  
 
 
 /*-- First Check. See if package is used through release reference --*/
 SELECT COUNT(rc.PV_ID) INTO RowCount
   FROM (
     SELECT rl.REF_RTAG_ID
     FROM RELEASE_LINKS rl
    WHERE rl.RTAG_ID = nRtagId
     ) rl,
   RELEASE_CONTENT rc
   WHERE rc.RTAG_ID = rl.REF_RTAG_ID
     AND rc.PV_ID = nPvId;   
 
 
 -- Decide if package can be edited  
 IF RowCount > 0 THEN
  -- Package is referenced from other release, hence cannot be edited
  RETURN 0; 
  
 ELSE
 
  -- Check is only done for releases in restrictive mode
  SELECT rt.OFFICIAL INTO cReleaseMode
    FROM RELEASE_TAGS rt
   WHERE rt.RTAG_ID = nRtagId;
  
  IF cReleaseMode = 'N'OR cReleaseMode = 'R' THEN
   -- Do not do any firther checking,
   -- Package is editable here
   RETURN 1;
  END IF; 
  
 END IF;
 
 
 
 
 /*-- Further checking --*/
 -- Get proj_id
 SELECT rt.PROJ_ID  INTO  ProjId
   FROM RELEASE_TAGS rt
  WHERE rt.RTAG_ID = nRtagId;
    
 
  
 BEGIN

  -- Get v_ext
  SELECT pv.V_EXT  INTO  Vext
    FROM PACKAGE_VERSIONS pv
   WHERE pv.PV_ID = nPvId;  

  EXCEPTION
      WHEN NO_DATA_FOUND THEN
          Vext := NULL;

 END;  
  
 
 --Temp Hack for Step Project
 IF ProjId != 281 THEN
  -- Find if package can be edited in this project
   SELECT COUNT(pe.EXT_NAME) INTO RowCount
    FROM PROJECT_EXTENTIONS pe
   WHERE pe.PROJ_ID != ProjId  
     AND pe.EXT_NAME = VExt;
 END IF;  
   
   
 -- Decide if package can be edited  
 IF RowCount > 0 THEN
  -- Package extension is found in other projects, hece NOT EDITABLE
  RETURN 0;
 ELSE
  RETURN 1; 
 END IF;

 
END CAN_EDIT_PKG_IN_PROJECT;
/

CREATE OR REPLACE PACKAGE pk_archive
IS
/*
------------------------------
||  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 counter
    maxIterations NUMBER := 50;    -- Maximum number of iterations allowed.
                                    -- This will prevent infinite loops if cyrcular dependencies are found
    sessionNum NUMBER;
BEGIN
    SELECT SEQ_session_num.nextval INTO sessionNum FROM DUAL;


    -- Redo Patch Ignore warnings
    Ignore_Dependency_Warnings ( NNrtag_id, NULL, NULL, TRUE );


    Level_One_Conflicts ( NNrtag_id, sessionNum );
 
    LOOP
        Level_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 Available
    Check_New_Patches ( NNrtag_id );


 /* Circular Dependency Flag */
    /*
 IF iteration > maxIterations
 THEN
  UPDATE release_tags SET
         circular_dependency = 'Y'
   WHERE rtag_id = NNrtag_id;
 ELSE
  UPDATE release_tags SET
         circular_dependency = NULL
   WHERE 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 Order
     SELECT pp.INSTALL_ORDER INTO currInstallOrder
       FROM PACKAGE_PATCHES pp
      WHERE pp.PV_ID = nPvId
        AND pp.PATCH_ID = nPatchId;


  IF currInstallOrder > nToInstallOrder
  THEN

  FromInstallOrder := nToInstallOrder;

     -- Shift others Up
  UPDATE PACKAGE_PATCHES pp SET
      pp.INSTALL_ORDER = pp.INSTALL_ORDER + 1
   WHERE pp.PV_ID = nPvId
           AND pp.PATCH_ID != nPatchId
     AND pp.INSTALL_ORDER BETWEEN  FromInstallOrder  AND  currInstallOrder - 1;


  ELSIF currInstallOrder < nToInstallOrder
  THEN

  FromInstallOrder := currInstallOrder + 1;

  -- Shift others Down
        UPDATE PACKAGE_PATCHES pp SET
      pp.INSTALL_ORDER = pp.INSTALL_ORDER - 1
   WHERE pp.PV_ID = nPvId
           AND pp.PATCH_ID != nPatchId
     AND pp.INSTALL_ORDER BETWEEN  FromInstallOrder  AND  nToInstallOrder;

  END IF;


  -- Move Patch to new install order
     UPDATE PACKAGE_PATCHES pp SET
      pp.INSTALL_ORDER = nToInstallOrder
   WHERE pp.PV_ID = nPvId
     AND 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 NULL
    THEN
  -- Auto Update Dependencies --
  INSERT INTO PACKAGE_DEPENDENCIES
      SELECT 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_order
        FROM 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.dlocked
             FROM RELEASE_CONTENT rel, PACKAGE_VERSIONS rpv
             WHERE rel.pv_id = rpv.pv_id AND rtag_id = nRtag_id
             ) frc
       WHERE dep.pv_id = nFROMpv_id
         AND dep.dpv_id = pv.pv_id
         AND pv.pkg_id = frc.pkg_id(+)
         AND NVL(pv.v_ext, '|LINK_A_NULL|') = frc.v_ext(+);

 ELSE
  -- Clone Dependencies --
  INSERT INTO PACKAGE_DEPENDENCIES
      SELECT nTOpv_id         AS pv_id,
             dep.dpv_id,
             nTOpkg_id        AS pkg_id,
             dep.dpkg_id,
             dep.build_type,
       dep.display_order
        FROM PACKAGE_DEPENDENCIES dep
       WHERE dep.pv_id = nFROMpv_id;

 END IF;

----------------------------------------- Clone Issues -------------------------------------------------------
 IF enumISSUES_STATE_IMPORTED IS NULL
 THEN
        /* 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, notes
            FROM CQ_ISSUES
              WHERE 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, notes
           FROM CQ_ISSUES
             WHERE pv_id = nFROMpv_id
               AND 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_user
     FROM 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_USER
           FROM ADDITIONAL_NOTES an
          WHERE 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_TEST
           FROM UNIT_TESTS ut
          WHERE ut.PV_ID = nFROMpv_id
            AND 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_SUMMARY
    FROM UNIT_TESTS ut
   WHERE ut.PV_ID = nFROMpv_id
     AND 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_LATEST
           FROM PACKAGE_DOCUMENTS pd
          WHERE 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_TYPE
   FROM PACKAGE_BUILD_ENV pkgbe
  WHERE 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_ID
   FROM PACKAGE_PROCESSES pp
   WHERE pp.PV_ID = nFROMpv_id;     
---------------------------------------------------------------------------------------------------------------

 /* LOG ACTION */
    SELECT pv.PKG_VERSION INTO FromVersion
      FROM PACKAGE_VERSIONS pv
     WHERE 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_COMPONENTS
  DELETE FROM RELEASE_COMPONENTS
  WHERE 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 ) THEN
      RAISE_APPLICATION_ERROR (-20000, 'Please contact the person in charge of Release Manager now.' );
   END IF

   /*-------------------------------------------------------*/
   UPDATE release_content rc
      SET rc.pkg_state = 5                          -- enumPKG_STATE_NEW_PATCH
    WHERE rc.pv_id IN (
             SELECT prod.pv_id
               FROM (SELECT   pp.pv_id AS orig_parent_id,
                              COUNT (*) AS num_of_patches
                         FROM release_content rc, package_patches pp
                        WHERE rc.pv_id = pp.pv_id AND rc.rtag_id = nrtagid
                     GROUP BY pp.pv_id) orig,
                    (SELECT   prodpp.pv_id, pp.pv_id AS parent_id,
                              COUNT (*) AS num_of_patches
                         FROM release_content rc,
                              package_patches pp,
                              package_dependencies dep,
                              package_versions pv,
                              package_patches prodpp
                        WHERE rc.pv_id = pp.pv_id
                          AND rc.rtag_id = nrtagid
                          AND pp.patch_id = dep.dpv_id
                          AND dep.pv_id = pv.pv_id
                          AND pv.is_patch = 'Y'
                          AND pv.dlocked = 'Y'
                          AND prodpp.patch_id = dep.pv_id
                     GROUP BY prodpp.pv_id, pp.pv_id) prod,
                    release_content rc
              WHERE orig.orig_parent_id = prod.pv_id
                AND orig.num_of_patches != prod.num_of_patches
                AND rc.rtag_id = nrtagid
                AND rc.pv_id = prod.pv_id
                AND rc.pkg_state = 0
             UNION
             SELECT   prodpp.pv_id
                 FROM release_content rc,
                      package_patches pp,
                      package_dependencies dep,
                      package_versions pv,
                      package_patches prodpp
                WHERE rc.pv_id = pp.pv_id
                  AND rc.rtag_id = nrtagid
                  AND pp.patch_id = dep.dpv_id
                  AND dep.pv_id = pv.pv_id
                  AND pv.is_patch = 'Y'
                  AND pv.dlocked = 'Y'
                  AND prodpp.patch_id = dep.pv_id
             GROUP BY prodpp.pv_id, pp.pv_id
             MINUS
             SELECT   pp.pv_id
                 FROM release_content rc, package_patches pp
                WHERE rc.pv_id = pp.pv_id AND rc.rtag_id = nrtagid
             GROUP 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 IS
        SELECT rc.RTAG_ID, rc.BASE_VIEW_ID, pv.pv_id
    FROM release_content rc,
        package_versions pv,
         package_versions opv
   WHERE rc.pv_id = pv.pv_id
     AND opv.pkg_id = pv.pkg_id
     AND NVL(opv.v_ext, '|LINK_A_NULL|') = NVL(pv.v_ext, '|LINK_A_NULL|')
     AND opv.pv_id = nPv_id
     AND 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;

BEGIN

 OPEN sync_rtags_cur;
 FETCH sync_rtags_cur INTO sync_rtags_rec;

 WHILE sync_rtags_cur%FOUND
 LOOP
  DELETE FROM RELEASE_CONTENT
   WHERE rtag_id = sync_rtags_rec.rtag_id
        AND 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 dependency
    SELECT v_ext, pkg_id, is_patch INTO SSV_EXT, NNdpkg_id, DepIsPatch
      FROM PACKAGE_VERSIONS
     WHERE pv_id = retPV_ID;

    -- get pkg_id of parent package
    SELECT pkg_id, is_patch INTO NNpkg_id, PvIsPatch
      FROM PACKAGE_VERSIONS
     WHERE pv_id = NNpv_id;


 IF ( (PvIsPatch IS NULL) AND (DepIsPatch IS NULL) ) OR
     ( (PvIsPatch IS NOT NULL) )THEN

     IF NNdelete_old_dependency = 1 THEN
         /* Used for CUSTOM dependency add/edit */

         --- Remove old dependency ---
            IF (PvIsPatch IS NULL) THEN
             -- Do it for Packages
          DELETE FROM PACKAGE_DEPENDENCIES
           WHERE ( pv_id, dpv_id, pkg_id, dpkg_id, build_type, display_order ) IN
              (
              SELECT dep.*
                FROM PACKAGE_DEPENDENCIES dep,
                     PACKAGE_VERSIONS dpv,
                     PACKAGE_VERSIONS pv
               WHERE dep.dpv_id = dpv.pv_id
                 AND dep.pv_id = NNpv_id
                 AND pv.pv_id = retPV_ID
                 AND dpv.pkg_id = pv.pkg_id
                 AND NVL(dpv.v_ext, '|LINK_A_NULL|') = NVL(SSV_EXT, '|LINK_A_NULL|')
              );
            ELSE
             -- Do it for Patches
                DELETE FROM PACKAGE_DEPENDENCIES
           WHERE ( pv_id, dpv_id, pkg_id, dpkg_id, build_type, display_order ) IN
              (
              SELECT dep.*
                FROM PACKAGE_DEPENDENCIES dep
               WHERE dep.dpv_id = retPV_ID
                 AND 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 FK
    SELECT act.ACTTYPE_ID INTO ActionTypeId
      FROM ACTION_TYPE act
     WHERE 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
  ) IS

  crlf 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);

EXCEPTION
  WHEN UTL_SMTP.INVALID_OPERATION THEN
    dbms_output.put_line(' Invalid Operation in SMTP transaction.');
  WHEN UTL_SMTP.TRANSIENT_ERROR THEN
    dbms_output.put_line(' Temporary problems with sending email - try again later.');
  WHEN UTL_SMTP.PERMANENT_ERROR THEN
    dbms_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);

BEGIN

 IF NOT sInList IS NULL
 THEN
  LOOP
         EXIT WHEN in_list IS NULL;
         pos := INSTR ( in_list, cSeparator );
   val := LTRIM ( RTRIM ( SUBSTR ( in_list, 1, pos-1 ) ) );
   
   IF (NOT val IS NULL) THEN
          cItemCollection.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 IS
        SELECT pkg.pkg_id
          FROM packages pkg
         WHERE 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%NOTFOUND
    THEN
        -- 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 );

    ELSE
        retPKG_ID := packages_rec.pkg_id;

    END IF;


    CLOSE packages_cur;

END New_Package_Name;
/

CREATE OR REPLACE PROCEDURE UPDATE_PROCESSES
IS

proc_id NUMBER;
/******************************************************************************
   NAME:       DELETE_DO_NOT_RIPPLE
   PURPOSE:    TO DELETE ENTRIES FROM THE DO_NOT_RIPPLE TABLE WHEN A PACKAGE
               IS RELEASED

   REVISIONS:
   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_RIPPLE
      Sysdate:         21/04/2006
      Date and Time:   21/04/2006, 3:21:55 PM, and 21/04/2006 3:21:55 PM
      Username:         (set in TOAD Options, Procedure Editor)
      Table Name:       (set in the "New PL/SQL Object" dialog)

******************************************************************************/
   CURSOR ripple_cur
   IS
      select proc_id,  proc_name, run_as, pkg_owner, is_interface from processes;

   ripple_rec   ripple_cur%ROWTYPE;
BEGIN
   OPEN ripple_cur;

   FETCH ripple_cur
    INTO ripple_rec;

   WHILE ripple_cur%FOUND
   LOOP
   

update deployment_manager.processes_config 
set proc_id = ripple_rec.proc_id
where pkg_health_tag = ripple_rec.proc_name
and cmd_interface IS NULL
and pkg_owner IS NULL
and is_interface IS NULL; 
   

      FETCH ripple_cur
       INTO 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 counter
    iteration   NUMBER := 1;                  -- Maximum number of iterations allowed.
    maxIterations  NUMBER := 50;          -- This will prevent infinite loops if cyrcular dependencies are found
 UP_THE_TREE  CONSTANT NUMBER DEFAULT 1;
 DOWN_THE_TREE  CONSTANT NUMBER DEFAULT -1;
    sessionNum   NUMBER;
 levelNum  NUMBER;
 
BEGIN
    SELECT 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_ext
    FROM release_content rc,
        package_versions pv
   WHERE rc.rtag_id = nRtag_id
     AND rc.pv_id = pv.pv_id
   MINUS
  SELECT sessionNum AS SESSION_NUM, 
      iteration AS LEVEL_NUM,
      UP_THE_TREE AS DIRECTION,
      dep.pv_id, pv.pkg_id, pv.v_ext
    FROM package_dependencies dep,
        package_versions pv
   WHERE 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;
    LOOP
        
  INSERT INTO TEMP_TREE_BROWSE (SESSION_NUM, LEVEL_NUM, DIRECTION, PV_ID, PKG_ID, V_EXT )
   SELECT DISTINCT 
          sessionNum AS SESSION_NUM,
          iteration AS LEVEL_NUM,
       UP_THE_TREE AS DIRECTION, 
       rdep.pv_id, rdep.pkg_id, rdep.v_ext
     FROM (  
           SELECT dep.pv_id, pv.pkg_id, pv.v_ext, dep.dpv_id, dpv.pkg_id AS dpkg_id, dpv.v_ext AS dv_ext
             FROM package_dependencies dep,
                  release_content rc,
         package_versions pv,
         package_versions dpv
            WHERE dep.pv_id = rc.pv_id
              AND rc.rtag_id = nRtag_id
        AND dep.pv_id = pv.pv_id
        AND dep.dpv_id = dpv.pv_id
     ) rdep,
     temp_tree_browse ttb
    WHERE 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 = sessionNum
      AND ttb.LEVEL_NUM = iteration - 1 
   MINUS
   /* Packages with all depencencies NOT matched */  
   SELECT DISTINCT 
          sessionNum AS SESSION_NUM,
          iteration AS LEVEL_NUM, 
       UP_THE_TREE AS DIRECTION,
       rdep.pv_id, rdep.pkg_id, rdep.v_ext
     FROM (  
           SELECT dep.pv_id, pv.pkg_id, pv.v_ext, dep.dpv_id, dpv.pkg_id AS dpkg_id, dpv.v_ext AS dv_ext
             FROM package_dependencies dep,
                  release_content rc,
         package_versions pv,
         package_versions dpv
            WHERE dep.pv_id = rc.pv_id
              AND rc.rtag_id = nRtag_id
        AND dep.pv_id = pv.pv_id
        AND dep.dpv_id = dpv.pv_id
     ) rdep,
     (
      SELECT tb.*
        FROM temp_tree_browse tb
       WHERE tb.SESSION_NUM = sessionNum
     ) ttb
    WHERE 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 THEN
           iteration := iteration + 1;
  END IF;
        EXIT WHEN (rowCnt < 1) OR (iteration > maxIterations);
    END LOOP;
 
 /*---------------------------------------------------------------------------------------------------------------------*/
 
 /*     Check for unresolved dependencies
 ||  
 */
 /* UNRESOLVED */
  SELECT COUNT(*) INTO rowCnt
    FROM (
    SELECT pv.pv_id, pv.pkg_id, pv.v_ext
     FROM release_content rc,
         package_versions pv
    WHERE rc.rtag_id = nRtag_id
      AND rc.pv_id = pv.pv_id    
   MINUS
   SELECT ttb.pv_id, ttb.pkg_id, ttb.v_ext
     FROM temp_tree_browse ttb
    WHERE ttb.session_num = sessionNum
   );
    
  
     
  IF rowCnt > 0 
  THEN
    /*     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_ext
     FROM (     
     /* Packages no one depends on ( Top level packages )*/
     ( 
     /* All parents*/ 
     SELECT pv.pkg_id, pv.v_ext
             FROM package_dependencies dep,
                  release_content rc,
         package_versions pv
            WHERE dep.pv_id = rc.pv_id
              AND rc.rtag_id = nRtag_id
        AND dep.pv_id = pv.pv_id
      MINUS
      /* All children */
     SELECT dpv.pkg_id, dpv.v_ext
             FROM package_dependencies dep,
                  release_content rc,
         package_versions dpv
            WHERE dep.pv_id = rc.pv_id
              AND rc.rtag_id = nRtag_id
        AND dep.dpv_id = dpv.pv_id
      ) 
      MINUS
     /* Packages with resolved dependencies from UP THE TREE */ 
     SELECT ttb.pkg_id, ttb.v_ext
       FROM temp_tree_browse ttb
      WHERE ttb.session_num = sessionNum
      ) tpkg,
      package_versions pv,
      release_content rc
   WHERE rc.rtag_id = nRtag_id
     AND rc.pv_id = pv.pv_id
     AND tpkg.pkg_id = pv.pkg_id
     AND 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;  
   LOOP   
      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_ext
       FROM (     
       /* All Unresolved */
       (
       SELECT pv.pkg_id, pv.v_ext
         FROM release_content rc,
             package_versions pv
        WHERE rc.rtag_id = nRtag_id
          AND rc.pv_id = pv.pv_id    
       MINUS
       SELECT ttb.pkg_id, ttb.v_ext
         FROM temp_tree_browse ttb
        WHERE ttb.session_num = sessionNum
       )
        MINUS
       (  
        /* Children of Unresolved */  
       SELECT dpv.pkg_id, dpv.V_EXT
         FROM (
         SELECT pv.pv_id, pv.pkg_id, pv.v_ext
           FROM release_content rc,
               package_versions pv
          WHERE rc.rtag_id = nRtag_id
            AND rc.pv_id = pv.pv_id    
         MINUS
         SELECT ttb.pv_id, ttb.pkg_id, ttb.v_ext
           FROM temp_tree_browse ttb
          WHERE ttb.session_num = sessionNum
           ) unr,
           package_dependencies dep,
           package_versions dpv
        WHERE unr.pv_id = dep.pv_id
          AND dep.dpv_id = dpv.pv_id
        )  
        ) tpkg,
        package_versions pv,
        release_content rc
     WHERE rc.rtag_id = nRtag_id
       AND rc.pv_id = pv.pv_id
       AND tpkg.pkg_id = pv.pkg_id
       AND NVL(tpkg.v_ext,'|LINK_A_NULL|') = NVL(pv.v_ext,'|LINK_A_NULL|');
       
            rowCnt := SQL%ROWCOUNT;
         IF rowCnt > 0 THEN
            iteration := 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_ID
    FROM temp_tree_browse ttb
   WHERE ttb.session_num = sessionNum
     AND ttb.direction = UP_THE_TREE; 
     
 /*Get last step_num */
 SELECT MAX(ttb.level_num) + 1 INTO levelNum
 FROM temp_tree_browse ttb WHERE ttb.session_num = sessionNum AND ttb.DIRECTION = UP_THE_TREE;
 
 /* UNRESOLVED */
  SELECT COUNT(*) INTO rowCnt
    FROM (
    SELECT pv.pv_id, pv.pkg_id, pv.v_ext
     FROM release_content rc,
         package_versions pv
    WHERE rc.rtag_id = nRtag_id
      AND rc.pv_id = pv.pv_id    
   MINUS
   SELECT ttb.pv_id, ttb.pkg_id, ttb.v_ext
     FROM temp_tree_browse ttb
    WHERE ttb.session_num = sessionNum
   );
 
   
 IF rowCnt > 0
 THEN
  /* 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 UNRESOLVED
      FROM (
      SELECT pv.pv_id, pv.pkg_id, pv.v_ext
       FROM release_content rc,
           package_versions pv
      WHERE rc.rtag_id = nRtag_id
        AND rc.pv_id = pv.pv_id    
     MINUS
     SELECT ttb.pv_id, ttb.pkg_id, ttb.v_ext
       FROM temp_tree_browse ttb
      WHERE 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_ID
    FROM temp_tree_browse ttb
   WHERE ttb.session_num = sessionNum
     AND 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 IS
 SELECT pp.PV_ID,
     pp.PATCH_ID,
        ROWNUM AS NEW_INSTALL_ORDER
   FROM PACKAGE_PATCHES pp
  WHERE pp.PV_ID = nPvId
 ORDER BY pp.INSTALL_ORDER;
recPatch curPatch%ROWTYPE;

BEGIN

  -- Delete Patch
     DELETE
       FROM PACKAGE_PATCHES pp
      WHERE pp.PV_ID = nPvId
        AND pp.PATCH_ID = nPatchId;


 -- Redo Install Order
    OPEN curPatch;
 FETCH curPatch INTO recPatch;

 WHILE curPatch%FOUND
 LOOP

  UPDATE PACKAGE_PATCHES pp SET
   pp.INSTALL_ORDER = recPatch.NEW_INSTALL_ORDER
  WHERE pp.PV_ID = nPvId
    AND pp.PATCH_ID = recPatch.PATCH_ID;

  FETCH curPatch INTO recPatch;
 END LOOP;

 CLOSE curPatch;




    /* LOG ACTION */
    SELECT pv.PKG_VERSION INTO PatchVersion
      FROM PACKAGE_VERSIONS pv
     WHERE 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_COMPONENTS
  INSERT 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_SYSDATETIME
RETURN DATE
IS
/* ---------------------------------------------------------------------------
    Version: 3.0.0
   --------------------------------------------------------------------------- */
BEGIN

    RETURN 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 IS
        SELECT note_id
          FROM ADDITIONAL_NOTES
         WHERE pv_id = pnPv_id
           AND note_title = psNote_title;
    an_duplicate_rec an_duplicate_cur%ROWTYPE;

BEGIN
 outErrCode := -1;  -- Set default return error code to ERROR state

 OPEN an_duplicate_cur;
    FETCH an_duplicate_cur INTO an_duplicate_rec;

    IF an_duplicate_cur%NOTFOUND
    THEN
  /* 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 SUCCESS
 END 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 ) IS

GroupId NUMBER;               
           
          

/******************************************************************************
   NAME:       UPDATE_VIEW
   PURPOSE:    

   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_VIEW
      Sysdate:         10/04/2006
      Date and Time:   10/04/2006, 1:55:19 PM, and 10/04/2006 1:55:19 PM
      Username:         (set in TOAD Options, Procedure Editor)
      Table Name:       (set in the "New PL/SQL Object" dialog)

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

BEGIN
  SELECT GROUP_EMAIL_ID into GroupId
  FROM AUTOBUILD_FAILURE
  WHERE PROJ_ID = nProjId
  AND VIEW_ID = nViewId;
  
  DELETE FROM MEMBERS_GROUP
  WHERE GROUP_EMAIL_ID = GroupId
  AND 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 IS
        SELECT pv.*, pkg.pkg_name
          FROM package_versions pv,
         packages pkg
         WHERE pv.pv_id = NNparent_id
     AND pv.pkg_id = pkg.pkg_id;
    parent_rec parent_cur%ROWTYPE;

    CURSOR patch_cur IS
        SELECT pv.*, pg.pkg_name
          FROM package_versions pv,
         packages pg
         WHERE pv.pkg_id = parPkg_id
     AND pv.pkg_version = SSpatch_version
     AND pv.pkg_id = pg.pkg_id;
    patch_rec patch_cur%ROWTYPE;

 CURSOR releases_cur IS
        SELECT rc.pv_id
    FROM release_content rc
   WHERE rc.pv_id = patch_rec.pv_id;
    releases_rec releases_cur%ROWTYPE;


BEGIN

 -- Get Last Install Order
    SELECT Count(*) INTO LastInstallOrder
   FROM PACKAGE_PATCHES pp
  WHERE pp.PV_ID = NNparent_id;


    -- Get parent details
 OPEN parent_cur;
    FETCH parent_cur INTO parent_rec;
 parPkg_id := parent_rec.pkg_id;


 -- Find if patch exists in database
    OPEN patch_cur;
    FETCH patch_cur INTO patch_rec;


    -- Parent must be official
    IF parent_rec.dlocked = 'Y' THEN

     IF patch_cur%NOTFOUND
     THEN
         isPatchDlocked := '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_ORDER
           FROM package_versions pv
       WHERE pv.pv_id = patchPv_id
         AND 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 );


     ELSE

      patchPv_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%NOTFOUND
      THEN
    -- This pv_id is trully a patch, hence add Y to column IS_PATCH
    UPDATE package_versions SET
        is_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_ORDER
            FROM package_versions pv
        WHERE pv.pv_id = patchPv_id
          AND 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 dependencies
    IF (oPatchDepCollector.COUNT > 0) AND (isPatchDlocked = 'N') THEN
     -- Delete Existing Dependencies
        DELETE
          FROM PACKAGE_DEPENDENCIES dep
         WHERE dep.PV_ID = patchPv_id;


        -- Insert new dependencies
        INSERT 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_TYPE
          FROM PACKAGE_VERSIONS pv
         WHERE 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 test
IS

proc_id NUMBER;
/******************************************************************************
   NAME:       DELETE_DO_NOT_RIPPLE
   PURPOSE:    TO DELETE ENTRIES FROM THE DO_NOT_RIPPLE TABLE WHEN A PACKAGE
               IS RELEASED

   REVISIONS:
   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_RIPPLE
      Sysdate:         21/04/2006
      Date and Time:   21/04/2006, 3:21:55 PM, and 21/04/2006 3:21:55 PM
      Username:         (set in TOAD Options, Procedure Editor)
      Table Name:       (set in the "New PL/SQL Object" dialog)

******************************************************************************/
   CURSOR ripple_cur
   IS
      select distinct pkg_health_tag, cmd_interface, pkg_owner, is_interface from deployment_manager.processes_config;

   ripple_rec   ripple_cur%ROWTYPE;
BEGIN
   OPEN ripple_cur;

   FETCH ripple_cur
    INTO ripple_rec;

   WHILE ripple_cur%FOUND
   LOOP
   
   select 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_cur
       INTO 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 IS
        SELECT pkg_id FROM PACKAGES
        WHERE pkg_name = SSpkg_name;
    packages_rec packages_cur%ROWTYPE;

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

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

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

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

    ELSE
        parPkg_id := packages_rec.pkg_id;

    END IF;

    CLOSE packages_cur;



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

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

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

        Split_Version ( SSpkg_version, SSV_MM, SSV_NMM, SSV_EXT );

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


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

            -- Clone Package Version Details --
            INSERT INTO PACKAGE_VERSIONS ( pv_id, pkg_id, pkg_version, dlocked, created_stamp, creator_id, modified_stamp, modifier_id, V_MM, V_NMM, V_EXT,
                                           src_path, pv_description, PV_OVERVIEW, LAST_PV_ID, owner_id, BUILD_TYPE )
                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
                  FROM PACKAGE_VERSIONS pv
                 WHERE pv.pv_id = cloneFrom_pv_id;

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

        ELSE
            /* BRAND NEW version + ext */
            INSERT INTO PACKAGE_VERSIONS ( pv_id, pkg_id, pkg_version, dlocked, created_stamp, creator_id, modified_stamp, modifier_id, V_MM, V_NMM, V_EXT, owner_id, LAST_PV_ID, BUILD_TYPE )
         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;

    ELSE
        retPV_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;

BEGIN


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


        /* Log Action */
        -- Get Release Location
        SELECT proj.PROJ_NAME ||' / '|| rt.RTAG_NAME INTO ReleaseLocation
    FROM PROJECTS proj,
        RELEASE_TAGS rt
   WHERE rt.PROJ_ID = proj.PROJ_ID
     AND rt.RTAG_ID = nRtagId;

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

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


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

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



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


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


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

 ELSE
        /* Patch Ignore Warnings */

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


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

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


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


    END IF;


END Ignore_Dependency_Warnings;
/

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 Package
    UPDATE RELEASE_CONTENT rc SET
     rc.PKG_STATE = 0
    WHERE rc.PV_ID = nPvId
 AND 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 THEN
        ResultsURL := sResults;
       ELSE
        IF (sResults <> '') OR ( NOT sResults IS NULL ) THEN
         outFileName := FILENAME_PREFIX || newID ||'_'|| sResults;
      ResultsAttachment := sDpkg_path || '/' || outFileName;
           END IF;
       END IF;

 -- Remove NOT_DONE entry if exists
 DELETE FROM UNIT_TESTS
  WHERE pv_id = nPv_id
    AND 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 Package
    INSERT 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 Package
    DELETE FROM DO_NOT_RIPPLE
 WHERE PV_ID = nPvId
 AND 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 settings
   DELETE FROM PACKAGE_BUILD_ENV
   WHERE pv_id = nPvId;

   -- Reset flag to N
   UPDATE PACKAGE_VERSIONS SET
    IS_BUILD_ENV_REQUIRED = 'N'
   WHERE PV_ID = nPvId;


   -- Set new Build Env
   IF NOT sBuildEnvIdList IS NULL THEN
      -- Insert into PACKAGE_BUILD_ENV
      INSERT INTO PACKAGE_BUILD_ENV ( PV_ID, BE_ID )
   SELECT nPvId AS PV_ID,
      be.BE_ID
     FROM BUILD_ENVIRONMENTS be
    WHERE be.BE_ID IN (
               SELECT *
              FROM THE ( SELECT CAST( IN_LIST_NUMBER( sBuildEnvIdList ) AS RELMGR_NUMBER_TAB_t ) FROM dual )
             );

    -- Set flag to N
    UPDATE PACKAGE_VERSIONS SET
     IS_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;

BEGIN
    currDot := INSTR ( SSpkg_version, '.', -1 );      -- Find 1st dot from the right

    IF ( currDot > 0 )
    THEN
        -- YES dot separator found --
        SSV_EXT := GET_V_EXT( SSpkg_version );

        IF NOT SSV_EXT IS NULL
        THEN
            lastDot := currDot;
            currDot := INSTR ( SSpkg_version, '.', -1, 2 );   -- Find 2nd dot from the right

            IF ( currDot > 0 )
            THEN
                -- XXXX.M.E
                SSV_MM := SUBSTR ( SSpkg_version, currDot + 1, lastDot - currDot - 1);
                SSV_NMM := SUBSTR ( SSpkg_version, 0, currDot ) || SSV_EXT;
            ELSE
                -- XXXX.E
                SSV_MM := NULL;
                SSV_NMM := SSpkg_version;
            END IF;

        ELSE
            -- XXXX.M
            SSV_MM := SUBSTR ( SSpkg_version, currDot + 1, LENGTH(SSpkg_version) - currDot );
            SSV_NMM := SUBSTR ( SSpkg_version, 0, currDot );

        END IF;

    ELSE
        -- NO dot separator found --
        -- XXXXXX
        SSV_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 mode
 SELECT rt.OFFICIAL INTO returnValue
   FROM RELEASE_TAGS rt
  WHERE 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 Solanki
 
 Version: 4.0
   --------------------------------------------------------------------------- */
   STATE CHAR;
   
BEGIN
  
 SELECT DLOCKED INTO STATE
 FROM PACKAGE_VERSIONS
 WHERE PV_ID = nPvId; 

 IF STATE = 'A' THEN --It was made official for autobuilds
  -- Unlock Package
  UPDATE PACKAGE_VERSIONS pv SET
  pv.DLOCKED = 'P'
  WHERE pv.PV_ID = nPvId; 
 ELSE
  -- Unlock Package
  UPDATE PACKAGE_VERSIONS pv SET
  pv.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();

BEGIN
  oRTagIdCollector := IN_LIST_NUMBER ( nRtagIdList );

  -- Touch Release for Rebuild
     UPDATE RELEASE_TAGS rt SET
      rt.REBUILD_ENV = 'Y',
        rt.REBUILD_STAMP = 0
      WHERE 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 Package
    UPDATE PACKAGE_VERSIONS pv SET
     pv.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 IS
        SELECT pkg_id FROM PACKAGES
        WHERE pkg_name = SSpkg_name;
    packages_rec packages_cur%ROWTYPE;

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

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

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

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

    ELSE
        parPkg_id := packages_rec.pkg_id;

    END IF;

    CLOSE packages_cur;



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

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

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

        Split_Version ( SSpkg_version, SSV_MM, SSV_NMM, SSV_EXT );





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

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

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

        retPV_ID := parPv_id;

    ELSE
        retPV_ID := package_versions_rec.pv_id;

    END IF;

    CLOSE package_versions_cur;




END Seed_Package_Names_Versions2;
/

CREATE OR REPLACE PROCEDURE delete_do_not_ripple
IS
/******************************************************************************
   NAME:       DELETE_DO_NOT_RIPPLE
   PURPOSE:    TO DELETE ENTRIES FROM THE DO_NOT_RIPPLE TABLE WHEN A PACKAGE
               IS RELEASED

   REVISIONS:
   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_RIPPLE
      Sysdate:         21/04/2006
      Date and Time:   21/04/2006, 3:21:55 PM, and 21/04/2006 3:21:55 PM
      Username:         (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*/
   IS
      SELECT   dnr.rtag_id, dnr.pv_id AS last_pv_id, pv.pv_id AS new_pv_id
          FROM release_content rc, do_not_ripple dnr, package_versions pv
         WHERE rc.pv_id = pv.pv_id
           AND rc.rtag_id = dnr.rtag_id
           AND pv.dlocked = 'Y'
           AND pv.last_pv_id = dnr.pv_id
           AND pv.pv_id != dnr.pv_id
      ORDER BY last_pv_id;

   ripple_rec   ripple_cur%ROWTYPE;
BEGIN
   OPEN ripple_cur;

   FETCH ripple_cur
    INTO ripple_rec;

   WHILE ripple_cur%FOUND
   LOOP
      DELETE FROM do_not_ripple
            WHERE rtag_id = ripple_rec.rtag_id
              AND pv_id = ripple_rec.last_pv_id;

      FETCH ripple_cur
       INTO 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 || ',';

BEGIN

 IF NOT sInList IS NULL
 THEN
  LOOP
         EXIT 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_info
IS
/******************************************************************************
   NAME:       DELETE_AUTOBUILD_FAILURE_INFO
   PURPOSE:

   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_INFO
      Sysdate:         2/06/2006
      Date and Time:   2/06/2006, 8:44:25 AM, and 2/06/2006 8:44:25 AM
      Username:         (set in TOAD Options, Procedure Editor)
      Table Name:       (set in the "New PL/SQL Object" dialog)

******************************************************************************/
BEGIN
   DELETE FROM autobuild_failure
         WHERE group_email_id NOT IN (SELECT group_email_id
                                        FROM members_group);
END delete_autobuild_failure_info;
/

CREATE OR REPLACE PROCEDURE PAOLO_Build_Tree ( nRtag_id IN NUMBER ) IS

    sessionNumber NUMBER := 0;
    iteration NUMBER := 1; 
    rowCnt NUMBER := 0;
    maxIterations  NUMBER := 50;
 
BEGIN
 
   
    INSERT INTO TEMP_TREE_BROWSE ( SESSION_NUM, LEVEL_NUM, PV_ID )
    SELECT sessionNumber, iteration, rc.PV_ID
      FROM RELEASE_CONTENT rc
     WHERE rc.RTAG_ID = nRtag_id;
     
    iteration := iteration + 1;
    LOOP
        
        INSERT INTO TEMP_TREE_BROWSE ( SESSION_NUM, LEVEL_NUM, PV_ID )
  SELECT sessionNumber,     
               iteration,
               dep.DPV_ID
          FROM TEMP_TREE_BROWSE ttb,
               PACKAGE_DEPENDENCIES dep
         WHERE dep.PV_ID = ttb.PV_ID 
           AND ttb.LEVEL_NUM = iteration - 1 
           
         MINUS  
         
        SELECT sessionNumber, iteration, ttb.PV_ID
          FROM TEMP_TREE_BROWSE ttb;   
          
          
         
  
  rowCnt := SQL%ROWCOUNT;
  IF rowCnt > 0 THEN
           iteration := 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 Package
    UPDATE PACKAGE_VERSIONS pv SET
     pv.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();

BEGIN

    oPvIdCollector := IN_LIST_NUMBER ( nPvIdList );

    -- Get Action Type FK
    SELECT act.ACTTYPE_ID INTO ActionTypeId
      FROM ACTION_TYPE act
     WHERE act.NAME = enumActionTypeName;

    -- Log Action
    INSERT INTO ACTION_LOG ( USER_ID, ACTION_DATETIME, PV_ID, DESCRIPTION, ACTTYPE_ID )
    SELECT nUserId,
        ORA_SYSDATETIME,
           pv.PV_ID,
           sAdditionalComments,
           ActionTypeId
      FROM PACKAGE_VERSIONS pv
     WHERE 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 IS
        SELECT pv_id
        FROM package_versions
       WHERE 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 version
 SELECT pv.PKG_VERSION INTO OldPkgVersion
      FROM PACKAGE_VERSIONS pv
     WHERE pv.PV_ID = NNpv_id;


 sPackageVersion := SSpkg_version;


 IF OldPkgVersion != sPackageVersion THEN

     OPEN package_versions_cur;
     FETCH package_versions_cur INTO package_versions_rec;

     IF package_versions_cur%NOTFOUND
     THEN

   -- Split current version in parts
      Split_Version ( SSpkg_version, SSV_MM, SSV_NMM, SSV_EXT );


      -- Automated built config
   IF (cBuildType = 'A') THEN
    sPackageVersion := '('|| NNpv_id ||')'|| SSV_EXT; -- Make sure that version is still unique
   END IF;


         -- Packge version not found, hence rename it.
         UPDATE package_versions
            SET 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 = NULL
          WHERE pv_id = NNpv_id;


   IF (cBuildType = 'A') THEN
    sLabel := GET_AUTOMATED_LABEL( NNpv_id );
    UPDATE PACKAGE_VERSIONS pv SET
     pv.PKG_LABEL = sLabel
           WHERE 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 BOOLEAN
IS
/* ---------------------------------------------------------------------------
    Version: 3.0.0
   --------------------------------------------------------------------------- */

BEGIN
    IF LENGTH ( TRIM (TRANSLATE (SSV_EXT, '.ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz',' ') ) ) > 0
    THEN
        RETURN FALSE;
    ELSE
        RETURN TRUE;
    END IF;

END IS_VERSION_EXTENSION;
/

CREATE OR REPLACE PROCEDURE CLONED_PACKAGE_PROCESSES
IS

proc_id NUMBER;
/******************************************************************************
   NAME:       DELETE_DO_NOT_RIPPLE
   PURPOSE:    TO DELETE ENTRIES FROM THE DO_NOT_RIPPLE TABLE WHEN A PACKAGE
               IS RELEASED

   REVISIONS:
   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_RIPPLE
      Sysdate:         21/04/2006
      Date and Time:   21/04/2006, 3:21:55 PM, and 21/04/2006 3:21:55 PM
      Username:         (set in TOAD Options, Procedure Editor)
      Table Name:       (set in the "New PL/SQL Object" dialog)

******************************************************************************/
   CURSOR ripple_cur
   IS
      select distinct proc_id, prod_id from deployment_manager.processes_config;

   ripple_rec   ripple_cur%ROWTYPE;
BEGIN
   OPEN ripple_cur;

   FETCH ripple_cur
    INTO ripple_rec;

   WHILE ripple_cur%FOUND
   LOOP
   

   
insert into package_processes (PROC_ID, PV_ID) 
values( ripple_rec.proc_id, ripple_rec.prod_id);

      FETCH ripple_cur
       INTO 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 IS
        SELECT pkg_id FROM PACKAGES
        WHERE pkg_name = SSpkg_name;
    packages_rec packages_cur%ROWTYPE;

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

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

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

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

    ELSE
        parPkg_id := packages_rec.pkg_id;

    END IF;

    CLOSE packages_cur;



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

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

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

        Split_Version ( SSpkg_version, SSV_MM, SSV_NMM, SSV_EXT );

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


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

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

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

        ELSE
            /* BRAND NEW version + ext */
            INSERT INTO PACKAGE_VERSIONS ( pv_id, pkg_id, pkg_version, dlocked, created_stamp, creator_id, modified_stamp, modifier_id, V_MM, V_NMM, V_EXT, owner_id, LAST_PV_ID, BUILD_TYPE )
         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;

    ELSE
        retPV_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 dependency
    SELECT pkg.PKG_NAME ||' '|| pv.PKG_VERSION  INTO RuntimeDependency
   FROM RUNTIME_DEPENDENCIES rtd,
       PACKAGES pkg,
        PACKAGE_VERSIONS pv
  WHERE rtd.PV_ID = nPvId
    AND pv.PKG_ID = pkg.PKG_ID
    AND rtd.RTD_ID = pv.PV_ID
    AND rtd.RTD_ID = nRuntimeId;


 -- Delete Document
    DELETE
      FROM RUNTIME_DEPENDENCIES rtd
     WHERE rtd.PV_ID = nPvId
       AND 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) IS
ext VARCHAR2(50);



              
BEGIN

 --Extract the package version extension
 SELECT V_EXT into ext
 FROM PACKAGE_VERSIONS
 WHERE PV_ID = nPvId; 
 
    -- Deprecate Package
    INSERT INTO DEPRECATED_PACKAGES (RTAG_ID, PKG_ID, COMMENTS, V_EXT)
 VALUES (nRtagId, nPkgId, sComments, ext);
 
 IF ext IS NOT NULL THEN
    UPDATE RELEASE_CONTENT 
    SET PKG_ID = nPkgId, DEPRECATED_STATE = 6
    WHERE RTAG_ID = nRtagId
    AND PV_ID IN (SELECT PV.PV_ID 
            FROM PACKAGE_VERSIONS PV, PACKAGES PKG
       WHERE PKG.PKG_ID = PV.PKG_ID
       AND PV.V_EXT = ext
       AND PKG.PKG_ID = nPkgId
      );      
 ELSE
    UPDATE RELEASE_CONTENT 
    SET PKG_ID = nPkgId, DEPRECATED_STATE = 6
    WHERE RTAG_ID = nRtagId
    AND PV_ID IN (SELECT PV.PV_ID 
            FROM PACKAGE_VERSIONS PV, PACKAGES PKG
       WHERE PKG.PKG_ID = PV.PKG_ID
       AND PV.V_EXT IS NULL
       AND PKG.PKG_ID = nPkgId
      );
 END IF;
     
 UPDATE RELEASE_CONTENT
 SET PKG_ID = nPkgId, DEPRECATED_STATE = 7
 WHERE RTAG_ID = nRtagId
 AND PV_ID IN (     SELECT DISTINCT
              qry.PV_ID
              FROM (
            SELECT dep.*,
           LEVEL AS LEVEL_NUM
           FROM PACKAGE_DEPENDENCIES dep 
         START WITH dep.DPV_ID IN ( nPvId ) 
        CONNECT BY PRIOR dep.PV_ID = dep.DPV_ID
         ) qry,
        PACKAGES pkg,
        PACKAGE_VERSIONS pv,
        RELEASE_CONTENT rc
         WHERE qry.PV_ID = pv.PV_ID AND pv.PKG_ID = pkg.PKG_ID
        AND rc.RTAG_ID = nRtagId AND rc.PV_ID = qry.PV_ID
        AND rc.DEPRECATED_STATE IS NULL
     );    

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

END Deprecate_Package;
/

CREATE OR REPLACE PROCEDURE add_autobuild_failure_info (
   nprojid     IN   NUMBER,
   nviewid     IN   NUMBER,
   suserlist   IN   VARCHAR2
)
IS
   groupid   NUMBER;
/******************************************************************************
   NAME:       ADD_AUTOBUILD_FAILURE
   PURPOSE:

   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_FAILURE
      Sysdate:         11/04/2006
      Date and Time:   11/04/2006, 9:28:28 AM, and 11/04/2006 9:28:28 AM
      Username:         (set in TOAD Options, Procedure Editor)
      Table Name:       (set in the "New PL/SQL Object" dialog)

******************************************************************************/
BEGIN
   BEGIN
      SELECT group_email_id
        INTO groupid
        FROM autobuild_failure
       WHERE proj_id = nprojid AND view_id = nviewid;
   EXCEPTION
      WHEN NO_DATA_FOUND
      THEN
         --  Create Next Sequence Id ---
         SELECT seq_group_email_id.NEXTVAL
           INTO groupid
           FROM 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   |      OK
    0     0     0     1   |      MAJOR
    0     0     1     0   |      MINOR
    0     0     1     1   |      MAJOR
   ------------------------------------
    0     1     0     0   |      MAJOR_READY
    0     1     0     1   |      MAJOR
    0     1     1     0   |      MAJOR
    0     1     1     1   |      MAJOR
   ------------------------------------
    1     0     0     0   |      MINOR_READY
    1     0     0     1   |      MAJOR
    1     0     1     0   |      MINOR
    1     0     1     1   |      MAJOR
   ------------------------------------
    1     1     0     0   |      MAJOR_READY
    1     1     0     1   |      MAJOR
    1     1     1     0   |      MAJOR
    1     1     1     1   |      MAJOR
   ------------------------------------
*/

BEGIN
    /*----------------------------------------------
    ||              MINOR READY
    */----------------------------------------------
   
    UPDATE release_content
       SET pkg_state = 4
     WHERE rtag_id = NNrtag_id
       AND pv_id IN
           (
           SELECT DISTINCT pv_id
           FROM temp_env_states
           WHERE session_num = NNsession_num
             AND level_num = 1
             AND tes_state = 2
           );

    /*----------------------------------------------
    ||              MAJOR READY
    */----------------------------------------------
    UPDATE release_content
       SET pkg_state = 3
     WHERE rtag_id = NNrtag_id
       AND pv_id IN
           (
           SELECT DISTINCT pv_id
           FROM temp_env_states
           WHERE session_num = NNsession_num
             AND level_num = 1
             AND tes_state IN (0,1)
           );

    /*----------------------------------------------
    ||                MINOR
    */----------------------------------------------
    UPDATE release_content
       SET pkg_state = 2
     WHERE rtag_id = NNrtag_id
       AND pv_id IN
           (
           SELECT DISTINCT pv_id
            FROM temp_env_states
            WHERE session_num = NNsession_num
              AND level_num >= 2
              AND tes_state = 2
            MINUS
           SELECT pv_id
             FROM release_content
            WHERE rtag_id = NNrtag_id
              AND pkg_state = 3
           );

    /*----------------------------------------------
    ||                MAJOR
    */----------------------------------------------
    UPDATE release_content
       SET pkg_state = 1
     WHERE rtag_id = NNrtag_id
       AND pv_id IN
           (
           SELECT DISTINCT pv_id
           FROM temp_env_states
           WHERE session_num = NNsession_num
             AND level_num >= 2
             AND tes_state IN (0,1)
           /* MAJOR EXCEPTION - When package has Major Ready and Minor */
           UNION
           SELECT DISTINCT tes.pv_id
           FROM temp_env_states tes,
                release_content rc
           WHERE tes.session_num = NNsession_num
             AND tes.level_num >= 2
             AND tes.tes_state = 2
             AND rtag_id = NNrtag_id
             AND rc.pv_id = tes.pv_id
             AND rc.pkg_state = 3
           );

    /*----------------------------------------------
    ||                 OK
    */----------------------------------------------
    UPDATE release_content
       SET pkg_state = 0
     WHERE rtag_id = NNrtag_id
       AND pv_id IN
           (
           SELECT rc.pv_id
             FROM release_content rc
            WHERE rc.rtag_id = NNrtag_id
              AND NOT rc.pv_id IN
                    (
                     SELECT DISTINCT pv_id
                     FROM 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 Solanki
 Modified: 24th October 2006
 Reason: 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 Id
 SELECT rt.PROJ_ID INTO nProjId
   FROM RELEASE_TAGS rt
  WHERE rt.RTAG_ID = nRtagId; 
 
 IF nModeCode = 1 THEN
  -- Open Mode
  cMode := 'N';
  
  /* LOG ACTION */
     Log_Project_Action ( nProjId, 'set_to_open_mode', UserId, 'Release is set to Open Mode', nRtagId );
  
 ELSIF nModeCode = 2 THEN
  -- Restrictive Mode
  cMode := 'R';
  
  /* LOG ACTION */
     Log_Project_Action ( nProjId, 'set_to_restrictive_mode', UserId, 'Release is set to Restrictive Mode', nRtagId );
  
 ELSIF nModeCode = 3 THEN
  -- Closed Mode
  cMode := 'Y';
  
  /* LOG ACTION */
     Log_Project_Action ( nProjId, 'set_to_closed_mode', UserId, 'Release is set to Closed Mode', nRtagId  );

 ELSIF nModeCode = 4 THEN
  -- CCB Mode
  cMode := 'C';
  
  /* LOG ACTION */
     Log_Project_Action ( nProjId, 'set_to_ccb_mode', UserId, 'Release is set to CCB Mode', nRtagId  );
 
 ELSIF nModeCode = 5 THEN
  -- Archive Mode
  cMode := 'A';
  
  /* LOG ACTION */
     Log_Project_Action ( nProjId, 'set_to_archive_mode', UserId, 'Release is set to Archive Mode', nRtagId  ); 
    
  
 END IF;
 
 
 
 -- Now update table
 IF NOT cMode IS NULL THEN
  UPDATE RELEASE_TAGS rt SET
  rt.OFFICIAL = cMode
  WHERE 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 Num
    SELECT pd.DOC_NUM INTO DocNumber
      FROM PACKAGE_DOCUMENTS pd
     WHERE pd.PV_ID = nPvId
       AND pd.DOC_ID = nDocId;


 -- Delete Document
    DELETE
      FROM PACKAGE_DOCUMENTS pd
     WHERE pd.PV_ID = nPvId
       AND 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;


BEGIN

       IF nTestTypeId = enumTEST_TYPE_AUTOMATIC THEN
        ResultsURL := sResults;
       ELSE
        IF (sResults <> '') OR ( NOT sResults IS NULL ) THEN
         outFileName := FILENAME_PREFIX || nTest_id ||'_'|| sResults;
      ResultsAttachment := sDpkg_path || '/' || outFileName;
           END IF;
       END IF;

 --- Update Unit Test ---
    UPDATE UNIT_TESTS SET
           TEST_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 = ResultsAttachment
     WHERE TEST_ID = nTest_id
       AND 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;


BEGIN

       IF nTestTypeId = enumTEST_TYPE_AUTOMATIC THEN
        ResultsURL := sResults;
       ELSE
        IF (sResults <> '') OR ( NOT sResults IS NULL ) THEN
         outFileName := FILENAME_PREFIX || nTest_id ||'_'|| sResults;
      ResultsAttachment := sDpkg_path || '/' || outFileName;
           END IF;
       END IF;

       /* IF (sResults <> '') OR ( NOT sResults IS NULL ) THEN
         outFileName := FILENAME_PREFIX || newID ||'_'|| sResults;
      sResults_url:= 'doc' || '/' || 
   ;
        END IF;*/
 --- Update Unit Test ---
    UPDATE UNIT_TESTS SET
           TEST_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 = ResultsAttachment
     WHERE TEST_ID = nTest_id
       AND 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 IS
        SELECT COUNT(*) AS cnt_note
          FROM ADDITIONAL_NOTES
         WHERE pv_id = pnPv_id
           AND note_id != pnNote_id
           AND note_title = psNote_title;
    an_duplicate_rec an_duplicate_cur%ROWTYPE;

BEGIN
 outErrCode := -1;  -- Set default return error code to ERROR state

 OPEN an_duplicate_cur;
    FETCH an_duplicate_cur INTO an_duplicate_rec;

    IF an_duplicate_rec.cnt_note < 1
    THEN
  --- Update Additional Note ---
     UPDATE ADDITIONAL_NOTES SET
         note_title = psNote_title,
         note_body = psNote_body,
         mod_date = Ora_Sysdate,
         mod_user = pnUser_id
         WHERE note_id = pnNote_id
           AND pv_id = pnPv_id;
  outErrCode := 0;  -- Set return to SUCCESS
 END 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_SYSDATE
RETURN DATE
IS
/* ---------------------------------------------------------------------------
    Version: 3.0.0
   --------------------------------------------------------------------------- */
BEGIN

    RETURN 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) IS
             
ext VARCHAR2(50);
PvIdList VARCHAR2(32767);
nIdCollector RELMGR_NUMBER_TAB_t := RELMGR_NUMBER_TAB_t();
PvId NUMBER;
sComments VARCHAR2(32767);
             
BEGIN



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


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

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

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

END Undeprecate_Package;
/

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
   --------------------------------------------------------------------------- */



BEGIN
 IF cUpdateAcceptedStateOnly = 'Y'
 THEN
  --- Update Accepted State Only ---
  IF ( sAccepted IS NULL )
  THEN
   -- Clear alleptance
   UPDATE UNIT_TESTS SET
             TEST_ACCEPTED = NULL,
             ACCEPTANCE_DATE = NULL,
       ACCEPTED_BY = NULL,
       REVIEW_COMMENTS = NULL
       WHERE TEST_ID = pnTest_id
         AND PV_ID = pnPv_id;

     ELSE
   UPDATE UNIT_TESTS SET
             TEST_ACCEPTED = sAccepted,
             ACCEPTANCE_DATE = TO_DATE( sAcceptanceDate,'DD/MM/YYYY' ),
       ACCEPTED_BY = sAcceptedBy
       WHERE TEST_ID = pnTest_id
         AND PV_ID = pnPv_id;

  END IF;


 ELSE
  --- Update Unit Test Acceptance ---
     UPDATE UNIT_TESTS SET
            TEST_ACCEPTED = sAccepted,
      REVIEW_COMMENTS = sReviewComments,
            ACCEPTANCE_DATE = TO_DATE( sAcceptanceDate,'DD/MM/YYYY' ),
      ACCEPTED_BY = sAcceptedBy
      WHERE TEST_ID = pnTest_id
        AND 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 ) IS
            
            
  TO 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 IS
        SELECT pv.pv_id, pv.is_patch
          FROM PACKAGE_VERSIONS pv
         WHERE pv.pkg_version = SSpkg_version
           AND 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 IS
        SELECT DISTINCT pkg_id, dlocked FROM PACKAGE_VERSIONS WHERE pv_id = NNorig_pv_id;
    clone_package_versions_rec clone_package_versions_cur%ROWTYPE;

BEGIN
 IF NNsetto_pv_id IS NULL
    THEN
  -- 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%NOTFOUND
     THEN
         SELECT 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_REQUIRED
               FROM PACKAGE_VERSIONS pv
              WHERE pv.pv_id = NNorig_pv_id;


         -- Set Issues Type for cloning ---
         IF origDlocked = 'Y'
         THEN
             nIssuesTypes := enumISSUES_STATE_IMPORTED;
         ELSE
             nIssuesTypes := NULL;
         END IF;

   Basic_Clone ( NNorig_pv_id, retPv_id, NNrtag_id, NNuser_id, origPkg_id, nIssuesTypes );



     ELSE
         retPv_id := package_versions_rec.pv_id;

     END IF;

        CLOSE package_versions_cur;

 ELSE
     retPv_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) THEN
     UPDATE RELEASE_CONTENT
        SET pv_id = retPv_id,
            insert_stamp = Ora_Sysdate,
            insertor_id = NNuser_id
      WHERE rtag_id = NNrtag_id
        AND pv_id = NNorig_pv_id;

        /* LOG ACTION */
        SELECT proj.PROJ_NAME ||' / '|| vt.VTREE_NAME ||' / '|| rt.RTAG_NAME INTO ReleaseLocation
    FROM PROJECTS proj,
        VTREES vt,
         RELEASE_TAGS rt
   WHERE rt.VTREE_ID = vt.VTREE_ID
     AND vt.PROJ_ID = proj.PROJ_ID
     AND 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();

BEGIN
 oPvIdCollector := IN_LIST_NUMBER ( sPvIdList );

    -- Remove Ignore Warnings
     DELETE
       FROM ignore_warnings
   WHERE (rtag_id, pv_id, dpv_id) IN
      (
       SELECT igw.rtag_id, igw.pv_id, igw.dpv_id
         FROM ignore_warnings igw,
              package_versions dpv,
              package_versions pv
        WHERE igw.dpv_id = dpv.pv_id
          AND igw.rtag_id = nRTagId
          AND dpv.pkg_id = pv.pkg_id
          AND 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;

BEGIN
    lastDot := 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 )
        THEN
            RETURN SSV_EXT;
        ELSE
            RETURN 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_INTEREST
   PURPOSE:    To remove interest in a given package per project

   REVISIONS:
   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_INTEREST
      Sysdate:         12/05/2006
      Date and Time:   12/05/2006, 11:28:50 AM, and 12/05/2006 11:28:50 AM
      Username:         (set in TOAD Options, Procedure Editor)
      Table Name:       (set in the "New PL/SQL Object" dialog)

******************************************************************************/
BEGIN

  --Delete from PACKAGE_INTEREST
  DELETE FROM PACKAGE_INTEREST
  WHERE PROJ_ID = nProjId
  AND PKG_ID = nPkgId
  AND 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 Rebuild
     UPDATE RELEASE_TAGS rt SET
      rt.REBUILD_ENV = 'Y',
        rt.REBUILD_STAMP = 0
      WHERE 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 IS
     SELECT pv.pkg_version, pkg.pkg_name
    FROM runtime_dependencies rtd,
         package_versions pv,
      packages pkg
   WHERE rtd.rtd_id = pv.pv_id
     AND pv.pkg_id = pkg.pkg_id
     AND rtd.pv_id = NNpv_id
     AND rtd.rtd_id = NNrtd_id;
    rtd_rec rtd_cur%ROWTYPE;

 CURSOR old_rtd_cur IS
     SELECT pv.pv_id
    FROM package_versions pv
   WHERE pv.pkg_id IN ( SELECT rtdpv.pkg_id
                FROM package_versions rtdpv
          WHERE rtdpv.pv_id = NNrtd_id )
     AND pv.pkg_version = SSrtd_version;
 old_rtd_rec old_rtd_cur%ROWTYPE;


BEGIN

    -- Get current runtime dependency details
 OPEN 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 affect
          all 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%NOTFOUND
  THEN
   -- 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 SET
       rtd_id = retRTD_ID,
       rtd_url = SSrtd_url,
       rtd_comments = SSrtd_comments
    WHERE pv_id = NNpv_id
      AND rtd_id = NNrtd_id;

  ELSE
   -- Update runtime dependency table --
   UPDATE runtime_dependencies SET
       rtd_id = old_rtd_rec.pv_id,
       rtd_url = SSrtd_url,
       rtd_comments = SSrtd_comments
    WHERE pv_id = NNpv_id
      AND rtd_id = NNrtd_id;

  END IF;


  CLOSE old_rtd_cur;

 ELSE
  --  Version has not changed, hence update everithing except version --
  UPDATE runtime_dependencies SET
      rtd_url = SSrtd_url,
      rtd_comments = SSrtd_comments
   WHERE pv_id = NNpv_id
     AND 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 FK
    SELECT act.ACTTYPE_ID INTO ActionTypeId
      FROM ACTION_TYPE act
     WHERE 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 ) IS

 oldPvId 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" releases
  SELECT proj.PROJ_NAME ||' > '|| rt.RTAG_NAME  INTO sLocation
    FROM WORK_IN_PROGRESS wip,
        RELEASE_TAGS rt,
      PROJECTS proj
   WHERE wip.PV_ID = newPvId
     AND wip.RTAG_ID = rt.RTAG_ID
     AND rt.OFFICIAL != 'Y'
     AND rt.PROJ_ID = proj.PROJ_ID;

  EXCEPTION
      WHEN NO_DATA_FOUND THEN
          sLocation := 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 ReleaseLocation
    FROM PROJECTS proj,
        RELEASE_TAGS rt
   WHERE rt.PROJ_ID = proj.PROJ_ID
     AND rt.RTAG_ID = RtagId;

  Log_Action ( newPvId, 'work_in_progress', UserId, 'Location: '|| ReleaseLocation );

 ELSE
  
  RAISE_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 ) IS

 ReleaseLocation VARCHAR2(4000);

BEGIN

 /*--------------- Business Rules Here -------------------*/
 /*-------------------------------------------------------*/


 -- Get release location for logging pusposes
 SELECT proj.PROJ_NAME ||' / '|| rt.RTAG_NAME INTO ReleaseLocation
   FROM PROJECTS proj,
       RELEASE_TAGS rt
  WHERE rt.PROJ_ID = proj.PROJ_ID
    AND rt.RTAG_ID = RtagId;


 -- Delete from Work In Progress
 DELETE
   FROM WORK_IN_PROGRESS wip
  WHERE wip.RTAG_ID = RtagId
    AND 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 ) IS

 nIdCollector RELMGR_NUMBER_TAB_t := RELMGR_NUMBER_TAB_t();
 ReleaseLocation VARCHAR2(4000);
 PvId NUMBER;

BEGIN

 /*--------------- Business Rules Here -------------------*/
 IF (PvIdList IS NULL)
 THEN
  RAISE_APPLICATION_ERROR (-20000, 'Please select at least one package.' );
 END IF;
 /*-------------------------------------------------------*/


 nIdCollector := IN_LIST_NUMBER ( PvIdList );


 -- Get release location for logging pusposes
 SELECT proj.PROJ_NAME  ||' / '|| rt.RTAG_NAME INTO ReleaseLocation
   FROM PROJECTS proj,
       RELEASE_TAGS rt
  WHERE rt.PROJ_ID = proj.PROJ_ID
    AND rt.RTAG_ID = RtagId;


 FOR i IN 1..nIdCollector.COUNT
 LOOP
  PvId := nIdCollector(i);

  -- Delete from Work In Progress
  DELETE
    FROM WORK_IN_PROGRESS wip
   WHERE wip.RTAG_ID = RtagId
     AND 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 IS

 ReturnValue NUMBER;

BEGIN
 SELECT wip.VIEW_ID INTO ReturnValue
   FROM WORK_IN_PROGRESS wip
  WHERE wip.RTAG_ID = RtagId
    AND wip.PV_ID = PvId;

 RETURN ReturnValue;
END;
/*-------------------------------------------------------------------------------------------------------*/
PROCEDURE GET_VIEW_CONTENT ( RtagId IN NUMBER, ViewId IN NUMBER, RecordSet OUT typeCur ) IS

 IsBaseView CHAR(1);
 
BEGIN

 -- Check if the view is BASE VIEW
 SELECT vi.BASE_VIEW INTO IsBaseView
   FROM VIEWS vi
  WHERE vi.VIEW_ID = ViewId;
  
 IF (IsBaseView = 'Y') THEN 
  -- Get Base view content
  OPEN RecordSet FOR
  SELECT 0 AS PKG_STATE,
      NULL AS DEPRECATED_STATE,
      pv.pv_id, 
      pkg.pkg_name, 
      pv.pkg_version, 
      pv.dlocked, 
      pv.pv_description,
      pv.BUILD_TYPE
    FROM WORK_IN_PROGRESS rel,
         packages pkg,
         package_versions pv
   WHERE pv.pkg_id = pkg.pkg_id
     AND rel.pv_id = pv.pv_id
     AND rel.VIEW_ID = ViewId
     AND rel.RTAG_ID = RtagId
   ORDER BY UPPER(pkg.PKG_NAME);
  

 ELSE  
  
   -- Get non base view content
  OPEN RecordSet FOR
  SELECT 0 AS PKG_STATE,
      NULL AS DEPRECATED_STATE,
      pv.pv_id, 
      pkg.pkg_name, 
      pv.pkg_version, 
      pv.dlocked, 
      pv.pv_description,
      pv.BUILD_TYPE
    FROM WORK_IN_PROGRESS rel,
         packages pkg,
         package_versions pv,
      VIEW_DEF vd
   WHERE pv.pkg_id = pkg.pkg_id
     AND rel.pv_id = pv.pv_id
     AND vd.VIEW_ID = ViewId
     AND vd.PKG_ID = pv.PKG_ID
     AND rel.RTAG_ID = RtagId
   ORDER BY UPPER(pkg.PKG_NAME);
  
  
 END IF;   
 

END;
/*-------------------------------------------------------------------------------------------------------*/
PROCEDURE CHANGE_PACKAGE_VIEW ( PvId IN NUMBER, RtagId IN NUMBER, NewViewId IN NUMBER ) IS
 
BEGIN
 
 UPDATE WORK_IN_PROGRESS wip SET
 wip.VIEW_ID = NewViewId
 WHERE wip.PV_ID = PvId
   AND wip.RTAG_ID = RtagId;
 
END;
/*-------------------------------------------------------------------------------------------------------*/
END PK_WORK_IN_PROGRESS;
/

CREATE OR REPLACE PACKAGE BODY pk_package
IS
/*
------------------------------
||  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
   )
   IS
      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;
      spackageversion              VARCHAR2 (4000);
      nissuestypes                 NUMBER;
      nviewid                      NUMBER;
      reccount                     NUMBER;
      isreleased                   package_versions.dlocked%TYPE       := 'N';
      slabel                       VARCHAR2 (4000)                    := NULL;

      CURSOR package_versions_cur
      IS
         SELECT pv.pv_id, pv.is_patch, pv.dlocked
           FROM package_versions pv
          WHERE pv.pkg_version = snewpkgversion
            AND pv.pkg_id IN (SELECT DISTINCT origpv.pkg_id
                                         FROM package_versions origpv
                                        WHERE origpv.pv_id = nlastpvid);

      package_versions_rec         package_versions_cur%ROWTYPE;

      CURSOR clone_package_versions_cur
      IS
         SELECT DISTINCT pkg_id, dlocked
                    FROM package_versions
                   WHERE pv_id = nlastpvid;

      clone_package_versions_rec   clone_package_versions_cur%ROWTYPE;
   BEGIN
      spackageversion := snewpkgversion;

      IF nsettopvid IS NULL
      THEN
         -- 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%NOTFOUND
         THEN
            ---  Create brand new package ---
            SELECT seq_pv_id.NEXTVAL
              INTO returnpvid
              FROM DUAL;

            -- Split Version to get extention + other
            split_version (spackageversion, ssv_mm, ssv_nmm, ssv_ext);

            -- Get previous package to clone from
            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;

            -- Automated built config
            IF (cbuildtype = 'A')
            THEN
               spackageversion := '(' || returnpvid || ')' || ssv_ext;
                                    -- Make sure that version is still unique
            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, 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_id
                 FROM package_versions pv
                WHERE pv.pv_id = nlastpvid;

            -- Set Issues Type for cloning ---
            IF origdlocked = 'Y'
            THEN
               nissuestypes := enumissues_state_imported;
            ELSE
               nissuestypes := NULL;
            END IF;

            -- Update Label for automated built
            IF (cbuildtype = 'A')
            THEN
               slabel := get_automated_label (returnpvid);

               UPDATE package_versions pv
                  SET pv.pkg_label = slabel
                WHERE 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;
      ELSE
         returnpvid := nsettopvid;
      END IF;
   END new_version;

/*-------------------------------------------------------------------------------------------------------*/
   PROCEDURE change_state (
      pvid       IN   NUMBER,
      newstate   IN   package_versions.dlocked%TYPE,
      userid     IN   NUMBER
   )
   IS
   BEGIN
      -- Set package in Released mode
      UPDATE package_versions pv
         SET pv.dlocked = newstate,
             pv.modified_stamp = ora_sysdatetime,
             pv.modifier_id = userid
       WHERE 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
   )
   IS
      LOCKED     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 official
         SELECT pv.dlocked
           INTO LOCKED
           FROM package_versions pv
          WHERE pv.pv_id = pvid;

         IF LOCKED = 'Y'
         THEN
            problemstring :=
                  problemstring
               || '- Package is locked and released.'
               || UTL_TCP.crlf;
         END IF;

         -- Cannot remove if used in BOMs
         SELECT COUNT (osc.prod_id)
           INTO ROWCOUNT
           FROM deployment_manager.os_contents osc
          WHERE osc.prod_id = pvid;

         IF ROWCOUNT > 0
         THEN
            problemstring :=
                  problemstring
               || '- Package is part of Bill-Of-Material (BOM) in Deployment Manager.'
               || UTL_TCP.crlf;
         END IF;

         -- Cannot remove if Referenced as build dependency
         SELECT COUNT (dep.pv_id)
           INTO ROWCOUNT
           FROM package_dependencies dep
          WHERE dep.dpv_id = pvid;

         IF ROWCOUNT > 0
         THEN
            problemstring :=
                  problemstring
               || '- Package is referenced by other package as build dependency.'
               || UTL_TCP.crlf;
         END IF;

         -- Cannot remove if Referenced as runtime dependency
         SELECT COUNT (rd.pv_id)
           INTO ROWCOUNT
           FROM runtime_dependencies rd
          WHERE rd.rtd_id = pvid;

         IF ROWCOUNT > 0
         THEN
            problemstring :=
                  problemstring
               || '- Package is referenced by other package as runtime dependency.'
               || UTL_TCP.crlf;
         END IF;

         -- Cannot remove if Referenced as patch
         SELECT COUNT (pp.pv_id)
           INTO ROWCOUNT
           FROM package_patches pp
          WHERE pp.patch_id = pvid;

         IF ROWCOUNT > 0
         THEN
            problemstring :=
                  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 Progress
         DELETE FROM work_in_progress wip
               WHERE wip.pv_id = pvid;

         --- Remove From Pending
         DELETE FROM planned pl
               WHERE pl.pv_id = pvid;

         --- Remove From Released area
         DELETE FROM release_content rc
               WHERE rc.pv_id = pvid;

         ---Remove From Package Processes
         DELETE FROM package_processes pp
               WHERE pp.pv_id = pvid;

         --- Remove Dependencies
         DELETE FROM package_dependencies dep
               WHERE dep.pv_id = pvid;

         DELETE FROM package_dependencies dep
               WHERE dep.dpv_id = pvid;

         --- Remove Runtime dependencies
         DELETE FROM runtime_dependencies rtd
               WHERE rtd.pv_id = pvid;

         DELETE FROM runtime_dependencies rtd
               WHERE rtd.rtd_id = pvid;

         --- Remove components
         DELETE FROM product_components pc
               WHERE pc.pv_id = pvid;

         DELETE FROM release_components rc
               WHERE rc.pv_id = pvid;

         --- Remove From Notification History
         DELETE FROM notification_history nh
               WHERE nh.pv_id = pvid;

         --- Remove From Ignore Warnings
         DELETE FROM ignore_warnings iw
               WHERE iw.pv_id = pvid;

         --- Remove From Additional Notes
         DELETE FROM additional_notes an
               WHERE an.pv_id = pvid;

         --- Remove From CQ Issues
         DELETE FROM cq_issues cq
               WHERE cq.pv_id = pvid;

         --- Remove from Package Patches
         DELETE FROM package_patches pp
               WHERE pp.pv_id = pvid;

         DELETE FROM package_patches pp
               WHERE pp.patch_id = pvid;

         --- Remove From Package Documents
         DELETE FROM package_documents pd
               WHERE pd.pv_id = pvid;

         --- Remove from Code Review
         DELETE FROM code_reviews cr
               WHERE cr.pv_id = pvid;

         --- Remove from Unit Tests
         DELETE FROM unit_tests ut
               WHERE ut.pv_id = pvid;

         --- Remove from Package BuildEnv
         DELETE FROM package_build_env pbe
               WHERE pbe.pv_id = pvid;

         --- Remove from Build Order
         DELETE FROM build_order bo
               WHERE bo.pv_id = pvid;

         --- Remove from Note Manager
         DELETE FROM note_manager nm
               WHERE nm.nid = pvid;

         --- Remove from Action log
         DELETE FROM action_log al
               WHERE al.pv_id = pvid;
			   
		 --- Remove from Do Not Ripple 
		 DELETE FROM DO_NOT_RIPPLE dnr
		 	   WHERE dnr.PV_ID = pvid;
			   
		 --- Remove from Jira Issues 
		 DELETE FROM JIRA_ISSUES jira
		 	   WHERE jira.PV_ID = pvid;
			   			   
         --- Finally Remove From Package Versions
         --- Get Package name
         SELECT pv.pkg_id
           INTO pkgid
           FROM package_versions pv
          WHERE pv.pv_id = pvid;

         DELETE FROM package_versions pv
               WHERE pv.pv_id = pvid;

         --- Remove package name if not used any more
         SELECT COUNT (pv.pv_id)
           INTO ROWCOUNT
           FROM package_versions pv
          WHERE pv.pkg_id = pkgid;

         IF ROWCOUNT < 1
         THEN
            DELETE FROM PACKAGES pkg
                  WHERE 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
   )
   IS
      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
      IS
         SELECT pv.*, pkg.pkg_name
           FROM package_versions pv, PACKAGES pkg
          WHERE pv.pv_id = nparentpvid AND pv.pkg_id = pkg.pkg_id;

      parent_rec           parent_cur%ROWTYPE;

      CURSOR patch_cur
      IS
         SELECT pv.*, pg.pkg_name
           FROM package_versions pv, PACKAGES pg
          WHERE pv.pkg_id = parpkg_id
            AND pv.pkg_version = snewpatchversion
            AND pv.pkg_id = pg.pkg_id;

      patch_rec            patch_cur%ROWTYPE;

      CURSOR releases_cur
      IS
         SELECT rc.pv_id
           FROM release_content rc
          WHERE rc.pv_id = patch_rec.pv_id;

      releases_rec         releases_cur%ROWTYPE;
   BEGIN
      -- Get Last Install Order
      SELECT COUNT (*)
        INTO lastinstallorder
        FROM package_patches pp
       WHERE pp.pv_id = nparentpvid;

      -- Get parent details
      OPEN parent_cur;

      FETCH parent_cur
       INTO parent_rec;

      parpkg_id := parent_rec.pkg_id;

      -- Find if patch exists in database
      OPEN patch_cur;

      FETCH patch_cur
       INTO patch_rec;

      -- Parent must be official
      IF parent_rec.dlocked = 'Y'
      THEN
         IF patch_cur%NOTFOUND
         THEN
            ispatchdlocked := 'N';

            -- Create new patch version --
            SELECT seq_pv_id.NEXTVAL
              INTO patchpv_id
              FROM 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_order
                  FROM package_versions pv
                 WHERE 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
                       );
         ELSE
            patchpv_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%NOTFOUND
            THEN
               -- This pv_id is trully a patch, hence add Y to column IS_PATCH
               UPDATE package_versions
                  SET 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_order
                     FROM package_versions pv
                    WHERE 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 dependencies
      IF (opatchdepcollector.COUNT > 0) AND (ispatchdlocked = 'N')
      THEN
         -- Delete Existing Dependencies
         DELETE FROM package_dependencies dep
               WHERE dep.pv_id = patchpv_id;

         -- Insert new dependencies
         INSERT 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_type
              FROM package_versions pv
             WHERE pv.pv_id IN (
                      SELECT *
                        FROM TABLE
                                (CAST
                                    (opatchdepcollector AS relmgr_number_tab_t)
                                ));
      END IF;

      -- Return patch_id
      returnpatchid := patchpv_id;

      CLOSE parent_cur;

      CLOSE patch_cur;
   EXCEPTION
      WHEN DUP_VAL_ON_INDEX
      THEN
         raise_application_error (-20000,
                                     'Patch version '
                                  || snewpatchversion
                                  || ' already exist.'
                                 );
   END;

/*-------------------------------------------------------------------------------------------------------*/
   PROCEDURE obsolete_patch (
      patchid            IN   NUMBER,
      isobsolete         IN   CHAR,
      obsoletecomments   IN   VARCHAR2,
      userid             IN   NUMBER
   )
   IS
   BEGIN
      -- Update patch
      UPDATE package_versions pv
         SET pv.is_obsolete = isobsolete,
             pv.obsolete_comments = obsoletecomments
       WHERE pv.pv_id = patchid;

      /*
      -- Update patch children
      UPDATE PACKAGE_VERSIONS pv SET
      pv.IS_OBSOLETE = IsObsolete,
      pv.OBSOLETE_COMMENTS = ObsoleteComments
      WHERE pv.PV_ID IN (
                     SELECT DISTINCT dep.DPV_ID
                       FROM PACKAGE_DEPENDENCIES dep
                      WHERE dep.PV_ID = PatchId
                     );


      -- Update patch parent
      UPDATE PACKAGE_VERSIONS pv SET
      pv.IS_OBSOLETE = IsObsolete,
      pv.OBSOLETE_COMMENTS = ObsoleteComments
      WHERE pv.PV_ID IN (
                     SELECT DISTINCT dep.PV_ID
                       FROM PACKAGE_DEPENDENCIES dep
                      WHERE dep.DPV_ID = PatchId
                     );

         */

      /* LOG ACTION */
      IF isobsolete IS NOT NULL
      THEN
         log_action (patchid,
                     'patch_obsolete',
                     userid,
                     'Obsolete patch. ' || obsoletecomments
                    );
      ELSE
         log_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
   )
   IS
   BEGIN
      /*--------------- Business Rules Here -------------------*/
      IF spatchidlist IS NULL
      THEN
         raise_application_error (-20000,
                                  'Please select one or more Patches.'
                                 );
      END IF;

/*-------------------------------------------------------*/

      -- Update patch
      UPDATE package_versions pv
         SET pv.is_obsolete = isobsolete,
             pv.obsolete_comments = obsoletecomments
       WHERE pv.pv_id IN (
                SELECT *
                  FROM THE
                          (SELECT CAST
                                     (in_list_number (spatchidlist) AS relmgr_number_tab_t
                                     )
                             FROM DUAL
                          ));
   /*
   -- Update patch children
   UPDATE PACKAGE_VERSIONS pv SET
   pv.IS_OBSOLETE = IsObsolete,
   pv.OBSOLETE_COMMENTS = ObsoleteComments
   WHERE pv.PV_ID IN (
                  SELECT DISTINCT dep.DPV_ID
                    FROM PACKAGE_DEPENDENCIES dep
                   WHERE dep.PV_ID = PatchId
                  );


   -- Update patch parent
   UPDATE PACKAGE_VERSIONS pv SET
   pv.IS_OBSOLETE = IsObsolete,
   pv.OBSOLETE_COMMENTS = ObsoleteComments
   WHERE pv.PV_ID IN (
                  SELECT DISTINCT dep.PV_ID
                    FROM PACKAGE_DEPENDENCIES dep
                   WHERE dep.DPV_ID = PatchId
                  );

      */

   /* LOG ACTION
   IF IsObsolete IS NOT NULL THEN
         Log_Action ( PatchId, 'patch_obsolete', UserId,
                  'Obsolete patch. '|| ObsoleteComments );
   ELSE
      Log_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
   )
   IS
      pkgname   VARCHAR2 (100);
/*Rupesh Release on 17/05/2006*/
   BEGIN
      INSERT 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
   )
   IS
      processname   VARCHAR2 (4000);

/*Rupesh Release on 17/05/2006*/
      CURSOR proc_cur
      IS
         SELECT prc.proc_id
           FROM processes prc
          WHERE 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;
   BEGIN
      OPEN proc_cur;

      FETCH proc_cur
       INTO proc_rec;

      WHILE proc_cur%FOUND
      LOOP
         INSERT INTO package_processes
                     (proc_id, pv_id
                     )
              VALUES (proc_rec.proc_id, npvid
                     );

         SELECT prc.proc_name
           INTO processname
           FROM processes prc
          WHERE prc.proc_id = proc_rec.proc_id;

         -- Log Action --
         log_action (npvid,
                     'process_add',
                     nuserid,
                     'Added process with health tag ' || processname
                    );

         FETCH proc_cur
          INTO 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
   )
   IS
      processname   VARCHAR2 (4000);
/* Rupesh Release on 17/05/2006*/
   BEGIN
      SELECT prc.proc_name
        INTO processname
        FROM processes prc
       WHERE prc.proc_id = nprocid;

      DELETE FROM package_processes
            WHERE proc_id = nprocid AND pv_id = npvid;

      -- Log Action --
      log_action (npvid,
                  'process_remove',
                  nuserid,
                  'Removed process with health tag ' || processname
                 );
   END;
/*-------------------------------------------------------------------------------------------------------*/
  /* 
  Author: Rupesh Solanki
  Purpose: To move package versions from one release to another
  Release: 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	
  )	
  IS	
  
  oldrtag_name VARCHAR2(4000);
  newrtag_name VARCHAR2(4000);

   
    
  BEGIN
  
  
  SELECT rtag_name into oldrtag_name
  FROM RELEASE_TAGS
  WHERE rtag_id = nrtagid;
  
  SELECT rtag_name into newrtag_name
  FROM RELEASE_TAGS
  WHERE rtag_id = nnewrtagid;
  
  /* Table Work In Progress*/
  	   UPDATE WORK_IN_PROGRESS
	   SET RTAG_ID = nnewrtagid
	   WHERE RTAG_ID = nrtagid
	   AND PV_ID = npvid;
	   
  /* Table PLANNED*/	
  	   UPDATE PLANNED
	   SET RTAG_ID = nnewrtagid
	   WHERE RTAG_ID = nrtagid
	   AND 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_archive
IS
/*
------------------------------
||  Author:  Rupesh Solanki
||  Date:    26 October 2006
||  Version:   1.0
------------------------------
*/

   /*-------------------------------------------------------------------------------------------------------*/
   PROCEDURE restore_and_flush_release_data (
      nrtagid   IN   release_tags.rtag_id%TYPE
   )
   IS
   BEGIN
      /* MIGRATION - PACKAGE_VERSIONS (do not delete the package versions at
      the moment) */
      INSERT INTO release_manager.package_versions
         SELECT *
           FROM package_versions
          WHERE pv_id IN (SELECT pv_id
                            FROM release_content
                           WHERE rtag_id = nrtagid);

      /* MIGRATION - DO_NOT_RIPPLE */
      INSERT INTO release_manager.do_not_ripple
         SELECT *
           FROM do_not_ripple dnp
          WHERE pv_id IN (SELECT pv_id
                            FROM release_content
                           WHERE rtag_id = nrtagid);

      /* PURGE DATA FROM DO_NOT_RIPPLE */
      DELETE FROM do_not_ripple
            WHERE rtag_id = nrtagid AND pv_id IN (SELECT pv_id
                                                    FROM release_content
                                                   WHERE rtag_id = nrtagid);

      /* MIGRATION - PACKAGE_BUILD_ENV */
      INSERT INTO release_manager.package_build_env
         SELECT *
           FROM package_build_env
          WHERE pv_id IN (SELECT pv_id
                            FROM release_content
                           WHERE rtag_id = nrtagid);

      /* PURGE DATA FROM PACKAGE_BUILD_ENV */
      DELETE FROM package_build_env
            WHERE pv_id IN (SELECT pv_id
                              FROM release_content
                             WHERE rtag_id = nrtagid);
							 
      /* MIGRATION - PACKAGE_BUILD_INFO */
      INSERT INTO release_manager.package_build_info
         SELECT *
           FROM package_build_info
          WHERE pv_id IN (SELECT pv_id
                            FROM release_content
                           WHERE rtag_id = nrtagid);

      /* PURGE DATA FROM PACKAGE_BUILD_INFO */
      DELETE FROM package_build_info
            WHERE pv_id IN (SELECT pv_id
                              FROM release_content
                             WHERE rtag_id = nrtagid);
							 

      /* MIGRATION - UNIT_TESTS  */
      INSERT INTO release_manager.unit_tests
         SELECT *
           FROM unit_tests
          WHERE pv_id IN (SELECT pv_id
                            FROM release_content
                           WHERE rtag_id = nrtagid);

      /* PURGE DATA FROM UNIT_TESTS*/
      DELETE FROM unit_tests
            WHERE pv_id IN (SELECT pv_id
                              FROM release_content
                             WHERE rtag_id = nrtagid);

      /* MIGRATION - PACKAGE_PROCESSES */
      INSERT INTO release_manager.package_processes
         SELECT *
           FROM package_processes
          WHERE pv_id IN (SELECT pv_id
                            FROM release_content
                           WHERE rtag_id = nrtagid);

      /* PURGE DATA FROM PACKAGE_PROCESSES*/
      DELETE FROM package_processes
            WHERE pv_id IN (SELECT pv_id
                              FROM release_content
                             WHERE rtag_id = nrtagid);

      /* MIGRATION - PACKAGE_DEPENDENCIES */
      INSERT INTO release_manager.package_dependencies
         SELECT *
           FROM package_dependencies
          WHERE pv_id IN (SELECT pv_id
                            FROM release_content
                           WHERE rtag_id = nrtagid);

      /* PURGE DATA FROM PACKAGE_DEPENDENCIES*/
      DELETE FROM package_dependencies
            WHERE pv_id IN (SELECT pv_id
                              FROM release_content
                             WHERE rtag_id = nrtagid);

      /* MIGRATION - CODE_REVIEWS */
      INSERT INTO release_manager.code_reviews
         SELECT *
           FROM code_reviews
          WHERE pv_id IN (SELECT pv_id
                            FROM release_content
                           WHERE rtag_id = nrtagid);

      /* PURGE DATA FROM CODE_REVIEWS*/
      DELETE FROM code_reviews
            WHERE pv_id IN (SELECT pv_id
                              FROM release_content
                             WHERE rtag_id = nrtagid);

      /* MIGRATION - RUNTIME_DEPENDENCIES*/
      INSERT INTO release_manager.runtime_dependencies
         SELECT *
           FROM runtime_dependencies
          WHERE pv_id IN (SELECT pv_id
                            FROM release_content
                           WHERE rtag_id = nrtagid);

      /* PURGE DATA FROM RUNTIME_DEPENDENCIES*/
      DELETE FROM runtime_dependencies
            WHERE pv_id IN (SELECT pv_id
                              FROM release_content
                             WHERE rtag_id = nrtagid);

      /* MIGRATION - PACKAGE_DOCUMENTS */
      INSERT INTO release_manager.package_documents
         SELECT *
           FROM package_documents
          WHERE pv_id IN (SELECT pv_id
                            FROM release_content
                           WHERE rtag_id = nrtagid);

      /* PURGE DATA FROM PACKAGE_DOCUMENTS*/
      DELETE FROM package_documents
            WHERE pv_id IN (SELECT pv_id
                              FROM release_content
                             WHERE rtag_id = nrtagid);

      /* MIGRATION - PACKAGE_PATCHES */
      INSERT INTO release_manager.package_patches
         SELECT *
           FROM package_patches
          WHERE pv_id IN (SELECT pv_id
                            FROM release_content
                           WHERE rtag_id = nrtagid);

      /* PURGE DATA FROM PACKAGE_PATCHES*/
      DELETE FROM package_patches
            WHERE pv_id IN (SELECT pv_id
                              FROM release_content
                             WHERE rtag_id = nrtagid);

      /* MIGRATION - CQ_ISSUES */
      INSERT INTO release_manager.cq_issues
         SELECT *
           FROM cq_issues
          WHERE pv_id IN (SELECT pv_id
                            FROM release_content
                           WHERE rtag_id = nrtagid);

      /* PURGE DATA FROM CQ_ISSUES*/
      DELETE FROM cq_issues
            WHERE pv_id IN (SELECT pv_id
                              FROM release_content
                             WHERE rtag_id = nrtagid);

      /* MIGRATION - ADDITIONAL_NOTES */
      INSERT INTO release_manager.additional_notes
         SELECT *
           FROM additional_notes
          WHERE pv_id IN (SELECT pv_id
                            FROM release_content
                           WHERE rtag_id = nrtagid);

      /* PURGE DATA FROM ADDITIONAL_NOTES*/
      DELETE FROM additional_notes
            WHERE pv_id IN (SELECT pv_id
                              FROM release_content
                             WHERE rtag_id = nrtagid);

      /* MIGRATION - RELEASE_COMPONENTS */
      INSERT INTO release_manager.release_components
         SELECT *
           FROM release_components
          WHERE pv_id IN (SELECT pv_id
                            FROM release_content
                           WHERE rtag_id = nrtagid);

      /* PURGE DATA FROM RELEASE_COMPONENTS*/
      DELETE FROM release_components
            WHERE pv_id IN (SELECT pv_id
                              FROM release_content
                             WHERE rtag_id = nrtagid);

      /* MIGRATION - IGNORE_WARNINGS */
      INSERT INTO release_manager.ignore_warnings
         SELECT *
           FROM ignore_warnings
          WHERE pv_id IN (SELECT pv_id
                            FROM release_content
                           WHERE rtag_id = nrtagid);

      /* PURGE DATA FROM IGNORE_WARNINGS*/
      DELETE FROM ignore_warnings
            WHERE pv_id IN (SELECT pv_id
                              FROM release_content
                             WHERE rtag_id = nrtagid);

      /* MIGRATION - NOTIFICATION_HISTORY */
      INSERT INTO release_manager.notification_history
         SELECT *
           FROM notification_history
          WHERE pv_id IN (SELECT pv_id
                            FROM release_content
                           WHERE rtag_id = nrtagid);

      /* PURGE DATA FROM NOTIFICATION_HISTORY*/
      DELETE FROM notification_history
            WHERE pv_id IN (SELECT pv_id
                              FROM release_content
                             WHERE rtag_id = nrtagid);

      /* MIGRATION - BUILD_ORDER   */
      INSERT INTO release_manager.build_order
         SELECT *
           FROM build_order
          WHERE pv_id IN (SELECT pv_id
                            FROM release_content
                           WHERE rtag_id = nrtagid);

      /* PURGE DATA FROM BUILD_ORDER*/
      DELETE FROM build_order
            WHERE pv_id IN (SELECT pv_id
                              FROM release_content
                             WHERE rtag_id = nrtagid);

      /* PURGE DATA FROM WORK_IN_PROGRESS 
      DELETE FROM work_in_progress
            WHERE rtag_id = nrtagid;*/

      /* PURGE DATA FROM PLANNED 
      DELETE FROM planned
            WHERE rtag_id = nrtagid;*/

      /* MIGRATION - JIRA_ISSUES */
      INSERT INTO release_manager.jira_issues
         SELECT *
           FROM jira_issues
          WHERE pv_id IN (SELECT pv_id
                            FROM release_content
                           WHERE rtag_id = nrtagid);

      /* PURGE DATA FROM JIRA_ISSUES*/
      DELETE FROM jira_issues
            WHERE pv_id IN (SELECT pv_id
                              FROM release_content
                             WHERE rtag_id = nrtagid);

      /* MIGRATION - PRODUCT_COMPONENTS */
      INSERT INTO release_manager.product_components
         SELECT *
           FROM product_components
          WHERE pv_id IN (SELECT pv_id
                            FROM release_content
                           WHERE rtag_id = nrtagid);

      /* PURGE DATA FROM PRODUCT_COMPONENTS*/
      DELETE FROM product_components
            WHERE pv_id IN (SELECT pv_id
                              FROM release_content
                             WHERE rtag_id = nrtagid);

      /* MIGRATION - ACTION_LOG */
      INSERT INTO release_manager.action_log
         SELECT *
           FROM action_log
          WHERE pv_id IN (SELECT pv_id
                            FROM release_content
                           WHERE rtag_id = nrtagid);

      /* PURGE DATA FROM ACTION_LOG*/
      DELETE FROM action_log
            WHERE pv_id IN (SELECT pv_id
                              FROM release_content
                             WHERE rtag_id = nrtagid);

      /* MIGRATION - PROJECT_ACTION_LOG */
      INSERT INTO release_manager.project_action_log
         SELECT *
           FROM project_action_log
          WHERE rtag_id = nrtagid;

      /* PURGE DATA FROM PROJECT_ACTION_LOG */
      DELETE FROM project_action_log
            WHERE rtag_id = nrtagid;

      /* MIGRATION - DEPRECATED_PACKAGES */
      INSERT INTO release_manager.deprecated_packages
         SELECT *
           FROM deprecated_packages
          WHERE rtag_id = nrtagid;

      /* PURGE DATA FROM DEPRECATED_PACKAGES */
      DELETE FROM deprecated_packages
            WHERE rtag_id = nrtagid;

      /* MIGRATION - RELEASE_CONTENT */
      INSERT INTO release_manager.release_content
         SELECT *
           FROM release_content
          WHERE rtag_id = nrtagid;

      /* PURGE DATA FROM PACKAGE_VERSIONS (delete package versions now as 
      all child records wiped out)*/
      DELETE FROM package_versions
            WHERE pv_id IN (SELECT pv_id
                              FROM release_content
                             WHERE rtag_id = nrtagid);
        
      /* PURGE DATA FROM RELEASE_CONTENT*/
      DELETE FROM release_content
            WHERE rtag_id = nrtagid;
			
      /* PURGE DATA FROM RELEASE_TAGS*/
      DELETE FROM release_tags
            WHERE rtag_id = nrtagid;
			
                
   END;

/*-------------------------------------------------------------------------------------------------------*/
   PROCEDURE write_action_log (
      nuserid   IN   NUMBER,
      nrtagid   IN   release_tags.rtag_id%TYPE
   )
   IS
   BEGIN
      /* 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)   IS


BEGIN


 IF (NOT RtagId IS NULL) THEN
  -- Case with RTAG_ID present
  OPEN records FOR
  SELECT rt.PROJ_ID,
      rt.RTAG_ID AS RTAG_ID
    FROM RELEASE_TAGS rt
   WHERE rt.RTAG_ID = TO_NUMBER(RtagId);

 ELSIF (NOT ProjId IS NULL) THEN
  -- Case with PROJ_ID present
  OPEN records FOR
  SELECT TO_NUMBER(ProjId) AS PROJ_ID,
      -1 AS RTAG_ID
    FROM DUAL;

 ELSE
  -- Case with none present
  OPEN records FOR
  SELECT -1 AS PROJ_ID,
      -1 AS RTAG_ID
    FROM 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 ) IS

 nOsId NUMBER;

BEGIN
 /*--------------- Business Rules Here -------------------*/
 -- OS Name Requirements --
 IF (sOsName IS NULL) THEN
  RAISE_APPLICATION_ERROR (-20000, 'OsName cannot be NULL.');
 END IF;


 -- File Requirements --
 IF (NOT sFileName IS NULL) THEN

  IF (nByteSize IS NULL) THEN
   RAISE_APPLICATION_ERROR (-20000, 'Byte Size cannot be NULL.');

  ELSIF (sCRCcksum IS NULL) THEN
   RAISE_APPLICATION_ERROR (-20000, 'CRC CKSUM cannot be NULL.');

  END IF;

 END IF;


 -- Folder Requirements --
 -- No requirements for now.

 /*-------------------------------------------------------*/

 -- Get OsId
 nOsId := Get_OsId ( sOsName );


 -- Insert component entry
 INSERT 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
   );


 EXCEPTION
    WHEN DUP_VAL_ON_INDEX
 THEN
  RAISE_APPLICATION_ERROR (-20000, 'Cannot have duplicate product components.');

END;
/*-------------------------------------------------------------------------------------------------------*/
PROCEDURE Remove_All_Product_Components ( nPvId IN NUMBER, sOsName IN VARCHAR2 ) IS

 nOsId NUMBER;

BEGIN
 /*--------------- Business Rules Here -------------------*/
 /*-------------------------------------------------------*/

 -- Get OsId
 nOsId := Get_OsId ( sOsName );


 -- Delete component entry
 DELETE
   FROM PRODUCT_COMPONENTS pc
  WHERE pc.PV_ID = nPvId
    AND pc.OS_ID = nOsId;


END;
/*-------------------------------------------------------------------------------------------------------*/
FUNCTION Get_OsId ( sOsName IN VARCHAR2 ) RETURN NUMBER IS
 Code NUMBER;
 
BEGIN

 -- Get Platform Code --
 SELECT pf.CODE INTO Code
   FROM PLATFORMS pf
  WHERE UPPER( pf.NAME ) = UPPER( sOsName );
    
 
 RETURN Code;    

 EXCEPTION
     WHEN NO_DATA_FOUND THEN
         RAISE_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 ) IS

 oldPvId 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" releases
  SELECT proj.PROJ_NAME ||' > '|| rt.RTAG_NAME, rt.RTAG_ID  INTO sLocation, nRtagIdLocation
    FROM PLANNED pl,
        RELEASE_TAGS rt,
      PROJECTS proj
   WHERE pl.PV_ID = newPvId
     AND pl.RTAG_ID = rt.RTAG_ID
     AND rt.OFFICIAL != 'Y'
     AND rt.PROJ_ID = proj.PROJ_ID;

  EXCEPTION
      WHEN NO_DATA_FOUND THEN
          sLocation := NULL;
 END;          



 IF (sLocation IS NULL) OR (nRtagIdLocation = RtagId) THEN

  -- Add to "Pending" area
  INSERT INTO PLANNED ( RTAG_ID, PV_ID, VIEW_ID )
  VALUES( RtagId, newPvId, ViewId );


     /* LOG ACTION */
  SELECT proj.PROJ_NAME ||' / '|| rt.RTAG_NAME INTO ReleaseLocation
    FROM PROJECTS proj,
        RELEASE_TAGS rt
   WHERE rt.PROJ_ID = proj.PROJ_ID
     AND rt.RTAG_ID = RtagId;

  Log_Action ( newPvId, 'add_to_planned', UserId, 'Location: '|| ReleaseLocation );
 
 ELSE
  
  RAISE_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 ) IS

 ReleaseLocation VARCHAR2(4000);

BEGIN

 /*--------------- Business Rules Here -------------------*/
 /*-------------------------------------------------------*/


 -- Get release location for logging pusposes
 SELECT proj.PROJ_NAME  ||' / '|| rt.RTAG_NAME INTO ReleaseLocation
   FROM PROJECTS proj,
       RELEASE_TAGS rt
  WHERE rt.PROJ_ID = proj.PROJ_ID
    AND rt.RTAG_ID = RtagId;


 -- Delete from Work In Progress
 DELETE
   FROM PLANNED pl
  WHERE pl.RTAG_ID = RtagId
    AND 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 IS

 ReturnValue NUMBER;

BEGIN
 SELECT pl.VIEW_ID INTO ReturnValue
   FROM PLANNED pl
  WHERE pl.RTAG_ID = RtagId
    AND pl.PV_ID = PvId;

 RETURN ReturnValue;
END;
/*-------------------------------------------------------------------------------------------------------*/
PROCEDURE GET_VIEW_CONTENT ( RtagId IN NUMBER, ViewId IN NUMBER, RecordSet OUT typeCur ) IS

 IsBaseView CHAR(1);
 
BEGIN

 -- Check if the view is BASE VIEW
 SELECT vi.BASE_VIEW INTO IsBaseView
   FROM VIEWS vi
  WHERE vi.VIEW_ID = ViewId;

  
 IF (IsBaseView = 'Y') THEN 
  -- Get Base view content
  OPEN RecordSet FOR
  SELECT 0 AS PKG_STATE,
      NULL AS DEPRECATED_STATE,
      pv.pv_id, 
      pkg.pkg_name, 
      pv.pkg_version, 
      pv.dlocked, 
      pv.pv_description,
      pv.BUILD_TYPE
    FROM PLANNED rel,
         packages pkg,
         package_versions pv
   WHERE pv.pkg_id = pkg.pkg_id
     AND rel.pv_id = pv.pv_id
     AND rel.VIEW_ID = ViewId
     AND rel.RTAG_ID = RtagId
   ORDER BY UPPER(pkg.PKG_NAME);

 ELSE  
  
   -- Get non base view content
  OPEN RecordSet FOR
  SELECT 0 AS PKG_STATE,
      NULL AS DEPRECATED_STATE,
      pv.pv_id, 
      pkg.pkg_name, 
      pv.pkg_version, 
      pv.dlocked, 
      pv.pv_description,
      pv.BUILD_TYPE
    FROM PLANNED rel,
         packages pkg,
         package_versions pv,
      VIEW_DEF vd
   WHERE pv.pkg_id = pkg.pkg_id
     AND rel.pv_id = pv.pv_id
     AND rel.RTAG_ID = RtagId
     AND vd.VIEW_ID = ViewId
     AND vd.PKG_ID = pv.PKG_ID
   ORDER BY UPPER(pkg.PKG_NAME);
  
 END IF;  
  

END;
/*-------------------------------------------------------------------------------------------------------*/
PROCEDURE CHANGE_PACKAGE_VIEW ( PvId IN NUMBER, RtagId IN NUMBER, NewViewId IN NUMBER ) IS
 
BEGIN
 
 UPDATE PLANNED pl SET
 pl.VIEW_ID = NewViewId
 WHERE pl.PV_ID = PvId
   AND pl.RTAG_ID = RtagId;
 
END;
/*-------------------------------------------------------------------------------------------------------*/
END PK_PLANNED;
/

