Rem drv: <create type="pkgbodys" pos="system/system_pkgbody.sql+"/>
Rem
Rem $Header: provision_pkgbody.sql 10-aug-2007.07:56:13 saurgarg Exp $
Rem
Rem provision_pkgbody.sql
Rem
Rem Copyright (c) 2004, 2007, Oracle. All rights reserved.  
Rem
Rem    NAME
Rem      provision_pkgbody.sql - <one-line expansion of the name>
Rem
Rem    DESCRIPTION
Rem      <short description of component this file declares/defines>
Rem
Rem    NOTES
Rem      <other useful comments, qualifications, etc.>
Rem
Rem    MODIFIED   (MM/DD/YY)
Rem    saurgarg    08/10/07 - Adding purge_prov_collection procedure for purging
Rem                           the table entries in mgmt_prov_collection table
Rem    saurgarg    09/20/06 - fix for 5486559
Rem    pshroff     06/30/05 - change the initial value of fractioncomplete to 0
Rem    gsbhatia    07/01/05 - New repmgr header impl 
Rem    rkaggarw    05/25/05 - changes to add assignment subtype
Rem    rmadampa    05/08/05 - add cluster insert proc 
Rem    pshroff     04/30/05 - adding propertyDict to assignment table 
Rem    rmadampa    04/27/05 - data model enhancements 1 
Rem    ashwikum    04/15/05 - Removing def of net config related stored 
Rem                           procedure 
Rem    pshroff     03/16/05 - to fix issues in bug#4221658 
Rem    ashwikum    02/24/05 - Functions for net config value generator 
Rem    pshroff     02/01/05 - adding label/desciption field for assignment 
Rem    prayarot    12/21/04 - Added metric collection table 
Rem    prayarot    12/12/04 - To add Default Image function 
Rem    pshroff     11/29/04 - adding assignmenttype param in assignment 
Rem    pshroff     11/02/04 - Modified insert methods 
Rem    pshroff     10/26/04 - Modified PRELOAD_PROC as per review comments
Rem    pshroff     10/18/04 - Created
Rem

SET ECHO ON
SET FEEDBACK 1
SET NUMWIDTH 10
SET LINESIZE 80
SET TRIMSPOOL ON
SET TAB OFF
SET PAGESIZE 100
SET SERVEROUTPUT ON

CREATE OR REPLACE  PACKAGE BODY "SYSMAN"."MGMT_PROVISION" 
    AS


FUNCTION insert_provision_hw_table (
    p_status              IN VARCHAR2,
    p_hostname            IN VARCHAR2,
    p_macaddr1            IN VARCHAR2,
    p_macaddr2            IN VARCHAR2,
    p_macaddr3            IN VARCHAR2,
    p_macaddr4            IN VARCHAR2,
    p_interfacename1      IN VARCHAR2,
    p_interfacename2      IN VARCHAR2,
    p_interfacename3      IN VARCHAR2,
    p_interfacename4      IN VARCHAR2,
    p_target_type         IN VARCHAR2,
    p_name                IN VARCHAR2 DEFAULT NULL,
    p_desc                IN VARCHAR2 DEFAULT NULL
) RETURN VARCHAR2
AS
    v_hw_id       VARCHAR2(255);
    v_hw_guid     RAW(16);

BEGIN

    INSERT INTO mgmt_prov_hardware(hw_guid, name, description, hostname, 
        mac_address1, mac_address2, mac_address3, mac_address4, 
        interface_name1, interface_name2, interface_name3, interface_name4)  
    VALUES(SYS_GUID(), p_name, p_desc, p_hostname, 
        p_macaddr1, p_macaddr2, p_macaddr3, p_macaddr4, 
        p_interfacename1, p_interfacename2, p_interfacename3, p_interfacename4)
    RETURNING hw_guid INTO v_hw_guid;


    v_hw_id := rawtohex(v_hw_guid);

    INSERT INTO mgmt_prov_tgt_status(prov_tgt_guid, status, 
        prov_target_type)
    VALUES (v_hw_id, p_status, 
        p_target_type);
     
    RETURN v_hw_id;

END insert_provision_hw_table;


FUNCTION insert_provision_cluster_table (
    p_status        IN VARCHAR2,
    p_name          IN VARCHAR2,
    p_desc          IN VARCHAR2 DEFAULT NULL,
    p_target_type   IN VARCHAR2,
    p_imageUrn      IN VARCHAR2
) RETURN VARCHAR2
AS
    v_cluster_guid      RAW(16);
    v_cluster_id        VARCHAR2(32);
BEGIN
    INSERT INTO mgmt_prov_cluster(cluster_guid, name, description, image_cluster_urn)
    VALUES(SYS_GUID(), p_name, p_desc, p_imageUrn)
    RETURNING cluster_guid INTO v_cluster_guid;

    v_cluster_id := rawtohex(v_cluster_guid);

    INSERT INTO mgmt_prov_tgt_status(prov_tgt_guid, status, 
        prov_target_type)
    VALUES (v_cluster_guid, p_status, 
        p_target_type);

    RETURN v_cluster_id;
END insert_provision_cluster_table;


FUNCTION insert_provision_si_table (
    p_status        IN VARCHAR2,
    p_name          IN VARCHAR2,
    p_desc          IN VARCHAR2 DEFAULT NULL,
    p_target_type   IN VARCHAR2,
    p_imageUrn      IN VARCHAR2
) RETURN VARCHAR2
AS
    v_suiteinst_guid      RAW(16);
    v_suiteinst_id        VARCHAR2(32);
BEGIN
    INSERT INTO mgmt_prov_suite_instance(suite_inst_guid, name, description, suite_urn)
    VALUES(SYS_GUID(), p_name, p_desc, p_imageUrn)
    RETURNING suite_inst_guid INTO v_suiteinst_guid;

    v_suiteinst_id := rawtohex(v_suiteinst_guid);

    INSERT INTO mgmt_prov_tgt_status(prov_tgt_guid, status, 
        prov_target_type)
    VALUES (v_suiteinst_guid, p_status, 
        p_target_type);

    RETURN v_suiteinst_id;
END insert_provision_si_table;


-- will return hw_guid column value
-- for the newly added row

FUNCTION insert_provision_op_table (
    p_optype      IN VARCHAR2,
    p_statusmsg   IN VARCHAR2,
    p_jobid       IN VARCHAR2
) RETURN VARCHAR2
AS
    v_op_id       VARCHAR2(255);
    v_op_guid     RAW(16);

BEGIN

    INSERT INTO mgmt_prov_operation(op_guid, creation_time, last_modified_time, 
        fraction_complete, status_msg, op_type, job_id)
    VALUES(SYS_GUID(), SYSTIMESTAMP, SYSTIMESTAMP, 
        0.0, p_statusmsg, p_optype, p_jobid)
    RETURNING op_guid INTO v_op_guid;

    v_op_id := rawtohex(v_op_guid);
    RETURN v_op_id;

END insert_provision_op_table;


PROCEDURE   insert_provision_history_table (
    p_tgt_guid         IN VARCHAR2,
    p_op_guid          IN VARCHAR2,
    p_assignment_guid  IN VARCHAR2,
    p_hostname         IN VARCHAR2 DEFAULT NULL
)
AS
    v_op_guid    RAW(16);
    v_tgt_guid  RAW(16);
    v_assignment_guid RAW(16);

BEGIN

    v_tgt_guid := HEXTORAW(p_tgt_guid);
    v_op_guid := HEXTORAW(p_op_guid);

    if (p_assignment_guid = null) THEN
        v_assignment_guid := null;
    else
        v_assignment_guid := HEXTORAW(p_assignment_guid);
    end if;


    INSERT INTO mgmt_prov_history(prov_tgt_guid, op_guid, assignment_guid, 
        hostname)
    VALUES(v_tgt_guid, v_op_guid, v_assignment_guid, 
        p_hostname);
 
END insert_provision_history_table;



FUNCTION insert_provision_assign_table (
    p_name                     IN VARCHAR2,
    p_description              IN VARCHAR2,
    p_status                   IN VARCHAR2,
    p_type                     IN VARCHAR2,
    p_subtype                  IN VARCHAR2,
    p_username                 IN VARCHAR2,
    p_stage_urn                IN VARCHAR2,
    p_comp_urn                 IN VARCHAR2,
    p_network_urn              IN VARCHAR2,
    p_boot_server_urn          IN VARCHAR2,
    p_starttime                IN TIMESTAMP WITH TIME ZONE,
    p_stage_username           IN VARCHAR2,
    p_stage_password           IN VARCHAR2,
    p_configuration_properties IN MGMT_PROV_ASSIGNMENT.PROPERTIES%TYPE,
    p_suite_guid               IN VARCHAR2,
    p_dependent_on_assignment  IN VARCHAR2,
    p_target_reset_time        IN NUMBER
) RETURN VARCHAR2
AS
    v_assignment_id       VARCHAR2(255);
    v_assignment_guid  RAW(16);

BEGIN

    INSERT INTO mgmt_prov_assignment(assignment_guid, name, description, 
        status, assignment_type, assignment_subtype, username, stage_urn, stage_username, 
        stage_password, component_urn, network_urn, boot_server_urn, 
        properties, start_time, target_reset_time, last_modified_time)
    VALUES(SYS_GUID(), p_name, p_description, 
        p_status, p_type, p_subtype, p_username, p_stage_urn, p_stage_username, 
        p_stage_password, p_comp_urn, p_network_urn, p_boot_server_urn, 
        p_configuration_properties, p_starttime, p_target_reset_time, SYSTIMESTAMP)
    RETURNING assignment_guid INTO v_assignment_guid;
 
    -- the p_suite_guid is set only for assignments that are part of a suite
    -- assignment
    IF p_suite_guid IS NOT NULL THEN
        INSERT INTO mgmt_prov_asn_dependencies(parent_asn_guid, child_asn_guid, 
            dep_on_asn_guid)
        VALUES(p_suite_guid, v_assignment_guid, p_dependent_on_assignment);
    END IF;

    v_assignment_id := rawtohex(v_assignment_guid);
    
    RETURN v_assignment_id;

END insert_provision_assign_table;


FUNCTION insert_prov_default_img_table (
   p_ip_addr_prefix  IN MGMT_PROV_DEFAULT_IMAGE.IP_ADDRESS_PREFIX%TYPE,
   p_name            IN MGMT_PROV_DEFAULT_IMAGE.NAME%TYPE DEFAULT NULL,
   p_desc            IN MGMT_PROV_DEFAULT_IMAGE.DESCRIPTION%TYPE DEFAULT NULL
) RETURN VARCHAR2
AS
    v_default_img_id       VARCHAR2(255);
    v_default_img_guid     RAW(16);

BEGIN

    INSERT INTO mgmt_prov_default_image(default_guid, ip_address_prefix, 
        last_modified_time, name, description)
    VALUES(SYS_GUID(), p_ip_addr_prefix, 
        SYSTIMESTAMP, p_name, p_desc)
    RETURNING default_guid INTO v_default_img_guid;

    v_default_img_id := rawtohex(v_default_img_guid);

    RETURN v_default_img_id;

END insert_prov_default_img_table;


PROCEDURE  call_provision_job (
     p_hostname IN VARCHAR2
)
AS

l_job_targets MGMT_JOB_TARGET_LIST;
l_job_params MGMT_JOB_PARAM_LIST;
l_job_name varchar2(64);
l_job_id RAW(16);
l_execution_id RAW(16);
l_schedule MGMT_JOB_SCHEDULE_RECORD;

op_guid VARCHAR2(255);

BEGIN

    -- Submit the parameters for the job. All parameters that your job
    -- needs must be specified here
    l_job_params := MGMT_JOB_PARAM_LIST();

    l_job_params.extend(1);
    l_job_params(l_job_params.last) := MGMT_JOB_PARAM_RECORD('hostname', MGMT_JOBS.PARAM_TYPE_SCALAR, p_hostname, null);

    l_job_name := 'Provision-' || SYSTIMESTAMP; 

    l_job_targets := MGMT_JOB_TARGET_LIST();

    -- Submit the targets for the job. Extend jobTargets by as many
    -- targets as your job needs (or can handle)
    l_job_targets.extend( 1);

    l_job_targets(1) := MGMT_JOB_TARGET_RECORD(p_hostname, 'host');


    -- Set up the schedule for the job. The schedule below is for
    -- an immediate, one-time job. It executes in the timezone of
    -- the repository. For examples of other types of schedules,
    -- comment out this section and uncomment one of the
    -- other sections below
    l_schedule := MGMT_JOBS.get_job_schedule_record(MGMT_JOBS.ONE_TIME_FREQUENCY_CODE,
                                         SYSDATE,
                                         null, 0, 0, 0, null, null,
                                         MGMT_JOBS.TIMEZONE_REPOSITORY,
                                         0, 0, null);

    MGMT_JOBS.submit_job(l_job_name,
                         'ECM Snapshot Provision Job',
                         'ProvisionJob',
                         l_job_targets,
                         l_job_params,
                         l_schedule,
                         l_job_id,
                         l_execution_id,
                         null, 1);


    commit;


END call_provision_job;



PROCEDURE preload_callback (
    v_input IN SMP_EMD_NVPAIR_ARRAY,
    v_preLoadOutput OUT SMP_EMD_NVPAIR_ARRAY) IS

-- obtained from v_input
l_target_guid MGMT_TARGETS.TARGET_GUID%TYPE;
l_snapshot_type MGMT_ECM_GEN_SNAPSHOT.SNAPSHOT_TYPE%TYPE := NULL;
l_start_timestamp MGMT_ECM_GEN_SNAPSHOT.START_TIMESTAMP%TYPE := SYSDATE;

-- returned in v_preLoadOutput
l_snapshot_guid VARCHAR2(64) := NULL;

-- obtained from mgmt_targets
l_target_name MGMT_TARGETS.TARGET_NAME%TYPE := NULL;
l_target_type MGMT_TARGETS.TARGET_TYPE%TYPE := NULL;
l_display_target_name MGMT_TARGETS.DISPLAY_NAME%TYPE := NULL;
l_display_target_type MGMT_TARGETS.TYPE_DISPLAY_NAME%TYPE := NULL;

-- local variables
l_input_name VARCHAR(256) := NULL;
l_null_description CONSTANT MGMT_ECM_GEN_SNAPSHOT.DESCRIPTION%TYPE := NULL;
l_null_message CONSTANT MGMT_ECM_GEN_SNAPSHOT.MESSAGE%TYPE := NULL;
l_null_elapsed_time CONSTANT MGMT_ECM_GEN_SNAPSHOT.ELAPSED_TIME%TYPE := -1;
l_null_creator CONSTANT MGMT_ECM_GEN_SNAPSHOT.CREATOR%TYPE := NULL;

op_guid VARCHAR2(255);

BEGIN

 FOR i IN 1..v_input.COUNT
  LOOP
    l_input_name := UPPER(v_input(i).name);
    IF (l_input_name = ECM_CT.G_TARGET_GUID) THEN
      l_target_guid := HEXTORAW(v_input(i).value);
      END IF;
   END LOOP;   
  BEGIN
    SELECT target_name, target_type, display_name, type_display_name
      INTO l_target_name, l_target_type, l_display_target_name, l_display_target_type
      FROM mgmt_targets
      WHERE (target_guid = l_target_guid);

  -- call Provision Job with passing the hostname now
     CALL_PROVISION_JOB(l_target_name);

  -- bail out when target doesn't exist
  EXCEPTION
    WHEN NO_DATA_FOUND THEN
      RAISE MGMT_GLOBAL.target_does_not_exist;

  END;
  v_preLoadOutput := SMP_EMD_NVPAIR_ARRAY();

END preload_callback;

PROCEDURE PURGE_PROV_COLLECTION( pcb_params IN OUT MGMT_PURGE_CALLBACK_PARAMS)
IS
  l_rows_purged NUMBER;
BEGIN
  dbms_output.put_line('The row count is :');
  SELECT count(*) INTO l_rows_purged
  from MGMT_PROV_COLLECTION
  where to_date(COLLECTED_TIME, 'DY MON dd HH24:MI:SS YYYY') < (CURRENT_TIMESTAMP - 1);

  DELETE FROM MGMT_PROV_COLLECTION
  where to_date(COLLECTED_TIME, 'DY MON dd HH24:MI:SS YYYY') < (CURRENT_TIMESTAMP - 1);

  pcb_params.callback_result := 0;
  pcb_params.rows_processed  := l_rows_purged;
  pcb_params.error_code      := 0;
  pcb_params.error_msg       := NULL;

  COMMIT;

EXCEPTION
WHEN OTHERS THEN
  RAISE;
END PURGE_PROV_COLLECTION;

END MGMT_PROVISION;
/

SET SERVEROUTPUT OFF