create or replace package body odwaslib
is

/*****************************************************************************************
Purpose         Maintain and access the ODWA Search (Query) Library

Usage

Remarks

Revision history
When        Who                                Construct
  Revision  What
------------------------------------------------------------------------------------------
$revision_history$
19-Nov-2002   Kannan Parthasarathy
              - Fixed OAC P1 violations.
16-aug-2001   Lucas Jellema
  1.1         - implement fixes in Query Library functionality:
              - query label is mandatory but does not give error when saved.
              - make item search category mandatory
              - make execute query a button- show rounded Save button (instead of rectangular button)
              - make category a mandatory property
              - enforce mandatory items
              - add rounded Search button to query property palette
              - Publish to Public is not longer mandatory
              - Categories with spaces in their names are now catered for
30-mar-2001   Lucas Jellema
  1.0
*****************************************************************************************/
/* $add_to_revision_history
$
*/

--
-- private constants
--
REVISION_LABEL        constant varchar2(30) := '$x.y:: 1.1            $';
PACKAGE_NAME          constant varchar2(30) := 'ODWASLIB';

procedure js_exec
is
begin
  htp.p
  (' <SCRIPT LANGUAGE="JavaScript"><!--
  function execQuery( queryId)
  {

    opener.focus();
    opener.document.queryForm.qQueryId.value = queryId;
    opener.document.queryForm.submit();
    top.close();
  }
    //--></SCRIPT>
    <NOSCRIPT>'|| Rob_msg.getmsg(rob_msg.ACC005_NOSCRIPT,'','','','')||'</NOSCRIPT>
  ');
end; -- js_exec

-- this procedure will retrieve the query criteria saved with query p_qlb_id
-- and subsequently call queryFrame with p_run_mode ='execute' (to immediately execute the query)
-- and all values for the query parameters
procedure loadQueryCriteria
( p_session_id in number
, p_qlb_id in number
) is
  cursor c_qlb
         ( b_qlb_id in number
         ) is
    select distinct
           qlb.category
    ,      qlb.description
    ,      qlb.basic
    ,      qlb.advanced
    ,      qlb.audit_tab
    ,      qlb.version
    ,      qlb.label
    ,      qlb.published
    ,      qlb.owner
    ,      qlb.last_date_changed
    ,      cdwpbase.get_tag_value(qlb.basic,'name','{','}') name
    ,      cdwpbase.get_tag_value(qlb.basic,'workareairid','{','}') workareairid
    ,      cdwpbase.get_tag_value(qlb.basic,'elementtype','{','}') elementtype
    ,      cdwpbase.get_tag_value(qlb.basic,'casesensitive','{','}') casesensitive
    ,      cdwpbase.get_tag_value(qlb.basic,'folderirid','{','}') folderirid
    ,      cdwpbase.get_tag_value(qlb.basic,'folderivid','{','}') folderivid
    ,      cdwpbase.get_tag_value(qlb.basic,'tipversionsonly','{','}') tipversionsonly
    ,      cdwpbase.get_tag_value(qlb.basic,'includenested','{','}') includenested
    ,      cdwpbase.get_tag_value(qlb.advanced,'where','{','}')  where_clause
    ,      cdwpbase.get_tag_value(qlb.advanced,'ivid','{','}')  ivid
    ,      cdwpbase.get_tag_value(qlb.advanced,'irid','{','}')  irid
    ,      cdwpbase.get_tag_value(qlb.advanced,'txttype','{','}')  txttype
    ,      cdwpbase.get_tag_value(qlb.advanced,'txtcontains','{','}')  txtcontains
    ,      cdwpbase.get_tag_value(qlb.advanced,'filecontains','{','}')  filecontains
    ,      cdwpbase.get_tag_value(qlb.advanced,'deptype','{','}')  deptype
    ,      cdwpbase.get_tag_value(qlb.advanced,'globalname','{','}')  globalname
    ,      cdwpbase.get_tag_value(qlb.advanced,'globalnametype','{','}')  globalnametype
    ,      cdwpbase.get_tag_value(qlb.advanced,'usedbyivid','{','}')  usedbyivid
    ,      cdwpbase.get_tag_value(qlb.version,'checkstate','{','}')  checkstate
    ,      cdwpbase.get_tag_value(qlb.version,'rootversionsonly','{','}')  rootversionsonly
    ,      cdwpbase.get_tag_value(qlb.version,'versionlabel','{','}')  versionlabel
    ,      cdwpbase.get_tag_value(qlb.version,'branchirid','{','}')   branchirid
    ,      cdwpbase.get_tag_value(qlb.version,'cfgivid','{','}')  cfgivid
    ,      cdwpbase.get_tag_value(qlb.version,'checknotescontain','{','}')  checknotescontain
    ,      cdwpbase.get_tag_value(qlb.version,'checkoutuser','{','}')  checkoutuser
    ,      cdwpbase.get_tag_value(qlb.version,'checkedoutafter','{','}')  checkedoutafter
    ,      cdwpbase.get_tag_value(qlb.version,'checkedoutbefore','{','}')  checkedoutbefore
    ,      cdwpbase.get_tag_value(qlb.version,'checkinuser','{','}')  checkinuser
    ,      cdwpbase.get_tag_value(qlb.version,'checkedinafter','{','}')  checkedinafter
    ,      cdwpbase.get_tag_value(qlb.version,'checkedinbefore','{','}')  checkedinbefore
    ,      cdwpbase.get_tag_value(qlb.version,'checkedinat','{','}')  checkedinat
    ,      cdwpbase.get_tag_value(qlb.audit_tab,'createdby','{','}')   createdby
    ,      cdwpbase.get_tag_value(qlb.audit_tab,'createdafter','{','}')   createdafter
    ,      cdwpbase.get_tag_value(qlb.audit_tab,'createdbefore','{','}')  createdbefore
    ,      cdwpbase.get_tag_value(qlb.audit_tab,'changedby','{','}')   changedby
    ,      cdwpbase.get_tag_value(qlb.audit_tab,'changedafter','{','}')   changedafter
    ,      cdwpbase.get_tag_value(qlb.audit_tab,'changedbefore','{','}')  changedbefore
    ,      cdwpbase.get_tag_value(qlb.audit_tab,'wastebasket','{','}')  wastebasket
    from   odwa_query_library qlb
    where  qlb.id = b_qlb_id
    ;
  r_qlb c_qlb%rowtype;
begin
  open c_qlb( b_qlb_id => p_qlb_id);
  fetch c_qlb
  into  r_qlb;
  close c_qlb;
  odwasrch.queryFrame
  ( p_session_id => p_session_id
  , qName => r_qlb.name
  , qCaseSensitive     => r_qlb.casesensitive
  , qWorkareaIrid      => r_qlb.workareairid
  , qTipVersionsOnly   => r_qlb.tipversionsonly
  , qFolderIrid        => r_qlb.folderirid
  , qFolderIvid        => r_qlb.folderivid
  , qIncludeNested     => r_qlb.includenested
  , qElementType       => r_qlb.elementtype
  , qIvid              => r_qlb.ivid
  , qIrid              => r_qlb.irid
  , qTxtType           => r_qlb.txttype
  , qTxtContains       => r_qlb.txtcontains
  , qFileContains      => r_qlb.filecontains
  , qWhere             => r_qlb.where_clause
  , qDepType           => r_qlb.deptype
  , qGlobalName        => r_qlb.globalname
  , qGlobalNameType    => r_qlb.globalnametype
  , qUsedByIvid        => r_qlb.usedbyivid
  , qcheckstate         => r_qlb.checkstate
  , qrootversionsonly         => r_qlb.rootversionsonly
  , qversionlabel         => r_qlb.versionlabel
  , qbranchirid         => r_qlb.branchirid
  , qcfgivid         => r_qlb.cfgivid
  , qchecknotescontain         => r_qlb.checknotescontain
  , qcheckoutuser         => r_qlb.checkoutuser
  , qcheckedoutafter         => r_qlb.checkedoutafter
  , qcheckedoutbefore         => r_qlb.checkedoutbefore
  , qcheckinuser         => r_qlb.checkinuser
  , qcheckedinafter         => r_qlb.checkedinafter
  , qcheckedinbefore         => r_qlb.checkedinbefore
  , qcreatedby    => r_qlb.createdby
  , qcreatedafter    => r_qlb.createdafter
  , qcreatedbefore    => r_qlb.createdbefore
  , qchangedby    => r_qlb.changedby
  , qchangedafter    => r_qlb.changedafter
  , qchangedbefore    => r_qlb.changedbefore
  , qwastebasket    => r_qlb.wastebasket
  , p_Run_Mode => 'execute'
  );
end; -- loadQueryCriteria


procedure saveQuery
( p_session_id    in   number
, qName              in   varchar2 default null  -- BASIC
, qCaseSensitive     in   varchar2 default null
, qWorkareaIrid      in   varchar2 default null
, qTipVersionsOnly   in   varchar2 default null
, qFolderIrid        in   varchar2 default null
, qFolderIvid        in   varchar2 default null
, qIncludeNested     in   varchar2 default null
, qElementType       in   varchar2 default null
, qIvid              in   varchar2 default null  -- ADVANCED
, qIrid              in   varchar2 default null
, qTxtType           in   varchar2 default null
, qTxtContains       in   varchar2 default null
, qFileContains      in   varchar2 default null
, qDepType           in   varchar2 default null
, qGlobalName        in   varchar2 default null
, qGlobalNameType    in   varchar2 default null
, qUsedByIvid        in   varchar2 default null
, qCheckState        in   varchar2 default null  -- VERSION
, qRootVersionsOnly  in   varchar2 default null
, qVersionLabel      in   varchar2 default null
, qBranchIrid        in   varchar2 default null
, qCfgIvid           in   varchar2 default null
, qCheckNotesContain in   varchar2 default null
, qCheckOutUser      in   varchar2 default null
, qCheckedOutAfter   in   varchar2 default null
, qCheckedOutBefore  in   varchar2 default null
, qCheckInUser       in   varchar2 default null
, qCheckedInAfter    in   varchar2 default null
, qCheckedInBefore   in   varchar2 default null
, qCheckedInAt       in   varchar2 default null
, qCreatedBy         in   varchar2 default null  -- AUDIT
, qCreatedAfter      in   varchar2 default null
, qCreatedBefore     in   varchar2 default null
, qChangedBy         in   varchar2 default null
, qChangedAfter      in   varchar2 default null
, qChangedBefore     in   varchar2 default null
, qWastebasket in   varchar2 default null
, qWhere             in   varchar2 default null
, qOrderBy           in   varchar2 default null
)
is

 function add
 ( p_name in varchar2
 , p_value in varchar2
 ) return varchar2
 is
 begin
   return '{'||p_name||'='||p_value||'}';
 end; -- add

 function get_basic
 return varchar2
 is
   l_basic varchar2(10000);
 begin
   l_basic:= add('name'   , qName)
   ||add('casesensitive'  , qCaseSensitive)
   ||add('workareairid'   , qworkareairid)
   ||add('tipversionsonly', qTipVersionsOnly)
   ||add('folderirid'     , qFolderIrid)
   ||add('folderivid'     , qFolderIvid)
   ||add('elementtype'    , qElementType)
   ||add('includenested'  , qIncludeNested)
   ;
   return l_basic;
 end; -- get_basic

 function get_advanced
 return varchar2
 is
   l_advanced varchar2(10000);
 begin
   l_advanced:= add('ivid'   , qIvid)
   ||add('irid'  , qIrid)
   ||add('txttype'   , qTxtType)
   ||add('txtcontains', qTxtContains)
   ||add('filecontains'     , qFileContains)
   ||add('where'     , qWhere)
   ||add('deptype'     , qDepType)
   ||add('globalname'     , qGlobalName)
   ||add('globalnametype'     , qGlobalNameType)
   ||add('usedbyivid'     , qUsedByIvid)
   ;
   return l_advanced;
 end; -- get_advanced


 function get_audit
 return varchar2
 is
   l_audit varchar2(10000);
 begin
   l_audit:= add('createdby'   , qCreatedBy)
   ||add('createdafter'  , qCreatedAfter)
   ||add('createdbefore'   , qCreatedBefore)
   ||add('changedby'   , qchangedBy)
   ||add('changedafter'  , qchangedAfter)
   ||add('changedbefore'   , qchangedBefore)
   ||add('wastebasket'   , qWastebasket)
   ;
   return l_audit;
 end; -- get_audit

 function get_version
 return varchar2
 is
   l_version varchar2(10000);
 begin
   l_version:= add('checkstate'   , qCheckState)
   ||add('rootversionsonly'  , qRootVersionsOnly)
   ||add('versionlabel'   , qVersionLabel)
   ||add('branchirid'   , qBranchIrid)
   ||add('cfgivid'   , qCfgIvid)
   ||add('checknotescontain'   , qCheckNotesContain)
   ||add('checkoutuser'   , qCheckOutUser)
   ||add('checkedoutafter'   , qCheckedOutAfter)
   ||add('checkedoutbefore'   , qCheckedOutBefore)
   ||add('checkedinbefore'   , qCheckedInBefore)
   ||add('checkedinafter'   , qCheckedInAfter)
   ||add('checkinuser'   , qCheckInUser)
   ;
   return l_version;
 end; -- get_version


begin
  saveQueryToLibrary
  ( p_session_id    => p_session_id
  , p_basic         => get_basic
  , p_advanced      => get_advanced
  , p_audit         => get_audit
  , p_version       => get_version
  );
end; --saveQuery

procedure js_init_textarea
( p_description in varchar2
) is
begin
  htp.p
  (' <SCRIPT LANGUAGE="JavaScript"><!--


  function initTextarea()
  {

   document.saveQueryForm.p_description.value ="'
   ||replace( replace( p_description,chr(13),'\r'),chr(10),'')
   ||'";'
   );
  htp.p('

} // initTextarea()
    //--></SCRIPT>
    <NOSCRIPT>'|| Rob_msg.getmsg(rob_msg.ACC005_NOSCRIPT,'','','','')||'</NOSCRIPT>
  ');

end; -- js_init_textarea

procedure saveQueryToLibrary
( p_session_id    in   number
, p_basic         in   varchar2  default null
, p_advanced      in   varchar2  default null
, p_audit         in   varchar2  default null
, p_version       in   varchar2  default null
, p_category      in   varchar2  default null
, p_label         in   varchar2  default null
, p_description   in   varchar2  default null
, p_published     in   varchar2  default null
, p_owner         in   varchar2  default USER
, p_last_date_changed in  varchar2  default null
, p_status        in   varchar2  default null
, p_message       in   varchar2  default null
) is
  cursor c_cat is
    select distinct
           qlb.category
    from   odwa_query_library qlb
    where  qlb.category is not null
    order
    by     qlb.category
    ;
    cursor c_qlb
         ( b_label in varchar2
         ) is
    select qlb.id
    ,      qlb.owner
    from   odwa_query_library qlb
    where  qlb.label = b_label
    ;
  r_qlb c_qlb%rowtype;
  l_cat_list varchar2(30000);
  l_cat_exists boolean:= false;
begin
  -- create session when necessary, refresh session cache, set workarea context
  -- from here on reference to odwactxt.get_... functions for any context value
  odwactxt.update_context
  ( p_session_id    => p_session_id
  , p_package_name  => PACKAGE_NAME
  , p_procedure_name=> 'saveQuery'
  , p_new_request => false
  );
  htp.htmlopen;
  htp.headopen;
  cdwp.write_about(package_name, revision_label);
  cdwp.include_report_styles;
  htp.title(Rob_msg.GetMsg(Rob_msg.DSP336_ODWASLIB_QLIB ,'','','',''));
  htp.p('<STYLE> TD { vertical-align:top } </STYLE>');
  odwahelp.js_invoke_help;
  js_init_textarea( p_description);
  htp.headClose;

  htp.bodyOpen
  ( cattributes=>'BGCOLOR="#FFFFFF" '
  ||cdwpbase.ifThenElse
    ( p_description is not null
    , ' onLoad="initTextarea()"'
    )
  );
  odwapred.open_palette(p_title => Rob_msg.GetMsg(Rob_msg.CAP225_ODWASLIB_SAVEQRY,'','','',''));
  if p_status ='I'
  then
    odwapred.print_property
    ( Rob_msg.GetMsg(Rob_msg.CAP127_ODWAINFO_STATUS ,'','','','')
    , '<b>'||Rob_msg.GetMsg(Rob_msg.CAP226_ODWASLIB_SUCINS,'','','','')||'</b>'
    , p_mandatory=> 'Y'
    );
  elsif p_status ='U'
  then
    odwapred.print_property
    ( Rob_msg.GetMsg(Rob_msg.CAP127_ODWAINFO_STATUS ,'','','','')
    , '<b>'||Rob_msg.GetMsg(Rob_msg.CAP227_ODWASLIB_SUCUPD,'','','','')||'</b>'
    , p_mandatory=> 'Y'
    );
  elsif p_status ='F'
  then
    odwapred.print_property
    ( Rob_msg.GetMsg(Rob_msg.CAP127_ODWAINFO_STATUS ,'','','','')
    , cdwp.add_images('{warning.gif}')
    ||'<b>'||Rob_msg.GetMsg(Rob_msg.CAP228_ODWASLIB_FAIL,'','','','')||'</b>'
    , p_mandatory=> 'Y'
    );
  end if;
  if p_message is not null
  then
    odwapred.print_property
    ( ''
    , p_message
    , p_mandatory=> 'N'
    );
  end if; -- p_message is not null
  htp.formopen
  ( curl         => ''
  , cmethod      => 'POST'
  , cattributes  => 'NAME="catForm" '
  , ctarget      => ''
  );

  -- Categories
  l_cat_list:= htf.formselectopen
                   ( cname   => 'category_example'
                   , cprompt => ''
                   , nsize   => 1
                   , cattributes => 'SINGLE'
                   ||' onChange="document.saveQueryForm.p_category.value=document.catForm.category_example.options[document.saveQueryForm.p_category.value=document.catForm.category_example.selectedIndex].value"'

                   );
   l_cat_list:= l_cat_list
        ||'<OPTION VALUE=""'
         ||'>'
         ||Rob_msg.GetMsg(Rob_msg.CAP229_ODWASLIB_SELCAT,'','','','')
         ;
  for r_cat in c_cat loop
     l_cat_exists:= true;
     l_cat_list:= l_cat_list
          ||'<OPTION VALUE="'||r_cat.category||'"'
           ||'>'
           ||r_cat.category
           ;
  end loop; -- r_cat
  l_cat_list:= l_cat_list
  ||htf.formselectclose
  ;
  if l_cat_exists
  then
    odwapred.print_property
    ( Rob_msg.GetMsg(Rob_msg.CAP230_ODWASLIB_LISTCAT,'','','','')
    , l_cat_list
    , p_mandatory=> 'N'
    );
  end if;
  htp.formclose;

  htp.formopen
  ( curl         => 'odwaslib.storeQuery'
  , cmethod      => 'POST'
  , cattributes  => 'NAME="saveQueryForm" '
  , ctarget      => ''
  );
  htp.p
  ( ' <INPUT TYPE="hidden" NAME="p_session_id" '
  ||' VALUE="'||to_char(p_session_id)||'">'
  );
  htp.p
  ( ' <INPUT TYPE="hidden" NAME="p_basic" '
  ||' VALUE="'||p_basic||'">'
  );
  htp.p
  ( ' <INPUT TYPE="hidden" NAME="p_advanced" '
  ||' VALUE="'||p_advanced||'">'
  );
  htp.p
  ( ' <INPUT TYPE="hidden" NAME="p_version" '
  ||' VALUE="'||p_version||'">'
  );
  htp.p
  ( ' <INPUT TYPE="hidden" NAME="p_audit" '
  ||' VALUE="'||p_audit||'">'
  );
  htp.p
  ( ' <INPUT TYPE="hidden" NAME="p_owner" '
  ||' VALUE="'||p_owner||'">'
  );
  htp.p
  ( ' <INPUT TYPE="hidden" NAME="p_last_date_changed" '
  ||' VALUE="'||nvl( p_last_date_changed, to_char(sysdate, 'dd-mon-yyyy hh24:mi'))||'">'
  );

  odwapred.print_property
  ( Rob_msg.GetMsg(Rob_msg.CAP231_ODWASLIB_CAT,'','','','')
  , htf.formText
    ( CNAME        => 'p_category'
    , csize        => 30
    , cmaxlength   => 30
    , CVALUE       =>  p_category
    , CATTRIBUTES  => ''
    )
  , p_mandatory=> 'Y' -- -- 1.1 make category mandatory
  );
  odwapred.print_property
  ( Rob_msg.GetMsg(Rob_msg.CAP232_ODWASLIB_QRYLBL,'','','','')
  , htf.formText
    ( CNAME        => 'p_label'
    , csize        => 30
    , cmaxlength   => 100
    , CVALUE       =>  p_label
    , CATTRIBUTES  => ''
    )
  , p_mandatory=> 'Y'
  );
  odwapred.print_property
  ( Rob_msg.GetMsg(Rob_msg.CAP008_CDWP_DESC ,'','','','')
  , htf.formtextarea
    ( CNAME        => 'p_description'
    , nrows        => 4
    , ncolumns     => 30
    , CATTRIBUTES  => 'WRAP="VIRTUAL"'
    )
  , p_mandatory=> 'N'
  );
  odwapred.print_property
  ( Rob_msg.GetMsg(Rob_msg.CAP233_ODWASLIB_QRYOWNR,'','','','')
  , USER
  , p_mandatory=> 'Y'
  );
  odwapred.print_property
  ( Rob_msg.GetMsg(Rob_msg.CAP234_ODWASLIB_DTCHGD ,'','','','')
  , nvl(p_last_date_changed, to_char( sysdate, 'dd-mon-yyyy hh24:mi'))
  , p_mandatory=> 'Y'
  );
   odwapred.print_property
  ( Rob_msg.GetMsg(Rob_msg.CAP235_ODWASLIB_PUBLISH,'','','','')
  , htf.formCheckBox
    ( CNAME        => 'p_published'
    , CVALUE       => 'Y'
    , CCHECKED     => cdwpbase.ifThenElse( p_published = 'Y', 'Y')
    , CATTRIBUTES  => ''
    )
  , p_mandatory=> 'N'
  );
  if nvl(p_status,'C') not in ('U','I') -- 1.1 no save button after insert or update has taken place!
  then
    odwapred.print_property
    ( ''
    , htf.anchor -- 1.1 show rounded button instead of rectangular
      ( curl => 'javascript:document.saveQueryForm.submit()'
      , ctext => cdwp.add_images(p_text => '{save_round.gif}',p_attributes => 'ALT="'||Rob_msg.GetMsg(Rob_msg.CAP236_ODWASLIB_SAVE,'','','','')||'"')
      )
    /*  htf.formsubmit
      ( CNAME        => ''
      , CVALUE       => 'Save'
      , CATTRIBUTES  => ''
      )
    */
    , p_mandatory=> 'N'
    );
  else
    open c_qlb( b_label => p_label);
    fetch c_qlb
    into  r_qlb;
    close c_qlb;
    if r_qlb.id is not null
    then
      -- 1.1 show Search button to execute query
       odwapred.print_property
      ( ''
      ,  htf.anchor -- 1.1 show rounded button instead of rectangular
         ( curl => 'javascript:top.execQuery('||to_char(r_qlb.id)||')'
         , ctext => cdwp.add_images(p_text => '{search_round.gif}',p_attributes => 'ALT="'||Rob_msg.GetMsg(Rob_msg.CAP237_ODWASLIB_EXEQRY,'','','','')||'"')
         )
      , p_mandatory=> 'N'
      );
    end if; -- r_qlb.id is not null
  end if; -- p_status not in  'I', 'U'
  /*
    odwapred.print_property
    ( ''
    , htf.anchor -- 1.1 should show rounded button instead of text Close Window
      ( curl => 'javascript:top.close()'
      , ctext => 'Close Window'
      )
    , p_mandatory=> 'N'
    );
*/
  htp.formClose;
  odwapred.close_palette;
  htp.nl;
  htp.nl;
  htp.p
  ( htf.anchor -- 1.1 should show rounded button instead of text Close Window
    ( curl => 'javascript:top.close()'
    , ctext => Rob_msg.GetMsg(Rob_msg.CAP238_ODWASLIB_CLOSE,'','','','')
    )
  );
  htp.bodyclose;
  htp.htmlclose;
end; -- saveQueryToLibrary

procedure storeQuery
( p_session_id    in   number
, p_basic         in   varchar2  default null
, p_advanced      in   varchar2  default null
, p_audit         in   varchar2  default null
, p_version       in   varchar2  default null
, p_category      in   varchar2  default null
, p_label         in   varchar2  default null
, p_description   in   varchar2  default null
, p_published     in   varchar2  default null
, p_owner         in   varchar2  default USER
, p_last_date_changed in  varchar2  default null
) is
  cursor c_qlb
         ( b_label in varchar2
         ) is
    select qlb.id
    ,      qlb.owner
    from   odwa_query_library qlb
    where  qlb.label = b_label
    ;
  r_qlb c_qlb%rowtype;
  l_status varchar2(1);
  l_message varchar2(4000);
  l_success boolean:= true;
begin
  -- 1.1
  if p_category is null
  then
    l_message:= Rob_msg.GetMsg(Rob_msg.DSP337_ODWASLIB_MANDATORY ,Rob_msg.GetMsg(Rob_msg.CAP231_ODWASLIB_CAT ,'','','',''),'','','');
    l_status:= 'F';
    l_success:= false;
  end if;
  -- 1.1
  if p_label is null
  then
    l_message:= Rob_msg.GetMsg(Rob_msg.DSP337_ODWASLIB_MANDATORY ,Rob_msg.GetMsg(Rob_msg.CAP232_ODWASLIB_QRYLBL ,'','','',''),'','','');
    l_status:= 'F';
    l_success:= false;
  end if;
  -- if p_label does not exist at all,
  -- then insert this query
  -- else
  --   if p_label exists for owner=p_owner (=USER)
  --   then
  --     update query
  --   else
  --     refuse to update query since it is owned by another user
  --
  if l_success
  then
    open c_qlb( b_label => p_label);
    fetch c_qlb
    into  r_qlb;
    close c_qlb;
    if r_qlb.id is null -- NOT FOUND at all
    then
      -- INSERT
      insert into odwa_query_library qlb
      ( id
      , category
      , label
      , description
      , published
      , owner
      , last_date_changed
      , basic
      , advanced
      , version
      , audit_tab
      )
      values
      ( odwa_qlb_seq.nextval
      , replace(p_category,' ','_')
      , p_label
      , p_description
      , p_published
      , p_owner
      , sysdate
      , p_basic
      , p_advanced
      , p_version
      , p_audit
      );
      l_status := 'I';

    else
      if r_qlb.owner = p_owner
      then
        -- UPDATE
        update odwa_query_library qlb
        set    (category
        , label
        , description
        , published
        , owner
        , last_date_changed
        , basic
        , advanced
        , version
        , audit_tab
        ) =
        ( select replace(p_category,' ','_')
        , p_label
        , p_description
        , p_published
        , p_owner
        , sysdate
        , p_basic
        , p_advanced
        , p_version
        , p_audit
        from dual
        )
        where id = r_qlb.id
        ;
        l_status := 'U';
      else
        l_status := 'F';
        l_message:= Rob_msg.GetMsg(Rob_msg.DSP338_ODWASLIB_CHGNAME,'','','','');
      end if;  -- r_qlb.owner = p_owner
    end if; -- r_qlb.id is null -- NOT FOUND at all
  end if; -- l_success = true
  saveQueryToLibrary
  ( p_session_id => p_session_id
  , p_basic => p_basic
  , p_advanced => p_advanced
  , p_version => p_version
  , p_category => replace(p_category,' ','_')
  , p_label => p_label
  , p_description => p_description
  , p_published => p_published
  , p_owner => p_owner
  , p_last_date_changed => p_last_date_changed
  , p_status => l_status
  , p_message => l_message
  );
end; -- storeQuery

procedure js_submit
is
begin
  htp.p
  (' <SCRIPT LANGUAGE="JavaScript"><!--
  function submitForm()
  {

    document.queryForm.submit();
  }
    //--></SCRIPT>
    <NOSCRIPT>'|| Rob_msg.getmsg(rob_msg.ACC005_NOSCRIPT,'','','','')||'</NOSCRIPT>
  ');
end; -- js_submit

procedure js_confirmDeleteQuery
( p_qlb_id in number
) is
begin
   htp.p
   ('
   <SCRIPT language="JavaScript">
   <!--hide from old browsers

   function confirmDeleteQuery()
   {
     if ( confirm("Are you sure you want to delete this query from the library?")
        )
     {
       document.location="odwaslib.deleteQuery?'
                    ||'p_session_id='||to_char(odwactxt.get_session_id)
                    ||chr(38)||'p_qlb_id='||to_char(p_qlb_id)
                    ||'";
     }

   } //confirmDeleteQuery
  //-->
  </SCRIPT>
  <NOSCRIPT>'|| Rob_msg.getmsg(rob_msg.ACC005_NOSCRIPT,'','','','')||'</NOSCRIPT>
  ');
end; -- js_confirmDeleteQuery


procedure queryPalette
( p_session_id    in number
, p_qlb_id            in number
) is
  cursor c_qlb
         ( b_qlb_id in number
         ) is
    select distinct
           qlb.category
    ,      qlb.description
    ,      qlb.basic
    ,      qlb.advanced
    ,      qlb.audit_tab
    ,      qlb.version
    ,      qlb.label
    ,      qlb.published
    ,      qlb.owner
    ,      qlb.last_date_changed
    from   odwa_query_library qlb
    where  qlb.id = b_qlb_id
    ;
  r_qlb c_qlb%rowtype;
  l_cat_list varchar2(30000);
  l_cat_exists boolean:= false;
  l_menu_bar varchar2(20000);

begin
  -- create session when necessary, refresh session cache, set workarea context
  -- from here on reference to odwactxt.get_... functions for any context value
  odwactxt.update_context
  ( p_session_id    => p_session_id
  , p_package_name  => PACKAGE_NAME
  , p_procedure_name=> 'queryPalette'
  );
  open c_qlb( b_qlb_id => p_qlb_id);
  fetch c_qlb
  into  r_qlb;
  close c_qlb;

  htp.htmlopen;
  htp.headopen;
  cdwp.write_about(package_name, revision_label);
  cdwp.include_report_styles;
  htp.title(Rob_msg.GetMsg(Rob_msg.DSP339_ODWASLIB_QRYPAL,'','','',''));
  htp.p('<STYLE> TD { vertical-align:top } </STYLE>');
  odwahelp.js_invoke_help;
  js_submit;
  js_confirmDeleteQuery
  ( p_qlb_id  => P_qlb_id
  );
  htp.headClose;

  htp.bodyOpen
  ( cattributes=>'BGCOLOR="#FFFFFF" '
  );
  if r_qlb.owner = USER
  then
    -- current user is owner of this Query
    -- he is allowed to Update and Delete

    htp.tableopen( cattributes => 'BORDER="0" align="RIGHT"');  -- 1.4  bgcolor="88DDF9"
    cdwp.tableRowOpen;
    htp.tabledata
    ( cvalue=> cdwp.add_images( p_text => '{ft-left.gif}')
    ||'<A href="javascript:submitForm()">'||cdwp.add_images('{edit.gif}')||'</A>'
    ||'<A href="javascript:confirmDeleteQuery()">'||cdwp.add_images('{deletiongr.gif}')||'</A>'
    ||cdwp.add_images
      ( p_text => '{ft-right.gif}'
      , p_attributes=> ' Qwidth="25" '
      )
    , calign => 'right'
    , cattributes => 'Zwidth="400" ABGCOLOR="#cccccc"'
    );
    cdwp.tableRowClose;
    cdwp.tableClose;
    htp.nl;
    htp.nl;
  end if; -- r_qlb.owner = USER
  odwapred.open_palette(p_title => Rob_msg.GetMsg(Rob_msg.DSP339_ODWASLIB_QRYPAL,'','','',''));
  htp.formopen
  ( curl         => 'odwaslib.saveQueryToLibrary'
  , cmethod      => 'POST'
  , cattributes  => 'NAME="queryForm" '
  , ctarget      => ''
  );
  htp.p
  ( ' <INPUT TYPE="hidden" NAME="p_session_id" '
  ||' VALUE="'||to_char(odwactxt.get_session_id)||'">'
  );
  htp.p
  ( ' <INPUT TYPE="hidden" NAME="p_basic" '
  ||' VALUE="'||r_qlb.basic||'">'
  );
  htp.p
  ( ' <INPUT TYPE="hidden" NAME="p_category" '
  ||' VALUE="'||r_qlb.category||'">'
  );
  htp.p
  ( ' <INPUT TYPE="hidden" NAME="p_label" '
  ||' VALUE="'||r_qlb.label||'">'
  );
  htp.p
  ( ' <INPUT TYPE="hidden" NAME="p_description" '
  ||' VALUE="'||r_qlb.description||'">'
  );
  htp.p
  ( ' <INPUT TYPE="hidden" NAME="p_published" '
  ||' VALUE="'||r_qlb.published||'">'
  );
  htp.p
  ( ' <INPUT TYPE="hidden" NAME="p_owner" '
  ||' VALUE="'||r_qlb.owner||'">'
  );
  htp.p
  ( ' <INPUT TYPE="hidden" NAME="p_last_date_changed" '
  ||' VALUE="'||to_char(r_qlb.last_date_changed, 'dd-mon-yyyy hh24:mi')||'">'
  );
  htp.formClose;

  odwapred.print_property
  ( Rob_msg.GetMsg(Rob_msg.CAP231_ODWASLIB_CAT ,'','','','')
  , r_qlb.category
  , p_mandatory=> 'Y'
  );
  odwapred.print_property
  ( Rob_msg.GetMsg(Rob_msg.CAP232_ODWASLIB_QRYLBL,'','','','')
  , r_qlb.label
  , p_mandatory=> 'Y'
  );
  odwapred.print_property
  ( Rob_msg.GetMsg(Rob_msg.CAP008_CDWP_DESC ,'','','','')
  , r_qlb.description
  , p_mandatory=> 'N'
  );
  odwapred.print_property
  ( Rob_msg.GetMsg(Rob_msg.CAP233_ODWASLIB_QRYOWNR ,'','','','')
  , r_qlb.owner
  , p_mandatory=> 'Y'
  );
  odwapred.print_property
  ( Rob_msg.GetMsg(Rob_msg.CAP234_ODWASLIB_DTCHGD,'','','','')
  , to_char( r_qlb.last_date_changed, 'dd-mon-yyyy hh24:mi')
  , p_mandatory=> 'Y'
  );
   odwapred.print_property
  ( Rob_msg.GetMsg(Rob_msg.CAP235_ODWASLIB_PUBLISH,'','','','')
  , r_qlb.published
  , p_mandatory=> 'N'
  );
  -- 1.1 show Search button to execute query
   odwapred.print_property
  ( ''
  ,  htf.anchor -- 1.1 show rounded button instead of rectangular
     ( curl => 'javascript:top.execQuery('||to_char(p_qlb_id)||')'
     , ctext => cdwp.add_images(p_text => '{search_round.gif}',p_attributes => 'ALT="'||Rob_msg.GetMsg(Rob_msg.CAP237_ODWASLIB_EXEQRY,'','','','')||'"')
     )
  , p_mandatory=> 'N'
  );
  odwapred.close_palette;
  htp.bodyclose;
  htp.htmlclose;
end; -- queryPalette

procedure deleteQuery
( p_session_id    in number
, p_qlb_id            in number
) is
  cursor c_qlb
         ( b_qlb_id in number
         ) is
    select distinct
           qlb.category
    ,      qlb.description
    ,      qlb.basic
    ,      qlb.advanced
    ,      qlb.audit_tab
    ,      qlb.version
    ,      qlb.label
    ,      qlb.published
    ,      qlb.owner
    ,      qlb.last_date_changed
    from   odwa_query_library qlb
    where  qlb.id = b_qlb_id
    ;
  r_qlb c_qlb%rowtype;

begin
  -- create session when necessary, refresh session cache, set workarea context
  -- from here on reference to odwactxt.get_... functions for any context value
  odwactxt.update_context
  ( p_session_id    => p_session_id
  , p_package_name  => PACKAGE_NAME
  , p_procedure_name=> 'deleteQuery'
  );
  open c_qlb( b_qlb_id => p_qlb_id);
  fetch c_qlb
  into  r_qlb;
  close c_qlb;
  delete odwa_query_library
  where  id = p_qlb_id
  ;
  htp.htmlopen;
  htp.headopen;
  cdwp.write_about(package_name, revision_label);
  cdwp.include_report_styles;
  htp.title(Rob_msg.GetMsg(Rob_msg.DSP340_ODWASLIB_DELQRY ,'','','',''));
  htp.p('<STYLE> TD { vertical-align:top } </STYLE>');
  odwahelp.js_invoke_help;
  htp.headClose;

  htp.bodyOpen
  ( cattributes=>'BGCOLOR="#FFFFFF" '
  ||'onLoad="top.reloadNodeIdx( top.getParentNodeRelative( top.selectedIdx, 1))"'
  );
  odwapred.open_palette(p_title => Rob_msg.GetMsg(Rob_msg.DSP341_ODWASLIB_QRYDLTD ,'','','',''));
  odwapred.print_property
  ( Rob_msg.GetMsg(Rob_msg.CAP231_ODWASLIB_CAT ,'','','','')
  , r_qlb.category
  , p_mandatory=> 'N'
  );
  odwapred.print_property
  ( Rob_msg.GetMsg(Rob_msg.CAP232_ODWASLIB_QRYLBL,'','','','')
  , r_qlb.label
  , p_mandatory=> 'Y'
  );
   odwapred.print_property
  ( Rob_msg.GetMsg(Rob_msg.CAP235_ODWASLIB_PUBLISH,'','','','')
  , r_qlb.published
  , p_mandatory=> 'Y'
  );
  odwapred.close_palette;
  htp.bodyclose;
  htp.htmlclose;
end; -- deleteQuery


procedure node_selected
( p_session_id    in   number
, p_classification in varchar2
) is
  l_qlb_id number(10):= to_number(cdwpbase.get_tag_value(p_tag => 'ID',p_string => p_classification));
begin
  queryPalette
  ( p_session_id => p_session_id
  , p_qlb_id => l_qlb_id
  );
end;  -- node_selected

procedure dataXchange
( p_session_id    in   number
) is
begin
  -- create session when necessary, refresh session cache, set workarea context
  -- from here on reference to odwactxt.get_... functions for any context value
  odwactxt.update_context
  ( p_session_id    => p_session_id
  , p_package_name  => PACKAGE_NAME
  , p_procedure_name=> 'dataXchange'
  );
  htp.htmlOpen;
  htp.headOpen;
  cdwp.write_about(package_name, revision_label);
  htp.headClose;
  htp.bodyOpen;
  htp.bodyClose;
  htp.htmlClose;
end; -- dataXchange

procedure awaitNode
( p_session_id    in   number
) is
begin
  odwactxt.update_context
  ( p_session_id    => p_session_id
  , p_package_name  => PACKAGE_NAME
  , p_procedure_name=> 'awaitNode'
  );
  htp.htmlOpen;
  htp.headOpen;
  cdwp.write_about(package_name, revision_label);
  htp.headClose;
  htp.bodyOpen;
  htp.bodyClose;
  htp.htmlClose;
end; -- awaitNode




procedure rightSide
( p_session_id    in   number
) is
begin
  -- create session when necessary, refresh session cache, set workarea context
  -- from here on reference to odwactxt.get_... functions for any context value
  odwactxt.update_context
  ( p_session_id    => p_session_id
  , p_package_name  => PACKAGE_NAME
  , p_procedure_name=> 'rightSide'
  );

  htp.htmlOpen;
  htp.headOpen;
  cdwp.write_about(package_name, revision_label);
  htp.headClose;
  htp.framesetopen
  ( crows       =>  '90%,*'
  , cattributes => 'BORDER="0" LONGDESC=odwahelp.rightSide?p_session_id=&p_him_id=1650 TITLE="'
    ||Rob_msg.Getmsg(Rob_msg.DSP336_ODWASLIB_QLIB,'','','','')||'"'
  );
    htp.frame
    ( 'odwaslib.awaitNode?p_session_id='
            ||to_char(odwactxt.get_session_id)
    , cname=> 'paletteFrame'
    , cattributes => 'LONGDESC=odwahelp.rightSide?p_session_id=&p_him_id=1650 TITLE="'
    ||Rob_msg.Getmsg(Rob_msg.DSP336_ODWASLIB_QLIB,'','','','')||'"'
    );
  htp.frame
  ( 'odwaslib.dataXchange?p_session_id='
          ||to_char(odwactxt.get_session_id)
  , cname=> 'dataXchange'
  , cscrolling => 'no'
  , cattributes => 'LONGDESC=odwahelp.rightSide?p_session_id=&p_him_id=1650 TITLE="'
    ||Rob_msg.Getmsg(Rob_msg.DSP336_ODWASLIB_QLIB,'','','','')||'"'
  );
  htp.framesetclose;
  htp.htmlClose;
end; -- rightSide

procedure leftSide
( p_session_id    in   number
) is
begin
  -- create session when necessary, refresh session cache, set workarea context
  -- from here on reference to odwactxt.get_... functions for any context value
  odwactxt.update_context
  ( p_session_id    => p_session_id
  , p_package_name  => PACKAGE_NAME
  , p_procedure_name=> 'leftSide'
  );

  htp.htmlOpen;
  htp.headOpen;
  cdwp.write_about(package_name, revision_label);
  htp.headClose;
  htp.bodyOpen
  ( cattributes=>'BGCOLOR="#FFFFFF" '
  ||'onLoad="top.init();top.drawOnLoad()"'
  );

  htp.bodyClose;
  htp.htmlClose;
end; -- leftSide



procedure load_tree
 is
  cursor c_qlb
  is
  select qlb.category
  from   odwa_query_library qlb
  where  qlb.owner = USER
         or
         qlb.published = 'Y'
  UNION
  select '(no category)' category
  from   odwa_query_library qlb
  where  exists
         ( select 'x'
           from odwa_query_library qlb2
           where qlb2.category is null
           and
            ( qlb2.owner = USER
              or
              qlb2.published = 'Y'
            )
         )
  order
  by     category
  ;
  l_last_cat varchar2(200):='XCXLJ';
  l_cat_icon     varchar2(200):= cdwp.add_images('{view_impl.gif}');
  l_lab_icon     varchar2(200):= cdwp.add_images('{user_code.gif}');
begin
  htmltree.reset_tbl;
  for r_qlb in c_qlb loop
    if r_qlb.category <> l_last_cat
    then
      htmltree.add_node
      ( p_display_label    => l_cat_icon||cdwpbase.nbsp(1)
                              ||replace(r_qlb.category,' ','+')
      , p_node_level       => 1
      , p_has_children     => true
      , p_is_expanded      => false
      , p_value            => ''
      , p_additional_label => '' --l_link
      , p_is_inflatable    => true
      , p_classification   => '{CAT='||r_qlb.category||'}nolink'
      );
      l_last_cat:= r_qlb.category;
    end if; -- r_qlb.category <> l_last_cat
/*
    htmltree.add_node
    ( p_display_label    =>  l_lab_icon||cdwpbase.nbsp(1)||r_qlb.label
    , p_node_level       => 2
    , p_has_children     => false
    , p_is_expanded      => false
    , p_value            => ''
    , p_additional_label => '' --l_link
    , p_is_inflatable    => false
    , p_classification   => '{ID='||to_char(r_qlb.id)||'}'
    );
    */
  end loop; -- r_qlb
    htmltree.find_folder_nodes; -- to set the has_children property where appropriate
end; -- load_tree

procedure inflate_node
( p_node_index    in number
, p_node_level    in number  -- level of node-to-be-inflated (all its children will have p_node_level + 1 or higher)
                             -- node levels in client are number 0..(highest level-1). in server 1..highest level
, p_num_of_nodes           in number
, p_node_value             in varchar2 default null
, p_node_type              in varchar2 default null
, p_entry_point            in number   default null
, p_session_id             in number
, p_root_value             in varchar2
, p_root_classification    in varchar2 default null
) is

  l_cat varchar2(100):= cdwpbase.get_tag_value( p_string=> p_node_type, p_tag => 'CAT');

  cursor c_qlb
         ( b_cat in varchar2
         )  is
  select qlb.category
  ,      qlb.label
  ,      qlb.id
  ,      qlb.owner
  from   odwa_query_library qlb
  where  (
          qlb.owner = USER
          or
          qlb.published = 'Y'
         )
  and    nvl(replace(qlb.category,'+',' '),'(no category)') = replace(b_cat, '+',' ')
  order
  by     qlb.category
  ,      qlb.label
  ;
  l_last_cat varchar2(200):='XCXLJ';
  l_cat_icon     varchar2(200):= cdwp.add_images('{view_impl.gif}');
  l_lab_icon     varchar2(200):= cdwp.add_images('{user_code.gif}');
begin
  htmltree.reset_tbl;
  for r_qlb in c_qlb( b_cat => l_cat) loop
    htmltree.add_node
    ( p_display_label    =>  l_lab_icon||cdwpbase.nbsp(1)||r_qlb.label
    , p_node_level       => p_node_level + 1
    , p_has_children     => false
    , p_is_expanded      => false
    , p_value            => ''
    , p_additional_label => '' --l_link
    , p_is_inflatable    => false
    , p_classification   => '{ID='||to_char(r_qlb.id)||'}'
    );
  end loop; -- r_qlb
    htmltree.find_folder_nodes; -- to set the has_children property where appropriate

  htmltree.write_inflate_code
  ( p_node_index    => p_node_index
  , p_num_of_nodes  => p_num_of_nodes
  , p_frame_locator => 'top.'
  , p_entry_point   => p_entry_point
  );
end; -- inflate_node

-- main procedure for the Query Library screen
-- sets up a frame that ultimately will look as follows:
-- -------------------------------
-- |              |              |
-- |              |              |
-- |              |              |
-- |              | paletteFrame |
-- |              |------------- |
-- |              | dataXchange  |
-- -------------------------------
--   {leftSide}     { rightSide }
procedure openLibrary
( p_session_id    in   number
) is
  l_js_action varchar2(2000);
  l_node_href varchar2(2000);
begin
  odwactxt.update_context
  ( p_session_id    => p_session_id
  , p_package_name  => PACKAGE_NAME
  , p_procedure_name=> 'open_library'
  );

  htp.htmlOpen;
  htp.headOpen;
  js_exec;
  l_js_action:=
  'function pickNode( idx)
    {
    var url;
    selectedIdx = idx;
    redrawTree();
          { top.rightSide.paletteFrame.location = "odwaslib.node_selected?p_session_id='||to_char(odwactxt.get_session_id)
          ||chr(38)||'p_classification="+nodesTree[idx].classification;
          }
    }
  ';

-- now we could use the function getParentNodeRelative( idx, levels) or getParentNodeAbsolute( idx, level)
-- to retrieve values/classifications of parent nodes
-- e.g. to get the root Folder irid for any selected node, refer to:
-- nodesTree[ getParentNodeAbsolute( idx, 0)].value
  l_node_href:=
  'pickNode(\"" +idx + "\" )'; -- (\"" +elId + "\","+idx+" )'; --\""+name+\"")';

    load_tree;
    htmltree.js_tree
    ( p_codeFrameName      => 'top'
    , p_treeFrameName      => 'leftSide'
    , p_treeFramePath      => 'top.leftSide'
    , p_xchangeFramePath   => 'top.rightSide.dataXchange'
    , p_js_action          => l_js_action
    , p_node_href          => l_node_href
    , p_inflate_request    => 'odwaslib.inflate_Node'
    , p_inflate_parameters =>   '"'
                              ||chr(38)
                              ||'p_session_id='||to_char(odwactxt.get_session_id)
                              ||chr(38)
                              ||'p_root_value="+nodesTree[getParentNodeAbsolute( idx, 0)].value'
                              ||'+"'
                              ||chr(38)
                              ||'p_root_classification="+nodesTree[getParentNodeAbsolute( idx, 0)].classification'
    );

  cdwp.write_about(package_name, revision_label);
  htp.title
  ( Rob_msg.GetMsg(Rob_msg.DSP336_ODWASLIB_QLIB,'','','','')
  );

  htp.headClose;
  htp.framesetopen
  ( ccols       =>  '40%,60%'
  , cattributes => 'BORDER="2" LONGDESC=odwahelp.rightSide?p_session_id=&p_him_id=1650 TITLE="'
    ||Rob_msg.Getmsg(Rob_msg.DSP336_ODWASLIB_QLIB,'','','','')||'"'
  );
  htp.frame
  ( csrc => 'odwaslib.leftSide?p_session_id='
          ||to_char(odwactxt.get_session_id)
  , cname=> 'leftSide'
  , cattributes => 'LONGDESC=odwahelp.rightSide?p_session_id=&p_him_id=1650 TITLE="'
    ||Rob_msg.Getmsg(Rob_msg.DSP336_ODWASLIB_QLIB,'','','','')||'"'
  );

  htp.frame
  ( 'odwaslib.rightSide?p_session_id='
          ||to_char(odwactxt.get_session_id)
  , cname=> 'rightSide'
  , cattributes =>'LONGDESC=odwahelp.rightSide?p_session_id=&p_him_id=1650 TITLE="'
    ||Rob_msg.Getmsg(Rob_msg.DSP336_ODWASLIB_QLIB,'','','','')||'"'
  );
  htp.framesetclose;
  htp.htmlClose;
end; -- open_library


begin
   null;
end; -- odwaslib
/