/* $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
                       := 'Competency';

BEGIN

  xxas_com_sr_pkg.set_rest_service
    (p_service_name       => l_SERVICE
    ,p_description        => 'Find Competencies'
    ,p_path               => 'competency'
    ,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)  
                                                     UNION
                                                     select xcev.competence_id
                                                     from xxas_competence_elements_v xcev
                                                     where xcev.person_id = fnd_global.employee_id
                                                    )
                      ) p                 
        ]'
    );

    
  xxas_com_sr_pkg.set_rest_method
    (p_service_name       => l_SERVICE
    ,p_method_name        => 'FindRatingsforCompetency'
    ,p_description        => 'Find Proficiency levels for the given competencies'
    ,p_methods            => 'GET'
    ,p_remove_root_ele    => true
    ,p_path               => '{competencyId}/proficiencies'    
    ,p_sql                =>
      q'[
					SELECT XMLElement
													 ("data"
													 ,XMLAttributes
														 ('true' as "json:force-array"
														 ,'http://json.org/' as "xmlns:json")
													 ,XMLAgg
														 (XMLElement
															 ("proficiency"
															 ,XMLForest
																 (p.rating_level_id "id"
																 ,p.name "value"
																 ,p.name "label")
															 )
														 )
													 ) "data"
									FROM (select b.step_value || '.' || b.name name
                              ,b.rating_level_id
                              ,b.step_value
                        from per_competences a, per_rating_levels_vl b
                        where a.competence_id = {pathParam.competencyId}
                        and (b.competence_id = a.competence_id
                        or a.rating_scale_id = b.rating_scale_id)
                        order by b.step_value) p                          
        ]'
    );
    
  xxas_com_sr_pkg.set_rest_method
    (p_service_name       => l_SERVICE
    ,p_method_name        => 'FindCompetencyList'
    ,p_description        => 'Finds list of competencies'
    ,p_methods            => 'GET'
    ,p_remove_root_ele    => true
    ,p_path               => 'list/{enrollmentId}'
    ,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",
														('/view-competency/' ||competence_element_id||'_'||{pathParam.enrollmentId}) "href",
														'modal' "target",
														CASE 
															WHEN xxas_com_sec_pkg.test_instance('XXAS_DT_COMP_PROF_EDIT', competence_element_id) = 'Y' THEN
																	 'true'
															ELSE
																	 'false'
															END  "statusUpdateAllowed"
														),
									XMLELEMENT
									("rating",
																XMLFOREST(   'true' AS "updateAllowed"
																						,'xxlarge' AS "iconSize"
																						,'small' AS "labelSize"
																						,nvl(xxas_perf_util_pkg.get_rating_icon_name(rslt.perf_plan_enrollment_id),'starfilled') AS "icon"        
																						,'assertive' 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(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"
                                          )
									) ,
									XMLELEMENT
									("links",
															 XMLELEMENT( "rating",
																 XMLFOREST(xxas_com_rest_util_pkg.uri_from_path('competency/'||competence_element_id)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",
                                                    {pathParam.enrollmentId} AS "enrollmentId",
                                                    0 AS "reviewStepId",
                                                    'note' AS "type",
                                                    null AS "body",
                                                    'true' AS "editMode",
                                                    'true' AS "disableAutoSave",
                                                    xxas_com_msg_pkg.get_string('XXAS_RB_COM_SAVE') AS "submitButtonText",
                                                    xxas_com_msg_pkg.get_string('XXAS_RB_COM_CAN')  AS "cancelButtonText",
                                                    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
                                                      ("linked"
                                                         ,XMLElement
                                                           ("image"
                                                              ,XMLElement
                                                                ("self"
                                                                  ,xxas_com_rest_util_pkg.uri_from_path
                                                                    ('people/' || nvl(fnd_global.employee_id, -1) || '/photo')
                                                                )
                                                                ,CASE WHEN fnd_global.employee_id IS NOT NULL THEN
                                                                    XMLForest
                                                                      (xxas_per_image_pkg.url(fnd_global.employee_id, t.last_update_date, 'SMALL') "small"
                                                                      ,xxas_per_image_pkg.url(fnd_global.employee_id, t.last_update_date, 'MEDIUM') "medium"
                                                                      ,xxas_perf_util_pkg.get_initials(fnd_global.employee_id,sysdate) "initials"
                                                                      )
                                                                  END
                                                                ) 
                                                      ),
                                                    CASE WHEN xxas_com_sec_pkg.test_instance('XXAS_DT_COMP_REV_RATING',
                                                                                             rslt.competence_element_id) = 'Y' THEN
                                                      XMLElement
                                                        ( "rating",
                                                          (SELECT XMLElement
                                                                    ("iconSteps",
                                                                     XMLAttributes('true' AS "json:force-array",
                                                                                   'http://json.org/' AS "xmlns:json" ),
                                                                     XMLAgg
                                                                          (XMLElement
                                                                            ("statuses",
                                                                             XMLForest
                                                                               (lookup_code   AS "id",
                                                                                meaning       AS "label",
                                                                                nvl(xxas_perf_util_pkg.get_rating_icon_color({pathParam.enrollmentId}),'energized') AS "color"
                                                                                )
                                                                             )
                                                                           )
                                                                    )
                                                             FROM (SELECT * 
                                                                     FROM hr_lookups hrl
                                                                    WHERE hrl.lookup_type = xxas_perf_util_pkg.get_comp_rating_lookup({pathParam.enrollmentId})
                                                                      AND hrl.enabled_flag = 'Y'
                                                                      AND trunc(sysdate)
                                                                               BETWEEN nvl(hrl.start_date_active,trunc(sysdate))
                                                                                   AND nvl(hrl.end_date_active,trunc(sysdate)) 
                                                                    ORDER BY lookup_code )
                                                          ),
                                                          XMLForest
                                                            (CASE WHEN xxas_perf_util_pkg.get_rating_icon_name({pathParam.enrollmentId}) IS NULL 
                                                                       AND xxas_perf_util_pkg.get_rating_icon_color({pathParam.enrollmentId}) IS NULL 
                                                                 THEN 'true' 
                                                                 ELSE 'false'
                                                                 END AS "showAsSelectSingle",
                                                                 'false' AS "required",
                                                             nvl(xxas_perf_util_pkg.get_rating_icon_name({pathParam.enrollmentId}),'starfilled') AS "icon")
                                                        )  
                                                    END,
                                                    XMLELEMENT("links",
                                                     XMLELEMENT( "comments",
                                                      XMLFOREST(xxas_com_rest_util_pkg.uri_from_path('competency/competency-review-create/'||competence_element_id||'?enrollmentId='||{pathParam.enrollmentId})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_COMPETENCY_REV_CRE',competence_element_id) = 'Y'
                                           AND INSTR(lower
                                              (NVL({requestParam.includeComments},'none')),
                                               'none') = 0
                                          )-- Empty Item for New comment
                                         ,(SELECT XMLAGG(
                                                     XMLELEMENT("item",
                                                       XMLFOREST(xgrv.competency_review_id AS "id",
                                                                 xgrv.competency_element_id AS "parentId",
                                                                 0 AS "reviewStepId",
                                                                 'note' AS "type",
                                                                 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_RB_COM_SAVE') AS "submitButtonText",
                                                                 xxas_com_msg_pkg.get_string('XXAS_RB_COM_CAN')  AS "cancelButtonText",                                                                 
                                                                 xxas_util_pkg.days_ago(xgrv.last_update_date) AS "dateCreatedFormatted", 
                                                                 nvl(xxas_util_pkg.get_formatted_name
                                                                   (papf.full_name
                                                                   ,papf.global_name
                                                                   ,papf.local_name
                                                                   ,papf.first_name
                                                                   ,papf.last_name)
                                                                   ,papf.last_name) "author"),
                                                                 XMLElement
                                                                   ("linked"
                                                                    ,XMLElement
                                                                      ("image"
                                                                       ,XMLElement
                                                                         ("self"
                                                                           ,xxas_com_rest_util_pkg.uri_from_path
                                                                             ('people/' || nvl(xgrv.reviewer_person_id, -1) || '/photo')
                                                                         )
                                                                       ,CASE WHEN xgrv.reviewer_person_id IS NOT NULL THEN
                                                                                  XMLForest
                                                                                   (xxas_per_image_pkg.url(xgrv.reviewer_person_id, t.last_update_date, 'SMALL') "small"
                                                                                   ,xxas_per_image_pkg.url(xgrv.reviewer_person_id, t.last_update_date, 'MEDIUM') "medium"
                                                                                   ,xxas_perf_util_pkg.get_initials(xgrv.reviewer_person_id,sysdate) "initials"
                                                                                   )
                                                                        END
                                                                      ) 
                                                                     ,xxas_competency_review_pkg.competency_comment_actions -- actions
                                                                       (p_competency_review_id => xgrv.competency_review_id
                                                                      ).get_xml() 
                                                                   ),
                                                                 CASE WHEN xxas_com_sec_pkg.test_instance('XXAS_DT_COMP_REV_RATING'
                                                                                                        ,rslt.competence_element_id) = 'Y' THEN
                                                                   XMLElement
                                                                     ("rating",
                                                                       (SELECT XMLElement
                                                                                 ("iconSteps",
                                                                                  XMLAttributes('true' AS "json:force-array",
                                                                                                'http://json.org/' AS "xmlns:json" ),
                                                                                  XMLAgg
                                                                                    (XMLElement
                                                                                      ("statuses",
                                                                                       XMLForest
                                                                                         (lookup_code      AS "id"
                                                                                          ,meaning         AS "label"
                                                                                          ,nvl(xxas_perf_util_pkg.get_rating_icon_color({pathParam.enrollmentId}),'energized') AS "color"
                                                                                         )
                                                                                      )
                                                                                    )
                                                                                 )
                                                                           FROM (SELECT * 
                                                                                   FROM hr_lookups hrl
                                                                                  WHERE hrl.lookup_type = xxas_perf_util_pkg.get_comp_rating_lookup({pathParam.enrollmentId})
                                                                                    AND hrl.enabled_flag = 'Y'
                                                                                    AND trunc(sysdate )
                                                                                          BETWEEN nvl(hrl.start_date_active,trunc(sysdate))
                                                                                              AND nvl(hrl.end_date_active,trunc(sysdate)) 
                                                                                 ORDER BY lookup_code
                                                                                )
                                                                       ),
                                                                       XMLForest
                                                                           (CASE WHEN xxas_perf_util_pkg.get_rating_icon_name({pathParam.enrollmentId}) IS NULL 
                                                                                       AND xxas_perf_util_pkg.get_rating_icon_color({pathParam.enrollmentId}) IS NULL 
                                                                                 THEN 'true' 
                                                                                 ELSE 'false'
                                                                                 END AS "showAsSelectSingle",
                                                                            nvl(xxas_perf_util_pkg.get_rating_icon_name({pathParam.enrollmentId}),'starfilled') AS "icon",
                                                                            xgrv.rating AS "value",
                                                                            'false' AS "required"
                                                                            )
                                                                     ) 
                                                                 END ,                                                                   
                                                                 XMLELEMENT("links",
                                                                   XMLELEMENT("comments",
                                                                     XMLFOREST(xxas_com_rest_util_pkg.uri_from_path('competency/competency-review-update/' ||xgrv.competency_review_id)AS "href")
                                                                             )
                                                                           ) "links"
                                                               )ORDER BY xgrv.last_update_date desc
                                                          )
                                             FROM xxas_competency_reviews_v xgrv,
                                                  xxas_per_thumbnails t,
                                                  per_all_people_f papf
                                            WHERE xgrv.competency_element_id = rslt.competence_element_id 
                                              AND xgrv.perf_plan_enrollment_id = {pathParam.enrollmentId}
                                              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_COMPETENCY_REV_VIEW',xgrv.competency_review_id) = 'Y' 
                                              AND INSTR(lower
                                              (NVL({requestParam.includeComments},'none')),
                                               'none') = 0 
                                              AND ( 
                                                    (INSTR(lower
                                                          (NVL({requestParam.includeComments},'none')),
                                                           'adhoc') > 0
                                                     AND
                                                     xgrv.perf_review_step_id IS NULL
                                                    )
                                                      OR
                                                    ( 
                                                      xgrv.perf_review_step_id IS NOT NULL
                                                      AND
                                                      INSTR(lower
                                                              (NVL({requestParam.includeComments},'none')),
                                                                    'inreviews') > 0
                                                    )
                                                  )    
                                         )                                                  
                                     )                                     
                                  )
                          ) "linked"
									)                          
                  ORDER BY rslt.compe_start_date 
                ) as data
   FROM xxas_competency_details_v rslt
   WHERE rslt.perf_plan_enrollment_id = {pathParam.enrollmentId}
   AND {authFunctionPredicate.XXAS_DT_PERF_ENROLLMENT}
   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}/{enrollmentId}'
    ,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/' ||competence_element_id) "href",
														'modal'         "target"),
									XMLELEMENT
									("rating",
																XMLFOREST(   'true' AS "updateAllowed"
																						,'xxlarge' AS "iconSize"
																						,'large' AS "labelSize"
																						,nvl(xxas_perf_util_pkg.get_rating_icon_name({pathParam.enrollmentId}),'starfilled') AS "icon"        
																						,nvl(xxas_perf_util_pkg.get_rating_icon_color({pathParam.enrollmentId}),'energized') 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({pathParam.enrollmentId}),'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")
									) ,
									XMLELEMENT
									("links",
															 XMLELEMENT( "rating",
																 XMLFOREST(xxas_com_rest_util_pkg.uri_from_path('competency/'||competence_element_id)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",
                                                    {pathParam.enrollmentId} AS "enrollmentId",
                                                    0 AS "reviewStepId",
                                                    'note' AS "type",
                                                    null AS "body",
                                                    'true' AS "editMode",
                                                    'true' AS "disableAutoSave",
                                                    xxas_com_msg_pkg.get_string('XXAS_RB_COM_SAVE') AS "submitButtonText",
                                                    xxas_com_msg_pkg.get_string('XXAS_RB_COM_CAN')  AS "cancelButtonText",
                                                    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
                                                      ("linked"
                                                         ,XMLElement
                                                           ("image"
                                                              ,XMLElement
                                                                ("self"
                                                                  ,xxas_com_rest_util_pkg.uri_from_path
                                                                    ('people/' || nvl(fnd_global.employee_id, -1) || '/photo')
                                                                )
                                                                ,CASE WHEN fnd_global.employee_id IS NOT NULL THEN
                                                                    XMLForest
                                                                      (xxas_per_image_pkg.url(fnd_global.employee_id, t.last_update_date, 'SMALL') "small"
                                                                      ,xxas_per_image_pkg.url(fnd_global.employee_id, t.last_update_date, 'MEDIUM') "medium"
                                                                      ,xxas_perf_util_pkg.get_initials(fnd_global.employee_id,sysdate) "initials"
                                                                      )
                                                                  END
                                                                ) 
                                                      ),
                                                      CASE WHEN xxas_com_sec_pkg.test_instance('XXAS_DT_COMP_REV_RATING',
                                                                                             a.competence_element_id) = 'Y' THEN
                                                      XMLElement
                                                        ( "rating",
                                                          (SELECT XMLElement
                                                                    ("iconSteps",
                                                                     XMLAttributes('true' AS "json:force-array",
                                                                                   'http://json.org/' AS "xmlns:json" ),
                                                                     XMLAgg
                                                                          (XMLElement
                                                                            ("statuses",
                                                                             XMLForest
                                                                               (lookup_code   AS "id",
                                                                                meaning       AS "label",
                                                                                nvl(xxas_perf_util_pkg.get_rating_icon_color({pathParam.enrollmentId}),'energized') AS "color"
                                                                                )
                                                                             )
                                                                           )
                                                                    )
                                                             FROM (SELECT * 
                                                                     FROM hr_lookups hrl
                                                                    WHERE hrl.lookup_type = xxas_perf_util_pkg.get_comp_rating_lookup({pathParam.enrollmentId})
                                                                      AND hrl.enabled_flag = 'Y'
                                                                      AND trunc(sysdate)
                                                                               BETWEEN nvl(hrl.start_date_active,trunc(sysdate))
                                                                                   AND nvl(hrl.end_date_active,trunc(sysdate)) 
                                                                    ORDER BY lookup_code )
                                                          ),
                                                          XMLForest
                                                            (CASE WHEN xxas_perf_util_pkg.get_rating_icon_name({pathParam.enrollmentId}) IS NULL 
                                                                       AND xxas_perf_util_pkg.get_rating_icon_color({pathParam.enrollmentId}) IS NULL 
                                                                 THEN 'true' 
                                                                 ELSE 'false'
                                                                 END AS "showAsSelectSingle",
                                                                 'false' AS "required",
                                                             nvl(xxas_perf_util_pkg.get_rating_icon_name({pathParam.enrollmentId}),'starfilled') AS "icon")
                                                        )  
                                                    END,
                                                    XMLELEMENT("links",
                                                     XMLELEMENT( "comments",
                                                      XMLFOREST(xxas_com_rest_util_pkg.uri_from_path('competency/competency-review-create/'||competence_element_id||'?enrollmentId='||{pathParam.enrollmentId})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_COMPETENCY_REV_CRE',competence_element_id) = 'Y'
                                          )-- Empty Item for New comment
                                         ,(SELECT XMLAGG(
                                                     XMLELEMENT("item",
                                                       XMLFOREST(xgrv.competency_review_id AS "id",
                                                                 xgrv.competency_element_id AS "parentId",
                                                                 0 AS "reviewStepId",
                                                                 'note' AS "type",
                                                                 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_RB_COM_SAVE') AS "submitButtonText",
                                                                 xxas_com_msg_pkg.get_string('XXAS_RB_COM_CAN')  AS "cancelButtonText",                                                                 
                                                                 xxas_util_pkg.days_ago(xgrv.last_update_date) AS "dateCreatedFormatted", 
                                                                 nvl(xxas_util_pkg.get_formatted_name
                                                                   (papf.full_name
                                                                   ,papf.global_name
                                                                   ,papf.local_name
                                                                   ,papf.first_name
                                                                   ,papf.last_name)
                                                                   ,papf.last_name) "author"),
                                                                 XMLElement
                                                                   ("linked"
                                                                    ,XMLElement
                                                                      ("image"
                                                                       ,XMLElement
                                                                         ("self"
                                                                           ,xxas_com_rest_util_pkg.uri_from_path
                                                                             ('people/' || nvl(xgrv.reviewer_person_id, -1) || '/photo')
                                                                         )
                                                                       ,CASE WHEN xgrv.reviewer_person_id IS NOT NULL THEN
                                                                                  XMLForest
                                                                                   (xxas_per_image_pkg.url(xgrv.reviewer_person_id, t.last_update_date, 'SMALL') "small"
                                                                                   ,xxas_per_image_pkg.url(xgrv.reviewer_person_id, t.last_update_date, 'MEDIUM') "medium"
                                                                                   ,xxas_perf_util_pkg.get_initials(xgrv.reviewer_person_id,sysdate) "initials"
                                                                                   )
                                                                        END
                                                                      ) 
                                                                     ,xxas_competency_review_pkg.competency_comment_actions -- actions
                                                                       (p_competency_review_id => xgrv.competency_review_id
                                                                      ).get_xml() 
                                                                   ), 
                                                                CASE WHEN xxas_com_sec_pkg.test_instance('XXAS_DT_COMP_REV_RATING'
                                                                                                        ,a.competence_element_id) = 'Y' THEN
                                                                   XMLElement
                                                                     ("rating",
                                                                       (SELECT XMLElement
                                                                                 ("iconSteps",
                                                                                  XMLAttributes('true' AS "json:force-array",
                                                                                                'http://json.org/' AS "xmlns:json" ),
                                                                                  XMLAgg
                                                                                    (XMLElement
                                                                                      ("statuses",
                                                                                       XMLForest
                                                                                         (lookup_code      AS "id"
                                                                                          ,meaning         AS "label"
                                                                                          ,nvl(xxas_perf_util_pkg.get_rating_icon_color({pathParam.enrollmentId}),'energized') AS "color"
                                                                                         )
                                                                                      )
                                                                                    )
                                                                                 )
                                                                           FROM (SELECT * 
                                                                                   FROM hr_lookups hrl
                                                                                  WHERE hrl.lookup_type = xxas_perf_util_pkg.get_comp_rating_lookup({pathParam.enrollmentId})
                                                                                    AND hrl.enabled_flag = 'Y'
                                                                                    AND trunc(sysdate )
                                                                                          BETWEEN nvl(hrl.start_date_active,trunc(sysdate))
                                                                                              AND nvl(hrl.end_date_active,trunc(sysdate)) 
                                                                                 ORDER BY lookup_code
                                                                                )
                                                                       ),
                                                                       XMLForest
                                                                           (CASE WHEN xxas_perf_util_pkg.get_rating_icon_name({pathParam.enrollmentId}) IS NULL 
                                                                                       AND xxas_perf_util_pkg.get_rating_icon_color({pathParam.enrollmentId}) IS NULL 
                                                                                 THEN 'true' 
                                                                                 ELSE 'false'
                                                                                 END AS "showAsSelectSingle",
                                                                            nvl(xxas_perf_util_pkg.get_rating_icon_name({pathParam.enrollmentId}),'starfilled') AS "icon",
                                                                            xgrv.rating AS "value",
                                                                            'false' AS "required"
                                                                            )
                                                                     ) 
                                                                 END ,                                                                  
                                                                 XMLELEMENT("links",
                                                                   XMLELEMENT("comments",
                                                                     XMLFOREST(xxas_com_rest_util_pkg.uri_from_path('competency/competency-review-update/' ||xgrv.competency_review_id)AS "href")
                                                                             )
                                                                           ) "links"
                                                               )ORDER BY xgrv.last_update_date desc
                                                          )
                                             FROM xxas_competency_reviews_v xgrv,
                                                  xxas_per_thumbnails t,
                                                  per_all_people_f papf
                                            WHERE xgrv.competency_element_id = {pathParam.competenceElementId}
                                              AND xgrv.perf_plan_enrollment_id = {pathParam.enrollmentId}
                                              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_COMPETENCY_REV_VIEW',xgrv.competency_review_id) = 'Y')                                          
                                     )                                     
                                  )
                          ) "linked"
                   )
                  ORDER BY a.start_date 
                )
      ) as data
   FROM xxas_competencies_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 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};
                        
          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)
                       ,'self','modal')
                         )
                      .build();
         
         {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}'
    ,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_COMPETENCY_REV_CRE', {pathParam.competencyElementId}) THEN
            l_resp := xxas_http_response
                        .status(xxas_http_resp_status_pkg.unauthorised)
                        .build();
          ELSE
          xxas_competency_review_pkg.create_competency_review
            (p_competency_element_id   => {pathParam.competencyElementId}
            ,p_perf_plan_enrollment_id => {requestParam.enrollmentId}
            ,p_form_data_xml           => l_request_xml);            
              
          l_resp := xxas_http_response
                      .ok(xxas_com_msg_pkg.get_success_message_xml
                           (xxas_com_msg_type
                         (xxas_util_pkg.app, 'XXAS_COM_CHNG_SAVE', null)
                       ,'self'
                       ,'modal')
                         )
                      .build();
         
         {response} := l_resp;
          END IF;         
        END
      ]'
    );    

  xxas_com_sr_pkg.set_rest_method
    (p_service_name       => l_SERVICE
    ,p_method_name        => 'UpdateCompetencyReview'
    ,p_description        => 'Update a competency review'
    ,p_methods            => 'PUT'
    ,p_path               => 'competency-review-update/{competencyReviewId}'
    ,p_plsql              =>
      q'[
        DECLARE
        
          l_xml           XMLType;
          l_resp          xxas_http_resp_obj;
          l_request_xml   XMLType;           
          l_reload_page   VARCHAR2(10);        
          
        BEGIN
          l_request_xml := {requestBody};
          
          IF NOT xxas_com_sec_pkg.test_instance_rtn_boolean
            ('XXAS_DT_COMPETENCY_REV_UPD', {pathParam.competencyReviewId}) 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);
           
          l_resp := xxas_http_response
                      .ok(xxas_com_msg_pkg.get_success_message_xml
                           (xxas_com_msg_type
                         (xxas_util_pkg.app, 'XXAS_COM_CHNG_SAVE', null)
                       ,'self'
                       ,'modal')
                         )
                      .build();
         
         {response} := l_resp;
         END IF;
        END
      ]'
    );     
    
  xxas_com_sr_pkg.set_rest_method
    (p_service_name       => l_SERVICE
    ,p_method_name        => 'DeleteCompetencyReview'
    ,p_description        => 'Deletes a Competency review'
    ,p_methods            => 'POST'
    ,p_path               => 'competency-review-delete/{competencyReviewId}'
    ,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_COMPETENCY_REV_DEL', {pathParam.competencyReviewId}) 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;