/* $Id$ */
/*============================================================================*
 |                 Copyright (c) 2014 Applaud Solutions UK Ltd                |
 |                             All rights reserved                            |
 *===========================================================================*/

SET VERIFY OFF
SET FEEDBACK OFF
SET SCAN OFF
WHENEVER OSERROR  EXIT FAILURE ROLLBACK
WHENEVER SQLERROR EXIT FAILURE ROLLBACK

DECLARE

  --
  -- Local constants and variables
  --
  l_SERVICE CONSTANT xxas_com_service_repository.service_name%TYPE
                       := 'PerformanceReview';
                       
BEGIN
      
  xxas_com_sr_pkg.set_rest_service
    (p_service_name       => l_SERVICE
    ,p_description        => 'Manage performance reviews' 
    ,p_consumes           => 'application/xml'
    ,p_produces           => 'application/xml'
    );
    
  xxas_com_sr_pkg.set_rest_method
    (p_service_name       => l_SERVICE
    ,p_method_name        => 'PlanReviewByManager'
    ,p_description        => 'Plan reviews for direct reports by manager '
    ,p_methods            => 'GET'
    ,p_path               => 'performance-plan-review/{perfPlanReviewId}/by-manager/{personId}'
    ,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
                    ("item"
                    ,XMLForest
                      (xprf.perf_review_form_id "id"
                     ,'note' "type"
                      ,'&lt;div class="pre"&gt;&lt;font color="'
                      || decode(xprf.status_code
                               ,'NS'
                               ,xxas_rslt_cache_pkg.resolve_color_name('red')
                               ,'A'
                               ,xxas_rslt_cache_pkg.resolve_color_name('green')
                               ,'O'
                               ,xxas_rslt_cache_pkg.resolve_color_name('green')															 
                               ,'D'
                               ,xxas_rslt_cache_pkg.resolve_color_name('orange')
                               ,'X'
                               ,xxas_rslt_cache_pkg.resolve_color_name('grey')
                               )
                      || '"&gt;' 
                      || xxas_util_pkg.decode_lookup('XXAS_PERF_REVIEW_FORM_STATUSES', xprf.status_code)
                      || decode(xprf.status_code
                               ,'D'
                               ,nvl((SELECT ' - ' || xtsv.title
                                    from xxas_perf_review_steps xprs1
                                        ,xxas_perf_Review_templ_steps_v xtsv
                                    where  xprs1.perf_review_step_id = xprf.current_step_id
                                    and    xtsv.perf_Review_templ_step_id = xprs1.perf_Review_templ_step_id)
                                   ,'')
                               )        
                      || '&lt;/font&gt;&lt;/div&gt;' "body"
                     ,decode
                        (xxas_emp_menu_pkg.has_access_to_page
                          ('performance-review', xprf.perf_review_form_id)
                        ,'Y'
                        ,'#/performance-review/' || xprf.perf_review_form_id)  "href"
                     ,xxas_util_pkg.get_formatted_name
                         (papf.full_name
                         ,papf.global_name
                         ,papf.local_name
                         ,papf.first_name
                         ,papf.last_name
                         ,papf.known_as) "author"
                     ,XMLElement
                      ("data"
                      ,xxas_perf_review_pkg.get_review_forms_actions
                        (xprf.perf_review_form_id).get_xml()
                      ,XMLElement
                         ("image"
                         ,XMLElement
                            ("self"
                            ,xxas_com_rest_util_pkg.uri_from_path
                              ('people/' || nvl(xprf.person_id, -1) || '/photo'))
                         ,CASE WHEN xprf.person_id IS NOT NULL THEN
                            XMLForest
                              (xxas_per_image_pkg.url(xprf.person_id, img.last_update_date, 'SMALL') "small"
                              ,xxas_per_image_pkg.url(xprf.person_id, img.last_update_date, 'MEDIUM') "medium"
                              ,xxas_per_image_pkg.url(xprf.person_id, img.last_update_date, 'FULL_SIZE') "fullSize"
                              )
                          END
                         )
                     ).extract('/data/*') AS "linked"
                    )
                  ) ORDER BY papf.last_name, nvl(papf.known_as, papf.first_name)
                )
              ) "data"
         FROM  xxas_perf_review_forms xprf
              ,xxas_perf_plan_reviews xppr
              ,xxas_per_thumbnails img
			  ,xxas_perf_plan_enrollments rslt
			  ,per_all_people_f papf
        WHERE  xprf.perf_review_template_id = xppr.perf_review_template_id
        AND    xppr.perf_plan_review_id = {pathParam.perfPlanReviewId}
        AND    xprf.person_id = img.person_id(+)
        AND    rslt.perf_plan_id = xppr.perf_plan_id
        AND    rslt.perf_plan_enrollment_id = xprf.perf_plan_enrollment_id
        AND    rslt.person_id = xprf.person_id
        AND    rslt.assignment_id = xprf.assignment_id
        AND    papf.person_id = rslt.person_id
        AND    trunc(sysdate) BETWEEN
               papf.effective_start_date AND papf.effective_end_date		
		AND    {authFunctionPredicate.XXAS_DT_PERF_ENROLLMENT}
        AND    xprf.assignment_id in
                 (SELECT primary_key_id
                  FROM   TABLE(xxas_com_hier_pkg.org_chart_drs_tbl
                                ('ASG', 'ASG', xxas_util_pkg.get_primary_worker_asg
                                                 ({pathParam.personId},trunc(sysdate)), trunc(sysdate))))

        ]'
    );
    
  xxas_com_sr_pkg.set_rest_method
    (p_service_name       => l_SERVICE
    ,p_method_name        => 'done'
    ,p_description        => 'Done step of review'
    ,p_methods            => 'POST'
    ,p_path               => 'reviewstep/done/{perfReviewFormId}'
    ,p_plsql              =>
      q'[
        DECLARE
        
          l_xml           XMLType;
          l_resp          xxas_http_resp_obj;       
          l_prev_status_code varchar2(10);
          l_prev_step_id number;
          l_current_status_code varchar2(10);
          l_current_step_id number;       
          l_redirect_page varchar2(256) := 'self';
        BEGIN

          IF NOT (xxas_com_sec_pkg.test_instance_rtn_boolean
                   ('XXAS_DT_PERF_REV_PA_DONE', {pathParam.perfReviewFormId})
                 )
          THEN
            
            l_resp := xxas_http_response
                        .status(xxas_http_resp_status_pkg.unauthorised)
                        .build();

          ELSE
            xxas_perf_review_pkg.get_review_form_details({pathParam.perfReviewFormId},l_prev_status_code,l_prev_step_id);
            xxas_perf_review_pkg.handle_done
              ({pathParam.perfReviewFormId},null,{requestParam.activePersonId});
            xxas_perf_review_pkg.get_review_form_details({pathParam.perfReviewFormId},l_current_status_code,l_current_step_id);            
            
            IF NVL({requestParam.redirectOnSubmit},'N') = 'Y' THEN
              l_redirect_page := xxas_perf_util_pkg.get_redirect_page_id({pathParam.perfReviewFormId});
            END IF;
            
            IF l_prev_status_code != l_current_status_code and l_current_status_code = 'A' THEN
            l_resp := xxas_http_response
                        .ok(xxas_com_msg_pkg.get_success_message_xml
                             (xxas_com_msg_type
                           (xxas_util_pkg.app, 'XXAS_PERF_COMPLETION_STEP_MSG', null)
                         ,l_redirect_page)
                           )
                        .build();
            ELSE
            l_resp := xxas_http_response
                        .ok(xxas_com_msg_pkg.get_success_message_xml
                             (xxas_com_msg_type
                           (xxas_util_pkg.app, 'XXAS_PERF_NEXT_STEP_MSG', null)
                         ,l_redirect_page)
                           )
                        .build();
            END IF;   
            
         END IF;
         
         {response} := l_resp;
         
        END
      ]'
    );   
    
  xxas_com_sr_pkg.set_rest_method
    (p_service_name       => l_SERVICE
    ,p_method_name        => 'back'
    ,p_description        => 'Back step of review'
    ,p_methods            => 'POST'
    ,p_path               => 'reviewstep/back/{perfReviewFormId}'
    ,p_plsql              =>
      q'[
        DECLARE
        
          l_xml           XMLType;
          l_resp          xxas_http_resp_obj;
          
        BEGIN

		  IF NOT xxas_com_sec_pkg.test_instance_rtn_boolean
            ('XXAS_DT_PERF_REV_PA_BACK', {pathParam.perfReviewFormId}) THEN
            
            l_resp := xxas_http_response
                        .status(xxas_http_resp_status_pkg.unauthorised)
                        .build();
             
          ELSE
		  
		        xxas_perf_review_pkg.handle_back({pathParam.perfReviewFormId});
			
            l_resp := xxas_http_response
                      .ok(xxas_com_msg_pkg.get_success_message_xml
                           (xxas_com_msg_type
                         (xxas_util_pkg.app, 'XXAS_PERF_BACK_STEP_MSG', null)
                       ,'self')
                         )
                      .build();
         
         {response} := l_resp;
                  
         END IF;

        END
      ]'
    );     

    
  xxas_com_sr_pkg.set_rest_method
    (p_service_name       => l_SERVICE
    ,p_method_name        => 'reassign'
    ,p_description        => 'Review step of review'
    ,p_methods            => 'POST'
    ,p_path               => 'reviewstep/reassign/{perfReviewFormId}'
    ,p_plsql              =>
      q'[
        DECLARE
        
          l_xml           XMLType;
          l_resp          xxas_http_resp_obj;         
        BEGIN

          IF NOT xxas_com_sec_pkg.test_instance_rtn_boolean
            ('XXAS_DT_PERF_REV_PA_REASSIGN', {pathParam.perfReviewFormId}) THEN
            
            l_resp := xxas_http_response
                        .status(xxas_http_resp_status_pkg.unauthorised)
                        .build();
             
          ELSE
            
		    l_resp := xxas_http_response
                        .ok(xxas_com_msg_pkg.get_success_message_xml
                             (xxas_com_msg_type
                               (xxas_util_pkg.app, 'XXELC_PERF_NEXT_STEP_MSG', null)
                            ,'/performance-reassign/' || {pathParam.perfReviewFormId}
						    ,'modal')
                           )
                        .build();
            
            {response} := l_resp;
                  
         END IF;

        END
      ]'
    );  
	
    xxas_com_sr_pkg.set_rest_method
    (p_service_name       => l_SERVICE
    ,p_method_name        => 'submit'
    ,p_description        => 'Submit'
    ,p_methods            => 'POST'
    ,p_path               => 'reviewstep/submit/{perfReviewFormId}'
    ,p_plsql              =>
      q'[
        DECLARE
        
          l_xml           XMLType;
          l_resp          xxas_http_resp_obj;
          l_prev_status_code varchar2(10);
          l_prev_step_id number;
          l_current_status_code varchar2(10);
          l_current_step_id number;
          l_redirect_page varchar2(256) := 'self';
          
        BEGIN

          IF NOT (xxas_com_sec_pkg.test_instance_rtn_boolean
                   ('XXAS_DT_PERF_REV_PA_SUBMIT', {pathParam.perfReviewFormId})
                 )
          THEN
            
            l_resp := xxas_http_response
                        .status(xxas_http_resp_status_pkg.unauthorised)
                        .build();
             
          ELSE  
            xxas_perf_review_pkg.get_review_form_details({pathParam.perfReviewFormId},l_prev_status_code,l_prev_step_id);
            xxas_perf_review_pkg.handle_submit
              ({pathParam.perfReviewFormId},null,{requestParam.activePersonId});
            xxas_perf_review_pkg.get_review_form_details({pathParam.perfReviewFormId},l_current_status_code,l_current_step_id);
            
            IF NVL({requestParam.redirectOnSubmit},'N') = 'Y' THEN            
              l_redirect_page := xxas_perf_util_pkg.get_redirect_page_id({pathParam.perfReviewFormId});
            END IF;
            
            IF l_prev_status_code != l_current_status_code and l_current_status_code = 'A' THEN
            l_resp := xxas_http_response
                        .ok(xxas_com_msg_pkg.get_success_message_xml
                             (xxas_com_msg_type
                           (xxas_util_pkg.app, 'XXAS_PERF_COMPLETION_STEP_MSG', null)
                         ,l_redirect_page)
                           )
                        .build();
            ELSE
            l_resp := xxas_http_response
                        .ok(xxas_com_msg_pkg.get_success_message_xml
                             (xxas_com_msg_type
                           (xxas_util_pkg.app, 'XXAS_PERF_NEXT_STEP_MSG', null)
                         ,l_redirect_page)
                           )
                        .build();
            END IF;            
         END IF;
         
         {response} := l_resp;
         
        END
      ]'
    );
    
    xxas_com_sr_pkg.set_rest_method
    (p_service_name       => l_SERVICE
    ,p_method_name        => 'sign-off'
    ,p_description        => 'sign-off'
    ,p_methods            => 'POST'
    ,p_path               => 'reviewstep/sign-off'
    ,p_plsql              =>
      q'[
        DECLARE
          l_request_xml   XMLType;
          l_resp          xxas_http_resp_obj;
        BEGIN

          l_request_xml := {requestBody};
          
          xxas_perf_review_pkg.signoff_pdp(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_PERF_REVIEW_SIGNED_OFF', null)
                         ,'self')
                           )
                        .build();
         
         {response} := l_resp;
         
        END
      ]'
    );
    
	xxas_com_sr_pkg.set_rest_method
    (p_service_name       => l_SERVICE
    ,p_method_name        => 'participants'
    ,p_description        => 'Get participants of a step'
    ,p_methods            => 'GET'
    ,p_path               => 'reviewstep/participants/{perfReviewStepId}'
	,p_remove_root_ele    => true
    ,p_sql                =>
      q'[
        SELECT XMLElement
                 ("data"
                 ,XMLAgg
                   (XMLElement
                     ("participants"
                     ,XMLForest
                       (p.name as "id"
                       ,p.value as "label"
                       ,p.value as "value"
                       )
                     )
                   )
                 )
        FROM  TABLE(xxas_perf_review_pkg.get_step_participants({pathParam.perfReviewStepId})) p
      ]'
    ); 
    
  xxas_com_sr_pkg.set_rest_method
    (p_service_name       => l_SERVICE
    ,p_method_name        => 'feedbackproviders'
    ,p_description        => 'Get feedbackproviders for the review'
    ,p_methods            => 'GET'
    ,p_path               => 'reviewstep/fbproviders/{perfReviewFormId}'
	,p_remove_root_ele    => true
    ,p_sql                =>
      q'[
       SELECT XMLElement("data"
                        ,XMLAgg(XMLElement("item"
                                          ,XMLForest(xgv."type"     as "type"
                                                    ,xgv."id"       as "id"
                                                    ,xgv."author"   as "author"
                                                    ,xgv."pageId"   as "pageId"
                                                    ,xgv."objectId" as "objectId"
                                                    ,xgv."body"     as "body"
                                                    ,xgv."linked"   as "linked"
                                                    )
                                          )
                               )
                        )
              FROM   xxas_grantees_v xgv
                    ,xxas_perf_review_forms xprf
              WHERE  xprf.perf_review_form_id = 1
              AND    xprf.person_id = xgv.granter_person_id
              AND    trunc(xprf.start_date) BETWEEN xgv.from_date AND xgv.to_date
              AND    xprf.current_step_person_id = fnd_global.employee_id
              AND    xgv.relationship_code = 'FBP'
      ]'
    ); 
    
  xxas_com_sr_pkg.set_rest_method
    (p_service_name       => l_SERVICE
    ,p_method_name        => 'currentstep'
    ,p_description        => 'Get current step details'
    ,p_methods            => 'GET'
    ,p_path               => 'reviewstep/currentstep/{perfReviewFormId}'
	--,p_remove_root_ele    => true
    ,p_sql                =>
      q'[
        SELECT XMLAgg
                 (XMLElement
                    ("item"
                    ,XMLFOREST
                       (xprf.current_step_id                               "id"
                        ,'people'                                          "type"
                        ,CASE WHEN xprf.status_code NOT IN ('A', 'X') AND
                                   xprf.current_step_id = xprs.perf_review_step_id
                              THEN 'positive'
                         END "color"
                        ,xxas_com_msg_pkg.get_string(xprts.title_message)  "name"
                        ,CASE WHEN part.participants = 1 THEN
                           (SELECT xxas_util_pkg.get_formatted_name
                                    (papf.full_name
                                    ,papf.global_name
                                    ,papf.local_name
                                    ,papf.first_name
                                    ,papf.last_name
                                    ,papf.known_as)
                            FROM   per_all_people_f papf
                            WHERE  papf.person_id = part.person_id
                            AND    trunc(sysdate)
                                   BETWEEN papf.effective_start_date AND papf.effective_end_date
                            )
                         WHEN part.participants > 1 THEN
                            part.participants || ' '||xxas_com_msg_pkg.get_string('XXAS_PERF_PARTICIPANTS')
                         ELSE 
                           xxas_com_msg_pkg.get_string('XXAS_PERF_NA')
                         END "title"
						 ,CASE WHEN nvl({requestParam.showActivity},'true') = 'true' THEN
                  CASE WHEN xprs.status_code='A' THEN
															xxas_com_msg_pkg.get_string('XXAS_PERF_REV_COMPLETE')||' '|| xxas_util_pkg.date_in_short(xxas_perf_review_pkg.review_step_due_date
                                                                                  (xprts.perf_review_templ_step_id
                                                                                  ,xprf.start_date)
                                                                                  ) 
												ELSE
														decode(nvl({requestParam.showDueDate},'true'),'true',
																		xxas_com_msg_pkg.get_string('XXAS_PERF_GOAL_DUE_DATE')||' '|| xxas_util_pkg.date_in_short
																		(xxas_perf_review_pkg.review_step_due_date
																		(xprts.perf_review_templ_step_id
																		,xprf.start_date)
																		) 
																 ,' ')
						      END  
						 END		 "overline"
                       )
                    ,xxas_perf_review_pkg.get_participant_label_xml({pathParam.perfReviewFormId},xprs.perf_review_step_id,xprs.status_code
                                    ,xprf.current_step_id,xprf.current_step_person_id,xprts.title_message,part.participants,part.person_id) 
                    ,decode(xxas_com_avail_pkg.get_free_busy(part.assignment_id)
                           ,'U'
                           , null
                           ,XMLElement
                              ("availability"
                               ,XMLForest
                                  (xxas_com_avail_pkg.get_free_busy(part.assignment_id) "freeBusy"
                                  ,xxas_com_avail_pkg.get_color(part.assignment_id) "color"
                                  ,xxas_com_avail_pkg.get_availability(part.assignment_id) "title"
                                  ,xxas_com_avail_pkg.get_description(part.assignment_id) "description"
                                  ,xxas_com_avail_pkg.get_update_datetime(part.assignment_id) "lastUpdated"
                                  ,xxas_com_avail_pkg.get_icon(part.assignment_id) "icon"
                                  )
                              )
                           )
                    ,XMLElement
                       ("linked"
                       ,XMLElement
                         ("image"
                         ,nvl2(part.person_id
                              ,extract(xxas_com_rest_mgr_pkg.get_xml_entity
                                         ('/people/' || decode
                                                         (part.participants
                                                         ,1
                                                         ,part.person_id
                                                         ,-1) || '/photo'
                                          ,null
                                          ,xxas_com_srv_param_varray
                                             (xxas_com_srv_param_value('includeEdits', 'false'))
                                         ).deletexml('//initials'),'/data/*')
                              ,null)
                         ,XMLElement
                           ("initials"
                           ,CASE WHEN part.participants = 1 THEN
                               (SELECT xxas_util_pkg.get_initials
                                         (papf.first_name
                                         ,papf.last_name
                                         ,papf.known_as)
                                FROM   per_all_people_f papf
                                WHERE  papf.person_id = part.person_id
                                AND    trunc(sysdate)
                                       BETWEEN papf.effective_start_date AND papf.effective_end_date
                                )
                             WHEN part.participants > 1 THEN
                                'x' || part.participants
                             ELSE 
                               null 
                             END
                           )
                         )
                        )
                    ) ORDER BY xprts.sequence
                 ) "data"
        FROM   xxas_perf_review_forms xprf
              ,xxas_perf_review_steps xprs
              ,xxas_perf_review_templ_steps xprts
              ,(SELECT count(1) participants
                       ,max(b.reviewer_person_id) person_id
                       ,xxas_com_asg_pkg.get_primary_work_asg
                         (max(b.reviewer_person_id), trunc(sysdate)) assignment_id
                       ,perf_review_step_id
                FROM   xxas_perf_review_participants b
                GROUP BY b.perf_review_step_id
               ) part
        WHERE xprf.perf_review_form_id = {pathParam.perfReviewFormId}
        AND   xprf.perf_review_form_id = xprs.perf_review_form_id
        AND   xprs.perf_review_templ_step_id = xprts.perf_review_templ_step_id
        AND   part.perf_review_step_id (+) = xprs.perf_review_step_id
      ]'
    ); 
    
  xxas_com_sr_pkg.set_rest_method
      (p_service_name       => l_SERVICE
      ,p_method_name        => 'CorrectOwnership'
      ,p_description        => 'Correct or transfer ownership'
      ,p_methods            => 'POST'
      ,p_path               => 'reviewstep/transfer/{perfReviewFormId}'
      ,p_plsql              =>
        q'[
          DECLARE
          
            l_xml           XMLType;
            l_resp          xxas_http_resp_obj;
            
          BEGIN

           IF NOT xxas_com_sec_pkg.test_instance_rtn_boolean
              ('XXAS_DT_PERF_REV_TRANSFER', {pathParam.perfReviewFormId}) THEN
              
              l_resp := xxas_http_response
                          .status(xxas_http_resp_status_pkg.unauthorised)
                          .build();
               
            ELSE
              xxas_perf_review_pkg.handle_transfer
                ({pathParam.perfReviewFormId}
                ,{requestParam.copyOnTransfer});
                
              l_resp := xxas_http_response
                        .ok(xxas_com_msg_pkg.get_success_message_xml
                             (xxas_com_msg_type
                           (xxas_util_pkg.app, 'XXAS_COM_CHNG_SAVE', null)
                         ,'self')
                           )
                        .build();
                        
           {response} := l_resp;                        
                   
           END IF;

          END
        ]'
      );     
      
    xxas_com_sr_pkg.set_rest_method
      (p_service_name       => l_SERVICE
      ,p_method_name        => 'PushBackParticipant'
      ,p_description        => 'Push back a participant'
      ,p_methods            => 'POST'
      ,p_path               => 'reviewstep/pushback/{perfReviewFormId}/by-participant/{perfReviewerId}'
      ,p_plsql              =>
        q'[
          DECLARE
          
            l_xml           XMLType;
            l_resp          xxas_http_resp_obj;
            
          BEGIN

           IF NOT xxas_com_sec_pkg.test_instance_rtn_boolean
              ('XXAS_PERF_REV_PUSHBACK', {pathParam.perfReviewFormId}) THEN
              
              l_resp := xxas_http_response
                          .status(xxas_http_resp_status_pkg.unauthorised)
                          .build();
               
            ELSE
              xxas_perf_review_pkg.handle_pushback
                ({pathParam.perfReviewFormId}
                ,{pathParam.perfReviewerId});
                
              l_resp := xxas_http_response
                        .ok(xxas_com_msg_pkg.get_success_message_xml
                             (xxas_com_msg_type
                           (xxas_util_pkg.app, 'XXAS_COM_CHNG_SAVE', null)
                         ,'self')
                           )
                        .build();
                        
           {response} := l_resp;                        
                   
           END IF;

          END
        ]'
      );

	xxas_com_sr_pkg.set_rest_method
    (p_service_name       => l_SERVICE
    ,p_method_name        => 'currently-with'
    ,p_description        => 'Get list of current participants'
    ,p_methods            => 'GET'
    ,p_path               => 'performance-review/currently-with/{perfReviewFormId}'
	  ,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
                          ("participants"
                           ,XMLForest
                             ('note' "type"
                              ,xprp.reviewer_person_id "id"
                              ,CASE WHEN fnd_global.employee_id = xprp.reviewer_person_id 
                                    THEN xxas_com_msg_pkg.get_string('XXAS_PERF_YOU')
                                    ELSE 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)
                               END "author")
                            ,XMLElement
                               ("labels"
                                ,XMLAttributes
                                   ('true' as "json:force-array"
                                    ,'http://json.org/' as "xmlns:json")
                                ,XMLElement
                                  ("item"
                                  ,XMLForest
                                    (xpp.participant as  "title"
                                     ,CASE WHEN xprp.status_code = 'A' THEN 'balanced' -- green
                                           WHEN xprp.status_code = 'D'  THEN 'energized' --orange
                                          WHEN xprp.status_code = 'NS'  THEN 'assertive' -- red
                                          ELSE 'positive' --blue
                                      END as "color")) 
                                  ,XMLElement
                                    ("item"
                                     ,XMLForest
                                       (xxas_util_pkg.decode_lookup('XXAS_PERF_REVIEW_PER_STATUSES', xprp.status_code) as  "title"
                                        ,CASE WHEN xprp.status_code = 'A' THEN 'balanced' -- green
                                              WHEN xprp.status_code = 'D'  THEN 'energized' --orange
                                             WHEN xprp.status_code = 'NS'  THEN 'assertive' -- red
                                             ELSE 'positive' --blue
                                         END as "color"))
                                  ,CASE WHEN NVL({requestParam.activePersonId},-1) = xprp.reviewer_person_id 
                                        THEN XMLElement
                                               ("item"
                                               ,XMLFOREST
                                                  (xxas_com_msg_pkg.get_string('XXAS_PERF_CURR_ACTIVE') as  "title"
                                                  ,'positive' as "color"))
                                   END)
                                 ,XMLElement
                                    ("linked"
                                    ,xxas_perf_participants_pkg.currently_with_actions
                                       (xprf.perf_review_form_id
                                       ,xprp.reviewer_person_id
                                       ,{requestParam.activePersonId}).get_xml()
                                    ,XMLElement
                                       ("image"
                                       ,XMLElement
                                          ("self"
                                          ,xxas_com_rest_util_pkg.uri_from_path
                                            ('people/' || nvl(xprp.reviewer_person_id, -1) || '/photo'))
                                       ,CASE WHEN xprp.reviewer_person_id IS NOT NULL THEN
                                          XMLForest
                                            (xxas_per_image_pkg.url(xprp.reviewer_person_id, t.last_update_date, 'SMALL') "small"
                                            ,xxas_per_image_pkg.url(xprp.reviewer_person_id, t.last_update_date, 'MEDIUM') "medium"
                                            ,xxas_per_image_pkg.url(xprp.reviewer_person_id, t.last_update_date, 'FULL_SIZE') "fullSize"
                                            )
                                        END))
                                 )
                               )
                             ) "data"
             FROM xxas_perf_review_participants xprp,
                  xxas_perf_participants_v xpp,
                  xxas_per_thumbnails t,
                  per_all_people_f papf,
                  xxas_perf_review_forms xprf
            WHERE  xprf.current_step_id = xprp.perf_review_step_id
              AND xpp.perf_participant_id = xprp.perf_participant_id
              AND xprp.reviewer_person_id = t.person_id (+)
              AND  xprp.reviewer_person_id = papf.person_id
              AND trunc ( sysdate )
                  BETWEEN papf.effective_start_date
                      AND papf.effective_end_date
               and xprf.perf_review_form_id = {pathParam.perfReviewFormId}
      ]'
    ); 
          
END;

/
COMMIT;
EXIT;