/* $Id$ */
/*============================================================================*
 |                 Copyright (c) 2015 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
                       := 'CompetencyInReview';

BEGIN

  xxas_com_sr_pkg.set_rest_service
    (p_service_name       => l_SERVICE
    ,p_description        => 'Find Competencies'
    ,p_path               => 'competency-in-rev'
    ,p_consumes           => 'application/xml'
    ,p_produces           => 'application/xml');

  xxas_com_sr_pkg.set_rest_method
    (p_service_name       => l_SERVICE
    ,p_method_name        => 'FindCompetency'
    ,p_description        => 'Find competencies for the current person'
    ,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
															 ("competency"
															 ,XMLForest
																 (p.competence_id "id"
																 ,p.name "value"
																 ,p.name "label")
															 )
														 )
													 ) "data"
									FROM (select pc.name , pc.competence_id from per_competences_vl pc
												where business_group_id is null
												and lower(name) like lower('%'||{requestParam.q}||'%')
												and trunc(sysdate) between pc.date_from and nvl(date_to, trunc(sysdate))
												AND pc.competence_id NOT IN (select pce.competence_id from per_competence_elements pce
																									where pce.person_id = fnd_global.employee_id
																									and pce.type = 'PERSONAL'
																									and trunc(sysdate) between nvl(pce.effective_date_from,sysdate) and nvl(pce.effective_date_to,sysdate))) p                 
        ]'
    );

    
  xxas_com_sr_pkg.set_rest_method
    (p_service_name       => l_SERVICE
    ,p_method_name        => 'FindCompetencyByReview'
    ,p_description        => 'Finds list of competencies by 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(data)
            ) as data
    FROM (
      SELECT 
        XMLAgg(
           XMLELEMENT
									( "competency",
									XMLFOREST( competence_element_id "id",
									                    competence_element_id,
														proficiency_level_id,
														'competency' "type",
														competence_name "title",
                            rslt.current_step_id AS "reviewStepId",                            
														('/view-competency-in-rev/' ||competence_element_id||'_'||{pathParam.reviewFormId}||'_'||source) "href",
														'modal' "target",
														CASE 
															WHEN xxas_com_sec_pkg.test_instance('XXAS_DT_COMP_PROF_EDIT_IN_REV', competence_element_id, {pathParam.reviewFormId},source) = 'Y' THEN
																	 'true'
															ELSE
																	 'false'
															END  "statusUpdateAllowed"
														),
									XMLELEMENT
									("rating",
																XMLFOREST(   'true' AS "updateAllowed"
																						,'xxlarge' AS "iconSize"
																						,'large' AS "labelSize"
																						,nvl(xxas_perf_util_pkg.get_rating_icon_name(rslt.perf_plan_enrollment_id),'starfilled') AS "icon"        
																						,'balanced' AS "iconColor"                            
																						,'true' AS "multiColor"),
																(select XMLELEMENT("iconSteps",
																			XMLATTRIBUTES('true' AS "json:force-array",'http://json.org/' AS "xmlns:json" )
																					 ,XMLAGG(XMLELEMENT(NAME "iconSteps",XMLFOREST(rating_level_id AS "id",
																													name AS "label",
																													rslt.current_step_id AS "reviewStepId",
																													nvl(xxas_perf_util_pkg.get_rating_icon_color(rslt.perf_plan_enrollment_id),'positive') AS "color"))))
																from  (select * from per_rating_levels_vl 																
                                order by step_value)
                                where rating_scale_id = rslt.rating_scale_id
																),
																XMLFOREST(   rslt.proficiency_level_id AS "value"
																						,'Novice' AS "min"
																						,'Expert' AS "max"
																						,'MIN' AS "minLabel"                          
																						,'MAX' AS "maxLabel"
                                            , 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"  
                                          )
									) ,
                  CASE
                  WHEN (SELECT weighting_percent FROM xxas_competence_elements WHERE competence_element_id = rslt.competence_element_id) IS NOT NULL THEN
                      XMLELEMENT("weighting",
                          XMLFOREST(
                              xxas_com_msg_pkg.get_string('XXAS_GOAL_WEIGHTING') AS "label",
                              (SELECT weighting_percent FROM xxas_competence_elements WHERE competence_element_id = rslt.competence_element_id) AS "value"
                          )
                      )
                  ELSE
                      NULL
                  END AS "weighting",
                  XMLELEMENT("weightingUpdateAllowed", 'false'),
									XMLELEMENT
									("links",
															 XMLELEMENT( "rating",
																 XMLFOREST(xxas_com_rest_util_pkg.uri_from_path('competency-in-rev/'||competence_element_id||'?source='||source)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 LOWER(NVL({requestParam.enableAttachments}, '-1')) = 'y' 
                          THEN xxas_perf_util_pkg.get_attachments_xml(rslt.competence_element_id)
                        END,
                        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",
                                                    competence_element_id AS "parentId",
                                                    rslt.current_step_id AS "reviewStepId",
                                                    'note' AS "type",
                                                    null AS "body",
                                                    'true' AS "editMode",
                                                    'false' AS "disableAutoSave",
                                                    lower(NVL({requestParam.richTextComment},'false'))  AS "htmlEditor",
                                                    NVL({requestParam.commentMaxLength},3900)   AS "maxlength",                                               
                                                    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("meta"
                                                        ,XMLElement("options"
                                                           ,XMLForest('true' AS "autoSave"
                                                                     ,xxas_com_msg_pkg.get_string_for_lang
                                                                        ('XXAS_COM_AUTOSAVE_TIMEOUT','US')  AS "autoSaveTimeout"
                                                                     ,xxas_com_msg_pkg.get_string
                                                                        ('XXAS_PERF_COMMENTS_SAVED') AS "autoSaveMessage"
                                                                     )
                                                            )
                                                    ),      
                                                    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_perf_util_pkg.curr_step_compe_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(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_comp_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('competency-in-rev/competency-review-create/'||competence_element_id||'/'||{pathParam.reviewFormId}||'?source='||source)AS "href"))) "links"                    
                                                          )
                                          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_COMPE_REV_CRE_IN_REV',rslt.competence_element_id,{pathParam.reviewFormId},rslt.source) = 'Y'
                                          )-- Empty Item for New comment
                                         ,(SELECT XMLAGG(
                                                     XMLELEMENT("item",
                                                       XMLFOREST(xcrv.competency_review_id AS "id",
                                                                 xcrv.competency_element_id AS "parentId",
                                                                 rslt.current_step_id AS "reviewStepId",
                                                                 'note' AS "type",
                                                                 xcrv.comments AS "body",
                                                                 'false' AS "disableAutoSave",
                                                                 lower(NVL({requestParam.richTextComment},'false'))  AS "htmlEditor",
                                                                 NVL({requestParam.commentMaxLength},3900)   AS "maxlength",                                                                 
                                                                  xxas_util_pkg.days_ago(xcrv.last_update_date)
                                                                   ||
                                                                   CASE WHEN xcrv.perf_review_step_id IS NOT NULL THEN ' (' END
                                                                   ||
                                                                   CASE WHEN INSTR(lower(NVL({requestParam.includeComments},-1)),
                                                                                             'inallreviews') > 0
                                                                   THEN
                                                                      xxas_perf_util_pkg.get_form_step_name(xcrv.perf_review_step_id)
                                                                   ELSE
                                                                      xxas_perf_util_pkg.get_step_name (xcrv.perf_review_step_id)
                                                                   END
                                                                   ||
                                                                   CASE WHEN xcrv.perf_review_step_id IS NOT NULL THEN ')' END
                                                                                        AS "dateCreatedFormatted", 
                                                                 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("meta"
                                                                  ,XMLElement("options"
                                                                             ,XMLForest('true' AS "autoSave"
                                                                                       ,xxas_com_msg_pkg.get_string_for_lang
                                                                                          ('XXAS_COM_AUTOSAVE_TIMEOUT','US')  AS "autoSaveTimeout"
                                                                                       ,xxas_com_msg_pkg.get_string
                                                                                          ('XXAS_PERF_COMMENTS_SAVED') AS "autoSaveMessage"
                                                                                       )
                                                                              )
                                                                 ),
                                                                 XMLElement
                                                                   ("linked"
                                                                    ,XMLElement
                                                                      ("image"
                                                                       ,XMLElement
                                                                         ("self"
                                                                           ,xxas_com_rest_util_pkg.uri_from_path
                                                                             ('people/' || nvl(xcrv.reviewer_person_id, -1) || '/photo')
                                                                         )
                                                                       ,CASE WHEN xcrv.reviewer_person_id IS NOT NULL THEN
                                                                                  XMLForest
                                                                                   (xxas_per_image_pkg.url(xcrv.reviewer_person_id, t.last_update_date, 'SMALL') "small"
                                                                                   ,xxas_per_image_pkg.url(xcrv.reviewer_person_id, t.last_update_date, 'MEDIUM') "medium"
                                                                                   ,xxas_perf_util_pkg.get_initials(xcrv.reviewer_person_id,sysdate) "initials"
                                                                                   )
                                                                        END
                                                                      ) 
                                                                     ,xxas_competency_review_pkg.comment_actions_in_rev -- actions
                                                                       (p_competency_review_id => xcrv.competency_review_id
                                                                       ,p_perf_review_form_id  => {pathParam.reviewFormId}
                                                                      ).get_xml() 
                                                                   ),
                                                                 CASE WHEN xxas_perf_util_pkg.given_templ_step_goal_access 
                                                                             (p_perf_review_step_id  => xcrv.perf_review_step_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(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_comp_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",
                                                                            xcrv.rating AS "value"
                                                                            )
                                                                     ) 
                                                                 END,                                                                   
                                                                 XMLELEMENT("links",
                                                                   XMLELEMENT("comments",
                                                                     XMLFOREST(xxas_com_rest_util_pkg.uri_from_path('competency-in-rev/competency-review-update/' ||xcrv.competency_review_id||'/'||{pathParam.reviewFormId})AS "href")
                                                                             )
                                                                           ) "links"
                                                               )ORDER BY 
                                                                 (case when lower(nvl({requestParam.commentsSort},'lastupdated')) = 'step'
                                                                 then xxas_perf_util_pkg.get_review_start_date(xcrv.perf_review_step_id) end) desc 
                                                                ,(case when lower(nvl({requestParam.commentsSort},'lastupdated')) = 'step'
                                                                 then xxas_perf_util_pkg.get_step_sequence(xcrv.perf_review_step_id) end) desc
                                                                ,(case when lower(nvl({requestParam.commentsSort},'lastupdated')) = 'lastupdated' 
                                                                 then xcrv.last_update_date end) desc
                                                          )
                                             FROM xxas_competency_reviews_v xcrv,
                                                  xxas_per_thumbnails t,
                                                  per_all_people_f papf
                                            WHERE xcrv.competency_element_id = rslt.competence_element_id
                                              AND papf.person_id = t.person_id (+)
                                              AND xcrv.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_COMPE_REV_VIEW_IN_REV',
                                                  competency_review_id,{pathParam.reviewFormId}) = 'Y'
                                              AND INSTR(lower
                                                (NVL({requestParam.includeComments},-1)),
                                                 'none') = 0 
                                              AND (                                                     
                                                      xcrv.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 = xcrv.perf_review_step_id)
                                                         AND
                                                         INSTR(lower
                                                                 (NVL({requestParam.includeComments},-1)),
                                                                      'inthisreview') > 0
                                                        ) 
                                                        OR
                                                        (INSTR(lower
                                                                 (NVL({requestParam.includeComments},-1)),
                                                                      'inallreviews') > 0
                                                        )
                                                      )
                                                  )      --includeComments                                        
                                              )                                              
                                     )                                     
                                  )
                          ) "linked"
                  )
                  ORDER BY rslt.competence_name, rslt.compe_start_date 
                )
       as data
   FROM  xxas_competency_det_in_rev_v rslt
   WHERE rslt.perf_review_form_id     = {pathParam.reviewFormId}
   AND   xxas_com_sec_pkg.test_instance
                ('XXAS_DT_COMPETENCIES_IN_REV',
                  competence_element_id,
                  perf_review_form_id,
                  source) = 'Y'
   GROUP BY rslt.person_id
    )
      ]'
    );
    
        
  xxas_com_sr_pkg.set_rest_method
    (p_service_name       => l_SERVICE
    ,p_method_name        => 'Competency Details'
    ,p_description        => 'Finds details of a Competency'
    ,p_methods            => 'GET'
    ,p_remove_root_ele    => true
    ,p_path               => 'details/{competenceElementId}/{reviewFormId}'
    ,p_sql                =>
      q'[  
      SELECT 
    XMLElement
      ("data"
         ,XMLAttributes
            ('true' as "json:force-array"
            ,'http://json.org/' as "xmlns:json")
        ,XMLAgg(
           XMLELEMENT
									("item",
									XMLFOREST( competence_element_id "id",
									                    competence_element_id,
														proficiency_level_id,
														'competency'    "type",
														competence_name "title",
														('/view-competency-in-rev/' ||competence_element_id) "href",
														'modal'         "target",
                            'false' AS "competency_status_update_allowed"),                            
									XMLELEMENT
									("rating",
																XMLFOREST(   'true' AS "updateAllowed"
																						,'xxlarge' AS "iconSize"
																						,'large' AS "labelSize"
																						,nvl(xxas_perf_util_pkg.get_rating_icon_name(a.perf_plan_enrollment_id),'starfilled') AS "icon"        
																						,'balanced' AS "iconColor"                            
																						,'true' AS "multiColor"),
																(select XMLELEMENT("iconSteps",
																			XMLATTRIBUTES('true' AS "json:force-array",'http://json.org/' AS "xmlns:json" )
																					 ,XMLAGG(XMLELEMENT(NAME "iconSteps",XMLFOREST(rating_level_id AS "id",
																													name AS "label",
																													nvl(xxas_perf_util_pkg.get_rating_icon_color(a.perf_plan_enrollment_id),'positive') AS "color"))))
																from  (select * from per_rating_levels_vl 																
                                order by step_value)
                                where rating_scale_id = a.rating_scale_id
																),
																XMLFOREST(   a.proficiency_level_id AS "value"
																						,'Novice' AS "min"
																						,'Expert' AS "max"
																						,'MIN' AS "minLabel"                          
																						,'MAX' AS "maxLabel"
                                            , CASE WHEN xxas_perf_util_pkg.get_rating_icon_name(a.perf_plan_enrollment_id) IS NULL 
                                                         AND xxas_perf_util_pkg.get_rating_icon_color(a.perf_plan_enrollment_id) IS NULL 
                                                   THEN 'true' 
                                                   ELSE 'false'
                                                   END AS "showAsSelectSingle"                                          
                                         )
									) ,
									XMLELEMENT
									("links",
															 XMLELEMENT( "rating",
																 XMLFOREST(xxas_com_rest_util_pkg.uri_from_path('competency-in-rev/'||competence_element_id||'?source='||source)AS "href"))
									) "links"
									,
                  XMLELEMENT
									("linked",
                        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",
                                                    competence_element_id AS "parentId",                                                    
                                                    a.current_step_id AS "reviewStepId",
                                                    'note' AS "type",
                                                    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_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_perf_util_pkg.curr_step_compe_comm_acc_flag 
                                                                (p_perf_review_form_id  => a.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(a.perf_plan_enrollment_id),'energized') AS "color")
                                                                             ) ORDER BY lookup_code
                                                                       ) 
                                                                       FROM hr_lookups hrl
                                                                      WHERE hrl.lookup_type =  xxas_perf_util_pkg.get_comp_rating_lookup(a.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(a.perf_plan_enrollment_id) IS NULL 
                                                                         AND xxas_perf_util_pkg.get_rating_icon_color(a.perf_plan_enrollment_id) IS NULL 
                                                                   THEN 'true' 
                                                                   ELSE 'false'
                                                                   END AS "showAsSelectSingle",
                                                               nvl(xxas_perf_util_pkg.get_rating_icon_name(a.perf_plan_enrollment_id),'starfilled') AS "icon")
                                                          )  
                                                      END,
                                                    XMLELEMENT("links",
                                                     XMLELEMENT( "comments",
                                                      XMLFOREST(xxas_com_rest_util_pkg.uri_from_path('competency-in-rev/competency-review-create/'||competence_element_id||'/'||{pathParam.reviewFormId}||'?source='||source)AS "href"))) "links"                    
                                                          )
                                          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_COMPE_REV_CRE_IN_REV',
                                                                           a.competence_element_id,
                                                                           a.perf_review_form_id,a.source) = 'Y'
                                          )-- Empty Item for New comment
                                         ,(SELECT XMLAGG(
                                                     XMLELEMENT("item",
                                                       XMLFOREST(xcrv.competency_review_id AS "id",
                                                                 xcrv.competency_element_id AS "parentId",
                                                                 a.current_step_id AS "reviewStepId",
                                                                 'note' AS "type",
                                                                 xcrv.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_RB_COM_SAVE') AS "submitButtonText",
                                                                 xxas_com_msg_pkg.get_string('XXAS_RB_COM_CAN')  AS "cancelButtonText",                                                                 
                                                                 xxas_util_pkg.days_ago(xcrv.last_update_date) 
                                                                 || CASE WHEN xcrv.perf_review_step_id IS NOT NULL THEN
                                                                       ' (' ||xxas_perf_util_pkg.get_form_step_name(xcrv.perf_review_step_id) 
                                                                       || ')'
                                                                    END AS "dateCreatedFormatted",
                                                                 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(xcrv.reviewer_person_id, -1) || '/photo')
                                                                         )
                                                                       ,CASE WHEN xcrv.reviewer_person_id IS NOT NULL THEN
                                                                                  XMLForest
                                                                                   (xxas_per_image_pkg.url(xcrv.reviewer_person_id, t.last_update_date, 'SMALL') "small"
                                                                                   ,xxas_per_image_pkg.url(xcrv.reviewer_person_id, t.last_update_date, 'MEDIUM') "medium"
                                                                                   ,xxas_perf_util_pkg.get_initials(xcrv.reviewer_person_id,sysdate) "initials"
                                                                                   )
                                                                        END
                                                                      ) 
                                                                     ,xxas_competency_review_pkg.comment_actions_in_rev -- actions
                                                                       (p_competency_review_id  => xcrv.competency_review_id
                                                                       ,p_perf_review_form_id   => {pathParam.reviewFormId}
                                                                      ).get_xml() 
                                                                   ),
                                                                 CASE WHEN xxas_perf_util_pkg.curr_step_compe_comm_acc_flag 
                                                                             (p_perf_review_form_id  => a.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(a.perf_plan_enrollment_id),'energized') AS "color")
                                                                                       ) ORDER BY lookup_code
                                                                                 ) 
                                                                                 FROM hr_lookups hrl
                                                                                WHERE hrl.lookup_type =  xxas_perf_util_pkg.get_comp_rating_lookup(a.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(a.perf_plan_enrollment_id) IS NULL 
                                                                                       AND xxas_perf_util_pkg.get_rating_icon_color(a.perf_plan_enrollment_id) IS NULL 
                                                                                 THEN 'true' 
                                                                                 ELSE 'false'
                                                                                 END AS "showAsSelectSingle",
                                                                            nvl(xxas_perf_util_pkg.get_rating_icon_name(a.perf_plan_enrollment_id),'starfilled') AS "icon",
                                                                            xcrv.rating AS "value"
                                                                            )
                                                                     ) 
                                                                 END,                                                                   
                                                                 XMLELEMENT("links",
                                                                   XMLELEMENT("comments",
                                                                     XMLFOREST(xxas_com_rest_util_pkg.uri_from_path('competency-in-rev/competency-review-update/' ||xcrv.competency_review_id||'/'||{pathParam.reviewFormId})AS "href")
                                                                             )
                                                                           ) "links"
                                                               )ORDER BY 
                                                                 (case when lower(nvl({requestParam.commentsSort},'lastupdated')) = 'step'
                                                                 then xxas_perf_util_pkg.get_review_start_date(xcrv.perf_review_step_id) end) desc 
                                                                ,(case when lower(nvl({requestParam.commentsSort},'lastupdated')) = 'step'
                                                                 then xxas_perf_util_pkg.get_step_sequence(xcrv.perf_review_step_id) end) desc
                                                                ,(case when lower(nvl({requestParam.commentsSort},'lastupdated')) = 'lastupdated' 
                                                                 then xcrv.last_update_date end) desc
                                                          )
                                             FROM xxas_competency_reviews_v xcrv,
                                                  xxas_per_thumbnails t,
                                                  per_all_people_f papf
                                            WHERE xcrv.competency_element_id = {pathParam.competenceElementId}
                                              AND papf.person_id = t.person_id (+)
                                              AND xcrv.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_COMPE_REV_VIEW_IN_REV',
                                                       xcrv.competency_review_id,
                                                       a.perf_review_form_id) = 'Y'                                                            
                                            )
                                     )                                     
                                  )
                          ) "linked"
                   )
                  ORDER BY a.compe_start_date 
                )
      ) as data
   FROM xxas_competency_det_in_rev_v 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.competence_element_id = {pathParam.competenceElementId}
    AND a.perf_review_form_id = {pathParam.reviewFormId}
    and a.source = {requestParam.source}
    AND trunc ( sysdate )
        BETWEEN RSLT.effective_start_date
            AND RSLT.effective_end_date
      ]'
    );    

  xxas_com_sr_pkg.set_rest_method
    (p_service_name       => l_SERVICE
    ,p_method_name        => 'UpdateCompetencyRating'
    ,p_description        => 'Update competencies rating'
    ,p_methods            => 'PUT'    
    ,p_path               => '{competenceElementId}'
    ,p_plsql              =>    
      q'[ 
        DECLARE
        
          l_xml           XMLType;
          l_resp          xxas_http_resp_obj;
          l_request_xml   XMLType;          
          
        BEGIN
          l_request_xml := {requestBody};
          
          IF {requestParam.source} = 'XXAS' THEN
          
            IF 1=2 THEN
              l_resp := xxas_http_response
                          .status(xxas_http_resp_status_pkg.unauthorised)
                          .build();
            ELSE
            xxas_competence_elements_pkg.update_competency_status
              (p_competence_element_id => {pathParam.competenceElementId}
              ,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)
                         ,null,'modal')
                           )
                        .build();
           
           END IF;
           
         ELSE
            IF 1=2 THEN
              l_resp := xxas_http_response
                          .status(xxas_http_resp_status_pkg.unauthorised)
                          .build();
            ELSE
            xxas_competencies_pkg.update_competency_status
              (p_competence_element_id => {pathParam.competenceElementId}
              ,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)
                         ,null,'modal')
                           )
                        .build();
           END IF;
         END IF;     
         
        {response} := l_resp;
        END
      ]'
    );    

  xxas_com_sr_pkg.set_rest_method
    (p_service_name       => l_SERVICE
    ,p_method_name        => 'CreateCompetencyReview'
    ,p_description        => 'Creates a competency review'
    ,p_methods            => 'PUT'
    ,p_path               => 'competency-review-create/{competencyElementId}/{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_competency_review_pkg.create_competency_review
            (p_competency_element_id   => {pathParam.competencyElementId}
            ,p_perf_review_form_id     => {pathParam.reviewFormId}
            ,p_source                  => {requestParam.source}
            ,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
      ]'
    );  

  xxas_com_sr_pkg.set_rest_method
    (p_service_name       => l_SERVICE
    ,p_method_name        => 'UpdateCompetencyReview'
    ,p_description        => 'Update a competency review'
    ,p_methods            => 'PUT'
    ,p_path               => 'competency-review-update/{competencyReviewId}/{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_COMPE_REV_UPD_IN_REV', {pathParam.competencyReviewId}, {pathParam.reviewFormId}) THEN
            l_resp := xxas_http_response
                        .status(xxas_http_resp_status_pkg.unauthorised)
                        .build();
          ELSE                  
          xxas_competency_review_pkg.update_competency_review
            (p_competency_review_id    => {pathParam.competencyReviewId}      
            ,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        => 'DeleteCompetencyReview'
    ,p_description        => 'Deletes a Competency review'
    ,p_methods            => 'POST'
    ,p_path               => 'competency-review-delete/{competencyReviewId}/{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_COMPE_REV_DEL_IN_REV', {pathParam.competencyReviewId}, {pathParam.reviewFormId}) THEN
            l_resp := xxas_http_response
                        .status(xxas_http_resp_status_pkg.unauthorised)
                        .build();
          ELSE              
          xxas_competency_review_pkg.delete_review
            (p_competency_review_id => {pathParam.competencyReviewId});
              
          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;