/* $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 Flexible View
  --
  l_sql :=
'        SELECT XMLElement
                ("View"
                ,XMLAttributes
                  (xxas_com_sec_pkg.get_function_parameter(:pViewName, ''pLayoutType'') as "layoutType"
                  ,xxas_com_sec_pkg.get_object(xxas_com_sec_pkg.get_function_parameter(:pViewName, ''pDataFunction'')) as "dataObject"
                  ,xxas_emp_views_pkg.create_allowed
                    (:pViewName, :pPrimaryKeyId, :pPrimaryKeyId2
                    ,:pPrimaryKeyId3, :pPrimaryKeyId4, :pPrimaryKeyId5) as "createAllowed"
                  ,xxas_emp_view_util_pkg.review_method_exists_flag(:pViewName) as "twoStepSubmit")
                  ,XMLElement
                    ("Rows"
                    ,XMLAgg
                      (XMLElement
                        ("Row"
                        ,XMLAttributes
                          (r.update_allowed as "updateAllowed"
                          ,r.delete_allowed as "deleteAllowed")
                        ,XMLElement
                          ("Key"
                          ,XMLForest
                            (data_object as "DataObject"
                            ,instance_pk1_value as "InstancePk1Value"
                            ,instance_pk2_value as "InstancePk2Value"
                            ,instance_pk3_value as "InstancePk3Value"
                            ,instance_pk4_value as "InstancePk4Value"
                            ,instance_pk5_value as "InstancePk5Value"
                            ,object_version_number as "ObjectVersionNumber"))    
                        ,XMLElement
                          ("ViewFields"
                          ,XMLForest
                            (status_color as "StatusColor"
                            ,status_text as "StatusText"
                            ,group_name as "GroupName"
                            ,group_icon as "GroupIcon"
                            ,group_color as "GroupColor"
                            ,summary_icon as "SummaryIcon"
                            ,summary_context as "SummaryContext"
                            ,summary_text as "SummaryText"
                            ,summary_message as "SummaryMessage"
                            ,summary_decorator as "SummaryDecorator"
                            ,timeline_text as "TimelineText"
                            ,timeline_icon as "TimelineIcon"
                            ,detail_header_context as "DetailHeaderContext"
                            ,detail_header_text as "DetailHeaderText"
                            ,detail_header_message as "DetailHeaderMessage"
                            ,show_attachments as "ShowAttachments"
                            ,attachments_label as "AttachmentsLabel"
                            ,item1_prompt as "Item1Prompt"
                            ,item1_html as "Item1Html"
                            ,item2_prompt as "Item2Prompt"
                            ,item2_html as "Item2Html"
                            ,item3_prompt as "Item3Prompt"
                            ,item3_html as "Item3Html"
                            ,item4_prompt as "Item4Prompt"
                            ,item4_html as "Item4Html"
                            ,item5_prompt as "Item5Prompt"
                            ,item5_html as "Item5Html"
                            ,item6_prompt as "Item6Prompt"
                            ,item6_html as "Item6Html"
                            ,item7_prompt as "Item7Prompt"
                            ,item7_html as "Item7Html"
                            ,item8_prompt as "Item8Prompt"
                            ,item8_html as "Item8Html"
                            ,item9_prompt as "Item9Prompt"
                            ,item9_html as "Item9Html"
                            ,item10_prompt as "Item10Prompt"
                            ,item10_html as "Item10Html"
                            ,item11_prompt as "Item11Prompt"
                            ,item11_html as "Item11Html"
                            ,item12_prompt as "Item12Prompt"
                            ,item12_html as "Item12Html"
                            ,item13_prompt as "Item13Prompt"
                            ,item13_html as "Item13Html"
                            ,item14_prompt as "Item14Prompt"
                            ,item14_html as "Item14Html"
                            ,item15_prompt as "Item15Prompt"
                            ,item15_html as "Item15Html"
                            ,item16_prompt as "Item16Prompt"
                            ,item16_html as "Item16Html"
                            ,item17_prompt as "Item17Prompt"
                            ,item17_html as "Item17Html"
                            ,item18_prompt as "Item18Prompt"
                            ,item18_html as "Item18Html"
                            ,item19_prompt as "Item19Prompt"
                            ,item19_html as "Item19Html"
                            ,item20_prompt as "Item20Prompt"
                            ,item20_html as "Item20Html"
                            ,item21_prompt as "Item21Prompt"
                            ,item21_html as "Item21Html"
                            ,item22_prompt as "Item22Prompt"
                            ,item22_html as "Item22Html"
                            ,item23_prompt as "Item23Prompt"
                            ,item23_html as "Item23Html"
                            ,item24_prompt as "Item24Prompt"
                            ,item24_html as "Item24Html"
                            ,item25_prompt as "Item25Prompt"
                            ,item25_html as "Item25Html"
                            ,item26_prompt as "Item26Prompt"
                            ,item26_html as "Item26Html"
                            ,item27_prompt as "Item27Prompt"
                            ,item27_html as "Item27Html"
                            ,item28_prompt as "Item28Prompt"
                            ,item28_html as "Item28Html"
                            ,item29_prompt as "Item29Prompt"
                            ,item29_html as "Item29Html"
                            ,item30_prompt as "Item30Prompt"
                            ,item30_html as "Item30Html") column_value)
                            ,workflow_details
                            ,XMLElement
                              ("WfDetailsHref"
                              ,wf_details_href) 
                            ,attachment_details)))) "EmpView"
          FROM table(xxas_emp_views_pkg.get_view_tbl
                      (:pViewName, :pPrimaryKeyId,  :pEffectiveDate
                      ,:pPrimaryKeyId2, :pPrimaryKeyId3, :pPrimaryKeyId4, :pPrimaryKeyId5, nvl(:pWfDetailsHref,''N''))) r ';

  xxas_com_sr_pkg.seed_query_service
    (p_service_name        => 'QueryEmpFlexibleView'
    ,p_securing_function   => 'XXAS_SRV_Q_EMP_FLEX_VIEW_PERM'
    ,p_sql                 => l_sql
    ,p_use_iso_date_format => false
    ,p_parameters          => xxas_com_sr_param_varray
                                (xxas_com_sr_param_type('pViewName'      , xxas_com_sr_pkg.g_VARCHAR2)
                                ,xxas_com_sr_param_type('pPrimaryKeyId'  , xxas_com_sr_pkg.g_VARCHAR2)
                                ,xxas_com_sr_param_type('pPrimaryKeyId2' , xxas_com_sr_pkg.g_VARCHAR2)
                                ,xxas_com_sr_param_type('pPrimaryKeyId3' , xxas_com_sr_pkg.g_VARCHAR2)
                                ,xxas_com_sr_param_type('pPrimaryKeyId4' , xxas_com_sr_pkg.g_VARCHAR2)
                                ,xxas_com_sr_param_type('pPrimaryKeyId5' , xxas_com_sr_pkg.g_VARCHAR2)
                                ,xxas_com_sr_param_type('pEffectiveDate' , xxas_com_sr_pkg.g_DATE)
                                ,xxas_com_sr_param_type('pWfDetailsHref' , xxas_com_sr_pkg.g_VARCHAR2)));

END;
/
COMMIT;
EXIT;
