/* $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
                       := 'Worklistvacrules';
                       
BEGIN
      
  xxas_com_sr_pkg.set_rest_service
    (p_service_name       => l_SERVICE
    ,p_description        => 'Interact with workflow notifications'
    ,p_path               => 'workflow-vac-rules/{personId}'
    ,p_consumes           => 'application/xml'
    ,p_produces           => 'application/xml'
    );
    
  xxas_com_sr_pkg.set_rest_method
    (p_service_name       => l_SERVICE
    ,p_method_name        => 'Getvacation rules'
    ,p_description        => 'Get workflow vac rules'
    ,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
                                  (
        wrr.rule_id
       ,wrr.rule_id                                                                       AS "id"
       ,wrr.role
       ,case wrr.action
        when 'NOOP' then
          wf_directory.GetRoleDisplayName(wrr.role)
        when 'RESPOND' then
          wf_directory.GetRoleDisplayName(wrr.role)
        else
          wf_directory.GetRoleDisplayName(nvl(wrr.action_argument,wrr.role))
        end                                                                               AS "sender"
       ,'worklist'                                                                        AS "type"
       ,case action
  when 'NOOP' THEN xxas_com_msg_pkg.get_string('XXAS_WL_DELIVER_TO_ME')
  when 'RESPOND' Then xxas_com_msg_pkg.get_string('XXAS_WL_CLOSE')
  when 'FORWARD' then xxas_com_msg_pkg.get_string('XXAS_WL_DELEGATE')
  when 'TRANSFER' then xxas_com_msg_pkg.get_string('XXAS_WL_TRANSFER')
  else initcap(action)
 end  || ': '|| nvl2(xxas_com_worklist_pkg.item_type_to_name(wrr.message_type),xxas_com_worklist_pkg.item_type_to_name(wrr.message_type)||' - ', '')
            || nvl2(wm.display_name,   wm.display_name  ,xxas_com_msg_pkg.get_string('XXAS_WL_ALL_NOTIFICATION'))           AS "subject"              
       ,xxas_com_rest_util_pkg.uri_from_app_route('vacation-rule/' || wrr.rule_id)        AS "href"
       ,'modal'                                                                           AS "target"
       ,XMLElement
         ("data"
         ,xxas_com_worklist_pkg.actions_vac_rules(wrr.rule_id)
         , 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"
       /*,xxas_com_worklist_pkg.actions_vac_rules(wrr.rule_id
                                               ,decode(wrr.action
                                                      ,'NOOP',fnd_global.user_name
                                                      ,'RESPOND',fnd_global.user_name
                                                      ,nvl(wrr.action_argument
                                                          ,fnd_global.user_name)
                                                      ))                                  AS "linked"*/
) 
,XMLElement
                   ("labels"
	                   ,XMLAttributes
                     ('true' as "json:force-array"
                      ,'http://json.org/' as "xmlns:json")
	                    ,CASE  WHEN wrr.begin_date > sysdate  THEN
                        XMLElement ("data"
                                   , XMLForest
                                    ( xxas_com_msg_pkg.get_string('XXAS_WL_STARTS_IN')||to_char(trunc(wrr.begin_date) - trunc(sysdate))|| xxas_com_msg_pkg.get_string('XXAS_WL_DAYS') "title"
                                    ,'energized' "color")) 
                             WHEN nvl(wrr.end_date,sysdate) >= sysdate THEN
                        XMLElement ("data"
                                   , XMLForest
                                    ( 'Active' "title"
                                    ,'balanced' "color"))
                        WHEN wrr.end_date &lt; 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 wrr.end_date desc,wrr.begin_date desc
 ) ) "data"
FROM   wf_routing_rules wrr
        ,WF_MESSAGES_VL wm
      ,(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
       )wf
WHERE  nvl(wrr.action_argument,wrr.role) = wf.user_name(+)
AND    wrr.MESSAGE_TYPE = wm.TYPE (+)
AND    wrr.MESSAGE_NAME = wm.NAME (+)
AND    wrr.role IN (SELECT user_name 
                   FROM   fnd_user 
                   WHERE employee_id= {pathParam.personId}
                   AND   trunc(SYSDATE) BETWEEN start_date AND nvl(end_date,SYSDATE)
                   AND   (employee_id != fnd_global.employee_id
                            OR 
                          user_id = fnd_global.user_id
                         )
                   )

 ]'
    );
END;
/
COMMIT;
EXIT;