/* $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
                       := 'Emppendingactions';
                       
BEGIN
      
  xxas_com_sr_pkg.set_rest_service
    (p_service_name       => l_SERVICE
    ,p_description        => 'Pending actions' 
    ,p_path               => 'emp-pending-actions/{person_id}'    
    ,p_consumes           => 'application/xml'
    ,p_produces           => 'application/xml'
    );
    
   
   xxas_com_sr_pkg.set_rest_method
    (p_service_name       => l_SERVICE
    ,p_method_name        => 'Pending actions'
    ,p_description        => 'Pending actions'
    ,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
                                ("item"
                                ,XMLFOREST
                                  (
                                     transaction_id                                                                   AS "id"
                                    ,'flexible'                                                                       AS "type"
                                    , hr_sflutil_ss.GETSUBJECT(rslt.transaction_id, NULL)                      AS "line1"
									
                                    ,'Requested on '||to_char(rslt.creation_date)
                                       ||' by '||
                                    (SELECT DECODE(fnd_profile.value('HR_LOCAL_OR_GLOBAL_NAME_FORMAT'),'G',p.global_name,p.local_name)
                                            FROM per_all_people_f p
                                            WHERE p.person_id=(DECODE(rslt.TRANSACTION_REF_TABLE,'PER_APPRAISALS'
                                                               , hr_workflow_ss.getApprStartingPointPersonId(rslt.transaction_id)
															                                               , rslt.person_id))
                                             AND sysdate BETWEEN p.EFFECTIVE_START_DATE AND p.EFFECTIVE_END_DATE
                                     )  || (select xxas_mss_util_pkg.get_pa_status_color(rslt.transaction_id) from dual) AS "line3"
                                    , xxas_util_pkg.get_formatted_name (papf.full_name
   									                                   ,papf.global_name 
																	   ,papf.local_name
																	   ,papf.first_name 
																	   ,papf.last_name 
																	   ,papf.known_as)                                  "requested_for"
                                    , (SELECT DECODE(fnd_profile.value('HR_LOCAL_OR_GLOBAL_NAME_FORMAT'),'G',p.global_name,p.local_name)
                                              FROM per_all_people_f p
                                               WHERE p.person_id=(DECODE(rslt.TRANSACTION_REF_TABLE,'PER_APPRAISALS'
                                               , hr_workflow_ss.getApprStartingPointPersonId(rslt.transaction_id), rslt.person_id))
                                               AND sysdate BETWEEN p.EFFECTIVE_START_DATE AND p.EFFECTIVE_END_DATE
                                      )                                                                         "requested_by" 
                                    ,xxas_selfservice_workflow_pkg.get_status_meaning(transaction_id) "status"
									,xxas_mss_util_pkg.get_pa_icon_color(rslt.transaction_id) AS "statusColor"
                                    ,hr_sflutil_ss.GETSUBJECT(rslt.transaction_id, NULL)             "action_type"
                                    ,rslt.creation_date  
                                    ,xxas_mss_util_pkg.date_to_age(rslt.creation_date) "notification_age"
                                     ,xxas_mss_util_pkg.get_page_href(null,rslt.item_key)  AS "href"
									 ,xxas_mss_util_pkg.get_page_icon(rslt.item_key)  AS "icon"
									,'modal'  AS "target"

                                  ) ) ORDER  BY rslt.creation_date DESC
           ) ) "data"
      
                  FROM (select  distinct hat.creation_date
				               ,hat.transaction_id
							   ,hat.creator_person_id person_id
							   ,hat.selected_person_id
							   ,transaction_ref_table
							   ,hat.item_key
                          FROM hr_api_transactions hat 
						  ,hr_api_transaction_steps hts
                          WHERE hat.status NOT IN ('D', 'N', 'W', 'ACTIVE', 'AC')
						  AND hts.transaction_id = hat.transaction_id
						 
					   ) rslt
					  , per_all_people_f papf 
					  ,xxas_per_thumbnails img
				WHERE   papf.person_id = img.person_id(+)
                    AND papf.person_id  = rslt.selected_person_id
                    AND TRUNC(sysdate) BETWEEN papf.effective_start_date AND papf.effective_end_date	
                    AND rslt.selected_person_id = {pathParam.person_id}
                    AND {authFunctionPredicate.XXAS_EMP_PENDING_ACTIONS_VIEW}					
			       
               
						
     ]'
    );     
    
  

    
END;
/
COMMIT;
EXIT;
