/* $Id$ */
/*============================================================================*
 |                 Copyright (c) 2016 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
                       := 'Worklistuseraccess';
                       
BEGIN
      
  xxas_com_sr_pkg.set_rest_service
    (p_service_name       => l_SERVICE
    ,p_description        => 'Interact with workflow notifications'
    ,p_path               => 'workflow-user-access'
    ,p_consumes           => 'application/xml'
    ,p_produces           => 'application/xml'
    );
    
  xxas_com_sr_pkg.set_rest_method
    (p_service_name       => l_SERVICE
    ,p_method_name        => 'wl user access'
    ,p_description        => 'Get workflow user access'
	,p_path               => '{personId}'
    ,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
                                  (
                                    g.grant_guid
                                 , g.grant_guid                                                             AS "id"
                                 , wf_directory.GetRoleDisplayName(nvl(g.grantee_key,fnd_global.user_name))  AS "sender"
                                 ,'worklist'                                                                 AS "type"
                             /*   ,case parameter2
                                      when null THEN xxas_com_msg_pkg.get_string('XXAS_WL_ALL_TYPES_NOTIFICATION')  
                                       else xxas_com_worklist_pkg.item_type_to_name(g.parameter2)
                                       end            AS "subject" */
                              ,nvl2(parameter2, xxas_com_worklist_pkg.item_type_to_name_wl(parameter2) ,xxas_com_msg_pkg.get_string('XXAS_WL_ALL_TYPES_NOTIFICATION')
                              )      AS "subject" --XXAS_WL_ALL_TYPES_NOTIFICATION
                                 ,XMLElement
                                  ("data"
                                 ,xxas_com_worklist_pkg.actions_delegate_access(g.grant_guid)
                                  , XMLElement
                                   ("image"
                                   ,XMLElement
                                   ("self"
                                   ,xxas_com_rest_util_pkg.uri_from_path
                                  ('people/' || nvl(wf.person_id, -1) || '/photo'))
                                  ,CASE WHEN wf.person_id IS NOT NULL THEN
                                   XMLForest
                                      (xxas_per_image_pkg.url(wf.person_id, wf.last_update_date, 'SMALL') "small"
                                       ,xxas_per_image_pkg.url(wf.person_id, wf.last_update_date, 'MEDIUM') "medium"
                                       ,xxas_per_image_pkg.url(wf.person_id, wf.last_update_date, 'FULL_SIZE') "fullSize"
                      )
                  END
                 )
      
         ).extract('/data/*')                                                           AS "linked"

                            ) 
           /**/   ,XMLElement
                   ("labels"
	                   ,XMLAttributes
                     ('true' as "json:force-array"
                      ,'http://json.org/' as "xmlns:json")
	                    ,CASE  WHEN g.start_date > trunc(sysdate) + 1  THEN
                        XMLElement ("data"
                                   , XMLForest
                                  ( xxas_com_msg_pkg.get_message('XXAS','XXAS_WL_STARTS_IN_DAYS','COUNT',to_char(trunc(g.start_date) - trunc(sysdate)))  "title"
                                    ,'energized' "color")) 
                        when g.start_date  >= trunc(sysdate) + 1 THEN
                          XMLElement ("data"
                                 , XMLForest
                          ( xxas_com_msg_pkg.get_message('XXAS','XXAS_WL_STARTS_IN_DAY','COUNT',to_char(trunc(g.start_date) - trunc(sysdate)))  "title"
                             ,'energized' "color"))          
                      WHEN trunc(nvl(g.end_date,sysdate)) >= trunc(sysdate)  THEN
                        XMLElement ("data"
                                   , XMLForest
                                    ( xxas_com_msg_pkg.get_string ('XXAS_WL_ACTIVE') "title"   --active
                                    ,'balanced' "color"))
                        WHEN g.end_date &lt; trunc(sysdate)  THEN
                        XMLElement ("data"
                                   , XMLForest
                                    ( xxas_com_msg_pkg.get_string('XXAS_WL_EXPIRED') "title"     
                                    ,xxas_rslt_cache_pkg.resolve_color_name('grey') "color"))
                        
                       END
                         ) "labels"
     ) order by g.end_date desc 
   ) 
  ) "data"
FROM    fnd_grants g 
      ,(SELECT photo.person_id
              ,usr.user_name
              ,photo.last_update_date
        FROM   fnd_user usr
              ,xxas_per_thumbnails photo
        WHERE  photo.person_id = usr.employee_id
		and    usr.user_name = fnd_global.user_name
       )wf
  WHERE MENU_ID = (SELECT fm.menu_id FROM fnd_menus fm WHERE fm.menu_name = 'FND_WF_WORKLIST')
  AND OBJECT_ID in (SELECT fo.object_id  FROM fnd_objects fo  WHERE fo.database_object_name = 'WF_NOTIFICATIONS')
  AND INSTANCE_SET_ID = (SELECT fis.instance_set_id  FROM FND_OBJECT_INSTANCE_SETS fis WHERE fis.instance_set_name = 'WL_PROXY_ACCESS')
  AND PARAMETER1              = (select user_name 
                                 from fnd_user 
								 where employee_id= {pathParam.personId}
								 and user_name = fnd_global.user_name
								 )
  AND INSTANCE_TYPE            = 'SET'
 AND nvl(g.grantee_key,fnd_global.user_name) = wf.user_name(+)

 ]'


     );
	
	
	xxas_com_sr_pkg.set_rest_method
    (p_service_name       => l_SERVICE
    ,p_method_name        => 'Respond to user access'
    ,p_description        => 'Respond to user access'
    ,p_path               => '{action}'
    ,p_methods            => 'DELETE'
    ,p_plsql              =>
      q'[
         DECLARE
        
          l_xml         XMLType;
          l_resp        xxas_http_resp_obj;
          l_request_xml XMLType;
          
        BEGIN
        
          l_request_xml := {requestBody};

          xxas_com_worklist_pkg.bulk_respond_delegate_access
            (p_action       => {pathParam.action}
            ,p_request_body => 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
                               ,'All changes saved', null)
                               ,'self','modal')
                         )
                      .build();
         
         {response} := l_resp;

        END
      ]'
    );
	
	 xxas_com_sr_pkg.set_rest_method
    (p_service_name       => l_SERVICE
    ,p_method_name        => 'Delete user access grant'
    ,p_description        => 'Delete user access grant'
    ,p_path               => 'DELETE/{grantGuid}'
    ,p_methods            => 'DELETE'
    ,p_plsql              =>
      q'[
		
				 DECLARE
          
          l_xml                 XMLType;
          l_resp                xxas_http_resp_obj;
          
        BEGIN
                  
          xxas_com_worklist_pkg.remove_grant
            (p_grant_guid              => {pathParam.grantGuid}
             ,p_response_xml         => l_xml
            );
          
         l_resp := xxas_http_response
                      .ok(l_xml)
                      .build();
                      
         {response} := l_resp;

        END
      ]'
    );
    
END;
/
COMMIT;
EXIT;