/* $Id$ */
/*============================================================================*
 |                 Copyright (c) 2010 Applaud Solutions UK Ltd                |
 |                             All rights reserved                            |
 *===========================================================================*/

SET VERIFY OFF
SET FEEDBACK OFF
SET SCAN OFF
WHENEVER OSERROR  EXIT FAILURE ROLLBACK
WHENEVER SQLERROR EXIT FAILURE ROLLBACK

DECLARE

  --
  -- Local constants and variables
  --
  l_sql varchar2(32000);

BEGIN

  --
  -- Register Query Actions
  --
  l_sql :=
'        SELECT f.function_name action_name
              ,e.prompt action_title
              ,e.description description
              ,xxas_com_sec_pkg.get_function_url
                 (f.function_id
                 ,''pPrimaryKey1Value='' ||
                      decode
                        (o.obj_name, :pObjectName,  :pPrimaryKeyId
                                   , :pObjectName2, :pPrimaryKeyId2
                                   , :pObjectName3, :pPrimaryKeyId3
                                   , :pObjectName4, :pPrimaryKeyId4
                                   , :pObjectName5, :pPrimaryKeyId5
                                   , null)
                 ,o.obj_name
                 ,decode
                    (o.obj_name, :pObjectName,  :pPrimaryKeyId
                               , :pObjectName2, :pPrimaryKeyId2
                               , :pObjectName3, :pPrimaryKeyId3
                               , :pObjectName4, :pPrimaryKeyId4
                               , :pObjectName5, :pPrimaryKeyId5
                               , null)
                 ,xxas_com_sec_pkg.get_parameter_value
                    (f.parameters, ''pSecurityGroupId'')) url
              ,f.web_icon icon_name
              ,nvl(xxas_com_sec_pkg.get_parameter_value
                    (f.parameters, ''pNewTabOrWindow'')
                  ,''Y'') new_tab_or_window
              ,xxas_com_sec_pkg.get_parameter_value
                 (f.parameters, ''pContextType'') context_type
              ,xxas_com_sec_pkg.get_parameter_value
                 (f.parameters, ''pGroups'') groups
              ,decode(f.function_name
                     , ''XXAS_COM_ACT_WORKLIST_SS''
                     , ''RED'', null) badge_color
              ,decode(f.function_name
                      ,''XXAS_COM_ACT_WORKLIST_SS''
                      ,(SELECT count(*)
                        FROM (SELECT wn.notification_id
                              FROM   wf_notifications wn
                              WHERE  wn.status = ''OPEN''
                              AND    wn.more_info_role IS NULL
                              AND    wn.recipient_role IN
                                (SELECT WUR.role_name
                                 FROM WF_USER_ROLES WUR
                                 WHERE WUR.user_name = fnd_global.user_name)
                              UNION ALL
                              SELECT wn.notification_id
                              FROM   wf_notifications wn
                              WHERE  wn.status = ''OPEN''
                              AND    wn.more_info_role IN
                                (SELECT WUR.role_name
                                 FROM WF_USER_ROLES WUR
                                 WHERE WUR.user_name = fnd_global.user_name)))
                     ,null) badge_number
               ,decode(f.function_name
                      ,''XXAS_COM_ACT_WORKLIST_SS''
                      , ''Unread notifications'') badge_text
               ,o.obj_name object_name
               ,decode
                  (o.obj_name, :pObjectName,  :pPrimaryKeyId
                             , :pObjectName2, :pPrimaryKeyId2
                             , :pObjectName3, :pPrimaryKeyId3
                             , :pObjectName4, :pPrimaryKeyId4
                             , :pObjectName5, :pPrimaryKeyId5
                             , null) primary_key_value
        FROM   fnd_menus m
              ,fnd_menu_entries_vl e
              ,fnd_form_functions_vl f
              ,fnd_objects o
        WHERE  m.menu_name = fnd_profile.value(''XXAS_COM_REG_ACTIONS_MENU'')
        AND    m.menu_id = e.menu_id
        AND    e.function_id = f.function_id
        AND    f.object_id = o.object_id
        AND    xxas_com_sec_pkg.test_instance
                 (f.function_name
                 ,decode
                   (o.obj_name, :pObjectName,  :pPrimaryKeyId
                              , :pObjectName2, :pPrimaryKeyId2
                              , :pObjectName3, :pPrimaryKeyId3
                              , :pObjectName4, :pPrimaryKeyId4
                              , :pObjectName5, :pPrimaryKeyId5
                              , null)) = ''Y''
        ORDER BY e.entry_sequence  ';

  xxas_com_sr_pkg.seed_query_service
    (p_service_name      => 'QueryActions'
    ,p_securing_function => 'XXAS_SRV_Q_ACTN_PERM'
    ,p_sql               => l_sql
    ,p_parameters        => xxas_com_sr_param_varray
                              (xxas_com_sr_param_type('pObjectName'    , xxas_com_sr_pkg.g_VARCHAR2)
                              ,xxas_com_sr_param_type('pPrimaryKeyId'  , xxas_com_sr_pkg.g_VARCHAR2)
                              ,xxas_com_sr_param_type('pObjectName2'   , xxas_com_sr_pkg.g_VARCHAR2)
                              ,xxas_com_sr_param_type('pPrimaryKeyId2' , xxas_com_sr_pkg.g_VARCHAR2)
                              ,xxas_com_sr_param_type('pObjectName3'   , xxas_com_sr_pkg.g_VARCHAR2)
                              ,xxas_com_sr_param_type('pPrimaryKeyId3' , xxas_com_sr_pkg.g_VARCHAR2)
                              ,xxas_com_sr_param_type('pObjectName4'   , xxas_com_sr_pkg.g_VARCHAR2)
                              ,xxas_com_sr_param_type('pPrimaryKeyId4' , xxas_com_sr_pkg.g_VARCHAR2)
                              ,xxas_com_sr_param_type('pObjectName5'   , xxas_com_sr_pkg.g_VARCHAR2)
                              ,xxas_com_sr_param_type('pPrimaryKeyId5' , xxas_com_sr_pkg.g_VARCHAR2)));

END;
/
COMMIT;
EXIT;
