/* $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
                       := 'WorkflowNotifications';
                       
BEGIN
      
  xxas_com_sr_pkg.set_rest_service
    (p_service_name       => l_SERVICE
    ,p_description        => 'Interact with workflow notifications'
    ,p_path               => 'workflow-notifications'
    ,p_consumes           => 'application/xml'
    ,p_produces           => 'application/xml'
    );
  
xxas_com_sr_pkg.set_rest_method
    (p_service_name       => l_SERVICE
    ,p_method_name        => 'GetNotifications'
    ,p_description        => 'Get workflow notifications'
    ,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
                                  (
        notification_id                                                                   AS "id"
       ,wf.notification_id
       ,wf_directory.GetRoleDisplayName(wf.recipient_role)   to_user
       ,'worklist'                                                                        AS "type"
       ,wf.from_user                                                                      AS "sender"
              ,decode(wf.more_info_role
              ,null, wf.subject
              ,fnd_message.get_string('FND','FND_MORE_INFO_REQUESTED')
                   || ' ' ||wf.subject)                                                   AS "subject"
       ,xxas_util_pkg.date_to_age(wf.begin_date)                                          AS "dateSentFormatted"
              ,xxas_com_rest_util_pkg.uri_from_app_route
          ('notification/' || wf.notification_id || '')  AS "href"
  ,'modal'                                                                           AS "target"
  ,{requestParam.modalWidth} as "modalWidth"
       ,XMLElement
         ("data"
         ,xxas_com_worklist_pkg.get_actions(notification_id, wf.from_role,{requestParam.responseFormula}).get_xml()
         ,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_util_pkg.get_initials(wf.first_name, wf.last_name, wf.known_as) "initials"
                      ,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"
       ,wf.begin_date
       ,case when (trunc(wf.due_date) - trunc(sysdate)) between 0 and 3 then
         'timer'
        else  null end                                                                    AS "icon"
       ,case when (trunc(wf.due_date) - trunc(sysdate)) between 0 and 3 then
         'assertive'
        else  null end                                                                    AS "iconColor"
       ,case when (trunc(wf.due_date) - trunc(sysdate)) between 0 and 3 then
         'This is due ' || xxas_com_worklist_pkg.date_to_phrase(wf.due_date)
        end                                                                               AS "status"
       ,nvl(nvl2(wf.more_info_role,'false',NULL)
           ,(SELECT nvl(max('false'),'true')
             FROM  wf_message_attributes_vl wma
             WHERE wf.message_name = wma.message_name
             AND   wf.message_type = wma.message_type
             AND   wma.name = 'RESULT'
             AND   wma.subtype = 'RESPOND'
             )
        )                                                                                 AS "closeOnly"    
) ) ORDER  BY wf.begin_date DESC
 ) ) "data"
      
FROM   (SELECT   /*+ index(WF WF_NOTIFICATIONS_N1)*/ notification_id                                                                   
                ,wf.from_user                                                                      
                ,wf.subject                                                                        
                ,wf.begin_date
                ,wf.from_role
                ,wf.message_name
                ,wf.message_type
                ,wf.more_info_role
                ,wf.due_date
                ,wf.recipient_role recipient_role
                ,img.person_id
                ,img.last_update_date
                ,img.first_name
                ,img.last_name
                ,img.known_as
        FROM    wf_notifications wf
               ,(SELECT usr.user_name
                       ,t.person_id
                       ,t.last_update_date
                       ,papf.first_name
                       ,papf.last_name
                       ,papf.known_as
                 FROM   fnd_user usr
                       ,xxas_per_thumbnails t
                       ,per_all_people_f papf
                 WHERE  usr.employee_id = t.person_id(+)
                 AND    usr.employee_id = papf.person_id
                 AND    trunc(sysdate) BETWEEN
                        papf.effective_start_date AND papf.effective_end_date) img
        WHERE  wf.from_role = img.user_name(+)
        AND   wf.recipient_role IN 
                 (SELECT ROLE_NAME
                  FROM   wf_user_roles wr
                  WHERE  wr.user_name = fnd_global.user_name
                /*  Commenting to avoid performance issues in customer instances.
                UNION           
                  SELECT ROLE_NAME
                  FROM   wf_user_roles wr
                  WHERE  wr.user_name IN
                           (SELECT parameter1
                            FROM   fnd_grants fg
                            WHERE  fg.grantee_key = fnd_global.user_name
                            AND    instr(nvl(fg.parameter2,wf.message_type),wf.message_type) > 0
                            AND    fg.INSTANCE_TYPE   = 'SET'
                            AND fg.OBJECT_ID       IN  (SELECT fo.object_id from fnd_objects fo 
                                                      where  fo.database_object_name = 'WF_NOTIFICATIONS'
                                                      )
                           AND Fg.MENU_ID       = (SELECT fm.menu_id
                                                   from   fnd_menus fm
                                                   where  fm.menu_name = 'FND_WF_WORKLIST'
                                                   )
                           AND fg.INSTANCE_SET_ID = (select fis.instance_set_id
                                                     from FND_OBJECT_INSTANCE_SETS fis
                                                     where fis.instance_set_name = 'WL_PROXY_ACCESS'
                                                     )
                            AND    trunc(SYSDATE) BETWEEN trunc(start_date) AND nvl(trunc(end_date),trunc(SYSDATE))
                            )
                 */
                 )
        AND wf.status = 'OPEN'
        AND wf.more_info_role IS NULL
        UNION ALL
      SELECT  notification_id                                                                 
                ,wf.from_user                                                                      
                ,wf.subject                                                                        
                ,wf.begin_date
                ,wf.from_role
                ,wf.message_name
                ,wf.message_type
                ,wf.more_info_role
                ,wf.due_date
                ,wf.more_info_role  recipient_role
                ,img.person_id
                ,img.last_update_date
                ,img.first_name
                ,img.last_name
                ,img.known_as
        FROM    wf_notifications wf
               ,(SELECT usr.user_name
                       ,t.person_id
                       ,t.last_update_date
                       ,papf.first_name
                       ,papf.last_name
                       ,papf.known_as
                 FROM   fnd_user usr
                       ,xxas_per_thumbnails t
                       ,per_all_people_f papf
                 WHERE  usr.employee_id = t.person_id(+)
                 AND    usr.employee_id = papf.person_id
                 AND    trunc(sysdate) BETWEEN
                        papf.effective_start_date AND papf.effective_end_date) img
        WHERE  wf.from_role = img.user_name(+)
        AND    wf.more_info_role IN 
                 (SELECT ROLE_NAME
                  FROM   wf_user_roles wr
                  WHERE  wr.user_name = fnd_global.user_name
                /*  Commenting to avoid performance issues in customer instances.
                  UNION 
                  SELECT ROLE_NAME
                  FROM   wf_user_roles wr
                  WHERE  wr.user_name IN 
                           (SELECT parameter1
                            FROM   fnd_grants fg
                            WHERE  fg.grantee_key = fnd_global.user_name
                            AND    fg.INSTANCE_TYPE   = 'SET'
                            AND    instr(nvl(fg.parameter2,wf.message_type),wf.message_type) > 0
                            AND fg.OBJECT_ID       IN  (SELECT fo.object_id from fnd_objects fo 
                                                      where  fo.database_object_name = 'WF_NOTIFICATIONS'
                                                      )
                           AND Fg.MENU_ID       = (SELECT fm.menu_id
                                                   from   fnd_menus fm
                                                   where  fm.menu_name = 'FND_WF_WORKLIST'
                                                   )
                           AND fg.INSTANCE_SET_ID = (select fis.instance_set_id
                                                     from FND_OBJECT_INSTANCE_SETS fis
                                                     where fis.instance_set_name = 'WL_PROXY_ACCESS'
                                                     )
                            AND    trunc(sysdate) between trunc(start_date) and nvl(trunc(end_date),trunc(sysdate))
                            )
                  */
                 )
        AND wf.status = 'OPEN'
         )wf
        ]'
    );
    
  xxas_com_sr_pkg.set_rest_method
    (p_service_name       => l_SERVICE
    ,p_method_name        => 'NotificationsForUser'
    ,p_description        => 'Get workflow notifications'
    ,p_methods            => 'GET'
    ,p_remove_root_ele    => true
    ,p_path               =>'{userId}'
    ,p_sql                =>
      q'[
      select xxas_com_worklist_pkg.get_notifications_xml({requestParams},{pathParam.userId}) "data" from dual
        ]'
    );

  xxas_com_sr_pkg.set_rest_method
    (p_service_name       => l_SERVICE
    ,p_method_name        => 'GetBadgeCount'
    ,p_description        => 'Gets the number of open workflow notifications'
    ,p_path               => 'badge-count'
    ,p_methods            => 'GET'      
    ,p_remove_root_ele    => false
    ,p_sql                =>
      q'[
      SELECT 
               to_char(wf_notification.workCount({requestParam.userName})) as "badge"
               ,'true' as "global"
    
      FROM dual
        ]'
    );
    
  xxas_com_sr_pkg.set_rest_method
    (p_service_name       => l_SERVICE
    ,p_method_name        => 'RespondToNotifications'
    ,p_description        => 'Respond to one or more notifications'
    ,p_path               => '{action}'
    ,p_methods            => 'DELETE'
    ,p_plsql              =>
      q'[
        DECLARE
        
          l_xml         XMLType;
          l_resp        xxas_http_resp_obj;
          l_resp_xml    XMLType;
          l_request_xml XMLType;
          
        BEGIN
        
          l_request_xml := {requestBody};

          l_resp_xml := xxas_com_worklist_pkg.bulk_respond
                          (p_action                => {pathParam.action}
                          ,p_request_body          => l_request_xml
                          ,p_response_xml_function => {requestParam.respXMLFunction}
                          ,p_source_block          => {requestParam.sourceBlock});
					
		  
          l_resp := xxas_http_response
                      .ok(l_resp_xml
                         )
                      .build();						  
              
		  
         {response} := l_resp;

        END
      ]'
    );
	
  xxas_com_sr_pkg.set_rest_method
    (p_service_name       => l_SERVICE
    ,p_method_name        => 'ReassignNotifications'
    ,p_description        => 'Reassign one or more notifications'
    ,p_path               => '{action}'
    ,p_methods            => 'POST'
    ,p_plsql              =>
      q'[
        DECLARE
        
          l_xml         XMLType;
          l_resp        xxas_http_resp_obj;
          l_resp_xml    XMLType;
          l_request_xml XMLType;
          l_notification_list VARCHAR2(4000):= '';
		  
		  CURSOR csr_get_notification_list IS
		  SELECT wn.notification_id
          FROM  XMLTable
            ('/*/*'
            PASSING (l_request_xml)
            COLUMNS item_id   NUMBER         path 'id'
                   ,item_type VARCHAR2(4000) path 'type') i
         ,wf_notifications wn
         WHERE i.item_type = '"worklist"'
         AND   i.item_id = wn.notification_id;
		 
        BEGIN
        
          l_request_xml := {requestBody};

          FOR csr_rec IN csr_get_notification_list LOOP
		    l_notification_list := l_notification_list || csr_rec.notification_id||'$';
		  END LOOP;
  
		  l_resp_xml := xxas_com_rest_util_pkg.app_route_redirect_response
                        (p_app_route     => 'bulk-reassign/' ||
                                            to_char(fnd_global.employee_id) || '_' || l_notification_list
                        ,p_target        => 'modal'
                        ,p_persist_cache => 'true');
						
          l_resp := xxas_http_response
                      .ok(l_resp_xml
                         )
                      .build();						  
              
		  
         {response} := l_resp;

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