SET QUOTED_IDENTIFIER OFF
GO
CREATE VIEW al_bi_hyperlinks_view
AS
SELECT     '<a href=../../opendoc/openDocument.jsp?&sDocType=wid&sDocName=SW%20Installation%20Inventory%20Lookup&lsRefresh=Y&lsSEnter%20License%20Group%20ID='
                       + REPLACE(AL_LICENSE_BLOCK.block_id, ' ', '%20') + '>' + CAST(al_installs_covered_view.installs_covered AS char) + '</a>' AS hyperlink,
                      CAST(AL_LICENSE_BLOCK.block_id AS char) AS parameter,'' as  parameter2, CAST(al_installs_covered_view.installs_covered AS char)  AS displaystring, 'Installs_Covered' AS descript
FROM         AL_LICENSE_BLOCK LEFT OUTER JOIN
                      al_installs_covered_view ON AL_LICENSE_BLOCK.block_id = al_installs_covered_view.block_id
UNION
SELECT   '<a href=../../opendoc/openDocument.jsp?&sDocType=wid&sDocName=License%20Groups%20Lookup&lsRefresh=Y&lsSEnter%20License%20Group%20ID=' + replace(al_compliance_status_view.block_id,' ','%20') + '>' + 'License Group Details'  + '</a>'  as hyperlink,
                CAST(al_compliance_status_view.block_id AS char) as parameter,'' as  parameter2,'License Group Details' as displaystring, 'License_GroupID_Link' as descript
FROM       al_compliance_status_view
UNION
SELECT    '<a href=../../opendoc/openDocument.jsp?&sDocType=wid&sDocName=SW%20Installation%20Inventory%20Lookup%20Product&lsRefresh=Y&lsSProduct%20Installed%20Name=' + cast(replace(al_compliance_status_view.product_name ,' ','%20') as char(257)) + '>' + 'SW Installation Details' +  '</a>' as hyperlink,
                 cast(al_compliance_status_view.product_name as char(257)) as parameter,'' as  parameter2, 'SW Installation Details' as displaystring, 'Product_name_link' as descript
FROM       al_compliance_status_view
UNION
SELECT    '<a href=../../opendoc/openDocument.jsp?&sDocType=wid&sDocName=Hardware%20Inventory%20Lookup&lsRefresh=Y&lsSHost%20Name=' + cast(replace(al_compliance_status_view.host_name,' ','%20')as char(255)) + '>' +cast(al_compliance_status_view.host_name as char(255))+ '</a>' as hyperlink,
                 cast(al_compliance_status_view.host_name as char(255)) as parameter, '' as  parameter2, cast(al_compliance_status_view.host_name as char(255)) as displaystring,'Host_Name_HW_InvLink_Lookup'  as descript
FROM       al_compliance_status_view
UNION
SELECT    '<a href=../../opendoc/openDocument.jsp?&sDocType=wid&sDocName=Hardware%20Inventory&lsRefresh=Y&lsMEnter%20value(s)%20for%20Serial%20Number(Enter%20*%20to%20select%20all%20values):=*&lsMEnter%20value(s)%20for%20Supervisor(Enter%20*%20to%20select%20all%20values):=*&lsMEnter%20value(s)%20for%20Location(Enter%20*%20to%20select%20all%20values):=*&lsMEnter%20value(s)%20for%20Organization(Enter%20*%20to%20select%20all%20values):=*&lsMEnter%20value(s)%20for%20Company(Enter%20*%20to%20select%20all%20values):=*&lsMEnter%20value(s)%20for%20Host%20Name(Enter%20*%20to%20select%20all%20values):='+cast(replace(al_compliance_status_view.host_name,' ','%20')as char(255))+'>'+cast(al_compliance_status_view.host_name as char(255))+'</a>'
               as hyperlink, cast(al_compliance_status_view.host_name as char(255)) as parameter,'' as  parameter2, cast(al_compliance_status_view.host_name as char(255)) as displaystring,'Host_Name_HW_InvLink'  as descript
FROM      al_compliance_status_view
UNION
SELECT    '<a href=../../opendoc/openDocument.jsp?&sDocType=wid&sDocName=SW%20Installation%20Inventory%20Lookup%20Product&lsRefresh=&lsSProduct%20Installed%20Name='+cast(replace(al_compliance_status_view.product_name,' ','%20') as char(257))+'>'+cast(al_compliance_status_view.product_name as char(257))+'</a>'
                as hyperlink,cast(al_compliance_status_view.product_name as char(257)) as parameter,'' as  parameter2, cast(al_compliance_status_view.product_name as char(257)) as displaystring,'Product'  as descript
FROM       al_compliance_status_view
UNION
SELECT   '<a href=../../opendoc/openDocument.jsp?&sDocType=wid&sDocName=License%20Metric%20Lookup%20for%20LCB&lsRefresh=Y&lsSProduct%20Name='+cast(replace(al_product_def.product_name,' ','%20') as char(255))+'&lsSBlock%20Id='+replace(al_license_block.block_id,' ','%20')+'>' +'License Metric Details'+'</a>'
               as hyperlink,CAST(al_license_block.block_id as CHAR) as parameter,cast(al_product_def.product_name as char(255)) as parameter2, 'License Metric Details' as displaystring,'License_Metrics_Lookup_for_LCB'  as descript
FROM      al_license_block INNER JOIN al_link_product_block
    ON al_link_product_block.block_id=al_license_block.block_id
                      INNER JOIN al_product_def
                      ON al_product_def.product_uuid=al_link_product_block.product_uuid
UNION
SELECT    '<a href=../../opendoc/openDocument.jsp?&sDocType=wid&sDocName=License%20Metric%20Lookup%20for%20LCB&lsRefresh=Y&lsSBlock%20Id=*&lsSProduct%20Name='+cast(replace(al_product_def.product_name,' ','%20') as char(255))+'>' +'License Metric Details for'+cast(al_product_def.product_name as char(255))+'</a>'
          as hyperlink,cast(al_product_def.product_name as char(255)) as parameter,'' as parameter2, cast(al_product_def.product_name as char(255)) as displaystring,'License_Metrics_Lookup'  as descript
FROM      al_product_def
UNION
SELECT   '<a href=../../opendoc/openDocument.jsp?&sDocType=wid&sDocName=License%20Inventory%20Lookup%20for%20LCB&lsRefresh=Y&lsSProduct%20Name='+cast(replace(al_product_def.product_name,' ','%20') as char(255))+'&lsSBlock%20Id='+replace(al_license_block.block_id,' ','%20')+'>' +'License Inventory Detail'+'</a>'
          as hyperlink,cast(al_license_block.block_id as char) as parameter,cast(al_product_def.product_name as char(255)) as parameter2, 'License Inventory Detail' as displaystring,'License_Inv_Lookup_for_LCB'  as descript
FROM     al_license_block INNER JOIN al_link_product_block
         ON al_link_product_block.block_id=al_license_block.block_id
          INNER JOIN al_product_def
          ON al_product_def.product_uuid=al_link_product_block.product_uuid
UNION
SELECT   '<a href=../../opendoc/openDocument.jsp?&sDocType=wid&sDocName=License%20Metrics%20Lookup&lsRefresh=Y&lsSLicense%20Name='+cast(replace(al_bi_owned_res_view.resource_name,' ','%20')as char(100))+'>' +'License Metric Details for '+cast(al_bi_owned_res_view.RESOURCE_NAME as char(100))+'</a>'
        as hyperlink,cast(al_bi_owned_res_view.RESOURCE_NAME as char(100)) as parameter,'' as parameter2, cast(al_bi_owned_res_view.RESOURCE_NAME as char(100)) as displaystring,'Usage_Rights_Lookup'  as descript
FROM    al_bi_owned_res_view
UNION
SELECT  '<a href=../../opendoc/openDocument.jsp?&sDocType=wid&sDocName=Software%20Installation%20Inventory%20Lookup%20for%20LCB&lsRefresh=Y&lsSProduct%20Name='+cast(replace(al_product_def.product_name,' ','%20') as char(255))+'&lsSBlock%20Id='+replace(al_license_block.block_id,' ','%20')+'>'+'SW Installation Details'+'</a>'
        as hyperlink,cast(al_license_block.block_id as CHAR) as parameter,cast(al_product_def.product_name as char(255)) as parameter2, 'SW Installation Details' as displaystring,'SW_Inst_Link_for_LCB'  as descript
FROM     al_license_block INNER JOIN al_link_product_block
         ON al_link_product_block.block_id=al_license_block.block_id
          INNER JOIN al_product_def
          ON al_product_def.product_uuid=al_link_product_block.product_uuid
UNION
SELECT   '<a href=../../opendoc/openDocument.jsp?&sDocType=wid&sDocName=Products%20Covered%20Lookup&lsRefresh=Y&lsSLicense%20Name='+cast(replace(al_bi_owned_res_view.resource_name,' ','%20') as char(100))+'>' +'Product Details for '+cast(al_bi_owned_res_view.RESOURCE_NAME as char(100))+'</a>'
        as hyperlink,cast(al_bi_owned_res_view.RESOURCE_NAME as char(100)) as parameter,'' as parameter2, cast(al_bi_owned_res_view.RESOURCE_NAME as char(100)) as displaystring,'Product_Rights_Lookup'  as descript
FROM    al_bi_owned_res_view  
UNION
SELECT    '<a href=../../opendoc/openDocument.jsp?&sDocType=wid&sDocName=License%20Purchases%20Lookup&lsRefresh=Y&lsSLicense%20Name='+cast(replace(al_bi_owned_res_view.resource_name,' ','%20') as char(100))+'>' +'Purchase Details for '+cast(al_bi_owned_res_view.RESOURCE_NAME as char(100))+'</a>'
        as hyperlink,cast(al_bi_owned_res_view.RESOURCE_NAME as char(100)) as parameter,'' as parameter2, cast(al_bi_owned_res_view.RESOURCE_NAME as char(100)) as displaystring,'Purchase_Lookup'  as descript
FROM    al_bi_owned_res_view
UNION
SELECT     '<a href=../../opendoc/openDocument.jsp?&sType=wid&sDocName=Software%20Installation%20Inventory%20Lookup&lsRefresh=Y&lsSProduct%20Installed%20Name=*&lsSHost%20Name='+cast(replace(ca_discovered_hardware.host_name,' ','%20') as char(255))+'>'+cast(ca_discovered_hardware.host_name as char(255))+'</a>'
        as hyperlink,cast(ca_discovered_hardware.host_name as char(255)) as parameter,'' as parameter2, cast(ca_discovered_hardware.host_name as char(255)) as displaystring,'Host_name_SW_Inv_Link'  as descript
FROM    ca_discovered_hardware
UNION
SELECT     '<a href=../../opendoc/openDocument.jsp?&sDocType=wid&sDocName=License%20Groups%20Lookup&lsRefresh=Y&lsSEnter%20License%20Group%20ID='+replace(al_license_block.block_id,' ','%20')+'>' +'License Group Details'  +'</a>'
        as hyperlink,cast(al_license_block.block_id as CHAR) as parameter,'' as parameter2, 'License Group Details'  as displaystring,'Doc_License_GroupID_Link'  as descript
FROM    al_license_block
UNION
SELECT     '<a href=../../opendoc/openDocument.jsp?&sDocType=wid&sDocName=SW%20Installation%20Inventory%20Lookup&lsRefresh=Y&lsSEnter%20License%20Group%20ID='+replace(al_license_block.block_id,' ','%20')+'>' +'License Group Details'  +'</a>'  
        as hyperlink,cast(al_license_block.block_id as CHAR) as parameter,'' as parameter2, 'License Group Details'  as displaystring,'License_GroupID_Link_for_SW_Inst_Inv'  as descript
FROM    al_license_block
GO