---------------
-- VIEWS     --
---------------

drop view H_HPINVDATA
GO
create view H_HPINVDATA
as
select 
    COMPUTER.COMPUTER_SYS_ID,
    COMPUTER.TME_OBJECT_ID,
    COMPUTER.TME_OBJECT_LABEL,
    COMPUTER.COMPUTER_SCANTIME,
    IP_ADDR.IP_ADDR,
    IP_ADDR.IP_HOSTNAME,
    COMPUTER.OS_NAME,
    COMPUTER.OS_MAJOR_VERS,
    COMPUTER.OS_MINOR_VERS,
    H_HP_CPU.CPU_TYPE,
    H_HP_CPU.CPU_SPEED,
    H_HP_SYSTEM.SYSTEM_NAME,
    H_HP_SYSTEM.SYSTEM_ROM_REV,
    H_HP_SYSTEM.FLASH_ROM,
    H_HP_SYSTEM.SERIAL_NUMBER,
    H_HP_SYSTEM.TOTAL_SYS_MEMORY,
    H_HP_SYSTEM.ASSET_TAG
from
    COMPUTER,IP_ADDR,H_HP_CPU,H_HP_SYSTEM
where
    IP_ADDR.COMPUTER_SYS_ID =
    COMPUTER.COMPUTER_SYS_ID
and
    H_HP_CPU.COMPUTER_SYS_ID =
    COMPUTER.COMPUTER_SYS_ID
and
    H_HP_SYSTEM.COMPUTER_SYS_ID =
    COMPUTER.COMPUTER_SYS_ID
and
    COMPUTER.COMPUTER_SYS_ID =
    COMPUTER.COMPUTER_SYS_ID
go

drop view H_HPSWVER
GO
create view H_HPSWVER
as
select
    COMPUTER.COMPUTER_SYS_ID,
    COMPUTER.TME_OBJECT_ID,
    COMPUTER.TME_OBJECT_LABEL,
    COMPUTER.COMPUTER_SCANTIME,
    H_HP_SYSTEM.SYSTEM_NAME,
    H_HP_SYSTEM.SYSTEM_ROM_REV,
    H_HP_SYSTEM.FLASH_ROM,
    H_HP_SOFTWARE_VERSIONS.NAME,
    H_HP_SOFTWARE_VERSIONS.DESCRIPTION,
    H_HP_SOFTWARE_VERSIONS.VERSION,
    H_HP_SYSTEM.RECORD_TIME    
from
    COMPUTER, H_HP_SOFTWARE_VERSIONS,H_HP_SYSTEM
where
    H_HP_SOFTWARE_VERSIONS.COMPUTER_SYS_ID =
    COMPUTER.COMPUTER_SYS_ID
and
    H_HP_SYSTEM.COMPUTER_SYS_ID =
    COMPUTER.COMPUTER_SYS_ID
go

----------------
--- New Views ---
----------------

drop view H_HPCPU
GO
create view H_HPCPU
as
select 
    COMPUTER.COMPUTER_SYS_ID,
    COMPUTER.TME_OBJECT_LABEL,
    COMPUTER.TME_OBJECT_ID,
    H_HP_CPU.CPU_TYPE,
    H_HP_CPU.CPU_NUMBER,
    H_HP_CPU.CPU_SPEED,
    H_HP_CPU.L2_CACHE,
    H_HP_CPU.L3_CACHE,
    H_HP_CPU.RECORD_TIME
from
    COMPUTER,H_HP_CPU
where
    H_HP_CPU.COMPUTER_SYS_ID =
    COMPUTER.COMPUTER_SYS_ID
go


drop view H_HPSLOTS
GO
create view H_HPSLOTS
as
select 
    COMPUTER.COMPUTER_SYS_ID,
    COMPUTER.TME_OBJECT_LABEL,
    COMPUTER.TME_OBJECT_ID,
    H_HP_EISA_PCI_SLOTS.SLOT_NUMBER,
    H_HP_EISA_PCI_SLOTS.BOARD_EISA_PCI_ID,
    H_HP_EISA_PCI_SLOTS.BOARD_NAME,
    H_HP_EISA_PCI_SLOTS.RECORD_TIME
from
    COMPUTER,H_HP_EISA_PCI_SLOTS
where
    H_HP_EISA_PCI_SLOTS.COMPUTER_SYS_ID =
    COMPUTER.COMPUTER_SYS_ID
go

drop view H_HPIDEDEV
GO
create view H_HPIDEDEV
as
select 
    COMPUTER.COMPUTER_SYS_ID,
    COMPUTER.TME_OBJECT_LABEL,
    COMPUTER.TME_OBJECT_ID,
    H_HP_IDE_DEVICES.CONTROLLER_DRIVE,
    H_HP_IDE_DEVICES.NAME,
    H_HP_IDE_DEVICES.FIRMWARE_REV,
    H_HP_IDE_DEVICES.DISK_SIZE,
    H_HP_IDE_DEVICES.SERIAL_NUMBER,
    H_HP_IDE_DEVICES.RECORD_TIME
from
    COMPUTER,H_HP_IDE_DEVICES
where
    H_HP_IDE_DEVICES.COMPUTER_SYS_ID =
    COMPUTER.COMPUTER_SYS_ID
and
   H_HP_IDE_DEVICES.TYPE = 'Disk'
go

drop view H_HPDRVARRY
GO
create view H_HPDRVARRY
as
select 
    COMPUTER.COMPUTER_SYS_ID,
    COMPUTER.TME_OBJECT_LABEL,
    COMPUTER.TME_OBJECT_ID,
    H_HP_DRIVE_ARRAY_CONTROLLER.MODEL,
    H_HP_DA_PHYSICAL.SLOT_PORT_BAY,
    H_HP_DA_PHYSICAL.FIRMWARE_REV,
    H_HP_DA_PHYSICAL.DRIVE_SIZE,
    H_HP_DA_PHYSICAL.HOT_PLUGGABLE
from
    COMPUTER,H_HP_DRIVE_ARRAY_CONTROLLER,H_HP_DA_PHYSICAL
where
    H_HP_DRIVE_ARRAY_CONTROLLER.COMPUTER_SYS_ID =
    COMPUTER.COMPUTER_SYS_ID
and 
    H_HP_DA_PHYSICAL.COMPUTER_SYS_ID =
    COMPUTER.COMPUTER_SYS_ID
go

drop view H_HPSASDRV
GO
create view H_HPSASDRV
as
select 
    COMPUTER.COMPUTER_SYS_ID,
    COMPUTER.TME_OBJECT_LABEL,
    COMPUTER.TME_OBJECT_ID,
    H_HP_SAS_HBA.MODEL,
    H_HP_SAS_PHY_DRV.HBA_INDEX,
    H_HP_SAS_PHY_DRV.DRIVE_SIZE,
    H_HP_SAS_PHY_DRV.DRIVE_SPEED,
    H_HP_SAS_PHY_DRV.DRIVE_FIRMWARE,
    H_HP_SAS_PHY_DRV.DRIVE_TYPE,
    H_HP_SAS_PHY_DRV.HOT_PLUGGABLE
from
    COMPUTER,H_HP_SAS_HBA,H_HP_SAS_PHY_DRV
where
    H_HP_SAS_HBA.COMPUTER_SYS_ID =
    COMPUTER.COMPUTER_SYS_ID
and 
    H_HP_SAS_PHY_DRV.COMPUTER_SYS_ID =
    COMPUTER.COMPUTER_SYS_ID
go

drop view H_HPATADISK
GO
create view H_HPATADISK
as
select 
    COMPUTER.COMPUTER_SYS_ID,
    COMPUTER.TME_OBJECT_LABEL,
    COMPUTER.TME_OBJECT_ID,
    H_HP_ATA_DISK.DISK_INDEX,
    H_HP_ATA_DISK.DISK_SIZE,
    H_HP_ATA_DISK.CONTROLLER_SLOT,
    H_HP_ATA_DISK.DISK_MODEL,
    H_HP_ATA_DISK.DISK_FIRMWARE,
    H_HP_ATA_DISK.DISK_TYPE,
    H_HP_ATA_DISK.SATA_VERSION
from
    COMPUTER,H_HP_ATA_DISK
where
    H_HP_ATA_DISK.COMPUTER_SYS_ID =
    COMPUTER.COMPUTER_SYS_ID
go

drop view H_HPSCSI
GO
create view H_HPSCSI
as
select 
    COMPUTER.COMPUTER_SYS_ID,
    COMPUTER.TME_OBJECT_LABEL,
    COMPUTER.TME_OBJECT_ID,
    H_HP_SCSI_DEVICES.CNTLR_SLOT_SCSI_ID,
    H_HP_SCSI_DEVICES.NAME,
    H_HP_SCSI_DEVICES.FIRMWARE_REV,
    H_HP_SCSI_DEVICES.DISK_SIZE,
    H_HP_SCSI_DEVICES.HOT_PLUGGABLE,
    H_HP_SCSI_DEVICES.RECORD_TIME
from
    COMPUTER,H_HP_SCSI_DEVICES
where
    H_HP_SCSI_DEVICES.COMPUTER_SYS_ID =
    COMPUTER.COMPUTER_SYS_ID
and 
    H_HP_SCSI_DEVICES.TYPE = 'Disk'
go

drop view H_HPFCA
GO
create view H_HPFCA
as
select 
    COMPUTER.COMPUTER_SYS_ID,
    COMPUTER.TME_OBJECT_LABEL,
    COMPUTER.TME_OBJECT_ID,
    H_HP_FCA_CONTROLLER.MODEL,
    H_HP_FCA_PHYSICAL_DRIVES.STORAGE_BOX_INDEX,
    H_HP_FCA_PHYSICAL_DRIVES.DRIVE_INDEX,
    H_HP_FCA_PHYSICAL_DRIVES.DRIVE_SIZE,
    H_HP_FCA_PHYSICAL_DRIVES.FIRMWARE_REV,
    H_HP_FCA_PHYSICAL_DRIVES.RECORD_TIME
from
    COMPUTER,H_HP_FCA_CONTROLLER,H_HP_FCA_PHYSICAL_DRIVES
where
    H_HP_FCA_CONTROLLER.COMPUTER_SYS_ID =
    COMPUTER.COMPUTER_SYS_ID
and
    H_HP_FCA_PHYSICAL_DRIVES.COMPUTER_SYS_ID =
    COMPUTER.COMPUTER_SYS_ID
go

delete from QUERY_VIEWS where VIEW_NAME = 'H_HPINVDATA'
go
delete from QUERY_VIEWS where VIEW_NAME = 'H_HPSWVER'
go
delete from QUERY_VIEWS where VIEW_NAME = 'H_HPCPU'
go
delete from QUERY_VIEWS where VIEW_NAME = 'H_HPSLOTS'
go
delete from QUERY_VIEWS where VIEW_NAME = 'H_HPIDEDEV'
go
delete from QUERY_VIEWS where VIEW_NAME = 'H_HPDRVARRY'
go
delete from QUERY_VIEWS where VIEW_NAME = 'H_HPSASDRV'
go
delete from QUERY_VIEWS where VIEW_NAME = 'H_HPATADISK'
go
delete from QUERY_VIEWS where VIEW_NAME = 'H_HPSCSI'
go
delete from QUERY_VIEWS where VIEW_NAME = 'H_HPFCA'
go

insert into QUERY_VIEWS (VIEW_NAME) values ('H_HPCPU')
insert into QUERY_VIEWS (VIEW_NAME) values ('H_HPINVDATA')
insert into QUERY_VIEWS (VIEW_NAME) values ('H_HPSWVER')
insert into QUERY_VIEWS (VIEW_NAME) values ('H_HPSLOTS')
insert into QUERY_VIEWS (VIEW_NAME) values ('H_HPIDEDEV')
insert into QUERY_VIEWS (VIEW_NAME) values ('H_HPDRVARRY')
insert into QUERY_VIEWS (VIEW_NAME) values ('H_HPSASDRV')
insert into QUERY_VIEWS (VIEW_NAME) values ('H_HPATADISK')
insert into QUERY_VIEWS (VIEW_NAME) values ('H_HPSCSI')
insert into QUERY_VIEWS (VIEW_NAME) values ('H_HPFCA')
go