/* $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 VIEW xxas_competency_det_in_rev_v
AS
SELECT    xcev.competence_id,
          xcev.person_id,
          xcev.competence_element_id,
          xcev.compe_start_date,
          xcev.compe_end_date,
          xcev.type,
          xcev.party_id,
          xcev.business_group_id,
          xcev.status,					
          xcev.proficiency_level_id,        
          xcev.competence_name,
          xcev.behavioural_indicator,
          xcev.competence_desc,
          xcev.rating_scale_id,
          xcev.step_value,
          xcev.step_name,
          xcev.proficiency_name,
          xcev.proficiency,
          xcev.acquired_by,
          xcev.certification_date,
          xcev.measured_by,
          xcev.next_review_date,
          xcev.justification,
          xcev.created_by,
          xcev.creation_date,
          xcev.last_update_date,
          xcev.last_updated_by,
          xcev.last_update_login,
          xcev.attribute_category,
          xcev.attribute1,
          xcev.attribute2,
          xcev.attribute3,
          xcev.attribute4,
          xcev.attribute5,
          xcev.attribute6,
          xcev.attribute7,
          xcev.attribute8,
          xcev.attribute9,
          xcev.attribute10,
          xcev.attribute11,
          xcev.attribute12,
          xcev.attribute13,
          xcev.attribute14,
          xcev.attribute15,
          xcev.attribute16,
          xcev.attribute17,
          xcev.attribute18,
          xcev.attribute19,
          xcev.attribute20,
          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
                          ,l.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(xcev.competence_element_id)
                 AND    a.entity_name = 'PQH_SS_ATTACHMENT'
                 ORDER  BY a.seq_num)
                                 AS xxas_attachment_table) ATTACHMENTS,
          xcev.perf_plan_enrollment_id,           
          xcev.parent_competence_element_id,
          xcev.created_by competency_creator,
          xprf.status_code form_status_code,
          xprf.perf_review_form_id,
          xprf.current_step_id,
          xprf.current_step_id perf_review_step_id,
          xcev.weighting_percent,
          'XXAS' source,
          xcd.current_state_code competency_state
FROM  xxas_competence_elements_v xcev,
      xxas_perf_review_forms_v xprf,
      xxas_com_data xcd
WHERE xprf.perf_plan_enrollment_id = xcev.perf_plan_enrollment_id
AND   xcd.data_type = xxas_com_data_type_pkg.get_data_type_competency
AND   xcd.data_id   = xcev.competence_element_id
UNION ALL
SELECT    xcdv.competence_id,
          xcdv.person_id,
          xcdv.competence_element_id,
          xcdv.compe_start_date,
          xcdv.compe_end_date,
          xcdv.type,
          xcdv.party_id,
          xcdv.business_group_id,
          xcdv.status,
          xcdv.proficiency_level_id, 
          xcdv.competence_name,
          xcdv.behavioural_indicator,
          xcdv.competence_desc,
          xcdv.rating_scale_id,
          xcdv.step_value,
          xcdv.step_name,
          xcdv.proficiency_name,
          xcdv.proficiency,
          xcdv.acquired_by,
          xcdv.certification_date,
          xcdv.measured_by,
          xcdv.next_review_date,
          xcdv.justification,
          xcdv.created_by,
          xcdv.creation_date,
          xcdv.last_update_date,
          xcdv.last_updated_by,
          xcdv.last_update_login,
          xcdv.attribute_category,
          xcdv.attribute1,
          xcdv.attribute2,
          xcdv.attribute3,
          xcdv.attribute4,
          xcdv.attribute5,
          xcdv.attribute6,
          xcdv.attribute7,
          xcdv.attribute8,
          xcdv.attribute9,
          xcdv.attribute10,
          xcdv.attribute11,
          xcdv.attribute12,
          xcdv.attribute13,
          xcdv.attribute14,
          xcdv.attribute15,
          xcdv.attribute16,
          xcdv.attribute17,
          xcdv.attribute18,
          xcdv.attribute19,
          xcdv.attribute20,
          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
                          ,l.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(xcdv.competence_element_id)
                 AND    a.entity_name = 'PQH_SS_ATTACHMENT'
                 ORDER  BY a.seq_num)
                                 AS xxas_attachment_table) ATTACHMENTS,
          xcdv.perf_plan_enrollment_id,          
          null parent_competence_element_id,					
          xcdv.created_by competency_creator,
          xprf.status_code form_status_code,
          xprf.perf_review_form_id,
          xprf.current_step_id ,
          xprf.current_step_id perf_review_step_id,         
          xcdv.weighting_level_id weighting_percent,      
          'PER' source,
          'A' competency_state
FROM  xxas_competency_details_v xcdv,
      xxas_perf_review_forms_v xprf
WHERE xprf.perf_plan_enrollment_id = xcdv.perf_plan_enrollment_id
AND NOT EXISTS
   (SELECT 1 from xxas_competence_elements_v
    WHERE parent_competence_element_id = xcdv.competence_element_id
   );

/
COMMIT;
EXIT;