/* $Id$ */
/*============================================================================*
 |                 Copyright (c) 2015 Applaud Solutions UK Ltd                |
 |                             All rights reserved                            |
 *===========================================================================*/

SET VERIFY OFF
SET FEEDBACK OFF
SET ESCAPE ON
WHENEVER OSERROR  EXIT FAILURE ROLLBACK
WHENEVER SQLERROR EXIT FAILURE ROLLBACK

CREATE OR REPLACE VIEW xxas_vacancies_pub_det_v
AS
SELECT                                                                   v."type",
                                                                         v."id",
                                                                         v."title",
                                                                         v."salary",
																		 v."salaryFormatted",
                                                                         --v."latlon",
																		 v."latitude",
																		 v."longitude",
                                     v."distance",
                                     v."distanceText",
                                                                         v."location",
																		 v."locationTitle",
                                                                         v."areaOfOrganization",
                                                                         v."organizationName",
                                                                         v."posted",
                                                                         v."postedFormatted",
                                                                         v."closing",
                                                                         v."closingFormatted",
                                                                         v."href",
                                                                         v.ref,
                                                                         v."refFormatted",
                                                                         v."postedName",
                                                                         v."flag",
                                                                         v."flagColor",
                                                                   --    v."keywords",
                                                                         v."internal",
                                                                         v."external",
                                                                         v."favorites",
				xxas_util_pkg.getXML(xxas_com_fields_list(xxas_com_fields('html','XXAS_VAC_ORG_DESC',IPC.ORG_DESCRIPTION)
												  ,xxas_com_fields('html','XXAS_VAC_BRIEF_DESC',IPC.BRIEF_DESCRIPTION)
												  ,xxas_com_fields('html','XXAS_VAC_DETAILED_DESC',IPC.DETAILED_DESCRIPTION)
												  ,xxas_com_fields('html','XXAS_VAC_JOB_REQ',IPC.JOB_REQUIREMENTS)
												  ,xxas_com_fields('html','XXAS_VAC_ADD_DETAILS',IPC.ADDITIONAL_DETAILS)
												  )
								    )                               "fields",
				xxas_util_pkg.getXML(xxas_com_fields_list(xxas_com_fields('html','XXAS_VAC_ORG_NAME',IPC.org_name)
												  ,xxas_com_fields('html','XXAS_VAC_ATTACHMENTS',xxas_util_pkg.get_attachments(v.vacancy_id,v."internal",v."external"))
												  ,xxas_com_fields('html','XXAS_VAC_COMPETENCES',xxas_util_pkg.get_competences(v.vacancy_id))
												  ,xxas_com_fields('html','XXAS_VAC_HOW_TO_APPLY',IPC.how_to_apply)
												  )
								    )                                    "asideFields",	
									                                     v.vacancy_id             
FROM  xxas_vacancies_pub_v v,
      IRC_POSTING_CONTENTS_VL IPC
WHERE v.POSTING_CONTENT_ID                 = IPC.POSTING_CONTENT_ID;
BEGIN
  xxas_fnd_dictionary_pkg.add_view
   (p_application_short_name => xxas_util_pkg.app
   ,p_view_name              => 'XXAS_VACANCIES_PUB_DET_V'
   ,p_description            => 'Applaud Vacancy Detail for a vacancy for External Site');
END;
/

COMMIT;
EXIT;