/* $Id$ */
/*============================================================================*
 |                 Copyright (c) 2013 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 VIEW xxas_attachments_v 
AS
SELECT a.attached_document_id
      ,a.attached_document_id attachment_id
      ,a.seq_num sequence_number
      ,e.document_entity_id
      ,e.table_name
      ,CASE
         WHEN e.pk1_column IN ('PERSON_ID', 'pPersonId')  THEN pk1_value
         WHEN e.pk2_column IN ('PERSON_ID', 'pPersonId')  THEN pk2_value
         WHEN e.pk3_column IN ('PERSON_ID', 'pPersonId')  THEN pk3_value
         WHEN e.pk4_column IN ('PERSON_ID', 'pPersonId')  THEN pk4_value
         WHEN e.pk5_column IN ('PERSON_ID', 'pPersonId')  THEN pk5_value
       END person_id
      ,a.entity_name attachment_entity
      ,a.pk1_value attachment_pk1
      ,a.pk2_value attachment_pk2
      ,a.pk3_value attachment_pk3
      ,a.pk4_value attachment_pk4
      ,a.pk5_value attachment_pk5
      ,c.name category_code
      ,c.user_name category_name
      ,d.document_id
      ,d.file_name
      ,d.title
      ,d.description
      ,l.file_id
      ,l.file_content_type mime_type
      ,l.file_format lob_format
      ,a.creation_date 
      ,xxas_wf_role_util_pkg.get_display_name_from_user
        (a.created_by) created_by
      ,a.last_update_date 
      ,xxas_wf_role_util_pkg.get_display_name_from_user
        (a.last_updated_by) last_updated_by
FROM   fnd_attached_documents a
      ,fnd_documents_vl d
      ,fnd_document_entities_vl e
      ,fnd_lobs l
      ,fnd_document_categories_vl c
      ,fnd_document_datatypes_vl t
WHERE  t.name = 'FILE'
AND    d.media_id = l.file_id(+)
AND    a.document_id = d.document_id
AND    a.entity_name = e.data_object_code
AND    a.category_id = c.category_id
AND    d.datatype_id = t.datatype_id;

BEGIN
  xxas_fnd_dictionary_pkg.add_view
   (p_application_short_name => xxas_util_pkg.app
   ,p_view_name              => 'XXAS_ATTACHMENTS_V'
   ,p_description            => 'Applaud Attachments');
END;
/

COMMIT;
EXIT;
