set define off
-- before executing this SQL script please perform following actions:
-- connect to SQL*Plus as PORTAL
-- execute provsyns.sql under $ORACLE_HOME/portal/admin/plsql/wwc - enter SCOTT as parameter
-- create synonym SCOTT.WWSBR_ALL_ITEMS for WWSBR_ALL_ITEMS;
-- create synonym SCOTT.WWSBR_ALL_CONTENT_AREAS for WWSBR_ALL_CONTENT_AREAS;
-- grant select on WWSBR_ALL_ITEMS to SCOTT;
-- grant select on WWSBR_ALL_CONTENT_AREAS to SCOTT;
-- connect to SQL*Plus as SCOTT
-- as SCOTT do: grant execute on show_page_for_item to public;
-- 
CREATE OR REPLACE PROCEDURE SCOTT.SHOW_PAGE_FOR_ITEM
   ( p_itemid IN integer,
     p_siteid IN integer,
     p_pageid IN integer,
     p_language IN varchar2 default wwctx_api.get_nls_language,
     p_dad IN varchar2 default 'portal',
     p_schema IN varchar2 default 'PORTAL')
   IS
-- Purpose: Render a link to a page that contains an item
-- Note that this example assumes that the item appears on a page and not on a tab
-- If the item appears on a tab you will have to generate a more complex value by querying WWSBR_ALL_FOLDERS for
--   the container page
--
-- MODIFICATION HISTORY
-- Person      Date     Comments
-- ---------   ------   -------------------------------------------
-- JSILVER     1/12/2004 Created

    itemTitle wwsbr_all_items.display_name%TYPE;
    itemPageId varchar2(32) := p_siteid || ',' || p_pageid;
BEGIN
   if 'X' || wwpro_api_parameters.get_value('_pageid','a') = 'X' then
      -- wwpro_api_parameters.get_value returns the value of the _pageid parameter for the current page
      -- This function will return null when called from a search portlet
      -- If the value is not null, the procedure assumes that the item is being rendered on its container page or a page portlet, so the link won't be displayed
      select i.display_name
         into itemTitle
         from  wwsbr_all_items i
         where i.id = p_itemid
         and   i.caid = p_siteid
         and   i.is_current_version = 1 -- for search only current version is returned
         and   i.active = 1 -- for search only active items are returned
            and    (i.language = p_language
                    or ( exists -- a row for the item in the page group default language
                          (select pg.id
                           from  wwsbr_all_content_areas pg
                           where pg.id = i.caid
                           and   pg.default_language = i.language
                          )
                         and not exists -- a row for the item in the current language
                          (select i2.id
                           from  wwsbr_all_items i2
                           where i2.id = i.id
                           and   i2.language = p_language
                           and   i2.is_current_version = i.is_current_version
                           and   i2.active = i.active
                          )
                       )
                    );
      -- Generate link
      -- href format is "/portal/page?_pageid=<p_siteid>,<p_pageid>&_dad=<p_dad>&_schema=<p_schema>"
      htp.p('<a href="/technology/portal/page?_pageid='
                || p_siteid || ',' || p_pageid
                || '&_dad=' || p_dad
                || '&_schema=' || p_schema
                || '">' || '<font class="titleorimageid1siteid0">' || itemTitle || '</font>' || '</a>');
   end if;
exception
      when others then htp.p(sqlerrm);
END; -- show_page_for_item
/