Rem
Rem $Header: ecm_db2xml_pkgbody.sql 23-jul-2007.15:08:55 rrawat Exp $
Rem
Rem ecm_db2xml_pkgbody.sql
Rem
Rem Copyright (c) 2002, 2007, Oracle. All rights reserved.  
Rem
Rem    NAME
Rem      ecm_db2xml_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    rrawat      07/23/07 - Backport rrawat_bug-5977153 from main
Rem    rrawat      06/28/07 - Bug-5977153
Rem    goramamo    10/23/02 - removed mount time
Rem    rmenon      06/26/02 - added nic and fs mount details logic
Rem    rmenon      06/17/02 - changed vendor order by clause to include new columns
Rem    rpinnama    05/15/02 - rpinnama_reorg_rep_scripts
Rem    rpinnama    05/15/02 - Created
Rem

CREATE OR REPLACE package body db2XML is

  procedure getSystemSummary (snapshotID RAW, systemSummary in out hcSystemSummaryRef)  is
  begin 
    OPEN systemSummary FOR select SNAPSHOT_GUID,HOSTNAME,DOMAIN,CC_NAME,CC_PARTNER_INFO,CC_SOFTWARE_VERSION, decode( NVL(to_char(CC_SOFTWARE_BUILD_DATE),'-1'), '-1' ,'', to_char(CC_SOFTWARE_BUILD_DATE ,'YYYY-MM-DD HH24:MI:SS') || ' (GMT)' )  as CC_SOFTWARE_BUILD_DATE from mgmt_hc_system_summary where snapshot_guid = snapshotID; 
  end;

  procedure getHardwareInfo (snapshotID RAW, hardwareInfo in out hcHardwareInfoRef)  is
  begin 
    OPEN hardwareInfo FOR select * from mgmt_hc_hardware_master where snapshot_guid = snapshotID; 
  end;

  procedure getCPUDetails (snapshotID RAW, cpuDetails in out hcCPUDetailsRef)  is
  begin 
    OPEN cpuDetails FOR select * from mgmt_hc_cpu_details where snapshot_guid = snapshotID order by vendor_name, ecache_in_mb, impl, mask, revision; 
  end;

  procedure getIOCardDetails (snapshotID RAW, iocardDetails in out hcIOCardDetailsRef)  is
  begin 
    OPEN iocardDetails FOR select * from mgmt_hc_iocard_details where snapshot_guid = snapshotID order by name, freq_in_mhz, bus; 
  end;

  procedure getNICDetails (snapshotID RAW, nicDetails in out hcNICDetailsRef)  is
  begin 
    OPEN nicDetails FOR select * from mgmt_hc_nic_details where snapshot_guid = snapshotID order by name, flags, mask, mac_address; 
  end;

  procedure getOSSummary (snapshotID RAW, osSummary in out hcOSSummaryRef)  is
  begin 
    OPEN osSummary FOR select * from mgmt_hc_os_summary where snapshot_guid = snapshotID; 
  end;

  procedure getOSProperties (snapshotID RAW, -- p_type VARCHAR2,
                             osProperties in out hcOSPropertiesRef)  is
  begin 
    OPEN osProperties FOR select * from mgmt_hc_os_properties
           where snapshot_guid = snapshotID
           -- and type = p_type
           order by type, name; 
  end;

  procedure getOSComponents (snapshotID RAW, osComponents in out hcOSComponentsRef)  is
  begin 
    OPEN osComponents FOR select SNAPSHOT_GUID,NAME,TYPE,VERSION,DESCRIPTION, decode( NVL(to_char(INSTALLATION_DATE),'-1'), '-1' ,'', to_char(INSTALLATION_DATE ,'YYYY-MM-DD HH24:MI:SS') || ' (GMT)' )  as INSTALLATION_DATE from mgmt_hc_os_components where snapshot_guid = snapshotID order by type, name, version, installation_date; 
  end;

  procedure getFSMountDetails (snapshotID RAW, fsMount in out hcFSMountRef) is
  begin 
    OPEN fsMount FOR select * from mgmt_hc_fs_mount_details where snapshot_guid = snapshotID order by resource_name, mount_location, type, mount_options; 
  end;

  procedure getVendorSoftwareSummary (vendorSoftwareID RAW, vendorSoftwareSummary in out hcVendorSoftwareSummaryRef) is
  begin 
    OPEN vendorSoftwareSummary FOR select VENDOR_SOFTWARE_GUID,SNAPSHOT_GUID,NAME,VENDOR_NAME,VERSION, decode( NVL(to_char(INSTALLATION_DATE),'-1'), '-1' ,'', to_char(INSTALLATION_DATE ,'YYYY-MM-DD HH24:MI:SS') || ' (GMT)' )  as INSTALLATION_DATE ,INSTALLED_LOCATION,DESCRIPTION,VENDOR_SOFTWARE_SPECIFIC_INFO from mgmt_hc_vendor_sw_summary where vendor_software_guid = vendorSoftwareID order by vendor_name, name, version, installation_date, installed_location; 
  end;

  procedure getVendorSoftwareComponents (vendorSoftwareID RAW, vendorSoftwareComponents in out hcVendorSoftwareComponentsRef) is
  begin 
    OPEN vendorSoftwareComponents FOR select VENDOR_SOFTWARE_GUID,NAME, TYPE, VERSION, DESCRIPTION,  decode( NVL(to_char(INSTALLATION_DATE),'-1'), '-1' ,'', to_char(INSTALLATION_DATE ,'YYYY-MM-DD HH24:MI:SS') || ' (GMT)' )  as INSTALLATION_DATE from mgmt_hc_vendor_sw_components where vendor_software_guid = vendorSoftwareID order by type, name, version;
  end;
end db2XML;
/
show errors;