/* $Id$ */
/*============================================================================*
 |                 Copyright (c) 2013 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_sql varchar2(32000);

BEGIN

  --
  -- Register Query Worklist
  --
  l_sql :=
'        SELECT XMLElement
                  ("Notifications"
                  ,XMLAgg
                    (XMLElement
                      ("Notification"
                      ,XMLAttributes
                        (notif.notification_id as "id"
                        ,notif.status as "statusCode"
                        ,notif.status_meaning as "statusMeaning"
                        ,notif.status_description as "statusDescription"
                        ,decode
                          (notif.status
                          ,''CLOSED''
                          ,''false''
                          ,decode
                            (xxas_com_worklist_pkg.reassign_allowed(notif.notification_id)
                            ,''Y'', ''true''
                            ,''false'')) as "reassignEnabled"
                        ,decode
                          (notif.status
                          ,''CLOSED''
                          ,''false''
                          ,decode
                            (xxas_com_worklist_pkg.request_moreinfo_allowed(notif.notification_id)
                            ,''Y'', ''true''
                            ,''false'')) as "requestMoreInfoEnabled")
                      ,XMLElement
                        ("Header"
                        ,XMLForest
                          (notif.from_user as "From"
                          ,notif.from_person_id as "FromPersonId"
                          ,notif.to_user as "To"
                          ,notif.subject as "Subject"
                          ,to_char(notif.begin_date, ''YYYY-MM-DD HH24:MI:SS'') as "SentDateTime"
                          ,to_char(notif.end_date, ''YYYY-MM-DD HH24:MI:SS'') as "ClosedDateTime"
                          ,to_char(notif.due_date, ''YYYY-MM-DD HH24:MI:SS'') as "DueDateTime"
                          ,decode(notif.is_fwk
                                 ,''Y''
                                 ,null
                                 ,decode(notif.text_body
                                        ,null, null
                                        ,substr(notif.text_body, 1, 150) || ''...'')) as "Snippet"))
                      ,XMLElement
                        ("Body"
                        ,XMLAttributes
                           (xxas_com_worklist_pkg.getFwkBodyURL(notif.notification_id) as "src"))
                      ,xxas_com_worklist_pkg.get_notif_responses
                        (:pViewName, notif.notification_id)) ORDER BY notif.begin_date DESC)) AS "Worklist"
        FROM  (SELECT  n.notification_id
                      ,n.subject
                      ,wf_notification.isFwkBody(n.notification_id) is_fwk
                      ,xxas_com_worklist_pkg.get_text_body(n.notification_id) text_body
                      ,xxas_com_worklist_pkg.get_html_body(n.notification_id) html_body
                      ,xxas_com_worklist_pkg.getFwkBodyURL(n.notification_id) html_src
                      ,n.status
                      ,l.meaning status_meaning
                      ,l.description status_description
                      ,xxas_util_pkg.server_to_user_timezone(n.begin_date) begin_date
                      ,xxas_util_pkg.server_to_user_timezone(n.end_date) end_date
                      ,xxas_util_pkg.server_to_user_timezone(n.due_date) due_date
                      ,n.responder
                      ,n.original_recipient
                      ,n.from_user
                      ,decode
                        (fr.orig_system
                        ,''PER'', fr.orig_system_id
                        ,null) from_person_id
                      ,n.to_user
                      ,n.from_role
                      ,n.more_info_role
                FROM (SELECT wn.notification_id
                            ,decode
                              (wn.subject
                              ,null
                              ,xxas_com_worklist_pkg.denorm_get_subject(wn.notification_id)
                              ,wn.subject) subject
                            ,wn.status
                            ,wn.begin_date
                            ,wn.end_date
                            ,wn.due_date
                            ,wn.responder
                            ,wn.original_recipient
                            ,decode
                              (wn.subject
                              ,null
                              ,xxas_com_worklist_pkg.denorm_get_from_user(wn.notification_id)
                              ,wn.from_user) from_user
                            ,decode
                              (wn.subject
                              ,null
                              ,xxas_com_worklist_pkg.denorm_get_to_user(wn.notification_id)
                              ,wn.to_user) to_user
                            ,wn.more_info_role
                            ,decode
                              (wn.subject
                              ,null
                              ,xxas_com_worklist_pkg.denorm_get_from_role(wn.notification_id)
                              ,wn.from_role) from_role
                            ,wn.message_type
                            ,wn.message_name
                      FROM   wf_notifications wn
                      WHERE  wn.status = nvl(:pStatusCode, ''OPEN'')
                      AND    wn.recipient_role IN
                        (SELECT WUR.role_name
                         FROM wf_user_roles wur
                         WHERE WUR.user_name = :pUserName) AND wn.more_info_role IS NULL
                      UNION ALL
                      SELECT wn.notification_id
                            ,decode
                              (wn.subject
                              ,null
                              ,xxas_com_worklist_pkg.denorm_get_subject(wn.notification_id)
                              ,wn.subject) subject
                            ,wn.status
                            ,wn.begin_date
                            ,wn.end_date
                            ,wn.due_date
                            ,wn.responder
                            ,wn.original_recipient
                            ,decode
                              (wn.subject
                              ,null
                              ,xxas_com_worklist_pkg.denorm_get_from_user(wn.notification_id)
                              ,wn.from_user) from_user
                            ,decode
                              (wn.subject
                              ,null
                              ,xxas_com_worklist_pkg.denorm_get_to_user(wn.notification_id)
                              ,wn.to_user) to_user
                            ,wn.more_info_role
                            ,decode
                              (wn.subject
                              ,null
                              ,xxas_com_worklist_pkg.denorm_get_from_role(wn.notification_id)
                              ,wn.from_role) from_role
                            ,wn.message_type
                            ,wn.message_name
                      FROM   wf_notifications wn
                      WHERE  wn.status = nvl(:pStatusCode, ''OPEN'')
                      AND    wn.more_info_role IN
                        (SELECT WUR.role_name
                         FROM wf_user_roles wur
                         WHERE WUR.user_name = :pUserName)) n
                     ,wf_item_types_vl it
                     ,wf_messages_vl m
                     ,wf_lookups l
                     ,wf_roles fr
                WHERE n.message_type = it.name
                AND   n.message_type = m.type
                AND   n.message_name = m.name
                AND   n.status = l.lookup_code
                AND   l.lookup_type = ''WF_NOTIFICATION_STATUS''
                AND   n.from_role = fr.name(+) ) notif 
        WHERE   EXISTS (SELECT ''Y''
                        FROM   dual
                        WHERE  xxas_com_sec_pkg.test_instance(''XXAS_WORKLIST_VIEW''
                                                             ,(SELECT user_id
                                                               FROM   fnd_user fur
                                                               WHERE  fur.user_name = :pUserName)) = ''Y'')' ;

  xxas_com_sr_pkg.seed_query_service
    (p_service_name      => 'QueryWorklist'
    ,p_securing_function => 'XXAS_SRV_Q_WORKLIST_PERM'
    ,p_sql               => l_sql
    ,p_parameters        => xxas_com_sr_param_varray
                              (xxas_com_sr_param_type('pViewName'  , xxas_com_sr_pkg.g_VARCHAR2)
                              ,xxas_com_sr_param_type('pStatusCode', xxas_com_sr_pkg.g_VARCHAR2)
                              ,xxas_com_sr_param_type('pUserName'  , xxas_com_sr_pkg.g_VARCHAR2)));

END;
/
COMMIT;
EXIT;
