/* $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_v
AS
SELECT * 
FROM  (SELECT  /*+ INDEX(LOC HR_LOCATIONS_N1) */
              'vacancies'                                           "type",
              pav.vacancy_id                                        "id",
               nvl(IPC.JOB_TITLE,'IRC' || PAV.VACANCY_ID)           "title",
               /*HL2.MEANING,
               PAV.ATTRIBUTE2,
               PAV.ATTRIBUTE3,                                     
               IVS.EMPLOYEE,
               IVS.CONTRACTOR,              */
               IVS.MIN_SALARY                                       "salary",
               IVS.salary_currency,
               ppd.segment4,
               DECODE(ivs.salary_period
                        ,'ANNUAL'
                        ,xxas_util_pkg.format_currency(ivs.min_salary
                                                      ,IVS.SALARY_CURRENCY
                                                      ,0
                                                      ) || ' yearly'
                        ,xxas_util_pkg.format_currency(ivs.min_salary
                                                      ,IVS.SALARY_CURRENCY
                                                      ) || ' hourly'
                    )                                               "salaryFormatted",
               --xxas_util_pkg.geom_to_latlon_xml(loc.geometry)       "latlon",
               (SELECT to_char(lat.y,'FM99999999999999990D99999','NLS_NUMERIC_CHARACTERS = ''.,''')
                FROM   
                TABLE(sdo_util.getvertices(LOC.geometry)) lat)      "latitude",
               (SELECT to_char(lon.x,'FM99999999999999990D99999','NLS_NUMERIC_CHARACTERS = ''.,''')
                FROM   
                TABLE(sdo_util.getvertices(LOC.geometry)) lon)      "longitude",
               LOC.TOWN_OR_CITY                                     "location",
        xxas_util_pkg.distance_from_user(loc.geometry) "distance",
        xxas_util_pkg.distance_from_user_text(loc.geometry) "distanceText",        
               LOC.location_code                                    "locationTitle",
               nvl(hrl.meaning,'Unknown')                           "areaOfOrganization",
               substr(hapf.name,instr(hapf.name,'.')+1,instr(hapf.name,'.',1,2) - instr(hapf.name,'.')-1) "organizationName",
               xxas_util_pkg.date_to_char(pav.date_from)                               "posted",
               'Posted ' || to_char(pav.date_from,'FMddth Mon')
                         || decode(to_char(pav.date_from,'YYYY')
                                  ,to_char(trunc(sysdate),'YYYY')
                                  ,''
                                  ,' '  || 
                                    to_char(pav.date_from,'YYYY'))  "postedFormatted",
               pav.date_to                                          "closing",
               nvl2(pav.date_to
                    ,'Ends: ' || 
                       to_char(pav.date_to,'FMMon ddth '),'')         "closingFormatted",
               xxas_com_rest_util_pkg.uri_from_app_route('vacancy-detail/'
                                           || PAV.VACANCY_ID
                                                        )           "href",
               PAV.NAME                                               ref,
               'Ref: IRC' || PAV.VACANCY_ID                         "refFormatted",
               IPC.NAME                                             "postedName",
               (CASE 
                 WHEN pav.date_from > trunc(sysdate) -5 then 'NEW'
                 WHEN pav.date_to BETWEEN trunc(sysdate) AND trunc(sysdate) + 2 
                 then 'HURRY'
                end)                                                "flag",
               (CASE 
                 WHEN pav.date_from > trunc(sysdate) -5 then 'energized'
                 WHEN pav.date_to BETWEEN trunc(sysdate) AND trunc(sysdate) + 2 
                  then 'assertive'
                end)                                                "flagColor",
                xxas_util_pkg.strip_out_markup(IPC.JOB_REQUIREMENTS)
                  || to_clob(ipc.JOB_TITLE) 
                  || xxas_util_pkg.strip_out_markup(IPC.BRIEF_DESCRIPTION)  
                  || xxas_util_pkg.strip_out_markup(IPC.DETAILED_DESCRIPTION)
                  || xxas_util_pkg.strip_out_markup(IPC.ADDITIONAL_DETAILS)     
                  --|| xxas_util_pkg.strip_out_markup(IPC.HOW_TO_APPLY)
                  || xxas_util_pkg.strip_out_markup(IPC.BENEFIT_INFO) 
                  || to_clob(PAV.VACANCY_ID)
                  || to_clob(concat('IRC',PAV.VACANCY_ID))          "keywords",
               nvl((SELECT 'true' 
                    FROM   PER_RECRUITMENT_ACTIVITY_FOR a,
                           PER_RECRUITMENT_ACTIVITIES b,
                           IRC_ALL_RECRUITING_SITES c
                    WHERE  a.VACANCY_ID = pav.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 = pav.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",
                --decode(ijbi.person_id,null,'false','true')          "favorites",
               decode((SELECT max(ijbi.person_id)
                       FROM   per_recruitment_activities pra
                             ,per_recruitment_activity_for praf
                             ,irc_job_basket_items ijbi
                       WHERE  praf.vacancy_id = pav.vacancy_id
                       AND    praf.recruitment_activity_id = pra.recruitment_activity_id
                       AND    ijbi.recruitment_activity_id = pra.recruitment_activity_id 
                       AND    ijbi.person_id = fnd_global.employee_id
                       AND    pra.recruiting_site_id = decode(Irc_utilities_pkg.is_internal_person(fnd_global.employee_id,SYSDATE)
                                                             ,'TRUE'
                                                             ,(select recruiting_site_id FROM irc_all_recruiting_sites where internal = 'Y')
                                                             ,(select recruiting_site_id FROM irc_all_recruiting_sites where external = 'Y')
                                                             ))
                     ,null
                     ,'false'
                     ,'true')                                      "favorites",                 
                IPC.POSTING_CONTENT_ID,
                pav.vacancy_id
      FROM     HR_LOOKUPS HRL,
               --HR_LOOKUPS HL2,
               PER_ALL_VACANCIES PAV,
               PER_RECRUITMENT_ACTIVITIES PRA,
               IRC_POSTING_CONTENTS_VL IPC,
               IRC_SEARCH_CRITERIA IVS,
               HR_LOCATIONS_ALL LOC,
               PER_RECRUITMENT_ACTIVITY_FOR PerRecruitmentActivityForEO,
             --  HR_LOOKUPS HLW,
              -- HR_LOOKUPS HLT,
               IRC_ALL_RECRUITING_SITES ias,
               HR_ALL_POSITIONS_F hapf,
               PER_POSITION_DEFINITIONS ppd
        --     irc_job_basket_items ijbi
      WHERE    PAV.STATUS                                     = 'APPROVED'
      AND hapf.position_id(+)                                 = pav.position_id
      AND ppd.position_definition_id(+)                       = hapf.position_definition_id
      AND PAV.VACANCY_ID                                      = PerRecruitmentActivityForEO.VACANCY_ID
      AND PerRecruitmentActivityForEO.RECRUITMENT_ACTIVITY_ID = PRA.RECRUITMENT_ACTIVITY_ID
      AND PRA.POSTING_CONTENT_ID                              = IPC.POSTING_CONTENT_ID
      AND PAV.VACANCY_ID                                      = IVS.OBJECT_ID
      AND IVS.OBJECT_TYPE                                     = 'VACANCY'
      AND HRL.LOOKUP_TYPE(+)                                  = 'IRC_PROFESSIONAL_AREA'
      AND IVS.PROFESSIONAL_AREA                               = HRL.LOOKUP_CODE(+)
     -- AND HL2.LOOKUP_TYPE(+)                                  = 'IRC_EMP_CAT'
      --AND IVS.EMPLOYMENT_CATEGORY                             = HL2.LOOKUP_CODE(+)
     -- AND HLW.LOOKUP_TYPE(+)                                  = 'IRC_WORK_AT_HOME'
      --AND IVS.WORK_AT_HOME                                    = HLW.LOOKUP_CODE(+)
      --AND HLT.LOOKUP_TYPE(+)                                  = 'IRC_TRAVEL_PERCENTAGE'
      --AND IVS.TRAVEL_PERCENTAGE                               = HLT.LOOKUP_CODE(+)
     /* AND ijbi.recruitment_activity_id (+)                    = PerRecruitmentActivityForEO.recruitment_activity_id
      AND ijbi.person_id (+)                                  = fnd_global.employee_id*/
      AND trunc(sysdate)              BETWEEN PRA.date_start AND NVL (PRA.date_end,trunc(sysdate))
      AND loc.location_id(+)                                  = pav.location_id
      AND (pav.date_from                                     <= trunc(sysdate)
          AND ((pav.date_to    IS NOT NULL
          AND pav.date_to      >= trunc(sysdate))
          OR (pav.date_to      IS NULL))
          )
      AND EXISTS
        (SELECT 1 
         FROM per_vacancies vac2 
         WHERE vac2.vacancy_id=pav.vacancy_id
        )
      AND pra.recruiting_site_id                              = ias.recruiting_site_id
      --AND ias.EXTERNAL           = 'Y'
      AND PerRecruitmentActivityForEO.RECRUITMENT_ACTIVITY_ID = (SELECT MAX(a.RECRUITMENT_ACTIVITY_ID) 
                                                                 FROM   PER_RECRUITMENT_ACTIVITY_FOR a,
                                                                        PER_RECRUITMENT_ACTIVITIES b
                                                                 WHERE  a.VACANCY_ID = pav.vacancy_id
                                                                 AND    a.RECRUITMENT_ACTIVITY_ID = b.RECRUITMENT_ACTIVITY_ID)
      --AND trunc(sysdate)              BETWEEN pra.date_start AND NVL (pra.date_end,trunc(sysdate))
      AND (hapf.position_id IS NULL OR trunc(sysdate) BETWEEN hapf.effective_start_date AND hapf.effective_end_date)
      ORDER BY pav.date_from  DESC
              ,pav.date_to    DESC
              ,pav.vacancy_id DESC
      );
BEGIN
  xxas_fnd_dictionary_pkg.add_view
   (p_application_short_name => xxas_util_pkg.app
   ,p_view_name              => 'XXAS_VACANCIES_PUB_V'
   ,p_description            => 'Applaud Vacancy List for External Site');
END;
/

COMMIT;
EXIT;