/* $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
                       := 'TransactionStatus';
                       
BEGIN
      
  xxas_com_sr_pkg.set_rest_service
    (p_service_name       => l_SERVICE
    ,p_description        => 'Manage transactons' 
    ,p_path               => 'transaction-status'    
    ,p_consumes           => 'application/xml'
    ,p_produces           => 'application/xml'
    );
    
   
   xxas_com_sr_pkg.set_rest_method
    (p_service_name       => l_SERVICE
    ,p_method_name        => 'Manage transactons'
    ,p_description        => 'Manage transactons'
    ,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"
								    ,'people'                                                                       AS "type"
                                    ,  xxas_util_pkg.get_formatted_name (papf.full_name 
									                                    ,papf.global_name
																		,papf.local_name 
																		,papf.first_name 
									                                    ,papf.last_name 
																		,papf.known_as) 
                                    || ' - ' || hr_sflutil_ss.GETSUBJECT(rslt.transaction_id, NULL)                      AS "name"
									
                                    ,'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
                                     )                                                                                 AS "title"
                                    , 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"
                                    ,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"
									,'modal'  AS "target"
                                    ,XMLElement
                                      ("data"
                                       ,XMLElement
                                       ("image"
                                       ,XMLElement
                                       ("self"
                                       ,xxas_com_rest_util_pkg.uri_from_path
                                       ('people/' || nvl(papf.person_id, -1) || '/photo'))
                                       ,CASE WHEN papf.person_id IS NOT NULL THEN
                                       XMLForest
                                        (xxas_util_pkg.get_initials(papf.first_name, papf.last_name, papf.known_as)  "initials"
                                          ,xxas_per_image_pkg.url(img.person_id, img.last_update_date, 'SMALL') "small"
                                          ,xxas_per_image_pkg.url(img.person_id,img.last_update_date, 'MEDIUM') "medium"
                                          ,xxas_per_image_pkg.url(img.person_id, img.last_update_date, 'FULL_SIZE') "fullSize"
                                        )
                                        END
                                      )
                                      ).extract('/data/*')                                                          AS "linked"

                                  ) ) ORDER  BY rslt.creation_date DESC
           ) ) "data"
      
                  FROM (select   hat.creation_date
				               , hat.transaction_id
							   , hat.creator_person_id person_id
							   ,hat.selected_person_id
							   ,transaction_ref_table
							   ,hts.transaction_step_id	
                                ,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    (  
                   ({requestParam.savedSearchId} IS NULL
                     AND 'Y' = xxas_com_hier_pkg.is_per_report_of_person(fnd_global.employee_id, rslt.person_id) 
                   )
                 OR
                   ({requestParam.savedSearchId} IS NOT NULL
                                 AND
                    EXISTS (SELECT 1 
                            FROM   TABLE(xxas_per_search_pkg.people_tbl
                                           (null
                                           ,( SELECT xxas_saved_search_pkg.xml_to_obj(ss.search_parameters)
                                              FROM   xxas_saved_searches_v ss
                                              WHERE  ss.saved_search_id = {requestParam.savedSearchId}
                                              AND    xxas_com_sec_pkg.test_instance
                                                       ('XXAS_DATA_SAVED_SEARCH_VIEW', {requestParam.savedSearchId}) = 'Y'
                                            )
                                           )
			                            ) rslts
                            WHERE  rslt.person_id IN (SELECT reportees.column_value
                                                      FROM   TABLE(xxas_com_hier_pkg.drs_for_per_tbl(rslts.person_id,SYSDATE)) reportees
                                                     )
                          )
                   )
                 )			
						
     ]'
    );     
    
  

    
END;
/
COMMIT;
EXIT;
