/* $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
                       := 'GoalsInReview';
                       
BEGIN
      
  xxas_com_sr_pkg.set_rest_service
    (p_service_name       => l_SERVICE
    ,p_description        => 'Manage goals within a performance review' 
    ,p_path               => 'goals-in-review'    
    ,p_consumes           => 'application/xml'
    ,p_produces           => 'application/xml'
    );
 
   xxas_com_sr_pkg.set_rest_method
    (p_service_name       => l_SERVICE
    ,p_method_name        => 'GetGoalsByReview'
    ,p_description        => 'Gets goals by performance review'
    ,p_methods            => 'GET'
    ,p_remove_root_ele    => true
    ,p_path               => 'by-review/{functionName}/{reviewFormId}'
    ,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_REVIEW_FORM_ID = :REVIEW_FORM_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('REVIEW_FORM_ID', {pathParam.reviewFormId})
            ,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        => 'GetGoalListByReview'
    ,p_description        => 'Gets goal-list by performance review'
    ,p_methods            => 'GET'
    ,p_remove_root_ele    => true
    ,p_path               => 'by-review/{reviewFormId}' 
    ,p_sql                =>
      q'[
       SELECT 
         XMLElement
          ("data",
           XMLAttributes
             ('true' as "json:force-array",
              'http://json.org/' as "xmlns:json"),
           XMLAgg
             (XMLElement
               ("item",
                XMLConcat
                  (dbms_xmlgen.getxmltype 
                     ('SELECT * 
                         FROM xxas_goals_review_base_v xgrb 
                        WHERE xgrb.perf_review_form_id = '
                      ||rslt.perf_review_form_id||
                      ' and xgrb.goal_id ='||
                      xg.goal_id).extract('/*/*/*'),
                   XMLForest
                    ('/view-goal-in-review/'||xg.goal_id||'_'||rslt.perf_review_form_id||'_'||{requestParam.activePersonId} AS "href",
                     'modal' AS "target" ,
                     nvl({requestParam.modalWidth},'medium') as "modalWidth" 
                     ,CASE WHEN NVL({requestParam.displayAttributes},NULL) IS NOT NULL
                     THEN
                        xxas_goal_review_pkg.get_goal_dff_attr(xg.goal_id, {requestParam.displayAttributes})
                     END  AS "line3" )
                    ,CASE WHEN rslt.status_code NOT IN ('A','O') AND xg.weighting_percent IS NOT NULL 
                      THEN XMLElement("weighting",
                             XMLForest(xxas_com_msg_pkg.get_string('XXAS_GOAL_WEIGHTING')          AS "label",
                                        rslt.current_step_id AS "reviewStepId",
                                       xg.weighting_percent AS "value"))
                      WHEN rslt.status_code IN ('A','O') 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",
                                                       rslt.current_step_id AS "reviewStepId",
                                                       xxas_perf_util_pkg.get_last_approved_goal_info(p_goal_id => xg.goal_id
                                                                                             ,p_column_name => 'WEIGHTING_PERCENT') AS "value"))                                                                              
                    END
                   ,CASE WHEN rslt.status_code NOT IN ('A','O') AND xg.complete_percent IS NOT NULL 
                      THEN XMLElement("completion",
                             XMLForest(xxas_com_msg_pkg.get_string('XXAS_GOAL_COMPLETION')         AS "label",
                                       rslt.current_step_id AS "reviewStepId",
                                       xg.complete_percent AS "value"))
                      WHEN rslt.status_code IN ('A','O') 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",
                                                       rslt.current_step_id AS "reviewStepId",
                                                       xxas_perf_util_pkg.get_last_approved_goal_info(p_goal_id => xg.goal_id
                                                                                             ,p_column_name => 'COMPLETE_PERCENT') AS "value"))                                                                              
                    END,
                   XMLElement("links",
                     XMLElement("self",
                       XMLForest(xxas_com_rest_util_pkg.uri_from_path('goals-in-review/'||xg.goal_id|| '/' ||rslt.perf_review_form_id) AS "href"))),
                  XMLElement("attachmentTrigger", 
                     XMLElement("links", XMLForest(xxas_com_rest_util_pkg.uri_from_path('documents?fileName={fileName}' || CHR(38) || 'fileMimeType={fileMimeType}') AS "href"))),
                  CASE 
                    WHEN LOWER(NVL({requestParam.enableAttachments}, '-1')) = 'y' 
                    THEN XMLElement("isEnableAttachments", 'true') 
                  END,
                   XMLElement
                     ("linked",
                      CASE WHEN INSTR(lower(NVL({requestParam.includeComments},-1)),'none') > 0 THEN 
                           xxas_goals_pkg.goal_comment_actions(xg.goal_id,rslt.perf_review_form_id,{requestParam.activePersonId}).get_xml() -- actions
                      END,
                      CASE 
                        WHEN LOWER(NVL({requestParam.enableAttachments}, '-1')) = 'y' 
                        THEN xxas_perf_util_pkg.get_attachments_xml(xg.goal_id)
                      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",
                                               rslt.current_step_id "reviewStepId",
                                               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||'_'||{requestParam.activePersonId} AS "parentId",
                                          rslt.current_step_id AS "reviewStepId",
                                          'note' AS "type",
                                          null   AS "body",
                                          'true' AS "editMode",
                                          xxas_com_msg_pkg.get_string('XXAS_PERF_REV_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",
                                          'false' AS "disableAutoSave",
                                          lower(NVL({requestParam.richTextComment},'false'))  AS "htmlEditor",
                                           NVL({requestParam.commentMaxLength},3900)   AS "maxlength",
                                          nvl(xxas_perf_util_pkg.get_formatted_name
                                                (papf.person_id)
                                                ,papf.last_name) "author"
                                         ),
                                      xxas_perf_util_pkg.get_auto_save_meta_xml(),
                                      XMLElement
                                        ("linked",
                                         XMLElement
                                           ("image",
                                              XMLElement
                                                  ("self",
                                                    xxas_com_rest_util_pkg.uri_from_path
                                                      ('people/' || 
                                                        nvl(nvl({requestParam.activePersonId},fnd_global.employee_id), -1) || 
                                                        '/photo')
                                                  ),
                                                  CASE WHEN nvl({requestParam.activePersonId},fnd_global.employee_id) IS NOT NULL THEN
                                                    XMLForest
                                                      (xxas_per_image_pkg.url(nvl({requestParam.activePersonId},fnd_global.employee_id),
                                                                              t.last_update_date,
                                                                              'SMALL') "small"
                                                      ,xxas_per_image_pkg.url(nvl({requestParam.activePersonId},fnd_global.employee_id),
                                                                              t.last_update_date, 
                                                                              'MEDIUM') "medium"
                                                      ,xxas_perf_util_pkg.get_initials(nvl({requestParam.activePersonId},fnd_global.employee_id),sysdate) "initials"
                                                      )
                                                  END
                                            ) 
                                        ),
                                      CASE WHEN xxas_perf_util_pkg.curr_step_goal_comm_acc_flag 
                                                    (p_perf_review_form_id  => rslt.perf_review_form_id
                                                    ,p_access_type          =>   'R'
                                                     ) = '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",
                                              XMLElement
                                                ("iconSteps"
                                                ,XMLAttributes('true' AS "json:force-array",'http://json.org/' AS "xmlns:json" )
                                                , (SELECT 
                                                XMLAGG(
                                                  XMLElement(NAME "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")
                                          )  
                                      END,
                                      XMLElement( "links", XMLElement( "comments",
                                       XMLForest(xxas_com_rest_util_pkg.uri_from_path
                                                   ('goals-in-review/goal-review-create/' ||xg.goal_id ||'/' ||rslt.perf_review_form_id || '?pageRefresh='||{requestParam.pageRefresh})AS "href"))))
                                      FROM dual, 
                                           xxas_per_thumbnails t,
                                           per_all_people_f papf
                                     WHERE papf.person_id = t.person_id (+)
                                       AND nvl({requestParam.activePersonId},fnd_global.employee_id) = papf.person_id
                                       AND TRUNC(sysdate)BETWEEN papf.effective_start_date AND papf.effective_end_date
                                       AND xxas_perf_util_pkg.is_partic_current_step_flag(rslt.perf_review_form_id, nvl({requestParam.activePersonId},fnd_global.employee_id)) = 'Y'
                                       AND NOT EXISTS ( SELECT 1 FROM xxas_goal_reviews_v xga WHERE xga.goal_id = xg.goal_id AND perf_review_step_id = rslt.current_step_id AND reviewer_person_id = nvl({requestParam.activePersonId},fnd_global.employee_id) )
                                       AND xxas_com_sec_pkg.test_instance('XXAS_DT_GOAL_REV_CRE_IN_REV',xg.goal_id,rslt.perf_review_form_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},-1)),
                                                 'review') > 0      -- If create is allowed in the list, 
                                                                    -- ensure the created comment 
                                                                    -- is shown after submitting.(inThisReview,inAllReviews)
                                       AND INSTR(lower
                                                (NVL({requestParam.includeComments},-1)),
                                                 'none') = 0
                                 ),-- Empty Item for New comment End
                                 ( SELECT 
                                     XMLAgg
                                       ( XMLElement
                                         ( "item",
                                           XMLForest
                                             ( xgrv.goal_review_id  AS "id",
                                               xgrv.goal_id||'_'||{requestParam.activePersonId} AS "parentId",
                                               rslt.current_step_id AS "reviewStepId",
                                               'note'               AS "type",
                                               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_REV_PLACEHOLDER') AS "placeholder",
                                               CASE WHEN {requestParam.dateDisplay} is not null THEN 
                                                         xxas_perf_util_pkg.goal_date_display(xgrv.last_update_date,xgrv.perf_review_step_id,{requestParam.includeComments})
                                               ELSE xxas_perf_util_pkg.goal_display(xgrv.perf_review_step_id,{requestParam.includeComments})
                                               END as "noteType",
                                               CASE WHEN {requestParam.dateDisplay} is null THEN 
                                                    xxas_util_pkg.days_ago(xgrv.last_update_date)
                                               END AS "dateCreatedFormatted",
                                               nvl(xxas_perf_util_pkg.get_formatted_name
                                                 (papf.person_id)
                                                 ,papf.last_name) "author"),
                                           xxas_perf_util_pkg.get_auto_save_meta_xml(),
                                           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
                                                 ),
                                               CASE WHEN xxas_perf_util_pkg.is_partic_current_step_flag(rslt.perf_review_form_id, nvl({requestParam.activePersonId},fnd_global.employee_id)) = 'Y' THEN
                                               xxas_goal_review_pkg.goal_comment_actions -- actions
                                                 (p_goal_id             => xgrv.goal_id
                                                 ,p_goal_review_id      => xgrv.goal_review_id
                                                 ,p_perf_review_form_id => rslt.perf_review_form_id
                                                 ,p_active_person_id    => {requestParam.activePersonId}).get_xml() END
                                             ),
                                           CASE WHEN xxas_perf_util_pkg.given_templ_step_goal_access 
                                                       (p_perf_review_step_id  => xgrv.perf_review_step_id
                                                       ,p_access_type          => 'R') = '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",
                                                   XMLElement
                                                    ("iconSteps"
                                                    ,XMLAttributes('true' AS "json:force-array",'http://json.org/' AS "xmlns:json" )
                                                    , (SELECT 
                                                    XMLAGG(XMLElement(NAME "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"
                                                      )
                                               ) 
                                           END,                                                    
                                           XMLElement
                                             ("links",
                                               XMLElement
                                                 ("comments",
                                                   XMLForest
                                                     (xxas_com_rest_util_pkg.uri_from_path
                                                       ('goals-in-review/goal-review-update/' ||
                                                        xgrv.goal_review_id ||
                                                        '/' ||
                                                        rslt.perf_review_form_id) AS "href")
                                                 )
                                             ) "links")ORDER BY 
                                             (case when lower(nvl({requestParam.commentsSort},'lastupdated')) = 'step'
                                             then xxas_perf_util_pkg.get_review_start_date(xgrv.perf_review_step_id) end) desc 
                                            ,(case when lower(nvl({requestParam.commentsSort},'lastupdated')) = 'step'
                                             then xxas_perf_util_pkg.get_step_sequence(xgrv.perf_review_step_id) end) desc
                                            ,(case when lower(nvl({requestParam.commentsSort},'lastupdated')) = 'lastupdated' 
                                             then xgrv.last_update_date end) 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_IN_REV',
                                           xgrv.goal_review_id,
                                           rslt.perf_review_form_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},-1)),
                                                 'none') = 0 
                                    AND (
                                          (INSTR(lower
                                                  (NVL({requestParam.includeComments},-1)),
                                                   'withgoalrating') > 0
                                             AND
                                             xgrv.rating IS NOT NULL
                                           )
                                         OR
                                           INSTR(lower
                                                    (NVL({requestParam.includeComments},-1)),
                                                     'withgoalrating') = 0
                                         )
                                    AND ( 
                                          (INSTR(lower
                                                (NVL({requestParam.includeComments},-1)),
                                                 'adhoc') > 0
                                           AND
                                           xgrv.perf_review_step_id IS NULL
                                          )
                                            OR
                                          ( 
                                            xgrv.perf_review_step_id IS NOT NULL
                                            AND
                                            ( 
                                              ({pathParam.reviewFormId} IN 
                                                    (SELECT perf_review_form_id 
                                                       FROM xxas_perf_review_steps xprs 
                                                      WHERE xprs.perf_review_step_id = xgrv.perf_review_step_id)
                                               AND
                                               INSTR(lower
                                                       (NVL({requestParam.includeComments},-1)),
                                                            'inthisreview') > 0
                                              ) 
                                              OR
                                              (INSTR(lower
                                                       (NVL({requestParam.includeComments},-1)),
                                                            'inallreviews') > 0
                                              )
                                            )
                                          )
                                        )
                               )
                          )
                       )
                    )
                 )
               )
             )
           )"data"
         FROM xxas_perf_review_forms_v rslt,
              xxas_goals_v xg
        WHERE rslt.perf_plan_enrollment_id = xg.perf_plan_enrollment_id
          AND rslt.perf_review_form_id     = {pathParam.reviewFormId}
          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 xxas_com_sec_pkg.test_instance
                ('XXAS_DT_GOALS_IN_REV',
                  xg.goal_id,
                  rslt.perf_review_form_id) = 'Y'
     ]'
    ); 
    
 xxas_com_sr_pkg.set_rest_method
    (p_service_name       => l_SERVICE
    ,p_method_name        => 'GetGoalDetailInReview'
    ,p_description        => 'Gets goal-detail by performance review'
    ,p_methods            => 'GET'
    ,p_remove_root_ele    => true
    ,p_path               => 'goal-detail/{goalId}/{reviewFormId}'
    ,p_sql                =>
      q'[
      SELECT 
         XMLElement
           ("data",
              XMLElement
                ("item",
                   XMLForest
                     (xg.goal_id          AS "id",
                      'goal'              AS "type",
                      CASE WHEN rslt.status_code NOT IN ('A','O') THEN xg.goal
                          ELSE xxas_perf_util_pkg.get_last_approved_goal(p_goal_id     => xg.goal_id) 
                      END                  AS "title",
                      rslt.current_step_id AS "reviewStepId",
                      CASE WHEN rslt.status_code NOT IN ('A','O') 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                   AS "label",
                      CASE WHEN xxas_com_sec_pkg.test_instance
                                 ('XXAS_DT_GOAL_UPD_IN_REV', xg.goal_id, rslt.perf_review_form_id) = 'Y' THEN
	                              'true'
	                         ELSE
                                'false'
	                     END  AS "statusUpdateAllowed",
                       CASE WHEN xxas_com_sec_pkg.test_instance
                                 ('XXAS_DT_GOAL_W_UPD_IN_REV', xg.goal_id, rslt.perf_review_form_id) = 'Y' THEN
	                              'true'
	                         ELSE
                                'false'
	                     END  AS "weightingUpdateAllowed",
                       CASE WHEN xxas_com_sec_pkg.test_instance
                                 ('XXAS_DT_GOAL_C_UPD_IN_REV', xg.goal_id, rslt.perf_review_form_id) = 'Y' THEN
	                              'true'
	                         ELSE
                                'false'
	                     END  AS "completionUpdateAllowed"),
                     CASE WHEN rslt.status_code NOT IN ('A','O') AND xg.weighting_percent IS NOT NULL  
                          THEN XMLElement("weighting",
                                 XMLForest(xxas_com_msg_pkg.get_string('XXAS_GOAL_WEIGHTING')          AS "label",
                                           rslt.current_step_id AS "reviewStepId",
                                           xg.weighting_percent AS "value"))
                          WHEN rslt.status_code IN ('A','O') 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",
                                                           rslt.current_step_id AS "reviewStepId",
                                                           xxas_perf_util_pkg.get_last_approved_goal_info(p_goal_id => xg.goal_id
                                                                                                 ,p_column_name => 'WEIGHTING_PERCENT') AS "value"))                                                                              
                     END AS "weighting",
                     CASE WHEN rslt.status_code NOT IN ('A','O') AND xg.complete_percent IS NOT NULL 
                      THEN XMLElement("completion",
                             XMLForest(xxas_com_msg_pkg.get_string('XXAS_GOAL_COMPLETION')          AS "label",
                                       rslt.current_step_id AS "reviewStepId",
                                       xg.complete_percent AS "value"))
                      WHEN rslt.status_code IN ('A','O') 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",
                                                       rslt.current_step_id "reviewStepId",
                                                       xxas_perf_util_pkg.get_last_approved_goal_info(p_goal_id => xg.goal_id
                                                                                             ,p_column_name => 'COMPLETE_PERCENT') AS "value"))                                                                              
                    END AS "completion" ,
                      XMLElement("status",
                        XMLForest(xg.status_code    AS "id",
                                  xg.status_meaning AS "label")),
                   XMLElement("links",
                     XMLElement("self",
                       XMLForest
                         (xxas_com_rest_util_pkg.uri_from_path
                           ('goals-in-review/'||xg.goal_id||'/'||rslt.perf_review_form_id||'?refresh=Y') AS "href"))),
                   XMLElement
                     ("linked",
                     (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",
                                               rslt.current_step_id "reviewStepId",
                                               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("item", -- Empty Item for New comment
                                                    XMLFOREST(0 AS "id",
                                                    xg.goal_id||'_'||{requestParam.activePersonId} AS "parentId",
                                                    rslt.current_step_id AS "reviewStepId",
                                                    'note' AS "type",
                                                    'false' AS "required",
                                                    null AS "body",
                                                    '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_REV_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(nvl({requestParam.activePersonId},fnd_global.employee_id), -1) || '/photo')
                                                                )
                                                                ,CASE WHEN nvl({requestParam.activePersonId},fnd_global.employee_id) IS NOT NULL THEN
                                                                    XMLForest
                                                                      (xxas_per_image_pkg.url(nvl({requestParam.activePersonId},fnd_global.employee_id), t.last_update_date, 'SMALL') "small"
                                                                      ,xxas_per_image_pkg.url(nvl({requestParam.activePersonId},fnd_global.employee_id), t.last_update_date, 'MEDIUM') "medium"
                                                                      ,xxas_perf_util_pkg.get_initials(nvl({requestParam.activePersonId},fnd_global.employee_id),sysdate) "initials"
                                                                      )
                                                                  END
                                                                ) 
                                                      ),
                                                    CASE WHEN xxas_perf_util_pkg.curr_step_goal_comm_acc_flag 
                                                                  (p_perf_review_form_id  => rslt.perf_review_form_id
                                                                  ,p_access_type          =>   'R'
                                                                   ) = 'Y' THEN
                                                              XMLElement(
                                                                "rating",
                                                                      XMLElement
                                                                          ("iconSteps"
                                                                          ,XMLAttributes('true' AS "json:force-array",'http://json.org/' AS "xmlns:json" )
                                                                          , (SELECT 
                                                                          XMLAGG(
                                                                            XMLElement(NAME "statuses"
                                                                             ,XMLForest(lookup_code AS "id"
                                                                                       ,meaning     AS "label"
                                                                                       ,nvl(xxas_perf_util_pkg.get_rating_icon_color(xg.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(xg.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(  nvl(xxas_perf_util_pkg.get_rating_icon_name(xg.perf_plan_enrollment_id),'starfilled') AS "icon",
                                                                            CASE WHEN xxas_perf_util_pkg.get_rating_icon_name(xg.perf_plan_enrollment_id) IS NULL 
                                                                                       AND xxas_perf_util_pkg.get_rating_icon_color(xg.perf_plan_enrollment_id) IS NULL 
                                                                                 THEN 'true' 
                                                                                 ELSE 'false'
                                                                            END AS "showAsSelectSingle",
                                                                            'false'      AS "required")) 
                                                    END,
                                                    XMLELEMENT("links",
                                                       XMLELEMENT( "comments",
                                                         XMLFOREST(xxas_com_rest_util_pkg.uri_from_path
                                                           ('goals-in-review/goal-review-create/' ||
                                                                      xg.goal_id ||
                                                                      '/' ||
                                                                      rslt.perf_review_form_id)AS "href"))) "links")
                                          FROM dual,
                                               xxas_per_thumbnails t,
                                               per_all_people_f papf
                                         WHERE papf.person_id = t.person_id (+)
                                           AND nvl({requestParam.activePersonId},fnd_global.employee_id) = papf.person_id
                                           AND TRUNC(sysdate)BETWEEN papf.effective_start_date AND papf.effective_end_date
                                           AND xxas_perf_util_pkg.is_partic_current_step_flag(rslt.perf_review_form_id, nvl({requestParam.activePersonId},fnd_global.employee_id)) = 'Y'
                                           AND NOT EXISTS ( SELECT 1 FROM xxas_goal_reviews_v xga WHERE xga.goal_id = xg.goal_id AND perf_review_step_id = rslt.current_step_id AND reviewer_person_id = nvl({requestParam.activePersonId},fnd_global.employee_id) )
                                           AND xxas_com_sec_pkg.test_instance('XXAS_DT_GOAL_REV_CRE_IN_REV',xg.goal_id,rslt.perf_review_form_id) = 'Y'
                                          )-- Empty Item for New comment
                                        ,(SELECT XMLAGG(
                                                   XMLELEMENT("item",
                                                     XMLFOREST(xgrv.goal_review_id AS "id",
                                                               xgrv.goal_id||'_'||{requestParam.activePersonId} AS "parentId",
                                                               rslt.current_step_id AS "reviewStepId",
                                                               'note' AS "type",
                                                               'false' AS "required",
                                                               xgrv.comments AS "body",
                                                               'true' AS "disableAutoSave",
                                                               lower(NVL({requestParam.richTextComment},'false'))  AS "htmlEditor",
                                                               NVL({requestParam.commentMaxLength},3900)   AS "maxlength",
                                                               xxas_com_msg_pkg.get_string('XXAS_PERF_REV_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) "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) 
                                                                   ,CASE WHEN xxas_perf_util_pkg.is_partic_current_step_flag(rslt.perf_review_form_id, nvl({requestParam.activePersonId},fnd_global.employee_id)) = 'Y' THEN 
                                                                   xxas_goal_review_pkg.goal_comment_actions -- actions
                                                                     (p_goal_id => xgrv.goal_id
                                                                     ,p_goal_review_id => xgrv.goal_review_id
                                                                     ,p_perf_review_form_id => rslt.perf_review_form_id
                                                                     ,p_active_person_id    => {requestParam.activePersonId}).get_xml() END
                                                                 ),
                                                               CASE WHEN xxas_perf_util_pkg.curr_step_goal_comm_acc_flag 
                                                                           (p_perf_review_form_id  => rslt.perf_review_form_id
                                                                           ,p_access_type          =>   'R'
                                                                            ) = 'Y' THEN
                                                                    XMLElement
                                                                      ("rating",
                                                                         XMLElement
                                                                          ("iconSteps"
                                                                          ,XMLAttributes('true' AS "json:force-array",'http://json.org/' AS "xmlns:json" )
                                                                          , (SELECT 
                                                                          XMLAGG(
                                                                            XMLElement(NAME "statuses"
                                                                             ,XMLForest(lookup_code AS "id"
                                                                                       ,meaning     AS "label"
                                                                                       ,nvl(xxas_perf_util_pkg.get_rating_icon_color(xg.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(xg.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(nvl(xxas_perf_util_pkg.get_rating_icon_name(xg.perf_plan_enrollment_id),'starfilled') AS "icon"
                                                                                     ,CASE WHEN xxas_perf_util_pkg.get_rating_icon_name(xg.perf_plan_enrollment_id) IS NULL 
                                                                                                 AND xxas_perf_util_pkg.get_rating_icon_color(xg.perf_plan_enrollment_id) IS NULL 
                                                                                           THEN 'true' 
                                                                                           ELSE 'false'
                                                                                      END AS "showAsSelectSingle"
                                                                                  ,xgrv.rating AS "value"
                                                                                  ,'false'     AS "required"
                                                                                 )
                                                                      ) 
                                                               END,                                                    
                                                               XMLELEMENT("links",
                                                                 XMLELEMENT("comments",
                                                                   XMLFOREST(xxas_com_rest_util_pkg.uri_from_path
                                                                     ('goals-in-review/goal-review-update/' ||
                                                                      xgrv.goal_review_id ||
                                                                      '/' ||
                                                                      rslt.perf_review_form_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_IN_REV',
                                                  xgrv.goal_review_id,rslt.perf_review_form_id) = 'Y'
                                          )
                                        )
                       )
                    )
                 )
               )"data"
         FROM xxas_perf_review_forms_v rslt,
              xxas_goals_v xg
        WHERE rslt.perf_plan_enrollment_id = xg.perf_plan_enrollment_id
          AND rslt.perf_review_form_id     = {pathParam.reviewFormId}
          AND xg.goal_id = {pathParam.goalId}
          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 xxas_com_sec_pkg.test_instance
                ('XXAS_DT_GOALS_IN_REV',
                  xg.goal_id,
                  rslt.perf_review_form_id) = 'Y'
     ]'
    ); 
    
   xxas_com_sr_pkg.set_rest_method
    (p_service_name       => l_SERVICE
    ,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}/{reviewFormId}'
    ,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', 'PERF_REVIEW_FORM_ID = :REVIEW_FORM_ID')
          ,xxas_com_srv_param_varray
            (xxas_com_srv_param_value('GOAL_ID', {pathParam.goalId})
            ,xxas_com_srv_param_value('REVIEW_FORM_ID', {pathParam.reviewFormId}))
          , 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        => 'UpdateGoalStatus'
    ,p_description        => 'Update a goal''s status'
    ,p_methods            => 'PUT'
    ,p_path               => '{goalId}/{reviewFormId}'
    ,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_IN_REV', {pathParam.goalId}, {pathParam.reviewFormId}) AND
             NOT xxas_com_sec_pkg.test_instance_rtn_boolean('XXAS_DT_GOAL_W_UPD_IN_REV', {pathParam.goalId}, {pathParam.reviewFormId}) AND
             NOT xxas_com_sec_pkg.test_instance_rtn_boolean('XXAS_DT_GOAL_C_UPD_IN_REV', {pathParam.goalId}, {pathParam.reviewFormId})
          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}/{reviewFormId}'
    ,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};
          
            xxas_goal_review_pkg.create_goal_review
              (p_review_form_id => {pathParam.reviewFormId}
              ,p_form_data_xml  => l_request_xml
              ,p_reload_page    => l_reload_page);            
              
            IF l_request_xml.extract('/*/postType/text()') IS NOT NULL
              AND  replace(dbms_xmlgen.convert((l_request_xml.extract('/*/postType/text()').getStringval())
                                          ,dbms_xmlgen.entity_decode
                                          )
                      ,'"')= 'autoSubmitted' THEN 
                IF l_reload_page IS NOT NULL and nvl({requestParam.pageRefresh},'N') = 'Y' THEN
                  
                  l_resp := xxas_http_response
                              .ok(xxas_com_rest_util_pkg.app_route_reload_response('modal'))
                              .build();
                END IF;
            ELSE
                
                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();
                
            END IF;
          
          {response} := l_resp;
        
        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}/{reviewFormId}'
    ,p_plsql              =>
      q'[
        DECLARE
        
          l_xml           XMLType;
          l_resp          xxas_http_resp_obj;
          l_request_xml   XMLType;          
          l_reload_page   VARCHAR2(10) := NULL;
          
        BEGIN
          l_request_xml := {requestBody};
          
          IF NOT xxas_com_sec_pkg.test_instance_rtn_boolean
            ('XXAS_DT_GOAL_REV_UPD_IN_REV', {pathParam.goalReviewId}, {pathParam.reviewFormId}) 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);
            
            IF l_request_xml.extract('/*/postType/text()') IS NOT NULL
              AND replace(dbms_xmlgen.convert((l_request_xml.extract('/*/postType/text()').getStringval())
                                           ,dbms_xmlgen.entity_decode
                                          )
                      ,'"')= 'autoSubmitted' THEN         
              IF l_reload_page IS NOT NULL THEN
                
                l_resp := xxas_http_response
                            .ok(xxas_com_rest_util_pkg.app_route_reload_response('modal'))
                            .build();
              
              END IF;
            ELSE
              
              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();
              
            END IF;
            
         {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}/{reviewFormId}'
    ,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_IN_REV', {pathParam.goalReviewId}, {pathParam.reviewFormId}) 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
      ]'
    );      
    
END;
/
COMMIT;
EXIT;
