/* $Id$ */
/*============================================================================*
 |                 Copyright (c) 2013 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 EmployeeCenter Views service
  --
  l_sql :=
'          SELECT e.entry_sequence group_sequence
                ,m.menu_name group_name
                ,e.prompt group_title
                ,(SELECT lf.function_name
                  FROM   fnd_form_functions lf
                        ,fnd_menu_entries_vl le
                  WHERE  lf.function_id IN
                           (SELECT le1.function_id
                            FROM   fnd_menu_entries le1
                                  ,fnd_menus lem1
                            WHERE  lem1.menu_name = ''XXAS_EMP_LAYOUTS''
                            AND    lem1.menu_id = le1.menu_id)
                  AND    le.function_id = lf.function_id
                  AND    le.menu_id = e.menu_id
                  AND    rownum &lt; 2) group_layout
                ,(SELECT lf.parameters
                  FROM   fnd_form_functions lf
                        ,fnd_menu_entries_vl le
                  WHERE  lf.function_id IN
                           (SELECT le2.function_id
                            FROM   fnd_menu_entries le2
                                  ,fnd_menus lem2
                            WHERE  lem2.menu_name = ''XXAS_EMP_LAYOUTS''
                            AND    lem2.menu_id = le2.menu_id)
                  AND    le.function_id = lf.function_id
                  AND    le.menu_id = e.menu_id
                  AND    rownum &lt; 2) group_layout_parameters
                ,xxas_emp_export_pkg.get_emp_export_options
                      (f.function_name) emp_export
                ,e.description group_description
                ,e.entry_sequence view_sequence
                ,f.function_name view_name
                ,e.prompt view_title
                ,e.description view_description
                ,xxas_com_sec_pkg.get_parameter_value
                  (f.parameters, ''pModule'') module
                ,xxas_com_sec_pkg.get_parameter_value
                  (f.parameters, ''pServiceName'') web_service
                ,xxas_com_sec_pkg.get_parameter_value
                  (f.parameters, ''pLayoutType'') layout_type
                ,xxas_com_msg_pkg.get_string
                   (nvl(xxas_com_sec_pkg.get_parameter_value
                         (f.parameters, ''pNoDataMessage'')
                       ,''XXAS_RB_EMP_NO_INFO'')) no_data_text
                ,f.parameters view_parameters
                ,f.web_icon icon_name
                ,o.obj_name object_name
                ,decode
                   (o.obj_name, :pObjectName,  :pPrimaryKey1Value
                              , :pObjectName2, :pPrimaryKey2Value1
                              , :pObjectName3, :pPrimaryKey3Value1
                              , :pObjectName4, :pPrimaryKey4Value1
                              , :pObjectName5, :pPrimaryKey5Value1
                              , null) primary_key_value1
                ,decode
                   (o.obj_name, :pObjectName,  :pPrimaryKey1Value2
                              , :pObjectName2, :pPrimaryKey2Value2
                              , :pObjectName3, :pPrimaryKey3Value2
                              , :pObjectName4, :pPrimaryKey4Value2
                              , :pObjectName5, :pPrimaryKey5Value2
                              , null) primary_key_value2
                ,decode
                   (o.obj_name, :pObjectName,  :pPrimaryKey1Value3
                              , :pObjectName2, :pPrimaryKey2Value3
                              , :pObjectName3, :pPrimaryKey3Value3
                              , :pObjectName4, :pPrimaryKey4Value3
                              , :pObjectName5, :pPrimaryKey5Value3
                              , null) primary_key_value3
                ,decode
                   (o.obj_name, :pObjectName,  :pPrimaryKey1Value4
                              , :pObjectName2, :pPrimaryKey2Value4
                              , :pObjectName3, :pPrimaryKey3Value4
                              , :pObjectName4, :pPrimaryKey4Value4
                              , :pObjectName5, :pPrimaryKey5Value4
                              , null) primary_key_value4
                ,decode
                   (o.obj_name, :pObjectName,  :pPrimaryKey1Value5
                              , :pObjectName2, :pPrimaryKey2Value5
                              , :pObjectName3, :pPrimaryKey3Value5
                              , :pObjectName4, :pPrimaryKey4Value5
                              , :pObjectName5, :pPrimaryKey5Value5
                              , null) primary_key_value5
                ,(CASE o.obj_name 
                    WHEN :pObjectName  THEN xxas_com_sec_pkg.test_instance
                                              (f.function_name
                                              ,:pPrimaryKey1Value
                                              ,:pPrimaryKey1Value2
                                              ,:pPrimaryKey1Value3
                                              ,:pPrimaryKey1Value4
                                              ,:pPrimaryKey1Value5)
                    WHEN :pObjectName2 THEN xxas_com_sec_pkg.test_instance
                                              (f.function_name
                                              ,:pPrimaryKey2Value1
                                              ,:pPrimaryKey2Value2
                                              ,:pPrimaryKey2Value3
                                              ,:pPrimaryKey2Value4
                                              ,:pPrimaryKey2Value5)
                    WHEN :pObjectName3 THEN xxas_com_sec_pkg.test_instance
                                              (f.function_name
                                              ,:pPrimaryKey3Value1
                                              ,:pPrimaryKey3Value2
                                              ,:pPrimaryKey3Value3
                                              ,:pPrimaryKey3Value4
                                              ,:pPrimaryKey3Value5)
                    WHEN :pObjectName4 THEN xxas_com_sec_pkg.test_instance
                                              (f.function_name
                                              ,:pPrimaryKey4Value1
                                              ,:pPrimaryKey4Value2
                                              ,:pPrimaryKey4Value3
                                              ,:pPrimaryKey4Value4
                                              ,:pPrimaryKey4Value5)
                    WHEN :pObjectName5 THEN xxas_com_sec_pkg.test_instance
                                              (f.function_name
                                              ,:pPrimaryKey5Value1
                                              ,:pPrimaryKey5Value2
                                              ,:pPrimaryKey5Value3
                                              ,:pPrimaryKey5Value4
                                              ,:pPrimaryKey5Value5)
                    ELSE ''N'' 
                  END ) has_access    
          FROM  (SELECT rownum row_num
                       ,fme.function_id
                       ,fme.menu_id
                       ,fme.prompt
                       ,fme.entry_sequence
                       ,fme.description
                 FROM   fnd_menu_entries_vl fme
                 CONNECT BY PRIOR fme.sub_menu_id = fme.menu_id
                 START WITH fme.menu_id =
                   (SELECT menu_id
                    FROM   fnd_menus
                    WHERE  menu_name = fnd_profile.value(''XXAS_EMP_REG_VIEWS_MENU''))
                 ORDER SIBLINGS BY fme.entry_sequence) e
                ,fnd_form_functions f
                ,fnd_menus m
                ,fnd_objects o
          WHERE  e.prompt IS NOT NULL
          AND    e.menu_id = m.menu_id
          AND    e.function_id = f.function_id
          AND    f.object_id = o.object_id
          AND    f.function_name NOT LIKE ''XXAS_EMP_LAYOUT%''
          ORDER BY e.row_num ';

  xxas_com_sr_pkg.seed_query_service
    (p_service_name      => 'QueryEmpViews'
    ,p_securing_function => 'XXAS_SRV_Q_EMP_VIEWS_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('pPrimaryKey1Value' , xxas_com_sr_pkg.g_VARCHAR2)
                              ,xxas_com_sr_param_type('pPrimaryKey1Value2', xxas_com_sr_pkg.g_VARCHAR2)
                              ,xxas_com_sr_param_type('pPrimaryKey1Value3', xxas_com_sr_pkg.g_VARCHAR2)
                              ,xxas_com_sr_param_type('pPrimaryKey1Value4', xxas_com_sr_pkg.g_VARCHAR2)
                              ,xxas_com_sr_param_type('pPrimaryKey1Value5', xxas_com_sr_pkg.g_VARCHAR2)
                              ,xxas_com_sr_param_type('pObjectName2'      , xxas_com_sr_pkg.g_VARCHAR2)
                              ,xxas_com_sr_param_type('pPrimaryKey2Value1', xxas_com_sr_pkg.g_VARCHAR2)
                              ,xxas_com_sr_param_type('pPrimaryKey2Value2', xxas_com_sr_pkg.g_VARCHAR2)
                              ,xxas_com_sr_param_type('pPrimaryKey2Value3', xxas_com_sr_pkg.g_VARCHAR2)
                              ,xxas_com_sr_param_type('pPrimaryKey2Value4', xxas_com_sr_pkg.g_VARCHAR2)
                              ,xxas_com_sr_param_type('pPrimaryKey2Value5', xxas_com_sr_pkg.g_VARCHAR2)
                              ,xxas_com_sr_param_type('pObjectName3'      , xxas_com_sr_pkg.g_VARCHAR2)
                              ,xxas_com_sr_param_type('pPrimaryKey3Value1', xxas_com_sr_pkg.g_VARCHAR2)
                              ,xxas_com_sr_param_type('pPrimaryKey3Value2', xxas_com_sr_pkg.g_VARCHAR2)
                              ,xxas_com_sr_param_type('pPrimaryKey3Value3', xxas_com_sr_pkg.g_VARCHAR2)
                              ,xxas_com_sr_param_type('pPrimaryKey3Value4', xxas_com_sr_pkg.g_VARCHAR2)
                              ,xxas_com_sr_param_type('pPrimaryKey3Value5', xxas_com_sr_pkg.g_VARCHAR2)
                              ,xxas_com_sr_param_type('pObjectName4'      , xxas_com_sr_pkg.g_VARCHAR2)
                              ,xxas_com_sr_param_type('pPrimaryKey4Value1', xxas_com_sr_pkg.g_VARCHAR2)
                              ,xxas_com_sr_param_type('pPrimaryKey4Value2', xxas_com_sr_pkg.g_VARCHAR2)
                              ,xxas_com_sr_param_type('pPrimaryKey4Value3', xxas_com_sr_pkg.g_VARCHAR2)
                              ,xxas_com_sr_param_type('pPrimaryKey4Value4', xxas_com_sr_pkg.g_VARCHAR2)
                              ,xxas_com_sr_param_type('pPrimaryKey4Value5', xxas_com_sr_pkg.g_VARCHAR2)
                              ,xxas_com_sr_param_type('pObjectName5'      , xxas_com_sr_pkg.g_VARCHAR2)
                              ,xxas_com_sr_param_type('pPrimaryKey5Value1', xxas_com_sr_pkg.g_VARCHAR2)
                              ,xxas_com_sr_param_type('pPrimaryKey5Value2', xxas_com_sr_pkg.g_VARCHAR2)
                              ,xxas_com_sr_param_type('pPrimaryKey5Value3', xxas_com_sr_pkg.g_VARCHAR2)
                              ,xxas_com_sr_param_type('pPrimaryKey5Value4', xxas_com_sr_pkg.g_VARCHAR2)
                              ,xxas_com_sr_param_type('pPrimaryKey5Value5', xxas_com_sr_pkg.g_VARCHAR2)));

END;
/
COMMIT;
EXIT;
