/* $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_PERF_QUESTIONNAIRES_V
AS
SELECT xpq.questionnaire_id
    ,xpq.perf_plan_enrollment_id
    ,xpq.perf_review_step_id
    ,xpq.reviewer_person_id
    --,xpq.reviewer_person_id person_id
    ,xpq.status_code
    ,xxas_util_pkg.decode_lookup('XXAS_PERF_QUESTION_STATUSES', xpq.status_code) status_meaning
    ,xpq.rating
    ,xpq.comments_clob comments
    ,xpq.questionnaire_template
    ,xpq.question1
    ,xpq.question2
    ,xpq.question3
    ,xpq.question4
    ,xpq.question5
    ,xpq.question6
    ,xpq.question7
    ,xpq.question8
    ,xpq.question9
    ,xpq.question10
    ,xpq.question11
    ,xpq.question12
    ,xpq.question13
    ,xpq.question14
    ,xpq.question15
    ,xpq.question16
    ,xpq.question17
    ,xpq.question18
    ,xpq.question19
    ,xpq.question20
    ,xpq.question21
    ,xpq.question22
    ,xpq.question23
    ,xpq.question24
    ,xpq.question25
    ,xpq.question26
    ,xpq.question27
    ,xpq.question28
    ,xpq.question29
    ,xpq.question30
    ,xpq.question31
    ,xpq.question32
    ,xpq.question33
    ,xpq.question34
    ,xpq.question35
    ,xpq.question36
    ,xpq.question37
    ,xpq.question38
    ,xpq.question39
    ,xpq.question40
    ,xpq.question41
    ,xpq.question42
    ,xpq.question43
    ,xpq.question44
    ,xpq.question45
    ,xpq.question46
    ,xpq.question47
    ,xpq.question48
    ,xpq.question49
    ,xpq.question50
    ,xpq.question51
    ,xpq.question52
    ,xpq.question53
    ,xpq.question54
    ,xpq.question55
    ,xpq.question56
    ,xpq.question57
    ,xpq.question58
    ,xpq.question59
    ,xpq.question60
    ,xpq.created_by
    ,xpq.creation_date
    ,xpq.last_update_date
    ,xpq.last_updated_by
    ,xpq.last_update_login
    ,xprf.perf_review_form_id
    ,xprf.assignment_id
    ,xprf.person_id
    ,xprf.start_date
    ,cast(multiset
               (SELECT a.attached_document_id
                          ,a.seq_num sequence_number
                          ,a.entity_name
                          ,a.pk1_value
                          ,a.pk2_value
                          ,a.pk3_value
                          ,a.pk4_value
                          ,a.pk5_value
                          ,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
                          ,fnd_gfm.construct_download_url(fnd_web_config.gfm_agent, l.file_id) file_url
                          ,null wp_link
                                                  ,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_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.category_id = c.category_id
                 AND    d.datatype_id = t.datatype_id
                 AND    a.pk1_value IS NOT NULL
                 AND    a.pk1_value = to_char(xpq.questionnaire_id)
                 AND    a.entity_name = 'PQH_SS_ATTACHMENT'
                 ORDER  BY a.seq_num)
                                 AS xxas_attachment_table) ATTACHMENTS
  FROM xxas_perf_questionnaires xpq
      ,xxas_perf_review_forms xprf
      ,xxas_perf_Review_steps xprs
  WHERE xprs.perf_review_step_id = xpq.perf_review_step_id
  AND   xprs.perf_review_form_id = xprf.perf_review_form_id
  AND  NOT EXISTS
        (
         SELECT 1
           FROM xxas_com_data
          WHERE data_type = xxas_com_data_type_pkg.get_data_type_perf_quest
            AND current_state_code = 'D'
            AND data_id = xpq.questionnaire_id );
            
BEGIN
  xxas_fnd_dictionary_pkg.add_view
   (p_application_short_name => xxas_util_pkg.app
   ,p_view_name              => 'XXAS_PERF_QUESTIONNAIRES_V'
   ,p_description            => 'Applaud Performance Questionnaires');
END;     
/       

COMMIT;
EXIT;

