/* $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_html_vacancies_v
AS
select v.vacancy_name
      ,v.date_from
      ,v.date_to
      ,v.business_group_id
      ,v.vacancy_id
      ,v.status_code
	  ,v.status_color
	  ,v.org_name
	  ,v.job_name
	  ,v.budget_value
  --    ,v.budget_measurement_value
      ,v.last_update_date
      ,v.location_id
	  ,v.job_id
	  ,v.grade_id
	  ,v.position_id
	  ,v.organization_id
      ,v.assessment_id
	  ,v.security_method
	  ,v.attribute_category
	  ,v.attribute1
	  ,v.attribute2
	  ,v.attribute3
	  ,v.attribute4
	  ,v.attribute5
	  ,v.attribute6
	  ,v.attribute7
	  ,v.attribute8
	  ,v.attribute9
	  ,v.attribute10
	  ,v.attribute11
	  ,v.attribute12
	  ,v.attribute13
	  ,v.attribute14
	  ,v.attribute15
	  ,v.attribute16
	  ,v.attribute17
	  ,v.attribute18
	  ,v.attribute19
	  ,v.attribute20
	  ,v.professional_area
	  ,v.employee_flag
	  ,v.contractor_flag
	  ,v.employment_category
	  ,v.travel_percentage
	  ,v.work_at_home
	  ,v.min_salary
	  ,v.max_salary
	  ,v.salary_currency
	  ,v.salary_period
	  ,v.min_qual_level_id
	  ,v.max_qual_level_id
      ,v.recruiter_full_name
      ,v.manager_full_name
	  ,v.job_title
      ,v.posting_name
	  ,v.posting_job_title
	  ,v.posting_org_name
	  ,v.posting_org_description
	  ,v.posting_brief_description
	  ,v.posting_detailed_description
	  ,v.posting_job_requirements
	  ,case when v.transaction_id is null 
	        then v.posting_additional_details
	   else 
	       (select posting_additional_details
		   FROM   XMLTable
                     ('//IrcPostingContentsVlEORow'
                      PASSING xmltype(hat1.transaction_document)
                      COLUMNS posting_additional_details                varchar2(4000)          path '//AdditionalDetails'))
	   end posting_additional_details
	  ,case when v.transaction_id is null 
	        then v.posting_how_to_apply
	   else 
	       (select posting_how_to_apply
		   FROM   XMLTable
                     ('//IrcPostingContentsVlEORow'
                      PASSING xmltype(hat1.transaction_document)
                      COLUMNS posting_how_to_apply                      varchar2(4000)          path '//HowToApply'))
	   end posting_how_to_apply
	  ,v.image_url
	  ,v.display_recruiter_info
	  ,case when v.transaction_id is null
            then cast
                  (multiset
                   (SELECT xxas_irc_variable_comp_ele
                           (ivce.vacancy_id
                           ,ivce.variable_comp_lookup
                           ,hrlvc.meaning
                           ,ivce.object_version_number
                           ,ivce.last_update_date
                           ,ivce.last_updated_by
                           ,ivce.last_update_login
                           ,ivce.created_by
                           ,ivce.creation_date)
                    FROM   irc_variable_comp_elements ivce
                          ,hr_lookups hrlvc
                    WHERE  ivce.vacancy_id = v.vacancy_id
                    AND    hrlvc.lookup_type = 'IRC_VARIABLE_COMP_ELEMENT'
                    AND    hrlvc.lookup_code = ivce.variable_comp_lookup
                    ) AS xxas_irc_variable_comp_ele_tbl)
        else
        cast
             (multiset
               (SELECT xxas_irc_variable_comp_ele
                         (ivce.vacancy_id
                         ,ivce.variable_comp_lookup
                         ,xxas_util_pkg.decode_lookup('IRC_VARIABLE_COMP_ELEMENT', ivce.variable_comp_lookup)
                         ,ivce.object_version_number
                         ,xxas_xml_util_pkg.oafstring_to_date(ivce.last_update_date)
                         ,ivce.last_updated_by
                         ,ivce.last_update_login
                         ,ivce.created_by
                         ,xxas_xml_util_pkg.oafstring_to_date(ivce.creation_date))
                FROM   XMLTable
                         ('//IrcVariableCompElementsEORow'
                         PASSING xmltype(hat1.transaction_document)
                         COLUMNS vacancy_id                      NUMBER          path 'VacancyId'
                                ,variable_comp_lookup            VARCHAR2(30)    path 'VariableCompLookup'
                                ,object_version_number           NUMBER          path 'ObjectVersionNumber'
                                ,last_update_date                VARCHAR2(30)    path 'LastUpdateDate'
                                ,last_updated_by                 NUMBER          path 'LastUpdatedBy'
                                ,last_update_login               NUMBER          path 'LastUpdateLogin'
                                ,created_by                      NUMBER          path 'CreatedBy'
                                ,creation_date                   VARCHAR2(30)    path 'CreationDate'
                          ) ivce
             )
             AS xxas_irc_variable_comp_ele_tbl)
        end			 variable_comp_elements
        ,case when v.transaction_id is null
         then cast
               (multiset
                (SELECT xxas_per_competence_ele
                         (pce.competence_element_id
                         ,pce.object_version_number
                         ,pce.type
                         ,hrlct.meaning
                         ,pce.competence_id
                         ,pc.name
                         ,pce.proficiency_level_id
                         ,prlmin.name
                         ,pce.high_proficiency_level_id
                         ,prlmax.name
                         ,pce.mandatory
                         ,hrlmand.meaning
                         ,pce.attribute_category
                         ,pce.attribute1
                         ,pce.attribute2
                         ,pce.attribute3
                         ,pce.attribute4
                         ,pce.attribute5
                         ,pce.attribute6
                         ,pce.attribute7
                         ,pce.attribute8
                         ,pce.attribute9
                         ,pce.attribute10
                         ,pce.attribute11
                         ,pce.attribute12
                         ,pce.attribute13
                         ,pce.attribute14
                         ,pce.attribute15
                         ,pce.attribute16
                         ,pce.attribute17
                         ,pce.attribute18
                         ,pce.attribute19
                         ,pce.attribute20
                         ,pce.last_update_date
                         ,pce.last_updated_by
                         ,pce.last_update_login
                         ,pce.created_by
                         ,pce.creation_date)
                FROM   per_competence_elements pce
                      ,hr_lookups hrlct
                      ,hr_lookups hrlmand
                      ,per_competences pc
                      ,per_rating_levels_vl prlmin
                      ,per_rating_levels_vl prlmax
                      ,per_rating_levels_vl prldes
                WHERE  pce.object_id = v.vacancy_id
                AND    pce.object_name = 'VACANCY'
                AND    hrlct.lookup_type = 'COMPETENCE_ELEMENT_TYPE'
                AND    hrlct.lookup_code = pce.type
                AND    hrlmand.lookup_type = 'IRC_SKILL_DESIRABILITY'
                AND    hrlmand.lookup_code = pce.mandatory
                AND    pce.competence_id = pc.competence_id(+)
                AND    pce.proficiency_level_id = prlmin.rating_level_id(+)
                AND    pce.high_proficiency_level_id = prlmax.rating_level_id(+)
                AND    pce.rating_level_id = prldes.rating_level_id(+)
             ) AS xxas_per_competence_ele_tbl) 
		else
		    cast
             (multiset
               (SELECT xxas_per_competence_ele
                         (pce.competence_element_id
                         ,pce.object_version_number
                         ,pce.type
                         ,xxas_util_pkg.decode_lookup('COMPETENCE_ELEMENT_TYPE', pce.type)
                         ,pce.competence_id
                         ,pc.name
                         ,pce.proficiency_level_id
                         ,prlmin.name
                         ,pce.high_proficiency_level_id
                         ,prlmax.name
                         ,pce.mandatory
                         ,xxas_util_pkg.decode_lookup('IRC_SKILL_DESIRABILITY', pce.mandatory)
                         ,pce.attribute_category
                         ,pce.attribute1
                         ,pce.attribute2
                         ,pce.attribute3
                         ,pce.attribute4
                         ,pce.attribute5
                         ,pce.attribute6
                         ,pce.attribute7
                         ,pce.attribute8
                         ,pce.attribute9
                         ,pce.attribute10
                         ,pce.attribute11
                         ,pce.attribute12
                         ,pce.attribute13
                         ,pce.attribute14
                         ,pce.attribute15
                         ,pce.attribute16
                         ,pce.attribute17
                         ,pce.attribute18
                         ,pce.attribute19
                         ,pce.attribute20
                         ,xxas_xml_util_pkg.oafstring_to_date(pce.last_update_date)
                         ,pce.last_updated_by
                         ,pce.last_update_login
                         ,pce.created_by
                         ,xxas_xml_util_pkg.oafstring_to_date(pce.creation_date))
                FROM   XMLTable
                         ('//VacancyCompetenceElementsEORow'
                         PASSING xmltype(hat1.transaction_document)
                         COLUMNS competence_element_id           NUMBER          path 'CompetenceElementId'
                                ,object_version_number           NUMBER          path 'ObjectVersionNumber'
                                ,type                            VARCHAR2(30)    path 'Type'
                                ,competence_id                   NUMBER          path 'CompetenceId'
                                ,proficiency_level_id            NUMBER          path 'ProficiencyLevelId'
                                ,high_proficiency_level_id       NUMBER          path 'HighProficiencyLevelId'
                                ,mandatory                       VARCHAR2(30)    path 'Mandatory'
                                ,attribute_category              VARCHAR2(30)    path 'AttributeCategory'
                                ,attribute1                      VARCHAR2(150)   path 'Attribute1'
                                ,attribute2                      VARCHAR2(150)   path 'Attribute2'
                                ,attribute3                      VARCHAR2(150)   path 'Attribute3'
                                ,attribute4                      VARCHAR2(150)   path 'Attribute4'
                                ,attribute5                      VARCHAR2(150)   path 'Attribute5'
                                ,attribute6                      VARCHAR2(150)   path 'Attribute6'
                                ,attribute7                      VARCHAR2(150)   path 'Attribute7'
                                ,attribute8                      VARCHAR2(150)   path 'Attribute8'
                                ,attribute9                      VARCHAR2(150)   path 'Attribute9'
                                ,attribute10                     VARCHAR2(150)   path 'Attribute10'
                                ,attribute11                     VARCHAR2(150)   path 'Attribute11'
                                ,attribute12                     VARCHAR2(150)   path 'Attribute12'
                                ,attribute13                     VARCHAR2(150)   path 'Attribute13'
                                ,attribute14                     VARCHAR2(150)   path 'Attribute14'
                                ,attribute15                     VARCHAR2(150)   path 'Attribute15'
                                ,attribute16                     VARCHAR2(150)   path 'Attribute16'
                                ,attribute17                     VARCHAR2(150)   path 'Attribute17'
                                ,attribute18                     VARCHAR2(150)   path 'Attribute18'
                                ,attribute19                     VARCHAR2(150)   path 'Attribute19'
                                ,attribute20                     VARCHAR2(150)   path 'Attribute20'
                                ,last_update_date                VARCHAR2(30)    path 'LastUpdateDate'
                                ,last_updated_by                 NUMBER          path 'LastUpdatedBy'
                                ,last_update_login               NUMBER          path 'LastUpdateLogin'
                                ,created_by                      NUMBER          path 'CreatedBy'
                                ,creation_date                   VARCHAR2(30)    path 'CreationDate'
                          ) pce
                      ,per_competences pc
                      ,per_rating_levels_vl prlmin
                      ,per_rating_levels_vl prlmax
                WHERE  pce.competence_id = pc.competence_id(+)
                AND    pce.proficiency_level_id = prlmin.rating_level_id(+)
                AND    pce.high_proficiency_level_id = prlmax.rating_level_id(+)
             )
             AS xxas_per_competence_ele_tbl)
		end competence_elements
      ,case when v.transaction_id is null
            then cast
                 (multiset
                  (SELECT xxas_irc_rec_team_member
                           (itm.rec_team_member_id
                           ,itm.person_id
                           ,itm.party_id
                           ,itm.vacancy_id
                           ,itm.job_id
                           ,itm.start_date
                           ,itm.end_date
                           ,itm.update_allowed
                           ,itm.delete_allowed
                           ,itm.object_version_number
                           ,itm.last_update_date
                           ,itm.last_updated_by
                           ,itm.last_update_login
                           ,itm.created_by
                           ,itm.creation_date)
                   FROM   irc_rec_team_members ITM
                   WHERE  ITM.vacancy_id = v.vacancy_id
                  ) AS xxas_irc_rec_team_member_tbl) 
		else 
		cast
             (multiset
               (SELECT xxas_irc_rec_team_member
                         (itm.rec_team_member_id
                         ,itm.person_id
                         ,itm.party_id
                         ,itm.vacancy_id
                         ,itm.job_id
                         ,itm.start_date
                         ,itm.end_date
                         ,itm.update_allowed
                         ,itm.delete_allowed
                         ,itm.object_version_number
                         ,xxas_xml_util_pkg.oafstring_to_date(itm.last_update_date)
                         ,itm.last_updated_by
                         ,itm.last_update_login
                         ,itm.created_by
                         ,xxas_xml_util_pkg.oafstring_to_date(itm.creation_date))
                FROM   XMLTable
                         ('//IrcRecTeamMembersEORow'
                         PASSING xmltype(hat1.transaction_document)
                         COLUMNS rec_team_member_id              NUMBER          path 'RecTeamMemberId'
                                ,party_id                        NUMBER          path 'PartyId'
                                ,vacancy_id                      NUMBER          path 'VacancyId'
                                ,job_id                          NUMBER          path 'JobId'
                                ,start_date                      DATE            path 'StartDate'
                                ,end_date                        DATE            path 'EndDate'
                                ,update_allowed                  VARCHAR2(30)    path 'UpdateAllowed'
                                ,delete_allowed                  VARCHAR2(30)    path 'DeleteAllowed'
                                ,object_version_number           NUMBER          path 'ObjectVersionNumber'
                                ,person_id                       NUMBER          path 'PersonId'
                                ,last_update_date                VARCHAR2(30)    path 'LastUpdateDate'
                                ,last_updated_by                 NUMBER          path 'LastUpdatedBy'
                                ,last_update_login               NUMBER          path 'LastUpdateLogin'
                                ,created_by                      NUMBER          path 'CreatedBy'
                                ,creation_date                   VARCHAR2(30)    path 'CreationDate'
                          ) itm
             )
             AS xxas_irc_rec_team_member_tbl)
		end team_members
      ,case when v.transaction_id is null
            then cast
                 (multiset
                  (SELECT xxas_irc_agency_vacancy
                         (iav.agency_vacancy_id
                         ,iav.agency_id
                         ,pov.vendor_name
                         ,iav.vacancy_id
                         ,iav.start_date
                         ,iav.end_date
                         ,iav.max_allowed_applicants
                         ,xxas_util_pkg.flag_to_boolean(iav.manage_applicants_allowed)
                         ,iav.attribute_category
                         ,iav.attribute1
                         ,iav.attribute2
                         ,iav.attribute3
                         ,iav.attribute4
                         ,iav.attribute5
                         ,iav.attribute6
                         ,iav.attribute7
                         ,iav.attribute8
                         ,iav.attribute9
                         ,iav.attribute10
                         ,iav.attribute11
                         ,iav.attribute12
                         ,iav.attribute13
                         ,iav.attribute14
                         ,iav.attribute15
                         ,iav.attribute16
                         ,iav.attribute17
                         ,iav.attribute18
                         ,iav.attribute19
                         ,iav.attribute20
                         ,iav.attribute21
                         ,iav.attribute22
                         ,iav.attribute23
                         ,iav.attribute24
                         ,iav.attribute25
                         ,iav.attribute26
                         ,iav.attribute27
                         ,iav.attribute28
                         ,iav.attribute29
                         ,iav.attribute30
                         ,iav.object_version_number
                         ,iav.last_update_date
                         ,iav.last_updated_by
                         ,iav.last_update_login
                         ,iav.created_by
                         ,iav.creation_date)
                FROM   irc_agency_vacancies iav
                      ,po_vendors pov
                WHERE  iav.vacancy_id = v.vacancy_id
                AND    iav.agency_id = pov.vendor_id
             )
             AS xxas_irc_agency_vacancy_tbl) 
		else
		    cast
             (multiset
               (SELECT xxas_irc_agency_vacancy
                         (iav.agency_vacancy_id
                         ,iav.agency_id
                         ,pov.vendor_name
                         ,iav.vacancy_id
                         ,iav.start_date
                         ,iav.end_date
                         ,iav.max_allowed_applicants
                         ,xxas_util_pkg.flag_to_boolean(iav.manage_applicants_allowed)
                         ,iav.attribute_category
                         ,iav.attribute1
                         ,iav.attribute2
                         ,iav.attribute3
                         ,iav.attribute4
                         ,iav.attribute5
                         ,iav.attribute6
                         ,iav.attribute7
                         ,iav.attribute8
                         ,iav.attribute9
                         ,iav.attribute10
                         ,iav.attribute11
                         ,iav.attribute12
                         ,iav.attribute13
                         ,iav.attribute14
                         ,iav.attribute15
                         ,iav.attribute16
                         ,iav.attribute17
                         ,iav.attribute18
                         ,iav.attribute19
                         ,iav.attribute20
                         ,iav.attribute21
                         ,iav.attribute22
                         ,iav.attribute23
                         ,iav.attribute24
                         ,iav.attribute25
                         ,iav.attribute26
                         ,iav.attribute27
                         ,iav.attribute28
                         ,iav.attribute29
                         ,iav.attribute30
                         ,iav.object_version_number
                         ,xxas_xml_util_pkg.oafstring_to_date(iav.last_update_date)
                         ,iav.last_updated_by
                         ,iav.last_update_login
                         ,iav.created_by
                         ,xxas_xml_util_pkg.oafstring_to_date(iav.creation_date))
                FROM   XMLTable
                         ('//IrcAgencyVacanciesEORow'
                         PASSING xmltype(hat1.transaction_document)
                         COLUMNS agency_vacancy_id               NUMBER          path 'AgencyVacancyId'
                                ,agency_id                       NUMBER          path 'AgencyId'
                                ,vacancy_id                      NUMBER          path 'VacancyId'
                                ,start_date                      DATE            path 'StartDate'
                                ,end_date                        DATE            path 'EndDate'
                                ,max_allowed_applicants          NUMBER          path 'MaxAllowedApplicants'
                                ,manage_applicants_allowed       VARCHAR2(30)    path 'ManageApplicantsAllowed'
                                ,attribute_category              VARCHAR2(30)    path 'AttributeCategory'
                                ,attribute1                      VARCHAR2(150)   path 'Attribute1'
                                ,attribute2                      VARCHAR2(150)   path 'Attribute2'
                                ,attribute3                      VARCHAR2(150)   path 'Attribute3'
                                ,attribute4                      VARCHAR2(150)   path 'Attribute4'
                                ,attribute5                      VARCHAR2(150)   path 'Attribute5'
                                ,attribute6                      VARCHAR2(150)   path 'Attribute6'
                                ,attribute7                      VARCHAR2(150)   path 'Attribute7'
                                ,attribute8                      VARCHAR2(150)   path 'Attribute8'
                                ,attribute9                      VARCHAR2(150)   path 'Attribute9'
                                ,attribute10                     VARCHAR2(150)   path 'Attribute10'
                                ,attribute11                     VARCHAR2(150)   path 'Attribute11'
                                ,attribute12                     VARCHAR2(150)   path 'Attribute12'
                                ,attribute13                     VARCHAR2(150)   path 'Attribute13'
                                ,attribute14                     VARCHAR2(150)   path 'Attribute14'
                                ,attribute15                     VARCHAR2(150)   path 'Attribute15'
                                ,attribute16                     VARCHAR2(150)   path 'Attribute16'
                                ,attribute17                     VARCHAR2(150)   path 'Attribute17'
                                ,attribute18                     VARCHAR2(150)   path 'Attribute18'
                                ,attribute19                     VARCHAR2(150)   path 'Attribute19'
                                ,attribute20                     VARCHAR2(150)   path 'Attribute20'
                                ,attribute21                     VARCHAR2(150)   path 'Attribute21'
                                ,attribute22                     VARCHAR2(150)   path 'Attribute22'
                                ,attribute23                     VARCHAR2(150)   path 'Attribute23'
                                ,attribute24                     VARCHAR2(150)   path 'Attribute24'
                                ,attribute25                     VARCHAR2(150)   path 'Attribute25'
                                ,attribute26                     VARCHAR2(150)   path 'Attribute26'
                                ,attribute27                     VARCHAR2(150)   path 'Attribute27'
                                ,attribute28                     VARCHAR2(150)   path 'Attribute28'
                                ,attribute29                     VARCHAR2(150)   path 'Attribute29'
                                ,attribute30                     VARCHAR2(150)   path 'Attribute30'
                                ,object_version_number           NUMBER          path 'ObjectVersionNumber'
                                ,last_update_date                VARCHAR2(30)    path 'LastUpdateDate'
                                ,last_updated_by                 NUMBER          path 'LastUpdatedBy'
                                ,last_update_login               NUMBER          path 'LastUpdateLogin'
                                ,created_by                      NUMBER          path 'CreatedBy'
                                ,creation_date                   VARCHAR2(30)    path 'CreationDate'
                          ) iav
                      ,po_vendors pov
                WHERE  iav.agency_id = pov.vendor_id(+)
             )
             AS xxas_irc_agency_vacancy_tbl)
		end agency_vacancies
	  ,case when v.transaction_id is null
            then cast
                 (multiset
                  (SELECT xxas_per_rec_activity
                         (pra.recruitment_activity_id
                         ,praf.recruitment_activity_for_id
                         ,v.vacancy_id
                         ,pra.date_start
                         ,pra.date_end
                         ,pra.name
                         ,pra.posting_content_id
                         ,pra.recruiting_site_id
                         ,irs.site_name
                         ,pra.object_version_number
                         ,praf.object_version_number
                         ,pra.last_update_date
                         ,pra.last_updated_by
                         ,pra.last_update_login
                         ,pra.created_by
                         ,pra.creation_date)
                FROM   per_recruitment_activity_for praf
                      ,per_recruitment_activities pra
                      ,irc_all_recruiting_sites_vl irs
                WHERE  praf.vacancy_id = v.vacancy_id
                AND    praf.recruitment_activity_id = pra.recruitment_activity_id
                AND    pra.recruiting_site_id = irs.recruiting_site_id
             )
             AS xxas_per_rec_activity_tbl) 
		else
		    cast
             (multiset
               (SELECT xxas_per_rec_activity
                         (pra.recruitment_activity_id
                         ,null --recruitment_activity_for_id
                         ,v.vacancy_id
                         ,xxas_xml_util_pkg.oafstring_to_date(pra.date_start)
                         ,xxas_xml_util_pkg.oafstring_to_date(pra.date_end)
                         ,pra.name
                         ,pra.posting_content_id
                         ,pra.recruiting_site_id
                         ,irs.site_name
                         ,pra.object_version_number
                         ,null --for_object_version_number
                         ,xxas_xml_util_pkg.oafstring_to_date(pra.last_update_date)
                         ,pra.last_updated_by
                         ,pra.last_update_login
                         ,pra.created_by
                         ,xxas_xml_util_pkg.oafstring_to_date(pra.creation_date))
                FROM   XMLTable
                         ('//PerRecruitmentActivitiesEORow'
                         PASSING xmltype(hat1.transaction_document)
                         COLUMNS recruitment_activity_id         NUMBER          path 'RecruitmentActivityId'
                                ,date_start                      VARCHAR2(30)    path 'DateStart'
                                ,date_end                        VARCHAR2(30)    path 'DateEnd'                               
                                ,name                            VARCHAR2(30)    path 'Name'
                                ,posting_content_id              NUMBER          path 'PostingContentId' 
                                ,recruiting_site_id              NUMBER          path 'RecruitingSiteId' 
                                ,object_version_number           NUMBER          path 'ObjectVersionNumber'
                                ,last_update_date                VARCHAR2(30)    path 'LastUpdateDate'
                                ,last_updated_by                 NUMBER          path 'LastUpdatedBy'
                                ,last_update_login               NUMBER          path 'LastUpdateLogin'
                                ,created_by                      NUMBER          path 'CreatedBy'
                                ,creation_date                   VARCHAR2(30)    path 'CreationDate'
                          ) pra
                      ,irc_all_recruiting_sites_vl irs
                WHERE  pra.recruiting_site_id = irs.recruiting_site_id(+)
             )
             AS xxas_per_rec_activity_tbl)
		end recruitment_activities
	  ,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.entity_name IN('IRC_EXT_VAC_APPROVED', 'IRC_INT_VAC_APPROVED','IRC_EXT_VAC','IRC_INT_VAC')
                 AND    v.vacancy_id IS NOT NULL 
                 AND    a.pk1_value IS NOT NULL 
                 AND    a.pk1_value = to_char(v.vacancy_id)
             )
             AS xxas_attachment_table) attachments
	  ,xxas_selfservice_workflow_pkg.get_approvers(v.transaction_id,800,'IRCVACAPPROVAL') approvers
      ,v.num_applied
      ,v.num_review
      ,v.num_interview
      ,v.num_offer
      ,v.num_accept
      ,v.person_id
	  ,v.manager_id
	  ,v.recruiter_id
      ,v.transaction_id
	  ,v.update_allowed
	  ,v.delete_allowed
	  ,nvl((SELECT 'true' 
            FROM   PER_RECRUITMENT_ACTIVITY_FOR a,
                   PER_RECRUITMENT_ACTIVITIES b,
                   IRC_ALL_RECRUITING_SITES c
            WHERE  a.VACANCY_ID = v.vacancy_id
            AND    a.RECRUITMENT_ACTIVITY_ID = b.RECRUITMENT_ACTIVITY_ID
            AND    c.recruiting_site_id = b.recruiting_site_id
            AND    c.internal = 'Y'),'false')  internal
      ,nvl((SELECT 'true' 
            FROM   PER_RECRUITMENT_ACTIVITY_FOR a,
                   PER_RECRUITMENT_ACTIVITIES b,
                   IRC_ALL_RECRUITING_SITES c
            WHERE  a.VACANCY_ID = v.vacancy_id
            AND    a.RECRUITMENT_ACTIVITY_ID = b.RECRUITMENT_ACTIVITY_ID
            AND    c.recruiting_site_id = b.recruiting_site_id
            AND    c.external = 'Y'),'false')  external
FROM (( SELECT  PAV.name                    vacancy_name,
				PAV.date_from               date_from,
				PAV.date_to                 date_to,
                PAV.business_group_id       business_group_id,
				PAV.vacancy_id              vacancy_id,
				HRL.meaning                 status_code,
				CASE HRL.meaning
                     WHEN 'Open'     THEN 'positive'
                     WHEN 'Hold'     THEN 'energized'
                     WHEN 'Closed'   THEN 'assertive'
                     WHEN 'Pending'  THEN 'energized'
                     WHEN 'Approved' THEN 'balanced'
					 WHEN 'Filled'   THEN 'positive'
					 ELSE '#C0C0C0'
                END                             status_color,
				org.name                        org_name,
				pj.name                         job_name,
			--	PAV.number_of_openings          budget_value,
                PAV.budget_measurement_value    budget_value,
				PAV.last_update_date            last_update_date,
				PAV.location_id                 location_id,
				PAV.position_id                 position_id,
				PAV.job_id                      job_id,
				PAV.grade_id                    grade_id,
				PAV.organization_id             organization_id,
                PAV.assessment_id               assessment_id,
				PAV.security_method             security_method,
				PAV.attribute_category          attribute_category,
                PAV.attribute1                  attribute1,
                PAV.attribute2                  attribute2,
                PAV.attribute3                  attribute3,
                PAV.attribute4                  attribute4,
                PAV.attribute5                  attribute5,
                PAV.attribute6                  attribute6,
                PAV.attribute7                  attribute7,
                PAV.attribute8                  attribute8,
                PAV.attribute9                  attribute9,
                PAV.attribute10                 attribute10,
                PAV.attribute11                 attribute11,
                PAV.attribute12                 attribute12,
                PAV.attribute13                 attribute13,
                PAV.attribute14                 attribute14,
                PAV.attribute15                 attribute15,
                PAV.attribute16                 attribute16,
                PAV.attribute17                 attribute17,
                PAV.attribute18                 attribute18,
                PAV.attribute19                 attribute19,
                PAV.attribute20                 attribute20,
				ISC.professional_area           professional_area,
				ISC.employee                    employee_flag,
                ISC.contractor                  contractor_flag,
                ISC.employment_category         employment_category,
				ISC.travel_percentage           travel_percentage,
				ISC.work_at_home                work_at_home,
				ISC.min_salary                  min_salary,
                ISC.max_salary                  max_salary,
                ISC.salary_currency             salary_currency,
                ISC.salary_period               salary_period,
				ISC.min_qual_level              min_qual_level_id,
				ISC.max_qual_level              max_qual_level_id,
				IPC.recruiter_full_name         recruiter_full_name,
				IPC.manager_full_name           manager_full_name,
				IPC.job_title                   job_title,
				IPC.name                        title,
				IPC.name                        posting_name,
				IPC.job_title                   posting_job_title,
				IPC.org_name                    posting_org_name,
				dbms_lob.substr(IPC.org_description,4000,1)         posting_org_description,
                dbms_lob.substr(IPC.brief_description,4000,1)       posting_brief_description,
                dbms_lob.substr(IPC.detailed_description,4000,1)    posting_detailed_description,
                dbms_lob.substr(IPC.job_requirements,4000,1)        posting_job_requirements,
                dbms_lob.substr(IPC.additional_details,4000,1)      posting_additional_details,
                dbms_lob.substr(IPC.how_to_apply,4000,1)            posting_how_to_apply,
                dbms_lob.substr(IPC.image_url,4000,1)               image_url,
				IPC.display_recruiter_info  display_recruiter_info,
                PAV.manager_id              person_id,
				PAV.manager_id              manager_id,
				PAV.recruiter_id            recruiter_id,
                null                        transaction_id,
				xxas_vacancy_pkg.get_vacancy_applicant_status(PAV.vacancy_id,'ALL') num_applied,
				xxas_vacancy_pkg.get_vacancy_applicant_status(PAV.vacancy_id,'ACTIVE_APL') num_review,
				xxas_vacancy_pkg.get_vacancy_applicant_status(PAV.vacancy_id,'INTERVIEW') num_interview,
				xxas_vacancy_pkg.get_vacancy_applicant_status(PAV.vacancy_id,'OFFER') num_offer,
				xxas_vacancy_pkg.get_vacancy_applicant_status(PAV.vacancy_id,'ACCEPTED') num_accept,
				'N'                         update_allowed,
				'N'                         delete_allowed
        FROM    per_all_vacancies            PAV,
				per_requisitions             PR,
				irc_search_criteria          ISC,
				irc_posting_contents_vl      IPC,
				hr_all_organization_units_vl ORG,
				per_jobs_vl                  PJ,
				hr_lookups                   HRL
		WHERE   PAV.requisition_id       =  PR.requisition_id
		AND     PAV.primary_posting_id   =  IPC.posting_content_id(+)
		AND     PAV.vacancy_id           =  ISC.object_id(+)
        AND     PAV.organization_id      =  org.organization_id(+)
		AND     HRL.lookup_type          =  'VACANCY_STATUS'
        AND     HRL.lookup_code          =  PAV.status
        AND     PAV.job_id               =  pj.job_id(+)
		AND     ISC.object_type(+)       =  'VACANCY'
        AND     NOT EXISTS(SELECT '1'
                           FROM  hr_api_transactions ht
                           WHERE ht.transaction_ref_table = 'PER_ALL_VACANCIES'
                           AND ht.status NOT              IN ('D', 'E', 'W', 'AC')
                           AND ht.transaction_ref_id      = PAV.vacancy_id 
                       )
		AND     xxas_com_sec_pkg.test_instance('XXAS_DATA_VAC_VIEW', pav.vacancy_id) = 'Y'
	  )
UNION
      (select   PAV.name                    vacancy_name,
				PAV.date_from               date_from,
				PAV.date_to                 date_to,
                PAV.business_group_id       business_group_id,
				PAV.vacancy_id              vacancy_id,
				HRL.meaning                 status_code,
				CASE HRL.meaning
                     WHEN 'Open'     THEN 'positive'
                     WHEN 'Hold'     THEN 'energized'
                     WHEN 'Closed'   THEN 'assertive'
                     WHEN 'Pending'  THEN 'energized'
                     WHEN 'Approved' THEN 'balanced'
					 WHEN 'Filled'   THEN 'positive'
					 ELSE '#C0C0C0'
                END                             status_color,
				org.name                        org_name,
				pj.name                         job_name,
			--	PAV.number_of_openings          budget_value,
                PAV.budget_measurement_value    budget_value,
				PAV.last_update_date            last_update_date,
				PAV.location_id                 location_id,
				PAV.position_id                 position_id,
				PAV.job_id                      job_id,
				PAV.grade_id                    grade_id,
				PAV.organization_id             organization_id,
                PAV.assessment_id               assessment_id,
				PAV.security_method             security_method,
				PAV.attribute_category          attribute_category,
                PAV.attribute1                  attribute1,
                PAV.attribute2                  attribute2,
                PAV.attribute3                  attribute3,
                PAV.attribute4                  attribute4,
                PAV.attribute5                  attribute5,
                PAV.attribute6                  attribute6,
                PAV.attribute7                  attribute7,
                PAV.attribute8                  attribute8,
                PAV.attribute9                  attribute9,
                PAV.attribute10                 attribute10,
                PAV.attribute11                 attribute11,
                PAV.attribute12                 attribute12,
                PAV.attribute13                 attribute13,
                PAV.attribute14                 attribute14,
                PAV.attribute15                 attribute15,
                PAV.attribute16                 attribute16,
                PAV.attribute17                 attribute17,
                PAV.attribute18                 attribute18,
                PAV.attribute19                 attribute19,
                PAV.attribute20                 attribute20,
				ISC.professional_area           professional_area,
				ISC.employee                    employee_flag,
                ISC.contractor                  contractor_flag,
                ISC.employment_category         employment_category,
				ISC.travel_percentage           travel_percentage,
				ISC.work_at_home                work_at_home,
				ISC.min_salary                  min_salary,
                ISC.max_salary                  max_salary,
                ISC.salary_currency             salary_currency,
                ISC.salary_period               salary_period,
				ISC.min_qual_level              min_qual_level_id,
				ISC.max_qual_level              max_qual_level_id,
				IPC.recruiter_full_name         recruiter_full_name,
				IPC.manager_full_name           manager_full_name,
				IPC.job_title                   job_title,
				IPC.name                        title,
				IPC.name                        posting_name,
				IPC.job_title                   posting_job_title,
				IPC.org_name                    posting_org_name,
				dbms_lob.substr(IPC.org_description,4000,1)         posting_org_description,
                dbms_lob.substr(IPC.brief_description,4000,1)       posting_brief_description,
                dbms_lob.substr(IPC.detailed_description,4000,1)    posting_detailed_description,
                dbms_lob.substr(IPC.job_requirements,4000,1)        posting_job_requirements,
                dbms_lob.substr(IPC.additional_details,4000,1)      posting_additional_details,
                dbms_lob.substr(IPC.how_to_apply,4000,1)            posting_how_to_apply,
                dbms_lob.substr(IPC.image_url,4000,1)               image_url,
				IPC.display_recruiter_info  display_recruiter_info,
                TM.person_id                person_id,
				PAV.manager_id              manager_id,
				PAV.recruiter_id            recruiter_id,
                null                        transaction_id,
				xxas_vacancy_pkg.get_vacancy_applicant_status(PAV.vacancy_id,'ALL') num_applied,
				xxas_vacancy_pkg.get_vacancy_applicant_status(PAV.vacancy_id,'ACTIVE_APL') num_review,
				xxas_vacancy_pkg.get_vacancy_applicant_status(PAV.vacancy_id,'INTERVIEW') num_interview,
				xxas_vacancy_pkg.get_vacancy_applicant_status(PAV.vacancy_id,'OFFER') num_offer,
				xxas_vacancy_pkg.get_vacancy_applicant_status(PAV.vacancy_id,'ACCEPTED') num_accept,
				'N'     update_allowed,
				'N'     delete_allowed
       FROM     per_all_vacancies           PAV,
				irc_rec_team_members        TM,
				per_requisitions            PR,
				irc_search_criteria         ISC,
				irc_posting_contents_vl     IPC,
				hr_all_organization_units_vl ORG,
				per_jobs_vl                  PJ,
				hr_lookups                   HRL
	   WHERE    PAV.vacancy_id           =  TM.vacancy_id
       AND      TM.person_id             =  fnd_global.employee_id
	   AND      PAV.requisition_id       =  PR.requisition_id
	   AND      PAV.primary_posting_id   =  IPC.posting_content_id(+)
	   AND      PAV.vacancy_id           =  ISC.object_id(+)
	   AND      PAV.organization_id      =  org.organization_id(+)
	   AND      PAV.job_id               =  pj.job_id(+)
	   AND      HRL.lookup_type          =  'VACANCY_STATUS'
       AND      HRL.lookup_code          =  PAV.status
       AND      ISC.object_type(+)       =  'VACANCY'
       AND      NOT EXISTS(SELECT '1'
                           FROM  hr_api_transactions ht
                           WHERE ht.transaction_ref_table = 'PER_ALL_VACANCIES'
                           AND ht.status NOT              IN ('D', 'E', 'W', 'AC')
                           AND ht.transaction_ref_id      = PAV.vacancy_id 
                       )
	   AND      trunc(sysdate) BETWEEN
				   nvl(TM.start_date, trunc(sysdate)) AND nvl(TM.end_date, trunc(sysdate))
	   AND      xxas_com_sec_pkg.test_instance('XXAS_DATA_VAC_VIEW', pav.vacancy_id) = 'Y'
	  )
UNION
       (SELECT  xxas_xml_node_util_pkg.get_node_value(hat.transaction_id,'Name','//PerAllVacanciesEORow/') vacancy_name,
				xxas_xml_util_pkg.oafstring_to_date(xxas_xml_node_util_pkg.get_node_value(hat.transaction_id,'DateFrom','//PerAllVacanciesEORow/')) date_from,
				xxas_xml_util_pkg.oafstring_to_date(xxas_xml_node_util_pkg.get_node_value(hat.transaction_id,'DateTo','//PerAllVacanciesEORow/')) date_to,
				to_number(xxas_xml_node_util_pkg.get_node_value(hat.transaction_id,'BusinessGroupId','//PerAllVacanciesEORow/')) business_group_id,
                to_number(xxas_xml_node_util_pkg.get_node_value(hat.transaction_id,'VacancyId','//PerAllVacanciesEORow/')) vacancy_id,
				xxas_selfservice_workflow_pkg.decode_status
                     (xxas_selfservice_workflow_pkg.transcode_status(hat.status)) status_code,
				CASE xxas_selfservice_workflow_pkg.transcode_status(hat.status)
                     WHEN 'SFL'      THEN '#999999'
                     WHEN 'RFC'      THEN '#FF0000'
                     WHEN 'REVIEW'   THEN '#999999'
                     WHEN 'PENDING'  THEN 'energized'
                     WHEN 'ERROR'    THEN 'asserive'
                     WHEN 'APPROVED' THEN 'balanced'
                 END status_color,
				org.name org_name,
				pj.name  job_name,
				to_number(xxas_xml_node_util_pkg.get_node_value(hat.transaction_id,'NumberOfOpenings','//PerAllVacanciesEORow/')) budget_value,
--                to_number(xxas_xml_node_util_pkg.get_node_value(hat.transaction_id,'NumberOfOpenings','//PerAllVacanciesEORow/')) budget_measurement_value,
				xxas_xml_util_pkg.oafstring_to_date(xxas_xml_node_util_pkg.get_node_value(hat.transaction_id,'LastUpdateDate','//PerAllVacanciesEORow/')) last_update_date,
				to_number(xxas_xml_node_util_pkg.get_node_value(hat.transaction_id,'LocationId','//PerAllVacanciesEORow/')) location_id,
				to_number(xxas_xml_node_util_pkg.get_node_value(hat.transaction_id,'PositionId','//PerAllVacanciesEORow/')) position_id,
				to_number(xxas_xml_node_util_pkg.get_node_value(hat.transaction_id,'JobId','//PerAllVacanciesEORow/')) job_id,
				to_number(xxas_xml_node_util_pkg.get_node_value(hat.transaction_id,'GradeId','//PerAllVacanciesEORow/')) grade_id,
				to_number(xxas_xml_node_util_pkg.get_node_value(hat.transaction_id,'OrganizationId','//PerAllVacanciesEORow/')) organization_id,
				to_number(xxas_xml_node_util_pkg.get_node_value(hat.transaction_id,'AssessmentId','//PerAllVacanciesEORow/')) assessment_id,
				xxas_xml_node_util_pkg.get_node_value(hat.transaction_id,'SecurityMethod','//PerAllVacanciesEORow/') security_method,
				xxas_xml_node_util_pkg.get_node_value(hat.transaction_id,'AttributeCategory','//PerAllVacanciesEORow/') attribute_category,
				xxas_xml_node_util_pkg.get_node_value(hat.transaction_id,'Attribute1','//PerAllVacanciesEORow/')  attribute1,
				xxas_xml_node_util_pkg.get_node_value(hat.transaction_id,'Attribute2','//PerAllVacanciesEORow/')  attribute2,
				xxas_xml_node_util_pkg.get_node_value(hat.transaction_id,'Attribute3','//PerAllVacanciesEORow/')  attribute3,
				xxas_xml_node_util_pkg.get_node_value(hat.transaction_id,'Attribute4','//PerAllVacanciesEORow/')  attribute4,
				xxas_xml_node_util_pkg.get_node_value(hat.transaction_id,'Attribute5','//PerAllVacanciesEORow/')  attribute5,
				xxas_xml_node_util_pkg.get_node_value(hat.transaction_id,'Attribute6','//PerAllVacanciesEORow/')  attribute6,
				xxas_xml_node_util_pkg.get_node_value(hat.transaction_id,'Attribute7','//PerAllVacanciesEORow/')  attribute7,
				xxas_xml_node_util_pkg.get_node_value(hat.transaction_id,'Attribute8','//PerAllVacanciesEORow/')  attribute8,
				xxas_xml_node_util_pkg.get_node_value(hat.transaction_id,'Attribute9','//PerAllVacanciesEORow/')  attribute9,
				xxas_xml_node_util_pkg.get_node_value(hat.transaction_id,'Attribute10','//PerAllVacanciesEORow/') attribute10,
				xxas_xml_node_util_pkg.get_node_value(hat.transaction_id,'Attribute11','//PerAllVacanciesEORow/') attribute11,
				xxas_xml_node_util_pkg.get_node_value(hat.transaction_id,'Attribute12','//PerAllVacanciesEORow/') attribute12,
				xxas_xml_node_util_pkg.get_node_value(hat.transaction_id,'Attribute13','//PerAllVacanciesEORow/') attribute13,
				xxas_xml_node_util_pkg.get_node_value(hat.transaction_id,'Attribute14','//PerAllVacanciesEORow/') attribute14,
				xxas_xml_node_util_pkg.get_node_value(hat.transaction_id,'Attribute15','//PerAllVacanciesEORow/') attribute15,
				xxas_xml_node_util_pkg.get_node_value(hat.transaction_id,'Attribute16','//PerAllVacanciesEORow/') attribute16,
				xxas_xml_node_util_pkg.get_node_value(hat.transaction_id,'Attribute17','//PerAllVacanciesEORow/') attribute17,
				xxas_xml_node_util_pkg.get_node_value(hat.transaction_id,'Attribute18','//PerAllVacanciesEORow/') attribute18,
				xxas_xml_node_util_pkg.get_node_value(hat.transaction_id,'Attribute19','//PerAllVacanciesEORow/') attribute19,
				xxas_xml_node_util_pkg.get_node_value(hat.transaction_id,'Attribute20','//PerAllVacanciesEORow/') attribute20,
                xxas_xml_node_util_pkg.get_node_value(hat.transaction_id,'ProfessionalArea','//IrcVacancySearchCriteriaEORow/') professional_area,
				xxas_xml_node_util_pkg.get_node_value(hat.transaction_id,'Employee','//IrcVacancySearchCriteriaEORow/') employee_flag,
				xxas_xml_node_util_pkg.get_node_value(hat.transaction_id,'Contractor','//IrcVacancySearchCriteriaEORow/') contractor_flag,
                xxas_xml_node_util_pkg.get_node_value(hat.transaction_id,'EmploymentCategory','//IrcVacancySearchCriteriaEORow/') employment_category,
				to_number(xxas_xml_node_util_pkg.get_node_value(hat.transaction_id,'TravelPercentage','//IrcVacancySearchCriteriaEORow/')) travel_percentage,
				xxas_xml_node_util_pkg.get_node_value(hat.transaction_id,'WorkAtHome','//IrcVacancySearchCriteriaEORow/') work_at_home,
				to_number(xxas_xml_node_util_pkg.get_node_value(hat.transaction_id,'MinSalary','//IrcVacancySearchCriteriaEORow/')) min_salary,
				to_number(xxas_xml_node_util_pkg.get_node_value(hat.transaction_id,'MaxSalary','//IrcVacancySearchCriteriaEORow/')) max_salary,
				xxas_xml_node_util_pkg.get_node_value(hat.transaction_id,'SalaryCurrency','//IrcVacancySearchCriteriaEORow/') salary_currency,
				xxas_xml_node_util_pkg.get_node_value(hat.transaction_id,'SalaryPeriod','//IrcVacancySearchCriteriaEORow/') salary_period,
				to_number(xxas_xml_node_util_pkg.get_node_value(hat.transaction_id,'MinQualLevel','//IrcVacancySearchCriteriaEORow/')) min_qual_level_id,
				to_number(xxas_xml_node_util_pkg.get_node_value(hat.transaction_id,'MaxQualLevel','//IrcVacancySearchCriteriaEORow/')) max_qual_level_id,
				null                        recruiter_full_name,
				null                        manager_full_name,
				xxas_xml_node_util_pkg.get_node_value(hat.transaction_id,'JobTitle','//IrcPostingContentsVlEORow/') job_title,
				xxas_xml_node_util_pkg.get_node_value(hat.transaction_id,'Name','//IrcPostingContentsVlEORow/') title,
				xxas_xml_node_util_pkg.get_node_value(hat.transaction_id,'Name','//IrcPostingContentsVlEORow/') posting_name,
				xxas_xml_node_util_pkg.get_node_value(hat.transaction_id,'JobTitle','//IrcPostingContentsVlEORow/') posting_job_title,
				xxas_xml_node_util_pkg.get_node_value(hat.transaction_id,'OrgName','//IrcPostingContentsVlEORow/') posting_org_name,
				xxas_xml_node_util_pkg.get_node_value(hat.transaction_id,'OrgDescription','//IrcPostingContentsVlEORow/') posting_org_description,
				xxas_xml_node_util_pkg.get_node_value(hat.transaction_id,'BriefDescription','//IrcPostingContentsVlEORow/') posting_brief_description,
				xxas_xml_node_util_pkg.get_node_value(hat.transaction_id,'DetailedDescription','//IrcPostingContentsVlEORow/') posting_detailed_description,
				xxas_xml_node_util_pkg.get_node_value(hat.transaction_id,'JobRequirements','//IrcPostingContentsVlEORow/') posting_job_requirements,
				xxas_xml_node_util_pkg.get_node_value(hat.transaction_id,'AdditionalDetails','//IrcPostingContentsVlEORow/') posting_additional_details,
				xxas_xml_node_util_pkg.get_node_value(hat.transaction_id,'HowToApply','//IrcPostingContentsVlEORow/') how_to_apply,
				xxas_xml_node_util_pkg.get_node_value(hat.transaction_id,'ImageUrl','//IrcPostingContentsVlEORow/') image_url,
				xxas_xml_node_util_pkg.get_node_value(hat.transaction_id,'DisplayRecruiterInfo','//IrcPostingContentsVlEORow/') display_recruiter_info,
				hat.creator_person_id       person_id,
				to_number(xxas_xml_node_util_pkg.get_node_value(hat.transaction_id,'ManagerId','//PerAllVacanciesEORow/')) manager_id,
				to_number(xxas_xml_node_util_pkg.get_node_value(hat.transaction_id,'RecruiterId','//PerAllVacanciesEORow/')) recruiter_id,
                hat.transaction_id          transaction_id,
				xxas_vacancy_pkg.get_vacancy_applicant_status(to_number(xxas_xml_node_util_pkg.get_node_value(hat.transaction_id,'VacancyId','//PerAllVacanciesEORow/')),'ALL') num_applied,
				xxas_vacancy_pkg.get_vacancy_applicant_status(to_number(xxas_xml_node_util_pkg.get_node_value(hat.transaction_id,'VacancyId','//PerAllVacanciesEORow/')),'ACTIVE_APL') num_review,
				xxas_vacancy_pkg.get_vacancy_applicant_status(to_number(xxas_xml_node_util_pkg.get_node_value(hat.transaction_id,'VacancyId','//PerAllVacanciesEORow/')),'INTERVIEW') num_interview,
				xxas_vacancy_pkg.get_vacancy_applicant_status(to_number(xxas_xml_node_util_pkg.get_node_value(hat.transaction_id,'VacancyId','//PerAllVacanciesEORow/')),'OFFER') num_offer,
				xxas_vacancy_pkg.get_vacancy_applicant_status(to_number(xxas_xml_node_util_pkg.get_node_value(hat.transaction_id,'VacancyId','//PerAllVacanciesEORow/')),'ACCEPTED') num_accept,
				Decode(xxas_selfservice_workflow_pkg.decode_status(xxas_selfservice_workflow_pkg.transcode_status(hat.status)),
				'Draft','Y','N')                update_allowed,
				Decode(xxas_selfservice_workflow_pkg.decode_status(xxas_selfservice_workflow_pkg.transcode_status(hat.status)),
				'Draft','Y','N')                delete_allowed
		FROM    HR_API_TRANSACTIONS          hat,
				hr_all_organization_units_vl org,
				per_jobs_vl                  pj
		WHERE   hat.status          NOT IN ('D', 'N', 'W', 'ACTIVE', 'AC')
		AND     hat.transaction_ref_table = 'PER_ALL_VACANCIES'
		AND     org.organization_id(+)    = xxas_xml_node_util_pkg.get_node_value(hat.transaction_id,'OrganizationId','//PerAllVacanciesEORow/')
		AND     pj.job_id(+)           = xxas_xml_node_util_pkg.get_node_value(hat.transaction_id,'JobId','//PerAllVacanciesEORow/')
        AND     xxas_com_sec_pkg.test_instance('XXAS_DATA_TRANS_VIEW', hat.transaction_id) = 'Y'
		)
		) v,
		hr_api_transactions hat1
		WHERE v.transaction_id = hat1.transaction_id (+);   
BEGIN
  xxas_fnd_dictionary_pkg.add_view
   (p_application_short_name => xxas_util_pkg.app
   ,p_view_name              => 'XXAS_HTML_VACANCIES_V'
   ,p_description            => 'Applaud html vacacnies view');
END;
/

COMMIT;
EXIT;