Rev 3947 | Rev 5513 | Go to most recent revision | Blame | Compare with Previous | Last modification | View Log | RSS feed
---------------------------------------------------------- File created - Tuesday-September-23-2014------------------------------------------------------------------------------------------------------------------ DDL for Type DEPMGR_NUMBER_TAB_T--------------------------------------------------------CREATE OR REPLACE TYPE "DEPMGR_NUMBER_TAB_T" as TABLE of NUMBER//---------------------------------------------------------- DDL for Type SPLIT_TBL--------------------------------------------------------CREATE OR REPLACE TYPE "SPLIT_TBL" as table of varchar2(32767)//---------------------------------------------------------- DDL for Sequence SEQ_ARTICLE_ID--------------------------------------------------------CREATE SEQUENCE "SEQ_ARTICLE_ID" MINVALUE 1 MAXVALUE 999999999999999999999999999 INCREMENT BY 1 START WITH 10000 CACHE 20 ORDER NOCYCLE ;/---------------------------------------------------------- DDL for Sequence SEQ_BASE_ENV_ID--------------------------------------------------------CREATE SEQUENCE "SEQ_BASE_ENV_ID" MINVALUE 1 MAXVALUE 999999999999999999999999999 INCREMENT BY 1 START WITH 81675 CACHE 20 ORDER NOCYCLE ;/---------------------------------------------------------- DDL for Sequence SEQ_BOM_ID--------------------------------------------------------CREATE SEQUENCE "SEQ_BOM_ID" MINVALUE 1 MAXVALUE 999999999999999999999999999 INCREMENT BY 1 START WITH 85984 CACHE 20 ORDER NOCYCLE ;/---------------------------------------------------------- DDL for Sequence SEQ_BOM_NAME_ID--------------------------------------------------------CREATE SEQUENCE "SEQ_BOM_NAME_ID" MINVALUE 1 MAXVALUE 999999999999999999999999999 INCREMENT BY 1 START WITH 1501 CACHE 20 ORDER NOCYCLE ;/---------------------------------------------------------- DDL for Sequence SEQ_BRANCH_ID--------------------------------------------------------CREATE SEQUENCE "SEQ_BRANCH_ID" MINVALUE 1 MAXVALUE 999999999999999999999999999 INCREMENT BY 1 START WITH 4941 CACHE 20 ORDER NOCYCLE ;/---------------------------------------------------------- DDL for Sequence SEQ_HARDWARE_NAME_ID--------------------------------------------------------CREATE SEQUENCE "SEQ_HARDWARE_NAME_ID" MINVALUE 1 MAXVALUE 999999999999999999999999999 INCREMENT BY 1 START WITH 61 CACHE 20 ORDER NOCYCLE ;/---------------------------------------------------------- DDL for Sequence SEQ_MSG_ID--------------------------------------------------------CREATE SEQUENCE "SEQ_MSG_ID" MINVALUE 1 MAXVALUE 999999999999999999999999999 INCREMENT BY 1 START WITH 462 CACHE 20 ORDER NOCYCLE ;/---------------------------------------------------------- DDL for Sequence SEQ_NODE_ID--------------------------------------------------------CREATE SEQUENCE "SEQ_NODE_ID" MINVALUE 1 MAXVALUE 999999999999999999999999999 INCREMENT BY 1 START WITH 171695 CACHE 20 ORDER NOCYCLE ;/---------------------------------------------------------- DDL for Sequence SEQ_OS_ID--------------------------------------------------------CREATE SEQUENCE "SEQ_OS_ID" MINVALUE 1 MAXVALUE 999999999999999999999999999 INCREMENT BY 1 START WITH 177041 CACHE 20 ORDER NOCYCLE ;/---------------------------------------------------------- DDL for Sequence SEQ_PROC_ID--------------------------------------------------------CREATE SEQUENCE "SEQ_PROC_ID" MINVALUE 1 MAXVALUE 999999999999999999999999999 INCREMENT BY 1 START WITH 1441 CACHE 20 ORDER NOCYCLE ;/---------------------------------------------------------- DDL for Sequence SEQ_PROJ_ID--------------------------------------------------------CREATE SEQUENCE "SEQ_PROJ_ID" MINVALUE 1 MAXVALUE 999999999999999999999999999 INCREMENT BY 1 START WITH 881 CACHE 20 ORDER NOCYCLE ;/---------------------------------------------------------- DDL for Sequence SEQ_STATE_ID--------------------------------------------------------CREATE SEQUENCE "SEQ_STATE_ID" MINVALUE 1 MAXVALUE 999999999999999999999999999 INCREMENT BY 1 START WITH 1621 CACHE 20 ORDER NOCYCLE ;/---------------------------------------------------------- DDL for Sequence SEQ_UNIT_TESTS--------------------------------------------------------CREATE SEQUENCE "SEQ_UNIT_TESTS" MINVALUE 1 MAXVALUE 999999999999999999999999999 INCREMENT BY 1 START WITH 541 CACHE 20 ORDER NOCYCLE ;/---------------------------------------------------------- DDL for Table ACTION_TRAIL--------------------------------------------------------CREATE TABLE "ACTION_TRAIL"( "ACTION_STAMP" DATE,"USER_ID" NUMBER,"ACTION_EVENT" NUMBER,"METHOD_CALL" VARCHAR2(4000),"ACTION_SCRIPT" VARCHAR2(4000),"ACTION_DESCRIPTION" VARCHAR2(4000)) ;/---------------------------------------------------------- DDL for Table BASE_ENV--------------------------------------------------------CREATE TABLE "BASE_ENV"( "BASE_ENV_ID" NUMBER,"BOS_ID" NUMBER,"BASE_ENV_NAME" VARCHAR2(50),"OLD_BASE_ENV_ID" NUMBER,"BOM_ID" NUMBER) ;/---------------------------------------------------------- DDL for Table BASE_ENV_CONTENTS--------------------------------------------------------CREATE TABLE "BASE_ENV_CONTENTS"( "BASE_ENV_ID" NUMBER,"PROD_ID" NUMBER,"BASE_ENV_COMMENTS" VARCHAR2(4000),"SEQ_NUM" NUMBER,"IS_NODE_SPEC" VARCHAR2(2),"NOT_XML" VARCHAR2(2)) ;/---------------------------------------------------------- DDL for Table BOMS--------------------------------------------------------CREATE TABLE "BOMS"( "BOM_ID" NUMBER,"BRANCH_ID" NUMBER,"BOM_NAME_ID" NUMBER,"BOM_VERSION" VARCHAR2(4000),"IS_REJECTED" CHAR(1),"IS_READONLY" CHAR(1),"IS_HIDDEN" CHAR(1),"BOM_COMMENTS" VARCHAR2(4000),"BOM_LIFECYCLE" VARCHAR2(4000),"OLD_BOM_ID" NUMBER,"PARENT_BOM_ID" NUMBER,"RTAG_ID_FK" NUMBER,"PDF_ATTACHMENT_PATH" VARCHAR2(4000)) ;/---------------------------------------------------------- DDL for Table BOM_CONTENTS--------------------------------------------------------CREATE TABLE "BOM_CONTENTS"( "BOM_ID" NUMBER,"NODE_ID" NUMBER,"SEQ_NUM" NUMBER) ;/---------------------------------------------------------- DDL for Table BOM_NAMES--------------------------------------------------------CREATE TABLE "BOM_NAMES"( "BOM_NAME_ID" NUMBER,"BOM_NAME" VARCHAR2(255)) ;/---------------------------------------------------------- DDL for Table BOM_NOTES--------------------------------------------------------CREATE TABLE "BOM_NOTES"( "BOM_ID" NUMBER,"BOM_SCOPE" NUMBER,"SCOPE_ID" NUMBER,"ARTICLE_ID" NUMBER) ;/---------------------------------------------------------- DDL for Table BOM_STATE--------------------------------------------------------CREATE TABLE "BOM_STATE"( "BOM_ID" NUMBER,"STATE_ID" NUMBER,"DATE_STAMP" DATE,"USER_ID" NUMBER,"IS_DISPLAYED" CHAR(1)) ;/---------------------------------------------------------- DDL for Table BOM_STATE20100609--------------------------------------------------------CREATE TABLE "BOM_STATE20100609"( "BOM_ID" NUMBER,"STATE_ID" NUMBER,"DATE_STAMP" DATE,"USER_ID" NUMBER,"IS_DISPLAYED" CHAR(1)) ;/---------------------------------------------------------- DDL for Table BOM_TRAIL--------------------------------------------------------CREATE TABLE "BOM_TRAIL"( "BOM_ID" NUMBER,"TRAIL_ACTION" VARCHAR2(4000),"TRAIL_COMMENTS" VARCHAR2(4000),"USER_ID" NUMBER,"DATE_TIME_STAMP" DATE) ;/---------------------------------------------------------- DDL for Table BOS_TYPES--------------------------------------------------------CREATE TABLE "BOS_TYPES"( "BOS_ID" NUMBER,"BOS_TYPE" VARCHAR2(50),"BOS_ICON" VARCHAR2(50),"BOS_ORDER" NUMBER,"ELECTRONIC_NAME" VARCHAR2(50)) ;/---------------------------------------------------------- DDL for Table BRANCHES--------------------------------------------------------CREATE TABLE "BRANCHES"( "BRANCH_ID" NUMBER,"PROJ_ID" NUMBER,"BRANCH_NAME" VARCHAR2(255),"RM_VTREE_FK" NUMBER,"IS_HIDDEN" CHAR(1),"BRANCH_COMMENTS" VARCHAR2(4000)) ;/---------------------------------------------------------- DDL for Table DEF_ACTION_BUTTONS--------------------------------------------------------CREATE TABLE "DEF_ACTION_BUTTONS"( "ABTN_ID" NUMBER,"ABTN_NAME" VARCHAR2(1000),"TEXT" VARCHAR2(50),"ACTION_LINK" VARCHAR2(4000),"EVENT_HANDLER" VARCHAR2(4000),"IMG_ENABLED" VARCHAR2(1000),"IMG_DISABLED" VARCHAR2(1000),"HINT" VARCHAR2(1000),"VISIBLE" CHAR(1),"ACTIVE" CHAR(1),"IS_READONLY_ACTION" CHAR(1)) ;/---------------------------------------------------------- DDL for Table DEF_MENU_ITEMS--------------------------------------------------------CREATE TABLE "DEF_MENU_ITEMS"( "ITEM_ID" NUMBER,"ITEM_NAME" VARCHAR2(1000),"TEXT" VARCHAR2(255),"ACTION_LINK" VARCHAR2(255),"EVENT_HANDLER" VARCHAR2(4000),"IMG_ENABLED" VARCHAR2(4000),"IMG_DISABLED" VARCHAR2(4000),"HINT" VARCHAR2(50),"VISIBLE" CHAR(1),"ACTIVE" CHAR(1)) ;/---------------------------------------------------------- DDL for Table DM_PROJECTS--------------------------------------------------------CREATE TABLE "DM_PROJECTS"( "PROJ_ID" NUMBER,"PROJ_NAME" VARCHAR2(255),"RM_PROJECTS_FK" NUMBER,"IS_HIDDEN" CHAR(1),"IS_DOWNLOAD" CHAR(1)) ;/---------------------------------------------------------- DDL for Table HARDWARE--------------------------------------------------------CREATE TABLE "HARDWARE"( "HARDWARE_NAME_ID" NUMBER,"BOM_ID" NUMBER,"HARDWARE_NAME" VARCHAR2(255),"TIER_NAME" VARCHAR2(255),"OLD_HARDWARE_NAME_ID" NUMBER) ;/---------------------------------------------------------- DDL for Table HARDWARE_NODE--------------------------------------------------------CREATE TABLE "HARDWARE_NODE"( "HARDWARE_NAME_ID" NUMBER,"NODE_ID" NUMBER) ;/---------------------------------------------------------- DDL for Table HIDE_PRODUCTS--------------------------------------------------------CREATE TABLE "HIDE_PRODUCTS"( "OS_ID" NUMBER,"PROD_ID" NUMBER) ;/---------------------------------------------------------- DDL for Table KNOWLEDGE_BASE--------------------------------------------------------CREATE TABLE "KNOWLEDGE_BASE"( "ARTICLE_ID" NUMBER,"ARTICLE_TITLE" VARCHAR2(4000),"ARTICLE_DETAILS" VARCHAR2(4000),"VISIBILITY_LEVEL_FK" NUMBER,"IS_READONLY" CHAR(1),"CREATED_STAMP" DATE,"CREATOR_ID" NUMBER,"MOD_STAMP" DATE,"MODIFIER_ID" NUMBER) ;/---------------------------------------------------------- DDL for Table MESSAGE_BOARD--------------------------------------------------------CREATE TABLE "MESSAGE_BOARD"( "MSG_ID" NUMBER,"MSG_DETAILS" VARCHAR2(2000),"SUBMITION_DATE" DATE,"EXPIRY_DATE" DATE,"DUE_DATE" DATE) ;/---------------------------------------------------------- DDL for Table NETWORK_NODES--------------------------------------------------------CREATE TABLE "NETWORK_NODES"( "NODE_ID" NUMBER,"NODE_TYPE_ID" NUMBER,"NODE_NAME" VARCHAR2(255),"NODE_COMMENTS" VARCHAR2(4000),"OLD_NODE_ID" NUMBER,"NODE_VERSION" VARCHAR2(4000),"NODE_LIFE_CYCLE" NUMBER,"NOT_XML" VARCHAR2(2)) ;/---------------------------------------------------------- DDL for Table NODE_TRAIL--------------------------------------------------------CREATE TABLE "NODE_TRAIL"( "NODE_ID" NUMBER,"TRAIL_ACTION" VARCHAR2(4000),"USER_ID" NUMBER,"DATE_TIME_STAMP" DATE) ;/---------------------------------------------------------- DDL for Table NODE_TYPES--------------------------------------------------------CREATE TABLE "NODE_TYPES"( "NODE_TYPE_ID" NUMBER,"NODE_TYPE" VARCHAR2(50),"NODE_ICON" VARCHAR2(50),"NODE_IMAGE" VARCHAR2(50),"NODE_ORDER" NUMBER) ;/---------------------------------------------------------- DDL for Table NOTIFY_USER--------------------------------------------------------CREATE TABLE "NOTIFY_USER"( "PROJ_ID" NUMBER,"USER_ID" NUMBER,"EVENT_ENUM" NUMBER) ;/---------------------------------------------------------- DDL for Table OPERATING_SYSTEMS--------------------------------------------------------CREATE TABLE "OPERATING_SYSTEMS"( "OS_ID" NUMBER,"OS_NAME" VARCHAR2(50),"NODE_ID" NUMBER,"OS_COMMENTS" VARCHAR2(4000),"OLD_OS_ID" NUMBER,"NODE_SPEC_FILE" VARCHAR2(4000),"NOT_XML" VARCHAR2(2)) ;/---------------------------------------------------------- DDL for Table OS_BASE_ENV--------------------------------------------------------CREATE TABLE "OS_BASE_ENV"( "OS_ID" NUMBER,"BASE_ENV_ID" NUMBER) ;/---------------------------------------------------------- DDL for Table OS_CONTENTS--------------------------------------------------------CREATE TABLE "OS_CONTENTS"( "OS_ID" NUMBER,"SEQ_NUM" NUMBER,"PROD_ID" NUMBER,"PRODUCT_COMMENTS" VARCHAR2(4000),"CHANGE_LOG_FLAG" CHAR(1),"IS_NODE_SPEC" VARCHAR2(2),"NOT_XML" VARCHAR2(2)) ;COMMENT ON COLUMN "OS_CONTENTS"."CHANGE_LOG_FLAG" IS 'cvn';COMMENT ON TABLE "OS_CONTENTS" IS 'CHANGE_LOG_FLAG:N - newU - updated version';/---------------------------------------------------------- DDL for Table OS_TRAIL--------------------------------------------------------CREATE TABLE "OS_TRAIL"( "OS_ID" NUMBER,"TRAIL_ACTION" VARCHAR2(4000),"USER_ID" NUMBER,"DATE_TIME_STAMP" DATE) ;/---------------------------------------------------------- DDL for Table PACKAGE_TRAIL--------------------------------------------------------CREATE TABLE "PACKAGE_TRAIL"( "PKG_ID" NUMBER,"TRAIL_ACTION" VARCHAR2(4000),"USER_ID" NUMBER,"DATE_TIME_STAMP" DATE) ;/---------------------------------------------------------- DDL for Table PROCESSES_CONFIG--------------------------------------------------------CREATE TABLE "PROCESSES_CONFIG"( "PROC_ID" NUMBER,"PROD_ID" NUMBER,"OS_ID" NUMBER,"PKG_HEALTH_TAG" VARCHAR2(255),"CMD_INTERFACE" VARCHAR2(4000),"PKG_OWNER" VARCHAR2(4000),"IS_INTERFACE" CHAR(1),"PROC_DESCRIPTION" VARCHAR2(4000),"PKG_ID" NUMBER) ;/---------------------------------------------------------- DDL for Table PRODUCTION_BOM--------------------------------------------------------CREATE TABLE "PRODUCTION_BOM"( "PROJ_ID" NUMBER,"BOM_ID" NUMBER,"STATE_ID" NUMBER) ;/---------------------------------------------------------- DDL for Table PRODUCTS_QUEUE--------------------------------------------------------CREATE TABLE "PRODUCTS_QUEUE"( "PRODQ_ID" NUMBER,"PROJ_ID" NUMBER,"PROD_ID" NUMBER,"ARRIVAL_STAMP" DATE) ;/---------------------------------------------------------- DDL for Table PRODUCT_DETAILS--------------------------------------------------------CREATE TABLE "PRODUCT_DETAILS"( "PROD_ID" NUMBER,"IS_REJECTED" CHAR(1)) ;/---------------------------------------------------------- DDL for Table PRODUCT_NOTES--------------------------------------------------------CREATE TABLE "PRODUCT_NOTES"( "PROD_ID" NUMBER,"NOTES" VARCHAR2(4000)) ;/---------------------------------------------------------- DDL for Table PRODUCT_TRAIL--------------------------------------------------------CREATE TABLE "PRODUCT_TRAIL"( "PROD_ID" NUMBER,"TRAIL_ACTION" VARCHAR2(4000),"USER_ID" NUMBER,"DATE_TIME_STAMP" DATE) ;/---------------------------------------------------------- DDL for Table REJECTION_TRAIL--------------------------------------------------------CREATE TABLE "REJECTION_TRAIL"( "ENUM_ENTITY_TYPE" NUMBER,"ENTITY_ID" NUMBER,"REJECT_SEQ" NUMBER,"IS_REJECTED" CHAR(1),"COMMENTS" VARCHAR2(4000),"DATE_TIME_STAMP" DATE,"USER_ID" NUMBER) ;/---------------------------------------------------------- DDL for Table RELATED_ARTICLES--------------------------------------------------------CREATE TABLE "RELATED_ARTICLES"( "ARTICLE_ID" NUMBER,"RARTICLE_ID" NUMBER) ;/---------------------------------------------------------- DDL for Table RELEASE_AUTHORISATION--------------------------------------------------------CREATE TABLE "RELEASE_AUTHORISATION"( "PV_ID" NUMBER,"BOM_ID" NUMBER,"TESTER_ID" NUMBER,"TESTER_COMMENTS" VARCHAR2(4000),"TESTER_DATESTAMP" DATE,"MANAGER_ID" NUMBER,"MANAGER_COMMENTS" VARCHAR2(4000),"MANAGER_DATESTAMP" DATE,"IS_OFFICIAL" CHAR(1 CHAR)) ;/---------------------------------------------------------- DDL for Table STATES--------------------------------------------------------CREATE TABLE "STATES"( "STATE_ID" NUMBER,"PROJ_ID" NUMBER,"STATE_NAME" VARCHAR2(255),"STATE_SEQ" NUMBER,"STATE_TYPE_ENUM" NUMBER,"IS_DOWNLOAD" CHAR(1)) ;COMMENT ON TABLE "STATES" IS '1 Integration2 Released To Testing3 Released To Region4 Released To Customer';/---------------------------------------------------------- DDL for Table SYSTEM_CONFIGURATION--------------------------------------------------------CREATE TABLE "SYSTEM_CONFIGURATION"( "ITEM_NAME" VARCHAR2(255),"ITEM_VALUE" VARCHAR2(4000),"ITEM_DESCRIPTION" VARCHAR2(4000)) ;/---------------------------------------------------------- DDL for Table TABLEOFCONTENTS--------------------------------------------------------CREATE TABLE "TABLEOFCONTENTS"( "GROUPER" VARCHAR2(1000),"PAGE" NUMBER(*,0),"DATETIME" DATE) ;/---------------------------------------------------------- DDL for Table TEST_TYPES--------------------------------------------------------CREATE TABLE "TEST_TYPES"( "TEST_TYPE_ID" NUMBER,"TEST_TYPE_NAME" VARCHAR2(50),"DISPLAY_ORDER" NUMBER) ;/---------------------------------------------------------- DDL for Table UNIT_TESTS--------------------------------------------------------CREATE TABLE "UNIT_TESTS"( "TEST_ID" NUMBER,"PV_ID" NUMBER,"TEST_TYPES_FK" NUMBER,"TEST_SUMMARY" VARCHAR2(4000),"COMPLETION_DATE" DATE,"COMPLETED_BY" NUMBER,"RESULTS_URL" VARCHAR2(2000),"RESULTS_ATTACHMENT_NAME" VARCHAR2(2000)) ;/---------------------------------------------------------- DDL for Table VALIDATION_RULES--------------------------------------------------------CREATE TABLE "VALIDATION_RULES"( "FIELD_NAME" VARCHAR2(1000),"IS_REQUIRED" CHAR(1),"IS_NUMERIC" CHAR(1),"MIN_NUMERIC_VALUE" NUMBER,"MAX_NUMERIC_VALUE" NUMBER,"IS_DATE" CHAR(1),"START_DATE" DATE,"END_DATE" DATE,"MIN_STRING_LENGTH" NUMBER,"MAX_STRING_LENGTH" NUMBER,"REGEXP" VARCHAR2(4000),"REGEXP_DESCRIPTION" VARCHAR2(50)) ;/---------------------------------------------------------- DDL for Index INX_BASE_ENV_BOM_ID--------------------------------------------------------CREATE INDEX "INX_BASE_ENV_BOM_ID" ON "BASE_ENV" ("BOM_ID");/---------------------------------------------------------- DDL for Index PK_BOS_TYPES--------------------------------------------------------CREATE UNIQUE INDEX "PK_BOS_TYPES" ON "BOS_TYPES" ("BOS_ID");/---------------------------------------------------------- DDL for Index PRODUCT_NOTES_PK--------------------------------------------------------CREATE UNIQUE INDEX "PRODUCT_NOTES_PK" ON "PRODUCT_NOTES" ("PROD_ID");/---------------------------------------------------------- DDL for Index INX_OLD_BASE_ENV_ID--------------------------------------------------------CREATE INDEX "INX_OLD_BASE_ENV_ID" ON "BASE_ENV" ("OLD_BASE_ENV_ID");/---------------------------------------------------------- DDL for Index PK_DM_PROJECTS--------------------------------------------------------CREATE UNIQUE INDEX "PK_DM_PROJECTS" ON "DM_PROJECTS" ("PROJ_ID");/---------------------------------------------------------- DDL for Index INX_OLD_OS_ID--------------------------------------------------------CREATE INDEX "INX_OLD_OS_ID" ON "OPERATING_SYSTEMS" ("OLD_OS_ID");/---------------------------------------------------------- DDL for Index PK_NODE_TYPES--------------------------------------------------------CREATE UNIQUE INDEX "PK_NODE_TYPES" ON "NODE_TYPES" ("NODE_TYPE_ID");/---------------------------------------------------------- DDL for Index PK_DEF_ACTION_BUTTONS--------------------------------------------------------CREATE UNIQUE INDEX "PK_DEF_ACTION_BUTTONS" ON "DEF_ACTION_BUTTONS" ("ABTN_ID");/---------------------------------------------------------- DDL for Index UNQ_HARDWARE_NODE--------------------------------------------------------CREATE UNIQUE INDEX "UNQ_HARDWARE_NODE" ON "HARDWARE_NODE" ("HARDWARE_NAME_ID", "NODE_ID");/---------------------------------------------------------- DDL for Index PK_BASE_ENV--------------------------------------------------------CREATE UNIQUE INDEX "PK_BASE_ENV" ON "BASE_ENV" ("BASE_ENV_ID");/---------------------------------------------------------- DDL for Index UNQ_HARDWARE--------------------------------------------------------CREATE UNIQUE INDEX "UNQ_HARDWARE" ON "HARDWARE" ("BOM_ID", "HARDWARE_NAME");/---------------------------------------------------------- DDL for Index INX_KB_ARTICLE_DETAILS--------------------------------------------------------CREATE INDEX "INX_KB_ARTICLE_DETAILS" ON "KNOWLEDGE_BASE" ("ARTICLE_DETAILS");/---------------------------------------------------------- DDL for Index UNQ_OS_BASE_ENV--------------------------------------------------------CREATE UNIQUE INDEX "UNQ_OS_BASE_ENV" ON "OS_BASE_ENV" ("OS_ID", "BASE_ENV_ID");/---------------------------------------------------------- DDL for Index UNQ_BOS_TYPES--------------------------------------------------------CREATE UNIQUE INDEX "UNQ_BOS_TYPES" ON "BOS_TYPES" ("BOS_TYPE");/---------------------------------------------------------- DDL for Index INX_OS_NODE_ID--------------------------------------------------------CREATE INDEX "INX_OS_NODE_ID" ON "OPERATING_SYSTEMS" ("NODE_ID");/---------------------------------------------------------- DDL for Index INX_OLD_HARDWARE_NAME_ID--------------------------------------------------------CREATE INDEX "INX_OLD_HARDWARE_NAME_ID" ON "HARDWARE" ("OLD_HARDWARE_NAME_ID");/---------------------------------------------------------- DDL for Index INX_BOM_NOTES--------------------------------------------------------CREATE INDEX "INX_BOM_NOTES" ON "BOM_NOTES" ("BOM_ID", "BOM_SCOPE", "SCOPE_ID");/---------------------------------------------------------- DDL for Index INX_PC_PROC_ID--------------------------------------------------------CREATE INDEX "INX_PC_PROC_ID" ON "PROCESSES_CONFIG" ("PROC_ID");/---------------------------------------------------------- DDL for Index UNQ_NODE_TYPES--------------------------------------------------------CREATE UNIQUE INDEX "UNQ_NODE_TYPES" ON "NODE_TYPES" ("NODE_TYPE");/---------------------------------------------------------- DDL for Index UNQ_OS_CONTENTS--------------------------------------------------------CREATE UNIQUE INDEX "UNQ_OS_CONTENTS" ON "OS_CONTENTS" ("OS_ID", "PROD_ID");/---------------------------------------------------------- DDL for Index INX_BT_BOM_ID--------------------------------------------------------CREATE INDEX "INX_BT_BOM_ID" ON "BOM_TRAIL" ("BOM_ID");/---------------------------------------------------------- DDL for Index INX_BO_BOM_NAME_ID--------------------------------------------------------CREATE INDEX "INX_BO_BOM_NAME_ID" ON "BOMS" ("BOM_NAME_ID");/---------------------------------------------------------- DDL for Index INX_BO_BRANCH_ID--------------------------------------------------------CREATE INDEX "INX_BO_BRANCH_ID" ON "BOMS" ("BRANCH_ID");/---------------------------------------------------------- DDL for Index UNQ_POPUP_MENU_DEF--------------------------------------------------------CREATE UNIQUE INDEX "UNQ_POPUP_MENU_DEF" ON "DEF_MENU_ITEMS" ("ITEM_NAME");/---------------------------------------------------------- DDL for Index INX_PC_OS_ID--------------------------------------------------------CREATE INDEX "INX_PC_OS_ID" ON "PROCESSES_CONFIG" ("OS_ID");/---------------------------------------------------------- DDL for Index UNQ_ABTN_DEF--------------------------------------------------------CREATE UNIQUE INDEX "UNQ_ABTN_DEF" ON "DEF_ACTION_BUTTONS" ("ABTN_NAME");/---------------------------------------------------------- DDL for Index PK_NETWORK_NODES--------------------------------------------------------CREATE UNIQUE INDEX "PK_NETWORK_NODES" ON "NETWORK_NODES" ("NODE_ID");/---------------------------------------------------------- DDL for Index INX_BOM_NOTES_BOM_ID--------------------------------------------------------CREATE INDEX "INX_BOM_NOTES_BOM_ID" ON "BOM_NOTES" ("BOM_ID");/---------------------------------------------------------- DDL for Index INX_MSGBOARD--------------------------------------------------------CREATE INDEX "INX_MSGBOARD" ON "MESSAGE_BOARD" ("EXPIRY_DATE", "DUE_DATE");/---------------------------------------------------------- DDL for Index INX_OBE_OS_ID--------------------------------------------------------CREATE INDEX "INX_OBE_OS_ID" ON "OS_BASE_ENV" ("OS_ID");/---------------------------------------------------------- DDL for Index INX_HN_NODE_ID--------------------------------------------------------CREATE INDEX "INX_HN_NODE_ID" ON "HARDWARE_NODE" ("NODE_ID");/---------------------------------------------------------- DDL for Index INX_HN_HW_ID--------------------------------------------------------CREATE INDEX "INX_HN_HW_ID" ON "HARDWARE_NODE" ("HARDWARE_NAME_ID");/---------------------------------------------------------- DDL for Index INX_OLD_NODE_ID--------------------------------------------------------CREATE INDEX "INX_OLD_NODE_ID" ON "NETWORK_NODES" ("OLD_NODE_ID");/---------------------------------------------------------- DDL for Index UNQ_PROC_CONFIG--------------------------------------------------------CREATE INDEX "UNQ_PROC_CONFIG" ON "PROCESSES_CONFIG" ("PROD_ID", "OS_ID", "PKG_HEALTH_TAG");/---------------------------------------------------------- DDL for Index INX_HARDWARE_BOM_ID--------------------------------------------------------CREATE INDEX "INX_HARDWARE_BOM_ID" ON "HARDWARE" ("BOM_ID");/---------------------------------------------------------- DDL for Index INX_BO_BOM_VERSION--------------------------------------------------------CREATE INDEX "INX_BO_BOM_VERSION" ON "BOMS" ("BOM_VERSION");/---------------------------------------------------------- DDL for Index UNQ_DM_PROJECTS--------------------------------------------------------CREATE UNIQUE INDEX "UNQ_DM_PROJECTS" ON "DM_PROJECTS" ("PROJ_NAME");/---------------------------------------------------------- DDL for Index PK_BOMS--------------------------------------------------------CREATE UNIQUE INDEX "PK_BOMS" ON "BOMS" ("BOM_ID");/---------------------------------------------------------- DDL for Index UNQ_BASE_ENV--------------------------------------------------------CREATE UNIQUE INDEX "UNQ_BASE_ENV" ON "BASE_ENV" ("BASE_ENV_NAME", "BOM_ID");/---------------------------------------------------------- DDL for Index INX_ST_PROJ_ID--------------------------------------------------------CREATE INDEX "INX_ST_PROJ_ID" ON "STATES" ("PROJ_ID");/---------------------------------------------------------- DDL for Index PK_BRANCHES--------------------------------------------------------CREATE UNIQUE INDEX "PK_BRANCHES" ON "BRANCHES" ("BRANCH_ID");/---------------------------------------------------------- DDL for Index UNQ_VALIDATION_RULES--------------------------------------------------------CREATE UNIQUE INDEX "UNQ_VALIDATION_RULES" ON "VALIDATION_RULES" ("FIELD_NAME");/---------------------------------------------------------- DDL for Index UNQ_BOM_NAMES--------------------------------------------------------CREATE UNIQUE INDEX "UNQ_BOM_NAMES" ON "BOM_NAMES" ("BOM_NAME");/---------------------------------------------------------- DDL for Index PK_DEF_MENU_ITEMS--------------------------------------------------------CREATE UNIQUE INDEX "PK_DEF_MENU_ITEMS" ON "DEF_MENU_ITEMS" ("ITEM_ID");/---------------------------------------------------------- DDL for Index UNQ_STATES--------------------------------------------------------CREATE UNIQUE INDEX "UNQ_STATES" ON "STATES" ("PROJ_ID", "STATE_NAME");/---------------------------------------------------------- DDL for Index PK_STATES--------------------------------------------------------CREATE UNIQUE INDEX "PK_STATES" ON "STATES" ("STATE_ID");/---------------------------------------------------------- DDL for Index INX_BR_PROJ_ID--------------------------------------------------------CREATE INDEX "INX_BR_PROJ_ID" ON "BRANCHES" ("PROJ_ID");/---------------------------------------------------------- DDL for Index INX_OSC_PROD_ID--------------------------------------------------------CREATE INDEX "INX_OSC_PROD_ID" ON "OS_CONTENTS" ("PROD_ID");/---------------------------------------------------------- DDL for Index PKSYSTEM_CONFIGURATION1--------------------------------------------------------CREATE UNIQUE INDEX "PKSYSTEM_CONFIGURATION1" ON "SYSTEM_CONFIGURATION" ("ITEM_NAME");/---------------------------------------------------------- DDL for Index UNQ_BASE_ENV_CONTS--------------------------------------------------------CREATE UNIQUE INDEX "UNQ_BASE_ENV_CONTS" ON "BASE_ENV_CONTENTS" ("BASE_ENV_ID", "PROD_ID");/---------------------------------------------------------- DDL for Index UNQ_BOM_STATE--------------------------------------------------------CREATE UNIQUE INDEX "UNQ_BOM_STATE" ON "BOM_STATE" ("BOM_ID", "STATE_ID");/---------------------------------------------------------- DDL for Index PK_KNOWLEDGE_BASE--------------------------------------------------------CREATE UNIQUE INDEX "PK_KNOWLEDGE_BASE" ON "KNOWLEDGE_BASE" ("ARTICLE_ID");/---------------------------------------------------------- DDL for Index UNQ_RELATED_ARTICLES--------------------------------------------------------CREATE UNIQUE INDEX "UNQ_RELATED_ARTICLES" ON "RELATED_ARTICLES" ("ARTICLE_ID", "RARTICLE_ID");/---------------------------------------------------------- DDL for Index INX_BS_BOM_ID--------------------------------------------------------CREATE INDEX "INX_BS_BOM_ID" ON "BOM_STATE" ("BOM_ID");/---------------------------------------------------------- DDL for Index INX_PC_PROD_ID--------------------------------------------------------CREATE INDEX "INX_PC_PROD_ID" ON "PROCESSES_CONFIG" ("PROD_ID");/---------------------------------------------------------- DDL for Index INX_BEC_BASE_ENV_ID--------------------------------------------------------CREATE INDEX "INX_BEC_BASE_ENV_ID" ON "BASE_ENV_CONTENTS" ("BASE_ENV_ID");/---------------------------------------------------------- DDL for Index INX_OBE_BASE_ENV_ID--------------------------------------------------------CREATE INDEX "INX_OBE_BASE_ENV_ID" ON "OS_BASE_ENV" ("BASE_ENV_ID");/---------------------------------------------------------- DDL for Index UNQ_BOM_CONTENTS--------------------------------------------------------CREATE UNIQUE INDEX "UNQ_BOM_CONTENTS" ON "BOM_CONTENTS" ("BOM_ID", "NODE_ID");/---------------------------------------------------------- DDL for Index INX_BO_OLD_BOM_ID--------------------------------------------------------CREATE INDEX "INX_BO_OLD_BOM_ID" ON "BOMS" ("OLD_BOM_ID");/---------------------------------------------------------- DDL for Index UNQ_OPERATING_SYSTEMS--------------------------------------------------------CREATE UNIQUE INDEX "UNQ_OPERATING_SYSTEMS" ON "OPERATING_SYSTEMS" ("OS_NAME", "NODE_ID");/---------------------------------------------------------- DDL for Index PK_MESSAGE_BOARD--------------------------------------------------------CREATE UNIQUE INDEX "PK_MESSAGE_BOARD" ON "MESSAGE_BOARD" ("MSG_ID");/---------------------------------------------------------- DDL for Index INX_KB_MOD_STAMP--------------------------------------------------------CREATE INDEX "INX_KB_MOD_STAMP" ON "KNOWLEDGE_BASE" ("MOD_STAMP");/---------------------------------------------------------- DDL for Index INX_BO_PARENT_BOM_ID--------------------------------------------------------CREATE INDEX "INX_BO_PARENT_BOM_ID" ON "BOMS" ("PARENT_BOM_ID");/---------------------------------------------------------- DDL for Index INX_KB_ARTICLE_TITLE--------------------------------------------------------CREATE INDEX "INX_KB_ARTICLE_TITLE" ON "KNOWLEDGE_BASE" ("ARTICLE_TITLE");/---------------------------------------------------------- DDL for Index UNQ_REJECTION_TRAIL--------------------------------------------------------CREATE UNIQUE INDEX "UNQ_REJECTION_TRAIL" ON "REJECTION_TRAIL" ("ENUM_ENTITY_TYPE", "ENTITY_ID", "REJECT_SEQ");/---------------------------------------------------------- DDL for Index PK_OPERATING_SYSTEMS--------------------------------------------------------CREATE UNIQUE INDEX "PK_OPERATING_SYSTEMS" ON "OPERATING_SYSTEMS" ("OS_ID");/---------------------------------------------------------- DDL for Index PK_BOM_NAMES--------------------------------------------------------CREATE UNIQUE INDEX "PK_BOM_NAMES" ON "BOM_NAMES" ("BOM_NAME_ID");/---------------------------------------------------------- DDL for Index PKPRODUCT_DETAILS--------------------------------------------------------CREATE UNIQUE INDEX "PKPRODUCT_DETAILS" ON "PRODUCT_DETAILS" ("PROD_ID");/---------------------------------------------------------- DDL for Index INX_ACTION_TRAIL--------------------------------------------------------CREATE INDEX "INX_ACTION_TRAIL" ON "ACTION_TRAIL" ("ACTION_STAMP", "USER_ID", "ACTION_EVENT");/---------------------------------------------------------- DDL for Index PK_HARDWARE--------------------------------------------------------CREATE UNIQUE INDEX "PK_HARDWARE" ON "HARDWARE" ("HARDWARE_NAME_ID");/---------------------------------------------------------- DDL for Index INX_OSC_OS_ID--------------------------------------------------------CREATE INDEX "INX_OSC_OS_ID" ON "OS_CONTENTS" ("OS_ID");/---------------------------------------------------------- DDL for Index INX_BO_BOM_LIFECYCLE--------------------------------------------------------CREATE INDEX "INX_BO_BOM_LIFECYCLE" ON "BOMS" ("BOM_LIFECYCLE");/---------------------------------------------------------- DDL for Index UNQ_BRANCHES--------------------------------------------------------CREATE UNIQUE INDEX "UNQ_BRANCHES" ON "BRANCHES" ("PROJ_ID", "BRANCH_NAME");/---------------------------------------------------------- Constraints for Table HIDE_PRODUCTS--------------------------------------------------------ALTER TABLE "HIDE_PRODUCTS" MODIFY ("PROD_ID" NOT NULL ENABLE);ALTER TABLE "HIDE_PRODUCTS" MODIFY ("OS_ID" NOT NULL ENABLE);/---------------------------------------------------------- Constraints for Table BASE_ENV--------------------------------------------------------ALTER TABLE "BASE_ENV" ADD CONSTRAINT "PK_BASE_ENV" PRIMARY KEY ("BASE_ENV_ID") ENABLE;ALTER TABLE "BASE_ENV" MODIFY ("BOM_ID" NOT NULL ENABLE);ALTER TABLE "BASE_ENV" MODIFY ("BASE_ENV_NAME" NOT NULL ENABLE);ALTER TABLE "BASE_ENV" MODIFY ("BOS_ID" NOT NULL ENABLE);ALTER TABLE "BASE_ENV" MODIFY ("BASE_ENV_ID" NOT NULL ENABLE);/---------------------------------------------------------- Constraints for Table OS_CONTENTS--------------------------------------------------------ALTER TABLE "OS_CONTENTS" MODIFY ("PROD_ID" NOT NULL ENABLE);ALTER TABLE "OS_CONTENTS" MODIFY ("SEQ_NUM" NOT NULL ENABLE);ALTER TABLE "OS_CONTENTS" MODIFY ("OS_ID" NOT NULL ENABLE);/---------------------------------------------------------- Constraints for Table BOM_NAMES--------------------------------------------------------ALTER TABLE "BOM_NAMES" ADD CONSTRAINT "PK_BOM_NAMES" PRIMARY KEY ("BOM_NAME_ID") ENABLE;ALTER TABLE "BOM_NAMES" MODIFY ("BOM_NAME" NOT NULL ENABLE);ALTER TABLE "BOM_NAMES" MODIFY ("BOM_NAME_ID" NOT NULL ENABLE);/---------------------------------------------------------- Constraints for Table UNIT_TESTS--------------------------------------------------------ALTER TABLE "UNIT_TESTS" MODIFY ("TEST_TYPES_FK" NOT NULL ENABLE);ALTER TABLE "UNIT_TESTS" MODIFY ("PV_ID" NOT NULL ENABLE);ALTER TABLE "UNIT_TESTS" MODIFY ("TEST_ID" NOT NULL ENABLE);/---------------------------------------------------------- Constraints for Table SYSTEM_CONFIGURATION--------------------------------------------------------ALTER TABLE "SYSTEM_CONFIGURATION" ADD CONSTRAINT "PKSYSTEM_CONFIGURATION1" PRIMARY KEY ("ITEM_NAME") ENABLE;ALTER TABLE "SYSTEM_CONFIGURATION" MODIFY ("ITEM_NAME" NOT NULL ENABLE);/---------------------------------------------------------- Constraints for Table PACKAGE_TRAIL--------------------------------------------------------ALTER TABLE "PACKAGE_TRAIL" MODIFY ("DATE_TIME_STAMP" NOT NULL ENABLE);ALTER TABLE "PACKAGE_TRAIL" MODIFY ("USER_ID" NOT NULL ENABLE);ALTER TABLE "PACKAGE_TRAIL" MODIFY ("TRAIL_ACTION" NOT NULL ENABLE);ALTER TABLE "PACKAGE_TRAIL" MODIFY ("PKG_ID" NOT NULL ENABLE);/---------------------------------------------------------- Constraints for Table OS_TRAIL--------------------------------------------------------ALTER TABLE "OS_TRAIL" MODIFY ("DATE_TIME_STAMP" NOT NULL ENABLE);ALTER TABLE "OS_TRAIL" MODIFY ("USER_ID" NOT NULL ENABLE);/---------------------------------------------------------- Constraints for Table BOS_TYPES--------------------------------------------------------ALTER TABLE "BOS_TYPES" ADD CONSTRAINT "PK_BOS_TYPES" PRIMARY KEY ("BOS_ID") ENABLE;ALTER TABLE "BOS_TYPES" MODIFY ("BOS_ICON" NOT NULL ENABLE);ALTER TABLE "BOS_TYPES" MODIFY ("BOS_TYPE" NOT NULL ENABLE);ALTER TABLE "BOS_TYPES" MODIFY ("BOS_ID" NOT NULL ENABLE);/---------------------------------------------------------- Constraints for Table MESSAGE_BOARD--------------------------------------------------------ALTER TABLE "MESSAGE_BOARD" MODIFY ("SUBMITION_DATE" NOT NULL ENABLE);ALTER TABLE "MESSAGE_BOARD" MODIFY ("MSG_DETAILS" NOT NULL ENABLE);ALTER TABLE "MESSAGE_BOARD" MODIFY ("MSG_ID" NOT NULL ENABLE);/---------------------------------------------------------- Constraints for Table HARDWARE--------------------------------------------------------ALTER TABLE "HARDWARE" ADD CONSTRAINT "PK_HARDWARE" PRIMARY KEY ("HARDWARE_NAME_ID") ENABLE;ALTER TABLE "HARDWARE" MODIFY ("HARDWARE_NAME" NOT NULL ENABLE);ALTER TABLE "HARDWARE" MODIFY ("BOM_ID" NOT NULL ENABLE);ALTER TABLE "HARDWARE" MODIFY ("HARDWARE_NAME_ID" NOT NULL ENABLE);/---------------------------------------------------------- Constraints for Table BOMS--------------------------------------------------------ALTER TABLE "BOMS" ADD CONSTRAINT "PK_BOMS" PRIMARY KEY ("BOM_ID") ENABLE;ALTER TABLE "BOMS" MODIFY ("PARENT_BOM_ID" NOT NULL ENABLE);ALTER TABLE "BOMS" MODIFY ("BOM_LIFECYCLE" NOT NULL ENABLE);ALTER TABLE "BOMS" MODIFY ("IS_HIDDEN" NOT NULL ENABLE);ALTER TABLE "BOMS" MODIFY ("IS_READONLY" NOT NULL ENABLE);ALTER TABLE "BOMS" MODIFY ("BOM_VERSION" NOT NULL ENABLE);ALTER TABLE "BOMS" MODIFY ("BOM_NAME_ID" NOT NULL ENABLE);ALTER TABLE "BOMS" MODIFY ("BRANCH_ID" NOT NULL ENABLE);ALTER TABLE "BOMS" MODIFY ("BOM_ID" NOT NULL ENABLE);/---------------------------------------------------------- Constraints for Table STATES--------------------------------------------------------ALTER TABLE "STATES" ADD CONSTRAINT "PK_STATES" PRIMARY KEY ("STATE_ID") ENABLE;ALTER TABLE "STATES" MODIFY ("STATE_TYPE_ENUM" NOT NULL ENABLE);ALTER TABLE "STATES" MODIFY ("STATE_SEQ" NOT NULL ENABLE);ALTER TABLE "STATES" MODIFY ("STATE_NAME" NOT NULL ENABLE);ALTER TABLE "STATES" MODIFY ("PROJ_ID" NOT NULL ENABLE);ALTER TABLE "STATES" MODIFY ("STATE_ID" NOT NULL ENABLE);/---------------------------------------------------------- Constraints for Table NODE_TYPES--------------------------------------------------------ALTER TABLE "NODE_TYPES" ADD CONSTRAINT "PK_NODE_TYPES" PRIMARY KEY ("NODE_TYPE_ID") ENABLE;ALTER TABLE "NODE_TYPES" MODIFY ("NODE_IMAGE" NOT NULL ENABLE);ALTER TABLE "NODE_TYPES" MODIFY ("NODE_ICON" NOT NULL ENABLE);ALTER TABLE "NODE_TYPES" MODIFY ("NODE_TYPE" NOT NULL ENABLE);ALTER TABLE "NODE_TYPES" MODIFY ("NODE_TYPE_ID" NOT NULL ENABLE);/---------------------------------------------------------- Constraints for Table BOM_STATE--------------------------------------------------------ALTER TABLE "BOM_STATE" MODIFY ("IS_DISPLAYED" NOT NULL ENABLE);ALTER TABLE "BOM_STATE" MODIFY ("USER_ID" NOT NULL ENABLE);ALTER TABLE "BOM_STATE" MODIFY ("STATE_ID" NOT NULL ENABLE);ALTER TABLE "BOM_STATE" MODIFY ("BOM_ID" NOT NULL ENABLE);/---------------------------------------------------------- Constraints for Table HARDWARE_NODE--------------------------------------------------------ALTER TABLE "HARDWARE_NODE" MODIFY ("NODE_ID" NOT NULL ENABLE);ALTER TABLE "HARDWARE_NODE" MODIFY ("HARDWARE_NAME_ID" NOT NULL ENABLE);/---------------------------------------------------------- Constraints for Table NODE_TRAIL--------------------------------------------------------ALTER TABLE "NODE_TRAIL" MODIFY ("DATE_TIME_STAMP" NOT NULL ENABLE);ALTER TABLE "NODE_TRAIL" MODIFY ("USER_ID" NOT NULL ENABLE);ALTER TABLE "NODE_TRAIL" MODIFY ("NODE_ID" NOT NULL ENABLE);/---------------------------------------------------------- Constraints for Table PRODUCTS_QUEUE--------------------------------------------------------ALTER TABLE "PRODUCTS_QUEUE" MODIFY ("ARRIVAL_STAMP" NOT NULL ENABLE);ALTER TABLE "PRODUCTS_QUEUE" MODIFY ("PROD_ID" NOT NULL ENABLE);ALTER TABLE "PRODUCTS_QUEUE" MODIFY ("PROJ_ID" NOT NULL ENABLE);/---------------------------------------------------------- Constraints for Table DEF_ACTION_BUTTONS--------------------------------------------------------ALTER TABLE "DEF_ACTION_BUTTONS" ADD CONSTRAINT "PK_DEF_ACTION_BUTTONS" PRIMARY KEY ("ABTN_ID") ENABLE;ALTER TABLE "DEF_ACTION_BUTTONS" MODIFY ("IS_READONLY_ACTION" NOT NULL ENABLE);ALTER TABLE "DEF_ACTION_BUTTONS" MODIFY ("ACTIVE" NOT NULL ENABLE);ALTER TABLE "DEF_ACTION_BUTTONS" MODIFY ("VISIBLE" NOT NULL ENABLE);ALTER TABLE "DEF_ACTION_BUTTONS" MODIFY ("ABTN_NAME" NOT NULL ENABLE);ALTER TABLE "DEF_ACTION_BUTTONS" MODIFY ("ABTN_ID" NOT NULL ENABLE);/---------------------------------------------------------- Constraints for Table KNOWLEDGE_BASE--------------------------------------------------------ALTER TABLE "KNOWLEDGE_BASE" ADD CONSTRAINT "PK_KNOWLEDGE_BASE" PRIMARY KEY ("ARTICLE_ID") ENABLE;ALTER TABLE "KNOWLEDGE_BASE" MODIFY ("MODIFIER_ID" NOT NULL ENABLE);ALTER TABLE "KNOWLEDGE_BASE" MODIFY ("MOD_STAMP" NOT NULL ENABLE);ALTER TABLE "KNOWLEDGE_BASE" MODIFY ("CREATOR_ID" NOT NULL ENABLE);ALTER TABLE "KNOWLEDGE_BASE" MODIFY ("CREATED_STAMP" NOT NULL ENABLE);ALTER TABLE "KNOWLEDGE_BASE" MODIFY ("VISIBILITY_LEVEL_FK" NOT NULL ENABLE);ALTER TABLE "KNOWLEDGE_BASE" MODIFY ("ARTICLE_DETAILS" NOT NULL ENABLE);ALTER TABLE "KNOWLEDGE_BASE" MODIFY ("ARTICLE_TITLE" NOT NULL ENABLE);ALTER TABLE "KNOWLEDGE_BASE" MODIFY ("ARTICLE_ID" NOT NULL ENABLE);/---------------------------------------------------------- Constraints for Table BOM_TRAIL--------------------------------------------------------ALTER TABLE "BOM_TRAIL" MODIFY ("DATE_TIME_STAMP" NOT NULL ENABLE);ALTER TABLE "BOM_TRAIL" MODIFY ("USER_ID" NOT NULL ENABLE);ALTER TABLE "BOM_TRAIL" MODIFY ("TRAIL_ACTION" NOT NULL ENABLE);ALTER TABLE "BOM_TRAIL" MODIFY ("BOM_ID" NOT NULL ENABLE);/---------------------------------------------------------- Constraints for Table TEST_TYPES--------------------------------------------------------ALTER TABLE "TEST_TYPES" ADD PRIMARY KEY ("TEST_TYPE_ID") ENABLE;ALTER TABLE "TEST_TYPES" MODIFY ("TEST_TYPE_NAME" NOT NULL ENABLE);ALTER TABLE "TEST_TYPES" MODIFY ("TEST_TYPE_ID" NOT NULL ENABLE);/---------------------------------------------------------- Constraints for Table PRODUCT_TRAIL--------------------------------------------------------ALTER TABLE "PRODUCT_TRAIL" MODIFY ("DATE_TIME_STAMP" NOT NULL ENABLE);ALTER TABLE "PRODUCT_TRAIL" MODIFY ("USER_ID" NOT NULL ENABLE);/---------------------------------------------------------- Constraints for Table BOM_STATE20100609--------------------------------------------------------ALTER TABLE "BOM_STATE20100609" MODIFY ("IS_DISPLAYED" NOT NULL ENABLE);ALTER TABLE "BOM_STATE20100609" MODIFY ("USER_ID" NOT NULL ENABLE);ALTER TABLE "BOM_STATE20100609" MODIFY ("STATE_ID" NOT NULL ENABLE);ALTER TABLE "BOM_STATE20100609" MODIFY ("BOM_ID" NOT NULL ENABLE);/---------------------------------------------------------- Constraints for Table ACTION_TRAIL--------------------------------------------------------ALTER TABLE "ACTION_TRAIL" MODIFY ("ACTION_EVENT" NOT NULL ENABLE);ALTER TABLE "ACTION_TRAIL" MODIFY ("USER_ID" NOT NULL ENABLE);ALTER TABLE "ACTION_TRAIL" MODIFY ("ACTION_STAMP" NOT NULL ENABLE);/---------------------------------------------------------- Constraints for Table PRODUCT_NOTES--------------------------------------------------------ALTER TABLE "PRODUCT_NOTES" ADD CONSTRAINT "PRODUCT_NOTES_PK" PRIMARY KEY ("PROD_ID") ENABLE;ALTER TABLE "PRODUCT_NOTES" MODIFY ("PROD_ID" NOT NULL ENABLE);/---------------------------------------------------------- Constraints for Table OS_BASE_ENV--------------------------------------------------------ALTER TABLE "OS_BASE_ENV" MODIFY ("BASE_ENV_ID" NOT NULL ENABLE);ALTER TABLE "OS_BASE_ENV" MODIFY ("OS_ID" NOT NULL ENABLE);/---------------------------------------------------------- Constraints for Table BASE_ENV_CONTENTS--------------------------------------------------------ALTER TABLE "BASE_ENV_CONTENTS" MODIFY ("SEQ_NUM" NOT NULL ENABLE);ALTER TABLE "BASE_ENV_CONTENTS" MODIFY ("PROD_ID" NOT NULL ENABLE);ALTER TABLE "BASE_ENV_CONTENTS" MODIFY ("BASE_ENV_ID" NOT NULL ENABLE);/---------------------------------------------------------- Constraints for Table VALIDATION_RULES--------------------------------------------------------ALTER TABLE "VALIDATION_RULES" MODIFY ("IS_REQUIRED" NOT NULL ENABLE);ALTER TABLE "VALIDATION_RULES" MODIFY ("FIELD_NAME" NOT NULL ENABLE);/---------------------------------------------------------- Constraints for Table NOTIFY_USER--------------------------------------------------------ALTER TABLE "NOTIFY_USER" MODIFY ("EVENT_ENUM" NOT NULL ENABLE);ALTER TABLE "NOTIFY_USER" MODIFY ("USER_ID" NOT NULL ENABLE);ALTER TABLE "NOTIFY_USER" MODIFY ("PROJ_ID" NOT NULL ENABLE);/---------------------------------------------------------- Constraints for Table BOM_NOTES--------------------------------------------------------ALTER TABLE "BOM_NOTES" MODIFY ("ARTICLE_ID" NOT NULL ENABLE);ALTER TABLE "BOM_NOTES" MODIFY ("SCOPE_ID" NOT NULL ENABLE);ALTER TABLE "BOM_NOTES" MODIFY ("BOM_SCOPE" NOT NULL ENABLE);ALTER TABLE "BOM_NOTES" MODIFY ("BOM_ID" NOT NULL ENABLE);/---------------------------------------------------------- Constraints for Table PRODUCTION_BOM--------------------------------------------------------ALTER TABLE "PRODUCTION_BOM" MODIFY ("BOM_ID" NOT NULL ENABLE);ALTER TABLE "PRODUCTION_BOM" MODIFY ("PROJ_ID" NOT NULL ENABLE);/---------------------------------------------------------- Constraints for Table NETWORK_NODES--------------------------------------------------------ALTER TABLE "NETWORK_NODES" ADD CONSTRAINT "PK_NETWORK_NODES" PRIMARY KEY ("NODE_ID") ENABLE;ALTER TABLE "NETWORK_NODES" MODIFY ("NODE_NAME" NOT NULL ENABLE);ALTER TABLE "NETWORK_NODES" MODIFY ("NODE_TYPE_ID" NOT NULL ENABLE);ALTER TABLE "NETWORK_NODES" MODIFY ("NODE_ID" NOT NULL ENABLE);/---------------------------------------------------------- Constraints for Table PRODUCT_DETAILS--------------------------------------------------------ALTER TABLE "PRODUCT_DETAILS" ADD CONSTRAINT "PKPRODUCT_DETAILS" PRIMARY KEY ("PROD_ID") ENABLE;ALTER TABLE "PRODUCT_DETAILS" MODIFY ("PROD_ID" NOT NULL ENABLE);/---------------------------------------------------------- Constraints for Table DM_PROJECTS--------------------------------------------------------ALTER TABLE "DM_PROJECTS" ADD CONSTRAINT "PK_DM_PROJECTS" PRIMARY KEY ("PROJ_ID") ENABLE;ALTER TABLE "DM_PROJECTS" MODIFY ("PROJ_NAME" NOT NULL ENABLE);ALTER TABLE "DM_PROJECTS" MODIFY ("PROJ_ID" NOT NULL ENABLE);/---------------------------------------------------------- Constraints for Table BRANCHES--------------------------------------------------------ALTER TABLE "BRANCHES" ADD CONSTRAINT "PK_BRANCHES" PRIMARY KEY ("BRANCH_ID") ENABLE;ALTER TABLE "BRANCHES" MODIFY ("BRANCH_NAME" NOT NULL ENABLE);ALTER TABLE "BRANCHES" MODIFY ("PROJ_ID" NOT NULL ENABLE);ALTER TABLE "BRANCHES" MODIFY ("BRANCH_ID" NOT NULL ENABLE);/---------------------------------------------------------- Constraints for Table BOM_CONTENTS--------------------------------------------------------ALTER TABLE "BOM_CONTENTS" MODIFY ("NODE_ID" NOT NULL ENABLE);ALTER TABLE "BOM_CONTENTS" MODIFY ("BOM_ID" NOT NULL ENABLE);/---------------------------------------------------------- Constraints for Table REJECTION_TRAIL--------------------------------------------------------ALTER TABLE "REJECTION_TRAIL" MODIFY ("USER_ID" NOT NULL ENABLE);ALTER TABLE "REJECTION_TRAIL" MODIFY ("DATE_TIME_STAMP" NOT NULL ENABLE);ALTER TABLE "REJECTION_TRAIL" MODIFY ("REJECT_SEQ" NOT NULL ENABLE);ALTER TABLE "REJECTION_TRAIL" MODIFY ("ENTITY_ID" NOT NULL ENABLE);ALTER TABLE "REJECTION_TRAIL" MODIFY ("ENUM_ENTITY_TYPE" NOT NULL ENABLE);/---------------------------------------------------------- Constraints for Table RELATED_ARTICLES--------------------------------------------------------ALTER TABLE "RELATED_ARTICLES" MODIFY ("RARTICLE_ID" NOT NULL ENABLE);ALTER TABLE "RELATED_ARTICLES" MODIFY ("ARTICLE_ID" NOT NULL ENABLE);/---------------------------------------------------------- Constraints for Table DEF_MENU_ITEMS--------------------------------------------------------ALTER TABLE "DEF_MENU_ITEMS" ADD CONSTRAINT "PK_DEF_MENU_ITEMS" PRIMARY KEY ("ITEM_ID") ENABLE;ALTER TABLE "DEF_MENU_ITEMS" MODIFY ("ACTIVE" NOT NULL ENABLE);ALTER TABLE "DEF_MENU_ITEMS" MODIFY ("VISIBLE" NOT NULL ENABLE);ALTER TABLE "DEF_MENU_ITEMS" MODIFY ("ITEM_NAME" NOT NULL ENABLE);ALTER TABLE "DEF_MENU_ITEMS" MODIFY ("ITEM_ID" NOT NULL ENABLE);/---------------------------------------------------------- Constraints for Table PROCESSES_CONFIG--------------------------------------------------------ALTER TABLE "PROCESSES_CONFIG" MODIFY ("PKG_ID" NOT NULL ENABLE);ALTER TABLE "PROCESSES_CONFIG" MODIFY ("PKG_HEALTH_TAG" NOT NULL ENABLE);ALTER TABLE "PROCESSES_CONFIG" MODIFY ("OS_ID" NOT NULL ENABLE);ALTER TABLE "PROCESSES_CONFIG" MODIFY ("PROD_ID" NOT NULL ENABLE);/---------------------------------------------------------- Constraints for Table RELEASE_AUTHORISATION--------------------------------------------------------ALTER TABLE "RELEASE_AUTHORISATION" MODIFY ("TESTER_ID" NOT NULL ENABLE);ALTER TABLE "RELEASE_AUTHORISATION" MODIFY ("BOM_ID" NOT NULL ENABLE);ALTER TABLE "RELEASE_AUTHORISATION" MODIFY ("PV_ID" NOT NULL ENABLE);/---------------------------------------------------------- Constraints for Table OPERATING_SYSTEMS--------------------------------------------------------ALTER TABLE "OPERATING_SYSTEMS" ADD CONSTRAINT "PK_OPERATING_SYSTEMS" PRIMARY KEY ("OS_ID") ENABLE;ALTER TABLE "OPERATING_SYSTEMS" MODIFY ("NODE_ID" NOT NULL ENABLE);ALTER TABLE "OPERATING_SYSTEMS" MODIFY ("OS_NAME" NOT NULL ENABLE);ALTER TABLE "OPERATING_SYSTEMS" MODIFY ("OS_ID" NOT NULL ENABLE);/---------------------------------------------------------- Ref Constraints for Table BASE_ENV--------------------------------------------------------ALTER TABLE "BASE_ENV" ADD CONSTRAINT "FK_BASE_ENV_REF_BOMS" FOREIGN KEY ("BOM_ID")REFERENCES "BOMS" ("BOM_ID") ENABLE;ALTER TABLE "BASE_ENV" ADD CONSTRAINT "FK_BASE_ENV_REF_BOS_TYPES" FOREIGN KEY ("BOS_ID")REFERENCES "BOS_TYPES" ("BOS_ID") ENABLE;/---------------------------------------------------------- Ref Constraints for Table BASE_ENV_CONTENTS--------------------------------------------------------ALTER TABLE "BASE_ENV_CONTENTS" ADD CONSTRAINT "FK_BASE_ENV_CONT_REF_BASE_ENV" FOREIGN KEY ("BASE_ENV_ID")REFERENCES "BASE_ENV" ("BASE_ENV_ID") ENABLE;/---------------------------------------------------------- Ref Constraints for Table BOMS--------------------------------------------------------ALTER TABLE "BOMS" ADD CONSTRAINT "FK_BOMS_REF_BOM_NAME" FOREIGN KEY ("BOM_NAME_ID")REFERENCES "BOM_NAMES" ("BOM_NAME_ID") ENABLE;ALTER TABLE "BOMS" ADD CONSTRAINT "FK_BOMS_REF_BRANCHES" FOREIGN KEY ("BRANCH_ID")REFERENCES "BRANCHES" ("BRANCH_ID") ENABLE;/---------------------------------------------------------- Ref Constraints for Table BOM_CONTENTS--------------------------------------------------------ALTER TABLE "BOM_CONTENTS" ADD CONSTRAINT "FK_BOM_CONT_REF_BOMS" FOREIGN KEY ("BOM_ID")REFERENCES "BOMS" ("BOM_ID") ENABLE;ALTER TABLE "BOM_CONTENTS" ADD CONSTRAINT "FK_BOM_CONT_REF_NETWORK" FOREIGN KEY ("NODE_ID")REFERENCES "NETWORK_NODES" ("NODE_ID") ENABLE;/---------------------------------------------------------- Ref Constraints for Table BOM_NOTES--------------------------------------------------------ALTER TABLE "BOM_NOTES" ADD CONSTRAINT "FK_BOM_NOTES_REF_BOMS" FOREIGN KEY ("BOM_ID")REFERENCES "BOMS" ("BOM_ID") ENABLE;ALTER TABLE "BOM_NOTES" ADD CONSTRAINT "FK_BOM_NOTE_REF_HELP_NOTE" FOREIGN KEY ("ARTICLE_ID")REFERENCES "KNOWLEDGE_BASE" ("ARTICLE_ID") ENABLE;/---------------------------------------------------------- Ref Constraints for Table BOM_STATE--------------------------------------------------------ALTER TABLE "BOM_STATE" ADD CONSTRAINT "FK_BOM_STAT_REF_BOMS" FOREIGN KEY ("BOM_ID")REFERENCES "BOMS" ("BOM_ID") ENABLE;ALTER TABLE "BOM_STATE" ADD CONSTRAINT "FK_BOM_STAT_REF_STATES" FOREIGN KEY ("STATE_ID")REFERENCES "STATES" ("STATE_ID") ENABLE;/---------------------------------------------------------- Ref Constraints for Table BOM_TRAIL--------------------------------------------------------ALTER TABLE "BOM_TRAIL" ADD CONSTRAINT "FK_BOM_TRAIL_REF_BOMS" FOREIGN KEY ("BOM_ID")REFERENCES "BOMS" ("BOM_ID") ENABLE;/---------------------------------------------------------- Ref Constraints for Table BRANCHES--------------------------------------------------------ALTER TABLE "BRANCHES" ADD CONSTRAINT "FK_BRANCHES_RE_DM_PROJ" FOREIGN KEY ("PROJ_ID")REFERENCES "DM_PROJECTS" ("PROJ_ID") ENABLE;/---------------------------------------------------------- Ref Constraints for Table HARDWARE--------------------------------------------------------ALTER TABLE "HARDWARE" ADD CONSTRAINT "FK_HARDWARE_REF_BOMS" FOREIGN KEY ("BOM_ID")REFERENCES "BOMS" ("BOM_ID") ENABLE;/---------------------------------------------------------- Ref Constraints for Table HARDWARE_NODE--------------------------------------------------------ALTER TABLE "HARDWARE_NODE" ADD CONSTRAINT "FK_HARDWARE_NODE_REF_HARDWARE" FOREIGN KEY ("HARDWARE_NAME_ID")REFERENCES "HARDWARE" ("HARDWARE_NAME_ID") ENABLE;ALTER TABLE "HARDWARE_NODE" ADD CONSTRAINT "FK_HARDWARE_REF_NETWORK" FOREIGN KEY ("NODE_ID")REFERENCES "NETWORK_NODES" ("NODE_ID") ENABLE;/---------------------------------------------------------- Ref Constraints for Table HIDE_PRODUCTS--------------------------------------------------------ALTER TABLE "HIDE_PRODUCTS" ADD FOREIGN KEY ("OS_ID")REFERENCES "OPERATING_SYSTEMS" ("OS_ID") DISABLE;/---------------------------------------------------------- Ref Constraints for Table NETWORK_NODES--------------------------------------------------------ALTER TABLE "NETWORK_NODES" ADD CONSTRAINT "FK_NETWORK__REF_NODE_TYP" FOREIGN KEY ("NODE_TYPE_ID")REFERENCES "NODE_TYPES" ("NODE_TYPE_ID") ENABLE;/---------------------------------------------------------- Ref Constraints for Table NOTIFY_USER--------------------------------------------------------ALTER TABLE "NOTIFY_USER" ADD CONSTRAINT "FK_NOTIFY_USR_REFE_DM_PROJ" FOREIGN KEY ("PROJ_ID")REFERENCES "DM_PROJECTS" ("PROJ_ID") ENABLE;/---------------------------------------------------------- Ref Constraints for Table OPERATING_SYSTEMS--------------------------------------------------------ALTER TABLE "OPERATING_SYSTEMS" ADD CONSTRAINT "FK_OPERATIN_REF_NETWORK" FOREIGN KEY ("NODE_ID")REFERENCES "NETWORK_NODES" ("NODE_ID") ENABLE;/---------------------------------------------------------- Ref Constraints for Table OS_BASE_ENV--------------------------------------------------------ALTER TABLE "OS_BASE_ENV" ADD CONSTRAINT "FK_OS_BASE_ENV_REF_BASE_ENV" FOREIGN KEY ("BASE_ENV_ID")REFERENCES "BASE_ENV" ("BASE_ENV_ID") ENABLE;ALTER TABLE "OS_BASE_ENV" ADD CONSTRAINT "FK_OS_BASE_ENV_REF_OS" FOREIGN KEY ("OS_ID")REFERENCES "OPERATING_SYSTEMS" ("OS_ID") ENABLE;/---------------------------------------------------------- Ref Constraints for Table OS_CONTENTS--------------------------------------------------------ALTER TABLE "OS_CONTENTS" ADD CONSTRAINT "FK_OS_CONTE_REF_OPERATIN" FOREIGN KEY ("OS_ID")REFERENCES "OPERATING_SYSTEMS" ("OS_ID") ENABLE;/---------------------------------------------------------- Ref Constraints for Table PROCESSES_CONFIG--------------------------------------------------------ALTER TABLE "PROCESSES_CONFIG" ADD CONSTRAINT "FK_PROC_CONFIG_REF_OS" FOREIGN KEY ("OS_ID")REFERENCES "OPERATING_SYSTEMS" ("OS_ID") DISABLE;/---------------------------------------------------------- Ref Constraints for Table PRODUCTION_BOM--------------------------------------------------------ALTER TABLE "PRODUCTION_BOM" ADD FOREIGN KEY ("PROJ_ID")REFERENCES "DM_PROJECTS" ("PROJ_ID") ENABLE;ALTER TABLE "PRODUCTION_BOM" ADD FOREIGN KEY ("BOM_ID")REFERENCES "BOMS" ("BOM_ID") ENABLE;ALTER TABLE "PRODUCTION_BOM" ADD FOREIGN KEY ("STATE_ID")REFERENCES "STATES" ("STATE_ID") ENABLE;/---------------------------------------------------------- Ref Constraints for Table PRODUCTS_QUEUE--------------------------------------------------------ALTER TABLE "PRODUCTS_QUEUE" ADD CONSTRAINT "FK_PRODUCTS_REFERENCE_DM_PROJE" FOREIGN KEY ("PRODQ_ID")REFERENCES "DM_PROJECTS" ("PROJ_ID") ENABLE;/---------------------------------------------------------- Ref Constraints for Table RELATED_ARTICLES--------------------------------------------------------ALTER TABLE "RELATED_ARTICLES" ADD CONSTRAINT "FK_RA_ARTID_REF_ARTID" FOREIGN KEY ("ARTICLE_ID")REFERENCES "KNOWLEDGE_BASE" ("ARTICLE_ID") ENABLE;ALTER TABLE "RELATED_ARTICLES" ADD CONSTRAINT "FK_RA_RARTID_REF_ARTID" FOREIGN KEY ("RARTICLE_ID")REFERENCES "KNOWLEDGE_BASE" ("ARTICLE_ID") ENABLE;/---------------------------------------------------------- Ref Constraints for Table RELEASE_AUTHORISATION--------------------------------------------------------ALTER TABLE "RELEASE_AUTHORISATION" ADD CONSTRAINT "RELEASE_AUTHORISATION_R01" FOREIGN KEY ("BOM_ID")REFERENCES "BOMS" ("BOM_ID") ENABLE;/---------------------------------------------------------- Ref Constraints for Table STATES--------------------------------------------------------ALTER TABLE "STATES" ADD CONSTRAINT "FK_STATES_REF_DM_PROJ" FOREIGN KEY ("PROJ_ID")REFERENCES "DM_PROJECTS" ("PROJ_ID") ENABLE;/---------------------------------------------------------- DDL for Function GET_DATE--------------------------------------------------------CREATE OR REPLACE FUNCTION "GET_DATE" RETURN DATE IS/* ---------------------------------------------------------------------------Version: 1.0.0--------------------------------------------------------------------------- */BEGINRETURN TO_DATE( TO_CHAR( SYSDATE,'DD-MON-YYYY' ),'DD-MON-YYYY' );END GET_DATE;//---------------------------------------------------------- DDL for Function GET_DATETIME--------------------------------------------------------CREATE OR REPLACE FUNCTION "GET_DATETIME" RETURN DATE IS/* ---------------------------------------------------------------------------Version: 1.0.0--------------------------------------------------------------------------- */BEGINRETURN TO_DATE( TO_CHAR( SYSDATE,'DD-MON-YYYY HH24:MI:SS' ),'DD-MON-YYYY HH24:MI:SS' );END GET_DATETIME;//---------------------------------------------------------- DDL for Function GET_SYSTEM_VALUE--------------------------------------------------------CREATE OR REPLACE FUNCTION "GET_SYSTEM_VALUE" ( sItemName IN SYSTEM_CONFIGURATION.ITEM_NAME%TYPE ) RETURN SYSTEM_CONFIGURATION.ITEM_VALUE%TYPE IS/* ---------------------------------------------------------------------------Version: 1.0.0--------------------------------------------------------------------------- */ItemValue SYSTEM_CONFIGURATION.ITEM_VALUE%TYPE;BEGIN-- Get system valueSELECT sc.ITEM_VALUE INTO ItemValueFROM SYSTEM_CONFIGURATION scWHERE sc.ITEM_NAME = sItemName;RETURN ItemValue;END GET_SYSTEM_VALUE;//---------------------------------------------------------- DDL for Function IN_LIST_NUMBER--------------------------------------------------------CREATE OR REPLACE FUNCTION "IN_LIST_NUMBER" ( sInList IN VARCHAR2 ) RETURN DEPMGR_NUMBER_TAB_t IS/* ---------------------------------------------------------------------------Version: 1.0.0--------------------------------------------------------------------------- */sync_rtags DEPMGR_NUMBER_TAB_t := DEPMGR_NUMBER_TAB_t();pos NUMBER;in_list VARCHAR2(4000) := sInList || ',';BEGINIF NOT sInList IS NULLTHENLOOPEXIT WHEN in_list IS NULL;pos := INSTR ( in_list, ',' );sync_rtags.extend;sync_rtags(sync_rtags.count) := LTRIM ( RTRIM ( SUBSTR ( in_list, 1, pos-1 ) ) );in_list := SUBSTR ( in_list, pos+1 );END LOOP;END IF;RETURN sync_rtags;END IN_LIST_NUMBER;//---------------------------------------------------------- DDL for Function PRINT_SBOM_PACKAGES--------------------------------------------------------CREATE OR REPLACE FUNCTION "PRINT_SBOM_PACKAGES" (v_dm_branch_name VARCHAR2, v_sbom_version VARCHAR2)RETURN NUMBER IScur ref_cursor;SqlCmd VARCHAR2(4000);BEGINSqlCmd := 'CREATE GLOBAL TEMPORARY TABLE sbom_packagesON COMMIT PRESERVE ROWSAS SELECT DISTINCT pv.pv_idFROM deployment_manager.boms bINNER JOIN deployment_manager.bom_contents bc ON b.bom_id = bc.bom_idINNER JOIN deployment_manager.branches br ON b.branch_id = br.branch_idINNER JOIN deployment_manager.operating_systems os ON bc.node_id = os.node_idINNER JOIN deployment_manager.os_contents osc ON os.os_id = osc.os_idINNER JOIN release_manager.package_versions pv ON osc.prod_id = pv.pv_idWHERE br.branch_name = v_dm_branch_nameAND b.bom_version||''.''||b.bom_lifecycle = v_sbom_version;';EXECUTE SqlCmd;FOR cur IN SELECT DISTINCT pv_id FROM sbom_packages LOOPINSERT INTO sbom_packages (SELECT DISTINCT PV.pv_idFROM release_content RCINNER JOIN package_versions PV ON PV.pv_id = RC.pv_idINNER JOIN packages P ON P.pkg_id = PV.pkg_idWHERE PV.pv_id IN ( SELECT DISTINCT dpv_idFROM package_dependenciesSTART WITH pv_id = cur.pv_idCONNECT BY pv_id = PRIOR dpv_id ));END LOOP;FOR cur IN SELECT DISTINCT pv_id FROM sbom_packages LOOPDbms_Output.put_line(cur.pv_id);END LOOP;RETURN 0;END;//---------------------------------------------------------- DDL for Function STRREPLACE--------------------------------------------------------CREATE OR REPLACE FUNCTION "STRREPLACE" (str varchar2, from_str varchar2, to_str varchar2)return varchar2ASstr_temp varchar2(4000);str_pos number := instr(str, from_str);BEGINstr_temp := str;while ( str_pos > 0 ) loopstr_temp := substr(str_temp, 0, str_pos-1) || to_str ||substr(str_temp, str_pos + length(from_str));str_pos := instr(str_temp, from_str);end loop;return str_temp;END;//---------------------------------------------------------- DDL for Package PK_BASE_ENV--------------------------------------------------------CREATE OR REPLACE PACKAGE "PK_BASE_ENV" ISPROCEDURE Add_Base_Env ( sBaseEnvName IN BASE_ENV.BASE_ENV_NAME%TYPE,nBosId IN BASE_ENV.BOS_ID%TYPE,nBomId IN BASE_ENV.BOM_ID%TYPE,nOsId IN OS_BASE_ENV.OS_ID%TYPE,nUserId IN NUMBER );PROCEDURE Update_Base_Env ( nBaseEnvId IN BASE_ENV.BASE_ENV_ID%TYPE,sBaseEnvName IN BASE_ENV.BASE_ENV_NAME%TYPE,nBosId IN BASE_ENV.BOS_ID%TYPE,nOsId IN OS_BASE_ENV.OS_ID%TYPE,nUserId IN NUMBER );FUNCTION Get_Base_Env_Id ( sBaseEnvName IN BASE_ENV.BASE_ENV_NAME%TYPE,nBomId IN BASE_ENV.BOM_ID%TYPE ) RETURN BASE_ENV.BASE_ENV_ID%TYPE;PROCEDURE Destroy_Base_Env ( nBaseEnvId IN BASE_ENV.BASE_ENV_ID%TYPE,nOsId IN OS_BASE_ENV.OS_ID%TYPE,nUserId IN NUMBER);PROCEDURE Add_Product ( sProdIdList IN VARCHAR2,nBaseEnvId IN BASE_ENV.BASE_ENV_ID%TYPE,nOsId IN OS_BASE_ENV.OS_ID%TYPE,nUserId IN NUMBER );PROCEDURE Add_Product_Wrap ( nPkgId IN NUMBER,sProdVersion IN VARCHAR2,nBaseEnvId IN BASE_ENV_CONTENTS.BASE_ENV_ID%TYPE,nOsId IN OS_BASE_ENV.OS_ID%TYPE,nUserId IN NUMBER,sComments IN VARCHAR2 );PROCEDURE Remove_Products ( sProdIdList IN VARCHAR2,nBaseEnvId IN BASE_ENV_CONTENTS.BASE_ENV_ID%TYPE,nUserId IN NUMBER );PROCEDURE Update_Product ( nOldProdId IN BASE_ENV_CONTENTS.PROD_ID%TYPE,sNewProdIdVersion IN VARCHAR2,nBaseEnvId IN BASE_ENV_CONTENTS.BASE_ENV_ID%TYPE,nSeqNum IN BASE_ENV_CONTENTS.SEQ_NUM%TYPE,sComments IN BASE_ENV_CONTENTS.BASE_ENV_COMMENTS%TYPE,sSunOSElectronicName IN PACKAGES.SUNOS_ELECTRONIC_NAME%TYPE,sWinElectronicName IN PACKAGES.WIN_ELECTRONIC_NAME%TYPE,sNotXML IN BASE_ENV_CONTENTS.NOT_XML%TYPE,sPatchElectronicName IN PACKAGE_VERSIONS.PATCH_ELECTRONIC_NAME%TYPE,nUserId IN NUMBER,outProdId OUT PACKAGE_VERSIONS.PV_ID%TYPE );PROCEDURE Shift_Product_SeqNum ( nProdId IN BASE_ENV_CONTENTS.PROD_ID%TYPE,nBaseEnvId IN BASE_ENV_CONTENTS.BASE_ENV_ID%TYPE,nToSeqNum IN BASE_ENV_CONTENTS.SEQ_NUM%TYPE );PROCEDURE Sort_Products ( sProdIdOrder IN VARCHAR2,nBaseEnvId IN BASE_ENV_CONTENTS.BASE_ENV_ID%TYPE,nUserId IN NUMBER );END pk_Base_Env;//---------------------------------------------------------- DDL for Package PK_BASE_ENV_TEST--------------------------------------------------------CREATE OR REPLACE PACKAGE "PK_BASE_ENV_TEST" ISPROCEDURE Add_Base_Env ( sBaseEnvName IN BASE_ENV.BASE_ENV_NAME%TYPE,nBosId IN BASE_ENV.BOS_ID%TYPE,nBomId IN BASE_ENV.BOM_ID%TYPE,nOsId IN OS_BASE_ENV.OS_ID%TYPE );PROCEDURE Update_Base_Env ( nBaseEnvId IN BASE_ENV.BASE_ENV_ID%TYPE,sBaseEnvName IN BASE_ENV.BASE_ENV_NAME%TYPE,nBosId IN BASE_ENV.BOS_ID%TYPE );FUNCTION Get_Base_Env_Id ( sBaseEnvName IN BASE_ENV.BASE_ENV_NAME%TYPE,nBomId IN BASE_ENV.BOM_ID%TYPE ) RETURN BASE_ENV.BASE_ENV_ID%TYPE;PROCEDURE Destroy_Base_Env ( nBaseEnvId IN BASE_ENV.BASE_ENV_ID%TYPE );PROCEDURE Add_Product ( sProdIdList IN VARCHAR2,nBaseEnvId IN BASE_ENV.BASE_ENV_ID%TYPE,nUserId IN NUMBER );PROCEDURE Add_Product_Wrap ( nPkgId IN NUMBER,sProdVersion IN VARCHAR2,nBaseEnvId IN BASE_ENV_CONTENTS.BASE_ENV_ID%TYPE,nUserId IN NUMBER,sComments IN VARCHAR2 );PROCEDURE Remove_Products ( sProdIdList IN VARCHAR2,nBaseEnvId IN BASE_ENV_CONTENTS.BASE_ENV_ID%TYPE,nUserId IN NUMBER );PROCEDURE Update_Product ( nOldProdId IN BASE_ENV_CONTENTS.PROD_ID%TYPE,sNewProdIdVersion IN VARCHAR2,nBaseEnvId IN BASE_ENV_CONTENTS.BASE_ENV_ID%TYPE,nSeqNum IN BASE_ENV_CONTENTS.SEQ_NUM%TYPE,sComments IN BASE_ENV_CONTENTS.BASE_ENV_COMMENTS%TYPE,sElectronicName IN PACKAGES.ELECTRONIC_NAME%TYPE );PROCEDURE Shift_Product_SeqNum ( nProdId IN BASE_ENV_CONTENTS.PROD_ID%TYPE,nBaseEnvId IN BASE_ENV_CONTENTS.BASE_ENV_ID%TYPE,nToSeqNum IN BASE_ENV_CONTENTS.SEQ_NUM%TYPE );PROCEDURE Sort_Products ( sProdIdOrder IN VARCHAR2,nBaseEnvId IN BASE_ENV_CONTENTS.BASE_ENV_ID%TYPE,nUserId IN NUMBER );END pk_Base_Env_test;//---------------------------------------------------------- DDL for Package PK_BOM--------------------------------------------------------CREATE OR REPLACE PACKAGE "PK_BOM" ISPROCEDURE Add_Bom ( sBomName IN BOM_NAMES.BOM_NAME%TYPE DEFAULT NULL,sBomVersion IN BOMS.BOM_VERSION%TYPE,sBomLifeCycle IN BOMS.BOM_LIFECYCLE%TYPE,sBomComments IN BOMS.BOM_COMMENTS%TYPE DEFAULT NULL,nToBranchId IN BOMS.BRANCH_ID%TYPE,nFromBomId IN BOMS.BOM_ID%TYPE DEFAULT NULL,nRtagId IN BOMS.RTAG_ID_FK%TYPE,nUserId IN BOM_STATE.USER_ID%TYPE );PROCEDURE Update_Bom ( nBomId IN BOMS.BOM_ID%TYPE,nRtagId IN BOMS.RTAG_ID_FK%TYPE,sBomComment IN BOMS.BOM_COMMENTS%TYPE,nUserId IN NUMBER );PROCEDURE Update_ProdBom ( nBomId IN BOMS.BOM_ID%TYPE,nRtagId IN BOMS.RTAG_ID_FK%TYPE,sBomComment IN BOMS.BOM_COMMENTS%TYPE,nUserId IN NUMBER );FUNCTION New_Bom_Name ( sBomName IN BOM_NAMES.BOM_NAME%TYPE ) RETURN BOM_NAMES.BOM_NAME_ID%TYPE;FUNCTION New_Bom_Name ( nFromBomId IN BOMS.BOM_NAME_ID%TYPE ) RETURN BOMS.BOM_NAME_ID%TYPE;PROCEDURE Accept_Reject ( nBomId IN BOMS.BOM_ID%TYPE,cIsRejected IN BOMS.IS_REJECTED%TYPE,sComments IN REJECTION_TRAIL.COMMENTS%TYPE,nStateId IN BOM_STATE.STATE_ID%TYPE DEFAULT NULL,nUserId IN REJECTION_TRAIL.USER_ID%TYPE );PROCEDURE Set_Bom_Official ( nBomId IN BOMS.BOM_ID%TYPE,cIsOfficial IN BOMS.IS_READONLY%TYPE,nStateId IN BOM_STATE.STATE_ID%TYPE,nUserId IN NUMBER );PROCEDURE Update_Nodes_Version ( nBomId IN BOMS.BOM_ID%TYPE,nUserId IN NUMBER );PROCEDURE Destroy_Bom ( nBomId IN VARCHAR2 );PROCEDURE Destroy_BaseEnv ( nBaseEndIdCollector IN DEPMGR_NUMBER_TAB_t );PROCEDURE Destroy_Hardware ( nHardwareNameIdCollector IN DEPMGR_NUMBER_TAB_t );PROCEDURE Get_Next_Version (nFromBomId IN BOMS.BOM_ID%TYPE,nToBranchId IN BOMS.BRANCH_ID%TYPE,sNextVersion OUT BOMS.BOM_VERSION%TYPE,sNextLifeCycle OUT BOMS.BOM_LIFECYCLE%TYPE,nParentBomId IN OUT BOMS.PARENT_BOM_ID%TYPE,nBomNameId IN BOMS.BOM_NAME_ID%TYPE );PROCEDURE Release_To ( nBomId IN BOM_STATE.BOM_ID%TYPE,nUserId IN BOM_STATE.USER_ID%TYPE,nToState IN BOM_STATE.STATE_ID%TYPE DEFAULT NULL,sComments IN BOM_TRAIL.TRAIL_COMMENTS%TYPE DEFAULT NULL );PROCEDURE Accept_Reject_Comments ( nBomId IN BOMS.BOM_ID%TYPE,nRejectSeq IN REJECTION_TRAIL.REJECT_SEQ%TYPE,sComments IN REJECTION_TRAIL.COMMENTS%TYPE DEFAULT NULL,nUserId IN REJECTION_TRAIL.USER_ID%TYPE );PROCEDURE Log_Bom_Trail ( nBomId IN BOM_TRAIL.BOM_ID%TYPE,sAction IN BOM_TRAIL.TRAIL_ACTION%TYPE,nUserId IN BOM_TRAIL.USER_ID%TYPE,sComments IN BOM_TRAIL.TRAIL_COMMENTS%TYPE DEFAULT NULL );PROCEDURE Log_Node_Trail ( nNodeId IN NODE_TRAIL.NODE_ID%TYPE,sAction IN NODE_TRAIL.TRAIL_ACTION%TYPE,nUserId IN NODE_TRAIL.USER_ID%TYPE );PROCEDURE Log_Os_Trail ( nOsId IN OS_TRAIL.OS_ID%TYPE,sAction IN OS_TRAIL.TRAIL_ACTION%TYPE,nUserId IN OS_TRAIL.USER_ID%TYPE );END pk_Bom;//---------------------------------------------------------- DDL for Package PK_BOM_RPT--------------------------------------------------------CREATE OR REPLACE PACKAGE "PK_BOM_RPT" ASTYPE TOC_Type IS REF CURSOR RETURN TableOfContents%ROWTYPE;END PK_BOM_RPT;//---------------------------------------------------------- DDL for Package PK_BOM_TEST--------------------------------------------------------CREATE OR REPLACE PACKAGE "PK_BOM_TEST" ISPROCEDURE Add_Bom ( sBomName IN BOM_NAMES.BOM_NAME%TYPE DEFAULT NULL,sBomVersion IN BOMS.BOM_VERSION%TYPE,sBomLifeCycle IN BOMS.BOM_LIFECYCLE%TYPE,sBomComments IN BOMS.BOM_COMMENTS%TYPE DEFAULT NULL,nToBranchId IN BOMS.BRANCH_ID%TYPE,nFromBomId IN BOMS.BOM_ID%TYPE DEFAULT NULL,nUserId IN BOM_STATE.USER_ID%TYPE );PROCEDURE Update_Bom ( nBomId IN BOMS.BOM_ID%TYPE,nRtagId IN BOMS.RTAG_ID_FK%TYPE,sBomComment IN BOMS.BOM_COMMENTS%TYPE,nUserId IN NUMBER );FUNCTION New_Bom_Name ( sBomName IN BOM_NAMES.BOM_NAME%TYPE ) RETURN BOM_NAMES.BOM_NAME_ID%TYPE;FUNCTION New_Bom_Name ( nFromBomId IN BOMS.BOM_NAME_ID%TYPE ) RETURN BOMS.BOM_NAME_ID%TYPE;PROCEDURE Accept_Reject ( nBomId IN BOMS.BOM_ID%TYPE,cIsRejected IN BOMS.IS_REJECTED%TYPE,sComments IN REJECTION_TRAIL.COMMENTS%TYPE,nStateId IN BOM_STATE.STATE_ID%TYPE DEFAULT NULL,nUserId IN REJECTION_TRAIL.USER_ID%TYPE );PROCEDURE Set_Bom_Official ( nBomId IN BOMS.BOM_ID%TYPE,cIsOfficial IN BOMS.IS_READONLY%TYPE,nStateId IN BOM_STATE.STATE_ID%TYPE,nUserId IN NUMBER );PROCEDURE Update_Nodes_Version ( nBomId IN BOMS.BOM_ID%TYPE,nUserId IN NUMBER );PROCEDURE Destroy_Bom ( nBomId IN VARCHAR2 );PROCEDURE Destroy_BaseEnv ( nBaseEndIdCollector IN DEPMGR_NUMBER_TAB_t );PROCEDURE Destroy_Hardware ( nHardwareNameIdCollector IN DEPMGR_NUMBER_TAB_t );PROCEDURE Get_Next_Version (nFromBomId IN BOMS.BOM_ID%TYPE,nToBranchId IN BOMS.BRANCH_ID%TYPE,sNextVersion OUT BOMS.BOM_VERSION%TYPE,sNextLifeCycle OUT BOMS.BOM_LIFECYCLE%TYPE,nParentBomId IN OUT BOMS.PARENT_BOM_ID%TYPE,nBomNameId IN BOMS.BOM_NAME_ID%TYPE );PROCEDURE Release_To ( nBomId IN BOM_STATE.BOM_ID%TYPE,nUserId IN BOM_STATE.USER_ID%TYPE,nToState IN BOM_STATE.STATE_ID%TYPE DEFAULT NULL,sComments IN BOM_TRAIL.TRAIL_COMMENTS%TYPE DEFAULT NULL );PROCEDURE Accept_Reject_Comments ( nBomId IN BOMS.BOM_ID%TYPE,nRejectSeq IN REJECTION_TRAIL.REJECT_SEQ%TYPE,sComments IN REJECTION_TRAIL.COMMENTS%TYPE DEFAULT NULL,nUserId IN REJECTION_TRAIL.USER_ID%TYPE );PROCEDURE Log_Bom_Trail ( nBomId IN BOM_TRAIL.BOM_ID%TYPE,sAction IN BOM_TRAIL.TRAIL_ACTION%TYPE,nUserId IN BOM_TRAIL.USER_ID%TYPE,sComments IN BOM_TRAIL.TRAIL_COMMENTS%TYPE DEFAULT NULL );PROCEDURE Log_Node_Trail ( nNodeId IN NODE_TRAIL.NODE_ID%TYPE,sAction IN NODE_TRAIL.TRAIL_ACTION%TYPE,nUserId IN NODE_TRAIL.USER_ID%TYPE );PROCEDURE Log_Os_Trail ( nOsId IN OS_TRAIL.OS_ID%TYPE,sAction IN OS_TRAIL.TRAIL_ACTION%TYPE,nUserId IN OS_TRAIL.USER_ID%TYPE );END pk_Bom_Test;//---------------------------------------------------------- DDL for Package PK_NETWORK_NODE--------------------------------------------------------CREATE OR REPLACE PACKAGE "PK_NETWORK_NODE" IS/* ---------------------------------------------------------------------------Version: 1.0.0--------------------------------------------------------------------------- */PROCEDURE Add_Node ( sNodeName IN NETWORK_NODES.NODE_NAME%TYPE,nNodeTypeId IN NETWORK_NODES.NODE_TYPE_ID%TYPE,sNodeComments IN NETWORK_NODES.NODE_COMMENTS%TYPE,nBomId IN BOM_CONTENTS.BOM_ID%TYPE,nUserId IN NUMBER,sNotXML IN NETWORK_NODES.NOT_XML%TYPE );PROCEDURE Update_Node ( nNodeId IN NETWORK_NODES.NODE_ID%TYPE,sNodeName IN NETWORK_NODES.NODE_NAME%TYPE,nNodeTypeId IN NETWORK_NODES.NODE_TYPE_ID%TYPE,sNodeComments IN NETWORK_NODES.NODE_COMMENTS%TYPE,nUserId IN NUMBER,nLifeCycle IN NUMBER,sNotXML IN NETWORK_NODES.NOT_XML%TYPE );PROCEDURE Remove_Node ( sNodeIdList IN VARCHAR2,nBomId IN BOM_CONTENTS.BOM_ID%TYPE );PROCEDURE Destroy_Node ( nNodeIdCollection IN DEPMGR_NUMBER_TAB_t, nUserId IN NUMBER );PROCEDURE Destroy_Node ( nNodeIdList IN VARCHAR2, nUserId IN NUMBER );PROCEDURE Sort_Nodes ( sNodeIdOrder IN VARCHAR2,nBomId IN BOMS.BOM_ID%TYPE,nUserId IN NUMBER );END pk_Network_Node;//---------------------------------------------------------- DDL for Package PK_NETWORK_NODE_TEST--------------------------------------------------------CREATE OR REPLACE PACKAGE "PK_NETWORK_NODE_TEST" IS/* ---------------------------------------------------------------------------Version: 1.0.0--------------------------------------------------------------------------- */PROCEDURE Add_Node ( sNodeName IN NETWORK_NODES.NODE_NAME%TYPE,nNodeTypeId IN NETWORK_NODES.NODE_TYPE_ID%TYPE,sNodeComments IN NETWORK_NODES.NODE_COMMENTS%TYPE,nBomId IN BOM_CONTENTS.BOM_ID%TYPE,nUserId IN NUMBER );PROCEDURE Update_Node ( nNodeId IN NETWORK_NODES.NODE_ID%TYPE,sNodeName IN NETWORK_NODES.NODE_NAME%TYPE,nNodeTypeId IN NETWORK_NODES.NODE_TYPE_ID%TYPE,sNodeComments IN NETWORK_NODES.NODE_COMMENTS%TYPE );PROCEDURE Remove_Node ( sNodeIdList IN VARCHAR2,nBomId IN BOM_CONTENTS.BOM_ID%TYPE );PROCEDURE Destroy_Node ( nNodeIdCollection IN DEPMGR_NUMBER_TAB_t );PROCEDURE Destroy_Node ( nNodeIdList IN VARCHAR2 );PROCEDURE Sort_Nodes ( sNodeIdOrder IN VARCHAR2,nBomId IN BOMS.BOM_ID%TYPE,nUserId IN NUMBER );END pk_Network_Node_test;//---------------------------------------------------------- DDL for Package PK_OPERATING_SYSTEM--------------------------------------------------------CREATE OR REPLACE PACKAGE "PK_OPERATING_SYSTEM"ISPROCEDURE add_os (sosname IN operating_systems.os_name%TYPE,soscomments IN operating_systems.os_comments%TYPE,snodespecfile IN operating_systems.node_spec_file%TYPE,snotxml IN operating_systems.not_xml%TYPE,nnodeid IN operating_systems.node_id%TYPE,nbaseenvname IN base_env.base_env_name%TYPE,nbosid IN base_env.bos_id%TYPE DEFAULT NULL,nuserid IN NUMBER);PROCEDURE update_os (nosid IN operating_systems.os_id%TYPE,sosname IN operating_systems.os_name%TYPE,soscomments IN operating_systems.os_comments%TYPE,nbaseenvname IN base_env.base_env_name%TYPE,snodespecfile IN operating_systems.node_spec_file%TYPE,snotxml IN operating_systems.not_xml%TYPE,nuserid IN NUMBER);PROCEDURE add_product_wrap (npkgid IN NUMBER,sprodversion IN VARCHAR2,nosid IN os_contents.os_id%TYPE,nuserid IN NUMBER,scomments IN VARCHAR2 DEFAULT NULL);PROCEDURE set_base_conf (nosid IN os_base_env.os_id%TYPE,nbaseenvid IN os_base_env.base_env_id%TYPE);PROCEDURE shift_product_seqnum (nprodid IN os_contents.prod_id%TYPE,nosid IN os_contents.os_id%TYPE,ntoseqnum IN os_contents.seq_num%TYPE);PROCEDURE shift_bulk_products_seqnum (sprodidlist IN VARCHAR2,nosid IN os_contents.os_id%TYPE,cdirection IN CHAR);PROCEDURE destroy_os (nosidcollection IN depmgr_number_tab_t,nuserid IN NUMBER);PROCEDURE destroy_os (nosidlist IN VARCHAR2, nuserid IN NUMBER);PROCEDURE add_package_process (nprocidlist IN VARCHAR2,npvid IN processes_config.prod_id%TYPE,nosid IN os_contents.os_id%TYPE,nuserid IN NUMBER);PROCEDURE add_packages_process (npvidlist IN VARCHAR2,nosid IN os_contents.os_id%TYPE,nuserid IN NUMBER);END pk_operating_system;//---------------------------------------------------------- DDL for Package PK_PRODUCT--------------------------------------------------------CREATE OR REPLACE PACKAGE "PK_PRODUCT" IS/* ---------------------------------------------------------------------------Version: 1.0.0--------------------------------------------------------------------------- */PROCEDURE New_Product ( sProdName IN PACKAGES.PKG_NAME%TYPE,sProdVersion IN PACKAGE_VERSIONS.PKG_VERSION%TYPE,nUserId IN NUMBER,outProdId OUT PACKAGE_VERSIONS.PV_ID%TYPE );PROCEDURE Add_Product ( sProdIdList IN VARCHAR2,nOsId IN OS_CONTENTS.OS_ID%TYPE,nUserId IN NUMBER );PROCEDURE Hide_Product ( sProdIdList IN VARCHAR2,nOsId IN OS_CONTENTS.OS_ID%TYPE,nUserId IN NUMBER );PROCEDURE Unhide_Product ( sProdIdList IN NUMBER,nOsId IN OS_CONTENTS.OS_ID%TYPE,nUserId IN NUMBER );PROCEDURE Update_Process (nProcId IN PROCESSES_CONFIG.PROC_ID%TYPE,nProdId IN PROCESSES_CONFIG.PROD_ID%TYPE,nIsInterface IN PROCESSES_CONFIG.IS_INTERFACE%TYPE,nOsId IN PROCESSES_CONFIG.OS_ID%TYPE,nCmdInterface IN PROCESSES_CONFIG.CMD_INTERFACE%TYPE,nPkgOwner IN PROCESSES_CONFIG.PKG_OWNER%TYPE,sPkgHealthTag IN PROCESSES_CONFIG.PKG_HEALTH_TAG%TYPE,nProduct IN PROCESSES_CONFIG.PROD_ID%TYPE );PROCEDURE Update_Product ( nOldProdId IN OS_CONTENTS.PROD_ID%TYPE,sNewProdIdVersion IN VARCHAR2,nOsId IN OS_CONTENTS.OS_ID%TYPE,nSeqNum IN OS_CONTENTS.SEQ_NUM%TYPE,sComments IN OS_CONTENTS.PRODUCT_COMMENTS%TYPE,sSunOSElectronicName IN PACKAGES.SUNOS_ELECTRONIC_NAME%TYPE,sWinElectronicName IN PACKAGES.WIN_ELECTRONIC_NAME%TYPE,sIsNodeSpec IN OS_CONTENTS.IS_NODE_SPEC%TYPE,sNotXML IN OS_CONTENTS.NOT_XML%TYPE,sPatchElectronicName IN PACKAGE_VERSIONS.PATCH_ELECTRONIC_NAME%TYPE,nUserId IN NUMBER,outProdId OUT PACKAGE_VERSIONS.PV_ID%TYPE );PROCEDURE Replace_Product ( nOldProdId IN OS_CONTENTS.PROD_ID%TYPE,nNewProdId IN OS_CONTENTS.PROD_ID%TYPE,nOsId IN OS_CONTENTS.OS_ID%TYPE,nUserId IN NUMBER );PROCEDURE Remove_Process ( sProcName IN PROCESSES_CONFIG.PKG_HEALTH_TAG%TYPE,sProdIdList IN VARCHAR2,nOsId IN OS_CONTENTS.OS_ID%TYPE,sCmdInterface IN PROCESSES_CONFIG.CMD_INTERFACE%TYPE,sPkgOwner IN PROCESSES_CONFIG.PKG_OWNER%TYPE,sInterface IN PROCESSES_CONFIG.IS_INTERFACE%TYPE,nUserId IN NUMBER );PROCEDURE Remove_Products ( sProdIdList IN VARCHAR2,nOsId IN OS_CONTENTS.OS_ID%TYPE,nUserId IN NUMBER );PROCEDURE Add_Products_NodeSpec ( sProdIdList IN VARCHAR2,nOsId IN OS_CONTENTS.OS_ID%TYPE,nUserId IN NUMBER );PROCEDURE Accept_Reject ( nProdId IN PRODUCT_DETAILS.PROD_ID%TYPE,nBomId IN BOMS.BOM_ID%TYPE,sComments IN REJECTION_TRAIL.COMMENTS%TYPE DEFAULT NULL,nUserId IN REJECTION_TRAIL.USER_ID%TYPE,nOsId IN NUMBER );PROCEDURE Accept_Reject_List ( nProdIdList IN VARCHAR2,nBomId IN BOMS.BOM_ID%TYPE,sComments IN REJECTION_TRAIL.COMMENTS%TYPE DEFAULT NULL,nUserId IN REJECTION_TRAIL.USER_ID%TYPE,nOsId IN NUMBER );PROCEDURE Accept_Reject_Comments ( nProdId IN PRODUCT_DETAILS.PROD_ID%TYPE,nRejectSeq IN REJECTION_TRAIL.REJECT_SEQ%TYPE,sComments IN REJECTION_TRAIL.COMMENTS%TYPE DEFAULT NULL,nUserId IN REJECTION_TRAIL.USER_ID%TYPE );PROCEDURE Sort_Products ( sProdIdOrder IN VARCHAR2,nOsId IN OS_CONTENTS.OS_ID%TYPE,nUserId IN NUMBER );PROCEDURE Remove_Obsolete_Patches ( nOsId IN OS_CONTENTS.OS_ID%TYPE,nUserId IN NUMBER );FUNCTION GET_OS_ID ( PvId IN NUMBER ) RETURN NUMBER;FUNCTION GET_NODE_ID ( OsId IN NUMBER ) RETURN NUMBER;PROCEDURE Add_Product_Note ( nProdId IN NUMBER,sNotes IN VARCHAR2,nUserId IN NUMBER );PROCEDURE Edit_Product_Note ( nProdId IN NUMBER,sNotes IN VARCHAR2,nUserId IN NUMBER );PROCEDURE Log_Product_Trail ( nProdId IN PRODUCT_TRAIL.PROD_ID%TYPE,sAction IN PRODUCT_TRAIL.TRAIL_ACTION%TYPE,nUserId IN PRODUCT_TRAIL.USER_ID%TYPE );END pk_Product;//---------------------------------------------------------- DDL for Package PK_PRODUCTION--------------------------------------------------------CREATE OR REPLACE PACKAGE "PK_PRODUCTION" AS/******************************************************************************NAME: PK_PRODUCTIONPURPOSE:REVISIONS:Ver Date Author Description--------- ---------- --------------- ------------------------------------1.0 26/07/2006 1. Created this package.******************************************************************************/PROCEDURE Release_Authorisation_Tester ( nPvId IN NUMBER,nBomId IN NUMBER,nTesterId IN NUMBER,sTesterComments IN VARCHAR2);PROCEDURE Release_Authorisation_Manager ( nPvId IN NUMBER,nBomId IN NUMBER,nManagerId IN NUMBER,sManagerComments IN VARCHAR2);PROCEDURE Log_Pkg_Trail ( nPkgId IN OS_TRAIL.OS_ID%TYPE,sAction IN OS_TRAIL.TRAIL_ACTION%TYPE,nUserId IN OS_TRAIL.USER_ID%TYPE );PROCEDURE Reject_Product ( nPvId IN NUMBER,nTesterId IN NUMBER,sTesterComments IN VARCHAR2 );PROCEDURE Add_Production_Bom ( nBomId IN NUMBER,nBranchId IN NUMBER,nStateId IN NUMBER,nProjId IN NUMBER );END PK_PRODUCTION;//---------------------------------------------------------- DDL for Package PK_PRODUCT_TEST--------------------------------------------------------CREATE OR REPLACE PACKAGE "PK_PRODUCT_TEST" IS/* ---------------------------------------------------------------------------Version: 1.0.0--------------------------------------------------------------------------- */PROCEDURE New_Product ( sProdName IN PACKAGES.PKG_NAME%TYPE,sProdVersion IN PACKAGE_VERSIONS.PKG_VERSION%TYPE,nUserId IN NUMBER,outProdId OUT PACKAGE_VERSIONS.PV_ID%TYPE );PROCEDURE Add_Product ( sProdIdList IN VARCHAR2,nOsId IN OS_CONTENTS.OS_ID%TYPE,nUserId IN NUMBER );PROCEDURE Hide_Product ( sProdIdList IN VARCHAR2,nOsId IN OS_CONTENTS.OS_ID%TYPE,nUserId IN NUMBER );PROCEDURE Unhide_Product ( sProdIdList IN NUMBER,nOsId IN OS_CONTENTS.OS_ID%TYPE,nUserId IN NUMBER );PROCEDURE Update_Process (nProcId IN PROCESSES_CONFIG.PROC_ID%TYPE,nProdId IN PROCESSES_CONFIG.PROD_ID%TYPE,nIsInterface IN PROCESSES_CONFIG.IS_INTERFACE%TYPE,nOsId IN PROCESSES_CONFIG.OS_ID%TYPE,nCmdInterface IN PROCESSES_CONFIG.CMD_INTERFACE%TYPE,nPkgOwner IN PROCESSES_CONFIG.PKG_OWNER%TYPE,sPkgHealthTag IN PROCESSES_CONFIG.PKG_HEALTH_TAG%TYPE,nProduct IN PROCESSES_CONFIG.PROD_ID%TYPE );PROCEDURE Update_Product ( nOldProdId IN OS_CONTENTS.PROD_ID%TYPE,sNewProdIdVersion IN VARCHAR2,nOsId IN OS_CONTENTS.OS_ID%TYPE,nSeqNum IN OS_CONTENTS.SEQ_NUM%TYPE,sComments IN OS_CONTENTS.PRODUCT_COMMENTS%TYPE,sSunOSElectronicName IN PACKAGES.SUNOS_ELECTRONIC_NAME%TYPE,sWinElectronicName IN PACKAGES.WIN_ELECTRONIC_NAME%TYPE,sIsNodeSpec IN OS_CONTENTS.IS_NODE_SPEC%TYPE,sNotXML IN OS_CONTENTS.NOT_XML%TYPE,sPatchElectronicName IN PACKAGE_VERSIONS.PATCH_ELECTRONIC_NAME%TYPE,nUserId IN NUMBER,outProdId OUT PACKAGE_VERSIONS.PV_ID%TYPE );PROCEDURE Replace_Product ( nOldProdId IN OS_CONTENTS.PROD_ID%TYPE,nNewProdId IN OS_CONTENTS.PROD_ID%TYPE,nOsId IN OS_CONTENTS.OS_ID%TYPE,nUserId IN NUMBER );PROCEDURE Remove_Process ( sProcName IN PROCESSES_CONFIG.PKG_HEALTH_TAG%TYPE,sProdIdList IN VARCHAR2,nOsId IN OS_CONTENTS.OS_ID%TYPE,sCmdInterface IN PROCESSES_CONFIG.CMD_INTERFACE%TYPE,sPkgOwner IN PROCESSES_CONFIG.PKG_OWNER%TYPE,sInterface IN PROCESSES_CONFIG.IS_INTERFACE%TYPE,nUserId IN NUMBER );PROCEDURE Remove_Products ( sProdIdList IN VARCHAR2,nOsId IN OS_CONTENTS.OS_ID%TYPE,nUserId IN NUMBER );PROCEDURE Add_Products_NodeSpec ( sProdIdList IN VARCHAR2,nOsId IN OS_CONTENTS.OS_ID%TYPE,nUserId IN NUMBER );PROCEDURE Accept_Reject ( nProdId IN PRODUCT_DETAILS.PROD_ID%TYPE,nBomId IN BOMS.BOM_ID%TYPE,sComments IN REJECTION_TRAIL.COMMENTS%TYPE DEFAULT NULL,nUserId IN REJECTION_TRAIL.USER_ID%TYPE,nOsId IN NUMBER );PROCEDURE Accept_Reject_List ( nProdIdList IN VARCHAR2,nBomId IN BOMS.BOM_ID%TYPE,sComments IN REJECTION_TRAIL.COMMENTS%TYPE DEFAULT NULL,nUserId IN REJECTION_TRAIL.USER_ID%TYPE,nOsId IN NUMBER );PROCEDURE Accept_Reject_Comments ( nProdId IN PRODUCT_DETAILS.PROD_ID%TYPE,nRejectSeq IN REJECTION_TRAIL.REJECT_SEQ%TYPE,sComments IN REJECTION_TRAIL.COMMENTS%TYPE DEFAULT NULL,nUserId IN REJECTION_TRAIL.USER_ID%TYPE );PROCEDURE Sort_Products ( sProdIdOrder IN VARCHAR2,nOsId IN OS_CONTENTS.OS_ID%TYPE,nUserId IN NUMBER );PROCEDURE Remove_Obsolete_Patches ( nOsId IN OS_CONTENTS.OS_ID%TYPE,nUserId IN NUMBER );FUNCTION GET_OS_ID ( PvId IN NUMBER ) RETURN NUMBER;FUNCTION GET_NODE_ID ( OsId IN NUMBER ) RETURN NUMBER;END pk_Product_Test;//---------------------------------------------------------- DDL for Package PK_PROJECT--------------------------------------------------------CREATE OR REPLACE PACKAGE "PK_PROJECT" ISPROCEDURE Add_Project ( sProjName IN DM_PROJECTS.PROJ_NAME%TYPE,nRMProjFk IN DM_PROJECTS.RM_PROJECTS_FK%TYPE );PROCEDURE Update_Project ( nProjId IN DM_PROJECTS.PROJ_ID%TYPE,sProjName IN DM_PROJECTS.PROJ_NAME%TYPE,nRMProjFk IN DM_PROJECTS.RM_PROJECTS_FK%TYPE );PROCEDURE ShowHide_Project ( nProjId DM_PROJECTS.PROJ_ID%TYPE );PROCEDURE Remove_Project ( nProjId IN DM_PROJECTS.PROJ_ID%TYPE );PROCEDURE Add_Branch ( sBranchName IN BRANCHES.BRANCH_NAME%TYPE,nProjId IN BRANCHES.PROJ_ID%TYPE,nRMVTreeId IN BRANCHES.RM_VTREE_FK%TYPE,sBranchComments IN BRANCHES.BRANCH_COMMENTS%TYPE );PROCEDURE Update_Branch ( nBranchId IN BRANCHES.BRANCH_ID%TYPE,sBranchName IN BRANCHES.BRANCH_NAME%TYPE,nRMVTreeId IN BRANCHES.RM_VTREE_FK%TYPE,sBranchComments IN BRANCHES.BRANCH_COMMENTS%TYPE );PROCEDURE Remove_Branch ( nBranchId IN BRANCHES.BRANCH_ID%TYPE );PROCEDURE ShowHide_Branch ( nBranchId IN BRANCHES.BRANCH_ID%TYPE );PROCEDURE ShowHide_Download ( nProjId DM_PROJECTS.PROJ_ID%TYPE );PROCEDURE ShowHide_StateDownload ( nStateId STATES.STATE_ID%TYPE );END pk_Project;//---------------------------------------------------------- DDL for Package PK_REPORT--------------------------------------------------------CREATE OR REPLACE PACKAGE "PK_REPORT" IS/*------------------------------|| Last Modified: S.Vukovic|| Modified Date: 8/Mar/2005|| Spec Version: 1.0------------------------------*/TYPE typeCur IS REF CURSOR;/*================================================================================================*/FUNCTION BOM_PRODUCTS_BY_NODE_BY_OS ( BomId NUMBER ) RETURN typeCur;/*================================================================================================*/END PK_REPORT;//---------------------------------------------------------- DDL for Package PK_REQUIREMENTS--------------------------------------------------------CREATE OR REPLACE PACKAGE "PK_REQUIREMENTS" ISPROCEDURE Add_Bom ( nFromBomId IN BOMS.BOM_ID%TYPE,nToBranchId IN BOMS.BRANCH_ID%TYPE );PROCEDURE Destroy_Bom ( nBomId IN VARCHAR2 );END pk_Requirements;//---------------------------------------------------------- DDL for Package PK_STATE--------------------------------------------------------CREATE OR REPLACE PACKAGE "PK_STATE" ISPROCEDURE Add_State ( sStateName IN STATES.STATE_NAME%TYPE,nStateTypeEnum IN STATES.STATE_TYPE_ENUM%TYPE,nProjId IN STATES.PROJ_ID%TYPE );PROCEDURE Update_State ( nStateId IN STATES.STATE_ID%TYPE,sStateName IN STATES.STATE_NAME%TYPE,nStateSeq IN STATES.STATE_SEQ%TYPE,nStateTypeEnum IN STATES.STATE_TYPE_ENUM%TYPE );PROCEDURE Remove_State ( nStateId IN STATES.STATE_ID%TYPE );PROCEDURE Toggle_Is_Displayed ( nStateId IN BOM_STATE.STATE_ID%TYPE,nBomId IN BOM_STATE.BOM_ID%TYPE,cIsDisplay IN BOM_STATE.IS_DISPLAYED%TYPE );PROCEDURE Auto_Hide ( nStateId IN BOM_STATE.STATE_ID%TYPE,nInsertedBomId IN BOM_STATE.BOM_ID%TYPE,nEventValue IN SYSTEM_CONFIGURATION.ITEM_VALUE%TYPE );END pk_State;//---------------------------------------------------------- DDL for Package PK_UTILS--------------------------------------------------------CREATE OR REPLACE PACKAGE "PK_UTILS" IS/* ---------------------------------------------------------------------------Version: 1.0.0--------------------------------------------------------------------------- */PROCEDURE Clone_BOM ( nFromBomId IN BOMS.BOM_ID%TYPE,nToBomId IN BOMS.BOM_ID%TYPE,nUserId IN NUMBER );PROCEDURE Copy_BOM_Contents ( nFromBomId IN BOMS.BOM_ID%TYPE,nToBomId IN BOMS.BOM_ID%TYPE );PROCEDURE Copy_Network_Node ( nFromBomId IN BOMS.BOM_ID%TYPE,nToBomId IN BOMS.BOM_ID%TYPE,nFromNodeId IN NUMBER DEFAULT 0 );PROCEDURE Paste_OS_Contents;PROCEDURE Paste_Node ( sNodeIdList IN VARCHAR2,nFromBomId IN OPERATING_SYSTEMS.NODE_ID%TYPE,nToBomId IN OPERATING_SYSTEMS.NODE_ID%TYPE );PROCEDURE Paste_Os ( sOsIdList IN VARCHAR2,nFromNodeId IN OPERATING_SYSTEMS.NODE_ID%TYPE,nToNodeId IN OPERATING_SYSTEMS.NODE_ID%TYPE );PROCEDURE Paste_Products ( sProdIdList IN VARCHAR2,nFromOsId IN OS_CONTENTS.OS_ID%TYPE,nToOsId IN OS_CONTENTS.OS_ID%TYPE );PROCEDURE Paste_Products_To_Base_Env ( sProdIdList IN VARCHAR2,nFromOsId IN OS_CONTENTS.OS_ID%TYPE,nToBaseEnvId IN BASE_ENV.BASE_ENV_ID%TYPE );PROCEDURE Clean_Old_Id_Column;PROCEDURE Log_Rejection_Trail ( nEnumEntityType IN REJECTION_TRAIL.ENUM_ENTITY_TYPE%TYPE,nEntityId IN REJECTION_TRAIL.ENTITY_ID%TYPE,cIsRejected IN REJECTION_TRAIL.IS_REJECTED%TYPE,nUserId IN REJECTION_TRAIL.USER_ID%TYPE,sComments IN REJECTION_TRAIL.COMMENTS%TYPE DEFAULT NULL );PROCEDURE Log_Action ( nUserId IN ACTION_TRAIL.USER_ID%TYPE,nEvent IN ACTION_TRAIL.ACTION_EVENT%TYPE,sMethod IN ACTION_TRAIL.METHOD_CALL%TYPE DEFAULT NULL,sActionScript IN ACTION_TRAIL.ACTION_SCRIPT%TYPE DEFAULT NULL,sDescription IN ACTION_TRAIL.ACTION_DESCRIPTION%TYPE DEFAULT NULL );END pk_Utils;//---------------------------------------------------------- DDL for Package Body PK_BASE_ENV--------------------------------------------------------CREATE OR REPLACE PACKAGE BODY "PK_BASE_ENV"IS/* ---------------------------------------------------------------------------Version: 1.0.0--------------------------------------------------------------------------- *//*--------------------------------------------------------------------------------------------------*/PROCEDURE Add_Base_Env ( sBaseEnvName IN BASE_ENV.BASE_ENV_NAME%TYPE,nBosId IN BASE_ENV.BOS_ID%TYPE,nBomId IN BASE_ENV.BOM_ID%TYPE,nOsId IN OS_BASE_ENV.OS_ID%TYPE,nUserId IN NUMBER ) ISnBaseEnvId BASE_ENV.BASE_ENV_ID%TYPE;NodeName VARCHAR2(4000);BEGIN/*--------------- Business Rules Here -------------------*//*-------------------------------------------------------*/SELECT NN.NODE_NAME INTO NodeNameFROM NETWORK_NODES NN, OPERATING_SYSTEMS OSWHERE NN.NODE_ID = OS.NODE_IDAND OS.OS_ID = nOsId;-- Get base_env_idSELECT SEQ_BASE_ENV_ID.NEXTVAL INTO nBaseEnvId FROM DUAL;-- Insert new base env nameINSERT INTO BASE_ENV ( BASE_ENV_ID, BOS_ID, BASE_ENV_NAME, BOM_ID )VALUES ( nBaseEnvId, nBosId, sBaseEnvName, nBomId );/* Log BOM_TRAIL */pk_Bom.Log_Os_Trail ( nOsId, 'Added Base Configuration: '||sBaseEnvName||' into Node: '||NodeName, nUserId );EXCEPTIONWHEN DUP_VAL_ON_INDEXTHENRAISE_APPLICATION_ERROR (-20000, 'Base Configuration Name "'|| sBaseEnvName ||'" is Already Used in this BOM.');END Add_Base_Env;/*--------------------------------------------------------------------------------------------------*/PROCEDURE Update_Base_Env ( nBaseEnvId IN BASE_ENV.BASE_ENV_ID%TYPE,sBaseEnvName IN BASE_ENV.BASE_ENV_NAME%TYPE,nBosId IN BASE_ENV.BOS_ID%TYPE,nOsId IN OS_BASE_ENV.OS_ID%TYPE,nUserId IN NUMBER ) ISNodeName VARCHAR2(4000);BEGIN/*--------------- Business Rules Here -------------------*/IF (nBaseEnvId IS NULL)THENRAISE_APPLICATION_ERROR (-20000, 'Missing some parameters! nBaseEnvId= '|| nBaseEnvId );END IF;/*-------------------------------------------------------*/SELECT NN.NODE_NAME INTO NodeNameFROM NETWORK_NODES NN, OPERATING_SYSTEMS OSWHERE NN.NODE_ID = OS.NODE_IDAND OS.OS_ID = nOsId;-- Update Base EnvUPDATE BASE_ENV SETBASE_ENV_NAME = sBaseEnvName,BOS_ID = nBosIdWHERE BASE_ENV_ID = nBaseEnvId;/* Log BOM_TRAIL */pk_Bom.Log_Os_Trail ( nOsId, 'Updated Base Configuration: '||sBaseEnvName||' on Node: '||NodeName, nUserId );EXCEPTIONWHEN DUP_VAL_ON_INDEXTHENRAISE_APPLICATION_ERROR (-20000, 'Base Configuration Name "'|| sBaseEnvName ||'" is Already Used in this BOM.');END Update_Base_Env;/*--------------------------------------------------------------------------------------------------*/FUNCTION Get_Base_Env_Id ( sBaseEnvName IN BASE_ENV.BASE_ENV_NAME%TYPE,nBomId IN BASE_ENV.BOM_ID%TYPE ) RETURN BASE_ENV.BASE_ENV_ID%TYPE ISReturnValue BASE_ENV.BASE_ENV_ID%TYPE;CURSOR curBaseEnv ISSELECT be.BASE_ENV_IDFROM BASE_ENV beWHERE be.BASE_ENV_NAME = sBaseEnvNameAND be.BOM_ID = nBomId;recBaseEnv curBaseEnv%ROWTYPE;BEGIN/*--------------- Business Rules Here -------------------*//*-------------------------------------------------------*/-- Get bom_name_idOPEN curBaseEnv;FETCH curBaseEnv INTO recBaseEnv;IF curBaseEnv%FOUND THENReturnValue := recBaseEnv.BASE_ENV_ID;ELSEReturnValue := NULL;END IF;CLOSE curBaseEnv;RETURN ReturnValue;END Get_Base_Env_Id;/*--------------------------------------------------------------------------------------------------*/PROCEDURE Destroy_Base_Env ( nBaseEnvId IN BASE_ENV.BASE_ENV_ID%TYPE,nOsId IN OS_BASE_ENV.OS_ID%TYPE,nUserId IN NUMBER ) ISBaseEnvName VARCHAR2(100);NodeName VARCHAR2(4000);BEGIN/*--------------- Business Rules Here -------------------*/IF (nBaseEnvId IS NULL)THENRAISE_APPLICATION_ERROR (-20000, 'Missing some parameters! nBaseEnvId= '|| nBaseEnvId );END IF;/*-------------------------------------------------------*/SELECT NN.NODE_NAME INTO NodeNameFROM NETWORK_NODES NN, OPERATING_SYSTEMS OSWHERE NN.NODE_ID = OS.NODE_IDAND OS.OS_ID = nOsId;SELECT BASE_ENV_NAME INTO BaseEnvNameFROM BASE_ENVWHERE BASE_ENV_ID = nBaseEnvId;-- Destroy Base Env ContentsDELETEFROM BASE_ENV_CONTENTSWHERE BASE_ENV_ID = nBaseEnvId;-- Remove Base Env From BOMDELETEFROM OS_BASE_ENV obeWHERE obe.BASE_ENV_ID = nBaseEnvId;-- Destroy Base EnvDELETEFROM BASE_ENV beWHERE be.BASE_ENV_ID = nBaseEnvId;/* Log BOM_TRAIL */pk_Bom.Log_Os_Trail ( nOsId, 'Destroyed Base Configuration: '||BaseEnvName||' on Node: '||NodeName, nUserId );END Destroy_Base_Env;/*--------------------------------------------------------------------------------------------------*/PROCEDURE Add_Product ( sProdIdList IN VARCHAR2,nBaseEnvId IN BASE_ENV.BASE_ENV_ID%TYPE,nOsId IN OS_BASE_ENV.OS_ID%TYPE,nUserId IN NUMBER ) ISNextSeqNum NUMBER;BaseEnvName VARCHAR2(100);PkgName VARCHAR2(100);PkgVersion VARCHAR2(100);NodeName VARCHAR2(4000);CURSOR prod_cur ISSELECT pv.pv_id AS PROD_ID,pv.PV_DESCRIPTIONFROM PACKAGE_VERSIONS pvWHERE pv.pv_id IN (SELECT *FROM THE ( SELECT CAST( IN_LIST_NUMBER( sProdIdList ) AS DEPMGR_NUMBER_TAB_t ) FROM dual ));prod_rec prod_cur%ROWTYPE;BEGIN/*--------------- Business Rules Here -------------------*//*-------------------------------------------------------*/SELECT BASE_ENV_NAME INTO BaseEnvNameFROM BASE_ENVWHERE BASE_ENV_ID = nBaseEnvId;SELECT NN.NODE_NAME INTO NodeNameFROM NETWORK_NODES NN, OPERATING_SYSTEMS OSWHERE NN.NODE_ID = OS.NODE_IDAND OS.OS_ID = nOsId;-- Get seq_numSELECT MAX( bec.SEQ_NUM ) INTO NextSeqNumFROM BASE_ENV_CONTENTS becWHERE bec.BASE_ENV_ID = nBaseEnvId;-- Set NextSeqNum to 0 if no existing products are foundIF NextSeqNum IS NULL THENNextSeqNum := 1;ELSENextSeqNum := NextSeqNum + 1;END IF;-- Insert into OS ContentsOPEN prod_cur;FETCH prod_cur INTO prod_rec;WHILE prod_cur%FOUNDLOOPINSERT INTO BASE_ENV_CONTENTS ( BASE_ENV_ID, SEQ_NUM, PROD_ID, BASE_ENV_COMMENTS )VALUES (nBaseEnvId,NextSeqNum,prod_rec.PROD_ID,prod_rec.PV_DESCRIPTION);SELECT PKG.PKG_NAME INTO PkgNameFROM PACKAGES PKG, PACKAGE_VERSIONS PVWHERE PV.PV_ID = prod_rec.PROD_IDAND PKG.PKG_ID = PV.PKG_ID;SELECT PKG_VERSION INTO PkgVersionFROM PACKAGE_VERSIONS PVWHERE PV.PV_ID = prod_rec.PROD_ID;-- Log Action --pk_Bom.Log_Os_Trail ( nOsId,'Added Product: '||PkgName||' (Version '||PkgVersion||') to Base Configuration: '||BaseEnvName||' with Install Order: '||NextSeqNum||' on Node: '||NodeName,nUserId );NextSeqNum := NextSeqNum + 1;FETCH prod_cur INTO prod_rec;END LOOP;CLOSE prod_cur;END Add_Product;/*--------------------------------------------------------------------------------------------------*/PROCEDURE Remove_Products ( sProdIdList IN VARCHAR2,nBaseEnvId IN BASE_ENV_CONTENTS.BASE_ENV_ID%TYPE,nUserId IN NUMBER ) ISPkgName VARCHAR2(100);PkgVersion VARCHAR2(100);NextSeqNum NUMBER := 1;nIdCollector DEPMGR_NUMBER_TAB_t := DEPMGR_NUMBER_TAB_t();CURSOR prod_cur ISSELECT bec.PROD_ID, bec.SEQ_NUMFROM BASE_ENV_CONTENTS becWHERE bec.BASE_ENV_ID = nBaseEnvIdORDER BY bec.SEQ_NUM;prod_rec prod_cur%ROWTYPE;BEGIN/*--------------- Business Rules Here -------------------*/IF sProdIdList IS NULLTHENRAISE_APPLICATION_ERROR (-20000, 'Please select one or more Products.');END IF;/*-------------------------------------------------------*/-- Remove ProductsDELETEFROM BASE_ENV_CONTENTSWHERE BASE_ENV_ID = nBaseEnvIdAND PROD_ID IN (SELECT *FROM THE ( SELECT CAST( IN_LIST_NUMBER( sProdIdList ) AS DEPMGR_NUMBER_TAB_t ) FROM dual ));-- Update Seq NumbersOPEN prod_cur;FETCH prod_cur INTO prod_rec;WHILE prod_cur%FOUNDLOOPUPDATE BASE_ENV_CONTENTS SETSEQ_NUM = NextSeqNumWHERE BASE_ENV_ID = nBaseEnvIdAND PROD_ID = prod_rec.PROD_ID;NextSeqNum := NextSeqNum + 1;FETCH prod_cur INTO prod_rec;END LOOP;CLOSE prod_cur;nIdCollector := IN_LIST_NUMBER ( sProdIdList );FOR i IN 1..nIdCollector.COUNTLOOPSELECT PKG.PKG_NAME INTO PkgNameFROM PACKAGES PKG, PACKAGE_VERSIONS PVWHERE PV.PV_ID = nIdCollector(i)AND PKG.PKG_ID = PV.PKG_ID;SELECT PKG_VERSION INTO PkgVersionFROM PACKAGE_VERSIONS PVWHERE PV.PV_ID = nIdCollector(i);-- Log Action --pk_Bom.Log_Os_Trail ( nBaseEnvId,'Deleted Product: '||PkgName||' (Version: '||PkgVersion||') From Base Configuration from Node',nUserId );END LOOP;END Remove_Products;/*--------------------------------------------------------------------------------------------------*/PROCEDURE Add_Product_Wrap ( nPkgId IN NUMBER,sProdVersion IN VARCHAR2,nBaseEnvId IN BASE_ENV_CONTENTS.BASE_ENV_ID%TYPE,nOsId IN OS_BASE_ENV.OS_ID%TYPE,nUserId IN NUMBER,sComments IN VARCHAR2 ) ISProdId PACKAGE_VERSIONS.PV_ID%TYPE;ProdName PACKAGES.PKG_NAME%TYPE;CURSOR prod_cur ISSELECT bec.PROD_IDFROM BASE_ENV_CONTENTS becWHERE bec.PROD_ID = ProdIdAND bec.BASE_ENV_ID = nBaseEnvId;prod_rec prod_cur%ROWTYPE;BEGIN/*--------------- Business Rules Here -------------------*/IF (NOT nPkgId IS NULL) AND (sProdVersion IS NULL) AND (NOT nBaseEnvId IS NULL) AND (NOT nUserId IS NULL) THENRAISE_APPLICATION_ERROR (-20000, 'Please provide Product Version.('|| sProdVersion ||')' );ELSIF (nPkgId IS NULL) OR (sProdVersion IS NULL) OR (nBaseEnvId IS NULL) OR (nUserId IS NULL) THENRAISE_APPLICATION_ERROR (-20000, 'Missing some parameters! nPkgId= '|| nPkgId ||', sProdVersion='|| sProdVersion ||', nBaseEnvId='|| nBaseEnvId ||', nUserId='|| nUserId );END IF;/*-------------------------------------------------------*/-- Get Product Name --SELECT PKG_NAME INTO ProdName FROM PACKAGES WHERE PKG_ID = nPkgId;-- Create Product Version in Release Manager if Required, and Get Prod_id --Seed_Package_Names_Versions ( ProdName, sProdVersion, nUserId, ProdId );-- Add Product To Base Env --OPEN prod_cur;FETCH prod_cur INTO prod_rec;IF prod_cur%NOTFOUND THENpk_Base_Env.Add_Product ( ProdId, nBaseEnvId, nOsId, nUserId );-- Update CommentsIF (NOT sComments IS NULL) THENUPDATE BASE_ENV_CONTENTS bec SETbec.BASE_ENV_COMMENTS = sCommentsWHERE bec.BASE_ENV_ID = nBaseEnvIdAND bec.PROD_ID = ProdId;END IF;END IF;CLOSE prod_cur;END Add_Product_Wrap;/*--------------------------------------------------------------------------------------------------*/PROCEDURE Update_Product ( nOldProdId IN BASE_ENV_CONTENTS.PROD_ID%TYPE,sNewProdIdVersion IN VARCHAR2,nBaseEnvId IN BASE_ENV_CONTENTS.BASE_ENV_ID%TYPE,nSeqNum IN BASE_ENV_CONTENTS.SEQ_NUM%TYPE,sComments IN BASE_ENV_CONTENTS.BASE_ENV_COMMENTS%TYPE,sSunOSElectronicName IN PACKAGES.SUNOS_ELECTRONIC_NAME%TYPE,sWinElectronicName IN PACKAGES.WIN_ELECTRONIC_NAME%TYPE,sNotXML IN BASE_ENV_CONTENTS.NOT_XML%TYPE,sPatchElectronicName IN PACKAGE_VERSIONS.PATCH_ELECTRONIC_NAME%TYPE,nUserId IN NUMBER,outProdId OUT PACKAGE_VERSIONS.PV_ID%TYPE ) ISnNewProdId BASE_ENV_CONTENTS.PROD_ID%TYPE;sProdName PACKAGES.PKG_NAME%TYPE;CURSOR curProd ISSELECT pv.PV_IDFROM PACKAGE_VERSIONS pvWHERE pv.pkg_id IN ( SELECT pv.pkg_id FROM PACKAGE_VERSIONS pv WHERE pv.PV_ID = nOldProdId )AND pv.PKG_VERSION = sNewProdIdVersion;recProd curProd%ROWTYPE;BEGIN/*--------------- Business Rules Here -------------------*/IF (nOldProdId IS NULL) OR (sNewProdIdVersion IS NULL) OR (nBaseEnvId IS NULL) OR (nSeqNum IS NULL)THENRAISE_APPLICATION_ERROR (-20000, 'Missing some parameters! nOldProdId= '|| nOldProdId ||', sNewProdIdVersion= '|| sNewProdIdVersion ||', nBaseEnvId= '|| nBaseEnvId ||', nSeqNum= '|| nSeqNum);END IF;/*-------------------------------------------------------*/-- Get new prod_idOPEN curProd;FETCH curProd INTO recProd;-- Get the Package NameSELECT pkg.pkg_name into sProdNameFROM PACKAGES pkg, PACKAGE_VERSIONS pvWHERE pkg.pkg_id = pv.pkg_idAND pv.pv_id = nOldProdId;IF curProd%FOUND THENnNewProdId := recProd.PV_ID;ELSE-- Call Release Manager to add new productSeed_Package_Names_Versions ( sProdName, sNewProdIdVersion, nUserId, outProdId );nNewProdId := outProdId;/*RAISE_APPLICATION_ERROR (-20000, 'Version '|| sNewProdIdVersion ||' does not exist.');*/END IF;CLOSE curProd;-- Update Product DetailsUPDATE BASE_ENV_CONTENTS SETPROD_ID = nNewProdId,BASE_ENV_COMMENTS = sComments,NOT_XML = sNotXMLWHERE BASE_ENV_ID = nBaseEnvIdAND PROD_ID = nOldProdId;UPDATE PACKAGES SETSUNOS_ELECTRONIC_NAME = sSunOSElectronicName,WIN_ELECTRONIC_NAME = sWinElectronicNameWHERE PKG_ID =(SELECT PKG_IDFROM PACKAGE_VERSIONSWHERE PACKAGE_VERSIONS.PV_ID = nNewProdId);UPDATE PACKAGE_VERSIONS SETPATCH_ELECTRONIC_NAME = sPatchElectronicNameWHERE PV_ID = nNewProdId;-- Update seq_numShift_Product_SeqNum ( nNewProdId, nBaseEnvId, nSeqNum );END Update_Product;/*--------------------------------------------------------------------------------------------------*/PROCEDURE Shift_Product_SeqNum ( nProdId IN BASE_ENV_CONTENTS.PROD_ID%TYPE,nBaseEnvId IN BASE_ENV_CONTENTS.BASE_ENV_ID%TYPE,nToSeqNum IN BASE_ENV_CONTENTS.SEQ_NUM%TYPE ) IScurrSeqNum NUMBER;FromSeqNum NUMBER;BEGIN-- Get Current seq_numSELECT bec.SEQ_NUM INTO currSeqNumFROM BASE_ENV_CONTENTS becWHERE bec.BASE_ENV_ID = nBaseEnvIdAND bec.PROD_ID = nProdId;IF currSeqNum > nToSeqNumTHENFromSeqNum := nToSeqNum;-- Shift others UpUPDATE BASE_ENV_CONTENTS bec SETbec.SEQ_NUM = bec.SEQ_NUM + 1WHERE bec.BASE_ENV_ID = nBaseEnvIdAND bec.PROD_ID != nProdIdAND bec.SEQ_NUM BETWEEN FromSeqNum AND currSeqNum - 1;ELSIF currSeqNum < nToSeqNumTHENFromSeqNum := currSeqNum + 1;-- Shift others DownUPDATE BASE_ENV_CONTENTS bec SETbec.SEQ_NUM = bec.SEQ_NUM - 1WHERE bec.BASE_ENV_ID = nBaseEnvIdAND bec.PROD_ID != nProdIdAND bec.SEQ_NUM BETWEEN FromSeqNum AND nToSeqNum;END IF;-- Move Product to new seq_numUPDATE BASE_ENV_CONTENTS bec SETbec.SEQ_NUM = nToSeqNumWHERE bec.BASE_ENV_ID = nBaseEnvIdAND bec.PROD_ID = nProdId;END Shift_Product_SeqNum;/*--------------------------------------------------------------------------------------------------*/PROCEDURE Sort_Products ( sProdIdOrder IN VARCHAR2,nBaseEnvId IN BASE_ENV_CONTENTS.BASE_ENV_ID%TYPE,nUserId IN NUMBER ) ISnIdCollector DEPMGR_NUMBER_TAB_t := DEPMGR_NUMBER_TAB_t();SeqNum NUMBER;BEGIN/*--------------- Business Rules Here -------------------*/-- Check for parametersIF (sProdIdOrder IS NULL) OR (nBaseEnvId IS NULL)THENRAISE_APPLICATION_ERROR (-20000, 'Missing some parameters! nBaseEnvId='|| nBaseEnvId ||' sProdIdOrder='|| sProdIdOrder );END IF;/*-------------------------------------------------------*/nIdCollector := IN_LIST_NUMBER ( sProdIdOrder );-- Set start sequence numberSeqNum := 1;FOR i IN 1..nIdCollector.COUNTLOOP-- Update new sequence numberUPDATE BASE_ENV_CONTENTS osc SETosc.SEQ_NUM = SeqNumWHERE osc.BASE_ENV_ID = nBaseEnvIdAND osc.PROD_ID = nIdCollector(i);SeqNum := SeqNum + 1;END LOOP;END Sort_Products;/*--------------------------------------------------------------------------------------------------*/END pk_Base_Env;//---------------------------------------------------------- DDL for Package Body PK_BASE_ENV_TEST--------------------------------------------------------CREATE OR REPLACE PACKAGE BODY "PK_BASE_ENV_TEST"IS/* ---------------------------------------------------------------------------Version: 1.0.0--------------------------------------------------------------------------- *//*--------------------------------------------------------------------------------------------------*/PROCEDURE Add_Base_Env ( sBaseEnvName IN BASE_ENV.BASE_ENV_NAME%TYPE,nBosId IN BASE_ENV.BOS_ID%TYPE,nBomId IN BASE_ENV.BOM_ID%TYPE,nOsId IN OS_BASE_ENV.OS_ID%TYPE ) ISnBaseEnvId BASE_ENV.BASE_ENV_ID%TYPE;BEGIN/*--------------- Business Rules Here -------------------*//*-------------------------------------------------------*/-- Get base_env_idSELECT SEQ_BASE_ENV_ID.NEXTVAL INTO nBaseEnvId FROM DUAL;-- Insert new base env nameINSERT INTO BASE_ENV ( BASE_ENV_ID, BOS_ID, BASE_ENV_NAME, BOM_ID )VALUES ( nBaseEnvId, nBosId, sBaseEnvName, nBomId );EXCEPTIONWHEN DUP_VAL_ON_INDEXTHENRAISE_APPLICATION_ERROR (-20000, 'Base Configuration Name "'|| sBaseEnvName ||'" is Already Used in this BOM.');END Add_Base_Env;/*--------------------------------------------------------------------------------------------------*/PROCEDURE Update_Base_Env ( nBaseEnvId IN BASE_ENV.BASE_ENV_ID%TYPE,sBaseEnvName IN BASE_ENV.BASE_ENV_NAME%TYPE,nBosId IN BASE_ENV.BOS_ID%TYPE ) ISBEGIN/*--------------- Business Rules Here -------------------*/IF (nBaseEnvId IS NULL)THENRAISE_APPLICATION_ERROR (-20000, 'Missing some parameters! nBaseEnvId= '|| nBaseEnvId );END IF;/*-------------------------------------------------------*/-- Update Base EnvUPDATE BASE_ENV SETBASE_ENV_NAME = sBaseEnvName,BOS_ID = nBosIdWHERE BASE_ENV_ID = nBaseEnvId;EXCEPTIONWHEN DUP_VAL_ON_INDEXTHENRAISE_APPLICATION_ERROR (-20000, 'Base Configuration Name "'|| sBaseEnvName ||'" is Already Used in this BOM.');END Update_Base_Env;/*--------------------------------------------------------------------------------------------------*/FUNCTION Get_Base_Env_Id ( sBaseEnvName IN BASE_ENV.BASE_ENV_NAME%TYPE,nBomId IN BASE_ENV.BOM_ID%TYPE ) RETURN BASE_ENV.BASE_ENV_ID%TYPE ISReturnValue BASE_ENV.BASE_ENV_ID%TYPE;CURSOR curBaseEnv ISSELECT be.BASE_ENV_IDFROM BASE_ENV beWHERE be.BASE_ENV_NAME = sBaseEnvNameAND be.BOM_ID = nBomId;recBaseEnv curBaseEnv%ROWTYPE;BEGIN/*--------------- Business Rules Here -------------------*//*-------------------------------------------------------*/-- Get bom_name_idOPEN curBaseEnv;FETCH curBaseEnv INTO recBaseEnv;IF curBaseEnv%FOUND THENReturnValue := recBaseEnv.BASE_ENV_ID;ELSEReturnValue := NULL;END IF;CLOSE curBaseEnv;RETURN ReturnValue;END Get_Base_Env_Id;/*--------------------------------------------------------------------------------------------------*/PROCEDURE Destroy_Base_Env ( nBaseEnvId IN BASE_ENV.BASE_ENV_ID%TYPE ) ISBEGIN/*--------------- Business Rules Here -------------------*/IF (nBaseEnvId IS NULL)THENRAISE_APPLICATION_ERROR (-20000, 'Missing some parameters! nBaseEnvId= '|| nBaseEnvId );END IF;/*-------------------------------------------------------*/-- Destroy Base Env ContentsDELETEFROM BASE_ENV_CONTENTSWHERE BASE_ENV_ID = nBaseEnvId;-- Remove Base Env From BOMDELETEFROM OS_BASE_ENV obeWHERE obe.BASE_ENV_ID = nBaseEnvId;-- Destroy Base EnvDELETEFROM BASE_ENV beWHERE be.BASE_ENV_ID = nBaseEnvId;END Destroy_Base_Env;/*--------------------------------------------------------------------------------------------------*/PROCEDURE Add_Product ( sProdIdList IN VARCHAR2,nBaseEnvId IN BASE_ENV.BASE_ENV_ID%TYPE,nUserId IN NUMBER ) ISNextSeqNum NUMBER;CURSOR prod_cur ISSELECT pv.pv_id AS PROD_ID,pv.PV_DESCRIPTIONFROM PACKAGE_VERSIONS pvWHERE pv.pv_id IN (SELECT *FROM THE ( SELECT CAST( IN_LIST_NUMBER( sProdIdList ) AS DEPMGR_NUMBER_TAB_t ) FROM dual ));prod_rec prod_cur%ROWTYPE;BEGIN/*--------------- Business Rules Here -------------------*//*-------------------------------------------------------*/-- Get seq_numSELECT MAX( bec.SEQ_NUM ) INTO NextSeqNumFROM BASE_ENV_CONTENTS becWHERE bec.BASE_ENV_ID = nBaseEnvId;-- Set NextSeqNum to 0 if no existing products are foundIF NextSeqNum IS NULL THENNextSeqNum := 1;ELSENextSeqNum := NextSeqNum + 1;END IF;-- Insert into OS ContentsOPEN prod_cur;FETCH prod_cur INTO prod_rec;WHILE prod_cur%FOUNDLOOPINSERT INTO BASE_ENV_CONTENTS ( BASE_ENV_ID, SEQ_NUM, PROD_ID, BASE_ENV_COMMENTS )VALUES (nBaseEnvId,NextSeqNum,prod_rec.PROD_ID,prod_rec.PV_DESCRIPTION);-- Log Action --pk_Utils.Log_Action ( nUserId,1,'pk_Base_Env.Add_Product with BASE_ENV_ID='|| nBaseEnvId ||', SEQ_NUM='|| NextSeqNum ||', PROD_ID='|| prod_rec.PROD_ID,NULL, NULL );NextSeqNum := NextSeqNum + 1;FETCH prod_cur INTO prod_rec;END LOOP;CLOSE prod_cur;END Add_Product;/*--------------------------------------------------------------------------------------------------*/PROCEDURE Remove_Products ( sProdIdList IN VARCHAR2,nBaseEnvId IN BASE_ENV_CONTENTS.BASE_ENV_ID%TYPE,nUserId IN NUMBER ) ISNextSeqNum NUMBER := 1;CURSOR prod_cur ISSELECT bec.PROD_ID, bec.SEQ_NUMFROM BASE_ENV_CONTENTS becWHERE bec.BASE_ENV_ID = nBaseEnvIdORDER BY bec.SEQ_NUM;prod_rec prod_cur%ROWTYPE;BEGIN/*--------------- Business Rules Here -------------------*/IF sProdIdList IS NULLTHENRAISE_APPLICATION_ERROR (-20000, 'Please select one or more Products.');END IF;/*-------------------------------------------------------*/-- Remove ProductsDELETEFROM BASE_ENV_CONTENTSWHERE BASE_ENV_ID = nBaseEnvIdAND PROD_ID IN (SELECT *FROM THE ( SELECT CAST( IN_LIST_NUMBER( sProdIdList ) AS DEPMGR_NUMBER_TAB_t ) FROM dual ));-- Update Seq NumbersOPEN prod_cur;FETCH prod_cur INTO prod_rec;WHILE prod_cur%FOUNDLOOPUPDATE BASE_ENV_CONTENTS SETSEQ_NUM = NextSeqNumWHERE BASE_ENV_ID = nBaseEnvIdAND PROD_ID = prod_rec.PROD_ID;NextSeqNum := NextSeqNum + 1;FETCH prod_cur INTO prod_rec;END LOOP;CLOSE prod_cur;END Remove_Products;/*--------------------------------------------------------------------------------------------------*/PROCEDURE Add_Product_Wrap ( nPkgId IN NUMBER,sProdVersion IN VARCHAR2,nBaseEnvId IN BASE_ENV_CONTENTS.BASE_ENV_ID%TYPE,nUserId IN NUMBER,sComments IN VARCHAR2 ) ISProdId PACKAGE_VERSIONS.PV_ID%TYPE;ProdName PACKAGES.PKG_NAME%TYPE;CURSOR prod_cur ISSELECT bec.PROD_IDFROM BASE_ENV_CONTENTS becWHERE bec.PROD_ID = ProdIdAND bec.BASE_ENV_ID = nBaseEnvId;prod_rec prod_cur%ROWTYPE;BEGIN/*--------------- Business Rules Here -------------------*/IF (NOT nPkgId IS NULL) AND (sProdVersion IS NULL) AND (NOT nBaseEnvId IS NULL) AND (NOT nUserId IS NULL) THENRAISE_APPLICATION_ERROR (-20000, 'Please provide Product Version.('|| sProdVersion ||')' );ELSIF (nPkgId IS NULL) OR (sProdVersion IS NULL) OR (nBaseEnvId IS NULL) OR (nUserId IS NULL) THENRAISE_APPLICATION_ERROR (-20000, 'Missing some parameters! nPkgId= '|| nPkgId ||', sProdVersion='|| sProdVersion ||', nBaseEnvId='|| nBaseEnvId ||', nUserId='|| nUserId );END IF;/*-------------------------------------------------------*/-- Get Product Name --SELECT PKG_NAME INTO ProdName FROM PACKAGES WHERE PKG_ID = nPkgId;-- Create Product Version in Release Manager if Required, and Get Prod_id --Seed_Package_Names_Versions ( ProdName, sProdVersion, nUserId, ProdId );-- Add Product To Base Env --OPEN prod_cur;FETCH prod_cur INTO prod_rec;IF prod_cur%NOTFOUND THENpk_Base_Env.Add_Product ( ProdId, nBaseEnvId, nUserId );-- Update CommentsIF (NOT sComments IS NULL) THENUPDATE BASE_ENV_CONTENTS bec SETbec.BASE_ENV_COMMENTS = sCommentsWHERE bec.BASE_ENV_ID = nBaseEnvIdAND bec.PROD_ID = ProdId;END IF;END IF;CLOSE prod_cur;END Add_Product_Wrap;/*--------------------------------------------------------------------------------------------------*/PROCEDURE Update_Product ( nOldProdId IN BASE_ENV_CONTENTS.PROD_ID%TYPE,sNewProdIdVersion IN VARCHAR2,nBaseEnvId IN BASE_ENV_CONTENTS.BASE_ENV_ID%TYPE,nSeqNum IN BASE_ENV_CONTENTS.SEQ_NUM%TYPE,sComments IN BASE_ENV_CONTENTS.BASE_ENV_COMMENTS%TYPE,sElectronicName IN PACKAGES.ELECTRONIC_NAME%TYPE ) ISnNewProdId BASE_ENV_CONTENTS.PROD_ID%TYPE;CURSOR curProd ISSELECT pv.PV_IDFROM PACKAGE_VERSIONS pvWHERE pv.pkg_id IN ( SELECT pv.pkg_id FROM PACKAGE_VERSIONS pv WHERE pv.PV_ID = nOldProdId )AND pv.PKG_VERSION = sNewProdIdVersion;recProd curProd%ROWTYPE;BEGIN/*--------------- Business Rules Here -------------------*/IF (nOldProdId IS NULL) OR (sNewProdIdVersion IS NULL) OR (nBaseEnvId IS NULL) OR (nSeqNum IS NULL)THENRAISE_APPLICATION_ERROR (-20000, 'Missing some parameters! nOldProdId= '|| nOldProdId ||', sNewProdIdVersion= '|| sNewProdIdVersion ||', nBaseEnvId= '|| nBaseEnvId ||', nSeqNum= '|| nSeqNum);END IF;/*-------------------------------------------------------*/-- Get new prod_idOPEN curProd;FETCH curProd INTO recProd;IF curProd%FOUND THENnNewProdId := recProd.PV_ID;ELSERAISE_APPLICATION_ERROR (-20000, 'Version '|| sNewProdIdVersion ||' does not exist.');END IF;CLOSE curProd;-- Update Product DetailsUPDATE BASE_ENV_CONTENTS SETPROD_ID = nNewProdId,BASE_ENV_COMMENTS = sCommentsWHERE BASE_ENV_ID = nBaseEnvIdAND PROD_ID = nOldProdId;UPDATE PACKAGES SETELECTRONIC_NAME = sElectronicNameWHERE PKG_ID =(SELECT PKG_IDFROM PACKAGE_VERSIONSWHERE PACKAGE_VERSIONS.PV_ID = nNewProdId);-- Update seq_numShift_Product_SeqNum ( nNewProdId, nBaseEnvId, nSeqNum );END Update_Product;/*--------------------------------------------------------------------------------------------------*/PROCEDURE Shift_Product_SeqNum ( nProdId IN BASE_ENV_CONTENTS.PROD_ID%TYPE,nBaseEnvId IN BASE_ENV_CONTENTS.BASE_ENV_ID%TYPE,nToSeqNum IN BASE_ENV_CONTENTS.SEQ_NUM%TYPE ) IScurrSeqNum NUMBER;FromSeqNum NUMBER;BEGIN-- Get Current seq_numSELECT bec.SEQ_NUM INTO currSeqNumFROM BASE_ENV_CONTENTS becWHERE bec.BASE_ENV_ID = nBaseEnvIdAND bec.PROD_ID = nProdId;IF currSeqNum > nToSeqNumTHENFromSeqNum := nToSeqNum;-- Shift others UpUPDATE BASE_ENV_CONTENTS bec SETbec.SEQ_NUM = bec.SEQ_NUM + 1WHERE bec.BASE_ENV_ID = nBaseEnvIdAND bec.PROD_ID != nProdIdAND bec.SEQ_NUM BETWEEN FromSeqNum AND currSeqNum - 1;ELSIF currSeqNum < nToSeqNumTHENFromSeqNum := currSeqNum + 1;-- Shift others DownUPDATE BASE_ENV_CONTENTS bec SETbec.SEQ_NUM = bec.SEQ_NUM - 1WHERE bec.BASE_ENV_ID = nBaseEnvIdAND bec.PROD_ID != nProdIdAND bec.SEQ_NUM BETWEEN FromSeqNum AND nToSeqNum;END IF;-- Move Product to new seq_numUPDATE BASE_ENV_CONTENTS bec SETbec.SEQ_NUM = nToSeqNumWHERE bec.BASE_ENV_ID = nBaseEnvIdAND bec.PROD_ID = nProdId;END Shift_Product_SeqNum;/*--------------------------------------------------------------------------------------------------*/PROCEDURE Sort_Products ( sProdIdOrder IN VARCHAR2,nBaseEnvId IN BASE_ENV_CONTENTS.BASE_ENV_ID%TYPE,nUserId IN NUMBER ) ISnIdCollector DEPMGR_NUMBER_TAB_t := DEPMGR_NUMBER_TAB_t();SeqNum NUMBER;BEGIN/*--------------- Business Rules Here -------------------*/-- Check for parametersIF (sProdIdOrder IS NULL) OR (nBaseEnvId IS NULL)THENRAISE_APPLICATION_ERROR (-20000, 'Missing some parameters! nBaseEnvId='|| nBaseEnvId ||' sProdIdOrder='|| sProdIdOrder );END IF;/*-------------------------------------------------------*/nIdCollector := IN_LIST_NUMBER ( sProdIdOrder );-- Set start sequence numberSeqNum := 1;FOR i IN 1..nIdCollector.COUNTLOOP-- Update new sequence numberUPDATE BASE_ENV_CONTENTS osc SETosc.SEQ_NUM = SeqNumWHERE osc.BASE_ENV_ID = nBaseEnvIdAND osc.PROD_ID = nIdCollector(i);SeqNum := SeqNum + 1;END LOOP;END Sort_Products;/*--------------------------------------------------------------------------------------------------*/END pk_Base_Env_test;//---------------------------------------------------------- DDL for Package Body PK_BOM--------------------------------------------------------CREATE OR REPLACE PACKAGE BODY "PK_BOM"IS/* ---------------------------------------------------------------------------Version: 2.0--------------------------------------------------------------------------- *//*--------------------------------------------------------------------------------------------------*/PROCEDURE Add_Bom ( sBomName IN BOM_NAMES.BOM_NAME%TYPE DEFAULT NULL,sBomVersion IN BOMS.BOM_VERSION%TYPE,sBomLifeCycle IN BOMS.BOM_LIFECYCLE%TYPE,sBomComments IN BOMS.BOM_COMMENTS%TYPE DEFAULT NULL,nToBranchId IN BOMS.BRANCH_ID%TYPE,nFromBomId IN BOMS.BOM_ID%TYPE DEFAULT NULL,nRtagId IN BOMS.RTAG_ID_FK%TYPE,nUserId IN BOM_STATE.USER_ID%TYPE ) ISBomId BOMS.BOM_ID%TYPE;ParentBomId BOMS.PARENT_BOM_ID%TYPE;BomNameId BOM_NAMES.BOM_NAME_ID%TYPE;BomVersion BOMS.BOM_VERSION%TYPE;BomLifeCycle BOMS.BOM_LIFECYCLE%TYPE;ProjId BRANCHES.PROJ_ID%TYPE;CURSOR curBom ISSELECT bo.BOM_IDFROM BOMS bo,BRANCHES brWHERE bo.BRANCH_ID = br.BRANCH_IDAND br.PROJ_ID = ProjIdAND bo.BOM_NAME_ID = BomNameIdAND bo.BOM_VERSION = BomVersionAND bo.BOM_LIFECYCLE = BomLifeCycle;recBom curBom%ROWTYPE;DUPLICATE_BOM EXCEPTION;BEGIN/*--------------- Business Rules Here -------------------*/IF (nToBranchId IS NULL) OR (nUserId IS NULL)THENRAISE_APPLICATION_ERROR (-20000, 'Missing some parameters! nToBranchId= '|| nToBranchId ||', nUserId='|| nUserId );END IF;/*-------------------------------------------------------*/-- Get bom_idSELECT SEQ_BOM_ID.NEXTVAL INTO BomId FROM DUAL;-- Get bom_name_idIF nFromBomId IS NULL THENBomNameId := New_Bom_Name ( sBomName );ParentBomId := BomId;ELSEBomNameId := New_Bom_Name ( nFromBomId );ParentBomId := nFromBomId;END IF;-- Get Bom Version and Lifecycle--Get_Next_Version ( nFromBomId, nToBranchId, BomVersion, BomLifeCycle, ParentBomId, BomNameId );-- Get proj_idSELECT br.PROJ_ID INTO ProjIdFROM BRANCHES brWHERE br.BRANCH_ID = nToBranchId;-- Check for duplicate BOMsOPEN curBom;FETCH curBom INTO recBom;IF curBom%FOUNDTHENRAISE DUPLICATE_BOM;END IF;CLOSE curBom;-- Add Blank Bom --INSERT INTO BOMS ( BOM_ID, BRANCH_ID, BOM_NAME_ID, BOM_COMMENTS, BOM_VERSION, BOM_LIFECYCLE, IS_READONLY, IS_HIDDEN, PARENT_BOM_ID, RTAG_ID_FK )VALUES ( BomId, nToBranchId, BomNameId, sBomComments, sBomVersion, sBomLifeCycle, 'N', 'N', ParentBomId, nRtagId );/* Log BOM_TRAIL */pk_Bom.Log_Bom_Trail ( BomId, 'CREATED: Version '|| sBomVersion ||'.'|| sBomLifeCycle , nUserId );IF nFromBomId IS NOT NULL THEN/* Add Bom Clonned from ... */pk_Utils.Clone_BOM ( nFromBomId, BomId, nUserId );END IF;--- Set Bom State ---Release_To ( BomId, nUserId );EXCEPTIONWHEN DUPLICATE_BOMTHENRAISE_APPLICATION_ERROR (-20000, 'BOM "'|| sBomName ||' '|| BomVersion ||'.'|| BomLifeCycle ||'" is Already Used in this Project.');END Add_Bom;/*--------------------------------------------------------------------------------------------------*/PROCEDURE Update_Bom ( nBomId IN BOMS.BOM_ID%TYPE,nRtagId IN BOMS.RTAG_ID_FK%TYPE,sBomComment IN BOMS.BOM_COMMENTS%TYPE,nUserId IN NUMBER ) ISReleaseName VARCHAR2(100);BEGIN/*--------------- Business Rules Here -------------------*//*-------------------------------------------------------*/--- Update Bom Properties ---UPDATE BOMS bo SETbo.RTAG_ID_FK = nRtagId,bo.BOM_COMMENTS = sBomCommentWHERE bo.BOM_ID = nBomId;SELECT RTAG_NAME INTO ReleaseNameFROM RELEASE_TAGSWHERE RTAG_ID = nRtagId;/* Log BOM_TRAIL */pk_Bom.Log_Bom_Trail ( nBomId, 'Updated BOM properties: Set Release Manager Reference ='||ReleaseName||' and comments ='||sBomComment, nUserId );END Update_Bom;/*--------------------------------------------------------------------------------------------------*/PROCEDURE Update_ProdBom ( nBomId IN BOMS.BOM_ID%TYPE,nRtagId IN BOMS.RTAG_ID_FK%TYPE,sBomComment IN BOMS.BOM_COMMENTS%TYPE,nUserId IN NUMBER ) ISReleaseName VARCHAR2(100);BEGIN/*--------------- Business Rules Here -------------------*//*-------------------------------------------------------*/--- Update Bom Properties ---UPDATE BOMS bo SETbo.RTAG_ID_FK = nRtagId,bo.BOM_COMMENTS = sBomCommentWHERE bo.BOM_ID = nBomId;SELECT BRANCH_NAME INTO ReleaseNameFROM BRANCHESWHERE BRANCH_ID = nRtagId;/* Log BOM_TRAIL */pk_Bom.Log_Bom_Trail ( nBomId, 'Updated BOM properties: Set Deployment Manager Reference ='||ReleaseName||' and comments ='||sBomComment, nUserId );END Update_ProdBom;/*--------------------------------------------------------------------------------------------------*/FUNCTION New_Bom_Name ( sBomName IN BOM_NAMES.BOM_NAME%TYPE ) RETURN BOM_NAMES.BOM_NAME_ID%TYPE ISReturnValue BOM_NAMES.BOM_NAME_ID%TYPE;CURSOR curBomNames ISSELECT bn.BOM_NAME_IDFROM BOM_NAMES bnWHERE bn.BOM_NAME = sBomName;recBomNames curBomNames%ROWTYPE;BEGIN/*--------------- Business Rules Here -------------------*//*-------------------------------------------------------*/-- Check for existance of this nameOPEN curBomNames;FETCH curBomNames INTO recBomNames;ReturnValue := NULL;IF curBomNames%FOUND THENReturnValue := recBomNames.BOM_NAME_ID;END IF;CLOSE curBomNames;-- Finnaly Create Name if required --IF ReturnValue IS NULL THEN-- Get bom_name_idSELECT SEQ_BOM_NAME_ID.NEXTVAL INTO ReturnValue FROM DUAL;INSERT INTO BOM_NAMES ( BOM_NAME_ID, BOM_NAME )VALUES ( ReturnValue, sBomName );END IF;RETURN ReturnValue;END New_Bom_Name;/*--------------------------------------------------------------------------------------------------*/FUNCTION New_Bom_Name ( nFromBomId IN BOMS.BOM_NAME_ID%TYPE ) RETURN BOMS.BOM_NAME_ID%TYPE ISReturnValue BOMS.BOM_NAME_ID%TYPE;BEGIN/*--------------- Business Rules Here -------------------*//*-------------------------------------------------------*/-- Get bom_name_idSELECT bo.BOM_NAME_ID INTO ReturnValueFROM BOMS boWHERE bo.BOM_ID = nFromBomId;RETURN ReturnValue;END New_Bom_Name;/*--------------------------------------------------------------------------------------------------*/PROCEDURE Accept_Reject ( nBomId IN BOMS.BOM_ID%TYPE,cIsRejected IN BOMS.IS_REJECTED%TYPE,sComments IN REJECTION_TRAIL.COMMENTS%TYPE,nStateId IN BOM_STATE.STATE_ID%TYPE DEFAULT NULL,nUserId IN REJECTION_TRAIL.USER_ID%TYPE ) ISStateName STATES.STATE_NAME%TYPE;IsOfficial BOMS.IS_READONLY%TYPE;IsRejected VARCHAR2(50);BEGIN/*--------------- Business Rules Here -------------------*/-- Check if Bom is officialSELECT bo.IS_READONLY INTO IsOfficialFROM BOMS boWHERE bo.BOM_ID = nBomId;IF IsOfficial != 'Y' THENRAISE_APPLICATION_ERROR (-20000, 'BOM must be locked first.' );END IF;/*-------------------------------------------------------*/-- Get State NameSELECT st.STATE_NAME INTO StateNameFROM STATES stWHERE st.STATE_ID = nStateId;-- AcceptReject BOMUPDATE BOMS bo SETbo.IS_REJECTED = cIsRejectedWHERE bo.BOM_ID = nBomId;-- Auto-Hide BomIF cIsRejected = 'Y' THENpk_State.Auto_Hide ( nStateId, nBomId, GET_SYSTEM_VALUE('enumEVENT_ON_REJECT_BOM') );ELSEpk_State.Auto_Hide ( nStateId, nBomId, GET_SYSTEM_VALUE('enumEVENT_ON_ACCEPT_BOM') );END IF;-- Log Rejection Trailpk_Utils.Log_Rejection_Trail ( GET_SYSTEM_VALUE('enumENTITY_TYPE_BOM'), nBomId, cIsRejected, nUserId, sComments );/* Log BOM_TRAIL */-- Get Log TitleIsRejected := 'ACCEPTED';IF cIsRejected = 'Y' THENIsRejected := 'REJECTED';END IF;-- Finnaly log itLog_Bom_Trail ( nBomId, IsRejected ||': By '|| StateName, nUserId, sComments );END Accept_Reject;/*--------------------------------------------------------------------------------------------------*/PROCEDURE Set_Bom_Official ( nBomId IN BOMS.BOM_ID%TYPE,cIsOfficial IN BOMS.IS_READONLY%TYPE,nStateId IN BOM_STATE.STATE_ID%TYPE,nUserId IN NUMBER ) ISBEGIN/*--------------- Business Rules Here -------------------*//*-------------------------------------------------------*/--- Set Bom Official or Not ---UPDATE BOMS bo SETbo.IS_READONLY = cIsOfficialWHERE bo.BOM_ID = nBomId;-- Auto-Hide BomIF cIsOfficial = 'Y' THENpk_State.Auto_Hide ( nStateId, nBomId, GET_SYSTEM_VALUE('enumEVENT_ON_LOCK_BOM') );-- Update node versionUpdate_Nodes_Version ( nBomId, nUserId );ELSEpk_State.Auto_Hide ( nStateId, nBomId, GET_SYSTEM_VALUE('enumEVENT_ON_UNLOCK_BOM') );END IF;/* Log BOM_TRAIL */pk_Bom.Log_Bom_Trail ( nBomId, 'LOCKED: '|| cIsOfficial, nUserId );END Set_Bom_Official;/*--------------------------------------------------------------------------------------------------*/PROCEDURE Update_Nodes_Version ( nBomId IN BOMS.BOM_ID%TYPE,nUserId IN NUMBER ) ISCompareBomId NUMBER;ProjId NUMBER;BomVersion VARCHAR2(4000);CURSOR curUpdateNode ISSELECT DISTINCTnlook.NODE_ID,DECODE ( nlc.LAST_LIFE_CYCLE,NULL, 0,nlc.LAST_LIFE_CYCLE + 1 ) AS NEW_LIFE_CYCLEFROM (/* ADDED PATCHES ONLY */SELECT 'A' AS CHANGE_TYPE, newbom.PROD_ID, newbom.PKFROM (SELECT osc.PROD_ID,nn.NODE_NAME ||'_'|| os.OS_NAME AS PKFROM OS_CONTENTS osc,OPERATING_SYSTEMS os,NETWORK_NODES nn,BOM_CONTENTS bc,PACKAGE_VERSIONS pvWHERE osc.OS_ID = os.OS_IDAND os.NODE_ID = nn.NODE_IDAND bc.NODE_ID = nn.NODE_IDAND osc.PROD_ID = pv.PV_IDAND NOT pv.IS_PATCH IS NULLAND bc.BOM_ID = nBomId) newbom,(SELECT osc.PROD_ID,nn.NODE_NAME ||'_'|| os.OS_NAME AS PKFROM OS_CONTENTS osc,OPERATING_SYSTEMS os,NETWORK_NODES nn,BOM_CONTENTS bc,PACKAGE_VERSIONS pvWHERE osc.OS_ID = os.OS_IDAND os.NODE_ID = nn.NODE_IDAND bc.NODE_ID = nn.NODE_IDAND osc.PROD_ID = pv.PV_IDAND NOT pv.IS_PATCH IS NULLAND bc.BOM_ID = CompareBomId) oldbomWHERE newbom.PK = oldbom.PK (+)AND newbom.PROD_ID = oldbom.PROD_ID (+)AND oldbom.PK IS NULLUNION/* ADDED PATCHES ONLY TO BASE CONFIGURATION */SELECT 'A' AS CHANGE_TYPE, newbom.PROD_ID, newbom.PKFROM (SELECT bec.PROD_ID,nn.NODE_NAME ||'_'|| os.OS_NAME AS PKFROM BASE_ENV_CONTENTS bec,OS_BASE_ENV obe,OPERATING_SYSTEMS os,NETWORK_NODES nn,BOM_CONTENTS bc,PACKAGE_VERSIONS pvWHERE obe.OS_ID = os.OS_IDAND bec.BASE_ENV_ID = obe.BASE_ENV_IDAND os.NODE_ID = nn.NODE_IDAND bc.NODE_ID = nn.NODE_IDAND bec.PROD_ID = pv.PV_IDAND NOT pv.IS_PATCH IS NULLAND bc.BOM_ID = nBomId) newbom,(SELECT bec.PROD_ID,nn.NODE_NAME ||'_'|| os.OS_NAME AS PKFROM BASE_ENV_CONTENTS bec,OS_BASE_ENV obe,OPERATING_SYSTEMS os,NETWORK_NODES nn,BOM_CONTENTS bc,PACKAGE_VERSIONS pvWHERE obe.OS_ID = os.OS_IDAND bec.BASE_ENV_ID = obe.BASE_ENV_IDAND os.NODE_ID = nn.NODE_IDAND bc.NODE_ID = nn.NODE_IDAND bec.PROD_ID = pv.PV_IDAND NOT pv.IS_PATCH IS NULLAND bc.BOM_ID = CompareBomId) oldbomWHERE newbom.PK = oldbom.PK (+)AND newbom.PROD_ID = oldbom.PROD_ID (+)AND oldbom.PK IS NULLUNION/* ADDED PRODUCTS ONLY */SELECT DECODE( oldbom.PROD_ID,NULL, 'A', 'U' ) AS CHANGE_TYPE,newbom.PROD_ID, newbom.PKFROM (SELECT osc.PROD_ID,nn.NODE_NAME ||'_'|| os.OS_NAME AS PK,pv.PKG_ID,NVL( pv.V_EXT, '|LINK_A_NULL|' ) AS V_EXTFROM OS_CONTENTS osc,OPERATING_SYSTEMS os,NETWORK_NODES nn,BOM_CONTENTS bc,PACKAGE_VERSIONS pvWHERE osc.OS_ID = os.OS_IDAND os.NODE_ID = nn.NODE_IDAND bc.NODE_ID = nn.NODE_IDAND osc.PROD_ID = pv.PV_IDAND pv.IS_PATCH IS NULLAND bc.BOM_ID = nBomId) newbom,(SELECT osc.PROD_ID,nn.NODE_NAME ||'_'|| os.OS_NAME AS PK,pv.PKG_ID,NVL( pv.V_EXT, '|LINK_A_NULL|' ) AS V_EXTFROM OS_CONTENTS osc,OPERATING_SYSTEMS os,NETWORK_NODES nn,BOM_CONTENTS bc,PACKAGE_VERSIONS pvWHERE osc.OS_ID = os.OS_IDAND os.NODE_ID = nn.NODE_IDAND bc.NODE_ID = nn.NODE_IDAND osc.PROD_ID = pv.PV_IDAND pv.IS_PATCH IS NULLAND bc.BOM_ID = CompareBomId) oldbomWHERE newbom.PK = oldbom.PK (+)--AND newbom.PROD_ID = oldbom.PROD_ID (+)AND newbom.PKG_ID = oldbom.PKG_ID (+)AND newbom.V_EXT = oldbom.V_EXT (+)AND ( oldbom.PROD_ID != newbom.PROD_ID OR oldbom.PROD_ID IS NULL )AND newbom.PROD_ID NOT IN(SELECT osc.PROD_IDFROM OS_CONTENTS osc,OPERATING_SYSTEMS os,NETWORK_NODES nn,BOM_CONTENTS bc,PACKAGE_VERSIONS pvWHERE osc.OS_ID = os.OS_IDAND os.NODE_ID = nn.NODE_IDAND bc.NODE_ID = nn.NODE_IDAND osc.PROD_ID = pv.PV_IDAND pv.IS_PATCH IS NULLAND bc.BOM_ID = CompareBomId)UNION/* ADDED PRODUCTS ONLY TO BASE CONFIGURATION */SELECT DECODE( oldbom.PROD_ID,NULL, 'A', 'U' ) AS CHANGE_TYPE,newbom.PROD_ID, newbom.PKFROM (SELECT bec.PROD_ID,nn.NODE_NAME ||'_'|| os.OS_NAME AS PK,pv.PKG_ID,NVL( pv.V_EXT, '|LINK_A_NULL|' ) AS V_EXTFROM BASE_ENV_CONTENTS bec,OS_BASE_ENV obe,OPERATING_SYSTEMS os,NETWORK_NODES nn,BOM_CONTENTS bc,PACKAGE_VERSIONS pvWHERE obe.OS_ID = os.OS_IDAND bec.BASE_ENV_ID = obe.BASE_ENV_IDAND os.NODE_ID = nn.NODE_IDAND bc.NODE_ID = nn.NODE_IDAND bec.PROD_ID = pv.PV_IDAND pv.IS_PATCH IS NULLAND bc.BOM_ID = nBomId) newbom,(SELECT bec.PROD_ID,nn.NODE_NAME ||'_'|| os.OS_NAME AS PK,pv.PKG_ID,NVL( pv.V_EXT, '|LINK_A_NULL|' ) AS V_EXTFROM BASE_ENV_CONTENTS bec,OS_BASE_ENV obe,OPERATING_SYSTEMS os,NETWORK_NODES nn,BOM_CONTENTS bc,PACKAGE_VERSIONS pvWHERE obe.OS_ID = os.OS_IDAND bec.BASE_ENV_ID = obe.BASE_ENV_IDAND os.NODE_ID = nn.NODE_IDAND bc.NODE_ID = nn.NODE_IDAND bec.PROD_ID = pv.PV_IDAND pv.IS_PATCH IS NULLAND bc.BOM_ID = CompareBomId) oldbomWHERE newbom.PK = oldbom.PK (+)--AND newbom.PROD_ID = oldbom.PROD_ID (+)AND newbom.PKG_ID = oldbom.PKG_ID (+)AND newbom.V_EXT = oldbom.V_EXT (+)AND ( oldbom.PROD_ID != newbom.PROD_ID OR oldbom.PROD_ID IS NULL )UNION/* REMOVED PRODUCTS ONLY*/SELECT 'R' AS CHANGE_TYPE, oldbom.PROD_ID, oldbom.PKFROM (SELECT osc.PROD_ID,nn.NODE_NAME ||'_'|| os.OS_NAME AS PK,pv.PKG_ID,NVL( pv.V_EXT, '|LINK_A_NULL|' ) AS V_EXTFROM OS_CONTENTS osc,OPERATING_SYSTEMS os,NETWORK_NODES nn,BOM_CONTENTS bc,PACKAGE_VERSIONS pvWHERE osc.OS_ID = os.OS_IDAND os.NODE_ID = nn.NODE_IDAND bc.NODE_ID = nn.NODE_IDAND osc.PROD_ID = pv.PV_IDAND pv.IS_PATCH IS NULLAND bc.BOM_ID = nBomId) newbom,(SELECT osc.PROD_ID,nn.NODE_NAME ||'_'|| os.OS_NAME AS PK,pv.PKG_ID,NVL( pv.V_EXT, '|LINK_A_NULL|' ) AS V_EXTFROM OS_CONTENTS osc,OPERATING_SYSTEMS os,NETWORK_NODES nn,BOM_CONTENTS bc,PACKAGE_VERSIONS pvWHERE osc.OS_ID = os.OS_IDAND os.NODE_ID = nn.NODE_IDAND bc.NODE_ID = nn.NODE_IDAND osc.PROD_ID = pv.PV_IDAND pv.IS_PATCH IS NULLAND bc.BOM_ID = CompareBomId) oldbomWHERE newbom.PK (+) = oldbom.PK--AND newbom.PROD_ID (+) = oldbom.PROD_IDAND newbom.PKG_ID (+) = oldbom.PKG_IDAND newbom.V_EXT (+) = oldbom.V_EXTAND newbom.PK IS NULLUNION/* REMOVED PATCHES */SELECT 'R' AS CHANGE_TYPE, oldbom.PROD_ID, oldbom.PKFROM (SELECT osc.PROD_ID,nn.NODE_NAME ||'_'|| os.OS_NAME AS PKFROM OS_CONTENTS osc,OPERATING_SYSTEMS os,NETWORK_NODES nn,BOM_CONTENTS bc,PACKAGE_VERSIONS pvWHERE osc.OS_ID = os.OS_IDAND os.NODE_ID = nn.NODE_IDAND bc.NODE_ID = nn.NODE_IDAND osc.PROD_ID = pv.PV_IDAND NOT pv.IS_PATCH IS NULLAND bc.BOM_ID = nBomId) newbom,(SELECT osc.PROD_ID,nn.NODE_NAME ||'_'|| os.OS_NAME AS PKFROM OS_CONTENTS osc,OPERATING_SYSTEMS os,NETWORK_NODES nn,BOM_CONTENTS bc,PACKAGE_VERSIONS pvWHERE osc.OS_ID = os.OS_IDAND os.NODE_ID = nn.NODE_IDAND bc.NODE_ID = nn.NODE_IDAND osc.PROD_ID = pv.PV_IDAND NOT pv.IS_PATCH IS NULLAND bc.BOM_ID = CompareBomId) oldbomWHERE newbom.PK (+) = oldbom.PKAND newbom.PROD_ID (+) = oldbom.PROD_IDAND newbom.PK IS NULLUNION/* REMOVED PRODUCTS ONLY FROM BASE CONFIGURATION*/SELECT 'R' AS CHANGE_TYPE, oldbom.PROD_ID, oldbom.PKFROM (SELECT bec.PROD_ID,nn.NODE_NAME ||'_'|| os.OS_NAME AS PK,pv.PKG_ID,NVL( pv.V_EXT, '|LINK_A_NULL|' ) AS V_EXTFROM BASE_ENV_CONTENTS bec,OS_BASE_ENV obe,OPERATING_SYSTEMS os,NETWORK_NODES nn,BOM_CONTENTS bc,PACKAGE_VERSIONS pvWHERE obe.OS_ID = os.OS_IDAND bec.BASE_ENV_ID = obe.BASE_ENV_IDAND os.NODE_ID = nn.NODE_IDAND bc.NODE_ID = nn.NODE_IDAND bec.PROD_ID = pv.PV_IDAND pv.IS_PATCH IS NULLAND bc.BOM_ID = nBomId) newbom,(SELECT bec.PROD_ID,nn.NODE_NAME ||'_'|| os.OS_NAME AS PK,pv.PKG_ID,NVL( pv.V_EXT, '|LINK_A_NULL|' ) AS V_EXTFROM BASE_ENV_CONTENTS bec,OS_BASE_ENV obe,OPERATING_SYSTEMS os,NETWORK_NODES nn,BOM_CONTENTS bc,PACKAGE_VERSIONS pvWHERE obe.OS_ID = os.OS_IDAND bec.BASE_ENV_ID = obe.BASE_ENV_IDAND os.NODE_ID = nn.NODE_IDAND bc.NODE_ID = nn.NODE_IDAND bec.PROD_ID = pv.PV_IDAND pv.IS_PATCH IS NULLAND bc.BOM_ID = CompareBomId) oldbomWHERE newbom.PK (+) = oldbom.PK--AND newbom.PROD_ID (+) = oldbom.PROD_IDAND newbom.PKG_ID (+) = oldbom.PKG_IDAND newbom.V_EXT (+) = oldbom.V_EXTAND newbom.PK IS NULLUNION/* REMOVED PATCHES FROM BASE CONFIGURATION*/SELECT 'R' AS CHANGE_TYPE, oldbom.PROD_ID, oldbom.PKFROM (SELECT bec.PROD_ID,nn.NODE_NAME ||'_'|| os.OS_NAME AS PKFROM BASE_ENV_CONTENTS bec,OS_BASE_ENV obe,OPERATING_SYSTEMS os,NETWORK_NODES nn,BOM_CONTENTS bc,PACKAGE_VERSIONS pvWHERE obe.OS_ID = os.OS_IDAND bec.BASE_ENV_ID = obe.BASE_ENV_IDAND os.NODE_ID = nn.NODE_IDAND bc.NODE_ID = nn.NODE_IDAND bec.PROD_ID = pv.PV_IDAND NOT pv.IS_PATCH IS NULLAND bc.BOM_ID = nBomId) newbom,(SELECT bec.PROD_ID,nn.NODE_NAME ||'_'|| os.OS_NAME AS PKFROM BASE_ENV_CONTENTS bec,OS_BASE_ENV obe,OPERATING_SYSTEMS os,NETWORK_NODES nn,BOM_CONTENTS bc,PACKAGE_VERSIONS pvWHERE obe.OS_ID = os.OS_IDAND bec.BASE_ENV_ID = obe.BASE_ENV_IDAND os.NODE_ID = nn.NODE_IDAND bc.NODE_ID = nn.NODE_IDAND bec.PROD_ID = pv.PV_IDAND NOT pv.IS_PATCH IS NULLAND bc.BOM_ID = CompareBomId) oldbomWHERE newbom.PK (+) = oldbom.PKAND newbom.PROD_ID (+) = oldbom.PROD_IDAND newbom.PK IS NULL) qry,(SELECT nn.NODE_ID,nn.NODE_NAME,nn.NODE_NAME ||'_'|| os.OS_NAME AS PKFROM OPERATING_SYSTEMS os,NETWORK_NODES nn,BOM_CONTENTS bcWHERE os.NODE_ID = nn.NODE_IDAND bc.NODE_ID = nn.NODE_IDAND bc.BOM_ID = nBomId) nlook,(SELECT nn.NODE_NAME,MAX(nn.NODE_LIFE_CYCLE) AS LAST_LIFE_CYCLEFROM BOMS bo,BOM_CONTENTS bc,BRANCHES br,NETWORK_NODES nnWHERE bc.BOM_ID = bo.BOM_IDAND bo.BRANCH_ID = br.BRANCH_IDAND bc.NODE_ID = nn.NODE_IDAND br.PROJ_ID = ProjIdGROUP BY nn.NODE_NAME) nlcWHERE nlook.PK = qry.PKAND nlc.NODE_NAME = nlook.NODE_NAME;recUpdateNode curUpdateNode%ROWTYPE;BEGIN/*--------------- Business Rules Here -------------------*//*-------------------------------------------------------*/-- Get some BOM detailsSELECT bo.PARENT_BOM_ID, br.PROJ_ID, bo.BOM_VERSION ||'.'|| bo.BOM_LIFECYCLE INTO CompareBomId, ProjId, BomVersionFROM BOMS bo,BRANCHES brWHERE bo.BOM_ID = nBomIdAND bo.BRANCH_ID = br.BRANCH_ID;-- Update node versionOPEN curUpdateNode;FETCH curUpdateNode INTO recUpdateNode;WHILE curUpdateNode%FOUNDLOOPUPDATE NETWORK_NODES nn SETnn.NODE_VERSION = BomVersion,nn.NODE_LIFE_CYCLE = recUpdateNode.NEW_LIFE_CYCLEWHERE nn.NODE_ID = recUpdateNode.NODE_ID;FETCH curUpdateNode INTO recUpdateNode;END LOOP;CLOSE curUpdateNode;END Update_Nodes_Version;/*--------------------------------------------------------------------------------------------------*/PROCEDURE Destroy_Bom ( nBomId IN VARCHAR2 ) ISrowCount NUMBER;nIdCollector DEPMGR_NUMBER_TAB_t := DEPMGR_NUMBER_TAB_t();nBomIdCollector DEPMGR_NUMBER_TAB_t := DEPMGR_NUMBER_TAB_t();BEGIN/*--------------- Business Rules Here -------------------*/IF (nBomId IS NULL)THENRAISE_APPLICATION_ERROR (-20000, 'Missing some parameters! nBomId= '|| nBomId );END IF;-- Check Requirementspk_Requirements.Destroy_Bom ( nBomId );/*-------------------------------------------------------*/-- Load CollectornBomIdCollector := IN_LIST_NUMBER( nBomId );-- Destroy BASE_ENVnIdCollector := NULL;SELECT CAST ( MULTISET ( SELECT be.BASE_ENV_IDFROM BASE_ENV beWHERE be.BOM_ID IN (SELECT *FROM THE ( SELECT CAST( nBomIdCollector AS DEPMGR_NUMBER_TAB_t ) FROM dual ))) AS DEPMGR_NUMBER_TAB_t ) INTO nIdCollector FROM DUAL;Destroy_BaseEnv ( nIdCollector );-- Destroy NETWORK_NODESnIdCollector := NULL;SELECT CAST ( MULTISET ( SELECT bc.NODE_IDFROM BOM_CONTENTS bcWHERE bc.BOM_ID IN (SELECT *FROM THE ( SELECT CAST( nBomIdCollector AS DEPMGR_NUMBER_TAB_t ) FROM dual ))) AS DEPMGR_NUMBER_TAB_t ) INTO nIdCollector FROM DUAL;pk_Network_Node.Destroy_Node ( nIdCollector, 0 );-- Destroy HARDWAREnIdCollector := NULL;SELECT CAST ( MULTISET ( SELECT hr.HARDWARE_NAME_IDFROM HARDWARE hrWHERE hr.BOM_ID IN (SELECT *FROM THE ( SELECT CAST( nBomIdCollector AS DEPMGR_NUMBER_TAB_t ) FROM dual ))) AS DEPMGR_NUMBER_TAB_t ) INTO nIdCollector FROM DUAL;Destroy_Hardware ( nIdCollector );-- Destroy BOM_STATEDELETEFROM BOM_STATE bsWHERE bs.BOM_ID IN (SELECT *FROM THE ( SELECT CAST( nBomIdCollector AS DEPMGR_NUMBER_TAB_t ) FROM dual ));-- Destroy BOM_TRAILDELETEFROM BOM_TRAIL btWHERE bt.BOM_ID IN (SELECT *FROM THE ( SELECT CAST( nBomIdCollector AS DEPMGR_NUMBER_TAB_t ) FROM dual ));---- Finnaly Destroy BOM ----DELETEFROM BOMS boWHERE bo.BOM_ID IN (SELECT *FROM THE ( SELECT CAST( nBomIdCollector AS DEPMGR_NUMBER_TAB_t ) FROM dual ));END Destroy_Bom;/*--------------------------------------------------------------------------------------------------*/PROCEDURE Destroy_BaseEnv ( nBaseEndIdCollector IN DEPMGR_NUMBER_TAB_t ) ISBEGIN/*--------------- Business Rules Here -------------------*//*-------------------------------------------------------*/-- Destroy BASE_ENV_CONTENTSDELETEFROM BASE_ENV_CONTENTS becWHERE bec.BASE_ENV_ID IN ( SELECT * FROM TABLE ( CAST ( nBaseEndIdCollector AS DEPMGR_NUMBER_TAB_t ) ) );-- Destroy OS_BASE_ENVDELETEFROM OS_BASE_ENV obeWHERE obe.BASE_ENV_ID IN ( SELECT * FROM TABLE ( CAST ( nBaseEndIdCollector AS DEPMGR_NUMBER_TAB_t ) ) );-- Destroy BASE_ENVDELETEFROM BASE_ENV beWHERE be.BASE_ENV_ID IN ( SELECT * FROM TABLE ( CAST ( nBaseEndIdCollector AS DEPMGR_NUMBER_TAB_t ) ) );END Destroy_BaseEnv;/*--------------------------------------------------------------------------------------------------*/PROCEDURE Destroy_Hardware ( nHardwareNameIdCollector IN DEPMGR_NUMBER_TAB_t ) ISnIdCollector DEPMGR_NUMBER_TAB_t := DEPMGR_NUMBER_TAB_t();BEGIN/*--------------- Business Rules Here -------------------*//*-------------------------------------------------------*/-- Destroy NETWORK_NODESnIdCollector := NULL;SELECT CAST ( MULTISET ( SELECT hn.NODE_IDFROM HARDWARE_NODE hnWHERE hn.HARDWARE_NAME_ID IN ( SELECT * FROM TABLE ( CAST ( nHardwareNameIdCollector AS DEPMGR_NUMBER_TAB_t ) ) )) AS DEPMGR_NUMBER_TAB_t ) INTO nIdCollector FROM DUAL;pk_Network_Node.Destroy_Node ( nIdCollector, 0 );-- Finnaly Destroy HARDWAREDELETEFROM HARDWARE hdWHERE hd.HARDWARE_NAME_ID IN ( SELECT * FROM TABLE ( CAST ( nHardwareNameIdCollector AS DEPMGR_NUMBER_TAB_t ) ) );END Destroy_Hardware;/*--------------------------------------------------------------------------------------------------*/PROCEDURE Get_Next_Version (nFromBomId IN BOMS.BOM_ID%TYPE,nToBranchId IN BOMS.BRANCH_ID%TYPE,sNextVersion OUT BOMS.BOM_VERSION%TYPE,sNextLifeCycle OUT BOMS.BOM_LIFECYCLE%TYPE,nParentBomId IN OUT BOMS.PARENT_BOM_ID%TYPE,nBomNameId IN BOMS.BOM_NAME_ID%TYPE ) ISIsNotHead NUMBER;FromBranch NUMBER;LastRootVersion NUMBER;LastVersion BOMS.BOM_VERSION%TYPE;CURSOR curBoms ISSELECT Max ( CAST ( bo.BOM_VERSION AS NUMBER ) ) AS LAST_VERSIONFROM BOMS bo,BRANCHES brWHERE bo.BRANCH_ID = br.BRANCH_IDAND br.PROJ_ID IN ( SELECT br.PROJ_ID FROM BRANCHES br WHERE br.BRANCH_ID = nToBranchId )AND bo.BOM_NAME_ID = nBomNameId;recBoms curBoms%ROWTYPE;BEGIN/*--------------- Business Rules Here -------------------*//*-------------------------------------------------------*/-- Get Last Root VersionOPEN curBoms;FETCH curBoms INTO recBoms;LastRootVersion := recBoms.LAST_VERSION;CLOSE curBoms;IF nFromBomId IS NULL THEN/* Blank BOM Required */-- Get Next Available VersionsNextVersion := '1';sNextLifeCycle := '0';IF LastRootVersion IS NOT NULL THENsNextVersion := CAST( LastRootVersion + 1 AS VARCHAR2 );END IF;ELSE/* Create Bom From ... */-- Check if BOM is comming from different IterationSELECT bo.BRANCH_ID INTO FromBranchFROM BOMS boWHERE bo.BOM_ID = nFromBomId;IF FromBranch = nToBranchId THEN/* Bom is comming from same Iteration *//* i.e. Continue on same version stream. */-- Set parent_idnParentBomId := nFromBomId;-- Get Last VersionSELECT bo.BOM_VERSION INTO LastVersionFROM BOMS boWHERE bo.BOM_ID = nFromBomId;-- Check if Bom is Head (i.e. Last on its branch )SELECT COUNT(*) INTO IsNotHeadFROM BOMS bo,BRANCHES brWHERE bo.BRANCH_ID = br.BRANCH_IDAND br.PROJ_ID IN ( SELECT br.PROJ_ID FROM BRANCHES br WHERE br.BRANCH_ID = FromBranch )AND bo.PARENT_BOM_ID = nFromBomIdAND bo.BOM_VERSION = LastVersionAND bo.BOM_ID != bo.PARENT_BOM_ID;IF IsNotHead = 0 THEN/* Bom IS Head (i.e. Last on its branch ) */-- Get Bom Version and LifeCycleSELECT bo.BOM_VERSION, bo.BOM_LIFECYCLE INTO sNextVersion, sNextLifeCycleFROM BOMS boWHERE bo.BOM_ID = nFromBomId;-- Increase LifecyclesNextLifeCycle := CAST ( ( CAST( sNextLifeCycle AS NUMBER ) + 1 ) AS VARCHAR2 );ELSE/* Bom IS NOT Head */-- Get Bom Version and LifeCycleSELECT bo.BOM_VERSION, bo.BOM_LIFECYCLE INTO sNextVersion, sNextLifeCycleFROM BOMS boWHERE bo.BOM_ID = nFromBomId;-- Branch Bom VersionsNextVersion := sNextVersion ||'.'|| sNextLifeCycle;-- Reset Lifecycle to 1sNextLifeCycle := '1';END IF;ELSE/* BOM is comming from Different Iteration *//* i.e. Start new version stream. */-- Get Next Available VersionsNextVersion := '1';sNextLifeCycle := '0';IF LastRootVersion IS NOT NULL THENsNextVersion := CAST( LastRootVersion + 1 AS VARCHAR2 );END IF;END IF;END IF;END Get_Next_Version;/*--------------------------------------------------------------------------------------------------*/PROCEDURE Release_To ( nBomId IN BOM_STATE.BOM_ID%TYPE,nUserId IN BOM_STATE.USER_ID%TYPE,nToState IN BOM_STATE.STATE_ID%TYPE DEFAULT NULL,sComments IN BOM_TRAIL.TRAIL_COMMENTS%TYPE DEFAULT NULL ) ISrowCount NUMBER;IsOfficial BOMS.IS_READONLY%TYPE;StateId BOM_STATE.STATE_ID%TYPE;StateName STATES.STATE_NAME%TYPE;BEGIN/*--------------- Business Rules Here -------------------*/-- Check if already releasedSELECT Count(*) INTO rowCountFROM BOM_STATE bsWHERE bs.BOM_ID = nBomIdAND bs.STATE_ID = nToState;IF rowCount > 0 THENRAISE_APPLICATION_ERROR (-20000, 'This BOM is already release' );END IF;-- Check if Bom is officialSELECT bo.IS_READONLY INTO IsOfficialFROM BOMS boWHERE bo.BOM_ID = nBomId;IF (IsOfficial != 'Y') AND (nToState IS NOT NULL) THENRAISE_APPLICATION_ERROR (-20000, 'BOM must be locked first' );END IF;/*-------------------------------------------------------*/-- Get State --StateId := nToState;IF StateId IS NULL THENBEGIN-- If state_id is null it is considered as state_type_enum = 'BOM Creator'SELECT st.STATE_ID INTO StateIdFROM BOMS bo,BRANCHES br,DM_PROJECTS proj,STATES stWHERE bo.BRANCH_ID = br.BRANCH_IDAND br.PROJ_ID = proj.PROJ_IDAND st.PROJ_ID = proj.PROJ_IDAND bo.BOM_ID = nBomIdAND st.STATE_TYPE_ENUM = GET_SYSTEM_VALUE('enumSTATE_TYPE_CREATOR')AND rownum = 1ORDER BY st.STATE_ID;EXCEPTION WHEN NO_DATA_FOUND THENRAISE_APPLICATION_ERROR (-20000, 'Cannot find BOM Creator in project. Project is misconfigured! nBomId= '|| nBomId );END;END IF;-- Add Bom to State --INSERT INTO BOM_STATE (BOM_ID, STATE_ID, DATE_STAMP, IS_DISPLAYED, USER_ID )VALUES( nBomId,StateId,GET_DATE(),'Y',nUserId );-- Auto-Hide Bompk_State.Auto_Hide ( StateId, nBomId, GET_SYSTEM_VALUE('enumEVENT_ON_RELEASE_TO') );/* Log BOM_TRAIL */-- Get State Name --SELECT st.STATE_NAME INTO StateNameFROM STATES stWHERE st.STATE_ID = StateId;-- Log Actionpk_Bom.Log_Bom_Trail ( nBomId, 'RELEASED TO: '|| StateName, nUserId, sComments );END Release_To;/*--------------------------------------------------------------------------------------------------*/PROCEDURE Accept_Reject_Comments ( nBomId IN BOMS.BOM_ID%TYPE,nRejectSeq IN REJECTION_TRAIL.REJECT_SEQ%TYPE,sComments IN REJECTION_TRAIL.COMMENTS%TYPE DEFAULT NULL,nUserId IN REJECTION_TRAIL.USER_ID%TYPE ) ISIsRejected REJECTION_TRAIL.IS_REJECTED%TYPE;BEGIN/*--------------- Business Rules Here -------------------*/IF (nBomId IS NULL)THENRAISE_APPLICATION_ERROR (-20000, 'Missing some parameters! nBomId= '|| nBomId );END IF;/*-------------------------------------------------------*/IF nRejectSeq IS NULL THEN/* CREATE New Note */-- Get last IsRejected stateSELECT rt.IS_REJECTED INTO IsRejectedFROM REJECTION_TRAIL rtWHERE rt.ENTITY_ID = nBomIdAND rt.ENUM_ENTITY_TYPE = GET_SYSTEM_VALUE('enumENTITY_TYPE_BOM')AND rt.REJECT_SEQ =(SELECT MAX(REJECT_SEQ) FROMREJECTION_TRAIL rtWHERE rt.ENTITY_ID = nBomIdAND rt.ENUM_ENTITY_TYPE = GET_SYSTEM_VALUE('enumENTITY_TYPE_BOM'));/* Log Rejection Trail */pk_Utils.Log_Rejection_Trail ( GET_SYSTEM_VALUE('enumENTITY_TYPE_BOM'), nBomId, IsRejected, nUserId, sComments );ELSE/* EDIT Note */UPDATE REJECTION_TRAIL rt SETrt.COMMENTS = sComments,rt.USER_ID = nUserId,rt.DATE_TIME_STAMP = GET_DATETIME()WHERE rt.ENUM_ENTITY_TYPE = GET_SYSTEM_VALUE('enumENTITY_TYPE_BOM')AND rt.ENTITY_ID = nBomIdAND rt.REJECT_SEQ = nRejectSeq;END IF;END Accept_Reject_Comments;/*--------------------------------------------------------------------------------------------------*/PROCEDURE Log_Bom_Trail ( nBomId IN BOM_TRAIL.BOM_ID%TYPE,sAction IN BOM_TRAIL.TRAIL_ACTION%TYPE,nUserId IN BOM_TRAIL.USER_ID%TYPE,sComments IN BOM_TRAIL.TRAIL_COMMENTS%TYPE DEFAULT NULL ) ISBEGIN/*--------------- Business Rules Here -------------------*/IF (nBomId IS NULL) OR (sAction IS NULL) OR (nUserId IS NULL)THENRAISE_APPLICATION_ERROR (-20000, 'Missing some parameters! nBomId= '|| nBomId ||', sAction='|| sAction ||', nUserId='|| nUserId );END IF;/*-------------------------------------------------------*/INSERT INTO BOM_TRAIL ( BOM_ID, TRAIL_ACTION, TRAIL_COMMENTS, USER_ID, DATE_TIME_STAMP )VALUES ( nBomId, sAction, sComments, nUserId, GET_DATETIME() );END Log_Bom_Trail;/*--------------------------------------------------------------------------------------------------*/PROCEDURE Log_Node_Trail ( nNodeId IN NODE_TRAIL.NODE_ID%TYPE,sAction IN NODE_TRAIL.TRAIL_ACTION%TYPE,nUserId IN NODE_TRAIL.USER_ID%TYPE ) ISBEGIN/*--------------- Business Rules Here -------------------*/IF (nNodeId IS NULL) OR (sAction IS NULL) OR (nUserId IS NULL)THENRAISE_APPLICATION_ERROR (-20000, 'Missing some parameters! nNodeId= '|| nNodeId ||', sAction='|| sAction ||', nUserId='|| nUserId );END IF;/*-------------------------------------------------------*/INSERT INTO NODE_TRAIL ( NODE_ID, TRAIL_ACTION, USER_ID, DATE_TIME_STAMP )VALUES ( nNodeId, sAction, nUserId, GET_DATETIME() );END Log_Node_Trail;/*--------------------------------------------------------------------------------------------------*/PROCEDURE Log_Os_Trail ( nOsId IN OS_TRAIL.OS_ID%TYPE,sAction IN OS_TRAIL.TRAIL_ACTION%TYPE,nUserId IN OS_TRAIL.USER_ID%TYPE ) ISBEGIN/*--------------- Business Rules Here -------------------*/IF (nOsId IS NULL) OR (sAction IS NULL) OR (nUserId IS NULL)THENRAISE_APPLICATION_ERROR (-20000, 'Missing some parameters! nOsId= '|| nOsId ||', sAction='|| sAction ||', nUserId='|| nUserId );END IF;/*-------------------------------------------------------*/INSERT INTO OS_TRAIL ( OS_ID, TRAIL_ACTION, USER_ID, DATE_TIME_STAMP )VALUES ( nOsId, sAction, nUserId, GET_DATETIME() );END Log_Os_Trail;/*--------------------------------------------------------------------------------------------------*/END pk_Bom;//---------------------------------------------------------- DDL for Package Body PK_BOM_TEST--------------------------------------------------------CREATE OR REPLACE PACKAGE BODY "PK_BOM_TEST"IS/* ---------------------------------------------------------------------------Version: 2.0--------------------------------------------------------------------------- *//*--------------------------------------------------------------------------------------------------*/PROCEDURE Add_Bom ( sBomName IN BOM_NAMES.BOM_NAME%TYPE DEFAULT NULL,sBomVersion IN BOMS.BOM_VERSION%TYPE,sBomLifeCycle IN BOMS.BOM_LIFECYCLE%TYPE,sBomComments IN BOMS.BOM_COMMENTS%TYPE DEFAULT NULL,nToBranchId IN BOMS.BRANCH_ID%TYPE,nFromBomId IN BOMS.BOM_ID%TYPE DEFAULT NULL,nUserId IN BOM_STATE.USER_ID%TYPE ) ISBomId BOMS.BOM_ID%TYPE;ParentBomId BOMS.PARENT_BOM_ID%TYPE;BomNameId BOM_NAMES.BOM_NAME_ID%TYPE;BomVersion BOMS.BOM_VERSION%TYPE;BomLifeCycle BOMS.BOM_LIFECYCLE%TYPE;ProjId BRANCHES.PROJ_ID%TYPE;CURSOR curBom ISSELECT bo.BOM_IDFROM BOMS bo,BRANCHES brWHERE bo.BRANCH_ID = br.BRANCH_IDAND br.PROJ_ID = ProjIdAND bo.BOM_NAME_ID = BomNameIdAND bo.BOM_VERSION = BomVersionAND bo.BOM_LIFECYCLE = BomLifeCycle;recBom curBom%ROWTYPE;DUPLICATE_BOM EXCEPTION;BEGIN/*--------------- Business Rules Here -------------------*/IF (nToBranchId IS NULL) OR (nUserId IS NULL)THENRAISE_APPLICATION_ERROR (-20000, 'Missing some parameters! nToBranchId= '|| nToBranchId ||', nUserId='|| nUserId );END IF;/*-------------------------------------------------------*/-- Get bom_idSELECT SEQ_BOM_ID.NEXTVAL INTO BomId FROM DUAL;-- Get bom_name_idIF nFromBomId IS NULL THENBomNameId := New_Bom_Name ( sBomName );ParentBomId := BomId;ELSEBomNameId := New_Bom_Name ( nFromBomId );ParentBomId := nFromBomId;END IF;-- Get Bom Version and Lifecycle--Get_Next_Version ( nFromBomId, nToBranchId, BomVersion, BomLifeCycle, ParentBomId, BomNameId );-- Get proj_idSELECT br.PROJ_ID INTO ProjIdFROM BRANCHES brWHERE br.BRANCH_ID = nToBranchId;-- Check for duplicate BOMsOPEN curBom;FETCH curBom INTO recBom;IF curBom%FOUNDTHENRAISE DUPLICATE_BOM;END IF;CLOSE curBom;-- Add Blank Bom --INSERT INTO BOMS ( BOM_ID, BRANCH_ID, BOM_NAME_ID, BOM_COMMENTS, BOM_VERSION, BOM_LIFECYCLE, IS_READONLY, IS_HIDDEN, PARENT_BOM_ID )VALUES ( BomId, nToBranchId, BomNameId, sBomComments, sBomVersion, sBomLifeCycle, 'N', 'N', ParentBomId );/* Log BOM_TRAIL */pk_Bom.Log_Bom_Trail ( BomId, 'CREATED: Version '|| sBomVersion ||'.'|| sBomLifeCycle , nUserId );IF nFromBomId IS NOT NULL THEN/* Add Bom Clonned from ... */pk_Utils.Clone_BOM ( nFromBomId, BomId, nUserId );END IF;--- Set Bom State ---Release_To ( BomId, nUserId );EXCEPTIONWHEN DUPLICATE_BOMTHENRAISE_APPLICATION_ERROR (-20000, 'BOM "'|| sBomName ||' '|| BomVersion ||'.'|| BomLifeCycle ||'" is Already Used in this Project.');END Add_Bom;/*--------------------------------------------------------------------------------------------------*/PROCEDURE Update_Bom ( nBomId IN BOMS.BOM_ID%TYPE,nRtagId IN BOMS.RTAG_ID_FK%TYPE,sBomComment IN BOMS.BOM_COMMENTS%TYPE,nUserId IN NUMBER ) ISReleaseName VARCHAR2(100);BEGIN/*--------------- Business Rules Here -------------------*//*-------------------------------------------------------*/--- Update Bom Properties ---UPDATE BOMS bo SETbo.RTAG_ID_FK = nRtagId,bo.BOM_COMMENTS = sBomCommentWHERE bo.BOM_ID = nBomId;SELECT RTAG_NAME INTO ReleaseNameFROM RELEASE_TAGSWHERE RTAG_ID = nRtagId;/* Log BOM_TRAIL */pk_Bom.Log_Bom_Trail ( nBomId, 'Updated BOM properties: Set Release Manager Reference ='||ReleaseName||' and comments ='||sBomComment, nUserId );END Update_Bom;/*--------------------------------------------------------------------------------------------------*/FUNCTION New_Bom_Name ( sBomName IN BOM_NAMES.BOM_NAME%TYPE ) RETURN BOM_NAMES.BOM_NAME_ID%TYPE ISReturnValue BOM_NAMES.BOM_NAME_ID%TYPE;CURSOR curBomNames ISSELECT bn.BOM_NAME_IDFROM BOM_NAMES bnWHERE bn.BOM_NAME = sBomName;recBomNames curBomNames%ROWTYPE;BEGIN/*--------------- Business Rules Here -------------------*//*-------------------------------------------------------*/-- Check for existance of this nameOPEN curBomNames;FETCH curBomNames INTO recBomNames;ReturnValue := NULL;IF curBomNames%FOUND THENReturnValue := recBomNames.BOM_NAME_ID;END IF;CLOSE curBomNames;-- Finnaly Create Name if required --IF ReturnValue IS NULL THEN-- Get bom_name_idSELECT SEQ_BOM_NAME_ID.NEXTVAL INTO ReturnValue FROM DUAL;INSERT INTO BOM_NAMES ( BOM_NAME_ID, BOM_NAME )VALUES ( ReturnValue, sBomName );END IF;RETURN ReturnValue;END New_Bom_Name;/*--------------------------------------------------------------------------------------------------*/FUNCTION New_Bom_Name ( nFromBomId IN BOMS.BOM_NAME_ID%TYPE ) RETURN BOMS.BOM_NAME_ID%TYPE ISReturnValue BOMS.BOM_NAME_ID%TYPE;BEGIN/*--------------- Business Rules Here -------------------*//*-------------------------------------------------------*/-- Get bom_name_idSELECT bo.BOM_NAME_ID INTO ReturnValueFROM BOMS boWHERE bo.BOM_ID = nFromBomId;RETURN ReturnValue;END New_Bom_Name;/*--------------------------------------------------------------------------------------------------*/PROCEDURE Accept_Reject ( nBomId IN BOMS.BOM_ID%TYPE,cIsRejected IN BOMS.IS_REJECTED%TYPE,sComments IN REJECTION_TRAIL.COMMENTS%TYPE,nStateId IN BOM_STATE.STATE_ID%TYPE DEFAULT NULL,nUserId IN REJECTION_TRAIL.USER_ID%TYPE ) ISStateName STATES.STATE_NAME%TYPE;IsOfficial BOMS.IS_READONLY%TYPE;IsRejected VARCHAR2(50);BEGIN/*--------------- Business Rules Here -------------------*/-- Check if Bom is officialSELECT bo.IS_READONLY INTO IsOfficialFROM BOMS boWHERE bo.BOM_ID = nBomId;IF IsOfficial != 'Y' THENRAISE_APPLICATION_ERROR (-20000, 'BOM must be locked first.' );END IF;/*-------------------------------------------------------*/-- Get State NameSELECT st.STATE_NAME INTO StateNameFROM STATES stWHERE st.STATE_ID = nStateId;-- AcceptReject BOMUPDATE BOMS bo SETbo.IS_REJECTED = cIsRejectedWHERE bo.BOM_ID = nBomId;-- Auto-Hide BomIF cIsRejected = 'Y' THENpk_State.Auto_Hide ( nStateId, nBomId, GET_SYSTEM_VALUE('enumEVENT_ON_REJECT_BOM') );ELSEpk_State.Auto_Hide ( nStateId, nBomId, GET_SYSTEM_VALUE('enumEVENT_ON_ACCEPT_BOM') );END IF;-- Log Rejection Trailpk_Utils.Log_Rejection_Trail ( GET_SYSTEM_VALUE('enumENTITY_TYPE_BOM'), nBomId, cIsRejected, nUserId, sComments );/* Log BOM_TRAIL */-- Get Log TitleIsRejected := 'ACCEPTED';IF cIsRejected = 'Y' THENIsRejected := 'REJECTED';END IF;-- Finnaly log itLog_Bom_Trail ( nBomId, IsRejected ||': By '|| StateName, nUserId, sComments );END Accept_Reject;/*--------------------------------------------------------------------------------------------------*/PROCEDURE Set_Bom_Official ( nBomId IN BOMS.BOM_ID%TYPE,cIsOfficial IN BOMS.IS_READONLY%TYPE,nStateId IN BOM_STATE.STATE_ID%TYPE,nUserId IN NUMBER ) ISBEGIN/*--------------- Business Rules Here -------------------*//*-------------------------------------------------------*/--- Set Bom Official or Not ---UPDATE BOMS bo SETbo.IS_READONLY = cIsOfficialWHERE bo.BOM_ID = nBomId;-- Auto-Hide BomIF cIsOfficial = 'Y' THENpk_State.Auto_Hide ( nStateId, nBomId, GET_SYSTEM_VALUE('enumEVENT_ON_LOCK_BOM') );-- Update node versionUpdate_Nodes_Version ( nBomId, nUserId );ELSEpk_State.Auto_Hide ( nStateId, nBomId, GET_SYSTEM_VALUE('enumEVENT_ON_UNLOCK_BOM') );END IF;/* Log BOM_TRAIL */pk_Bom.Log_Bom_Trail ( nBomId, 'LOCKED: '|| cIsOfficial, nUserId );END Set_Bom_Official;/*--------------------------------------------------------------------------------------------------*/PROCEDURE Update_Nodes_Version ( nBomId IN BOMS.BOM_ID%TYPE,nUserId IN NUMBER ) ISCompareBomId NUMBER;ProjId NUMBER;BomVersion VARCHAR2(4000);CURSOR curUpdateNode ISSELECT DISTINCTnlook.NODE_ID,DECODE ( nlc.LAST_LIFE_CYCLE,NULL, 0,nlc.LAST_LIFE_CYCLE + 1 ) AS NEW_LIFE_CYCLEFROM (/* ADDED PATCHES ONLY */SELECT 'A' AS CHANGE_TYPE, newbom.PROD_ID, newbom.PKFROM (SELECT osc.PROD_ID,nn.NODE_NAME ||'_'|| os.OS_NAME AS PKFROM OS_CONTENTS osc,OPERATING_SYSTEMS os,NETWORK_NODES nn,BOM_CONTENTS bc,PACKAGE_VERSIONS pvWHERE osc.OS_ID = os.OS_IDAND os.NODE_ID = nn.NODE_IDAND bc.NODE_ID = nn.NODE_IDAND osc.PROD_ID = pv.PV_IDAND NOT pv.IS_PATCH IS NULLAND bc.BOM_ID = nBomId) newbom,(SELECT osc.PROD_ID,nn.NODE_NAME ||'_'|| os.OS_NAME AS PKFROM OS_CONTENTS osc,OPERATING_SYSTEMS os,NETWORK_NODES nn,BOM_CONTENTS bc,PACKAGE_VERSIONS pvWHERE osc.OS_ID = os.OS_IDAND os.NODE_ID = nn.NODE_IDAND bc.NODE_ID = nn.NODE_IDAND osc.PROD_ID = pv.PV_IDAND NOT pv.IS_PATCH IS NULLAND bc.BOM_ID = CompareBomId) oldbomWHERE newbom.PK = oldbom.PK (+)AND newbom.PROD_ID = oldbom.PROD_ID (+)AND oldbom.PK IS NULLUNION/* ADDED PATCHES ONLY TO BASE CONFIGURATION */SELECT 'A' AS CHANGE_TYPE, newbom.PROD_ID, newbom.PKFROM (SELECT bec.PROD_ID,nn.NODE_NAME ||'_'|| os.OS_NAME AS PKFROM BASE_ENV_CONTENTS bec,OS_BASE_ENV obe,OPERATING_SYSTEMS os,NETWORK_NODES nn,BOM_CONTENTS bc,PACKAGE_VERSIONS pvWHERE obe.OS_ID = os.OS_IDAND bec.BASE_ENV_ID = obe.BASE_ENV_IDAND os.NODE_ID = nn.NODE_IDAND bc.NODE_ID = nn.NODE_IDAND bec.PROD_ID = pv.PV_IDAND NOT pv.IS_PATCH IS NULLAND bc.BOM_ID = nBomId) newbom,(SELECT bec.PROD_ID,nn.NODE_NAME ||'_'|| os.OS_NAME AS PKFROM BASE_ENV_CONTENTS bec,OS_BASE_ENV obe,OPERATING_SYSTEMS os,NETWORK_NODES nn,BOM_CONTENTS bc,PACKAGE_VERSIONS pvWHERE obe.OS_ID = os.OS_IDAND bec.BASE_ENV_ID = obe.BASE_ENV_IDAND os.NODE_ID = nn.NODE_IDAND bc.NODE_ID = nn.NODE_IDAND bec.PROD_ID = pv.PV_IDAND NOT pv.IS_PATCH IS NULLAND bc.BOM_ID = CompareBomId) oldbomWHERE newbom.PK = oldbom.PK (+)AND newbom.PROD_ID = oldbom.PROD_ID (+)AND oldbom.PK IS NULLUNION/* ADDED PRODUCTS ONLY */SELECT DECODE( oldbom.PROD_ID,NULL, 'A', 'U' ) AS CHANGE_TYPE,newbom.PROD_ID, newbom.PKFROM (SELECT osc.PROD_ID,nn.NODE_NAME ||'_'|| os.OS_NAME AS PK,pv.PKG_ID,NVL( pv.V_EXT, '|LINK_A_NULL|' ) AS V_EXTFROM OS_CONTENTS osc,OPERATING_SYSTEMS os,NETWORK_NODES nn,BOM_CONTENTS bc,PACKAGE_VERSIONS pvWHERE osc.OS_ID = os.OS_IDAND os.NODE_ID = nn.NODE_IDAND bc.NODE_ID = nn.NODE_IDAND osc.PROD_ID = pv.PV_IDAND pv.IS_PATCH IS NULLAND bc.BOM_ID = nBomId) newbom,(SELECT osc.PROD_ID,nn.NODE_NAME ||'_'|| os.OS_NAME AS PK,pv.PKG_ID,NVL( pv.V_EXT, '|LINK_A_NULL|' ) AS V_EXTFROM OS_CONTENTS osc,OPERATING_SYSTEMS os,NETWORK_NODES nn,BOM_CONTENTS bc,PACKAGE_VERSIONS pvWHERE osc.OS_ID = os.OS_IDAND os.NODE_ID = nn.NODE_IDAND bc.NODE_ID = nn.NODE_IDAND osc.PROD_ID = pv.PV_IDAND pv.IS_PATCH IS NULLAND bc.BOM_ID = CompareBomId) oldbomWHERE newbom.PK = oldbom.PK (+)--AND newbom.PROD_ID = oldbom.PROD_ID (+)AND newbom.PKG_ID = oldbom.PKG_ID (+)AND newbom.V_EXT = oldbom.V_EXT (+)AND ( oldbom.PROD_ID != newbom.PROD_ID OR oldbom.PROD_ID IS NULL )UNION/* ADDED PRODUCTS ONLY TO BASE CONFIGURATION */SELECT DECODE( oldbom.PROD_ID,NULL, 'A', 'U' ) AS CHANGE_TYPE,newbom.PROD_ID, newbom.PKFROM (SELECT bec.PROD_ID,nn.NODE_NAME ||'_'|| os.OS_NAME AS PK,pv.PKG_ID,NVL( pv.V_EXT, '|LINK_A_NULL|' ) AS V_EXTFROM BASE_ENV_CONTENTS bec,OS_BASE_ENV obe,OPERATING_SYSTEMS os,NETWORK_NODES nn,BOM_CONTENTS bc,PACKAGE_VERSIONS pvWHERE obe.OS_ID = os.OS_IDAND bec.BASE_ENV_ID = obe.BASE_ENV_IDAND os.NODE_ID = nn.NODE_IDAND bc.NODE_ID = nn.NODE_IDAND bec.PROD_ID = pv.PV_IDAND pv.IS_PATCH IS NULLAND bc.BOM_ID = nBomId) newbom,(SELECT bec.PROD_ID,nn.NODE_NAME ||'_'|| os.OS_NAME AS PK,pv.PKG_ID,NVL( pv.V_EXT, '|LINK_A_NULL|' ) AS V_EXTFROM BASE_ENV_CONTENTS bec,OS_BASE_ENV obe,OPERATING_SYSTEMS os,NETWORK_NODES nn,BOM_CONTENTS bc,PACKAGE_VERSIONS pvWHERE obe.OS_ID = os.OS_IDAND bec.BASE_ENV_ID = obe.BASE_ENV_IDAND os.NODE_ID = nn.NODE_IDAND bc.NODE_ID = nn.NODE_IDAND bec.PROD_ID = pv.PV_IDAND pv.IS_PATCH IS NULLAND bc.BOM_ID = CompareBomId) oldbomWHERE newbom.PK = oldbom.PK (+)--AND newbom.PROD_ID = oldbom.PROD_ID (+)AND newbom.PKG_ID = oldbom.PKG_ID (+)AND newbom.V_EXT = oldbom.V_EXT (+)AND ( oldbom.PROD_ID != newbom.PROD_ID OR oldbom.PROD_ID IS NULL )UNION/* REMOVED PRODUCTS ONLY*/SELECT 'R' AS CHANGE_TYPE, oldbom.PROD_ID, oldbom.PKFROM (SELECT osc.PROD_ID,nn.NODE_NAME ||'_'|| os.OS_NAME AS PK,pv.PKG_ID,NVL( pv.V_EXT, '|LINK_A_NULL|' ) AS V_EXTFROM OS_CONTENTS osc,OPERATING_SYSTEMS os,NETWORK_NODES nn,BOM_CONTENTS bc,PACKAGE_VERSIONS pvWHERE osc.OS_ID = os.OS_IDAND os.NODE_ID = nn.NODE_IDAND bc.NODE_ID = nn.NODE_IDAND osc.PROD_ID = pv.PV_IDAND pv.IS_PATCH IS NULLAND bc.BOM_ID = nBomId) newbom,(SELECT osc.PROD_ID,nn.NODE_NAME ||'_'|| os.OS_NAME AS PK,pv.PKG_ID,NVL( pv.V_EXT, '|LINK_A_NULL|' ) AS V_EXTFROM OS_CONTENTS osc,OPERATING_SYSTEMS os,NETWORK_NODES nn,BOM_CONTENTS bc,PACKAGE_VERSIONS pvWHERE osc.OS_ID = os.OS_IDAND os.NODE_ID = nn.NODE_IDAND bc.NODE_ID = nn.NODE_IDAND osc.PROD_ID = pv.PV_IDAND pv.IS_PATCH IS NULLAND bc.BOM_ID = CompareBomId) oldbomWHERE newbom.PK (+) = oldbom.PK--AND newbom.PROD_ID (+) = oldbom.PROD_IDAND newbom.PKG_ID (+) = oldbom.PKG_IDAND newbom.V_EXT (+) = oldbom.V_EXTAND newbom.PK IS NULLUNION/* REMOVED PATCHES */SELECT 'R' AS CHANGE_TYPE, oldbom.PROD_ID, oldbom.PKFROM (SELECT osc.PROD_ID,nn.NODE_NAME ||'_'|| os.OS_NAME AS PKFROM OS_CONTENTS osc,OPERATING_SYSTEMS os,NETWORK_NODES nn,BOM_CONTENTS bc,PACKAGE_VERSIONS pvWHERE osc.OS_ID = os.OS_IDAND os.NODE_ID = nn.NODE_IDAND bc.NODE_ID = nn.NODE_IDAND osc.PROD_ID = pv.PV_IDAND NOT pv.IS_PATCH IS NULLAND bc.BOM_ID = nBomId) newbom,(SELECT osc.PROD_ID,nn.NODE_NAME ||'_'|| os.OS_NAME AS PKFROM OS_CONTENTS osc,OPERATING_SYSTEMS os,NETWORK_NODES nn,BOM_CONTENTS bc,PACKAGE_VERSIONS pvWHERE osc.OS_ID = os.OS_IDAND os.NODE_ID = nn.NODE_IDAND bc.NODE_ID = nn.NODE_IDAND osc.PROD_ID = pv.PV_IDAND NOT pv.IS_PATCH IS NULLAND bc.BOM_ID = CompareBomId) oldbomWHERE newbom.PK (+) = oldbom.PKAND newbom.PROD_ID (+) = oldbom.PROD_IDAND newbom.PK IS NULLUNION/* REMOVED PRODUCTS ONLY FROM BASE CONFIGURATION*/SELECT 'R' AS CHANGE_TYPE, oldbom.PROD_ID, oldbom.PKFROM (SELECT bec.PROD_ID,nn.NODE_NAME ||'_'|| os.OS_NAME AS PK,pv.PKG_ID,NVL( pv.V_EXT, '|LINK_A_NULL|' ) AS V_EXTFROM BASE_ENV_CONTENTS bec,OS_BASE_ENV obe,OPERATING_SYSTEMS os,NETWORK_NODES nn,BOM_CONTENTS bc,PACKAGE_VERSIONS pvWHERE obe.OS_ID = os.OS_IDAND bec.BASE_ENV_ID = obe.BASE_ENV_IDAND os.NODE_ID = nn.NODE_IDAND bc.NODE_ID = nn.NODE_IDAND bec.PROD_ID = pv.PV_IDAND pv.IS_PATCH IS NULLAND bc.BOM_ID = nBomId) newbom,(SELECT bec.PROD_ID,nn.NODE_NAME ||'_'|| os.OS_NAME AS PK,pv.PKG_ID,NVL( pv.V_EXT, '|LINK_A_NULL|' ) AS V_EXTFROM BASE_ENV_CONTENTS bec,OS_BASE_ENV obe,OPERATING_SYSTEMS os,NETWORK_NODES nn,BOM_CONTENTS bc,PACKAGE_VERSIONS pvWHERE obe.OS_ID = os.OS_IDAND bec.BASE_ENV_ID = obe.BASE_ENV_IDAND os.NODE_ID = nn.NODE_IDAND bc.NODE_ID = nn.NODE_IDAND bec.PROD_ID = pv.PV_IDAND pv.IS_PATCH IS NULLAND bc.BOM_ID = CompareBomId) oldbomWHERE newbom.PK (+) = oldbom.PK--AND newbom.PROD_ID (+) = oldbom.PROD_IDAND newbom.PKG_ID (+) = oldbom.PKG_IDAND newbom.V_EXT (+) = oldbom.V_EXTAND newbom.PK IS NULLUNION/* REMOVED PATCHES FROM BASE CONFIGURATION*/SELECT 'R' AS CHANGE_TYPE, oldbom.PROD_ID, oldbom.PKFROM (SELECT bec.PROD_ID,nn.NODE_NAME ||'_'|| os.OS_NAME AS PKFROM BASE_ENV_CONTENTS bec,OS_BASE_ENV obe,OPERATING_SYSTEMS os,NETWORK_NODES nn,BOM_CONTENTS bc,PACKAGE_VERSIONS pvWHERE obe.OS_ID = os.OS_IDAND bec.BASE_ENV_ID = obe.BASE_ENV_IDAND os.NODE_ID = nn.NODE_IDAND bc.NODE_ID = nn.NODE_IDAND bec.PROD_ID = pv.PV_IDAND NOT pv.IS_PATCH IS NULLAND bc.BOM_ID = nBomId) newbom,(SELECT bec.PROD_ID,nn.NODE_NAME ||'_'|| os.OS_NAME AS PKFROM BASE_ENV_CONTENTS bec,OS_BASE_ENV obe,OPERATING_SYSTEMS os,NETWORK_NODES nn,BOM_CONTENTS bc,PACKAGE_VERSIONS pvWHERE obe.OS_ID = os.OS_IDAND bec.BASE_ENV_ID = obe.BASE_ENV_IDAND os.NODE_ID = nn.NODE_IDAND bc.NODE_ID = nn.NODE_IDAND bec.PROD_ID = pv.PV_IDAND NOT pv.IS_PATCH IS NULLAND bc.BOM_ID = CompareBomId) oldbomWHERE newbom.PK (+) = oldbom.PKAND newbom.PROD_ID (+) = oldbom.PROD_IDAND newbom.PK IS NULL) qry,(SELECT nn.NODE_ID,nn.NODE_NAME,nn.NODE_NAME ||'_'|| os.OS_NAME AS PKFROM OPERATING_SYSTEMS os,NETWORK_NODES nn,BOM_CONTENTS bcWHERE os.NODE_ID = nn.NODE_IDAND bc.NODE_ID = nn.NODE_IDAND bc.BOM_ID = nBomId) nlook,(SELECT nn.NODE_NAME,MAX(nn.NODE_LIFE_CYCLE) AS LAST_LIFE_CYCLEFROM BOMS bo,BOM_CONTENTS bc,BRANCHES br,NETWORK_NODES nnWHERE bc.BOM_ID = bo.BOM_IDAND bo.BRANCH_ID = br.BRANCH_IDAND bc.NODE_ID = nn.NODE_IDAND br.PROJ_ID = ProjIdGROUP BY nn.NODE_NAME) nlcWHERE nlook.PK = qry.PKAND nlc.NODE_NAME = nlook.NODE_NAME;recUpdateNode curUpdateNode%ROWTYPE;BEGIN/*--------------- Business Rules Here -------------------*//*-------------------------------------------------------*/-- Get some BOM detailsSELECT bo.PARENT_BOM_ID, br.PROJ_ID, bo.BOM_VERSION ||'.'|| bo.BOM_LIFECYCLE INTO CompareBomId, ProjId, BomVersionFROM BOMS bo,BRANCHES brWHERE bo.BOM_ID = nBomIdAND bo.BRANCH_ID = br.BRANCH_ID;-- Update node versionOPEN curUpdateNode;FETCH curUpdateNode INTO recUpdateNode;WHILE curUpdateNode%FOUNDLOOPUPDATE NETWORK_NODES nn SETnn.NODE_VERSION = BomVersion,nn.NODE_LIFE_CYCLE = recUpdateNode.NEW_LIFE_CYCLEWHERE nn.NODE_ID = recUpdateNode.NODE_ID;FETCH curUpdateNode INTO recUpdateNode;END LOOP;CLOSE curUpdateNode;END Update_Nodes_Version;/*--------------------------------------------------------------------------------------------------*/PROCEDURE Destroy_Bom ( nBomId IN VARCHAR2 ) ISrowCount NUMBER;nIdCollector DEPMGR_NUMBER_TAB_t := DEPMGR_NUMBER_TAB_t();nBomIdCollector DEPMGR_NUMBER_TAB_t := DEPMGR_NUMBER_TAB_t();BEGIN/*--------------- Business Rules Here -------------------*/IF (nBomId IS NULL)THENRAISE_APPLICATION_ERROR (-20000, 'Missing some parameters! nBomId= '|| nBomId );END IF;-- Check Requirementspk_Requirements.Destroy_Bom ( nBomId );/*-------------------------------------------------------*/-- Load CollectornBomIdCollector := IN_LIST_NUMBER( nBomId );-- Destroy BASE_ENVnIdCollector := NULL;SELECT CAST ( MULTISET ( SELECT be.BASE_ENV_IDFROM BASE_ENV beWHERE be.BOM_ID IN (SELECT *FROM THE ( SELECT CAST( nBomIdCollector AS DEPMGR_NUMBER_TAB_t ) FROM dual ))) AS DEPMGR_NUMBER_TAB_t ) INTO nIdCollector FROM DUAL;Destroy_BaseEnv ( nIdCollector );-- Destroy NETWORK_NODESnIdCollector := NULL;SELECT CAST ( MULTISET ( SELECT bc.NODE_IDFROM BOM_CONTENTS bcWHERE bc.BOM_ID IN (SELECT *FROM THE ( SELECT CAST( nBomIdCollector AS DEPMGR_NUMBER_TAB_t ) FROM dual ))) AS DEPMGR_NUMBER_TAB_t ) INTO nIdCollector FROM DUAL;pk_Network_Node.Destroy_Node ( nIdCollector, 0 );-- Destroy HARDWAREnIdCollector := NULL;SELECT CAST ( MULTISET ( SELECT hr.HARDWARE_NAME_IDFROM HARDWARE hrWHERE hr.BOM_ID IN (SELECT *FROM THE ( SELECT CAST( nBomIdCollector AS DEPMGR_NUMBER_TAB_t ) FROM dual ))) AS DEPMGR_NUMBER_TAB_t ) INTO nIdCollector FROM DUAL;Destroy_Hardware ( nIdCollector );-- Destroy BOM_STATEDELETEFROM BOM_STATE bsWHERE bs.BOM_ID IN (SELECT *FROM THE ( SELECT CAST( nBomIdCollector AS DEPMGR_NUMBER_TAB_t ) FROM dual ));-- Destroy BOM_TRAILDELETEFROM BOM_TRAIL btWHERE bt.BOM_ID IN (SELECT *FROM THE ( SELECT CAST( nBomIdCollector AS DEPMGR_NUMBER_TAB_t ) FROM dual ));---- Finnaly Destroy BOM ----DELETEFROM BOMS boWHERE bo.BOM_ID IN (SELECT *FROM THE ( SELECT CAST( nBomIdCollector AS DEPMGR_NUMBER_TAB_t ) FROM dual ));END Destroy_Bom;/*--------------------------------------------------------------------------------------------------*/PROCEDURE Destroy_BaseEnv ( nBaseEndIdCollector IN DEPMGR_NUMBER_TAB_t ) ISBEGIN/*--------------- Business Rules Here -------------------*//*-------------------------------------------------------*/-- Destroy BASE_ENV_CONTENTSDELETEFROM BASE_ENV_CONTENTS becWHERE bec.BASE_ENV_ID IN ( SELECT * FROM TABLE ( CAST ( nBaseEndIdCollector AS DEPMGR_NUMBER_TAB_t ) ) );-- Destroy OS_BASE_ENVDELETEFROM OS_BASE_ENV obeWHERE obe.BASE_ENV_ID IN ( SELECT * FROM TABLE ( CAST ( nBaseEndIdCollector AS DEPMGR_NUMBER_TAB_t ) ) );-- Destroy BASE_ENVDELETEFROM BASE_ENV beWHERE be.BASE_ENV_ID IN ( SELECT * FROM TABLE ( CAST ( nBaseEndIdCollector AS DEPMGR_NUMBER_TAB_t ) ) );END Destroy_BaseEnv;/*--------------------------------------------------------------------------------------------------*/PROCEDURE Destroy_Hardware ( nHardwareNameIdCollector IN DEPMGR_NUMBER_TAB_t ) ISnIdCollector DEPMGR_NUMBER_TAB_t := DEPMGR_NUMBER_TAB_t();BEGIN/*--------------- Business Rules Here -------------------*//*-------------------------------------------------------*/-- Destroy NETWORK_NODESnIdCollector := NULL;SELECT CAST ( MULTISET ( SELECT hn.NODE_IDFROM HARDWARE_NODE hnWHERE hn.HARDWARE_NAME_ID IN ( SELECT * FROM TABLE ( CAST ( nHardwareNameIdCollector AS DEPMGR_NUMBER_TAB_t ) ) )) AS DEPMGR_NUMBER_TAB_t ) INTO nIdCollector FROM DUAL;pk_Network_Node.Destroy_Node ( nIdCollector, 0 );-- Finnaly Destroy HARDWAREDELETEFROM HARDWARE hdWHERE hd.HARDWARE_NAME_ID IN ( SELECT * FROM TABLE ( CAST ( nHardwareNameIdCollector AS DEPMGR_NUMBER_TAB_t ) ) );END Destroy_Hardware;/*--------------------------------------------------------------------------------------------------*/PROCEDURE Get_Next_Version (nFromBomId IN BOMS.BOM_ID%TYPE,nToBranchId IN BOMS.BRANCH_ID%TYPE,sNextVersion OUT BOMS.BOM_VERSION%TYPE,sNextLifeCycle OUT BOMS.BOM_LIFECYCLE%TYPE,nParentBomId IN OUT BOMS.PARENT_BOM_ID%TYPE,nBomNameId IN BOMS.BOM_NAME_ID%TYPE ) ISIsNotHead NUMBER;FromBranch NUMBER;LastRootVersion NUMBER;LastVersion BOMS.BOM_VERSION%TYPE;CURSOR curBoms ISSELECT Max ( CAST ( bo.BOM_VERSION AS NUMBER ) ) AS LAST_VERSIONFROM BOMS bo,BRANCHES brWHERE bo.BRANCH_ID = br.BRANCH_IDAND br.PROJ_ID IN ( SELECT br.PROJ_ID FROM BRANCHES br WHERE br.BRANCH_ID = nToBranchId )AND bo.BOM_NAME_ID = nBomNameId;recBoms curBoms%ROWTYPE;BEGIN/*--------------- Business Rules Here -------------------*//*-------------------------------------------------------*/-- Get Last Root VersionOPEN curBoms;FETCH curBoms INTO recBoms;LastRootVersion := recBoms.LAST_VERSION;CLOSE curBoms;IF nFromBomId IS NULL THEN/* Blank BOM Required */-- Get Next Available VersionsNextVersion := '1';sNextLifeCycle := '0';IF LastRootVersion IS NOT NULL THENsNextVersion := CAST( LastRootVersion + 1 AS VARCHAR2 );END IF;ELSE/* Create Bom From ... */-- Check if BOM is comming from different IterationSELECT bo.BRANCH_ID INTO FromBranchFROM BOMS boWHERE bo.BOM_ID = nFromBomId;IF FromBranch = nToBranchId THEN/* Bom is comming from same Iteration *//* i.e. Continue on same version stream. */-- Set parent_idnParentBomId := nFromBomId;-- Get Last VersionSELECT bo.BOM_VERSION INTO LastVersionFROM BOMS boWHERE bo.BOM_ID = nFromBomId;-- Check if Bom is Head (i.e. Last on its branch )SELECT COUNT(*) INTO IsNotHeadFROM BOMS bo,BRANCHES brWHERE bo.BRANCH_ID = br.BRANCH_IDAND br.PROJ_ID IN ( SELECT br.PROJ_ID FROM BRANCHES br WHERE br.BRANCH_ID = FromBranch )AND bo.PARENT_BOM_ID = nFromBomIdAND bo.BOM_VERSION = LastVersionAND bo.BOM_ID != bo.PARENT_BOM_ID;IF IsNotHead = 0 THEN/* Bom IS Head (i.e. Last on its branch ) */-- Get Bom Version and LifeCycleSELECT bo.BOM_VERSION, bo.BOM_LIFECYCLE INTO sNextVersion, sNextLifeCycleFROM BOMS boWHERE bo.BOM_ID = nFromBomId;-- Increase LifecyclesNextLifeCycle := CAST ( ( CAST( sNextLifeCycle AS NUMBER ) + 1 ) AS VARCHAR2 );ELSE/* Bom IS NOT Head */-- Get Bom Version and LifeCycleSELECT bo.BOM_VERSION, bo.BOM_LIFECYCLE INTO sNextVersion, sNextLifeCycleFROM BOMS boWHERE bo.BOM_ID = nFromBomId;-- Branch Bom VersionsNextVersion := sNextVersion ||'.'|| sNextLifeCycle;-- Reset Lifecycle to 1sNextLifeCycle := '1';END IF;ELSE/* BOM is comming from Different Iteration *//* i.e. Start new version stream. */-- Get Next Available VersionsNextVersion := '1';sNextLifeCycle := '0';IF LastRootVersion IS NOT NULL THENsNextVersion := CAST( LastRootVersion + 1 AS VARCHAR2 );END IF;END IF;END IF;END Get_Next_Version;/*--------------------------------------------------------------------------------------------------*/PROCEDURE Release_To ( nBomId IN BOM_STATE.BOM_ID%TYPE,nUserId IN BOM_STATE.USER_ID%TYPE,nToState IN BOM_STATE.STATE_ID%TYPE DEFAULT NULL,sComments IN BOM_TRAIL.TRAIL_COMMENTS%TYPE DEFAULT NULL ) ISrowCount NUMBER;IsOfficial BOMS.IS_READONLY%TYPE;StateId BOM_STATE.STATE_ID%TYPE;StateName STATES.STATE_NAME%TYPE;BEGIN/*--------------- Business Rules Here -------------------*/-- Check if already releasedSELECT Count(*) INTO rowCountFROM BOM_STATE bsWHERE bs.BOM_ID = nBomIdAND bs.STATE_ID = nToState;IF rowCount > 0 THENRAISE_APPLICATION_ERROR (-20000, 'This BOM is already release' );END IF;-- Check if Bom is officialSELECT bo.IS_READONLY INTO IsOfficialFROM BOMS boWHERE bo.BOM_ID = nBomId;IF (IsOfficial != 'Y') AND (nToState IS NOT NULL) THENRAISE_APPLICATION_ERROR (-20000, 'BOM must be locked first' );END IF;/*-------------------------------------------------------*/-- Get State --StateId := nToState;IF StateId IS NULL THEN-- If state_id is null it is considered as state_type_enum = 'BOM Creator'SELECT st.STATE_ID INTO StateIdFROM BOMS bo,BRANCHES br,DM_PROJECTS proj,STATES stWHERE bo.BRANCH_ID = br.BRANCH_IDAND br.PROJ_ID = proj.PROJ_IDAND st.PROJ_ID = proj.PROJ_IDAND bo.BOM_ID = nBomIdAND st.STATE_TYPE_ENUM = GET_SYSTEM_VALUE('enumSTATE_TYPE_CREATOR');END IF;-- Add Bom to State --INSERT INTO BOM_STATE (BOM_ID, STATE_ID, DATE_STAMP, IS_DISPLAYED, USER_ID )VALUES( nBomId,StateId,GET_DATE(),'Y',nUserId );-- Auto-Hide Bompk_State.Auto_Hide ( StateId, nBomId, GET_SYSTEM_VALUE('enumEVENT_ON_RELEASE_TO') );/* Log BOM_TRAIL */-- Get State Name --SELECT st.STATE_NAME INTO StateNameFROM STATES stWHERE st.STATE_ID = StateId;-- Log Actionpk_Bom.Log_Bom_Trail ( nBomId, 'RELEASED TO: '|| StateName, nUserId, sComments );END Release_To;/*--------------------------------------------------------------------------------------------------*/PROCEDURE Accept_Reject_Comments ( nBomId IN BOMS.BOM_ID%TYPE,nRejectSeq IN REJECTION_TRAIL.REJECT_SEQ%TYPE,sComments IN REJECTION_TRAIL.COMMENTS%TYPE DEFAULT NULL,nUserId IN REJECTION_TRAIL.USER_ID%TYPE ) ISIsRejected REJECTION_TRAIL.IS_REJECTED%TYPE;BEGIN/*--------------- Business Rules Here -------------------*/IF (nBomId IS NULL)THENRAISE_APPLICATION_ERROR (-20000, 'Missing some parameters! nBomId= '|| nBomId );END IF;/*-------------------------------------------------------*/IF nRejectSeq IS NULL THEN/* CREATE New Note */-- Get last IsRejected stateSELECT rt.IS_REJECTED INTO IsRejectedFROM REJECTION_TRAIL rtWHERE rt.ENTITY_ID = nBomIdAND rt.ENUM_ENTITY_TYPE = GET_SYSTEM_VALUE('enumENTITY_TYPE_BOM')AND ROWNUM = 1ORDER BY rt.REJECT_SEQ;/* Log Rejection Trail */pk_Utils.Log_Rejection_Trail ( GET_SYSTEM_VALUE('enumENTITY_TYPE_BOM'), nBomId, IsRejected, nUserId, sComments );ELSE/* EDIT Note */UPDATE REJECTION_TRAIL rt SETrt.COMMENTS = sComments,rt.USER_ID = nUserId,rt.DATE_TIME_STAMP = GET_DATETIME()WHERE rt.ENUM_ENTITY_TYPE = GET_SYSTEM_VALUE('enumENTITY_TYPE_BOM')AND rt.ENTITY_ID = nBomIdAND rt.REJECT_SEQ = nRejectSeq;END IF;END Accept_Reject_Comments;/*--------------------------------------------------------------------------------------------------*/PROCEDURE Log_Bom_Trail ( nBomId IN BOM_TRAIL.BOM_ID%TYPE,sAction IN BOM_TRAIL.TRAIL_ACTION%TYPE,nUserId IN BOM_TRAIL.USER_ID%TYPE,sComments IN BOM_TRAIL.TRAIL_COMMENTS%TYPE DEFAULT NULL ) ISBEGIN/*--------------- Business Rules Here -------------------*/IF (nBomId IS NULL) OR (sAction IS NULL) OR (nUserId IS NULL)THENRAISE_APPLICATION_ERROR (-20000, 'Missing some parameters! nBomId= '|| nBomId ||', sAction='|| sAction ||', nUserId='|| nUserId );END IF;/*-------------------------------------------------------*/INSERT INTO BOM_TRAIL ( BOM_ID, TRAIL_ACTION, TRAIL_COMMENTS, USER_ID, DATE_TIME_STAMP )VALUES ( nBomId, sAction, sComments, nUserId, GET_DATETIME() );END Log_Bom_Trail;/*--------------------------------------------------------------------------------------------------*/PROCEDURE Log_Node_Trail ( nNodeId IN NODE_TRAIL.NODE_ID%TYPE,sAction IN NODE_TRAIL.TRAIL_ACTION%TYPE,nUserId IN NODE_TRAIL.USER_ID%TYPE ) ISBEGIN/*--------------- Business Rules Here -------------------*/IF (nNodeId IS NULL) OR (sAction IS NULL) OR (nUserId IS NULL)THENRAISE_APPLICATION_ERROR (-20000, 'Missing some parameters! nNodeId= '|| nNodeId ||', sAction='|| sAction ||', nUserId='|| nUserId );END IF;/*-------------------------------------------------------*/INSERT INTO NODE_TRAIL ( NODE_ID, TRAIL_ACTION, USER_ID, DATE_TIME_STAMP )VALUES ( nNodeId, sAction, nUserId, GET_DATETIME() );END Log_Node_Trail;/*--------------------------------------------------------------------------------------------------*/PROCEDURE Log_Os_Trail ( nOsId IN OS_TRAIL.OS_ID%TYPE,sAction IN OS_TRAIL.TRAIL_ACTION%TYPE,nUserId IN OS_TRAIL.USER_ID%TYPE ) ISBEGIN/*--------------- Business Rules Here -------------------*/IF (nOsId IS NULL) OR (sAction IS NULL) OR (nUserId IS NULL)THENRAISE_APPLICATION_ERROR (-20000, 'Missing some parameters! nOsId= '|| nOsId ||', sAction='|| sAction ||', nUserId='|| nUserId );END IF;/*-------------------------------------------------------*/INSERT INTO OS_TRAIL ( OS_ID, TRAIL_ACTION, USER_ID, DATE_TIME_STAMP )VALUES ( nOsId, sAction, nUserId, GET_DATETIME() );END Log_Os_Trail;/*--------------------------------------------------------------------------------------------------*/END pk_Bom_Test;//---------------------------------------------------------- DDL for Package Body PK_NETWORK_NODE--------------------------------------------------------CREATE OR REPLACE PACKAGE BODY "PK_NETWORK_NODE"IS/* ---------------------------------------------------------------------------Version: 1.0.0--------------------------------------------------------------------------- *//*--------------------------------------------------------------------------------------------------*/PROCEDURE Add_Node ( sNodeName IN NETWORK_NODES.NODE_NAME%TYPE,nNodeTypeId IN NETWORK_NODES.NODE_TYPE_ID%TYPE,sNodeComments IN NETWORK_NODES.NODE_COMMENTS%TYPE,nBomId IN BOM_CONTENTS.BOM_ID%TYPE,nUserId IN NUMBER,sNotXML IN NETWORK_NODES.NOT_XML%TYPE ) ISNodeID NUMBER;CURSOR nn_cur IS -- Check Node Name DuplicatesSELECT bc.node_idFROM BOM_CONTENTS bc,NETWORK_NODES nnWHERE bc.NODE_ID = nn.NODE_IDAND bc.BOM_ID = nBomIdAND UPPER( nn.NODE_NAME ) = UPPER( sNodeName );nn_rec nn_cur%ROWTYPE;DUPLICATE_NODE_NAME EXCEPTION;BEGIN/*--------------- Business Rules Here -------------------*/OPEN nn_cur;FETCH nn_cur INTO nn_rec;IF nn_cur%FOUNDTHENRAISE DUPLICATE_NODE_NAME;END IF;CLOSE nn_cur;/*-------------------------------------------------------*/-- Get node_idSELECT SEQ_NODE_ID.NEXTVAL INTO NodeID FROM DUAL;-- Insert new Network NodeINSERT INTO NETWORK_NODES ( NODE_ID, NODE_TYPE_ID, NODE_NAME, NODE_COMMENTS, NOT_XML )VALUES (NodeID,nNodeTypeId,sNodeName,sNodeComments,sNotXML);-- Link Node with the BOMINSERT INTO BOM_CONTENTS ( BOM_ID, NODE_ID )VALUES (nBomId,NodeID);/* Log BOM_TRAIL */pk_Bom.Log_Bom_Trail ( nBomId, 'Added Network Node: '||sNodeName, nUserId );EXCEPTIONWHEN DUPLICATE_NODE_NAMETHENRAISE_APPLICATION_ERROR (-20000, 'Node Name "'|| sNodeName ||'" is Already Used in this BOM.');END Add_Node;/*--------------------------------------------------------------------------------------------------*/PROCEDURE Update_Node ( nNodeId IN NETWORK_NODES.NODE_ID%TYPE,sNodeName IN NETWORK_NODES.NODE_NAME%TYPE,nNodeTypeId IN NETWORK_NODES.NODE_TYPE_ID%TYPE,sNodeComments IN NETWORK_NODES.NODE_COMMENTS%TYPE,nUserId IN NUMBER,nLifeCycle IN NUMBER,sNotXML IN NETWORK_NODES.NOT_XML%TYPE ) ISBomID NUMBER;CURSOR nn_cur IS -- Check Node Name DuplicatesSELECT bc.node_idFROM BOM_CONTENTS bc,NETWORK_NODES nnWHERE bc.NODE_ID = nn.NODE_IDAND bc.BOM_ID = BomIdAND UPPER( nn.NODE_NAME ) = UPPER( sNodeName )AND nn.NODE_ID != nNodeId;nn_rec nn_cur%ROWTYPE;DUPLICATE_NODE_NAME EXCEPTION;BEGIN/*--------------- Business Rules Here -------------------*/-- Check all parametersIF (nNodeId IS NULL) OR (nNodeTypeId IS NULL)THENRAISE_APPLICATION_ERROR (-20000, 'Missing some parameters! nNodeId='|| nNodeId ||', nNodeTypeId='|| nNodeTypeId );END IF;-- Get bom_idSELECT bc.BOM_ID INTO BomIdFROM BOM_CONTENTS bcWHERE bc.NODE_ID = nNodeId;-- Check for duplicate namesOPEN nn_cur;FETCH nn_cur INTO nn_rec;IF nn_cur%FOUNDTHENRAISE DUPLICATE_NODE_NAME;END IF;CLOSE nn_cur;/*-------------------------------------------------------*/-- Update Network Node DetailsUPDATE NETWORK_NODES nn SETnn.NODE_NAME = sNodeName,nn.NODE_COMMENTS = sNodeComments,nn.NODE_TYPE_ID = nNodeTypeId,nn.NODE_LIFE_CYCLE = nLifeCycle,nn.NOT_XML = sNotXMLWHERE nn.NODE_ID = nNodeId;/* Log BOM_TRAIL */pk_Bom.Log_Bom_Trail ( BomID, 'Updated Network Node: '||sNodeName, nUserId );EXCEPTIONWHEN DUPLICATE_NODE_NAMETHENRAISE_APPLICATION_ERROR (-20000, 'Node Name "'|| sNodeName ||'" is Already Used in this BOM.');END Update_Node;/*--------------------------------------------------------------------------------------------------*/PROCEDURE Remove_Node ( sNodeIdList IN VARCHAR2,nBomId IN BOM_CONTENTS.BOM_ID%TYPE ) ISNODE_ID_REQUIRED EXCEPTION;BEGIN/*--------------- Business Rules Here -------------------*/IF sNodeIdList IS NULLTHENRAISE NODE_ID_REQUIRED;END IF;/*-------------------------------------------------------*/-- Remove from Bom ContentsDELETE FROM BOM_CONTENTSWHERE bom_id = nBomIdAND node_id IN (SELECT *FROM THE ( SELECT CAST( IN_LIST_NUMBER( sNodeIdList ) AS DEPMGR_NUMBER_TAB_t ) FROM dual ));-- Remove Network NodesDELETE FROM NETWORK_NODESWHERE node_id IN (SELECT *FROM THE ( SELECT CAST( IN_LIST_NUMBER( sNodeIdList ) AS DEPMGR_NUMBER_TAB_t ) FROM dual ));EXCEPTIONWHEN NODE_ID_REQUIREDTHENRAISE_APPLICATION_ERROR (-20000, 'You must select at least one Network Node.');END Remove_Node;/*--------------------------------------------------------------------------------------------------*/PROCEDURE Destroy_Node ( nNodeIdCollection IN DEPMGR_NUMBER_TAB_t, nUserId IN NUMBER ) ISnIdCollector DEPMGR_NUMBER_TAB_t := DEPMGR_NUMBER_TAB_t();NodeName VARCHAR2(4000);BomId NUMBER;BEGIN/*--------------- Business Rules Here -------------------*//*-------------------------------------------------------*/IF (nNodeIdCollection.COUNT > 0) THEN--Log Action TrailFOR i IN 1..nNodeIdCollection.COUNTLOOP-- Get bom_idSELECT bc.BOM_ID INTO BomIdFROM BOM_CONTENTS bcWHERE bc.NODE_ID = nNodeIdCollection(i);--Get node_nameSELECT nn.NODE_NAME INTO NodeNameFROM NETWORK_NODES nnWHERE nn.NODE_ID = nNodeIdCollection(i);/* Log BOM_TRAIL */pk_Bom.Log_Bom_Trail ( BomId, 'Deleted Network Node: '||NodeName, nUserId );END LOOP;-- Destroy Hardware Node LinkDELETEFROM HARDWARE_NODE hnWHERE hn.NODE_ID IN ( SELECT * FROM TABLE ( CAST ( nNodeIdCollection AS DEPMGR_NUMBER_TAB_t ) ) );-- Destroy Node from Bom ContentsDELETEFROM BOM_CONTENTS bcWHERE bc.NODE_ID IN ( SELECT * FROM TABLE ( CAST ( nNodeIdCollection AS DEPMGR_NUMBER_TAB_t ) ) );-- Destroy Operation SystemsnIdCollector := NULL;SELECT CAST ( MULTISET ( SELECT os.OS_IDFROM OPERATING_SYSTEMS osWHERE os.NODE_ID IN ( SELECT * FROM TABLE ( CAST ( nNodeIdCollection AS DEPMGR_NUMBER_TAB_t ) ) )) AS DEPMGR_NUMBER_TAB_t ) INTO nIdCollector FROM DUAL;pk_Operating_System.Destroy_Os ( nIdCollector, nUserId );---- Finnaly destroy Network Node ----DELETEFROM NETWORK_NODES nnWHERE nn.NODE_ID IN ( SELECT * FROM TABLE ( CAST ( nNodeIdCollection AS DEPMGR_NUMBER_TAB_t ) ) );END IF;END Destroy_Node;/*--------------------------------------------------------------------------------------------------*/PROCEDURE Destroy_Node ( nNodeIdList IN VARCHAR2, nUserId IN NUMBER ) ISnIdCollector DEPMGR_NUMBER_TAB_t := DEPMGR_NUMBER_TAB_t();BEGIN/*--------------- Business Rules Here -------------------*//*-------------------------------------------------------*/nIdCollector := IN_LIST_NUMBER ( nNodeIdList );Destroy_Node ( nIdCollector, nUserId);END Destroy_Node;/*--------------------------------------------------------------------------------------------------*/PROCEDURE Sort_Nodes ( sNodeIdOrder IN VARCHAR2,nBomId IN BOMS.BOM_ID%TYPE,nUserId IN NUMBER ) ISnIdCollector DEPMGR_NUMBER_TAB_t := DEPMGR_NUMBER_TAB_t();SeqNum NUMBER;BEGIN/*--------------- Business Rules Here -------------------*/-- Check for parametersIF (sNodeIdOrder IS NULL) OR (nBomId IS NULL)THENRAISE_APPLICATION_ERROR (-20000, 'Missing some parameters! nBomId='|| nBomId ||' sNodeIdOrder='|| sNodeIdOrder );END IF;/*-------------------------------------------------------*/nIdCollector := IN_LIST_NUMBER ( sNodeIdOrder );-- Set start sequence numberSeqNum := 1;FOR i IN 1..nIdCollector.COUNTLOOP-- Update new sequence numberUPDATE BOM_CONTENTS bc SETbc.SEQ_NUM = SeqNumWHERE bc.BOM_ID = nBomIdAND bc.NODE_ID = nIdCollector(i);SeqNum := SeqNum + 1;END LOOP;END Sort_Nodes;/*--------------------------------------------------------------------------------------------------*/END pk_Network_Node;//---------------------------------------------------------- DDL for Package Body PK_NETWORK_NODE_TEST--------------------------------------------------------CREATE OR REPLACE PACKAGE BODY "PK_NETWORK_NODE_TEST"IS/* ---------------------------------------------------------------------------Version: 1.0.0--------------------------------------------------------------------------- *//*--------------------------------------------------------------------------------------------------*/PROCEDURE Add_Node ( sNodeName IN NETWORK_NODES.NODE_NAME%TYPE,nNodeTypeId IN NETWORK_NODES.NODE_TYPE_ID%TYPE,sNodeComments IN NETWORK_NODES.NODE_COMMENTS%TYPE,nBomId IN BOM_CONTENTS.BOM_ID%TYPE,nUserId IN NUMBER ) ISNodeID NUMBER;CURSOR nn_cur IS -- Check Node Name DuplicatesSELECT bc.node_idFROM BOM_CONTENTS bc,NETWORK_NODES nnWHERE bc.NODE_ID = nn.NODE_IDAND bc.BOM_ID = nBomIdAND UPPER( nn.NODE_NAME ) = UPPER( sNodeName );nn_rec nn_cur%ROWTYPE;DUPLICATE_NODE_NAME EXCEPTION;BEGIN/*--------------- Business Rules Here -------------------*/OPEN nn_cur;FETCH nn_cur INTO nn_rec;IF nn_cur%FOUNDTHENRAISE DUPLICATE_NODE_NAME;END IF;CLOSE nn_cur;/*-------------------------------------------------------*/-- Get node_idSELECT SEQ_NODE_ID.NEXTVAL INTO NodeID FROM DUAL;-- Insert new Network NodeINSERT INTO NETWORK_NODES ( NODE_ID, NODE_TYPE_ID, NODE_NAME, NODE_COMMENTS )VALUES (NodeID,nNodeTypeId,sNodeName,sNodeComments);-- Link Node with the BOMINSERT INTO BOM_CONTENTS ( BOM_ID, NODE_ID )VALUES (nBomId,NodeID);/* Log BOM_TRAIL */pk_Bom.Log_Bom_Trail ( nBomId, 'ADDED NETWORK NODE: '||sNodeName, nUserId );EXCEPTIONWHEN DUPLICATE_NODE_NAMETHENRAISE_APPLICATION_ERROR (-20000, 'Node Name "'|| sNodeName ||'" is Already Used in this BOM.');END Add_Node;/*--------------------------------------------------------------------------------------------------*/PROCEDURE Update_Node ( nNodeId IN NETWORK_NODES.NODE_ID%TYPE,sNodeName IN NETWORK_NODES.NODE_NAME%TYPE,nNodeTypeId IN NETWORK_NODES.NODE_TYPE_ID%TYPE,sNodeComments IN NETWORK_NODES.NODE_COMMENTS%TYPE ) ISBomID NUMBER;CURSOR nn_cur IS -- Check Node Name DuplicatesSELECT bc.node_idFROM BOM_CONTENTS bc,NETWORK_NODES nnWHERE bc.NODE_ID = nn.NODE_IDAND bc.BOM_ID = BomIdAND UPPER( nn.NODE_NAME ) = UPPER( sNodeName )AND nn.NODE_ID != nNodeId;nn_rec nn_cur%ROWTYPE;DUPLICATE_NODE_NAME EXCEPTION;BEGIN/*--------------- Business Rules Here -------------------*/-- Check all parametersIF (nNodeId IS NULL) OR (nNodeTypeId IS NULL)THENRAISE_APPLICATION_ERROR (-20000, 'Missing some parameters! nNodeId='|| nNodeId ||', nNodeTypeId='|| nNodeTypeId );END IF;-- Get bom_idSELECT bc.BOM_ID INTO BomIdFROM BOM_CONTENTS bcWHERE bc.NODE_ID = nNodeId;-- Check for duplicate namesOPEN nn_cur;FETCH nn_cur INTO nn_rec;IF nn_cur%FOUNDTHENRAISE DUPLICATE_NODE_NAME;END IF;CLOSE nn_cur;/*-------------------------------------------------------*/-- Update Network Node DetailsUPDATE NETWORK_NODES nn SETnn.NODE_NAME = sNodeName,nn.NODE_COMMENTS = sNodeComments,nn.NODE_TYPE_ID = nNodeTypeIdWHERE nn.NODE_ID = nNodeId;EXCEPTIONWHEN DUPLICATE_NODE_NAMETHENRAISE_APPLICATION_ERROR (-20000, 'Node Name "'|| sNodeName ||'" is Already Used in this BOM.');END Update_Node;/*--------------------------------------------------------------------------------------------------*/PROCEDURE Remove_Node ( sNodeIdList IN VARCHAR2,nBomId IN BOM_CONTENTS.BOM_ID%TYPE ) ISNODE_ID_REQUIRED EXCEPTION;BEGIN/*--------------- Business Rules Here -------------------*/IF sNodeIdList IS NULLTHENRAISE NODE_ID_REQUIRED;END IF;/*-------------------------------------------------------*/-- Remove from Bom ContentsDELETE FROM BOM_CONTENTSWHERE bom_id = nBomIdAND node_id IN (SELECT *FROM THE ( SELECT CAST( IN_LIST_NUMBER( sNodeIdList ) AS DEPMGR_NUMBER_TAB_t ) FROM dual ));-- Remove Network NodesDELETE FROM NETWORK_NODESWHERE node_id IN (SELECT *FROM THE ( SELECT CAST( IN_LIST_NUMBER( sNodeIdList ) AS DEPMGR_NUMBER_TAB_t ) FROM dual ));EXCEPTIONWHEN NODE_ID_REQUIREDTHENRAISE_APPLICATION_ERROR (-20000, 'You must select at least one Network Node.');END Remove_Node;/*--------------------------------------------------------------------------------------------------*/PROCEDURE Destroy_Node ( nNodeIdCollection IN DEPMGR_NUMBER_TAB_t ) ISnIdCollector DEPMGR_NUMBER_TAB_t := DEPMGR_NUMBER_TAB_t();BEGIN/*--------------- Business Rules Here -------------------*//*-------------------------------------------------------*/IF (nNodeIdCollection.COUNT > 0) THEN-- Destroy Hardware Node LinkDELETEFROM HARDWARE_NODE hnWHERE hn.NODE_ID IN ( SELECT * FROM TABLE ( CAST ( nNodeIdCollection AS DEPMGR_NUMBER_TAB_t ) ) );-- Destroy Node from Bom ContentsDELETEFROM BOM_CONTENTS bcWHERE bc.NODE_ID IN ( SELECT * FROM TABLE ( CAST ( nNodeIdCollection AS DEPMGR_NUMBER_TAB_t ) ) );-- Destroy Operation SystemsnIdCollector := NULL;SELECT CAST ( MULTISET ( SELECT os.OS_IDFROM OPERATING_SYSTEMS osWHERE os.NODE_ID IN ( SELECT * FROM TABLE ( CAST ( nNodeIdCollection AS DEPMGR_NUMBER_TAB_t ) ) )) AS DEPMGR_NUMBER_TAB_t ) INTO nIdCollector FROM DUAL;pk_Operating_System.Destroy_Os ( nIdCollector );---- Finnaly destroy Network Node ----DELETEFROM NETWORK_NODES nnWHERE nn.NODE_ID IN ( SELECT * FROM TABLE ( CAST ( nNodeIdCollection AS DEPMGR_NUMBER_TAB_t ) ) );END IF;END Destroy_Node;/*--------------------------------------------------------------------------------------------------*/PROCEDURE Destroy_Node ( nNodeIdList IN VARCHAR2 ) ISnIdCollector DEPMGR_NUMBER_TAB_t := DEPMGR_NUMBER_TAB_t();BEGIN/*--------------- Business Rules Here -------------------*//*-------------------------------------------------------*/nIdCollector := IN_LIST_NUMBER ( nNodeIdList );Destroy_Node ( nIdCollector );END Destroy_Node;/*--------------------------------------------------------------------------------------------------*/PROCEDURE Sort_Nodes ( sNodeIdOrder IN VARCHAR2,nBomId IN BOMS.BOM_ID%TYPE,nUserId IN NUMBER ) ISnIdCollector DEPMGR_NUMBER_TAB_t := DEPMGR_NUMBER_TAB_t();SeqNum NUMBER;BEGIN/*--------------- Business Rules Here -------------------*/-- Check for parametersIF (sNodeIdOrder IS NULL) OR (nBomId IS NULL)THENRAISE_APPLICATION_ERROR (-20000, 'Missing some parameters! nBomId='|| nBomId ||' sNodeIdOrder='|| sNodeIdOrder );END IF;/*-------------------------------------------------------*/nIdCollector := IN_LIST_NUMBER ( sNodeIdOrder );-- Set start sequence numberSeqNum := 1;FOR i IN 1..nIdCollector.COUNTLOOP-- Update new sequence numberUPDATE BOM_CONTENTS bc SETbc.SEQ_NUM = SeqNumWHERE bc.BOM_ID = nBomIdAND bc.NODE_ID = nIdCollector(i);SeqNum := SeqNum + 1;END LOOP;END Sort_Nodes;/*--------------------------------------------------------------------------------------------------*/END pk_Network_Node_test;//---------------------------------------------------------- DDL for Package Body PK_OPERATING_SYSTEM--------------------------------------------------------CREATE OR REPLACE PACKAGE BODY "PK_OPERATING_SYSTEM"IS/* ---------------------------------------------------------------------------Modified: Rupesh SolankiDate: 20/03/2006--------------------------------------------------------------------------- *//*--------------------------------------------------------------------------------------------------*/PROCEDURE Add_Os ( sOsName IN OPERATING_SYSTEMS.OS_NAME%TYPE,sOsComments IN OPERATING_SYSTEMS.OS_COMMENTS%TYPE,sNodeSpecFile IN OPERATING_SYSTEMS.NODE_SPEC_FILE%TYPE,sNotXML IN OPERATING_SYSTEMS.NOT_XML%TYPE,nNodeId IN OPERATING_SYSTEMS.NODE_ID%TYPE,nBaseEnvName IN BASE_ENV.BASE_ENV_NAME%TYPE,nBosId IN BASE_ENV.BOS_ID%TYPE DEFAULT NULL,nUserId IN NUMBER ) ISOsID NUMBER;BosId NUMBER;BaseEnvId NUMBER;BomId NUMBER;CURSOR curOs IS -- Check Node Name DuplicatesSELECT os.OS_IDFROM OPERATING_SYSTEMS osWHERE os.NODE_ID = nNodeIdAND UPPER(os.OS_NAME) = UPPER(sOsName);recOs curOs%ROWTYPE;DUPLICATE_OS_NAME EXCEPTION;BEGIN/*--------------- Business Rules Here -------------------*/-- Check for duplicate namesOPEN curOs;FETCH curOs INTO recOs;IF curOs%FOUNDTHENRAISE DUPLICATE_OS_NAME;END IF;CLOSE curOs;/*-------------------------------------------------------*/-- Get os_idSELECT SEQ_OS_ID.NEXTVAL INTO OsID FROM DUAL;-- Insert new OSINSERT INTO OPERATING_SYSTEMS ( OS_ID, OS_NAME, NODE_ID, OS_COMMENTS, NODE_SPEC_FILE, NOT_XML )VALUES ( OsID, sOsName, nNodeId, sOsComments, sNodeSpecFile, sNotXML );/* Set up Base Configuration */IF nBaseEnvName IS NOT NULL THEN-- Get bom_idSELECT bc.BOM_ID INTO BomIdFROM BOM_CONTENTS bcWHERE bc.NODE_ID = nNodeId;-- Set bos _id to defaultBosId := 5; -- This is Generic OS in BOS_TYPES tableIF nBosId IS NOT NULL THENBosId := nBosId;END IF;-- Get base_env_idBaseEnvId := pk_Base_Env.Get_Base_Env_Id ( nBaseEnvName, BomId );-- Create Base Env if RequiredIF BaseEnvId IS NULL THENpk_Base_Env.Add_Base_Env ( nBaseEnvName, BosId, BomId, OsID, nUserId );BaseEnvId := pk_Base_Env.Get_Base_Env_Id ( nBaseEnvName, BomId );END IF;-- Finally assign this base env to OSINSERT INTO OS_BASE_ENV ( OS_ID, BASE_ENV_ID )VALUES ( OsID, BaseEnvId );END IF;/* Log BOM_TRAIL */pk_Bom.Log_Node_Trail ( nNodeId, 'Added Operating System: '||sOsName, nUserId );EXCEPTIONWHEN DUPLICATE_OS_NAMETHENRAISE_APPLICATION_ERROR (-20000, 'OS Name "'|| sOsName ||'" is Already Used in this Network Node.');END Add_Os;/*--------------------------------------------------------------------------------------------------*/PROCEDURE Update_Os ( nOsId IN OPERATING_SYSTEMS.OS_ID%TYPE,sOsName IN OPERATING_SYSTEMS.OS_NAME%TYPE,sOsComments IN OPERATING_SYSTEMS.OS_COMMENTS%TYPE,nBaseEnvName IN BASE_ENV.BASE_ENV_NAME%TYPE,sNodeSpecFile IN OPERATING_SYSTEMS.NODE_SPEC_FILE%TYPE,sNotXML IN OPERATING_SYSTEMS.NOT_XML%TYPE,nUserId IN NUMBER ) ISBomId NUMBER;BosId NUMBER;BaseEnvId NUMBER;NodeId NUMBER;CURSOR curOs ISSELECT os.OS_IDFROM OPERATING_SYSTEMS osWHERE os.NODE_ID = NodeIdAND UPPER(os.OS_NAME) = UPPER(sOsName)AND os.OS_ID != nOsId;recOs curOs%ROWTYPE;DUPLICATE_OS_NAME EXCEPTION;BEGIN/*--------------- Business Rules Here -------------------*/-- Check for parametersIF (nOsId IS NULL)THENRAISE_APPLICATION_ERROR (-20000, 'Missing some parameters! nOsId='|| nOsId );END IF;-- Get node_idSELECT os.NODE_ID INTO NodeIdFROM OPERATING_SYSTEMS osWHERE os.OS_ID = nOsId;-- Check for duplicate namesOPEN curOs;FETCH curOs INTO recOs;IF curOs%FOUNDTHENRAISE DUPLICATE_OS_NAME;END IF;CLOSE curOs;/*-------------------------------------------------------*/-- Delete current Base EnvDELETEFROM OS_BASE_ENV obeWHERE obe.OS_ID = nOsId;/* Set up Base Configuration */IF nBaseEnvName IS NOT NULL THEN-- Get bom_idSELECT bc.BOM_ID INTO BomIdFROM BOM_CONTENTS bcWHERE bc.NODE_ID = NodeId;-- Get base_env_idBaseEnvId := pk_Base_Env.Get_Base_Env_Id ( nBaseEnvName, BomId );-- Set bos _id to defaultBosId := 5; -- This is Generic OS in BOS_TYPES table-- Create Base Env if RequiredIF BaseEnvId IS NULL THENpk_Base_Env.Add_Base_Env ( nBaseEnvName, BosId, BomId, nOsId, nUserId );BaseEnvId := pk_Base_Env.Get_Base_Env_Id ( nBaseEnvName, BomId );ELSE-- Get bos_idSELECT be.BOS_ID INTO BosIdFROM BASE_ENV beWHERE be.BASE_ENV_ID = BaseEnvId;END IF;-- Finally assign this base env to OSINSERT INTO OS_BASE_ENV ( OS_ID, BASE_ENV_ID )VALUES ( nOsId, BaseEnvId );END IF;-- Update Os DetailsUPDATE OPERATING_SYSTEMS os SETos.OS_NAME = sOsName,os.OS_COMMENTS = sOsComments,os.NODE_SPEC_FILE = sNodeSpecFile,os.NOT_XML = sNotXMLWHERE os.OS_ID = nOsId;/* Log BOM_TRAIL */pk_Bom.Log_Node_Trail ( NodeId, 'Updated Operating System: '||sOsName, nUserId );EXCEPTIONWHEN DUPLICATE_OS_NAMETHENRAISE_APPLICATION_ERROR (-20000, 'OS Name "'|| sOsName ||'" is Already Used in this Network Node.');END Update_Os;/*--------------------------------------------------------------------------------------------------*/PROCEDURE Add_Product_Wrap ( nPkgId IN NUMBER,sProdVersion IN VARCHAR2,nOsId IN OS_CONTENTS.OS_ID%TYPE,nUserId IN NUMBER,sComments IN VARCHAR2 DEFAULT NULL ) ISProdId PACKAGE_VERSIONS.PV_ID%TYPE;ProdName PACKAGES.PKG_NAME%TYPE;CURSOR prod_cur ISSELECT osc.PROD_IDFROM OS_CONTENTS oscWHERE osc.PROD_ID = ProdIdAND osc.OS_ID = nOsId;prod_rec prod_cur%ROWTYPE;BEGIN/*--------------- Business Rules Here -------------------*/IF (NOT nPkgId IS NULL) AND (sProdVersion IS NULL) AND (NOT nOsId IS NULL) AND (NOT nUserId IS NULL) THENRAISE_APPLICATION_ERROR (-20000, 'Please provide Product Version.' );ELSIF (nPkgId IS NULL) OR (sProdVersion IS NULL) OR (nOsId IS NULL) OR (nUserId IS NULL) THENRAISE_APPLICATION_ERROR (-20000, 'Missing some parameters! nPkgId= '|| nPkgId ||', sProdVersion='|| sProdVersion ||', nOsId='|| nOsId ||', nUserId='|| nUserId );END IF;/*-------------------------------------------------------*/-- Get Product Name --SELECT PKG_NAME INTO ProdName FROM PACKAGES WHERE PKG_ID = nPkgId;-- Create Product Version in Release Manager if Required, and Get Prod_id --Seed_Package_Names_Versions ( ProdName, sProdVersion, nUserId, ProdId );-- Add Product To OS --OPEN prod_cur;FETCH prod_cur INTO prod_rec;IF prod_cur%NOTFOUND THENpk_Product.Add_Product ( ProdId, nOsId, nUserId );-- Add comments if requiredIF (NOT sComments IS NULL) THENUPDATE OS_CONTENTS osc SETosc.PRODUCT_COMMENTS = sCommentsWHERE osc.OS_ID = nOsIdAND osc.PROD_ID = ProdId;END IF;END IF;CLOSE prod_cur;END Add_Product_Wrap;/*--------------------------------------------------------------------------------------------------*/PROCEDURE Set_Base_Conf ( nOsId IN OS_BASE_ENV.OS_ID%TYPE,nBaseEnvId IN OS_BASE_ENV.BASE_ENV_ID%TYPE ) ISBEGIN/*--------------- Business Rules Here -------------------*//*-------------------------------------------------------*/-- Delete current Base Conf settingDELETE FROM OS_BASE_ENVWHERE OS_ID = nOsId;-- Set new Base ConfIF NOT nBaseEnvId IS NULLTHENINSERT INTO OS_BASE_ENV ( OS_ID, BASE_ENV_ID )VALUES ( nOsId, nBaseEnvId );END IF;END Set_Base_Conf;/*--------------------------------------------------------------------------------------------------*/PROCEDURE Shift_Product_SeqNum ( nProdId IN OS_CONTENTS.PROD_ID%TYPE,nOsId IN OS_CONTENTS.OS_ID%TYPE,nToSeqNum IN OS_CONTENTS.SEQ_NUM%TYPE ) IScurrSeqNum NUMBER;FromSeqNum NUMBER;BEGIN-- Get Current seq_numSELECT osc.SEQ_NUM INTO currSeqNumFROM OS_CONTENTS oscWHERE osc.OS_ID = nOsIdAND osc.PROD_ID = nProdId;IF currSeqNum > nToSeqNumTHENFromSeqNum := nToSeqNum;-- Shift others UpUPDATE OS_CONTENTS osc SETosc.SEQ_NUM = osc.SEQ_NUM + 1WHERE osc.OS_ID = nOsIdAND osc.PROD_ID != nProdIdAND osc.SEQ_NUM BETWEEN FromSeqNum AND currSeqNum - 1;ELSIF currSeqNum < nToSeqNumTHENFromSeqNum := currSeqNum + 1;-- Shift others DownUPDATE OS_CONTENTS osc SETosc.SEQ_NUM = osc.SEQ_NUM - 1WHERE osc.OS_ID = nOsIdAND osc.PROD_ID != nProdIdAND osc.SEQ_NUM BETWEEN FromSeqNum AND nToSeqNum;END IF;-- Move Product to new seq_numUPDATE OS_CONTENTS osc SETosc.SEQ_NUM = nToSeqNumWHERE osc.OS_ID = nOsIdAND osc.PROD_ID = nProdId;END Shift_Product_SeqNum;/*--------------------------------------------------------------------------------------------------*/PROCEDURE Shift_Bulk_Products_SeqNum ( sProdIdList IN VARCHAR2,nOsId IN OS_CONTENTS.OS_ID%TYPE,cDirection IN CHAR ) ISMinSeqNum NUMBER := 1;FirstSeqNum NUMBER;LastSeqNum NUMBER;MaxSeqNum NUMBER;CURSOR osc_down_cur ISSELECT osc.prod_id, osc.seq_numFROM OS_CONTENTS oscWHERE osc.OS_ID = nOsIdAND osc.PROD_ID IN (SELECT *FROM THE ( SELECT CAST( IN_LIST_NUMBER( sProdIdList ) AS DEPMGR_NUMBER_TAB_t ) FROM dual ))ORDER BY osc.seq_num ASC;osc_down_rec osc_down_cur%ROWTYPE;CURSOR osc_up_cur ISSELECT osc.prod_id, osc.seq_numFROM OS_CONTENTS oscWHERE osc.OS_ID = nOsIdAND osc.PROD_ID IN (SELECT *FROM THE ( SELECT CAST( IN_LIST_NUMBER( sProdIdList ) AS DEPMGR_NUMBER_TAB_t ) FROM dual ))ORDER BY osc.seq_num DESC;osc_up_rec osc_up_cur%ROWTYPE;BEGIN/*--------------- Business Rules Here -------------------*/IF sProdIdList IS NULLTHENRAISE_APPLICATION_ERROR (-20000, 'Please select one or more Products.');ELSIF (nOsId IS NULL) OR (cDirection IS NULL)THENRAISE_APPLICATION_ERROR (-20000, 'Missing some parameters! nOsId= '|| nOsId ||', cDirection='|| cDirection );END IF;/*-------------------------------------------------------*/-- Get First seq_numSELECT MIN( osc.SEQ_NUM ) INTO FirstSeqNumFROM OS_CONTENTS oscWHERE osc.OS_ID = nOsIdAND osc.PROD_ID IN (SELECT *FROM THE ( SELECT CAST( IN_LIST_NUMBER( sProdIdList ) AS DEPMGR_NUMBER_TAB_t ) FROM dual ));-- Get Last seq_numSELECT MAX( osc.SEQ_NUM ) INTO LastSeqNumFROM OS_CONTENTS oscWHERE osc.OS_ID = nOsIdAND osc.PROD_ID IN (SELECT *FROM THE ( SELECT CAST( IN_LIST_NUMBER( sProdIdList ) AS DEPMGR_NUMBER_TAB_t ) FROM dual ));-- Get Max seq_numSELECT MAX( osc.SEQ_NUM ) INTO MaxSeqNumFROM OS_CONTENTS oscWHERE osc.OS_ID = nOsId;IF cDirection = '+'THEN-- Shift UPIF LastSeqNum < MaxSeqNumTHEN/* Can only shift up if maxseqnum is not reached */OPEN osc_up_cur;FETCH osc_up_cur INTO osc_up_rec;WHILE osc_up_cur%FOUNDLOOPShift_Product_SeqNum ( osc_up_rec.PROD_ID, nOsId, osc_up_rec.SEQ_NUM + 1 );FETCH osc_up_cur INTO osc_up_rec;END LOOP;CLOSE osc_up_cur;END IF;ELSIF cDirection = '-'THEN-- Shift DOWNIF FirstSeqNum > MinSeqNumTHEN/* Can only shift down if minseqnum is not reached */OPEN osc_down_cur;FETCH osc_down_cur INTO osc_down_rec;WHILE osc_down_cur%FOUNDLOOPShift_Product_SeqNum ( osc_down_rec.PROD_ID, nOsId, osc_down_rec.SEQ_NUM - 1 );FETCH osc_down_cur INTO osc_down_rec;END LOOP;CLOSE osc_down_cur;END IF;END IF;END Shift_Bulk_Products_SeqNum;/*--------------------------------------------------------------------------------------------------*/PROCEDURE Destroy_Os ( nOsIdCollection IN DEPMGR_NUMBER_TAB_t, nUserId IN NUMBER ) ISnIdCollector DEPMGR_NUMBER_TAB_t := DEPMGR_NUMBER_TAB_t();NodeId NUMBER;OsName VARCHAR2(4000);BEGIN/*--------------- Business Rules Here -------------------*//*-------------------------------------------------------*/IF (nOsIdCollection.COUNT > 0) THEN--Log Action TrailFOR i IN 1..nOsIdCollection.COUNTLOOP-- Get node_idSELECT os.NODE_ID INTO NodeIdFROM OPERATING_SYSTEMS osWHERE os.OS_ID = nOsIdCollection(i);--Get os_nameSELECT os.OS_NAME INTO OsNameFROM OPERATING_SYSTEMS osWHERE os.OS_ID = nOsIdCollection(i);/* Log BOM_TRAIL */pk_Bom.Log_Node_Trail ( NodeId, 'Deleted Operating System: '||OsName, nUserId );END LOOP;-- Destroy PROCESSES_CONFIGDELETEFROM PROCESSES_CONFIG pcWHERE pc.OS_ID IN ( SELECT * FROM TABLE ( CAST ( nOsIdCollection AS DEPMGR_NUMBER_TAB_t ) ) );-- Destroy OS_CONTENTSDELETEFROM OS_CONTENTS oscWHERE osc.OS_ID IN ( SELECT * FROM TABLE ( CAST ( nOsIdCollection AS DEPMGR_NUMBER_TAB_t ) ) );-- Destroy OS_BASE_ENVDELETEFROM OS_BASE_ENV obeWHERE obe.OS_ID IN ( SELECT * FROM TABLE ( CAST ( nOsIdCollection AS DEPMGR_NUMBER_TAB_t ) ) );-- Destroy HIDE_PRODUCTSDELETEFROM HIDE_PRODUCTS hpWHERE hp.OS_ID IN ( SELECT * FROM TABLE ( CAST ( nOsIdCollection AS DEPMGR_NUMBER_TAB_t ) ) );-- Destroy OS_TRAILDELETEFROM OS_TRAIL otWHERE ot.OS_ID IN ( SELECT * FROM TABLE ( CAST ( nOsIdCollection AS DEPMGR_NUMBER_TAB_t ) ) );---- Finnaly destroy Operating System ----DELETEFROM OPERATING_SYSTEMS osWHERE os.OS_ID IN ( SELECT * FROM TABLE ( CAST ( nOsIdCollection AS DEPMGR_NUMBER_TAB_t ) ) );END IF;END Destroy_Os;/*--------------------------------------------------------------------------------------------------*/PROCEDURE Destroy_Os ( nOsIdList IN VARCHAR2, nUserId IN NUMBER ) ISnIdCollector DEPMGR_NUMBER_TAB_t := DEPMGR_NUMBER_TAB_t();BEGIN/*--------------- Business Rules Here -------------------*/IF nOsIdList IS NULLTHENRAISE_APPLICATION_ERROR (-20000, 'Please select one or more Operating Systems.');END IF;/*-------------------------------------------------------*/nIdCollector := IN_LIST_NUMBER ( nOsIdList );Destroy_Os ( nIdCollector, nUserId );END Destroy_Os;/*-------------------------------------------------------------------------------------------------------*/PROCEDURE add_package_process (nprocidlist IN VARCHAR2,npvid IN processes_config.prod_id%TYPE,nosid IN os_contents.os_id%TYPE,nuserid IN NUMBER)ISprocessname VARCHAR2 (4000);PkgName VARCHAR2 (4000);nPkgId NUMBER;nIdCollector DEPMGR_NUMBER_TAB_t := DEPMGR_NUMBER_TAB_t();/*Rupesh Release on 17/05/2006*/CURSOR proc_curISSELECT prc.proc_id, prc.proc_name, prc.proc_description,prc.run_as, prc.pkg_owner, prc.is_interfaceFROM processes prcWHERE prc.proc_id IN (SELECT *FROM THE(SELECT CAST(in_list_number (nprocidlist) AS DEPMGR_NUMBER_TAB_t)FROM DUAL));proc_rec proc_cur%ROWTYPE;BEGINBEGINSELECT PKG.PKG_NAME, PKG.PKG_ID INTO PkgName, nPkgIdFROM PACKAGE_VERSIONS PV, PACKAGES PKGWHERE PV.PV_ID = npvidAND PKG.PKG_ID = PV.PKG_ID;EXCEPTIONWHEN NO_DATA_FOUND THENRAISE_APPLICATION_ERROR (-20000, 'Select the Package Name and Version: ' );END;OPEN proc_cur;FETCH proc_curINTO proc_rec;WHILE proc_cur%FOUNDLOOPINSERT INTO processes_config(proc_id, prod_id, os_id, pkg_health_tag, cmd_interface, pkg_owner, is_interface, proc_description, pkg_id)VALUES (proc_rec.proc_id, npvid, nosid, proc_rec.proc_name, proc_rec.run_as, proc_rec.pkg_owner, proc_rec.is_interface, proc_rec.proc_description, nPkgId);SELECT prc.proc_nameINTO processnameFROM processes prcWHERE prc.proc_id = proc_rec.proc_id;-- Log Action --pk_Bom.Log_Os_Trail ( nosid,'Added process with health tag '|| processname ||' for product '|| PkgName,nUserId );FETCH proc_curINTO proc_rec;END LOOP;END;/*--------------------------------------------------------------------------------------------------*//*-------------------------------------------------------------------------------------------------------*/PROCEDURE add_packages_process (npvidlist IN VARCHAR2,nosid IN os_contents.os_id%TYPE,nuserid IN NUMBER)ISprocessname VARCHAR2 (4000);PkgName VARCHAR2 (4000);nIdCollector DEPMGR_NUMBER_TAB_t := DEPMGR_NUMBER_TAB_t();/*Rupesh Release on 17/05/2006*/CURSOR proc_curISSELECT prc.proc_id, prc.proc_name, prc.proc_description,prc.run_as, prc.pkg_owner, prc.is_interface, pp.PV_IDFROM processes prc, release_manager.package_processes ppWHERE pp.pv_id IN (SELECT *FROM THE(SELECT CAST(in_list_number (npvidlist) AS DEPMGR_NUMBER_TAB_t)FROM DUAL))AND pp.PROC_ID = prc.PROC_ID;proc_rec proc_cur%ROWTYPE;BEGINOPEN proc_cur;FETCH proc_curINTO proc_rec;WHILE proc_cur%FOUNDLOOPSELECT PKG.PKG_NAME INTO PkgNameFROM PACKAGE_VERSIONS PV, PACKAGES PKGWHERE PV.PV_ID = proc_rec.pv_idAND PKG.PKG_ID = PV.PKG_ID;INSERT INTO processes_config(proc_id, prod_id, os_id, pkg_health_tag, cmd_interface, pkg_owner, is_interface, proc_description)VALUES (proc_rec.proc_id, proc_rec.pv_id, nosid, proc_rec.proc_name, proc_rec.run_as, proc_rec.pkg_owner, proc_rec.is_interface, proc_rec.proc_description);SELECT prc.proc_nameINTO processnameFROM processes prcWHERE prc.proc_id = proc_rec.proc_id;-- Log Action --pk_Bom.Log_Os_Trail ( nosid,'Added process with health tag '|| processname ||' for product '|| PkgName,nUserId );FETCH proc_curINTO proc_rec;END LOOP;END;/*--------------------------------------------------------------------------------------------------*/END pk_Operating_System;//---------------------------------------------------------- DDL for Package Body PK_PRODUCT--------------------------------------------------------CREATE OR REPLACE PACKAGE BODY "PK_PRODUCT"IS/* ---------------------------------------------------------------------------Modified By: Rupesh SolankiDate: 20/03/2006--------------------------------------------------------------------------- *//*--------------------------------------------------------------------------------------------------*/PROCEDURE new_product (sprodname IN PACKAGES.pkg_name%TYPE,sprodversion IN package_versions.pkg_version%TYPE,nuserid IN NUMBER,outprodid OUT package_versions.pv_id%TYPE)ISBEGIN/*--------------- Business Rules Here -------------------*//*-------------------------------------------------------*/-- Call Release Manager to add new productseed_package_names_versions (sprodname,sprodversion,nuserid,outprodid);END new_product;/*--------------------------------------------------------------------------------------------------*/PROCEDURE add_product (sprodidlist IN VARCHAR2,nosid IN os_contents.os_id%TYPE,nuserid IN NUMBER)ISnextseqnum NUMBER;pkgname VARCHAR2 (100);pkgversion VARCHAR2 (100);nodename VARCHAR2 (4000);CURSOR prod_curISSELECT qry.pv_id AS prod_id, qry.pv_description, qry.comments,qry.is_patchFROM (/* Products */SELECT pv.pv_id, TO_NUMBER (NULL) AS install_order,TO_NUMBER (NULL) AS pkg_id, pv.pv_description,pv.comments, pv.is_patchFROM package_versions pvWHERE pv.is_patch IS NULLAND pv.pv_id IN (SELECT *FROM THE(SELECT CAST(in_list_number (sprodidlist) AS depmgr_number_tab_t)FROM DUAL))UNION ALL/* Patches */SELECT pv.pv_id, pp.install_order, pv.pkg_id,pv.pv_description, pv.comments, pv.is_patchFROM package_patches pp, package_versions pvWHERE pp.patch_id = pv.pv_idAND pp.patch_id IN (SELECT *FROM THE(SELECT CAST(in_list_number (sprodidlist) AS depmgr_number_tab_t)FROM DUAL))) qryORDER BY qry.pkg_id, qry.install_order;prod_rec prod_cur%ROWTYPE;BEGIN/*--------------- Business Rules Here -------------------*//*-------------------------------------------------------*/-- Get seq_numSELECT MAX (osc.seq_num)INTO nextseqnumFROM os_contents oscWHERE osc.os_id = nosid;-- Set NextSeqNum to 0 if no existing products are foundIF nextseqnum IS NULLTHENnextseqnum := 1;ELSEnextseqnum := nextseqnum + 1;END IF;-- Insert into OS ContentsOPEN prod_cur;FETCH prod_curINTO prod_rec;WHILE prod_cur%FOUNDLOOPIF (prod_rec.is_patch IS NULL)THENINSERT INTO os_contents(os_id, seq_num, prod_id,product_comments)VALUES (nosid, nextseqnum, prod_rec.prod_id,prod_rec.pv_description);ELSEINSERT INTO os_contents(os_id, seq_num, prod_id,product_comments)VALUES (nosid, nextseqnum, prod_rec.prod_id,prod_rec.comments);END IF;SELECT pkg.pkg_nameINTO pkgnameFROM PACKAGES pkg, package_versions pvWHERE pv.pv_id = prod_rec.prod_id AND pkg.pkg_id = pv.pkg_id;SELECT pkg_versionINTO pkgversionFROM package_versions pvWHERE pv.pv_id = prod_rec.prod_id;SELECT nn.node_nameINTO nodenameFROM network_nodes nn, operating_systems osWHERE nn.node_id = os.node_id AND os.os_id = nosid;-- Log Action --pk_bom.log_os_trail (nosid,'Added Product: '|| pkgname|| ' (Version: '|| pkgversion|| ') with Install Order: '|| nextseqnum|| ' on Node: '|| nodename,nuserid);nextseqnum := nextseqnum + 1;FETCH prod_curINTO prod_rec;END LOOP;CLOSE prod_cur;END add_product;/*--------------------------------------------------------------------------------------------------*/PROCEDURE hide_product (sprodidlist IN VARCHAR2,nosid IN os_contents.os_id%TYPE,nuserid IN NUMBER)ISBEGIN/*--------------- Business Rules Here -------------------*//*-------------------------------------------------------*/INSERT INTO hide_products(os_id, prod_id)VALUES (nosid, sprodidlist);END hide_product;/*--------------------------------------------------------------------------------------------------*/PROCEDURE unhide_product (sprodidlist IN NUMBER,nosid IN os_contents.os_id%TYPE,nuserid IN NUMBER)ISBEGIN/*--------------- Business Rules Here -------------------*//*-------------------------------------------------------*/DELETE FROM hide_productsWHERE prod_id = sprodidlist AND os_id = nosid;END unhide_product;/*--------------------------------------------------------------------------------------------------*/PROCEDURE update_process (nprocid IN processes_config.proc_id%TYPE,nprodid IN processes_config.prod_id%TYPE,nisinterface IN processes_config.is_interface%TYPE,nosid IN processes_config.os_id%TYPE,ncmdinterface IN processes_config.cmd_interface%TYPE,npkgowner IN processes_config.pkg_owner%TYPE,spkghealthtag IN processes_config.pkg_health_tag%TYPE,nproduct IN processes_config.prod_id%TYPE)ISBEGINUPDATE processes_configSET cmd_interface = ncmdinterface,pkg_owner = npkgowner,is_interface = nisinterface,pkg_health_tag = spkghealthtag,prod_id = nproductWHERE prod_id = nprodid AND os_id = nosid AND proc_id = nprocid;END update_process;/*--------------------------------------------------------------------------------------------------*/PROCEDURE update_product (noldprodid IN os_contents.prod_id%TYPE,snewprodidversion IN VARCHAR2,nosid IN os_contents.os_id%TYPE,nseqnum IN os_contents.seq_num%TYPE,scomments IN os_contents.product_comments%TYPE,ssunoselectronicname IN PACKAGES.sunos_electronic_name%TYPE,swinelectronicname IN PACKAGES.win_electronic_name%TYPE,sisnodespec IN os_contents.is_node_spec%TYPE,snotxml IN os_contents.not_xml%TYPE,spatchelectronicname IN package_versions.patch_electronic_name%TYPE,nuserid IN NUMBER,outprodid OUT package_versions.pv_id%TYPE)ISnnewprodid os_contents.prod_id%TYPE;sprodname PACKAGES.pkg_name%TYPE;CURSOR curprodISSELECT pv.pv_idFROM package_versions pvWHERE pv.pkg_id IN (SELECT pv.pkg_idFROM package_versions pvWHERE pv.pv_id = noldprodid)AND pv.pkg_version = snewprodidversion;recprod curprod%ROWTYPE;BEGIN/*--------------- Business Rules Here -------------------*/IF (noldprodid IS NULL)OR (snewprodidversion IS NULL)OR (nosid IS NULL)OR (nseqnum IS NULL)THENraise_application_error (-20000,'Missing some parameters! nOldProdId= '|| noldprodid|| ', sNewProdIdVersion= '|| snewprodidversion|| ', nOsId= '|| nosid|| ', nSeqNum= '|| nseqnum);END IF;/*-------------------------------------------------------*/-- Get new prod_idOPEN curprod;FETCH curprodINTO recprod;-- Get the Package NameSELECT pkg.pkg_nameINTO sprodnameFROM PACKAGES pkg, package_versions pvWHERE pkg.pkg_id = pv.pkg_id AND pv.pv_id = noldprodid;IF curprod%FOUNDTHENnnewprodid := recprod.pv_id;ELSE-- Call Release Manager to add new productseed_package_names_versions (sprodname,snewprodidversion,nuserid,outprodid);nnewprodid := outprodid;/*RAISE_APPLICATION_ERROR (-20000, 'Version '|| sNewProdIdVersion ||' does not exist.');*/END IF;CLOSE curprod;-- Update Product DetailsUPDATE os_contentsSET prod_id = nnewprodid,product_comments = scomments,not_xml = snotxml,is_node_spec = sisnodespecWHERE os_id = nosid AND prod_id = noldprodid;--Update Package Electronic Name Details In Packages TableUPDATE PACKAGESSET sunos_electronic_name = ssunoselectronicname,win_electronic_name = swinelectronicnameWHERE pkg_id = (SELECT pkg_idFROM package_versionsWHERE package_versions.pv_id = nnewprodid);--Update Patch Electronic Name In Package Versions TableUPDATE package_versionsSET patch_electronic_name = spatchelectronicnameWHERE pv_id = nnewprodid;-- Update seq_numpk_operating_system.shift_product_seqnum (nnewprodid, nosid, nseqnum);END update_product;/*--------------------------------------------------------------------------------------------------*/PROCEDURE replace_product (noldprodid IN os_contents.prod_id%TYPE,nnewprodid IN os_contents.prod_id%TYPE,nosid IN os_contents.os_id%TYPE,nuserid IN NUMBER)ISoldversion VARCHAR2 (100);newversion VARCHAR2 (100);pkgname VARCHAR2 (100);nodename VARCHAR2 (4000);BEGIN/*--------------- Business Rules Here -------------------*//*-------------------------------------------------------*/UPDATE os_contentsSET prod_id = nnewprodidWHERE prod_id = noldprodid AND os_id = nosid;SELECT pkg_versionINTO oldversionFROM package_versionsWHERE pv_id = noldprodid;SELECT pkg_versionINTO newversionFROM package_versionsWHERE pv_id = nnewprodid;SELECT pkg.pkg_nameINTO pkgnameFROM PACKAGES pkg, package_versions pvWHERE pv.pv_id = nnewprodid AND pkg.pkg_id = pv.pkg_id;SELECT nn.node_nameINTO nodenameFROM network_nodes nn, operating_systems osWHERE nn.node_id = os.node_id AND os.os_id = nosid;-- Log Action --pk_bom.log_os_trail (nosid,'Updated Product: '|| pkgname|| ' from Version: '|| oldversion|| ' to '|| newversion|| ' on Node: '|| nodename,nuserid);END replace_product;/*--------------------------------------------------------------------------------------------------*/PROCEDURE remove_process (sprocname IN processes_config.pkg_health_tag%TYPE,sprodidlist IN VARCHAR2,nosid IN os_contents.os_id%TYPE,scmdinterface IN processes_config.cmd_interface%TYPE,spkgowner IN processes_config.pkg_owner%TYPE,sinterface IN processes_config.is_interface%TYPE,nuserid IN NUMBER)ISprocessname VARCHAR2 (4000);nodename VARCHAR2 (4000);BEGIN-- Log Action --pk_bom.log_os_trail (nosid,'Deleted Process: ' || sprocname || ' From Node. ',nuserid);/*Select the Correct Details - Begin*/IF scmdinterface IS NOT NULLTHENIF sinterface IS NOT NULLTHENIF spkgowner IS NOT NULLTHENDELETE FROM processes_configWHERE os_id = nosidAND prod_id = sprodidlistAND pkg_health_tag = sprocnameAND cmd_interface = scmdinterfaceAND pkg_owner = spkgownerAND is_interface = sinterface;ELSE/*sPkgOwner IS NULL, sInterface IS NOT NULL, sCmdInterface IS NOT NULL*/DELETE FROM processes_configWHERE os_id = nosidAND prod_id = sprodidlistAND pkg_health_tag = sprocnameAND cmd_interface = scmdinterfaceAND pkg_owner IS NULLAND is_interface = sinterface;END IF;ELSE /* sInterface IS NULL, sCmdInterface IS NOT NULL*/IF spkgowner IS NOT NULLTHENDELETE FROM processes_configWHERE os_id = nosidAND prod_id = sprodidlistAND pkg_health_tag = sprocnameAND cmd_interface = scmdinterfaceAND pkg_owner = spkgownerAND is_interface IS NULL;ELSE/* sPkqOwner IS NULL, sInterface IS NULL, sCmdInterface IS NOT NULL*/DELETE FROM processes_configWHERE os_id = nosidAND prod_id = sprodidlistAND pkg_health_tag = sprocnameAND cmd_interface = scmdinterfaceAND pkg_owner IS NULLAND is_interface IS NULL;END IF;END IF;ELSE /* sCmdInterface IS NULL*/IF sinterface IS NOT NULLTHENIF spkgowner IS NOT NULLTHENDELETE FROM processes_configWHERE os_id = nosidAND prod_id = sprodidlistAND pkg_health_tag = sprocnameAND cmd_interface IS NULLAND pkg_owner = spkgownerAND is_interface = sinterface;ELSE/* sPkgOwner is NULL, sInterface IS NOT NULL, sCmdInterface IS NULL */DELETE FROM processes_configWHERE os_id = nosidAND prod_id = sprodidlistAND pkg_health_tag = sprocnameAND cmd_interface IS NULLAND pkg_owner IS NULLAND is_interface = sinterface;END IF;ELSE /* sInterface IS NULL, sCmdInterface IS NULL*/IF spkgowner IS NOT NULLTHENDELETE FROM processes_configWHERE os_id = nosidAND prod_id = sprodidlistAND pkg_health_tag = sprocnameAND cmd_interface IS NULLAND pkg_owner = spkgownerAND is_interface IS NULL;ELSE/* sPkgOwner IS NULL, sInterface IS NULL, sCmdInterface IS NULL*/DELETE FROM processes_configWHERE os_id = nosidAND prod_id = sprodidlistAND pkg_health_tag = sprocnameAND cmd_interface IS NULLAND pkg_owner IS NULLAND is_interface IS NULL;END IF;END IF;END IF;/*Select the Correct Details - End*/END remove_process;/*--------------------------------------------------------------------------------------------------*/PROCEDURE remove_products (sprodidlist IN VARCHAR2,nosid IN os_contents.os_id%TYPE,nuserid IN NUMBER)ISpkgname VARCHAR2 (100);pkgversion VARCHAR2 (100);nextseqnum NUMBER := 1;nidcollector depmgr_number_tab_t := depmgr_number_tab_t ();nodename VARCHAR2 (4000);CURSOR prod_curISSELECT osc.prod_id, osc.seq_numFROM os_contents oscWHERE osc.os_id = nosidORDER BY osc.seq_num;prod_rec prod_cur%ROWTYPE;BEGIN/*--------------- Business Rules Here -------------------*/IF sprodidlist IS NULLTHENraise_application_error (-20000,'Please select one or more Products.');END IF;/*-------------------------------------------------------*/SELECT nn.node_nameINTO nodenameFROM network_nodes nn, operating_systems osWHERE nn.node_id = os.node_id AND os.os_id = nosid;-- Remove ProductsDELETE FROM os_contentsWHERE os_id = nosidAND prod_id IN (SELECT *FROM THE(SELECT CAST(in_list_number (sprodidlist) AS depmgr_number_tab_t)FROM DUAL));-- Remove ProcessesDELETE FROM processes_configWHERE os_id = nosidAND pkg_id IN (SELECT pkg_idFROM package_versionsWHERE pv_id IN (SELECT *FROM THE(SELECT CAST(in_list_number(sprodidlist) AS depmgr_number_tab_t)FROM DUAL)));-- Update Seq NumbersOPEN prod_cur;FETCH prod_curINTO prod_rec;WHILE prod_cur%FOUNDLOOPUPDATE os_contentsSET seq_num = nextseqnumWHERE os_id = nosid AND prod_id = prod_rec.prod_id;nextseqnum := nextseqnum + 1;FETCH prod_curINTO prod_rec;END LOOP;CLOSE prod_cur;nidcollector := in_list_number (sprodidlist);FOR i IN 1 .. nidcollector.COUNTLOOPSELECT pkg.pkg_nameINTO pkgnameFROM PACKAGES pkg, package_versions pvWHERE pv.pv_id = nidcollector (i) AND pkg.pkg_id = pv.pkg_id;SELECT pkg_versionINTO pkgversionFROM package_versions pvWHERE pv.pv_id = nidcollector (i);-- Log Action --pk_bom.log_os_trail (nosid,'Deleted Product: '|| pkgname|| ' (Version: '|| pkgversion|| ') from Node: '|| nodename,nuserid);END LOOP;END remove_products;/*--------------------------------------------------------------------------------------------------*/PROCEDURE add_products_nodespec (sprodidlist IN VARCHAR2,nosid IN os_contents.os_id%TYPE,nuserid IN NUMBER)ISnextseqnum NUMBER := 1;CURSOR prod_curISSELECT osc.prod_id, osc.seq_numFROM os_contents oscWHERE osc.os_id = nosidORDER BY osc.seq_num;prod_rec prod_cur%ROWTYPE;BEGIN/*--------------- Business Rules Here -------------------*/IF sprodidlist IS NULLTHENraise_application_error (-20000,'Please select one or more Products.');END IF;/*-------------------------------------------------------*/UPDATE os_contentsSET is_node_spec = ''WHERE os_id = nosid;-- Set Nodespec OnUPDATE os_contentsSET is_node_spec = 'on'WHERE os_id = nosidAND prod_id IN (SELECT *FROM THE(SELECT CAST(in_list_number (sprodidlist) AS depmgr_number_tab_t)FROM DUAL));END add_products_nodespec;/*--------------------------------------------------------------------------------------------------*/PROCEDURE accept_reject (nprodid IN product_details.prod_id%TYPE,nbomid IN boms.bom_id%TYPE,scomments IN rejection_trail.comments%TYPE DEFAULT NULL,nuserid IN rejection_trail.user_id%TYPE,nosid IN NUMBER)ISisrejected product_details.is_rejected%TYPE;pkgname VARCHAR2 (100);pkgversion VARCHAR2 (100);nodename VARCHAR2 (4000);CURSOR curprodISSELECT pd.*FROM product_details pdWHERE pd.prod_id = nprodid;recprod curprod%ROWTYPE;BEGIN/*--------------- Business Rules Here -------------------*/IF (nprodid IS NULL)THENraise_application_error (-20000,'Please select at least one product.');END IF;/*-------------------------------------------------------*/-- Set new Accept-Reject stateisrejected := 'Y';SELECT nn.node_nameINTO nodenameFROM network_nodes nn, operating_systems osWHERE nn.node_id = os.node_id AND os.os_id = nosid;OPEN curprod;FETCH curprodINTO recprod;IF curprod%FOUNDTHEN/* Product Details found */SELECT pkg.pkg_nameINTO pkgnameFROM PACKAGES pkg, package_versions pvWHERE pv.pv_id = nprodid AND pkg.pkg_id = pv.pkg_id;SELECT pkg_versionINTO pkgversionFROM package_versions pvWHERE pv.pv_id = nprodid;-- Set new Accept-Reject stateIF recprod.is_rejected = 'Y'THENisrejected := 'N';-- Log Action --pk_bom.log_os_trail (nosid,'Accepted Product: '|| pkgname|| ' '|| pkgversion|| ' on Node: '|| nodename,nuserid);ELSEisrejected := 'Y';-- Log Action --pk_bom.log_os_trail (nosid,'Rejected Product: '|| pkgname|| ' '|| pkgversion|| ' on Node: '|| nodename,nuserid);END IF;-- Toggle Accept-Reject StateUPDATE product_details pdSET pd.is_rejected = isrejectedWHERE pd.prod_id = nprodid;ELSE/* Product Details NOT found */-- Toggle Accept-Reject StateINSERT INTO product_details(prod_id, is_rejected)VALUES (nprodid, isrejected);END IF;CLOSE curprod;-- Reflect changes to the BOM level/* Log Rejection Trail */pk_utils.log_rejection_trail(get_system_value ('enumENTITY_TYPE_PRODUCT'),nprodid,isrejected,nuserid,scomments);END accept_reject;/*--------------------------------------------------------------------------------------------------*/PROCEDURE accept_reject_list (nprodidlist IN VARCHAR2,nbomid IN boms.bom_id%TYPE,scomments IN rejection_trail.comments%TYPE DEFAULT NULL,nuserid IN rejection_trail.user_id%TYPE,nosid IN NUMBER)ISnidcollector depmgr_number_tab_t := depmgr_number_tab_t ();BEGIN/*--------------- Business Rules Here -------------------*/IF (nprodidlist IS NULL)THENraise_application_error (-20000,'Please select at least one product.');END IF;/*-------------------------------------------------------*/nidcollector := in_list_number (nprodidlist);FOR i IN 1 .. nidcollector.COUNTLOOPaccept_reject (nidcollector (i), nbomid, scomments, nuserid, nosid);END LOOP;END accept_reject_list;/*--------------------------------------------------------------------------------------------------*/PROCEDURE accept_reject_comments (nprodid IN product_details.prod_id%TYPE,nrejectseq IN rejection_trail.reject_seq%TYPE,scomments IN rejection_trail.comments%TYPE DEFAULT NULL,nuserid IN rejection_trail.user_id%TYPE)ISisrejected rejection_trail.is_rejected%TYPE;BEGIN/*--------------- Business Rules Here -------------------*/IF (nprodid IS NULL)THENraise_application_error (-20000,'Missing some parameters! nProdId= '|| nprodid);END IF;/*-------------------------------------------------------*/IF nrejectseq IS NULLTHEN/* CREATE New Note */-- Get last IsRejected stateSELECT rt.is_rejectedINTO isrejectedFROM rejection_trail rtWHERE rt.entity_id = nprodidAND rt.enum_entity_type =get_system_value ('enumENTITY_TYPE_PRODUCT')AND ROWNUM = 1ORDER BY rt.reject_seq;/* Log Rejection Trail */pk_utils.log_rejection_trail(get_system_value ('enumENTITY_TYPE_PRODUCT'),nprodid,isrejected,nuserid,scomments);ELSE/* EDIT Note */UPDATE rejection_trail rtSET rt.comments = scomments,rt.user_id = nuserid,rt.date_time_stamp = get_datetime ()WHERE rt.enum_entity_type =get_system_value ('enumENTITY_TYPE_PRODUCT')AND rt.entity_id = nprodidAND rt.reject_seq = nrejectseq;END IF;END accept_reject_comments;/*--------------------------------------------------------------------------------------------------*/PROCEDURE sort_products (sprodidorder IN VARCHAR2,nosid IN os_contents.os_id%TYPE,nuserid IN NUMBER)ISnidcollector depmgr_number_tab_t := depmgr_number_tab_t ();seqnum NUMBER;BEGIN/*--------------- Business Rules Here -------------------*/-- Check for parametersIF (sprodidorder IS NULL) OR (nosid IS NULL)THENraise_application_error (-20000,'Missing some parameters! nOsId='|| nosid|| ' sProdIdOrder='|| sprodidorder);END IF;/*-------------------------------------------------------*/nidcollector := in_list_number (sprodidorder);-- Set start sequence numberseqnum := 1;FOR i IN 1 .. nidcollector.COUNTLOOP-- Update new sequence numberUPDATE os_contents oscSET osc.seq_num = seqnumWHERE osc.os_id = nosid AND osc.prod_id = nidcollector (i);seqnum := seqnum + 1;END LOOP;END sort_products;/*--------------------------------------------------------------------------------------------------*/PROCEDURE remove_obsolete_patches (nosid IN os_contents.os_id%TYPE,nuserid IN NUMBER)ISnextseqnum NUMBER := 1;CURSOR prod_curISSELECT osc.prod_id, osc.seq_numFROM os_contents oscWHERE osc.os_id = nosidORDER BY osc.seq_num;prod_rec prod_cur%ROWTYPE;CURSOR log_obsolete_curISSELECT osc.prod_id, pv.pkg_versionFROM os_contents osc, package_versions pvWHERE pv.pv_id = osc.prod_idAND pv.is_obsolete = 'Y'AND osc.os_id = nosid;log_obsolete_rec log_obsolete_cur%ROWTYPE;BEGIN-- Log Obsolete PatchesOPEN log_obsolete_cur;FETCH log_obsolete_curINTO log_obsolete_rec;WHILE log_obsolete_cur%FOUNDLOOPpk_bom.log_os_trail (nosid,'Removed Obsolete Patch: '|| log_obsolete_rec.pkg_version,nuserid);FETCH log_obsolete_curINTO log_obsolete_rec;END LOOP;CLOSE log_obsolete_cur;-- Delete Obsolete Patches in the OS.DELETE FROM os_contentsWHERE os_id = nosidAND prod_id IN (SELECT osc.prod_idFROM os_contents osc, package_versions pvWHERE pv.pv_id = osc.prod_idAND pv.is_obsolete = 'Y'AND osc.os_id = nosid);-- Update Seq NumbersOPEN prod_cur;FETCH prod_curINTO prod_rec;WHILE prod_cur%FOUNDLOOPUPDATE os_contentsSET seq_num = nextseqnumWHERE os_id = nosid AND prod_id = prod_rec.prod_id;nextseqnum := nextseqnum + 1;FETCH prod_curINTO prod_rec;END LOOP;CLOSE prod_cur;END remove_obsolete_patches;/*--------------------------------------------------------------------------------------------------*/FUNCTION get_os_id (pvid IN NUMBER)RETURN NUMBERISreturnvalue NUMBER;BEGINSELECT osc.os_idINTO returnvalueFROM os_contents oscWHERE osc.prod_id = pvid;RETURN returnvalue;END;/*--------------------------------------------------------------------------------------------------*/FUNCTION get_node_id (osid IN NUMBER)RETURN NUMBERISreturnvalue NUMBER;BEGINSELECT os.node_idINTO returnvalueFROM operating_systems osWHERE os.os_id = osid;RETURN returnvalue;END;/*-------------------------------------------------------------------------------------------------------*/PROCEDURE add_product_note (nprodid IN NUMBER,snotes IN VARCHAR2,nuserid IN NUMBER)ISBEGININSERT INTO product_notesVALUES (nprodid, snotes);/* log Product_Trail*/pk_product.log_product_trail (nprodid, snotes, nuserid);END add_product_note;/*-------------------------------------------------------------------------------------------------------*/PROCEDURE edit_product_note (nprodid IN NUMBER,snotes IN VARCHAR2,nuserid IN NUMBER)ISBEGINUPDATE product_notesSET notes = snotesWHERE prod_id = nprodid;/* log Product_Trail*/pk_product.log_product_trail (nprodid, snotes, nuserid);END edit_product_note;/*--------------------------------------------------------------------------------------------------*/PROCEDURE log_product_trail (nprodid IN product_trail.prod_id%TYPE,saction IN product_trail.trail_action%TYPE,nuserid IN product_trail.user_id%TYPE)ISBEGIN/*--------------- Business Rules Here -------------------*/IF (nprodid IS NULL) OR (saction IS NULL) OR (nuserid IS NULL)THENraise_application_error (-20000,'Missing some parameters! nProdId= '|| nprodid|| ', sAction='|| saction|| ', nUserId='|| nuserid);END IF;/*-------------------------------------------------------*/INSERT INTO product_trail(prod_id, trail_action, user_id, date_time_stamp)VALUES (nprodid, saction, nuserid, get_datetime ());END log_product_trail;/*--------------------------------------------------------------------------------------------------*/END pk_product;//---------------------------------------------------------- DDL for Package Body PK_PRODUCTION--------------------------------------------------------CREATE OR REPLACE PACKAGE BODY "PK_PRODUCTION"IS/* ---------------------------------------------------------------------------Version: 1.0.0--------------------------------------------------------------------------- *//*--------------------------------------------------------------------------------------------------*/PROCEDURE Release_Authorisation_Tester ( nPvId IN NUMBER,nBomId IN NUMBER,nTesterId IN NUMBER,sTesterComments IN VARCHAR2 ) ISBEGIN/*--------------- Business Rules Here -------------------*//*-------------------------------------------------------*/-- Insert Into Release Authorisation Table --INSERT INTO RELEASE_AUTHORISATION ( PV_ID, BOM_ID, TESTER_ID, TESTER_COMMENTS, TESTER_DATESTAMP )VALUES ( nPvId, nBomId, nTesterId, sTesterComments, GET_DATETIME() );END Release_Authorisation_Tester;/*--------------------------------------------------------------------------------------------------*//*--------------------------------------------------------------------------------------------------*/PROCEDURE Release_Authorisation_Manager ( nPvId IN NUMBER,nBomId IN NUMBER,nManagerId IN NUMBER,sManagerComments IN VARCHAR2 ) ISBEGIN/*--------------- Business Rules Here -------------------*//*-------------------------------------------------------*/-- Insert Into Release Authorisation Table --UPDATE RELEASE_AUTHORISATIONSET MANAGER_ID = nManagerId,MANAGER_COMMENTS = sManagerComments,MANAGER_DATESTAMP = GET_DATETIME(),IS_OFFICIAL = 'Y'WHERE PV_ID = nPvIdAND BOM_ID = nBomId;END Release_Authorisation_Manager;/*--------------------------------------------------------------------------------------------------*//*--------------------------------------------------------------------------------------------------*/PROCEDURE Log_Pkg_Trail ( nPkgId IN OS_TRAIL.OS_ID%TYPE,sAction IN OS_TRAIL.TRAIL_ACTION%TYPE,nUserId IN OS_TRAIL.USER_ID%TYPE ) ISBEGIN/*--------------- Business Rules Here -------------------*/IF (nPkgId IS NULL) OR (sAction IS NULL) OR (nUserId IS NULL)THENRAISE_APPLICATION_ERROR (-20000, 'Missing some parameters! nOsId= '|| nPkgId ||', sAction='|| sAction ||', nUserId='|| nUserId );END IF;/*-------------------------------------------------------*/INSERT INTO PACKAGE_TRAIL ( PKG_ID, TRAIL_ACTION, USER_ID, DATE_TIME_STAMP )VALUES ( nPkgId, sAction, nUserId, GET_DATETIME() );END Log_Pkg_Trail;/*--------------------------------------------------------------------------------------------------*/PROCEDURE Reject_Product ( nPvId IN NUMBER,nTesterId IN NUMBER,sTesterComments IN VARCHAR2 ) ISIsRejected PRODUCT_DETAILS.IS_REJECTED%TYPE;CURSOR curProd ISSELECT pd.*FROM PRODUCT_DETAILS pdWHERE pd.PROD_ID = nPvId;recProd curProd%ROWTYPE;BEGIN-- Set new Accept-Reject stateIsRejected := 'Y';OPEN curProd;FETCH curProd INTO recProd;IF curProd%FOUND THEN/* Product Details found */-- Toggle Accept-Reject StateUPDATE PRODUCT_DETAILS pd SETpd.IS_REJECTED = IsRejectedWHERE pd.PROD_ID = nPvId;ELSE/* Product Details NOT found */-- Toggle Accept-Reject StateINSERT INTO PRODUCT_DETAILS ( PROD_ID, IS_REJECTED )VALUES ( nPvId, IsRejected );END IF;CLOSE curProd;/* Log Rejection Trail */pk_Utils.Log_Rejection_Trail ( GET_SYSTEM_VALUE('enumENTITY_TYPE_PRODUCT'), nPvId, IsRejected, nTesterId, sTesterComments );END Reject_Product;/*--------------------------------------------------------------------------------------------------*/PROCEDURE Add_Production_Bom ( nBomId IN NUMBER,nBranchId IN NUMBER,nStateId IN NUMBER,nProjId IN NUMBER ) ISBEGIN--Update the boms table with the branch_idUPDATE BOMSSET RTAG_ID_FK = nBranchIdWHERE BOM_ID = nBomId;--Add the production bom into the production_bom tableINSERT INTO PRODUCTION_BOM (PROJ_ID, BOM_ID, STATE_ID)VALUES(nProjId, nBomId, nStateId);END Add_Production_Bom;/*--------------------------------------------------------------------------------------------------*/END pk_production;//---------------------------------------------------------- DDL for Package Body PK_PRODUCT_TEST--------------------------------------------------------CREATE OR REPLACE PACKAGE BODY "PK_PRODUCT_TEST"IS/* ---------------------------------------------------------------------------Modified By: Rupesh SolankiDate: 20/03/2006--------------------------------------------------------------------------- *//*--------------------------------------------------------------------------------------------------*/PROCEDURE New_Product ( sProdName IN PACKAGES.PKG_NAME%TYPE,sProdVersion IN PACKAGE_VERSIONS.PKG_VERSION%TYPE,nUserId IN NUMBER,outProdId OUT PACKAGE_VERSIONS.PV_ID%TYPE ) ISBEGIN/*--------------- Business Rules Here -------------------*//*-------------------------------------------------------*/-- Call Release Manager to add new productSeed_Package_Names_Versions ( sProdName, sProdVersion, nUserId, outProdId );END New_Product;/*--------------------------------------------------------------------------------------------------*/PROCEDURE Add_Product ( sProdIdList IN VARCHAR2,nOsId IN OS_CONTENTS.OS_ID%TYPE,nUserId IN NUMBER ) ISNextSeqNum NUMBER;PkgName VARCHAR2(100);PkgVersion VARCHAR2(100);NodeName VARCHAR2(4000);CURSOR prod_cur ISSELECT qry.PV_ID AS PROD_ID,qry.PV_DESCRIPTION,qry.COMMENTS,qry.IS_PATCHFROM (/* Products */SELECT pv.PV_ID, TO_NUMBER(NULL) AS INSTALL_ORDER, TO_NUMBER(NULL) AS PKG_ID, pv.PV_DESCRIPTION, pv.COMMENTS, pv.IS_PATCHFROM PACKAGE_VERSIONS pvWHERE pv.IS_PATCH IS NULLAND pv.PV_ID IN (SELECT *FROM THE ( SELECT CAST( IN_LIST_NUMBER( sProdIdList ) AS DEPMGR_NUMBER_TAB_t ) FROM dual ))UNION ALL/* Patches */SELECT pv.PV_ID, pp.INSTALL_ORDER, pv.PKG_ID, pv.PV_DESCRIPTION, pv.COMMENTS, pv.IS_PATCHFROM PACKAGE_PATCHES pp,PACKAGE_VERSIONS pvWHERE pp.PATCH_ID = pv.PV_IDAND pp.PATCH_ID IN (SELECT *FROM THE ( SELECT CAST( IN_LIST_NUMBER( sProdIdList ) AS DEPMGR_NUMBER_TAB_t ) FROM dual ))) qryORDER BY qry.PKG_ID, qry.INSTALL_ORDER;prod_rec prod_cur%ROWTYPE;BEGIN/*--------------- Business Rules Here -------------------*//*-------------------------------------------------------*/-- Get seq_numSELECT MAX( osc.SEQ_NUM ) INTO NextSeqNumFROM OS_CONTENTS oscWHERE osc.OS_ID = nOsId;-- Set NextSeqNum to 0 if no existing products are foundIF NextSeqNum IS NULL THENNextSeqNum := 1;ELSENextSeqNum := NextSeqNum + 1;END IF;-- Insert into OS ContentsOPEN prod_cur;FETCH prod_cur INTO prod_rec;WHILE prod_cur%FOUNDLOOPIF (prod_rec.IS_PATCH IS NULL) THENINSERT INTO OS_CONTENTS ( OS_ID, SEQ_NUM, PROD_ID, PRODUCT_COMMENTS )VALUES (nOsId,NextSeqNum,prod_rec.PROD_ID,prod_rec.PV_DESCRIPTION);ELSEINSERT INTO OS_CONTENTS ( OS_ID, SEQ_NUM, PROD_ID, PRODUCT_COMMENTS )VALUES (nOsId,NextSeqNum,prod_rec.PROD_ID,prod_rec.COMMENTS);END IF;SELECT PKG.PKG_NAME INTO PkgNameFROM PACKAGES PKG, PACKAGE_VERSIONS PVWHERE PV.PV_ID = prod_rec.PROD_IDAND PKG.PKG_ID = PV.PKG_ID;SELECT PKG_VERSION INTO PkgVersionFROM PACKAGE_VERSIONS PVWHERE PV.PV_ID = prod_rec.PROD_ID;SELECT NN.NODE_NAME INTO NodeNameFROM NETWORK_NODES NN, OPERATING_SYSTEMS OSWHERE NN.NODE_ID = OS.NODE_IDAND OS.OS_ID = nOsId;-- Log Action --pk_Bom.Log_Os_Trail ( nOsId,'Added Product: '||PkgName||' (Version: '||PkgVersion||') with Install Order: '||NextSeqNum||' on Node: '||NodeName,nUserId );NextSeqNum := NextSeqNum + 1;FETCH prod_cur INTO prod_rec;END LOOP;CLOSE prod_cur;END Add_Product;/*--------------------------------------------------------------------------------------------------*/PROCEDURE Hide_Product ( sProdIdList IN VARCHAR2,nOsId IN OS_CONTENTS.OS_ID%TYPE,nUserId IN NUMBER ) ISBEGIN/*--------------- Business Rules Here -------------------*//*-------------------------------------------------------*/INSERT INTO HIDE_PRODUCTS ( OS_ID, PROD_ID )VALUES (nOsId,sProdIdList);END Hide_Product;/*--------------------------------------------------------------------------------------------------*/PROCEDURE Unhide_Product ( sProdIdList IN NUMBER,nOsId IN OS_CONTENTS.OS_ID%TYPE,nUserId IN NUMBER ) ISBEGIN/*--------------- Business Rules Here -------------------*//*-------------------------------------------------------*/DELETE FROM HIDE_PRODUCTS WHEREPROD_ID = sProdIdListAND OS_ID = nOsId;END Unhide_Product;/*--------------------------------------------------------------------------------------------------*/PROCEDURE Update_Process (nProcId IN PROCESSES_CONFIG.PROC_ID%TYPE,nProdId IN PROCESSES_CONFIG.PROD_ID%TYPE,nIsInterface IN PROCESSES_CONFIG.IS_INTERFACE%TYPE,nOsId IN PROCESSES_CONFIG.OS_ID%TYPE,nCmdInterface IN PROCESSES_CONFIG.CMD_INTERFACE%TYPE,nPkgOwner IN PROCESSES_CONFIG.PKG_OWNER%TYPE,sPkgHealthTag IN PROCESSES_CONFIG.PKG_HEALTH_TAG%TYPE,nProduct IN PROCESSES_CONFIG.PROD_ID%TYPE) ISBEGINUPDATE PROCESSES_CONFIG SETCMD_INTERFACE = nCmdInterface,PKG_OWNER = nPkgOwner,IS_INTERFACE = nIsInterface,PKG_HEALTH_TAG = sPkgHealthTag,PROD_ID = nProductWHERE PROD_ID = nProdIdAND OS_ID = nOsIdAND PROC_ID = nProcId ;END Update_Process;/*--------------------------------------------------------------------------------------------------*/PROCEDURE Update_Product ( nOldProdId IN OS_CONTENTS.PROD_ID%TYPE,sNewProdIdVersion IN VARCHAR2,nOsId IN OS_CONTENTS.OS_ID%TYPE,nSeqNum IN OS_CONTENTS.SEQ_NUM%TYPE,sComments IN OS_CONTENTS.PRODUCT_COMMENTS%TYPE,sSunOSElectronicName IN PACKAGES.SUNOS_ELECTRONIC_NAME%TYPE,sWinElectronicName IN PACKAGES.WIN_ELECTRONIC_NAME%TYPE,sIsNodeSpec IN OS_CONTENTS.IS_NODE_SPEC%TYPE,sNotXML IN OS_CONTENTS.NOT_XML%TYPE,sPatchElectronicName IN PACKAGE_VERSIONS.PATCH_ELECTRONIC_NAME%TYPE,nUserId IN NUMBER,outProdId OUT PACKAGE_VERSIONS.PV_ID%TYPE ) ISnNewProdId OS_CONTENTS.PROD_ID%TYPE;sProdName PACKAGES.PKG_NAME%TYPE;CURSOR curProd ISSELECT pv.PV_IDFROM PACKAGE_VERSIONS pvWHERE pv.pkg_id IN ( SELECT pv.pkg_id FROM PACKAGE_VERSIONS pv WHERE pv.PV_ID = nOldProdId )AND pv.PKG_VERSION = sNewProdIdVersion;recProd curProd%ROWTYPE;BEGIN/*--------------- Business Rules Here -------------------*/IF (nOldProdId IS NULL) OR (sNewProdIdVersion IS NULL) OR (nOsId IS NULL) OR (nSeqNum IS NULL)THENRAISE_APPLICATION_ERROR (-20000, 'Missing some parameters! nOldProdId= '|| nOldProdId ||', sNewProdIdVersion= '|| sNewProdIdVersion ||', nOsId= '|| nOsId ||', nSeqNum= '|| nSeqNum);END IF;/*-------------------------------------------------------*/-- Get new prod_idOPEN curProd;FETCH curProd INTO recProd;SELECT pkg.pkg_name into sProdNameFROM PACKAGES pkg, PACKAGE_VERSIONS pvWHERE pkg.pkg_id = pv.pkg_idAND pv.pv_id = nOldProdId;IF curProd%FOUND THENnNewProdId := recProd.PV_ID;ELSESeed_Package_Names_Versions ( sProdName, sNewProdIdVersion, nUserId, outProdId );nNewProdId := outProdId;/*RAISE_APPLICATION_ERROR (-20000, 'Version '|| sNewProdIdVersion ||' does not exist.');*/END IF;CLOSE curProd;-- Update Product DetailsUPDATE OS_CONTENTS SETPROD_ID = nNewProdId,PRODUCT_COMMENTS = sComments,NOT_XML = sNotXML,IS_NODE_SPEC = sIsNodeSpecWHERE OS_ID = nOsIdAND PROD_ID = nOldProdId;--Update Package Electronic Name Details In Packages TableUPDATE PACKAGES SETSUNOS_ELECTRONIC_NAME = sSunOSElectronicName,WIN_ELECTRONIC_NAME = sWinElectronicNameWHERE PKG_ID =(SELECT PKG_IDFROM PACKAGE_VERSIONSWHERE PACKAGE_VERSIONS.PV_ID = nNewProdId);--Update Patch Electronic Name In Package Versions TableUPDATE PACKAGE_VERSIONS SETPATCH_ELECTRONIC_NAME = sPatchElectronicNameWHERE PV_ID = nNewProdId;-- Update seq_numpk_Operating_System.Shift_Product_SeqNum ( nNewProdId, nOsId, nSeqNum );END Update_Product;/*--------------------------------------------------------------------------------------------------*/PROCEDURE Replace_Product ( nOldProdId IN OS_CONTENTS.PROD_ID%TYPE,nNewProdId IN OS_CONTENTS.PROD_ID%TYPE,nOsId IN OS_CONTENTS.OS_ID%TYPE,nUserId IN NUMBER ) ISOldVersion VARCHAR2(100);NewVersion VARCHAR2(100);PkgName VARCHAR2(100);NodeName VARCHAR2(4000);BEGIN/*--------------- Business Rules Here -------------------*//*-------------------------------------------------------*/UPDATE OS_CONTENTS SETPROD_ID = nNewProdIdWHERE PROD_ID = nOldProdIdAND OS_ID = nOsId;SELECT PKG_VERSION INTO OldVersionFROM PACKAGE_VERSIONSWHERE PV_ID = nOldProdId;SELECT PKG_VERSION INTO NewVersionFROM PACKAGE_VERSIONSWHERE PV_ID = nNewProdId;SELECT PKG.PKG_NAME INTO PkgNameFROM PACKAGES PKG, PACKAGE_VERSIONS PVWHERE PV.PV_ID = nNewProdIdAND PKG.PKG_ID = PV.PKG_ID;SELECT NN.NODE_NAME INTO NodeNameFROM NETWORK_NODES NN, OPERATING_SYSTEMS OSWHERE NN.NODE_ID = OS.NODE_IDAND OS.OS_ID = nOsId;-- Log Action --pk_Bom.Log_Os_Trail ( nOsId,'Updated Product: '||PkgName|| ' from Version: '||OldVersion||' to '||NewVersion||' on Node: '||NodeName,nUserId );END Replace_Product;/*--------------------------------------------------------------------------------------------------*/PROCEDURE Remove_Process ( sProcName IN PROCESSES_CONFIG.PKG_HEALTH_TAG%TYPE,sProdIdList IN VARCHAR2,nOsId IN OS_CONTENTS.OS_ID%TYPE,sCmdInterface IN PROCESSES_CONFIG.CMD_INTERFACE%TYPE,sPkgOwner IN PROCESSES_CONFIG.PKG_OWNER%TYPE,sInterface IN PROCESSES_CONFIG.IS_INTERFACE%TYPE,nUserId IN NUMBER ) ISProcessName VARCHAR2(4000);NodeName VARCHAR2(4000);BEGIN/*Select the Correct Details - Begin*/IF sCmdInterface IS NOT NULL THENIF sInterface IS NOT NULL THENIF sPkgOwner IS NOT NULL THENSELECT PKG_HEALTH_TAG INTO ProcessNameFROM PROCESSES_CONFIGWHERE OS_ID = nOsIdAND PROD_ID = sProdIdListAND PKG_HEALTH_TAG = sProcNameAND CMD_INTERFACE = sCmdInterfaceAND PKG_OWNER = sPkgOwnerAND IS_INTERFACE = sInterface;ELSE/*sPkgOwner IS NULL, sInterface IS NOT NULL, sCmdInterface IS NOT NULL*/SELECT PKG_HEALTH_TAG INTO ProcessNameFROM PROCESSES_CONFIGWHERE OS_ID = nOsIdAND PROD_ID = sProdIdListAND PKG_HEALTH_TAG = sProcNameAND CMD_INTERFACE = sCmdInterfaceAND PKG_OWNER IS NULLAND IS_INTERFACE = sInterface;END IF ;ELSEIF sInterface IS NULL OR sInterface = 'F' THEN/* sInterface IS NULL, sCmdInterface IS NOT NULL*/IF sPkgOwner IS NOT NULL THENSELECT PKG_HEALTH_TAG INTO ProcessNameFROM PROCESSES_CONFIGWHERE OS_ID = nOsIdAND PROD_ID = sProdIdListAND PKG_HEALTH_TAG = sProcNameAND CMD_INTERFACE = sCmdInterfaceAND PKG_OWNER = sPkgOwnerAND (IS_INTERFACE = 'F' OR IS_INTERFACE IS NULL);ELSE/* sPkqOwner IS NULL, sInterface IS NULL, sCmdInterface IS NOT NULL*/SELECT PKG_HEALTH_TAG INTO ProcessNameFROM PROCESSES_CONFIGWHERE OS_ID = nOsIdAND PROD_ID = sProdIdListAND PKG_HEALTH_TAG = sProcNameAND CMD_INTERFACE = sCmdInterfaceAND PKG_OWNER IS NULLAND IS_INTERFACE IS NULL;END IF;END IF;END IF;ELSE/* sCmdInterface IS NULL*/IF sInterface IS NOT NULL THENIF sPkgOwner IS NOT NULL THENSELECT PKG_HEALTH_TAG INTO ProcessNameFROM PROCESSES_CONFIGWHERE OS_ID = nOsIdAND PROD_ID = sProdIdListAND PKG_HEALTH_TAG = sProcNameAND CMD_INTERFACE IS NULLAND PKG_OWNER = sPkgOwnerAND IS_INTERFACE = sInterface;ELSE/* sPkgOwner is NULL, sInterface IS NOT NULL, sCmdInterface IS NULL */SELECT PKG_HEALTH_TAG INTO ProcessNameFROM PROCESSES_CONFIGWHERE OS_ID = nOsIdAND PROD_ID = sProdIdListAND PKG_HEALTH_TAG = sProcNameAND CMD_INTERFACE IS NULLAND PKG_OWNER IS NULLAND IS_INTERFACE = sInterface;END IF;ELSEIF sInterface IS NULL OR sInterface = 'F' THEN/* sInterface IS NULL, sCmdInterface IS NULL*/IF sPkgOwner IS NOT NULL THENSELECT PKG_HEALTH_TAG INTO ProcessNameFROM PROCESSES_CONFIGWHERE OS_ID = nOsIdAND PROD_ID = sProdIdListAND PKG_HEALTH_TAG = sProcNameAND CMD_INTERFACE IS NULLAND PKG_OWNER = sPkgOwnerAND (IS_INTERFACE IS NULL OR IS_INTERFACE = 'F');ELSE/* sPkgOwner IS NULL, sInterface IS NULL, sCmdInterface IS NULL*/SELECT PKG_HEALTH_TAG INTO ProcessNameFROM PROCESSES_CONFIGWHERE OS_ID = nOsIdAND PROD_ID = sProdIdListAND PKG_HEALTH_TAG = sProcNameAND CMD_INTERFACE IS NULLAND PKG_OWNER IS NULLAND IS_INTERFACE IS NULL;END IF;END IF;END IF;END IF;/*Select the Correct Details - End */-- Log Action --pk_Bom.Log_Os_Trail ( nOsId,'Deleted Process: '||ProcessName||' From Node: '||NodeName,nUserId );/*Select the Correct Details - Begin*/IF sCmdInterface IS NOT NULL THENIF sInterface IS NOT NULL THENIF sPkgOwner IS NOT NULL THENDELETEFROM PROCESSES_CONFIGWHERE OS_ID = nOsIdAND PROD_ID = sProdIdListAND PKG_HEALTH_TAG = sProcNameAND CMD_INTERFACE = sCmdInterfaceAND PKG_OWNER = sPkgOwnerAND IS_INTERFACE = sInterface;ELSE/*sPkgOwner IS NULL, sInterface IS NOT NULL, sCmdInterface IS NOT NULL*/DELETEFROM PROCESSES_CONFIGWHERE OS_ID = nOsIdAND PROD_ID = sProdIdListAND PKG_HEALTH_TAG = sProcNameAND CMD_INTERFACE = sCmdInterfaceAND PKG_OWNER IS NULLAND IS_INTERFACE = sInterface;END IF;ELSE/* sInterface IS NULL, sCmdInterface IS NOT NULL*/IF sPkgOwner IS NOT NULL THENDELETEFROM PROCESSES_CONFIGWHERE OS_ID = nOsIdAND PROD_ID = sProdIdListAND PKG_HEALTH_TAG = sProcNameAND CMD_INTERFACE = sCmdInterfaceAND PKG_OWNER = sPkgOwnerAND IS_INTERFACE IS NULL;ELSE/* sPkqOwner IS NULL, sInterface IS NULL, sCmdInterface IS NOT NULL*/DELETEFROM PROCESSES_CONFIGWHERE OS_ID = nOsIdAND PROD_ID = sProdIdListAND PKG_HEALTH_TAG = sProcNameAND CMD_INTERFACE = sCmdInterfaceAND PKG_OWNER IS NULLAND IS_INTERFACE IS NULL;END IF;END IF;ELSE/* sCmdInterface IS NULL*/IF sInterface IS NOT NULL THENIF sPkgOwner IS NOT NULL THENDELETEFROM PROCESSES_CONFIGWHERE OS_ID = nOsIdAND PROD_ID = sProdIdListAND PKG_HEALTH_TAG = sProcNameAND CMD_INTERFACE IS NULLAND PKG_OWNER = sPkgOwnerAND IS_INTERFACE = sInterface;ELSE/* sPkgOwner is NULL, sInterface IS NOT NULL, sCmdInterface IS NULL */DELETEFROM PROCESSES_CONFIGWHERE OS_ID = nOsIdAND PROD_ID = sProdIdListAND PKG_HEALTH_TAG = sProcNameAND CMD_INTERFACE IS NULLAND PKG_OWNER IS NULLAND IS_INTERFACE = sInterface;END IF;ELSE/* sInterface IS NULL, sCmdInterface IS NULL*/IF sPkgOwner IS NOT NULL THENDELETEFROM PROCESSES_CONFIGWHERE OS_ID = nOsIdAND PROD_ID = sProdIdListAND PKG_HEALTH_TAG = sProcNameAND CMD_INTERFACE IS NULLAND PKG_OWNER = sPkgOwnerAND IS_INTERFACE IS NULL;ELSE/* sPkgOwner IS NULL, sInterface IS NULL, sCmdInterface IS NULL*/DELETEFROM PROCESSES_CONFIGWHERE OS_ID = nOsIdAND PROD_ID = sProdIdListAND PKG_HEALTH_TAG = sProcNameAND CMD_INTERFACE IS NULLAND PKG_OWNER IS NULLAND IS_INTERFACE IS NULL;END IF;END IF;END IF;/*Select the Correct Details - End*/END Remove_Process;/*--------------------------------------------------------------------------------------------------*/PROCEDURE Remove_Products ( sProdIdList IN VARCHAR2,nOsId IN OS_CONTENTS.OS_ID%TYPE,nUserId IN NUMBER ) ISPkgName VARCHAR2(100);PkgVersion VARCHAR2(100);NextSeqNum NUMBER := 1;nIdCollector DEPMGR_NUMBER_TAB_t := DEPMGR_NUMBER_TAB_t();NodeName VARCHAR2(4000);CURSOR prod_cur ISSELECT osc.PROD_ID, osc.SEQ_NUMFROM OS_CONTENTS oscWHERE osc.OS_ID = nOsIdORDER BY osc.SEQ_NUM;prod_rec prod_cur%ROWTYPE;BEGIN/*--------------- Business Rules Here -------------------*/IF sProdIdList IS NULLTHENRAISE_APPLICATION_ERROR (-20000, 'Please select one or more Products.');END IF;/*-------------------------------------------------------*/SELECT NN.NODE_NAME INTO NodeNameFROM NETWORK_NODES NN, OPERATING_SYSTEMS OSWHERE NN.NODE_ID = OS.NODE_IDAND OS.OS_ID = nOsId;-- Remove ProductsDELETEFROM OS_CONTENTSWHERE OS_ID = nOsIdAND PROD_ID IN (SELECT *FROM THE ( SELECT CAST( IN_LIST_NUMBER( sProdIdList ) AS DEPMGR_NUMBER_TAB_t ) FROM dual ));-- Remove ProcessesDELETEFROM PROCESSES_CONFIGWHERE OS_ID = nOsIdAND PROD_ID IN (SELECT *FROM THE ( SELECT CAST( IN_LIST_NUMBER( sProdIdList ) AS DEPMGR_NUMBER_TAB_t ) FROM dual ));-- Update Seq NumbersOPEN prod_cur;FETCH prod_cur INTO prod_rec;WHILE prod_cur%FOUNDLOOPUPDATE OS_CONTENTS SETSEQ_NUM = NextSeqNumWHERE OS_ID = nOsIdAND PROD_ID = prod_rec.PROD_ID;NextSeqNum := NextSeqNum + 1;FETCH prod_cur INTO prod_rec;END LOOP;CLOSE prod_cur;nIdCollector := IN_LIST_NUMBER ( sProdIdList );FOR i IN 1..nIdCollector.COUNTLOOPSELECT PKG.PKG_NAME INTO PkgNameFROM PACKAGES PKG, PACKAGE_VERSIONS PVWHERE PV.PV_ID = nIdCollector(i)AND PKG.PKG_ID = PV.PKG_ID;SELECT PKG_VERSION INTO PkgVersionFROM PACKAGE_VERSIONS PVWHERE PV.PV_ID = nIdCollector(i);-- Log Action --pk_Bom.Log_Os_Trail ( nOsId,'Deleted Product: '||PkgName||' (Version: '||PkgVersion||') from Node: '||NodeName,nUserId );END LOOP;END Remove_Products;/*--------------------------------------------------------------------------------------------------*/PROCEDURE Add_Products_NodeSpec ( sProdIdList IN VARCHAR2,nOsId IN OS_CONTENTS.OS_ID%TYPE,nUserId IN NUMBER ) ISNextSeqNum NUMBER := 1;CURSOR prod_cur ISSELECT osc.PROD_ID, osc.SEQ_NUMFROM OS_CONTENTS oscWHERE osc.OS_ID = nOsIdORDER BY osc.SEQ_NUM;prod_rec prod_cur%ROWTYPE;BEGIN/*--------------- Business Rules Here -------------------*/IF sProdIdList IS NULLTHENRAISE_APPLICATION_ERROR (-20000, 'Please select one or more Products.');END IF;/*-------------------------------------------------------*/UPDATE OS_CONTENTSSET IS_NODE_SPEC = ''WHERE OS_ID = nOsId;-- Set Nodespec OnUPDATE OS_CONTENTSSET IS_NODE_SPEC = 'on'WHERE OS_ID = nOsIdAND PROD_ID IN (SELECT *FROM THE ( SELECT CAST( IN_LIST_NUMBER( sProdIdList ) AS DEPMGR_NUMBER_TAB_t ) FROM dual ));END Add_Products_NodeSpec;/*--------------------------------------------------------------------------------------------------*/PROCEDURE Accept_Reject ( nProdId IN PRODUCT_DETAILS.PROD_ID%TYPE,nBomId IN BOMS.BOM_ID%TYPE,sComments IN REJECTION_TRAIL.COMMENTS%TYPE DEFAULT NULL,nUserId IN REJECTION_TRAIL.USER_ID%TYPE,nOsId IN NUMBER ) ISIsRejected PRODUCT_DETAILS.IS_REJECTED%TYPE;PkgName VARCHAR2(100);PkgVersion VARCHAR2(100);NodeName VARCHAR2(4000);CURSOR curProd ISSELECT pd.*FROM PRODUCT_DETAILS pdWHERE pd.PROD_ID = nProdId;recProd curProd%ROWTYPE;BEGIN/*--------------- Business Rules Here -------------------*/IF (nProdId IS NULL)THENRAISE_APPLICATION_ERROR (-20000, 'Please select at least one product.' );END IF;/*-------------------------------------------------------*/-- Set new Accept-Reject stateIsRejected := 'Y';SELECT NN.NODE_NAME INTO NodeNameFROM NETWORK_NODES NN, OPERATING_SYSTEMS OSWHERE NN.NODE_ID = OS.NODE_IDAND OS.OS_ID = nOsId;OPEN curProd;FETCH curProd INTO recProd;IF curProd%FOUND THEN/* Product Details found */SELECT PKG.PKG_NAME INTO PkgNameFROM PACKAGES PKG, PACKAGE_VERSIONS PVWHERE PV.PV_ID = nProdIdAND PKG.PKG_ID = PV.PKG_ID;SELECT PKG_VERSION INTO PkgVersionFROM PACKAGE_VERSIONS PVWHERE PV.PV_ID = nProdId;-- Set new Accept-Reject stateIF recProd.IS_REJECTED = 'Y' THENIsRejected := 'N';-- Log Action --pk_Bom.Log_Os_Trail ( nOsId,'Accepted Product: '||PkgName||' '||PkgVersion||' on Node: '||NodeName,nUserId );ELSEIsRejected := 'Y';-- Log Action --pk_Bom.Log_Os_Trail ( nOsId,'Rejected Product: '||PkgName||' '||PkgVersion||' on Node: '||NodeName,nUserId );END IF;-- Toggle Accept-Reject StateUPDATE PRODUCT_DETAILS pd SETpd.IS_REJECTED = IsRejectedWHERE pd.PROD_ID = nProdId;ELSE/* Product Details NOT found */-- Toggle Accept-Reject StateINSERT INTO PRODUCT_DETAILS ( PROD_ID, IS_REJECTED )VALUES ( nProdId, IsRejected );END IF;CLOSE curProd;-- Reflect changes to the BOM level/* Log Rejection Trail */pk_Utils.Log_Rejection_Trail ( GET_SYSTEM_VALUE('enumENTITY_TYPE_PRODUCT'), nProdId, IsRejected, nUserId, sComments );END Accept_Reject;/*--------------------------------------------------------------------------------------------------*/PROCEDURE Accept_Reject_List ( nProdIdList IN VARCHAR2,nBomId IN BOMS.BOM_ID%TYPE,sComments IN REJECTION_TRAIL.COMMENTS%TYPE DEFAULT NULL,nUserId IN REJECTION_TRAIL.USER_ID%TYPE,nOsId IN NUMBER ) ISnIdCollector DEPMGR_NUMBER_TAB_t := DEPMGR_NUMBER_TAB_t();BEGIN/*--------------- Business Rules Here -------------------*/IF (nProdIdList IS NULL)THENRAISE_APPLICATION_ERROR (-20000, 'Please select at least one product.' );END IF;/*-------------------------------------------------------*/nIdCollector := IN_LIST_NUMBER ( nProdIdList );FOR i IN 1..nIdCollector.COUNTLOOPAccept_Reject ( nIdCollector(i), nBomId, sComments, nUserId, nOsId);END LOOP;END Accept_Reject_List;/*--------------------------------------------------------------------------------------------------*/PROCEDURE Accept_Reject_Comments ( nProdId IN PRODUCT_DETAILS.PROD_ID%TYPE,nRejectSeq IN REJECTION_TRAIL.REJECT_SEQ%TYPE,sComments IN REJECTION_TRAIL.COMMENTS%TYPE DEFAULT NULL,nUserId IN REJECTION_TRAIL.USER_ID%TYPE ) ISIsRejected REJECTION_TRAIL.IS_REJECTED%TYPE;BEGIN/*--------------- Business Rules Here -------------------*/IF (nProdId IS NULL)THENRAISE_APPLICATION_ERROR (-20000, 'Missing some parameters! nProdId= '|| nProdId );END IF;/*-------------------------------------------------------*/IF nRejectSeq IS NULL THEN/* CREATE New Note */-- Get last IsRejected stateSELECT rt.IS_REJECTED INTO IsRejectedFROM REJECTION_TRAIL rtWHERE rt.ENTITY_ID = nProdIdAND rt.ENUM_ENTITY_TYPE = GET_SYSTEM_VALUE('enumENTITY_TYPE_PRODUCT')AND ROWNUM = 1ORDER BY rt.REJECT_SEQ;/* Log Rejection Trail */pk_Utils.Log_Rejection_Trail ( GET_SYSTEM_VALUE('enumENTITY_TYPE_PRODUCT'), nProdId, IsRejected, nUserId, sComments );ELSE/* EDIT Note */UPDATE REJECTION_TRAIL rt SETrt.COMMENTS = sComments,rt.USER_ID = nUserId,rt.DATE_TIME_STAMP = GET_DATETIME()WHERE rt.ENUM_ENTITY_TYPE = GET_SYSTEM_VALUE('enumENTITY_TYPE_PRODUCT')AND rt.ENTITY_ID = nProdIdAND rt.REJECT_SEQ = nRejectSeq;END IF;END Accept_Reject_Comments;/*--------------------------------------------------------------------------------------------------*/PROCEDURE Sort_Products ( sProdIdOrder IN VARCHAR2,nOsId IN OS_CONTENTS.OS_ID%TYPE,nUserId IN NUMBER ) ISnIdCollector DEPMGR_NUMBER_TAB_t := DEPMGR_NUMBER_TAB_t();SeqNum NUMBER;BEGIN/*--------------- Business Rules Here -------------------*/-- Check for parametersIF (sProdIdOrder IS NULL) OR (nOsId IS NULL)THENRAISE_APPLICATION_ERROR (-20000, 'Missing some parameters! nOsId='|| nOsId ||' sProdIdOrder='|| sProdIdOrder );END IF;/*-------------------------------------------------------*/nIdCollector := IN_LIST_NUMBER ( sProdIdOrder );-- Set start sequence numberSeqNum := 1;FOR i IN 1..nIdCollector.COUNTLOOP-- Update new sequence numberUPDATE OS_CONTENTS osc SETosc.SEQ_NUM = SeqNumWHERE osc.OS_ID = nOsIdAND osc.PROD_ID = nIdCollector(i);SeqNum := SeqNum + 1;END LOOP;END Sort_Products;/*--------------------------------------------------------------------------------------------------*/PROCEDURE Remove_Obsolete_Patches ( nOsId IN OS_CONTENTS.OS_ID%TYPE,nUserId IN NUMBER ) ISNextSeqNum NUMBER := 1;CURSOR prod_cur ISSELECT osc.PROD_ID, osc.SEQ_NUMFROM OS_CONTENTS oscWHERE osc.OS_ID = nOsIdORDER BY osc.SEQ_NUM;prod_rec prod_cur%ROWTYPE;BEGIN-- Delete Obsolete Patches in the OS.DELETE FROM OS_CONTENTSWHERE OS_ID = nOsIdAND PROD_ID IN(SELECT osc.PROD_IDFROM OS_CONTENTS osc, PACKAGE_VERSIONS pvWHERE pv.PV_ID = osc.PROD_IDAND pv.IS_OBSOLETE = 'Y'AND osc.OS_ID = nOsId);-- Update Seq NumbersOPEN prod_cur;FETCH prod_cur INTO prod_rec;WHILE prod_cur%FOUNDLOOPUPDATE OS_CONTENTS SETSEQ_NUM = NextSeqNumWHERE OS_ID = nOsIdAND PROD_ID = prod_rec.PROD_ID;NextSeqNum := NextSeqNum + 1;FETCH prod_cur INTO prod_rec;END LOOP;CLOSE prod_cur;END Remove_Obsolete_Patches;/*--------------------------------------------------------------------------------------------------*/FUNCTION GET_OS_ID ( PvId IN NUMBER ) RETURN NUMBER ISReturnValue NUMBER;BEGINSELECT osc.OS_ID INTO ReturnValueFROM OS_CONTENTS oscWHERE osc.PROD_ID = PvId;RETURN ReturnValue;END;/*--------------------------------------------------------------------------------------------------*/FUNCTION GET_NODE_ID ( OsId IN NUMBER ) RETURN NUMBER ISReturnValue NUMBER;BEGINSELECT os.NODE_ID INTO ReturnValueFROM OPERATING_SYSTEMS osWHERE os.OS_ID = OsId;RETURN ReturnValue;END;/*-------------------------------------------------------------------------------------------------------*//*-------------------------------------------------------------------------------------------------------*/END pk_Product_Test;//---------------------------------------------------------- DDL for Package Body PK_PROJECT--------------------------------------------------------CREATE OR REPLACE PACKAGE BODY "PK_PROJECT"IS/* ---------------------------------------------------------------------------Version: 2.0--------------------------------------------------------------------------- *//*--------------------------------------------------------------------------------------------------*/PROCEDURE Add_Project ( sProjName IN DM_PROJECTS.PROJ_NAME%TYPE,nRMProjFk IN DM_PROJECTS.RM_PROJECTS_FK%TYPE ) ISProjId NUMBER;BEGIN/*--------------- Business Rules Here -------------------*//*-------------------------------------------------------*/-- Get proj_id --SELECT SEQ_PROJ_ID.NEXTVAL INTO ProjId FROM DUAL;-- Insert Branch --INSERT INTO DM_PROJECTS ( PROJ_ID, PROJ_NAME, RM_PROJECTS_FK, IS_HIDDEN )VALUES ( ProjId, sProjName, nRMProjFk, NULL );EXCEPTIONWHEN DUP_VAL_ON_INDEXTHENRAISE_APPLICATION_ERROR (-20000, 'Project Name "'|| sProjName ||'" is Already Used.');END Add_Project;/*--------------------------------------------------------------------------------------------------*/PROCEDURE Update_Project ( nProjId IN DM_PROJECTS.PROJ_ID%TYPE,sProjName IN DM_PROJECTS.PROJ_NAME%TYPE,nRMProjFk IN DM_PROJECTS.RM_PROJECTS_FK%TYPE ) ISBEGIN/*--------------- Business Rules Here -------------------*//*-------------------------------------------------------*/-- Update Project --UPDATE DM_PROJECTS SETPROJ_NAME = sProjName,RM_PROJECTS_FK = nRMProjFkWHERE PROJ_ID = nProjId;EXCEPTIONWHEN DUP_VAL_ON_INDEXTHENRAISE_APPLICATION_ERROR (-20000, 'Project Name "'|| sProjName ||'" is Already Used.');END Update_Project;/*--------------------------------------------------------------------------------------------------*/PROCEDURE ShowHide_Project ( nProjId DM_PROJECTS.PROJ_ID%TYPE ) IScurrIsHidden DM_PROJECTS.IS_HIDDEN%TYPE;BEGIN/*--------------- Business Rules Here -------------------*//*-------------------------------------------------------*/-- Get current IsHidden StateSELECT dmp.IS_HIDDEN INTO currIsHiddenFROM DM_PROJECTS dmpWHERE dmp.PROJ_ID = nProjId;-- Toggle IsHidden State --IF currIsHidden = 'Y' THENcurrIsHidden := NULL;ELSEcurrIsHidden := 'Y';END IF;-- Update State --UPDATE DM_PROJECTS dmp SETIS_HIDDEN = currIsHiddenWHERE dmp.PROJ_ID = nProjId;END ShowHide_Project;/*--------------------------------------------------------------------------------------------------*/PROCEDURE Remove_Project ( nProjId IN DM_PROJECTS.PROJ_ID%TYPE ) ISrowCount NUMBER DEFAULT 0;BEGIN/*--------------- Business Rules Here -------------------*/-- Check for Dependencies (Branches)SELECT Count(*) INTO rowCountFROM BRANCHES brWHERE br.PROJ_ID = nProjId;IF rowCount > 0 THENRAISE_APPLICATION_ERROR (-20000, 'Some Branches are still using this Project ( Counted '|| rowCount ||' ).' );END IF;-- Check for Dependencies (States)SELECT Count(*) INTO rowCountFROM STATES stWHERE st.PROJ_ID = nProjId;IF rowCount > 0 THENRAISE_APPLICATION_ERROR (-20000, 'Some States are still using this Project ( Counted '|| rowCount ||' ).' );END IF;/*-------------------------------------------------------*/-- Remove Notificaitons --DELETEFROM NOTIFY_USER nuWHERE nu.PROJ_ID = nProjId;-- Remove Product Queue --DELETEFROM PRODUCTS_QUEUE pqWHERE pq.PROJ_ID = nProjId;-- Remove Project --DELETEFROM DM_PROJECTS dmpWHERE dmp.PROJ_ID = nProjId;END Remove_Project;/*--------------------------------------------------------------------------------------------------*/PROCEDURE Add_Branch ( sBranchName IN BRANCHES.BRANCH_NAME%TYPE,nProjId IN BRANCHES.PROJ_ID%TYPE,nRMVTreeId IN BRANCHES.RM_VTREE_FK%TYPE,sBranchComments IN BRANCHES.BRANCH_COMMENTS%TYPE ) ISBranchId NUMBER;BEGIN/*--------------- Business Rules Here -------------------*//*-------------------------------------------------------*/-- Get branch_id --SELECT SEQ_BRANCH_ID.NEXTVAL INTO BranchId FROM DUAL;-- Insert Branch --INSERT INTO BRANCHES (BRANCH_ID, PROJ_ID, BRANCH_NAME, RM_VTREE_FK, BRANCH_COMMENTS )VALUES ( BranchId, nProjId, sBranchName, nRMVTreeId, sBranchComments );EXCEPTIONWHEN DUP_VAL_ON_INDEXTHENRAISE_APPLICATION_ERROR (-20000, 'Branch Name "'|| sBranchName ||'" is Already Used.');END Add_Branch;/*--------------------------------------------------------------------------------------------------*/PROCEDURE Remove_Branch ( nBranchId IN BRANCHES.BRANCH_ID%TYPE ) ISrowCount NUMBER DEFAULT 0;BEGIN/*--------------- Business Rules Here -------------------*/-- Check for DependenciesSELECT Count(*) INTO rowCountFROM BOMS boWHERE bo.BRANCH_ID = nBranchId;IF rowCount > 0 THENRAISE_APPLICATION_ERROR (-20000, 'Some BOMS are still using this Branch ( Counted '|| rowCount ||' ).' );END IF;/*-------------------------------------------------------*/-- Remove Branch --DELETEFROM BRANCHES brWHERE br.BRANCH_ID = nBranchId;END Remove_Branch;/*--------------------------------------------------------------------------------------------------*/PROCEDURE Update_Branch ( nBranchId IN BRANCHES.BRANCH_ID%TYPE,sBranchName IN BRANCHES.BRANCH_NAME%TYPE,nRMVTreeId IN BRANCHES.RM_VTREE_FK%TYPE,sBranchComments IN BRANCHES.BRANCH_COMMENTS%TYPE ) ISBEGIN/*--------------- Business Rules Here -------------------*//*-------------------------------------------------------*/-- Update Branch --UPDATE BRANCHES SETBRANCH_NAME = sBranchName,RM_VTREE_FK = nRMVTreeId,BRANCH_COMMENTS = sBranchCommentsWHERE BRANCH_ID = nBranchId;EXCEPTIONWHEN DUP_VAL_ON_INDEXTHENRAISE_APPLICATION_ERROR (-20000, 'Branch Name "'|| sBranchName ||'" is Already Used.');END Update_Branch;/*--------------------------------------------------------------------------------------------------*/PROCEDURE ShowHide_Branch ( nBranchId IN BRANCHES.BRANCH_ID%TYPE ) IScurrIsHidden BRANCHES.IS_HIDDEN%TYPE;BEGIN/*--------------- Business Rules Here -------------------*//*-------------------------------------------------------*/-- Get current IsHidden StateSELECT br.IS_HIDDEN INTO currIsHiddenFROM BRANCHES brWHERE br.BRANCH_ID = nBranchId;-- Toggle IsHidden State --IF currIsHidden = 'Y' THENcurrIsHidden := NULL;ELSEcurrIsHidden := 'Y';END IF;-- Update State --UPDATE BRANCHES SETIS_HIDDEN = currIsHiddenWHERE BRANCH_ID = nBranchId;END ShowHide_Branch;/*--------------------------------------------------------------------------------------------------*/PROCEDURE ShowHide_Download ( nProjId DM_PROJECTS.PROJ_ID%TYPE ) IScurrIsDownload DM_PROJECTS.IS_DOWNLOAD%TYPE;BEGIN/*--------------- Business Rules Here -------------------*//*-------------------------------------------------------*/-- Get current IsHidden StateSELECT dmp.IS_DOWNLOAD INTO currIsDownloadFROM DM_PROJECTS dmpWHERE dmp.PROJ_ID = nProjId;-- Toggle IsHidden State --IF currIsDownload = 'Y' THENcurrIsDownload := NULL;ELSEcurrIsDownload := 'Y';END IF;-- Update State --UPDATE DM_PROJECTS dmp SETIS_DOWNLOAD = currIsDownloadWHERE dmp.PROJ_ID = nProjId;END ShowHide_Download;/*--------------------------------------------------------------------------------------------------*/PROCEDURE ShowHide_StateDownload ( nStateId STATES.STATE_ID%TYPE ) IScurrIsDownload DM_PROJECTS.IS_DOWNLOAD%TYPE;BEGIN/*--------------- Business Rules Here -------------------*//*-------------------------------------------------------*/-- Get current IsHidden StateSELECT st.IS_DOWNLOAD INTO currIsDownloadFROM STATES stWHERE st.STATE_ID = nStateId;-- Toggle IsHidden State --IF currIsDownload = 'Y' THENcurrIsDownload := NULL;ELSEcurrIsDownload := 'Y';END IF;-- Update State --UPDATE STATES st SETIS_DOWNLOAD = currIsDownloadWHERE st.STATE_ID = nStateId;END ShowHide_StateDownload;END pk_Project;//---------------------------------------------------------- DDL for Package Body PK_REPORT--------------------------------------------------------CREATE OR REPLACE PACKAGE BODY "PK_REPORT" IS/*------------------------------|| Last Modified: S.Vukovic|| Modified Date: 8/Mar/2005|| Body Version: 1.0------------------------------*//*-------------------------------------------------------------------------------------------------------*/FUNCTION BOM_PRODUCTS_BY_NODE_BY_OS ( BomId NUMBER ) RETURN typeCur ISrecords typeCur;BEGINOPEN records FORSELECT nn.NODE_ID,nt.NODE_ICON,nn.NODE_NAME,bt.BOS_ICON,obe.BASE_ENV_ID,os.OS_ID,os.OS_NAME,osc.SEQ_NUM,osc.PROD_ID,pkg.PKG_NAME,pv.PKG_VERSIONFROM BOM_CONTENTS bc,OPERATING_SYSTEMS os,OS_CONTENTS osc,OS_BASE_ENV obe,BASE_ENV be,BOS_TYPES bt,NETWORK_NODES nn,NODE_TYPES nt,PACKAGE_VERSIONS pv,PACKAGES pkgWHERE os.NODE_ID (+) = nn.NODE_IDAND nn.NODE_ID = bc.NODE_IDAND nn.NODE_TYPE_ID = nt.NODE_TYPE_ID (+)AND bc.BOM_ID = BomIdAND pv.PKG_ID = pkg.PKG_ID (+)AND osc.OS_ID (+) = os.OS_IDAND osc.PROD_ID = pv.PV_ID (+)AND obe.OS_ID (+) = os.OS_IDAND obe.BASE_ENV_ID = be.BASE_ENV_ID (+)AND be.BOS_ID = bt.BOS_ID (+)ORDER BY UPPER(nn.NODE_NAME), UPPER(os.OS_NAME), osc.SEQ_NUM;RETURN records;END;/*-------------------------------------------------------------------------------------------------------*/END PK_REPORT;//---------------------------------------------------------- DDL for Package Body PK_REQUIREMENTS--------------------------------------------------------CREATE OR REPLACE PACKAGE BODY "PK_REQUIREMENTS"IS/* ---------------------------------------------------------------------------Version: 1.0.0--------------------------------------------------------------------------- *//*--------------------------------------------------------------------------------------------------*/PROCEDURE Add_Bom ( nFromBomId IN BOMS.BOM_ID%TYPE,nToBranchId IN BOMS.BRANCH_ID%TYPE ) ISrowCount NUMBER;FromBranchId NUMBER;BEGIN/*--------------- Business Rules Here -------------------*//*-------------------------------------------------------*/-- Get FromBranchIdSELECT bo.BRANCH_ID INTO FromBranchIdFROM BOMS boWHERE bo.BOM_ID = nFromBomId;IF FromBranchId = nToBranchId THEN/* Only check for rules if same branch_id */-- REQUIREMENT: Cannot Branch more then 2 BOMs from parentSELECT COUNT(*) INTO rowCountFROM BOMS boWHERE bo.PARENT_BOM_ID = nFromBomIdAND bo.BRANCH_ID = nToBranchIdAND bo.BOM_ID != bo.PARENT_BOM_ID;IF rowCount >= 2 THENRAISE_APPLICATION_ERROR (-20000, 'This BOM is already branched.' );END IF;END IF;END Add_Bom;/*--------------------------------------------------------------------------------------------------*/PROCEDURE Destroy_Bom ( nBomId IN VARCHAR2 ) ISrowCount NUMBER;nBomIdCollector DEPMGR_NUMBER_TAB_t := DEPMGR_NUMBER_TAB_t();BEGIN/*--------------- Business Rules Here -------------------*//*-------------------------------------------------------*/nBomIdCollector := IN_LIST_NUMBER( nBomId );-- REQUIREMENT: For now if can only deal with one bom at a timeIF nBomIdCollector.Count > 1 THENRAISE_APPLICATION_ERROR (-20000, 'Please select one BOM only.' );END IF;-- REQUIREMENT: BOM need to be UnlockedSELECT Count(*) INTO rowCountFROM BOMS boWHERE bo.IS_READONLY = 'Y'AND bo.BOM_ID IN (SELECT *FROM THE ( SELECT CAST( nBomIdCollector AS DEPMGR_NUMBER_TAB_t ) FROM dual ));IF rowCount > 0 THENRAISE_APPLICATION_ERROR (-20000, 'Cannot Destroy as selected BOM is locked.' );END IF;-- REQUIREMENT: Cannot be a parent to someoneSELECT Count(*) INTO rowCountFROM BOMS boWHERE bo.BOM_ID != bo.PARENT_BOM_IDAND bo.PARENT_BOM_ID IN (SELECT *FROM THE ( SELECT CAST( nBomIdCollector AS DEPMGR_NUMBER_TAB_t ) FROM dual ));IF rowCount > 0 THENRAISE_APPLICATION_ERROR (-20000, 'Cannot Destroy as some BOMs are created or branched from this BOM.' );END IF;-- Check for Dependencies (Knowledge Base Notes)SELECT Count(*) INTO rowCountFROM BOM_NOTES bnWHERE bn.BOM_ID IN (SELECT *FROM THE ( SELECT CAST( nBomIdCollector AS DEPMGR_NUMBER_TAB_t ) FROM dual ));IF rowCount > 0 THENRAISE_APPLICATION_ERROR (-20000, 'Cannot Destroy as some knowledge base notes are attached to this BOM.' );END IF;END Destroy_Bom;/*--------------------------------------------------------------------------------------------------*/END pk_Requirements;//---------------------------------------------------------- DDL for Package Body PK_STATE--------------------------------------------------------CREATE OR REPLACE PACKAGE BODY "PK_STATE"IS/* ---------------------------------------------------------------------------Version: 1.0.0--------------------------------------------------------------------------- *//*--------------------------------------------------------------------------------------------------*/PROCEDURE Add_State ( sStateName IN STATES.STATE_NAME%TYPE,nStateTypeEnum IN STATES.STATE_TYPE_ENUM%TYPE,nProjId IN STATES.PROJ_ID%TYPE ) ISStateId NUMBER;NextStateSeq NUMBER;BEGIN/*--------------- Business Rules Here -------------------*//*-------------------------------------------------------*/-- Get state_id --SELECT SEQ_STATE_ID.NEXTVAL INTO StateId FROM DUAL;-- Get Next Release SequenceSELECT MAX( st.STATE_SEQ ) INTO NextStateSeqFROM STATES stWHERE st.PROJ_ID = nProjId;IF NextStateSeq IS NULL THENNextStateSeq := 1;ELSENextStateSeq := NextStateSeq + 1;END IF;-- Insert Branch --INSERT INTO STATES ( STATE_ID, PROJ_ID, STATE_NAME, STATE_SEQ, STATE_TYPE_ENUM )VALUES ( StateId, nProjId, sStateName, NextStateSeq, nStateTypeEnum );EXCEPTIONWHEN DUP_VAL_ON_INDEXTHENRAISE_APPLICATION_ERROR (-20000, 'State Name "'|| sStateName ||'" is Already Used.');END Add_State;/*--------------------------------------------------------------------------------------------------*/PROCEDURE Update_State ( nStateId IN STATES.STATE_ID%TYPE,sStateName IN STATES.STATE_NAME%TYPE,nStateSeq IN STATES.STATE_SEQ%TYPE,nStateTypeEnum IN STATES.STATE_TYPE_ENUM%TYPE ) IScurrSeqNum NUMBER;FromSeqNum NUMBER;ProjId NUMBER;BEGIN/*--------------- Business Rules Here -------------------*//*-------------------------------------------------------*/-- Get State proj_id --SELECT st.PROJ_ID INTO ProjId FROM STATES st WHERE st.STATE_ID = nStateId;-- Get Current seq_numSELECT st.STATE_SEQ INTO currSeqNumFROM STATES stWHERE st.STATE_ID = nStateId;IF currSeqNum > nStateSeqTHENFromSeqNum := nStateSeq;-- Shift others UpUPDATE STATES st SETst.STATE_SEQ = st.STATE_SEQ + 1WHERE st.PROJ_ID = ProjIdAND st.STATE_SEQ BETWEEN FromSeqNum AND currSeqNum - 1;ELSIF currSeqNum < nStateSeqTHENFromSeqNum := currSeqNum + 1;-- Shift others DownUPDATE STATES st SETst.STATE_SEQ = st.STATE_SEQ - 1WHERE st.PROJ_ID = ProjIdAND st.STATE_SEQ BETWEEN FromSeqNum AND nStateSeq;END IF;-- Update State --UPDATE STATES st SETst.STATE_NAME = sStateName,st.STATE_SEQ = nStateSeq,st.STATE_TYPE_ENUM = nStateTypeEnumWHERE st.STATE_ID = nStateId;EXCEPTIONWHEN DUP_VAL_ON_INDEXTHENRAISE_APPLICATION_ERROR (-20000, 'State Name "'|| sStateName ||'" is Already Used.');END Update_State;/*--------------------------------------------------------------------------------------------------*/PROCEDURE Remove_State ( nStateId IN STATES.STATE_ID%TYPE ) ISrowCount NUMBER DEFAULT 0;ProjId NUMBER;NextSeqNum NUMBER;CURSOR curStates ISSELECT st.STATE_IDFROM STATES stWHERE st.PROJ_ID = ProjIdORDER BY st.STATE_SEQ;recStates curStates%ROWTYPE;BEGIN/*--------------- Business Rules Here -------------------*/-- Check for Dependencies (BOMS)SELECT Count(*) INTO rowCountFROM BOM_STATE bsWHERE bs.STATE_ID = nStateId;IF rowCount > 0 THENRAISE_APPLICATION_ERROR (-20000, 'Some BOMS are still using this State ( Counted '|| rowCount ||' ).' );END IF;/*-------------------------------------------------------*/-- Get State proj_id --SELECT st.PROJ_ID INTO ProjId FROM STATES st WHERE st.STATE_ID = nStateId;-- Remove State --DELETEFROM STATES stWHERE st.STATE_ID = nStateId;-- Update Seq Number --OPEN curStates;FETCH curStates INTO recStates;NextSeqNum := 1;WHILE curStates%FOUNDLOOPUPDATE STATES st SETst.STATE_SEQ = NextSeqNumWHERE st.STATE_ID = recStates.STATE_ID;NextSeqNum := NextSeqNum + 1;FETCH curStates INTO recStates;END LOOP;CLOSE curStates;END Remove_State;/*--------------------------------------------------------------------------------------------------*/PROCEDURE Toggle_Is_Displayed ( nStateId IN BOM_STATE.STATE_ID%TYPE,nBomId IN BOM_STATE.BOM_ID%TYPE,cIsDisplay IN BOM_STATE.IS_DISPLAYED%TYPE ) ISIsDisplayed BOM_STATE.IS_DISPLAYED%TYPE;BEGIN/*--------------- Business Rules Here -------------------*//*-------------------------------------------------------*/-- Get Current is_displayed/*SELECT bs.IS_DISPLAYED INTO IsDisplayedFROM BOM_STATE bsWHERE bs.BOM_ID = nBomIdAND bs.STATE_ID = nStateId;-- Toggle is_displayedIF IsDisplayed = 'Y' THENIsDisplayed := 'N';ELSEIsDisplayed := 'Y';END IF;*/-- Update bom stateUPDATE BOM_STATE SETIS_DISPLAYED = cIsDisplayWHERE BOM_ID = nBomIdAND STATE_ID = nStateId;END Toggle_Is_Displayed;/*--------------------------------------------------------------------------------------------------*/PROCEDURE Auto_Hide ( nStateId IN BOM_STATE.STATE_ID%TYPE,nInsertedBomId IN BOM_STATE.BOM_ID%TYPE,nEventValue IN SYSTEM_CONFIGURATION.ITEM_VALUE%TYPE ) ISCURSOR curParent ISSELECT bo.BOM_ID,bo.IS_READONLY,bo.IS_REJECTEDFROM BOMS boWHERE bo.BOM_ID IN (SELECT bo.PARENT_BOM_IDFROM BOMS boWHERE bo.BOM_ID = nInsertedBomIdAND bo.BOM_ID != bo.PARENT_BOM_ID);recParent curParent%ROWTYPE;ParentBomId BOMS.PARENT_BOM_ID%TYPE;ParentOfficial BOMS.IS_READONLY%TYPE;ParentRejected BOMS.IS_REJECTED%TYPE;BEGIN/*--------------- Business Rules Here -------------------*//*-------------------------------------------------------*/-- Get Parent DetailsOPEN curParent;FETCH curParent INTO recParent;ParentBomId := recParent.BOM_ID;ParentOfficial := recParent.IS_READONLY;ParentRejected := recParent.IS_REJECTED;CLOSE curParent;IF ParentOfficial = 'Y' THEN/* Auto-Hide BOM */IF nEventValue = GET_SYSTEM_VALUE('enumEVENT_ON_RELEASE_TO') THEN/* REQUIREMENT: Hide Rejected parent */IF ParentRejected = 'Y' THEN-- Update bom stateUPDATE BOM_STATE SETIS_DISPLAYED = 'N'WHERE BOM_ID = ParentBomIdAND STATE_ID = nStateId;END IF;ELSIF ( nEventValue = GET_SYSTEM_VALUE('enumEVENT_ON_LOCK_BOM') ) OR (nEventValue = GET_SYSTEM_VALUE('enumEVENT_ON_ACCEPT_BOM') ) THEN/* REQUIREMENT: Hide Rejected or Accepted parent */IF NOT ParentRejected IS NULL THEN-- Update bom stateUPDATE BOM_STATE SETIS_DISPLAYED = 'N'WHERE BOM_ID = ParentBomIdAND STATE_ID = nStateId;END IF;ELSIF ( nEventValue = GET_SYSTEM_VALUE('enumEVENT_ON_UNLOCK_BOM') ) OR (nEventValue = GET_SYSTEM_VALUE('enumEVENT_ON_REJECT_BOM') ) THEN/* REQUIREMENT: Show parent */-- Update bom stateUPDATE BOM_STATE SETIS_DISPLAYED = 'Y'WHERE BOM_ID = ParentBomIdAND STATE_ID = nStateId;END IF;END IF;END Auto_Hide;/*--------------------------------------------------------------------------------------------------*/END pk_State;//---------------------------------------------------------- DDL for Package Body PK_UTILS--------------------------------------------------------CREATE OR REPLACE PACKAGE BODY "PK_UTILS"IS/* ---------------------------------------------------------------------------Version: 1.0.0--------------------------------------------------------------------------- *//*--------------------------------------------------------------------------------------------------*/PROCEDURE Clone_BOM ( nFromBomId IN BOMS.BOM_ID%TYPE,nToBomId IN BOMS.BOM_ID%TYPE,nUserId IN NUMBER ) ISFromBom VARCHAR2(4000);BEGIN/*--------------- Business Rules Here -------------------*//*-------------------------------------------------------*/-- Define old_bom_idUPDATE BOMS bo SETbo.OLD_BOM_ID = nFromBomIdWHERE bo.BOM_ID = nToBomId;-- Copy Bom ContentsCopy_BOM_Contents ( nFromBomId, nToBomId );-- Finnaly Clean old_id columnsClean_Old_Id_Column();/* Log BOM_TRAIL */-- Get FromBom DetailsSELECT proj.PROJ_NAME ||' / '|| br.BRANCH_NAME ||' / '|| bn.BOM_NAME ||' '|| bo.BOM_VERSION ||'.'|| BOM_LIFECYCLEINTO FromBomFROM DM_PROJECTS proj,BOMS bo,BOM_NAMES bn,BRANCHES brWHERE br.PROJ_ID = proj.PROJ_IDAND bo.BRANCH_ID = br.BRANCH_IDAND bo.BOM_NAME_ID = bn.BOM_NAME_IDAND bo.BOM_ID = nFromBomId;-- Log Actionpk_Bom.Log_Bom_Trail ( nToBomId, 'COPIED: From '|| FromBom, nUserId );END Clone_BOM;/*--------------------------------------------------------------------------------------------------*/PROCEDURE Copy_BOM_Contents ( nFromBomId IN BOMS.BOM_ID%TYPE,nToBomId IN BOMS.BOM_ID%TYPE ) ISBEGIN/*--------------- Business Rules Here -------------------*//*-------------------------------------------------------*/-- Copy Network NodesCopy_Network_Node ( nFromBomId, nToBomId );-- Copy Hardware NamesINSERT INTO HARDWARE ( HARDWARE_NAME_ID, BOM_ID, HARDWARE_NAME, TIER_NAME, OLD_HARDWARE_NAME_ID )SELECT SEQ_HARDWARE_NAME_ID.NEXTVAL AS HARDWARE_NAME_ID,nToBomId AS BOM_ID,hw.HARDWARE_NAME,hw.TIER_NAME,hw.HARDWARE_NAME_ID AS OLD_HARDWARE_NAME_IDFROM HARDWARE hwWHERE hw.BOM_ID = nFromBomId;-- Copy Hardware MatrixINSERT INTO HARDWARE_NODE ( HARDWARE_NAME_ID, NODE_ID)SELECT hw.HARDWARE_NAME_ID,nn.NODE_IDFROM HARDWARE hw,NETWORK_NODES nn,HARDWARE_NODE hdWHERE hd.NODE_ID = nn.OLD_NODE_IDAND hd.HARDWARE_NAME_ID = hw.OLD_HARDWARE_NAME_IDAND hw.BOM_ID = nToBomId;-- Copy Base EnvironmentsINSERT INTO BASE_ENV ( BASE_ENV_ID, BOS_ID, BASE_ENV_NAME, OLD_BASE_ENV_ID, BOM_ID )SELECT SEQ_BASE_ENV_ID.NEXTVAL AS BASE_ENV_ID,be.BOS_ID,be.BASE_ENV_NAME,be.BASE_ENV_ID AS OLD_BASE_ENV_ID,nToBomId AS BOM_IDFROM BASE_ENV beWHERE be.BOM_ID = nFromBomId;-- Relink Base Environment ContentsINSERT INTO BASE_ENV_CONTENTS ( BASE_ENV_ID, PROD_ID, BASE_ENV_COMMENTS, SEQ_NUM, IS_NODE_SPEC, NOT_XML )SELECT be.BASE_ENV_ID AS BASE_ENV_ID,bec.PROD_ID,bec.BASE_ENV_COMMENTS,bec.SEQ_NUM,bec.IS_NODE_SPEC,bec.NOT_XMLFROM BASE_ENV be,BASE_ENV_CONTENTS becWHERE bec.BASE_ENV_ID = be.OLD_BASE_ENV_IDAND be.BOM_ID = nToBomId;-- Copy OSINSERT INTO OPERATING_SYSTEMS ( OS_ID, OS_NAME, NODE_ID, OS_COMMENTS, OLD_OS_ID, NODE_SPEC_FILE, NOT_XML )SELECT SEQ_OS_ID.NEXTVAL AS OS_ID,os.OS_NAME,nn.NODE_ID AS NODE_ID,os.OS_COMMENTS,os.OS_ID AS OLD_OS_ID,os.NODE_SPEC_FILE,os.NOT_XMLFROM OPERATING_SYSTEMS os,NETWORK_NODES nn,BOM_CONTENTS bcWHERE bc.NODE_ID = nn.NODE_IDAND os.NODE_ID = nn.OLD_NODE_IDAND bc.BOM_ID = nToBomId;-- Relink OS Base EnvironmentsINSERT INTO OS_BASE_ENV ( OS_ID, BASE_ENV_ID )SELECT os.OS_ID AS OS_ID,be.BASE_ENV_ID AS BASE_ENV_IDFROM OPERATING_SYSTEMS os,NETWORK_NODES nn,BOM_CONTENTS bc,OS_BASE_ENV osbe,BASE_ENV beWHERE osbe.OS_ID = os.OLD_OS_IDAND osbe.BASE_ENV_ID = be.OLD_BASE_ENV_IDAND bc.NODE_ID = nn.NODE_IDAND os.NODE_ID = nn.NODE_IDAND bc.BOM_ID = nToBomId;-- Copy OS ContentsINSERT INTO OS_CONTENTS ( OS_ID, SEQ_NUM, PROD_ID, PRODUCT_COMMENTS, CHANGE_LOG_FLAG, IS_NODE_SPEC, NOT_XML )SELECT os.OS_ID AS OS_ID,osc.SEQ_NUM,osc.PROD_ID,osc.PRODUCT_COMMENTS,osc.CHANGE_LOG_FLAG,osc.IS_NODE_SPEC,osc.NOT_XMLFROM OPERATING_SYSTEMS os,BOM_CONTENTS bc,OS_CONTENTS oscWHERE bc.NODE_ID = os.NODE_IDAND osc.OS_ID = os.OLD_OS_IDAND bc.BOM_ID = nToBomId;-- Copy Processes ConfigINSERT INTO PROCESSES_CONFIG ( PROC_ID, PROD_ID, OS_ID, PKG_HEALTH_TAG, CMD_INTERFACE, PKG_OWNER, IS_INTERFACE, PROC_DESCRIPTION, PKG_ID )SELECT pc.PROC_ID,pc.PROD_ID,os.OS_ID AS OS_ID,pc.PKG_HEALTH_TAG,pc.CMD_INTERFACE,pc.PKG_OWNER,pc.IS_INTERFACE,pc.PROC_DESCRIPTION,pc.PKG_IDFROM OPERATING_SYSTEMS os,BOM_CONTENTS bc,PROCESSES_CONFIG pcWHERE bc.NODE_ID = os.NODE_IDAND pc.OS_ID = os.OLD_OS_IDAND bc.BOM_ID = nToBomId;-- Copy Hide Products/PatchesINSERT INTO HIDE_PRODUCTS (OS_ID, PROD_ID)SELECT os.OS_ID,hp.PROD_IDFROM HIDE_PRODUCTS hp,OPERATING_SYSTEMS os,BOM_CONTENTS bcWHERE hp.OS_ID = os.OLD_OS_IDAND bc.NODE_ID = os.NODE_IDAND bc.BOM_ID = nToBomId ;END Copy_BOM_Contents;/*--------------------------------------------------------------------------------------------------*/PROCEDURE Copy_Network_Node ( nFromBomId IN BOMS.BOM_ID%TYPE,nToBomId IN BOMS.BOM_ID%TYPE,nFromNodeId IN NUMBER DEFAULT 0 ) ISBEGIN/*--------------- Business Rules Here -------------------*//*-------------------------------------------------------*/-- Copy Network NodesINSERT INTO NETWORK_NODES ( NODE_ID, NODE_TYPE_ID, NODE_NAME, NODE_COMMENTS, OLD_NODE_ID, NODE_VERSION, NODE_LIFE_CYCLE )SELECT SEQ_NODE_ID.NEXTVAL AS NODE_ID,nn.NODE_TYPE_ID,nn.NODE_NAME,nn.NODE_COMMENTS,nn.NODE_ID AS OLD_NODE_ID,nn.NODE_VERSION,nn.NODE_LIFE_CYCLEFROM NETWORK_NODES nn,BOM_CONTENTS bcWHERE bc.NODE_ID = nn.NODE_IDAND (( bc.NODE_ID = bc.NODE_ID AND nFromNodeId = 0 ) OR( bc.NODE_ID = nFromNodeId AND nFromNodeId != 0 ))AND bc.BOM_ID = nFromBomId;-- Relink Bom ContentsINSERT INTO BOM_CONTENTS ( BOM_ID, NODE_ID, SEQ_NUM)SELECT bo.BOM_ID,nn.NODE_ID,bc.SEQ_NUMFROM BOMS bo,BOM_CONTENTS bc,NETWORK_NODES nnWHERE bc.BOM_ID = bo.OLD_BOM_IDAND bc.NODE_ID = nn.OLD_NODE_IDAND (( nn.OLD_NODE_ID = nn.OLD_NODE_ID AND nFromNodeId = 0 ) OR( nn.OLD_NODE_ID = nFromNodeId AND nFromNodeId != 0 ))AND bo.BOM_ID = nToBomId;END Copy_Network_Node;/*--------------------------------------------------------------------------------------------------*/PROCEDURE Paste_Products ( sProdIdList IN VARCHAR2,nFromOsId IN OS_CONTENTS.OS_ID%TYPE,nToOsId IN OS_CONTENTS.OS_ID%TYPE ) ISMaxSeqNum NUMBER;BEGIN/*--------------- Business Rules Here -------------------*//*-------------------------------------------------------*/-- Get Max seq_numSELECT COUNT(*) INTO MaxSeqNumFROM OS_CONTENTS oscWHERE osc.OS_ID = nToOsId;-- Copy OS ContentsINSERT INTO OS_CONTENTS ( OS_ID, SEQ_NUM, PROD_ID, PRODUCT_COMMENTS, CHANGE_LOG_FLAG )SELECT nToOsId AS OS_ID,ROWNUM + MaxSeqNum,osc.PROD_ID,osc.PRODUCT_COMMENTS,osc.CHANGE_LOG_FLAGFROM OS_CONTENTS oscWHERE osc.OS_ID = nFromOsIdAND osc.PROD_ID IN (SELECT *FROM THE ( SELECT CAST( IN_LIST_NUMBER( sProdIdList ) AS DEPMGR_NUMBER_TAB_t ) FROM dual ));-- Copy Processes ConfigINSERT INTO PROCESSES_CONFIG ( PROC_ID, PROD_ID, OS_ID, PKG_HEALTH_TAG, CMD_INTERFACE )SELECT pc.PROC_ID,pc.PROD_ID,nToOsId AS OS_ID,pc.PKG_HEALTH_TAG,pc.CMD_INTERFACEFROM OPERATING_SYSTEMS os,BOM_CONTENTS bc,PROCESSES_CONFIG pcWHERE bc.NODE_ID = os.NODE_IDAND pc.OS_ID = os.OLD_OS_IDAND bc.BOM_ID = nFromOsIdAND pc.PROD_ID IN (SELECT *FROM THE ( SELECT CAST( IN_LIST_NUMBER( sProdIdList ) AS DEPMGR_NUMBER_TAB_t ) FROM dual ));EXCEPTIONWHEN DUP_VAL_ON_INDEXTHENRAISE_APPLICATION_ERROR (-20000, 'Cannot Paste Duplicate Product.');END Paste_Products;/*--------------------------------------------------------------------------------------------------*/PROCEDURE Paste_Products_To_Base_Env ( sProdIdList IN VARCHAR2,nFromOsId IN OS_CONTENTS.OS_ID%TYPE,nToBaseEnvId IN BASE_ENV.BASE_ENV_ID%TYPE ) ISMaxSeqNum NUMBER;BEGIN/*--------------- Business Rules Here -------------------*//*-------------------------------------------------------*/-- Get Max seq_numSELECT COUNT(*) INTO MaxSeqNumFROM BASE_ENV_CONTENTS becWHERE bec.BASE_ENV_ID = nToBaseEnvId;-- Copy OS ContentsINSERT INTO BASE_ENV_CONTENTS ( BASE_ENV_ID, PROD_ID, BASE_ENV_COMMENTS, SEQ_NUM )SELECT nToBaseEnvId AS BASE_ENV_ID,osc.PROD_ID,osc.PRODUCT_COMMENTS,ROWNUM + MaxSeqNumFROM OS_CONTENTS oscWHERE osc.OS_ID = nFromOsIdAND osc.PROD_ID IN (SELECT *FROM THE ( SELECT CAST( IN_LIST_NUMBER( sProdIdList ) AS DEPMGR_NUMBER_TAB_t ) FROM dual ));EXCEPTIONWHEN DUP_VAL_ON_INDEXTHENRAISE_APPLICATION_ERROR (-20000, 'Cannot Paste Duplicate Product.');END Paste_Products_To_Base_Env;/*--------------------------------------------------------------------------------------------------*/PROCEDURE Paste_Os ( sOsIdList IN VARCHAR2,nFromNodeId IN OPERATING_SYSTEMS.NODE_ID%TYPE,nToNodeId IN OPERATING_SYSTEMS.NODE_ID%TYPE ) ISToBomId NUMBER;BEGIN/*--------------- Business Rules Here -------------------*//*-------------------------------------------------------*/-- Get ToBomIdSELECT bc.BOM_ID INTO ToBomIdFROM BOM_CONTENTS bcWHERE bc.NODE_ID = nToNodeId;-- Copy OSINSERT INTO OPERATING_SYSTEMS ( OS_ID, OS_NAME, NODE_ID, OS_COMMENTS, OLD_OS_ID )SELECT SEQ_OS_ID.NEXTVAL AS OS_ID,os.OS_NAME,nToNodeId AS NODE_ID,os.OS_COMMENTS,os.OS_ID AS OLD_OS_IDFROM OPERATING_SYSTEMS osWHERE os.NODE_ID = nFromNodeIdAND os.OS_ID IN (SELECT *FROM THE ( SELECT CAST( IN_LIST_NUMBER( sOsIdList ) AS DEPMGR_NUMBER_TAB_t ) FROM dual ));-- Paste OS ContentsPaste_OS_Contents();-- Finnaly Clean old_id columnsClean_Old_Id_Column();EXCEPTIONWHEN DUP_VAL_ON_INDEXTHENRAISE_APPLICATION_ERROR (-20000, 'Cannot Paste Duplicate Operating Systems.');END Paste_Os;/*--------------------------------------------------------------------------------------------------*/PROCEDURE Paste_Node ( sNodeIdList IN VARCHAR2,nFromBomId IN OPERATING_SYSTEMS.NODE_ID%TYPE,nToBomId IN OPERATING_SYSTEMS.NODE_ID%TYPE ) ISCURSOR curNodeName IS -- Check Node Name DuplicatesSELECT nn.NODE_IDFROM (SELECT UPPER( nn.NODE_NAME ) AS NODE_NAMEFROM NETWORK_NODES nnWHERE nn.NODE_ID IN (SELECT *FROM THE ( SELECT CAST( IN_LIST_NUMBER( sNodeIdList ) AS DEPMGR_NUMBER_TAB_t ) FROM dual ))) nname,BOM_CONTENTS bc,NETWORK_NODES nnWHERE bc.NODE_ID = nn.NODE_IDAND bc.BOM_ID = nToBomIdAND UPPER( nn.NODE_NAME ) = UPPER( nname.NODE_NAME );recNodeName curNodeName%ROWTYPE;BEGIN/*--------------- Business Rules Here -------------------*/OPEN curNodeName;FETCH curNodeName INTO recNodeName;IF curNodeName%FOUNDTHENRAISE_APPLICATION_ERROR (-20000, 'Cannot Paste Duplicate Network Nodes.' );END IF;CLOSE curNodeName;/*-------------------------------------------------------*/-- Copy Network NodesINSERT INTO NETWORK_NODES ( NODE_ID, NODE_TYPE_ID, NODE_NAME, NODE_COMMENTS, OLD_NODE_ID, NODE_VERSION, NODE_LIFE_CYCLE )SELECT SEQ_NODE_ID.NEXTVAL AS NODE_ID,nn.NODE_TYPE_ID,nn.NODE_NAME,nn.NODE_COMMENTS,nn.NODE_ID AS OLD_NODE_ID,nn.NODE_VERSION,nn.NODE_LIFE_CYCLEFROM NETWORK_NODES nn,BOM_CONTENTS bcWHERE bc.NODE_ID = nn.NODE_IDAND bc.BOM_ID = nFromBomIdAND nn.NODE_ID IN (SELECT *FROM THE ( SELECT CAST( IN_LIST_NUMBER( sNodeIdList ) AS DEPMGR_NUMBER_TAB_t ) FROM dual ));-- Relink Bom ContentsINSERT INTO BOM_CONTENTS ( BOM_ID, NODE_ID )SELECT nToBomId,nn.NODE_IDFROM NETWORK_NODES nnWHERE NOT nn.OLD_NODE_ID IS NULL;-- Copy OSINSERT INTO OPERATING_SYSTEMS ( OS_ID, OS_NAME, NODE_ID, OS_COMMENTS, OLD_OS_ID )SELECT SEQ_OS_ID.NEXTVAL AS OS_ID,os.OS_NAME,nn.NODE_ID,os.OS_COMMENTS,os.OS_ID AS OLD_OS_IDFROM OPERATING_SYSTEMS os,NETWORK_NODES nnWHERE os.NODE_ID = nn.OLD_NODE_ID;-- Paste OS ContentsPaste_OS_Contents();-- Finnaly Clean old_id columnsClean_Old_Id_Column();END Paste_Node;/*--------------------------------------------------------------------------------------------------*/PROCEDURE Paste_OS_Contents ISBEGIN/*--------------- Business Rules Here -------------------*//*-------------------------------------------------------*//* Base environment copy is a bit more complex when is copied from different bom. TODO *//* CODE HERE */-- Copy OS ContentsINSERT INTO OS_CONTENTS ( OS_ID, SEQ_NUM, PROD_ID, PRODUCT_COMMENTS, CHANGE_LOG_FLAG )SELECT os.OS_ID AS OS_ID,osc.SEQ_NUM,osc.PROD_ID,osc.PRODUCT_COMMENTS,osc.CHANGE_LOG_FLAGFROM OS_CONTENTS osc,OPERATING_SYSTEMS osWHERE osc.OS_ID = os.OLD_OS_ID;-- Copy Processes ConfigINSERT INTO PROCESSES_CONFIG ( PROC_ID, PROD_ID, OS_ID, PKG_HEALTH_TAG, CMD_INTERFACE, PKG_ID )SELECT pc.PROC_ID,pc.PROD_ID,os.OS_ID AS OS_ID,pc.PKG_HEALTH_TAG,pc.CMD_INTERFACE,pc.PKG_IDFROM PROCESSES_CONFIG pc,OPERATING_SYSTEMS osWHERE pc.OS_ID = os.OLD_OS_ID;END Paste_OS_Contents;/*--------------------------------------------------------------------------------------------------*/PROCEDURE Clean_Old_Id_Column ISBEGIN/*--------------- Business Rules Here -------------------*//*-------------------------------------------------------*/-- Clean all old_id columnsUPDATE BOMS SET OLD_BOM_ID = NULL;UPDATE NETWORK_NODES SET OLD_NODE_ID = NULL;UPDATE HARDWARE SET OLD_HARDWARE_NAME_ID = NULL;UPDATE OPERATING_SYSTEMS SET OLD_OS_ID = NULL;UPDATE BASE_ENV SET OLD_BASE_ENV_ID = NULL;END Clean_Old_Id_Column;/*--------------------------------------------------------------------------------------------------*/PROCEDURE Log_Rejection_Trail ( nEnumEntityType IN REJECTION_TRAIL.ENUM_ENTITY_TYPE%TYPE,nEntityId IN REJECTION_TRAIL.ENTITY_ID%TYPE,cIsRejected IN REJECTION_TRAIL.IS_REJECTED%TYPE,nUserId IN REJECTION_TRAIL.USER_ID%TYPE,sComments IN REJECTION_TRAIL.COMMENTS%TYPE DEFAULT NULL ) ISnNextRejectSeq REJECTION_TRAIL.REJECT_SEQ%TYPE;CURSOR curRejectTrail ISSELECT MAX(rt.REJECT_SEQ) AS LAST_REJECT_SEQFROM REJECTION_TRAIL rtWHERE rt.ENTITY_ID = nEntityIdAND rt.ENUM_ENTITY_TYPE = nEnumEntityType;recRejectTrail curRejectTrail%ROWTYPE;BEGIN/*--------------- Business Rules Here -------------------*//*-------------------------------------------------------*/-- Get nNextRejectSeqOPEN curRejectTrail;FETCH curRejectTrail INTO recRejectTrail;nNextRejectSeq := 1;IF recRejectTrail.LAST_REJECT_SEQ IS NOT NULL THENnNextRejectSeq := recRejectTrail.LAST_REJECT_SEQ + 1;END IF;CLOSE curRejectTrail;-- Insert Rejection CommentINSERT INTO REJECTION_TRAIL ( ENUM_ENTITY_TYPE, ENTITY_ID, REJECT_SEQ, IS_REJECTED, COMMENTS, DATE_TIME_STAMP, USER_ID )VALUES (nEnumEntityType,nEntityId,nNextRejectSeq,cIsRejected,sComments,GET_DATETIME(),nUserId);END Log_Rejection_Trail;/*--------------------------------------------------------------------------------------------------*/PROCEDURE Log_Action ( nUserId IN ACTION_TRAIL.USER_ID%TYPE,nEvent IN ACTION_TRAIL.ACTION_EVENT%TYPE,sMethod IN ACTION_TRAIL.METHOD_CALL%TYPE DEFAULT NULL,sActionScript IN ACTION_TRAIL.ACTION_SCRIPT%TYPE DEFAULT NULL,sDescription IN ACTION_TRAIL.ACTION_DESCRIPTION%TYPE DEFAULT NULL ) ISBEGIN/*--------------- Business Rules Here -------------------*//*-------------------------------------------------------*/INSERT INTO ACTION_TRAIL ( ACTION_STAMP, USER_ID, ACTION_EVENT, METHOD_CALL, ACTION_SCRIPT, ACTION_DESCRIPTION )VALUES (TO_DATE( TO_CHAR( SYSDATE,'DD-MON-YYYY HH24:MI:SS' ),'DD-MON-YYYY HH24:MI:SS' ),nUserId,nEvent,sMethod,sActionScript,sDescription);END Log_Action;/*--------------------------------------------------------------------------------------------------*/END pk_Utils;//---------------------------------------------------------- DDL for Procedure ADD_PROCESS--------------------------------------------------------set define off;CREATE OR REPLACE PROCEDURE "ADD_PROCESS" (nProcId IN PROCESSES_CONFIG.PROC_ID%TYPE,sHealthTag IN PROCESSES_CONFIG.PKG_HEALTH_TAG%TYPE,nOsId IN PROCESSES_CONFIG.OS_ID%TYPE,sCmdInterface IN PROCESSES_CONFIG.CMD_INTERFACE%TYPE,sPkgOwner IN PROCESSES_CONFIG.PKG_OWNER%TYPE,sIsInterface IN PROCESSES_CONFIG.IS_INTERFACE%TYPE,nProdId IN PROCESSES_CONFIG.PROD_ID%TYPE,nUserId IN NUMBER) ISPkgName VARCHAR2(100);nPkgId NUMBER;/*Rupesh Release 6/02/2006*/BEGINSELECT PKG.PKG_NAME, PKG.PKG_ID INTO PkgName, nPkgIdFROM PACKAGE_VERSIONS PV, PACKAGES PKGWHERE PV.PV_ID = nProdIdAND PKG.PKG_ID = PV.PKG_ID;INSERT INTO PROCESSES_CONFIG (PROC_ID, PKG_HEALTH_TAG, OS_ID, CMD_INTERFACE, PKG_OWNER, IS_INTERFACE, PROD_ID, PKG_ID )VALUES (nProcId, sHealthTag, nOsId, sCmdInterface, sPkgOwner, sIsInterface, nProdId, nPkgId);-- Log Action --pk_Bom.Log_Os_Trail ( nOsId,'Added process with health tag '|| sHealthTag ||' for product '|| PkgName,nUserId );END ADD_PROCESS;//---------------------------------------------------------- DDL for Procedure LOG_ACTION--------------------------------------------------------set define off;CREATE OR REPLACE PROCEDURE "LOG_ACTION" ( nPvId IN PACKAGE_VERSIONS.PV_ID%TYPE,enumActionTypeName IN ACTION_TYPE.NAME%TYPE,nUserId IN ACTION_LOG.USER_ID%TYPE,sAdditionalComments IN ACTION_LOG.DESCRIPTION%TYPE DEFAULT NULL ) IS/* ---------------------------------------------------------------------------Version: 3.0--------------------------------------------------------------------------- */ActionTypeId NUMBER;BEGIN-- Get Action Type FKSELECT act.ACTTYPE_ID INTO ActionTypeIdFROM ACTION_TYPE actWHERE act.NAME = enumActionTypeName;INSERT INTO ACTION_LOG ( USER_ID, ACTION_DATETIME, PV_ID, DESCRIPTION, ACTTYPE_ID )VALUES ( nUserId, ORA_SYSDATETIME, nPvId, sAdditionalComments, ActionTypeId );END Log_Action;//---------------------------------------------------------- DDL for Procedure NEW_UNIT_TEST--------------------------------------------------------set define off;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,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) := 'DM_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 (sResults <> '') OR ( NOT sResults IS NULL ) THENoutFileName := sResults;ResultsAttachment := sDpkg_path || '/' || outFileName;END IF;-- Remove NOT_DONE entry if existsDELETE FROM UNIT_TESTSWHERE pv_id = nPv_idAND test_types_fk = enumTEST_TYPE_NOT_DONE;--- Add Additional Note ---INSERT INTO UNIT_TESTS ( TEST_ID,PV_ID,TEST_TYPES_FK,TEST_SUMMARY,COMPLETION_DATE,COMPLETED_BY,RESULTS_URL,RESULTS_ATTACHMENT_NAME )VALUES (newID,nPv_id,nTestTypeId,sTest_summary,SYSDATE(),nCompleted_by,ResultsURL,ResultsAttachment );END New_Unit_Test;//---------------------------------------------------------- DDL for Procedure SP_GET_TOC--------------------------------------------------------set define off;CREATE OR REPLACE PROCEDURE "SP_GET_TOC" (toc_Cursor IN OUT PK_BOM_RPT.TOC_Type,Test_Parameter IN varchar2)ASLAST_PAGE INTEGER;BEGINDELETE FROM TableOfContents;COMMIT;EXECUTE IMMEDIATE Test_Parameter;SELECT cast(MAX(PAGE) as INTEGER) into LAST_PAGE FROM TableOfContents;LAST_PAGE := LAST_PAGE + 2;insert into tableofcontentsvalues('Base Configurations',LAST_PAGE,GET_DATE());COMMIT;OPEN toc_Cursor FORSELECT * FROM TableOfContents;END sp_get_toc;//---------------------------------------------------------- DDL for Procedure WRITE_PRODUCTION_HISTORY--------------------------------------------------------set define off;CREATE OR REPLACE PROCEDURE "WRITE_PRODUCTION_HISTORY" (nBomId IN NUMBER,sBomName IN VARCHAR2,sNodeName IN VARCHAR2,sComment IN VARCHAR2,nUserId IN NUMBER)IS/******************************************************************************NAME: WRITE_PRODUCTION_HISTORYPURPOSE:REVISIONS:Ver Date Author Description--------- ---------- --------------- ------------------------------------1.0 6/07/2006 1. Created this procedure.NOTES:Automatically available Auto Replace Keywords:Object Name: WRITE_PRODUCTION_HISTORYSysdate: 6/07/2006Date and Time: 6/07/2006, 11:52:11 AM, and 6/07/2006 11:52:11 AMUsername: (set in TOAD Options, Procedure Editor)Table Name: (set in the "New PL/SQL Object" dialog)******************************************************************************/BEGININSERT INTO PRODUCTION_HISTORY (BOM_ID, BOM_NAME, NODE_NAME, COMMENTS, DATE_TIME_STAMP, USER_ID)VALUES (nBomId, sBomName, sNodeName, sComment, GET_DATETIME(), nUserId);END WRITE_PRODUCTION_HISTORY;//---------------------------------------------------------- DDL for Synonymn APPLICATIONS--------------------------------------------------------CREATE OR REPLACE SYNONYM "APPLICATIONS" FOR "APPLICATIONS";/---------------------------------------------------------- DDL for Synonymn APPLICATION_PAGES--------------------------------------------------------CREATE OR REPLACE SYNONYM "APPLICATION_PAGES" FOR "APPLICATION_PAGES";/---------------------------------------------------------- DDL for Synonymn CONTROL_OBJECTS--------------------------------------------------------CREATE OR REPLACE SYNONYM "CONTROL_OBJECTS" FOR "CONTROL_OBJECTS";/---------------------------------------------------------- DDL for Synonymn CQ_ISSUES--------------------------------------------------------CREATE OR REPLACE SYNONYM "CQ_ISSUES" FOR "CQ_ISSUES";/---------------------------------------------------------- DDL for Synonymn DATA_PERMISSIONS--------------------------------------------------------CREATE OR REPLACE SYNONYM "DATA_PERMISSIONS" FOR "DATA_PERMISSIONS";/---------------------------------------------------------- DDL for Synonymn DATA_TABLES--------------------------------------------------------CREATE OR REPLACE SYNONYM "DATA_TABLES" FOR "DATA_TABLES";/---------------------------------------------------------- DDL for Synonymn JIRA_ISSUES--------------------------------------------------------CREATE OR REPLACE SYNONYM "JIRA_ISSUES" FOR "JIRA_ISSUES";/---------------------------------------------------------- DDL for Synonymn PACKAGES--------------------------------------------------------CREATE OR REPLACE SYNONYM "PACKAGES" FOR "PACKAGES";/---------------------------------------------------------- DDL for Synonymn PACKAGE_DEPENDENCIES--------------------------------------------------------CREATE OR REPLACE SYNONYM "PACKAGE_DEPENDENCIES" FOR "PACKAGE_DEPENDENCIES";/---------------------------------------------------------- DDL for Synonymn PACKAGE_PATCHES--------------------------------------------------------CREATE OR REPLACE SYNONYM "PACKAGE_PATCHES" FOR "PACKAGE_PATCHES";/---------------------------------------------------------- DDL for Synonymn PACKAGE_VERSIONS--------------------------------------------------------CREATE OR REPLACE SYNONYM "PACKAGE_VERSIONS" FOR "PACKAGE_VERSIONS";/---------------------------------------------------------- DDL for Synonymn PAGE_CONTROL_OBJECTS--------------------------------------------------------CREATE OR REPLACE SYNONYM "PAGE_CONTROL_OBJECTS" FOR "PAGE_CONTROL_OBJECTS";/---------------------------------------------------------- DDL for Synonymn PERMISSION_TYPES--------------------------------------------------------CREATE OR REPLACE SYNONYM "PERMISSION_TYPES" FOR "PERMISSION_TYPES";/---------------------------------------------------------- DDL for Synonymn PK_AMUTILS--------------------------------------------------------CREATE OR REPLACE SYNONYM "PK_AMUTILS" FOR "PK_AMUTILS";/---------------------------------------------------------- DDL for Synonymn PK_SECURITY--------------------------------------------------------CREATE OR REPLACE SYNONYM "PK_SECURITY" FOR "PK_SECURITY";/---------------------------------------------------------- DDL for Synonymn PLATFORMS--------------------------------------------------------CREATE OR REPLACE SYNONYM "PLATFORMS" FOR "PLATFORMS";/---------------------------------------------------------- DDL for Synonymn PROCESSES--------------------------------------------------------CREATE OR REPLACE SYNONYM "PROCESSES" FOR "PROCESSES";/---------------------------------------------------------- DDL for Synonymn PRODUCT_COMPONENTS--------------------------------------------------------CREATE OR REPLACE SYNONYM "PRODUCT_COMPONENTS" FOR "PRODUCT_COMPONENTS";/---------------------------------------------------------- DDL for Synonymn PROJECTS--------------------------------------------------------CREATE OR REPLACE SYNONYM "PROJECTS" FOR "PROJECTS";/---------------------------------------------------------- DDL for Synonymn PROJECT_EXTENTIONS--------------------------------------------------------CREATE OR REPLACE SYNONYM "PROJECT_EXTENTIONS" FOR "PROJECT_EXTENTIONS";/---------------------------------------------------------- DDL for Synonymn RELEASE_CONTENT--------------------------------------------------------CREATE OR REPLACE SYNONYM "RELEASE_CONTENT" FOR "RELEASE_CONTENT";/---------------------------------------------------------- DDL for Synonymn RELEASE_TAGS--------------------------------------------------------CREATE OR REPLACE SYNONYM "RELEASE_TAGS" FOR "RELEASE_TAGS";/---------------------------------------------------------- DDL for Synonymn RM_USERS--------------------------------------------------------CREATE OR REPLACE SYNONYM "RM_USERS" FOR "USERS";/---------------------------------------------------------- DDL for Synonymn ROLES--------------------------------------------------------CREATE OR REPLACE SYNONYM "ROLES" FOR "ROLES";/---------------------------------------------------------- DDL for Synonymn ROLE_PRIVILEGES--------------------------------------------------------CREATE OR REPLACE SYNONYM "ROLE_PRIVILEGES" FOR "ROLE_PRIVILEGES";/---------------------------------------------------------- DDL for Synonymn SEED_PACKAGE_NAMES_VERSIONS--------------------------------------------------------CREATE OR REPLACE SYNONYM "SEED_PACKAGE_NAMES_VERSIONS" FOR "SEED_PACKAGE_NAMES_VERSIONS";/---------------------------------------------------------- DDL for Synonymn USERS--------------------------------------------------------CREATE OR REPLACE SYNONYM "USERS" FOR "USERS";/---------------------------------------------------------- DDL for Synonymn USER_APPLICATIONS--------------------------------------------------------CREATE OR REPLACE SYNONYM "USER_APPLICATIONS" FOR "USER_APPLICATIONS";/---------------------------------------------------------- DDL for Synonymn USER_ROLES--------------------------------------------------------CREATE OR REPLACE SYNONYM "USER_ROLES" FOR "USER_ROLES";/---------------------------------------------------------- DDL for Synonymn VTREES--------------------------------------------------------CREATE OR REPLACE SYNONYM "VTREES" FOR "VTREES";/---------------------------------------------------------- DDL for Synonymn VTREES_WORLD--------------------------------------------------------CREATE OR REPLACE SYNONYM "VTREES_WORLD" FOR "VTREES_WORLD";/