/* $Id$ */
/*============================================================================*
 |                 Copyright (c) 2014 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_SERVICE CONSTANT xxas_com_service_repository.service_name%TYPE
                       := 'Goals';
                       
BEGIN
      
  xxas_com_sr_pkg.set_rest_service
    (p_service_name       => l_SERVICE
    ,p_description        => 'Manage goals' 
    ,p_path               => 'goals'    
    ,p_consumes           => 'application/xml'
    ,p_produces           => 'application/xml'
    );
    
    xxas_com_sr_pkg.set_rest_method
    (p_service_name       => l_SERVICE
    ,p_method_name        => 'FindCascadedGoals'
    ,p_description        => 'Finds goals within a plan'
    ,p_methods            => 'GET'
    ,p_remove_root_ele    => true
    ,p_path               => 'cascaded/{enrollmentId}'
    ,p_sql                =>
      q'[   select XMLElement
                 ("data"
                 ,XMLAttributes
                   ('true' as "json:force-array"
                   ,'http://json.org/' as "xmlns:json")
                 ,XMLAgg(data) ) "data" from
           (SELECT XMLElement
                 ("goal"
           ,XMLElement --author
           ("data" ,
              XMLForest (
               b.person_id "id"
               ,'note' "type"
               ,nvl(xxas_util_pkg.get_formatted_name
                (papf.full_name
                ,papf.global_name
                ,papf.local_name
                ,papf.first_name
                ,papf.last_name)
              ,papf.last_name) "author"  )
            ).extract('/data/*')--author
           ,XMLElement
                 ("labels"
                 ,XMLAttributes
                   ('true' as "json:force-array"
                   ,'http://json.org/' as "xmlns:json")
                 ,XMLElement("data"
                            ,XMLFOREST(
                              xxas_com_msg_pkg.get_decoded_string(
                                xxas_com_msg_type(xxas_util_pkg.app
                                                 ,'XXAS_PERF_LINE_MANAGER'
                                                 ,xxas_com_msg_token_varray(xxas_com_msg_token_type('FORMAT',to_char(to_date(hier.hier_level,'YY'),'yth'))
                                                                           )
                                                 )) as  "title"
                              ,'positive' as "color"
                                      )
                            ) 
                  )
           ,XMLElement --Linked.image 
           ("linked"
           ,XMLElement
           ("image"
           ,XMLElement
              ("self"
              ,xxas_com_rest_util_pkg.uri_from_path
                ('people/' || nvl(b.person_id, -1) || '/photo'))
           ,CASE WHEN b.person_id IS NOT NULL THEN
              XMLForest
                (xxas_per_image_pkg.url(b.person_id, t.last_update_date, 'SMALL') "small"
                ,xxas_per_image_pkg.url(b.person_id, t.last_update_date, 'MEDIUM') "medium"
                ,xxas_perf_util_pkg.get_initials(b.person_id,sysdate) "initials"
                )
            END
           ) 
           )--Linked.image 

          ,(XMLElement
                     ("body",
                       --dbms_xmlgen.convert
                                     (XMLElement
                                       ( "ul",
                                         (XMLAgg --Body
                                          (XMLElement
                                           ("data"
                                            ,XMLForest
                                             (CASE WHEN xgv.goal_state = 'A' THEN xgv.goal
                                                   ELSE xxas_perf_util_pkg.get_last_approved_goal( p_goal_id     => xgv.goal_id) 
                                              END "li"
                                             )
                                           )
                                           ).extract('/data/*')
                                         ) "body" --Body 
                                        ).getClobVal() 
                                      )
                      )
              )
                 ) as data

   FROM xxas_goals_v xgv,
        xxas_perf_plan_enrollments_v b,
        xxas_perf_plan_enrollments_v RSLT,
        xxas_per_thumbnails t,
        per_all_people_f papf,
        (SELECT *
         FROM TABLE(xxas_com_hier_pkg.walk_up_asg_tbl ( (
                                                         SELECT xppf2.assignment_id
                                                         FROM xxas_perf_plan_enrollments xppf2
                                                         WHERE xppf2.perf_plan_enrollment_id = {pathParam.enrollmentId} ),
                                                             trunc ( sysdate ) ))
                                                  WHERE hier_level &#60;= nvl({requestParam.levels},999)) hier
  WHERE b.person_id = hier.person_id 
    AND xgv.access_code = 'S'
    AND xxas_perf_util_pkg.was_goal_visible(xgv.goal_id) = 'Y'
    AND xgv.perf_plan_enrollment_id = b.perf_plan_enrollment_id
    AND  RSLT.perf_plan_enrollment_id = {pathParam.enrollmentId}
    AND  RSLT.perf_plan_id = b.perf_plan_id
    AND b.person_id = t.person_id (+)
    AND b.person_id = papf.person_id
    AND EXISTS
    (
      SELECT 1 
      FROM xxas_goals_v xgvc
      WHERE xgvc.parent_goal_id = xgv.goal_id
      AND   xgvc.person_id = RSLT.person_id
    )
    AND {authFunctionPredicate.XXAS_DT_PERF_ENROLLMENT}
    AND trunc ( sysdate )
        BETWEEN papf.effective_start_date
            AND papf.effective_end_date
GROUP BY b.person_id,
         t.last_update_date,
         papf.full_name,
         papf.local_name,
         papf.global_name,
         papf.first_name,
         papf.last_name,
         hier.hier_level
         )
      ]'
    );       
  
  xxas_com_sr_pkg.set_rest_method
    (p_service_name       => l_SERVICE
    ,p_method_name        => 'FindGoalHistory'
    ,p_description        => 'Finds history of goals'
    ,p_methods            => 'GET'
    ,p_remove_root_ele    => true
    ,p_path               => 'goal-history/{personId}'
    ,p_sql                =>    
      q'[  SELECT 
    XMLElement
      ("data"
         ,XMLAttributes
            ('true' as "json:force-array"
            ,'http://json.org/' as "xmlns:json")
        ,XMLAgg(
           XMLElement("goal"
                   ,XMLElement ("data" ,--author
                      XMLForest ( a.person_id "id"
                                 ,'note' "type"
                                 --,'Just Now' "noteType"
                                 ,CASE WHEN a.days>1 THEN a.days||'d'
                                       WHEN a.days=1 THEN '1d'
                                       WHEN a.days=0 THEN xxas_com_msg_pkg.get_message('XXAS','XXAS_RB_EMP_TODAY')
                                       ELSE NULL
                                  END "dateCreatedFormatted"
                                 ,nvl(xxas_perf_util_pkg.get_formatted_name(a.person_id) ,RSLT.last_name)
                                        ||' - '
                                        ||COUNT(DISTINCT goal_id)
                                        ||CASE WHEN COUNT(distinct goal_id) >1  
                                               THEN ' '||xxas_com_msg_pkg.get_message('XXAS','XXAS_PERF_GOAL_GOALS')
                                               ELSE ' '||xxas_com_msg_pkg.get_message('XXAS','XXAS_PERF_GOAL_GOAL')
                                          END 
                                        ||' '||xxas_com_msg_pkg.get_message('XXAS','XXAS_PERF_GOAL_CHANGED')"author"  )
                                ).extract('/data/*')--author
                   ,XMLElement --Linked.image 
                       ("linked"
                        ,XMLElement
                         ("image"
                          ,XMLElement
                            ("self"
                             ,xxas_com_rest_util_pkg.uri_from_path ('people/' || nvl(a.person_id, -1) || '/photo'))
                             ,CASE WHEN a.person_id IS NOT NULL THEN
                                XMLForest
                                 (xxas_per_image_pkg.url(a.person_id, t.last_update_date, 'SMALL') "small"
                                 ,xxas_per_image_pkg.url(a.person_id, t.last_update_date, 'MEDIUM') "medium"
                                 ,xxas_perf_util_pkg.get_initials(a.person_id,sysdate) "initials" )
                               END
                         ) 
                       )--Linked.image
                   ,XMLElement
                     ("body",
                       (XMLElement
                         ( "ul",
                           (XMLAgg --Body
                             (XMLElement
                              ("li"
                               ,CASE WHEN xxas_emp_menu_pkg.has_access_to_page('view-goal', a.goal_id) = 'Y' THEN XMLElement
                                ("a",
                                 XMLAttributes
                                  ('#/'||{requestParam.navigateFrom}||'/'||{pathParam.personId}||'?modal=/view-goal/' ||a.goal_id as "href"), 
                                    XMLFOREST 
                                      ( CASE WHEN nvl(a.comments,0)>0  
                                         THEN DECODE(a.comments, 1, a.comments||' '||xxas_com_msg_pkg.get_message('XXAS','XXAS_PERF_COMMENT'),
                                                    a.comments||' '||xxas_com_msg_pkg.get_message('XXAS','XXAS_RB_TCM_COMMENTS')) 
                                                    ||' ' ||xxas_com_msg_pkg.get_message('XXAS','XXAS_EMP_ON') ||'  '
                                        END 
                                        ||'"'||SUBSTR(goal,1,100)||'"'||
                                        CASE WHEN length(goal) >100 
                                         THEN '...'
                                         ELSE null 
                                        END "em"
                                      )
                                ) ELSE
                                 XMLELEMENT 
                                      ("em", '"'||SUBSTR(goal,1,100)||'"'||
                                        CASE WHEN length(goal) >100 
                                         THEN '...'
                                         ELSE null 
                                        END 
                                      )END
                               ,XMLFOREST (CASE WHEN a.goal_days = a.days
                                            THEN DECODE(action,'UPDATE',' : '||xxas_goals_pkg.history_changes(a.changes,a.goal_id),null) 
                                           END "b")
                                    
                               ,CASE WHEN a.goal_days = a.days 
                                 THEN (DECODE ( action,
                                                'CREATE',' - '||xxas_com_msg_pkg.get_message('XXAS','XXAS_PERF_GOAL_ADDED'),
                                                'UPDATE',' - '||xxas_com_msg_pkg.get_message('XXAS','XXAS_PERF_GOAL_CHANGED'),
                                                'DELETE',' - '||xxas_com_msg_pkg.get_message('XXAS','XXAS_PERF_GOAL_DELETED1'),
                                                action )
                                      ) 
                                END 
                              ) ORDER BY a.days --,a.hours, a.minutes
                            )
                          ) "body" 
                        ).getStringVal()
                      )
                    )
                  ) 
                  ORDER BY a.days 
                )
      ) as data
   FROM (SELECT a.goal_person_id person_id,
                a.goal_id,
                a.goal,
                a.action,
                a.changes,
                a.days goal_days,
                b.days review_days,
                least ( nvl ( b.days,
                              a.days ),
                        nvl ( a.days,
                              b.days ) ) days,
                b.count comments
           FROM 
           (SELECT a1.* FROM xxas_goals_history_v a1
           WHERE a1.last_update_date >= (trunc(SYSDATE) - nvl({requestParam.historyDays},365))
           )a
                FULL OUTER JOIN (
                 SELECT b.goal_id,
                        b.days,
                        b.person_id,
                        count ( 1 ) count
                   FROM xxas_goal_reviews_hist_v b
                  WHERE b.action = 'REVIEW' and b.person_id in (SELECT column_value 
                                                              FROM TABLE(xxas_com_hier_pkg.drs_for_per_tbl( {pathParam.personId},trunc(sysdate) )) 
                                                             )
                        and b.last_update_date >= (trunc(SYSDATE) - nvl({requestParam.historyDays},365))
                GROUP BY b.goal_id,
                         b.days,
                         b.person_id ) b
                     ON a.goal_id = b.goal_id
        where a.goal_person_id in (SELECT column_value
                           FROM TABLE(xxas_com_hier_pkg.drs_for_per_tbl ( {pathParam.personId},
                                                                     trunc ( sysdate ) )) )
        ORDER BY days) a,
        per_all_people_f RSLT,
        xxas_per_thumbnails t
  WHERE a.person_id = t.person_id (+)
    AND a.person_id = RSLT.person_id
    AND a.days &#60;= nvl({requestParam.historyDays},9999)
    AND trunc ( sysdate )
        BETWEEN RSLT.effective_start_date
            AND RSLT.effective_end_date
    AND {authFunctionPredicate.XXAS_DT_GOALS_HIS}
  GROUP BY a.person_id,
           a.days,
           RSLT.last_name,
           t.last_update_date
      ]'
    );     
   
    xxas_com_sr_pkg.set_rest_method
    (p_service_name       => l_SERVICE
    ,p_method_name        => 'FindPlanGoals'
    ,p_description        => 'Finds goals within a plan'
    ,p_methods            => 'GET'
    ,p_remove_root_ele    => true
    ,p_path               => 'by-plan/{planId}/{personId}'
    ,p_sql                =>
      q'[ SELECT XMLElement
                 ("data"
                 ,XMLAttributes
                   ('true' as "json:force-array"
                   ,'http://json.org/' as "xmlns:json")
                 ,XMLAgg
                   (XMLElement
                     ("goal"
                     ,XMLForest
                       (data.goal_id "id"
                       ,data.goal "value"
                       ,CASE WHEN LENGTH(data.goal) > 80 
                             THEN SUBSTR(data.goal,1,80)||'...' 
                             ELSE data.goal
                             END || 
                                CASE WHEN allow =1 THEN
                                  ' - (' || xxas_perf_util_pkg.get_formatted_name(data.person_id) || ')'
                                END                                
                                   "label"
                       ,'flexible' "type"
                       ,data.goal "line1")
                     )
                   ORDER BY data.hier_level,
                            dbms_lob.substr(REGEXP_REPLACE(lower(data.goal), '&lt;[a-zA-Z/].*?&gt;'),100, 1)                            
                   )
                 ) "data" 
          FROM 
           (SELECT rslt.person_id
                   ,rslt.goal_id
                   ,CASE WHEN rslt.goal_state = 'A' THEN rslt.goal
                         ELSE xxas_perf_util_pkg.get_last_approved_goal( p_goal_id     => rslt.goal_id) 
                    END goal
                   ,hier.hier_level
                   ,CASE WHEN MAX(hier_level) OVER ()  = MIN(hier_level) OVER ()
                         THEN -1
                         ELSE 1
                         END
                    allow
            FROM xxas_goals_v rslt,
                (SELECT person_id 
                       ,hier_level
                 FROM   TABLE(xxas_com_hier_pkg.walk_up_asg_tbl
                                (xxas_util_pkg.get_primary_worker_asg({pathParam.personId},trunc(SYSDATE))
                                ,trunc(SYSDATE)
                                )
                             )
                 WHERE hier_level &#60;= nvl({requestParam.levels},999)
                ) hier
            WHERE (({requestParam.id} IS NOT NULL and rslt.goal_id = {requestParam.id}) OR 
                                          ({requestParam.id} IS NULL AND UPPER(goal) LIKE UPPER({requestParam.q})||'%'))
            AND     rslt.perf_plan_enrollment_id IN (
                                         SELECT xppf.perf_plan_enrollment_id
                                           FROM xxas_perf_plan_enrollments xppf
                                           WHERE xppf.perf_plan_id ={pathParam.planId} )
            AND    rslt.person_id = hier.person_id 
            AND    xxas_perf_util_pkg.was_goal_visible(rslt.goal_id) = 'Y'
            AND    {authFunctionPredicate.XXAS_DT_FIND_GOALS}
           ) data
      ]'
    ); 
    
   xxas_com_sr_pkg.set_rest_method
    (p_service_name       => l_SERVICE
    ,p_method_name        => 'GetGoalsByEnrollment'
    ,p_description        => 'Gets goals by enrollment'
    ,p_methods            => 'GET'
    ,p_remove_root_ele    => true
    ,p_path               => 'by-enrollment/{functionName}/{enrollmentId}'
    ,p_sql                =>
      q'[
         SELECT XMLElement("data"
                 ,XMLAttributes
                   ('true' as "json:force-array"
                   ,'http://json.org/' as "xmlns:json")
                   , xxas_com_sec_pkg.get_obj_data_w_binds
          ({pathParam.functionName}
          ,xxas_com_varchar2_table
            ('PERF_PLAN_ENROLLMENT_ID = :ENROLLMENT_ID'
            ,decode({requestParam.typeCode},null, null
                   ,'GOAL_TYPE_CODE IN
                      (SELECT column_value
                       FROM   TABLE(xxas_util_pkg.string_to_varchar2tbl
                                     (:GOAL_TYPE_CODE)))')
            ,decode({requestParam.categoryCode},null, null
                   ,'CATEGORY_CODE IN
                      (SELECT column_value
                       FROM   TABLE(xxas_util_pkg.string_to_varchar2tbl
                                     (:CATEGORY_CODE)))')
            )
          ,xxas_com_srv_param_varray
            (xxas_com_srv_param_value
              ('ENROLLMENT_ID', {pathParam.enrollmentId})
            ,decode({requestParam.typeCode}, null, null
                   ,xxas_com_srv_param_value
                      ('GOAL_TYPE_CODE', {requestParam.typeCode}))
            ,decode({requestParam.categoryCode}, null, null
                   ,xxas_com_srv_param_value
                      ('CATEGORY_CODE', {requestParam.categoryCode}))
            )
          , null -- p_rowset_tag_name 
          ,'item' -- p_row_tag_name 
          ,{requestParam.pageNumber}
          ,{requestParam.perPage})) "data"
      FROM dual
      ]'
    ); 
    
  xxas_com_sr_pkg.set_rest_method
    (p_service_name       => l_SERVICE
    ,p_method_name        => 'GetGoalListByEnrollmentGoalBased'
    ,p_description        => 'Gets goals-list by enrollment based on permission on goals'
    ,p_methods            => 'GET'
    ,p_remove_root_ele    => true
    ,p_path               => 'by-enrollment/goals-based/{enrollmentId}'
    ,p_sql                =>    
      q'[
       SELECT 
         XMLElement
          ("data",
           XMLAttributes
             ('true' as "json:force-array",
              'http://json.org/' as "xmlns:json"),
           XMLAgg
             (XMLElement
               ("item",
                 XMLForest
                   ('/view-goal/'||rslt.goal_id  "href",
                     'modal'  "target",
                     nvl({requestParam.modalWidth},'medium') as "modalWidth",
                     rslt.goal_id "id",
                     'goal' "type",
                     rslt.goal_type_meaning,
                     rslt.category_meaning,
                     rslt.creation_date,
                     rslt.last_update_date,
                     CASE WHEN rslt.goal_state = 'A' THEN rslt.goal
                          ELSE xxas_perf_util_pkg.get_last_approved_goal(p_goal_id     => rslt.goal_id) 
                     END "title",
                     "statusUpdateAllowed",
                     CASE WHEN xxas_com_sec_pkg.test_instance('XXAS_DT_GOAL_W_UPD', rslt.goal_id) = 'Y' THEN
	                          'true'
                      ELSE
                          'false'
                      END "weightingUpdateAllowed",
                     CASE WHEN xxas_com_sec_pkg.test_instance('XXAS_DT_GOAL_C_UPD', rslt.goal_id) = 'Y' THEN
                           'true'
                      ELSE
                          'false'
                      END "completionUpdateAllowed",
                     CASE WHEN rslt.goal_state = 'A' THEN rslt.goal_type_meaning 
                                                         || DECODE(rslt.category_meaning,null,null,' - ')
                                                         || rslt.category_meaning
                          ELSE xxas_util_pkg.decode_lookup ('XXAS_GOAL_TYPE'
                                                           ,xxas_perf_util_pkg.get_last_approved_goal_info
                                                             (p_goal_id => rslt.goal_id
                                                             ,p_column_name => 'GOAL_TYPE_CODE')) 
                              || DECODE(xxas_perf_util_pkg.get_last_approved_goal_info(p_goal_id => rslt.goal_id
                                                                             ,p_column_name => 'CATEGORY_CODE') ,null,null,' - ')
                              ||xxas_util_pkg.decode_lookup ('XXAS_GOAL_CATEGORY'
                                                             ,xxas_perf_util_pkg.get_last_approved_goal_info
                                                               (p_goal_id => rslt.goal_id
                                                               ,p_column_name => 'CATEGORY_CODE'))
                     END "label" ),
                 XMLElement("status",
                     XMLForest(rslt.status_code    AS "id",
                               rslt.status_meaning AS "label")) "status",
                 CASE WHEN rslt.goal_state = 'A' AND rslt.weighting_percent IS NOT NULL 
                      THEN XMLElement("weighting",
                             XMLForest(xxas_com_msg_pkg.get_string('XXAS_GOAL_WEIGHTING')         AS "label",  
                                       rslt.weighting_percent AS "value"))
                      WHEN rslt.goal_state != 'A' AND xxas_perf_util_pkg.get_last_approved_goal_info(p_goal_id => rslt.goal_id
                                                                                             ,p_column_name => 'WEIGHTING_PERCENT') IS NOT NULL  
                                     THEN XMLElement("weighting",
                                             XMLForest(xxas_com_msg_pkg.get_string('XXAS_GOAL_WEIGHTING')          AS "label",
                                                       xxas_perf_util_pkg.get_last_approved_goal_info(p_goal_id => rslt.goal_id
                                                                                             ,p_column_name => 'WEIGHTING_PERCENT') AS "value"))                                                                              
                 END "weighting",
                 CASE WHEN rslt.goal_state = 'A' AND rslt.complete_percent IS NOT NULL 
                      THEN XMLElement("completion",
                             XMLForest(xxas_com_msg_pkg.get_string('XXAS_GOAL_COMPLETION')         AS "label",
                                       rslt.complete_percent AS "value"))
                      WHEN rslt.goal_state != 'A' AND xxas_perf_util_pkg.get_last_approved_goal_info(p_goal_id => rslt.goal_id
                                                                                             ,p_column_name => 'COMPLETE_PERCENT') IS NOT NULL  
                                     THEN XMLElement("completion",
                                             XMLForest(xxas_com_msg_pkg.get_string('XXAS_GOAL_COMPLETION')          AS "label",
                                                       xxas_perf_util_pkg.get_last_approved_goal_info(p_goal_id => rslt.goal_id
                                                                                             ,p_column_name => 'COMPLETE_PERCENT') AS "value"))                                                                              
                 END "completion",
                 XMLElement("icons",
                   XMLAttributes('true' AS "json:force-array",
                                 'http://json.org/' AS "xmlns:json"),
                     XMLElement("icons",
                        CASE WHEN rslt.parent_goal_id IS NOT NULL 
                             THEN XMLForest('alignment' AS "label",
                                            'Aligned goal' AS "name") 
                        END )) "icons",
                 XMLElement("links",
                   XMLElement("self",
                     XMLForest(xxas_com_rest_util_pkg.uri_from_path
                                ('goals/'||rslt.goal_id) AS "href"))),
                 XMLElement
                   ("linked",
                    CASE WHEN INSTR(lower(NVL({requestParam.includeComments},'none')),
                                               'none') > 0 THEN 
                             xxas_goals_pkg.goal_comment_actions
                               (p_goal_id => rslt.goal_id).get_xml() -- actions
                    END,
                   (SELECT XMLElement
                            ("statuses",
                             XMLAttributes('true' AS "json:force-array",
                                           'http://json.org/' AS "xmlns:json"),
                             XMLAgg
                              (XMLElement
                                 ("statuses",
                                   XMLForest(lookup_code                 AS "id",
                                             meaning                     AS "label",
                                             xxas_perf_util_pkg.get_icon_color(lookup_code) AS "color")
                                 )
                               )
                             )
                     FROM hr_lookups hrl
                    WHERE lower(NVL({requestParam.goalStatus},'view')) = 'view'
                      AND hrl.lookup_type = 'XXAS_GOAL_STATUS'
                      AND hrl.enabled_flag = 'Y'
                      AND trunc(sysdate)
                         BETWEEN nvl(hrl.start_date_active,trunc(sysdate))
                          AND nvl(hrl.end_date_active,trunc(sysdate))
                   ),
                   XMLElement
                     ("comments",
                      XMLAttributes
                        ('true' AS "json:force-array",'http://json.org/' AS "xmlns:json" ),
                           XMLConcat
                             ( 
                               (SELECT XMLElement -- Empty Item for New comment Begin
                                  ("item", 
                                    XMLForest
                                      ( 0 AS "id",
                                        rslt.goal_id AS "parentId",
                                        0 AS "reviewStepId",
                                        'note' AS "type",
                                        null   AS "body",
                                       'false' AS "required",
                                       'true' AS "editMode",
                                       'true' AS "disableAutoSave",
                                       lower(NVL({requestParam.richTextComment},'false'))  AS "htmlEditor",
                                       NVL({requestParam.commentMaxLength},3900)   AS "maxlength",
                                       xxas_com_msg_pkg.get_string('XXAS_PERF_PLACEHOLDER') AS "placeholder",
                                       xxas_com_msg_pkg.get_string('XXAS_RB_COM_SAVE') AS "submitButtonText",
                                       xxas_com_msg_pkg.get_string('XXAS_RB_COM_CAN')  AS "cancelButtonText",
                                        nvl(xxas_util_pkg.get_formatted_name
                                              (papf.full_name
                                              ,papf.global_name
                                              ,papf.local_name
                                              ,papf.first_name
                                              ,papf.last_name)
                                              ,papf.last_name) "author"
                                       ),
                                    XMLElement
                                      ("linked",
                                       XMLElement
                                         ("image",
                                            XMLElement
                                                ("self",
                                                  xxas_com_rest_util_pkg.uri_from_path
                                                    ('people/' || 
                                                      nvl(fnd_global.employee_id, -1) || 
                                                      '/photo')
                                                ),
                                                CASE WHEN fnd_global.employee_id IS NOT NULL THEN
                                                  XMLForest
                                                    (xxas_per_image_pkg.url(fnd_global.employee_id,
                                                                            t.last_update_date,
                                                                            'SMALL') "small"
                                                    ,xxas_per_image_pkg.url(fnd_global.employee_id,
                                                                            t.last_update_date, 
                                                                            'MEDIUM') "medium"
                                                    ,xxas_perf_util_pkg.get_initials(fnd_global.employee_id,sysdate) "initials"
                                                    )
                                                END
                                          ) 
                                      ),
                                    CASE WHEN xxas_com_sec_pkg.test_instance('XXAS_DT_GOAL_REV_RATING',
                                                                             rslt.goal_id) = 'Y' 
                                         AND xxas_perf_util_pkg.hide_details({requestParam.hideRForTypes},rslt.goal_type_code) = 'N'
                                         AND xxas_perf_util_pkg.hide_details({requestParam.hideRForCat},rslt.category_code) = 'N' THEN
                                      XMLElement
                                        ( "rating",
                                          (SELECT XMLElement
                                                    ("iconSteps",
                                                     XMLAttributes('true' AS "json:force-array",
                                                                   'http://json.org/' AS "xmlns:json" ),
                                                     XMLAgg
                                                          (XMLElement
                                                            ("statuses",
                                                             XMLForest
                                                               (lookup_code   AS "id",
                                                                meaning       AS "label",
                                                                nvl(xxas_perf_util_pkg.get_rating_icon_color(xppe.perf_plan_enrollment_id),'energized') AS "color"
                                                                )
                                                             )
                                                           )
                                                    )
                                             FROM (SELECT * 
                                                     FROM hr_lookups hrl
                                                    WHERE hrl.lookup_type = xxas_perf_util_pkg.get_goal_rating_lookup({pathParam.enrollmentId})
                                                      AND hrl.enabled_flag = 'Y'
                                                      AND trunc(sysdate)
                                                               BETWEEN nvl(hrl.start_date_active,trunc(sysdate))
                                                                   AND nvl(hrl.end_date_active,trunc(sysdate)) 
                                                    ORDER BY lookup_code )
                                          ),
                                          XMLForest
                                            (CASE WHEN xxas_perf_util_pkg.get_rating_icon_name(xppe.perf_plan_enrollment_id) IS NULL 
                                                       AND xxas_perf_util_pkg.get_rating_icon_color(xppe.perf_plan_enrollment_id) IS NULL 
                                                 THEN 'true' 
                                                 ELSE 'false'
                                                 END AS "showAsSelectSingle",
                                                 'false' AS "required",
                                             nvl(xxas_perf_util_pkg.get_rating_icon_name(xppe.perf_plan_enrollment_id),'starfilled') AS "icon")
                                        )  
                                    END,
                                    XMLElement
                                      ( "links",
                                        XMLElement
                                          ( "comments",
                                             XMLForest
                                               (xxas_com_rest_util_pkg.uri_from_path
                                                 ('goals/goal-review-create/' ||
                                                   rslt.goal_id )AS "href")
                                          )
                                      )                        
                                  ) FROM dual, 
                                         xxas_per_thumbnails t,
                                         per_all_people_f papf
                                   WHERE papf.person_id = t.person_id (+)
                                     AND fnd_global.employee_id = papf.person_id
                                     AND TRUNC(sysdate)
                                           BETWEEN papf.effective_start_date
                                               AND papf.effective_end_date
                                     AND xxas_com_sec_pkg.test_instance('XXAS_DT_GOAL_REV_CRE',rslt.goal_id) = 'Y'
                                     AND xxas_perf_util_pkg.hide_details({requestParam.hideCForTypes},rslt.goal_type_code) = 'N'
                                     AND xxas_perf_util_pkg.hide_details({requestParam.hideCForCat},rslt.category_code) = 'N'
                                     AND INSTR(lower
                                              (NVL({requestParam.includeComments},'none')),
                                               'none') = 0
                               ),-- Empty Item for New comment End
                               ( SELECT 
                                   XMLAgg
                                     ( XMLElement
                                       ( "item",
                                         XMLForest
                                           ( xgrv.goal_review_id  AS "id",
                                             xgrv.goal_id         AS "parentId",
                                             0                    AS "reviewStepId",
                                             'note'               AS "type",
                                             'false'              AS "required",                                               
                                             xgrv.comments        AS "body",
                                             'false'               AS "disableAutoSave",
                                             lower(NVL({requestParam.richTextComment},'false'))  AS "htmlEditor",
                                             NVL({requestParam.commentMaxLength},3900)   AS "maxlength",
                                             xxas_com_msg_pkg.get_string('XXAS_PERF_PLACEHOLDER') AS "placeholder",
                                             xxas_com_msg_pkg.get_string('XXAS_RB_COM_SAVE') AS "submitButtonText",
                                             xxas_com_msg_pkg.get_string('XXAS_RB_COM_CAN')  AS "cancelButtonText",
                                             xxas_util_pkg.days_ago(xgrv.last_update_date) AS "dateCreatedFormatted",
                                             CASE WHEN xgrv.perf_review_step_id IS NOT NULL THEN 
                                                xxas_perf_util_pkg.get_form_step_name(xgrv.perf_review_step_id)
                                             END
                                              AS "noteType",
                                             nvl(xxas_util_pkg.get_formatted_name
                                               (papf.full_name
                                               ,papf.global_name
                                               ,papf.local_name
                                               ,papf.first_name
                                               ,papf.last_name)
                                               ,papf.last_name) "author"
                                           ),
                                         XMLElement
                                           ("linked",
                                             XMLElement
                                               ("image",
                                                 XMLElement
                                                   ("self",
                                                     xxas_com_rest_util_pkg.uri_from_path
                                                     ('people/' 
                                                       || nvl(xgrv.reviewer_person_id, -1) 
                                                       || '/photo')
                                                   ),
                                                 CASE WHEN xgrv.reviewer_person_id IS NOT NULL THEN
                                                    XMLForest
                                                     (xxas_per_image_pkg.url(xgrv.reviewer_person_id,
                                                                             t.last_update_date,
                                                                             'SMALL') "small"
                                                     ,xxas_per_image_pkg.url(xgrv.reviewer_person_id, 
                                                                             t.last_update_date, 
                                                                             'MEDIUM') "medium"
                                                     ,xxas_perf_util_pkg.get_initials(xgrv.reviewer_person_id,sysdate) "initials"
                                                     )
                                                 END
                                               ),
                                             xxas_goal_review_pkg.goal_comment_actions(p_goal_id => xgrv.goal_id -- actions
                                                                                     ,p_goal_review_id => xgrv.goal_review_id).get_xml() 
                                           ),
                                         CASE WHEN xxas_com_sec_pkg.test_instance('XXAS_DT_GOAL_REV_RATING'
                                                                                ,rslt.goal_id) = 'Y' 
                                              AND xxas_perf_util_pkg.hide_details({requestParam.hideRForTypes},rslt.goal_type_code) = 'N'
                                              AND xxas_perf_util_pkg.hide_details({requestParam.hideRForCat},rslt.category_code) = 'N' THEN
                                           XMLElement
                                             ("rating",
                                               (SELECT XMLElement
                                                         ("iconSteps",
                                                          XMLAttributes('true' AS "json:force-array",
                                                                        'http://json.org/' AS "xmlns:json" ),
                                                          XMLAgg
                                                            (XMLElement
                                                              ("statuses",
                                                               XMLForest
                                                                 (lookup_code      AS "id"
                                                                  ,meaning         AS "label"
                                                                  ,nvl(xxas_perf_util_pkg.get_rating_icon_color(xppe.perf_plan_enrollment_id),'energized') AS "color"
                                                                 )
                                                              )
                                                            )
                                                         )
                                                   FROM (SELECT * 
                                                           FROM hr_lookups hrl
                                                          WHERE hrl.lookup_type = xxas_perf_util_pkg.get_goal_rating_lookup({pathParam.enrollmentId})
                                                            AND hrl.enabled_flag = 'Y'
                                                            AND trunc(sysdate )
                                                                  BETWEEN nvl(hrl.start_date_active,trunc(sysdate))
                                                                      AND nvl(hrl.end_date_active,trunc(sysdate)) 
                                                         ORDER BY lookup_code
                                                        )
                                               ),
                                               XMLForest
                                                   (CASE WHEN xxas_perf_util_pkg.get_rating_icon_name(xppe.perf_plan_enrollment_id) IS NULL 
                                                               AND xxas_perf_util_pkg.get_rating_icon_color(xppe.perf_plan_enrollment_id) IS NULL 
                                                         THEN 'true' 
                                                         ELSE 'false'
                                                         END AS "showAsSelectSingle",
                                                    nvl(xxas_perf_util_pkg.get_rating_icon_name(xppe.perf_plan_enrollment_id),'starfilled') AS "icon",
                                                    xgrv.rating AS "value",
                                                    'false' AS "required"
                                                    )
                                             ) 
                                         END,                                                    
                                         XMLElement
                                           ("links",
                                             XMLElement
                                               ("comments",
                                                 XMLForest
                                                   (xxas_com_rest_util_pkg.uri_from_path
                                                     ('goals/goal-review-update/' ||xgrv.goal_review_id) AS "href")
                                               )
                                           ) "links"
                                       )ORDER BY xgrv.last_update_date desc
                                     )
                                 FROM xxas_goal_reviews_v xgrv,
                                      xxas_per_thumbnails t,
                                      per_all_people_f papf
                                WHERE xgrv.goal_id            = rslt.goal_id
                                  AND papf.person_id          = t.person_id (+)
                                  AND xgrv.reviewer_person_id = papf.person_id
                                  AND trunc(sysdate)
                                        BETWEEN papf.effective_start_date
                                        AND papf.effective_end_date
                                  AND xxas_com_sec_pkg.test_instance('XXAS_DT_GOAL_REV_VIEW',xgrv.goal_review_id) = 'Y'
                                  AND xxas_perf_util_pkg.hide_details({requestParam.hideCForTypes},rslt.goal_type_code) = 'N'
                                  AND xxas_perf_util_pkg.hide_details({requestParam.hideCForCat},rslt.category_code) = 'N'
                                  AND INSTR(lower
                                              (NVL({requestParam.includeComments},'none')),
                                               'none') = 0 
                                  AND ( 
                                        (INSTR(lower
                                              (NVL({requestParam.includeComments},'none')),
                                               'adhoc') > 0
                                         AND
                                         xgrv.perf_review_step_id IS NULL
                                        )
                                          OR
                                        ( 
                                          xgrv.perf_review_step_id IS NOT NULL
                                          AND
                                          INSTR(lower
                                                  (NVL({requestParam.includeComments},'none')),
                                                        'inreviews') > 0
                                        )
                                      )
                             )
                        )
                     )
                  ) -- Linked
                )
             ) -- XMLAgg
           ) "data" 
         FROM xxas_perf_plan_enrollments_v xppe,
              xxas_goals_v rslt
        WHERE xppe.perf_plan_enrollment_id = rslt.perf_plan_enrollment_id
          AND xppe.perf_plan_enrollment_id = {pathParam.enrollmentId}
          AND xxas_perf_util_pkg.was_goal_visible(rslt.goal_id) = 'Y'
          AND ({requestParam.typeCode} IS NULL OR 
               rslt.goal_type_code = nvl({requestParam.typeCode},rslt.goal_type_code))
          AND ({requestParam.categoryCode} IS NULL OR 
               rslt.category_code = nvl({requestParam.categoryCode},rslt.category_code))
          AND {authFunctionPredicate.XXAS_DT_PERF_GOALS}
     ]'
    );       

   xxas_com_sr_pkg.set_rest_method
    (p_service_name       => l_SERVICE
    ,p_method_name        => 'GetGoalListByEnrollment'
    ,p_description        => 'Gets goals-list by enrollment'
    ,p_methods            => 'GET'
    ,p_remove_root_ele    => true
    ,p_path               => 'by-enrollment/{enrollmentId}'
    ,p_sql                =>    
      q'[
       SELECT 
         XMLElement
          ("data",
           XMLAttributes
             ('true' as "json:force-array",
              'http://json.org/' as "xmlns:json"),
           XMLAgg
             (XMLElement
               ("item",
                 XMLForest
                   ('/view-goal/'||xg.goal_id  "href",
                     'modal'  "target",
                     xg.goal_id "id",
                     'goal' "type",
                     xg.goal_type_meaning,
                     xg.category_meaning,
                     xg.creation_date,
                     xg.last_update_date,
                     CASE WHEN xg.goal_state = 'A' THEN xg.goal
                          ELSE xxas_perf_util_pkg.get_last_approved_goal(p_goal_id     => xg.goal_id) 
                     END "title",
                     "statusUpdateAllowed",
                     CASE WHEN xxas_com_sec_pkg.test_instance('XXAS_DT_GOAL_W_UPD', xg.goal_id) = 'Y' THEN
	                          'true'
                        ELSE
                            'false'
                        END "weightingUpdateAllowed",
                     CASE WHEN xxas_com_sec_pkg.test_instance('XXAS_DT_GOAL_C_UPD', xg.goal_id) = 'Y' THEN
                             'true'
                        ELSE
                            'false'
                        END "completionUpdateAllowed",
                     CASE WHEN xg.goal_state = 'A' THEN xg.goal_type_meaning 
                                                         || DECODE(xg.category_meaning,null,null,' - ')
                                                         || xg.category_meaning
                          ELSE xxas_util_pkg.decode_lookup ('XXAS_GOAL_TYPE'
                                                           ,xxas_perf_util_pkg.get_last_approved_goal_info
                                                             (p_goal_id => xg.goal_id
                                                             ,p_column_name => 'GOAL_TYPE_CODE')) 
                              || DECODE(xxas_perf_util_pkg.get_last_approved_goal_info(p_goal_id => xg.goal_id
                                                                             ,p_column_name => 'CATEGORY_CODE') ,null,null,' - ')
                              ||xxas_util_pkg.decode_lookup ('XXAS_GOAL_CATEGORY'
                                                             ,xxas_perf_util_pkg.get_last_approved_goal_info
                                                               (p_goal_id => xg.goal_id
                                                               ,p_column_name => 'CATEGORY_CODE'))
                     END "label"),
                 XMLElement("status",
                     XMLForest(xg.status_code    AS "id",
                               xg.status_meaning AS "label")) "status",
                 CASE WHEN xg.goal_state = 'A' AND xg.weighting_percent IS NOT NULL 
                      THEN XMLElement("weighting",
                             XMLForest(xxas_com_msg_pkg.get_string('XXAS_GOAL_WEIGHTING')           AS "label",
                                       xg.weighting_percent AS "value"))
                      WHEN xg.goal_state != 'A' AND xxas_perf_util_pkg.get_last_approved_goal_info(p_goal_id => xg.goal_id
                                                                                             ,p_column_name => 'WEIGHTING_PERCENT') IS NOT NULL  
                                     THEN XMLElement("weighting",
                                             XMLForest(xxas_com_msg_pkg.get_string('XXAS_GOAL_WEIGHTING')          AS "label",
                                                       xxas_perf_util_pkg.get_last_approved_goal_info(p_goal_id => xg.goal_id
                                                                                             ,p_column_name => 'WEIGHTING_PERCENT') AS "value"))                                                                              
                 END "weighting",
                 CASE WHEN xg.goal_state = 'A' AND xg.complete_percent IS NOT NULL 
                      THEN XMLElement("completion",
                             XMLForest(xxas_com_msg_pkg.get_string('XXAS_GOAL_COMPLETION')          AS "label",
                                       xg.complete_percent AS "value"))
                      WHEN xg.goal_state != 'A' AND xxas_perf_util_pkg.get_last_approved_goal_info(p_goal_id => xg.goal_id
                                                                                             ,p_column_name => 'COMPLETE_PERCENT') IS NOT NULL  
                                     THEN XMLElement("completion",
                                             XMLForest(xxas_com_msg_pkg.get_string('XXAS_GOAL_COMPLETION')          AS "label",
                                                       xxas_perf_util_pkg.get_last_approved_goal_info(p_goal_id => xg.goal_id
                                                                                             ,p_column_name => 'COMPLETE_PERCENT') AS "value"))                                                                              
                 END "completion",
                 XMLElement("icons",
                   XMLAttributes('true' AS "json:force-array",
                                 'http://json.org/' AS "xmlns:json"),
                     XMLElement("icons",
                        CASE WHEN xg.parent_goal_id IS NOT NULL 
                             THEN XMLForest('alignment' AS "label",
                                            'Aligned goal' AS "name") 
                        END )) "icons",
                 XMLElement("links",
                   XMLElement("self",
                     XMLForest(xxas_com_rest_util_pkg.uri_from_path
                                ('goals/'||xg.goal_id) AS "href"))),
                 XMLElement
                   ("linked",
                    CASE WHEN INSTR(lower(NVL({requestParam.includeComments},'none')),
                                               'none') > 0 THEN 
                             xxas_goals_pkg.goal_comment_actions
                               (p_goal_id => xg.goal_id).get_xml() -- actions
                    END,
                   (SELECT XMLElement
                            ("statuses",
                             XMLAttributes('true' AS "json:force-array",
                                           'http://json.org/' AS "xmlns:json"),
                             XMLAgg
                              (XMLElement
                                 ("statuses",
                                   XMLForest(lookup_code                 AS "id",
                                             meaning                     AS "label",
                                             xxas_perf_util_pkg.get_icon_color(lookup_code) AS "color")
                                 )
                               )
                             )
                     FROM hr_lookups hrl
                    WHERE lower(NVL({requestParam.goalStatus},'view')) = 'view'
                      AND hrl.lookup_type = 'XXAS_GOAL_STATUS'
                      AND hrl.enabled_flag = 'Y'
                      AND trunc(sysdate)
                         BETWEEN nvl(hrl.start_date_active,trunc(sysdate))
                          AND nvl(hrl.end_date_active,trunc(sysdate))
                   ),
                   XMLElement
                     ("comments",
                      XMLAttributes
                        ('true' AS "json:force-array",'http://json.org/' AS "xmlns:json" ),
                           XMLConcat
                             ( 
                               (SELECT XMLElement -- Empty Item for New comment Begin
                                  ("item", 
                                    XMLForest
                                      ( 0 AS "id",
                                        xg.goal_id AS "parentId",
                                        0 AS "reviewStepId",
                                        'note' AS "type",
                                        null   AS "body",
                                       'false' AS "required",
                                       'true' AS "editMode",
                                       lower(NVL({requestParam.richTextComment},'false'))  AS "htmlEditor",
                                       NVL({requestParam.commentMaxLength},3900)   AS "maxlength",
                                       'true' AS "disableAutoSave",
                                       xxas_com_msg_pkg.get_string('XXAS_PERF_PLACEHOLDER') AS "placeholder",
                                       xxas_com_msg_pkg.get_string('XXAS_RB_COM_SAVE') AS "submitButtonText",
                                       xxas_com_msg_pkg.get_string('XXAS_RB_COM_CAN')  AS "cancelButtonText",
                                        nvl(xxas_util_pkg.get_formatted_name
                                              (papf.full_name
                                              ,papf.global_name
                                              ,papf.local_name
                                              ,papf.first_name
                                              ,papf.last_name)
                                              ,papf.last_name) "author"
                                       ),
                                    XMLElement
                                      ("linked",
                                       XMLElement
                                         ("image",
                                            XMLElement
                                                ("self",
                                                  xxas_com_rest_util_pkg.uri_from_path
                                                    ('people/' || 
                                                      nvl(fnd_global.employee_id, -1) || 
                                                      '/photo')
                                                ),
                                                CASE WHEN fnd_global.employee_id IS NOT NULL THEN
                                                  XMLForest
                                                    (xxas_per_image_pkg.url(fnd_global.employee_id,
                                                                            t.last_update_date,
                                                                            'SMALL') "small"
                                                    ,xxas_per_image_pkg.url(fnd_global.employee_id,
                                                                            t.last_update_date, 
                                                                            'MEDIUM') "medium"
                                                    ,xxas_perf_util_pkg.get_initials(fnd_global.employee_id,sysdate) "initials"
                                                    )
                                                END
                                          ) 
                                      ),
                                    CASE WHEN xxas_com_sec_pkg.test_instance('XXAS_DT_GOAL_REV_RATING',
                                                                             xg.goal_id) = 'Y' 
                                         AND xxas_perf_util_pkg.hide_details({requestParam.hideRForTypes},xg.goal_type_code) = 'N'
                                         AND xxas_perf_util_pkg.hide_details({requestParam.hideRForCat},xg.category_code) = 'N' THEN
                                      XMLElement
                                        ( "rating",
                                          (SELECT XMLElement
                                                    ("iconSteps",
                                                     XMLAttributes('true' AS "json:force-array",
                                                                   'http://json.org/' AS "xmlns:json" ),
                                                     XMLAgg
                                                          (XMLElement
                                                            ("statuses",
                                                             XMLForest
                                                               (lookup_code   AS "id",
                                                                meaning       AS "label",
                                                                nvl(xxas_perf_util_pkg.get_rating_icon_color(rslt.perf_plan_enrollment_id),'energized') AS "color"
                                                                )
                                                             )
                                                           )
                                                    )
                                             FROM (SELECT * 
                                                     FROM hr_lookups hrl
                                                    WHERE hrl.lookup_type = xxas_perf_util_pkg.get_goal_rating_lookup({pathParam.enrollmentId})
                                                      AND hrl.enabled_flag = 'Y'
                                                      AND trunc(sysdate)
                                                               BETWEEN nvl(hrl.start_date_active,trunc(sysdate))
                                                                   AND nvl(hrl.end_date_active,trunc(sysdate)) 
                                                    ORDER BY lookup_code )
                                          ),
                                          XMLForest
                                            (CASE WHEN xxas_perf_util_pkg.get_rating_icon_name(rslt.perf_plan_enrollment_id) IS NULL 
                                                       AND xxas_perf_util_pkg.get_rating_icon_color(rslt.perf_plan_enrollment_id) IS NULL 
                                                 THEN 'true' 
                                                 ELSE 'false'
                                                 END AS "showAsSelectSingle",
                                                 'false' AS "required",
                                             nvl(xxas_perf_util_pkg.get_rating_icon_name(rslt.perf_plan_enrollment_id),'starfilled') AS "icon")
                                        )  
                                    END,
                                    XMLElement
                                      ( "links",
                                        XMLElement
                                          ( "comments",
                                             XMLForest
                                               (xxas_com_rest_util_pkg.uri_from_path
                                                 ('goals/goal-review-create/' ||
                                                   xg.goal_id )AS "href")
                                          )
                                      )                        
                                  ) FROM dual, 
                                         xxas_per_thumbnails t,
                                         per_all_people_f papf
                                   WHERE papf.person_id = t.person_id (+)
                                     AND fnd_global.employee_id = papf.person_id
                                     AND TRUNC(sysdate)
                                           BETWEEN papf.effective_start_date
                                               AND papf.effective_end_date
                                     AND xxas_com_sec_pkg.test_instance('XXAS_DT_GOAL_REV_CRE',xg.goal_id) = 'Y'
                                     AND xxas_perf_util_pkg.hide_details({requestParam.hideCForTypes},xg.goal_type_code) = 'N'
                                     AND xxas_perf_util_pkg.hide_details({requestParam.hideCForCat},xg.category_code) = 'N'
                                     AND INSTR(lower
                                              (NVL({requestParam.includeComments},'none')),
                                               'none') = 0
                               ),-- Empty Item for New comment End
                               ( SELECT 
                                   XMLAgg
                                     ( XMLElement
                                       ( "item",
                                         XMLForest
                                           ( xgrv.goal_review_id  AS "id",
                                             xgrv.goal_id         AS "parentId",
                                             0                    AS "reviewStepId",
                                             'note'               AS "type",
                                             'false'              AS "required",                                               
                                             xgrv.comments        AS "body",
                                             decode({requestParam.autoSave},'Y','true','false') AS "disableAutoSave",
                                             lower(NVL({requestParam.richTextComment},'false'))  AS "htmlEditor",
                                             NVL({requestParam.commentMaxLength},3900)   AS "maxlength",
                                             xxas_com_msg_pkg.get_string('XXAS_PERF_PLACEHOLDER') AS "placeholder",
                                             xxas_com_msg_pkg.get_string('XXAS_RB_COM_SAVE') AS "submitButtonText",
                                             xxas_com_msg_pkg.get_string('XXAS_RB_COM_CAN')  AS "cancelButtonText",
                                             xxas_util_pkg.days_ago(xgrv.last_update_date) AS "dateCreatedFormatted",
                                             CASE WHEN xgrv.perf_review_step_id IS NOT NULL THEN
                                                    xxas_perf_util_pkg.get_form_step_name(xgrv.perf_review_step_id)
                                                 END
                                              AS "noteType",
                                             nvl(xxas_util_pkg.get_formatted_name
                                               (papf.full_name
                                               ,papf.global_name
                                               ,papf.local_name
                                               ,papf.first_name
                                               ,papf.last_name)
                                               ,papf.last_name) "author"
                                           ),
                                         XMLElement
                                           ("linked",
                                             XMLElement
                                               ("image",
                                                 XMLElement
                                                   ("self",
                                                     xxas_com_rest_util_pkg.uri_from_path
                                                     ('people/' 
                                                       || nvl(xgrv.reviewer_person_id, -1) 
                                                       || '/photo')
                                                   ),
                                                 CASE WHEN xgrv.reviewer_person_id IS NOT NULL THEN
                                                    XMLForest
                                                     (xxas_per_image_pkg.url(xgrv.reviewer_person_id,
                                                                             t.last_update_date,
                                                                             'SMALL') "small"
                                                     ,xxas_per_image_pkg.url(xgrv.reviewer_person_id, 
                                                                             t.last_update_date, 
                                                                             'MEDIUM') "medium"
                                                     ,xxas_perf_util_pkg.get_initials(xgrv.reviewer_person_id,sysdate) "initials"
                                                     )
                                                 END
                                               ),
                                             xxas_goal_review_pkg.goal_comment_actions(p_goal_id => xgrv.goal_id -- actions
                                                                                     ,p_goal_review_id => xgrv.goal_review_id).get_xml() 
                                           ),
                                         CASE WHEN xxas_com_sec_pkg.test_instance('XXAS_DT_GOAL_REV_RATING'
                                                                                ,xg.goal_id) = 'Y' 
                                              AND xxas_perf_util_pkg.hide_details({requestParam.hideRForTypes},xg.goal_type_code) = 'N'
                                              AND xxas_perf_util_pkg.hide_details({requestParam.hideRForCat},xg.category_code) = 'N' THEN
                                           XMLElement
                                             ("rating",
                                               (SELECT XMLElement
                                                         ("iconSteps",
                                                          XMLAttributes('true' AS "json:force-array",
                                                                        'http://json.org/' AS "xmlns:json" ),
                                                          XMLAgg
                                                            (XMLElement
                                                              ("statuses",
                                                               XMLForest
                                                                 (lookup_code      AS "id"
                                                                  ,meaning         AS "label"
                                                                  ,nvl(xxas_perf_util_pkg.get_rating_icon_color(rslt.perf_plan_enrollment_id),'energized') AS "color"
                                                                 )
                                                              )
                                                            )
                                                         )
                                                   FROM (SELECT * 
                                                           FROM hr_lookups hrl
                                                          WHERE hrl.lookup_type = xxas_perf_util_pkg.get_goal_rating_lookup({pathParam.enrollmentId})
                                                            AND hrl.enabled_flag = 'Y'
                                                            AND trunc(sysdate )
                                                                  BETWEEN nvl(hrl.start_date_active,trunc(sysdate))
                                                                      AND nvl(hrl.end_date_active,trunc(sysdate)) 
                                                         ORDER BY lookup_code
                                                        )
                                               ),
                                               XMLForest
                                                   (CASE WHEN xxas_perf_util_pkg.get_rating_icon_name(rslt.perf_plan_enrollment_id) IS NULL 
                                                               AND xxas_perf_util_pkg.get_rating_icon_color(rslt.perf_plan_enrollment_id) IS NULL 
                                                         THEN 'true' 
                                                         ELSE 'false'
                                                         END AS "showAsSelectSingle",
                                                    nvl(xxas_perf_util_pkg.get_rating_icon_name(rslt.perf_plan_enrollment_id),'starfilled') AS "icon",
                                                    xgrv.rating AS "value",
                                                    'false' AS "required"
                                                    )
                                             ) 
                                         END,                                                    
                                         XMLElement
                                           ("links",
                                             XMLElement
                                               ("comments",
                                                 XMLForest
                                                   (xxas_com_rest_util_pkg.uri_from_path
                                                     ('goals/goal-review-update/' ||xgrv.goal_review_id) AS "href")
                                               )
                                           ) "links"
                                       )ORDER BY xgrv.last_update_date desc
                                     )
                                 FROM xxas_goal_reviews_v xgrv,
                                      xxas_per_thumbnails t,
                                      per_all_people_f papf
                                WHERE xgrv.goal_id            = xg.goal_id
                                  AND papf.person_id          = t.person_id (+)
                                  AND xgrv.reviewer_person_id = papf.person_id
                                  AND trunc(sysdate)
                                        BETWEEN papf.effective_start_date
                                        AND papf.effective_end_date
                                  AND xxas_com_sec_pkg.test_instance('XXAS_DT_GOAL_REV_VIEW',xgrv.goal_review_id) = 'Y'
                                  AND xxas_perf_util_pkg.hide_details({requestParam.hideCForTypes},xg.goal_type_code) = 'N'
                                  AND xxas_perf_util_pkg.hide_details({requestParam.hideCForCat},xg.category_code) = 'N'
                                  AND INSTR(lower
                                              (NVL({requestParam.includeComments},'none')),
                                               'none') = 0 
                                  AND ( 
                                        (INSTR(lower
                                              (NVL({requestParam.includeComments},'none')),
                                               'adhoc') > 0
                                         AND
                                         xgrv.perf_review_step_id IS NULL
                                        )
                                          OR
                                        ( 
                                          xgrv.perf_review_step_id IS NOT NULL
                                          AND
                                          INSTR(lower
                                                  (NVL({requestParam.includeComments},'none')),
                                                        'inreviews') > 0
                                        )
                                      )
                             )
                        )
                     )
                  ) -- Linked
                )
             ) -- XMLAgg
           ) "data" 
         FROM xxas_perf_plan_enrollments_v rslt,
              xxas_goals_v xg
        WHERE rslt.perf_plan_enrollment_id = xg.perf_plan_enrollment_id
          AND rslt.perf_plan_enrollment_id = {pathParam.enrollmentId}
          AND xxas_perf_util_pkg.was_goal_visible(xg.goal_id) = 'Y'
          AND ({requestParam.typeCode} IS NULL OR 
               xg.goal_type_code = nvl({requestParam.typeCode},xg.goal_type_code))
          AND ({requestParam.categoryCode} IS NULL OR 
               xg.category_code = nvl({requestParam.categoryCode},xg.category_code))
          AND {authFunctionPredicate.XXAS_DT_PERF_ENROLLMENT}
     ]'
    );     
    
   xxas_com_sr_pkg.set_rest_method
    (p_service_name       => 'Goals'
    ,p_method_name        => 'Goal Details'
    ,p_description        => 'Finds details of a goal'
    ,p_methods            => 'GET'
    ,p_remove_root_ele    => true
    ,p_path               => 'goalDetails/{functionName}/{goalId}'
    ,p_sql                =>
      q'[   SELECT XMLElement("data"
                 ,XMLAttributes
                   ('true' as "json:force-array"
                   ,'http://json.org/' as "xmlns:json")
                   , xxas_com_sec_pkg.get_obj_data_w_binds
          ({pathParam.functionName}
          ,xxas_com_varchar2_table('GOAL_ID = :GOAL_ID')
          ,xxas_com_srv_param_varray
            (xxas_com_srv_param_value('GOAL_ID', {pathParam.goalId}))
          , null -- p_rowset_tag_name 
          ,'item' -- p_row_tag_name 
          ,{requestParam.pageNumber}
          ,{requestParam.perPage})) "data"
      FROM dual
      ]'
    );      
       
xxas_com_sr_pkg.set_rest_method
    (p_service_name       => l_SERVICE
    ,p_method_name        => 'GetGoalDetail'
    ,p_description        => 'Gets goals-detail'
    ,p_methods            => 'GET'
    ,p_remove_root_ele    => true
    ,p_path               => 'goal-detail/{goalId}'
    ,p_sql                =>    
      q'[
       SELECT  
         XMLElement
           ("data",
            XMLElement
              ("item",
               XMLForest
                 (rslt.goal_id "id",
                  'goal' "type",
                  rslt.goal_type_meaning,
                  rslt.category_meaning,
                  rslt.creation_date,
                  rslt.last_update_date,
                  CASE WHEN rslt.goal_state = 'A' THEN rslt.goal
                       ELSE xxas_perf_util_pkg.get_last_approved_goal(p_goal_id     => rslt.goal_id) 
                  END "title",
                  "statusUpdateAllowed",
                  CASE WHEN xxas_com_sec_pkg.test_instance('XXAS_DT_GOAL_W_UPD', rslt.goal_id) = 'Y' THEN
	                          'true'
                  ELSE
                      'false'
                  END "weightingUpdateAllowed",
                  CASE WHEN xxas_com_sec_pkg.test_instance('XXAS_DT_GOAL_C_UPD', rslt.goal_id) = 'Y' THEN
                       'true'
                  ELSE
                      'false'
                  END "completionUpdateAllowed",
                  CASE WHEN rslt.goal_state = 'A' THEN rslt.goal_type_meaning 
                                                      || DECODE(rslt.category_meaning,null,null,' - ')
                                                      || rslt.category_meaning
                       ELSE xxas_util_pkg.decode_lookup ('XXAS_GOAL_TYPE'
                                                        ,xxas_perf_util_pkg.get_last_approved_goal_info
                                                          (p_goal_id => rslt.goal_id
                                                          ,p_column_name => 'GOAL_TYPE_CODE')) 
                           || DECODE(xxas_perf_util_pkg.get_last_approved_goal_info(p_goal_id => rslt.goal_id
                                                                          ,p_column_name => 'CATEGORY_CODE') ,null,null,' - ')
                           ||xxas_util_pkg.decode_lookup ('XXAS_GOAL_CATEGORY'
                                                          ,xxas_perf_util_pkg.get_last_approved_goal_info
                                                            (p_goal_id => rslt.goal_id
                                                            ,p_column_name => 'CATEGORY_CODE'))
                  END "label" 
                 ),
               XMLElement
                 ("status",
                  XMLForest
                    (rslt.status_code    AS "id",
                     rslt.status_meaning AS "label")
                  ) "status",
               CASE WHEN rslt.goal_state = 'A' AND rslt.weighting_percent IS NOT NULL 
                    THEN XMLElement("weighting",
                          XMLForest( xxas_com_msg_pkg.get_string('XXAS_GOAL_WEIGHTING')         AS "label",
                                    rslt.weighting_percent AS "value"))
                    WHEN rslt.goal_state != 'A' AND xxas_perf_util_pkg.get_last_approved_goal_info(p_goal_id => rslt.goal_id
                                                                                          ,p_column_name => 'WEIGHTING_PERCENT') IS NOT NULL  
                    THEN XMLElement("weighting",
                                          XMLForest( xxas_com_msg_pkg.get_string('XXAS_GOAL_WEIGHTING')         AS "label",
                                                    xxas_perf_util_pkg.get_last_approved_goal_info(p_goal_id => rslt.goal_id
                                                                                          ,p_column_name => 'WEIGHTING_PERCENT') AS "value"))                                                                              
               END "weighting",
               CASE WHEN rslt.goal_state = 'A' AND rslt.complete_percent IS NOT NULL 
                      THEN XMLElement("completion",
                             XMLForest(xxas_com_msg_pkg.get_string('XXAS_GOAL_COMPLETION')          AS "label",
                                       rslt.complete_percent AS "value"))
                      WHEN rslt.goal_state != 'A' AND xxas_perf_util_pkg.get_last_approved_goal_info(p_goal_id => rslt.goal_id
                                                                                             ,p_column_name => 'COMPLETE_PERCENT') IS NOT NULL  
                                     THEN XMLElement("completion",
                                             XMLForest(xxas_com_msg_pkg.get_string('XXAS_GOAL_COMPLETION')          AS "label",
                                                       xxas_perf_util_pkg.get_last_approved_goal_info(p_goal_id => rslt.goal_id
                                                                                             ,p_column_name => 'COMPLETE_PERCENT') AS "value"))                                                                              
                 END "completion",
               XMLElement("icons",
                 XMLAttributes('true' AS "json:force-array",
                               'http://json.org/' AS "xmlns:json"),
                   XMLElement("icons",
                     CASE WHEN rslt.parent_goal_id IS NOT NULL 
                          THEN XMLForest('alignment' AS "label",
                                         'Aligned goal' AS "name") 
                     END )) "icons",
               XMLElement
                 ("links",
                 XMLElement
                   ("self",
                    XMLForest
                      (xxas_com_rest_util_pkg.uri_from_path('goals/'||rslt.goal_id||'?refresh=Y') AS "href"))),
                 XMLElement
                   ("linked",
                    xxas_goals_pkg.goal_comment_actions
                      (p_goal_id => rslt.goal_id).get_xml(), -- actions
                   (SELECT XMLElement
                            ("statuses",
                             XMLAttributes('true' AS "json:force-array",
                                           'http://json.org/' AS "xmlns:json"),
                             XMLAgg
                              (XMLElement
                                 ("statuses",
                                   XMLForest(lookup_code                 AS "id",
                                             meaning                     AS "label",
                                             xxas_perf_util_pkg.get_icon_color(lookup_code) AS "color")
                                 )
                               )
                             )
                     FROM hr_lookups hrl
                    WHERE lower(NVL({requestParam.goalStatus},'view')) = 'view'
                      AND hrl.lookup_type = 'XXAS_GOAL_STATUS'
                      AND hrl.enabled_flag = 'Y'
                      AND trunc(sysdate)
                         BETWEEN nvl(hrl.start_date_active,trunc(sysdate))
                          AND nvl(hrl.end_date_active,trunc(sysdate))
                   ),
                   XMLElement
                     ("comments",
                      XMLAttributes
                        ('true' AS "json:force-array",'http://json.org/' AS "xmlns:json" ),
                           XMLConcat
                             ( 
                               (SELECT XMLElement -- Empty Item for New comment Begin
                                  ("item", 
                                    XMLForest
                                      ( 0 AS "id",
                                        rslt.goal_id AS "parentId",
                                        0 AS "reviewStepId",
                                        'note' AS "type",
                                        null   AS "body",
                                       'false' AS "required",
                                       'true' AS "editMode",
                                       'true' AS "disableAutoSave",
                                       lower(NVL({requestParam.richTextComment},'false'))  AS "htmlEditor",
                                       NVL({requestParam.commentMaxLength},3900)   AS "maxlength",
                                       xxas_com_msg_pkg.get_string('XXAS_PERF_PLACEHOLDER') AS "placeholder",
                                       xxas_com_msg_pkg.get_string('XXAS_RB_COM_SAVE') AS "submitButtonText",
                                       xxas_com_msg_pkg.get_string('XXAS_RB_COM_CAN')  AS "cancelButtonText",
                                        nvl(xxas_util_pkg.get_formatted_name
                                              (papf.full_name
                                              ,papf.global_name
                                              ,papf.local_name
                                              ,papf.first_name
                                              ,papf.last_name)
                                              ,papf.last_name) "author"
                                       ),
                                    XMLElement
                                      ("linked",
                                       XMLElement
                                         ("image",
                                            XMLElement
                                                ("self",
                                                  xxas_com_rest_util_pkg.uri_from_path
                                                    ('people/' || 
                                                      nvl(fnd_global.employee_id, -1) || 
                                                      '/photo')
                                                ),
                                                CASE WHEN fnd_global.employee_id IS NOT NULL THEN
                                                  XMLForest
                                                    (xxas_per_image_pkg.url(fnd_global.employee_id,
                                                                            t.last_update_date,
                                                                            'SMALL') "small"
                                                    ,xxas_per_image_pkg.url(fnd_global.employee_id,
                                                                            t.last_update_date, 
                                                                            'MEDIUM') "medium"
                                                    ,xxas_perf_util_pkg.get_initials(fnd_global.employee_id,sysdate) "initials"
                                                    )
                                                END
                                          ) 
                                      ),
                                    CASE WHEN xxas_com_sec_pkg.test_instance('XXAS_DT_GOAL_REV_RATING',
                                                                             rslt.goal_id) = 'Y' THEN
                                      XMLElement
                                        ( "rating",
                                          (SELECT XMLElement
                                                    ("iconSteps",
                                                     XMLAttributes('true' AS "json:force-array",
                                                                   'http://json.org/' AS "xmlns:json" ),
                                                     XMLAgg
                                                          (XMLElement
                                                            ("statuses",
                                                             XMLForest
                                                               (lookup_code   AS "id",
                                                                meaning       AS "label",
                                                                nvl(xxas_perf_util_pkg.get_rating_icon_color(rslt.perf_plan_enrollment_id),'energized') AS "color"
                                                                )
                                                             )  ORDER BY lookup_code 
                                                           )
                                                    )
                                             FROM hr_lookups hrl
                                                    WHERE hrl.lookup_type = xxas_perf_util_pkg.get_goal_rating_lookup(rslt.perf_plan_enrollment_id)
                                                      AND hrl.enabled_flag = 'Y'
                                                      AND trunc(sysdate)
                                                               BETWEEN nvl(hrl.start_date_active,trunc(sysdate))
                                                                   AND nvl(hrl.end_date_active,trunc(sysdate)) 
                                          ),
                                          XMLForest
                                            (CASE WHEN xxas_perf_util_pkg.get_rating_icon_name(rslt.perf_plan_enrollment_id) IS NULL 
                                                       AND xxas_perf_util_pkg.get_rating_icon_color(rslt.perf_plan_enrollment_id) IS NULL 
                                                 THEN 'true' 
                                                 ELSE 'false'
                                                 END AS "showAsSelectSingle",
                                                 'false' AS "required",
                                             nvl(xxas_perf_util_pkg.get_rating_icon_name(rslt.perf_plan_enrollment_id),'starfilled') AS "icon")
                                        )  
                                    END,
                                    XMLElement
                                      ( "links",
                                        XMLElement
                                          ( "comments",
                                             XMLForest
                                               (xxas_com_rest_util_pkg.uri_from_path
                                                 ('goals/goal-review-create/' ||
                                                   rslt.goal_id )AS "href")
                                          )
                                      )                        
                                  ) FROM dual, 
                                         xxas_per_thumbnails t,
                                         per_all_people_f papf
                                   WHERE papf.person_id = t.person_id (+)
                                     AND fnd_global.employee_id = papf.person_id
                                     AND TRUNC(sysdate)
                                           BETWEEN papf.effective_start_date
                                               AND papf.effective_end_date
                                     AND xxas_com_sec_pkg.test_instance('XXAS_DT_GOAL_REV_CRE',rslt.goal_id) = 'Y'
                               ),-- Empty Item for New comment End
                               ( SELECT 
                                   XMLAgg
                                     ( XMLElement
                                       ( "item",
                                         XMLForest
                                           ( xgrv.goal_review_id  AS "id",
                                             xgrv.goal_id         AS "parentId",
                                             0                    AS "reviewStepId",
                                             'note'               AS "type",
                                             'false'              AS "required",                                               
                                             xgrv.comments        AS "body",
                                             'false'               AS "disableAutoSave",
                                             lower(NVL({requestParam.richTextComment},'false'))  AS "htmlEditor",
                                             NVL({requestParam.commentMaxLength},3900)   AS "maxlength",
                                             xxas_com_msg_pkg.get_string('XXAS_PERF_PLACEHOLDER') AS "placeholder",
                                             xxas_com_msg_pkg.get_string('XXAS_RB_COM_SAVE') AS "submitButtonText",
                                             xxas_com_msg_pkg.get_string('XXAS_RB_COM_CAN')  AS "cancelButtonText",
                                              xxas_util_pkg.days_ago(xgrv.last_update_date) AS "dateCreatedFormatted",
                                             CASE WHEN xgrv.perf_review_step_id IS NOT NULL THEN
                                                    xxas_perf_util_pkg.get_form_step_name(xgrv.perf_review_step_id)
                                                 END
                                              AS "noteType",
                                             nvl(xxas_util_pkg.get_formatted_name
                                               (papf.full_name
                                               ,papf.global_name
                                               ,papf.local_name
                                               ,papf.first_name
                                               ,papf.last_name)
                                               ,papf.last_name) "author"
                                           ),
                                         XMLElement
                                           ("linked",
                                             XMLElement
                                               ("image",
                                                 XMLElement
                                                   ("self",
                                                     xxas_com_rest_util_pkg.uri_from_path
                                                     ('people/' 
                                                       || nvl(xgrv.reviewer_person_id, -1) 
                                                       || '/photo')
                                                   ),
                                                 CASE WHEN xgrv.reviewer_person_id IS NOT NULL THEN
                                                    XMLForest
                                                     (xxas_per_image_pkg.url(xgrv.reviewer_person_id,
                                                                             t.last_update_date,
                                                                             'SMALL') "small"
                                                     ,xxas_per_image_pkg.url(xgrv.reviewer_person_id, 
                                                                             t.last_update_date, 
                                                                             'MEDIUM') "medium"
                                                     ,xxas_perf_util_pkg.get_initials(xgrv.reviewer_person_id,sysdate) "initials"
                                                     )
                                                 END
                                               ),
                                             xxas_goal_review_pkg.goal_comment_actions(p_goal_id => xgrv.goal_id -- actions
                                                                                     ,p_goal_review_id => xgrv.goal_review_id).get_xml() 
                                           ),
                                         CASE WHEN xxas_com_sec_pkg.test_instance('XXAS_DT_GOAL_REV_RATING'
                                                                                ,rslt.goal_id) = 'Y' THEN
                                           XMLElement
                                             ("rating",
                                               (SELECT XMLElement
                                                         ("iconSteps",
                                                          XMLAttributes('true' AS "json:force-array",
                                                                        'http://json.org/' AS "xmlns:json" ),
                                                          XMLAgg
                                                            (XMLElement
                                                              ("statuses",
                                                               XMLForest
                                                                 (lookup_code      AS "id"
                                                                  ,meaning         AS "label"
                                                                  ,nvl(xxas_perf_util_pkg.get_rating_icon_color(rslt.perf_plan_enrollment_id),'energized') AS "color"
                                                                 )
                                                              ) ORDER BY lookup_code
                                                            )
                                                         )
                                                   FROM hr_lookups hrl
                                                          WHERE hrl.lookup_type = xxas_perf_util_pkg.get_goal_rating_lookup(rslt.perf_plan_enrollment_id)
                                                            AND hrl.enabled_flag = 'Y'
                                                            AND trunc(sysdate )
                                                                  BETWEEN nvl(hrl.start_date_active,trunc(sysdate))
                                                                      AND nvl(hrl.end_date_active,trunc(sysdate)) 
                                               ),
                                               XMLForest
                                                   (CASE WHEN xxas_perf_util_pkg.get_rating_icon_name(rslt.perf_plan_enrollment_id) IS NULL 
                                                               AND xxas_perf_util_pkg.get_rating_icon_color(rslt.perf_plan_enrollment_id) IS NULL 
                                                         THEN 'true' 
                                                         ELSE 'false'
                                                         END AS "showAsSelectSingle",
                                                    nvl(xxas_perf_util_pkg.get_rating_icon_name(rslt.perf_plan_enrollment_id),'starfilled') AS "icon",
                                                    xgrv.rating AS "value",
                                                    'false' AS "required"
                                                    )
                                             ) 
                                         END,                                                    
                                         XMLElement
                                           ("links",
                                             XMLElement
                                               ("comments",
                                                 XMLForest
                                                   (xxas_com_rest_util_pkg.uri_from_path
                                                     ('goals/goal-review-update/' ||xgrv.goal_review_id) AS "href")
                                               )
                                           ) "links"
                                       )ORDER BY xgrv.last_update_date desc
                                     )
                                 FROM xxas_goal_reviews_v xgrv,
                                      xxas_per_thumbnails t,
                                      per_all_people_f papf
                                WHERE xgrv.goal_id            = rslt.goal_id
                                  AND papf.person_id          = t.person_id (+)
                                  AND xgrv.reviewer_person_id = papf.person_id
                                  AND trunc(sysdate)
                                        BETWEEN papf.effective_start_date
                                        AND papf.effective_end_date
                                  AND xxas_com_sec_pkg.test_instance('XXAS_DT_GOAL_REV_VIEW',xgrv.goal_review_id) = 'Y'
                             )
                        )
                     )
                  ) -- Linked
                  ) -- Item
                ) "data" 
         FROM xxas_goals_v rslt
        WHERE rslt.goal_id = {pathParam.goalId}
          AND {authFunctionPredicate.XXAS_DT_PERF_GOALS}
     ]'
    ); 
       
  xxas_com_sr_pkg.set_rest_method
    (p_service_name       => l_SERVICE
    ,p_method_name        => 'DeleteGoal'
    ,p_description        => 'Deletes a goal'
    ,p_methods            => 'DELETE'
    ,p_path               => '{goalId}'
    ,p_plsql              =>
      q'[
        DECLARE
        
          l_xml         XMLType;
          l_resp        xxas_http_resp_obj;
          
        BEGIN
        
          IF NOT xxas_com_sec_pkg.test_instance_rtn_boolean
            ('XXAS_DT_GOAL_DEL', {pathParam.goalId}) THEN
            l_resp := xxas_http_response
                        .status(xxas_http_resp_status_pkg.unauthorised)
                        .build();
          ELSE
          xxas_goals_pkg.delete_row
            (p_goal_id => {pathParam.goalId});
              
          l_resp := xxas_http_response
                      .ok(xxas_com_msg_pkg.get_success_message_xml
                           (xxas_com_msg_type
                         (xxas_util_pkg.app, 'XXAS_COM_CHNG_SAVE', null)
                       ,'self')
                         )
                      .build();
         
         {response} := l_resp;
                  
         END IF;

        END
      ]'
    );
    
  xxas_com_sr_pkg.set_rest_method
    (p_service_name       => l_SERVICE
    ,p_method_name        => 'UpdateGoalStatus'
    ,p_description        => 'Update a goal''s status'
    ,p_methods            => 'PUT'
    ,p_path               => '{goalId}'
    ,p_plsql              =>
      q'[
        DECLARE
        
          l_xml           XMLType;
          l_resp          xxas_http_resp_obj;
          l_request_xml   XMLType;          
          
        BEGIN
          l_request_xml := {requestBody};
          
          IF NOT xxas_com_sec_pkg.test_instance_rtn_boolean('XXAS_DT_GOAL_UPD', {pathParam.goalId}) AND
             NOT xxas_com_sec_pkg.test_instance_rtn_boolean('XXAS_DT_GOAL_W_UPD', {pathParam.goalId}) AND
             NOT xxas_com_sec_pkg.test_instance_rtn_boolean('XXAS_DT_GOAL_C_UPD', {pathParam.goalId})
          THEN
            l_resp := xxas_http_response
                        .status(xxas_http_resp_status_pkg.unauthorised)
                        .build();
          ELSE
          xxas_goals_pkg.update_goal_status
            (p_goal_id => {pathParam.goalId}
            ,p_form_data_xml => l_request_xml);
              
          l_resp := xxas_http_response
                      .ok(xxas_com_msg_pkg.get_success_message_xml
                           (xxas_com_msg_type
                         (xxas_util_pkg.app, 'XXAS_COM_CHNG_SAVE', null)
                       , CASE WHEN nvl({requestParam.refresh},'N') = 'Y' THEN 'self'
                         ELSE null
                         END
                       ,'modal')
                         )
                      .build();
         
         {response} := l_resp;
                  
         END IF;

        END
      ]'
    );  

  xxas_com_sr_pkg.set_rest_method
    (p_service_name       => l_SERVICE
    ,p_method_name        => 'CreateGoalReview'
    ,p_description        => 'Creates a goal review'
    ,p_methods            => 'PUT'
    ,p_path               => 'goal-review-create/{goalId}'
    ,p_plsql              =>
      q'[
        DECLARE
        
          l_xml           XMLType;
          l_resp          xxas_http_resp_obj;
          l_request_xml   XMLType;          
          
        BEGIN
          l_request_xml := {requestBody};
          
          IF NOT xxas_com_sec_pkg.test_instance_rtn_boolean
            ('XXAS_DT_GOAL_REV_CRE', {pathParam.goalId}) THEN
            l_resp := xxas_http_response
                        .status(xxas_http_resp_status_pkg.unauthorised)
                        .build();
          ELSE
          xxas_goal_review_pkg.create_goal_review
            (p_goal_review_id => {pathParam.goalId}
            ,p_form_data_xml => l_request_xml);            
              
          l_resp := xxas_http_response
                      .ok(xxas_com_msg_pkg.get_success_message_xml
                           (xxas_com_msg_type
                         (xxas_util_pkg.app, 'XXAS_COM_CHNG_SAVE', null)
                       ,'self'
                       ,'modal')
                         )
                      .build();
         
         {response} := l_resp;
                  
         END IF;

        END
      ]'
    );  
    
  xxas_com_sr_pkg.set_rest_method
    (p_service_name       => l_SERVICE
    ,p_method_name        => 'UpdateGoalReview'
    ,p_description        => 'Update a goal review'
    ,p_methods            => 'PUT'
    ,p_path               => 'goal-review-update/{goalReviewId}'
    ,p_plsql              =>
      q'[
        DECLARE
        
          l_xml           XMLType;
          l_resp          xxas_http_resp_obj;
          l_request_xml   XMLType;          
          l_reload_page   VARCHAR2(10);
          
        BEGIN
          l_request_xml := {requestBody};
          
          IF NOT xxas_com_sec_pkg.test_instance_rtn_boolean
            ('XXAS_DT_GOAL_REV_UPD', {pathParam.goalReviewId}) THEN
            l_resp := xxas_http_response
                        .status(xxas_http_resp_status_pkg.unauthorised)
                        .build();
          ELSE
          xxas_goal_review_pkg.update_goal_review
            (p_goal_review_id => {pathParam.goalReviewId}
            ,p_form_data_xml  => l_request_xml
            ,p_reload_page    => l_reload_page);
              
          l_resp := xxas_http_response
                      .ok(xxas_com_msg_pkg.get_success_message_xml
                           (xxas_com_msg_type
                         (xxas_util_pkg.app, 'XXAS_COM_CHNG_SAVE', null)
                       ,'self'
                       ,'modal')
                         )
                      .build();
         
         {response} := l_resp;
                  
         END IF;

        END
      ]'
    );     

  xxas_com_sr_pkg.set_rest_method
    (p_service_name       => l_SERVICE
    ,p_method_name        => 'DeleteGoalReview'
    ,p_description        => 'Deletes a goal review'
    ,p_methods            => 'POST'
    ,p_path               => 'goal-review-delete/{goalReviewId}'
    ,p_plsql              =>
      q'[
        DECLARE
        
          l_xml           XMLType;
          l_resp          xxas_http_resp_obj;
          l_request_xml   XMLType;          
          
        BEGIN
          l_request_xml := {requestBody};
          
          IF NOT xxas_com_sec_pkg.test_instance_rtn_boolean
            ('XXAS_DT_GOAL_REV_DEL', {pathParam.goalReviewId}) THEN
            l_resp := xxas_http_response
                        .status(xxas_http_resp_status_pkg.unauthorised)
                        .build();
          ELSE
          xxas_goal_review_pkg.delete_review
            (p_goal_review_id => {pathParam.goalReviewId});
              
          l_resp := xxas_http_response
                      .ok(xxas_com_msg_pkg.get_success_message_xml
                           (xxas_com_msg_type
                         (xxas_util_pkg.app, 'XXAS_COM_CHNG_SAVE', null)
                       ,'self'
                       ,'modal')
                         )
                      .build();
         
         {response} := l_resp;
                  
         END IF;

        END
      ]'
    );    
    
  xxas_com_sr_pkg.set_rest_method
    (p_service_name       => l_SERVICE
    ,p_method_name        => 'CopyGoals'
    ,p_description        => 'Copy one or more goals'
    ,p_path               => 'copy-to/{goalId}'
    ,p_methods            => 'POST'
    ,p_plsql              =>
      q'[
        DECLARE
        
          l_xml         XMLType;
          l_resp        xxas_http_resp_obj;
          l_resp_xml    XMLType;
          l_request_xml XMLType;
		 
        BEGIN
        
         l_request_xml := {requestBody};
  			 xxas_goals_pkg.copy_goal({pathParam.goalId},l_request_xml,l_resp_xml);
         l_resp := xxas_http_response
                      .ok(l_resp_xml
                         )
                      .build();	
         {response} := l_resp;

        END
      ]'
    );

  xxas_com_sr_pkg.set_rest_method
    (p_service_name       => l_SERVICE
    ,p_method_name        => 'GetCascadeTo'
    ,p_description        => 'Get DRs to cascade goals to'
    ,p_path               => 'get-cascade-to/{personId}/{assignmentId}/{planId}'
    ,p_methods            => 'GET'
    ,p_remove_root_ele    => true
    ,p_sql              =>
      q'[

         SELECT XMLElement
                 ("data"
                 ,XMLAttributes
                   ('true' as "json:force-array"
                   ,'http://json.org/' as "xmlns:json")
                 ,XMLAgg
                   (XMLElement
                     ("participant"
                     ,XMLForest
                       (p.assignment_id as "id"
                       ,xxas_perf_util_pkg.get_formatted_name (p.person_id)||' - '||p.job_name        as "label"
                       ,p.assignment_id as "value"
                       )
                     ,XMLElement
                       ("linked"
                       ,XMLElement
                         ("image"
                         ,XMLForest
                           (xxas_com_rest_util_pkg.uri_from_path
                              ('people/' || p.person_id || '/photo') "self"
                           ,xxas_perf_util_pkg.get_initials(p.person_id,sysdate) "initials"
                           ,xxas_per_image_pkg.url
                              (p.person_id, t.last_update_date, 'SMALL') "small"
                           )
                         )
                       )
                     )                   
                   )
                 )"data"
          FROM table(xxas_mss_util_pkg.direct_reportees_data({pathParam.personId},{pathParam.assignmentId})) p,
               xxas_per_thumbnails t,
               xxas_perf_plan_enrollments xppe 
          WHERE p.person_id = t.person_id (+)
           AND xppe.perf_plan_id = {pathParam.planId} 
           AND xppe.assignment_id = p.assignment_id
      ]'
    );
  
    xxas_com_sr_pkg.set_rest_method
    (p_service_name       => l_SERVICE
    ,p_method_name        => 'GetGoalsToCascade'
    ,p_description        => 'Get Goals to cascad'
    ,p_path               => 'get-goals-to-cascade/{enrollmentId}'
    ,p_methods            => 'GET'
    ,p_remove_root_ele    => true
    ,p_sql              =>
      q'[

          SELECT XMLElement
                  ("data"
                  ,XMLAttributes
                    ('true' as "json:force-array"
                    ,'http://json.org/' as "xmlns:json")
                  ,XMLAgg
                    (XMLElement
                      ("participant"
                      ,XMLForest
                        (p.goal_id as "id"
                        ,REGEXP_REPLACE(goal, '&lt;[a-zA-Z/].*?&gt;') as "label"
                        ,p.goal_id as "value"
                        )
                      )                   
                    )
                  )"data"
            FROM xxas_goals_v p
          WHERE p.perf_plan_enrollment_id = {pathParam.enrollmentId}
            AND p.goal_status_code != 'X'
            AND p.goal_type_code = 'B'
            AND p.access_code = 'S'
      ]'
    );
    
    xxas_com_sr_pkg.set_rest_method
    (p_service_name       => l_SERVICE
    ,p_method_name        => 'GetGoalsToCopy'
    ,p_description        => 'Get Goals to copy'
    ,p_path               => 'get-goals-to-copy/{planId}/{assignmentId}'
    ,p_methods            => 'GET'
    ,p_remove_root_ele    => true
    ,p_sql              =>
      q'[

         SELECT XMLElement
                 ("data"
                 ,XMLAttributes
                   ('true' as "json:force-array"
                   ,'http://json.org/' as "xmlns:json")
                 ,XMLAgg
                   (XMLElement
                     ("participant"
                     ,XMLForest
                       (p.goal_id as "id"
                       ,goal as "label"
                       ,p.goal_id as "value"
                       )
                     )                   
                   )
                 )"data"
          FROM xxas_goals_v p
         WHERE p.perf_plan_id = {pathParam.planId}
           AND p.assignment_id = {pathParam.assignmentId}
      ]'
    );
   xxas_com_sr_pkg.set_rest_method
    (p_service_name       => l_SERVICE
    ,p_method_name        => 'GetPlansCopyFrom'
    ,p_description        => 'Get Plans to copy from'
    ,p_path               => 'get-plans-copy-from/{assignmentId}'
    ,p_methods            => 'GET'
    ,p_remove_root_ele    => true
    ,p_sql              =>
      q'[

         SELECT XMLElement
                 ("data"
                 ,XMLAttributes
                   ('true' as "json:force-array"
                   ,'http://json.org/' as "xmlns:json")
                 ,XMLAgg
                   (XMLElement
                     ("participant"
                     ,XMLForest
                       (p.perf_plan_id as "id"
                       ,p.title as "label"
                       ,p.perf_plan_id as "value"
                       )
                     )    ORDER BY p.start_date desc                 
                   )
                 )"data"
          FROM xxas_perf_plans p,
               xxas_perf_plan_enrollments e
         WHERE p.perf_plan_id = e.perf_plan_id
           AND e.assignment_id = {pathParam.assignmentId}
         
      ]'
      );
   xxas_com_sr_pkg.set_rest_method
    (p_service_name       => l_SERVICE
    ,p_method_name        => 'GetPlansToCopy'
    ,p_description        => 'Get Plans to copy'
    ,p_path               => 'get-plans-to-copy/{assignmentId}'
    ,p_methods            => 'GET'
    ,p_remove_root_ele    => true
    ,p_sql              =>
      q'[

         SELECT XMLElement
                 ("data"
                 ,XMLAttributes
                   ('true' as "json:force-array"
                   ,'http://json.org/' as "xmlns:json")
                 ,XMLAgg
                   (XMLElement
                     ("participant"
                     ,XMLForest
                       (p.perf_plan_id as "id"
                       ,p.title as "label"
                       ,p.perf_plan_id as "value"
                       )
                     )    ORDER BY p.start_date desc                 
                   )
                 )"data"
          FROM xxas_perf_plans p,
               xxas_perf_plan_enrollments e
         WHERE p.perf_plan_id = e.perf_plan_id
           AND e.assignment_id = {pathParam.assignmentId}
           AND ({requestParam.exceptPlan} IS NULL OR {requestParam.exceptPlan} != e.perf_plan_id)
           AND EXISTS
             (
              SELECT xprf.perf_review_form_id
                FROM xxas_perf_review_forms_v xprf
               WHERE xprf.perf_plan_enrollment_id = e.perf_plan_enrollment_id
                 AND xprf.person_id = e.person_id
                 AND xprf.status_code != 'A'
                 AND xprf.start_date &#60;= trunc(sysdate)
                 AND xxas_perf_util_pkg.has_curr_goal_access_flag ( xprf.perf_review_form_id,
                                                                   xprf.person_id,
                                                                   'FE' ) = 'Y'
                      )
         
      ]'
    );
END;
/
COMMIT;
EXIT;
