/* $Id$ */
/*============================================================================*
 |                 Copyright (c) 2016 Applaud Solutions UK Ltd                |
 |                             All rights reserved                            |
 *===========================================================================*/

SET VERIFY OFF
SET FEEDBACK OFF
WHENEVER OSERROR  EXIT FAILURE ROLLBACK
WHENEVER SQLERROR EXIT FAILURE ROLLBACK

CREATE OR REPLACE FORCE VIEW xxas_wf_notifications_v
AS
 SELECT wf.notification_id,
        wf.group_id,
        wf.message_type,
        wf.message_name,
        wf.recipient_role,
        wf.status,
        wf.access_key,
        wf.mail_status,
        wf.priority,
        wf.begin_date,
        wf.end_date,
        wf.due_date,
        wf.responder,
        wf.user_comment,
        wf.callback,
        wf.context,
        wf.original_recipient,
        wf.from_user,
        wf.to_user,
        wf.subject,
        wf.language,
        wf.more_info_role,
        wf.from_role,
        wf.security_group_id,
        wf.user_key,
        wf.item_key,
        xxas_com_worklist_pkg.get_first_response(wf.notification_id) response,
        xxas_com_worklist_pkg.get_first_response(wf.notification_id, 'Y') response_l,
    /*    xxas_attachment_util_pkg.attachments_tbl
          (p_attachment_id                 =>   NULL
          ,p_entity_name                   =>   xxas_com_sec_pkg.get_parameter_value(wna.text_value,'entity')
          ,p_pk1_value                     =>   xxas_com_sec_pkg.get_parameter_value(wna.text_value,'pk1value')
          ,p_pk2_value                     =>   xxas_com_sec_pkg.get_parameter_value(wna.text_value,'pk2value')
          ,p_pk3_value                     =>   xxas_com_sec_pkg.get_parameter_value(wna.text_value,'pk3value')
          ,p_pk4_value                     =>   xxas_com_sec_pkg.get_parameter_value(wna.text_value,'pk4value')
          ,p_pk5_value                     =>   xxas_com_sec_pkg.get_parameter_value(wna.text_value,'pk5value')
          ) attachments,
        xxas_attachment_util_pkg.web_attachments_tbl
          (p_attachment_id                 =>   NULL
          ,p_entity_name                   =>   xxas_com_sec_pkg.get_parameter_value(wna.text_value,'entity')
          ,p_pk1_value                     =>   xxas_com_sec_pkg.get_parameter_value(wna.text_value,'pk1value')
          ,p_pk2_value                     =>   xxas_com_sec_pkg.get_parameter_value(wna.text_value,'pk2value')
          ,p_pk3_value                     =>   xxas_com_sec_pkg.get_parameter_value(wna.text_value,'pk3value')
          ,p_pk4_value                     =>   xxas_com_sec_pkg.get_parameter_value(wna.text_value,'pk4value')
          ,p_pk5_value                     =>   xxas_com_sec_pkg.get_parameter_value(wna.text_value,'pk5value')
          ) web_attachments,
          (Select count(1) from table(xxas_attachment_util_pkg.attachments_tbl
          (p_attachment_id                 =>   NULL
          ,p_entity_name                   =>   xxas_com_sec_pkg.get_parameter_value(wna.text_value,'entity')
          ,p_pk1_value                     =>   xxas_com_sec_pkg.get_parameter_value(wna.text_value,'pk1value')
          ,p_pk2_value                     =>   xxas_com_sec_pkg.get_parameter_value(wna.text_value,'pk2value')
          ,p_pk3_value                     =>   xxas_com_sec_pkg.get_parameter_value(wna.text_value,'pk3value')
          ,p_pk4_value                     =>   xxas_com_sec_pkg.get_parameter_value(wna.text_value,'pk4value')
          ,p_pk5_value                     =>   xxas_com_sec_pkg.get_parameter_value(wna.text_value,'pk5value')
          ))) file_attachments_count,
          (Select count(1) from table(xxas_attachment_util_pkg.web_attachments_tbl
          (p_attachment_id                 =>   NULL
          ,p_entity_name                   =>   xxas_com_sec_pkg.get_parameter_value(wna.text_value,'entity')
          ,p_pk1_value                     =>   xxas_com_sec_pkg.get_parameter_value(wna.text_value,'pk1value')
          ,p_pk2_value                     =>   xxas_com_sec_pkg.get_parameter_value(wna.text_value,'pk2value')
          ,p_pk3_value                     =>   xxas_com_sec_pkg.get_parameter_value(wna.text_value,'pk3value')
          ,p_pk4_value                     =>   xxas_com_sec_pkg.get_parameter_value(wna.text_value,'pk4value')
          ,p_pk5_value                     =>   xxas_com_sec_pkg.get_parameter_value(wna.text_value,'pk5value')
          ))) web_attachments_count,   */
        ((Select count(1) from table(xxas_attachment_util_pkg.attachments_tbl
          (p_attachment_id                 =>   NULL
          ,p_entity_name                   =>   xxas_com_sec_pkg.get_parameter_value(wna.text_value,'entity')
          ,p_pk1_value                     =>   xxas_com_sec_pkg.get_parameter_value(wna.text_value,'pk1value')
          ,p_pk2_value                     =>   xxas_com_sec_pkg.get_parameter_value(wna.text_value,'pk2value')
          ,p_pk3_value                     =>   xxas_com_sec_pkg.get_parameter_value(wna.text_value,'pk3value')
          ,p_pk4_value                     =>   xxas_com_sec_pkg.get_parameter_value(wna.text_value,'pk4value')
          ,p_pk5_value                     =>   xxas_com_sec_pkg.get_parameter_value(wna.text_value,'pk5value')
          ))) + (Select count(1) from table(xxas_attachment_util_pkg.web_attachments_tbl
          (p_attachment_id                 =>   NULL
          ,p_entity_name                   =>   xxas_com_sec_pkg.get_parameter_value(wna.text_value,'entity')
          ,p_pk1_value                     =>   xxas_com_sec_pkg.get_parameter_value(wna.text_value,'pk1value')
          ,p_pk2_value                     =>   xxas_com_sec_pkg.get_parameter_value(wna.text_value,'pk2value')
          ,p_pk3_value                     =>   xxas_com_sec_pkg.get_parameter_value(wna.text_value,'pk3value')
          ,p_pk4_value                     =>   xxas_com_sec_pkg.get_parameter_value(wna.text_value,'pk4value')
          ,p_pk5_value                     =>   xxas_com_sec_pkg.get_parameter_value(wna.text_value,'pk5value')
          )))) attachments_count,
         xxas_com_worklist_pkg.get_result_meaning(wf.notification_id,
                            xxas_com_worklist_pkg.getAttrText
                              (wf.notification_id,'RESULT')) response_meaning,
          xxas_com_worklist_pkg.getattrtext
              (wf.notification_id,'WF_NOTE') wf_notes,
          CASE WHEN xxas_com_worklist_pkg.getAttrText
              (wf.notification_id,'RESULT') IS NOT NULL THEN 'Y'
              ELSE 'N'
          END response_exists,
          CASE WHEN xxas_com_worklist_pkg.getattrtext
              (wf.notification_id,'WF_NOTE') IS NOT NULL THEN 'Y'
              ELSE 'N'
          END wf_note_exists
   FROM   wf_notifications wf,
          wf_notification_attributes wna
  WHERE wf.notification_id = wna.notification_id(+)
    AND wna.name(+) = '#ATTACHMENTS';
            
BEGIN
  xxas_fnd_dictionary_pkg.add_view
   (p_application_short_name => xxas_util_pkg.app
   ,p_view_name              => 'XXAS_WF_NOTIFICATIONS_V'
   ,p_description            => 'Applaud Worklist Notifications');
END;     
/       

COMMIT;
EXIT;

