/* $Id$ */
/*============================================================================*
 |                 Copyright (c) 2015 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_candidate_profile_v
AS
SELECT papf.person_id
      ,lower(xxas_util_pkg.get_username_from_per
              (papf.person_id, papf.effective_start_date)) username
      ,papf.title
      --,nvl(papf.known_as, papf.first_name) first_name   NT GIT#2774
      ,papf.first_name first_name 
      ,papf.last_name
      ,papf.email_address
      ,papf.sex
      ,papf.current_employee_flag
      ,papf.current_npw_flag
      ,papf.employee_number
      ,papf.npw_number
      ,addr.style
      ,addr.address_type address_type
      ,addr.address_line1 address_line1
      ,addr.address_line2 address_line2
      ,addr.address_line3 address_line3
      ,addr.town_or_city town_or_city
      ,addr.region_1 region_1
      ,addr.region_2 region_2
      ,addr.region_3 region_3
      ,addr.postal_code postal_code
      ,addr.country country
      ,addr.derived_locale derived_locale
      ,(SELECT lat.y
        FROM   TABLE(sdo_util.getvertices(addr.geometry)) lat) latitude
      ,(SELECT lon.x
        FROM   TABLE(sdo_util.getvertices(addr.geometry)) lon) longtitude
      ,addr.town_or_city || ', ' || region_1 address
      ,(SELECT doc.document_id
        FROM   irc_documents doc
        WHERE  doc.person_id = NVL(inp.person_id,papf.person_id)
        AND    doc.end_date IS NULL
        AND    doc.type(+) LIKE '%RESUME'
        AND    nvl(doc.document_id, 1) =
               nvl
                ((SELECT MAX(idoc1.document_id)
                  FROM   irc_documents idoc1
                  WHERE  idoc1.person_id = NVL(inp.person_id,papf.person_id)
                  AND    idoc1.type IN('RESUME', 'AUTO_RESUME')
                  AND    idoc1.end_date IS NULL
                  AND    last_update_date =
                    (SELECT max(idoc2.last_update_date)
                     FROM   irc_documents idoc2
                     WHERE  idoc2.person_id = NVL(inp.person_id,papf.person_id)
                     AND    idoc2.type IN('RESUME','AUTO_RESUME')
                     AND    idoc2.end_date IS NULL))
                ,1)) cv_document_id
      ,(SELECT doc.document_id
        FROM   irc_documents doc
        WHERE  doc.person_id = NVL(inp.person_id,papf.person_id)
        AND    doc.end_date IS NULL
        AND    doc.type(+) = 'COVERLETTER'
        AND    nvl(doc.document_id, 1) =
               nvl
                ((SELECT MAX(idoc1.document_id)
                  FROM   irc_documents idoc1
                  WHERE  idoc1.person_id = NVL(inp.person_id,papf.person_id)
                  AND    idoc1.type = 'COVERLETTER'
                  AND    idoc1.end_date IS NULL
                  AND    last_update_date =
                    (SELECT max(idoc2.last_update_date)
                     FROM   irc_documents idoc2
                     WHERE  idoc2.person_id = NVL(inp.person_id,papf.person_id)
                     AND    idoc2.type = 'COVERLETTER'
                     AND    idoc2.end_date IS NULL))
                ,1)) cov_let_document_id
      ,papf.attribute_category
      ,papf.attribute1
      ,papf.attribute2
      ,papf.attribute3
      ,papf.attribute4
      ,papf.attribute5
      ,papf.attribute6
      ,papf.attribute7
      ,papf.attribute8
      ,papf.attribute9
      ,papf.attribute10
      ,papf.attribute11
      ,papf.attribute12
      ,papf.attribute13
      ,papf.attribute14
      ,papf.attribute15
      ,papf.attribute16
      ,papf.attribute17
      ,papf.attribute18
      ,papf.attribute19
      ,papf.attribute20
      ,papf.attribute21
      ,papf.attribute22
      ,papf.attribute23
      ,papf.attribute24
      ,papf.attribute25
      ,papf.attribute26
      ,papf.attribute27
      ,papf.attribute28
      ,papf.attribute29
      ,papf.attribute30
      ,papf.per_information_category
      ,papf.per_information1
      ,papf.per_information2
      ,papf.per_information3
      ,papf.per_information4
      ,papf.per_information5
      ,papf.per_information6
      ,papf.per_information7
      ,papf.per_information8
      ,papf.per_information9
      ,papf.per_information10
      ,papf.per_information11
      ,papf.per_information12
      ,papf.per_information13
      ,papf.per_information14
      ,papf.per_information15
      ,papf.per_information16
      ,papf.per_information17
      ,papf.per_information18
      ,papf.per_information19
      ,papf.per_information20
      ,papf.per_information21
      ,papf.per_information22
      ,papf.per_information23
      ,papf.per_information24
      ,papf.per_information25
      ,papf.per_information26
      ,papf.per_information27
      ,papf.per_information28
      ,papf.per_information29
      ,papf.per_information30
      ,(SELECT  XMLAgg
                 (XMLElement
                   ("XXAS_PER_PHONE"
                   ,XMLForest
                     (hrl.lookup_code phone_type_code
                     ,hrl.meaning phone_type
                     ,phn.phone_id
                     ,phn.phone_number phone_number)
                   )
                 ORDER BY hrl.lookup_code)
        FROM     hr_lookups hrl
                ,per_phones phn
        WHERE    hrl.lookup_type = 'PHONE_TYPE'
        AND      hrl.lookup_code IN
                   (SELECT column_value
                    FROM TABLE(xxas_util_pkg.quoted_string_to_varchar2tbl
                                (fnd_profile.value('IRC_PHONE_TYPES'))))
        AND      hrl.enabled_flag = 'Y'
        AND      trunc(sysdate) BETWEEN
                 nvl(hrl.start_date_active, trunc(sysdate)) AND
                 nvl(hrl.end_date_active, trunc(sysdate))
        AND      phn.parent_id(+) = papf.person_id
        AND      phn.parent_table(+) = 'PER_ALL_PEOPLE_F'
        AND      phn.phone_type(+) = hrl.lookup_code
        AND      trunc(sysdate) BETWEEN
                 nvl(phn.date_from(+), trunc(sysdate)) AND
                 nvl(phn.date_to(+), trunc(sysdate))) phone_numbers
      ,(SELECT XMLAgg
                 (XMLElement
                   ("XXAS_IRC_DOCUMENT"
                   ,XMLForest
                     (doc.document_id
                     ,doc.file_name
                     ,doc.type
                     ,doc.description)
                   ) ORDER BY doc.last_update_date DESC)
        FROM   irc_documents doc
        WHERE  doc.person_id = NVL(inp.person_id,papf.person_id)
        AND    doc.end_date IS NULL
        AND    doc.type NOT IN('RESUME', 'AUTO_RESUME', 'COVERLETTER')) documents
     ,(SELECT XMLAgg
                 (XMLElement
                   ("XXAS_PREVIOUS_EMPLOYER"
                   ,XMLForest
                     (pe.prev_empr_id
                     ,pe.person_id
                     ,pe.prev_empr_name
                     ,pe.prev_empr_start_date
                     ,pe.prev_empr_end_date
                     ,pe.prev_empr_job_id
                     ,pe.prev_empr_job_name
                     ,pe.prev_empr_address
                     ,pe.prev_empr_duties
                     ,pe.period_years
                     ,pe.period_days
                     ,pe.employer_type)
                   ) ORDER BY pe.prev_empr_start_date DESC)
       FROM   xxas_per_previous_employers_v pe
       WHERE  pe.person_id = NVL(inp.person_id,papf.person_id)) previous_employers
     ,(SELECT XMLAgg
                 (XMLElement
                   ("XXAS_QUALIFICATION_EDUCATION"
                   ,XMLForest
                     (q.attendance_id
                     ,q.establishment
                     ,q.address
                     ,q.qualification_id
                     ,q.qualification_type_id
                     ,q.pq_category_code
                     ,q.qualification_name
                     ,q.title qualification_title
                     ,q.awarded_date)
                   ) ORDER BY q.awarded_date DESC)
       FROM   xxas_per_qualifications_v q
       WHERE  q.person_id = NVL(inp.person_id,papf.person_id)
       AND    q.education_or_award = 'EDUCATION') further_education
     ,(SELECT XMLAgg
                 (XMLElement
                   ("XXAS_QUALIFICATION_AWARD"
                   ,XMLForest
                     (q.qualification_id
                     ,q.awarding_body
                     ,q.qualification_type_id
                     ,q.pq_category_code
                     ,q.qualification_name
                     ,q.title qualification_title
                     ,q.grade_attained
                     ,q.awarded_date)
                   ) ORDER BY q.awarded_date DESC)
       FROM   xxas_per_qualifications_v q
       WHERE  q.person_id = NVL(inp.person_id,papf.person_id)
       AND    q.education_or_award = 'AWARDS') school
     ,NVL(inp.allow_access,'N')      allow_access
     ,NVL(inp.receive_info_mail,'N') receive_info_mail
     ,NVL(inp.matching_jobs,'N')     matching_jobs
     ,NVL(inp.matching_job_freq,'1') matching_job_freq
FROM   per_all_people_f papf
      ,irc_notification_preferences inp
      ,per_addresses addr
WHERE  trunc(sysdate) BETWEEN
       papf.effective_start_date AND papf.effective_end_date
AND    irc_utilities_pkg.get_recruitment_person_id
         (papf.person_id, trunc(sysdate)) = inp.person_id(+)
AND    inp.person_id = addr.person_id(+)
AND    nvl(inp.address_id
          ,decode(addr.address_type(+),'REC', addr.address_id(+), -1)) = addr.address_id(+)
AND    trunc(sysdate) BETWEEN
       addr.date_from(+) AND nvl(addr.date_to(+), trunc(sysdate));

BEGIN
  xxas_fnd_dictionary_pkg.add_view
   (p_application_short_name => xxas_util_pkg.app
   ,p_view_name              => 'XXAS_CANDIDATE_PROFILE_V'
   ,p_description            => 'Applaud Candidate Profile');
END;
/

COMMIT;
EXIT;

