/* $Id$ */
/*============================================================================*
 |                 Copyright (c) 2014 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_applications_v
AS
SELECT assignment_id
      ,person_id
	  ,"id"            "id"
	  ,"type"          "type"
	  ,"title"         "title"
	  ,"contract"      "contract"
	  ,"salary"        "salary"
	  ,"location"      "location"
	  ,"trainline"     "trainline"
	  ,"closing"       "closing"
	  ,"ref"           "ref"
	  ,"href"          "href"
	  ,"favorites"     "favorites"
	  ,xxas_util_pkg.getXML(xxas_com_fields_list(xxas_com_fields('html',extractvalue(xmlfields,'//contextLine2'),(extractvalue(xmlfields,'//bodyText')))
	                                   ,xxas_com_fields('html',' ',' ')
										 )
								    )                             "fields"					   
	  ,xxas_util_pkg.getXML(xxas_com_fields_list(xxas_com_fields('html',extractvalue(xmlfields,'//asideTitle'),(extractvalue(xmlfields,'//asideText')))
										 ,xxas_com_fields('html',' ',' '))
								    )                             "asideFields"
FROM (
SELECT paaf.assignment_id
      ,paaf.person_id
      ,paaf.assignment_id "id"
      ,'application' "type"
      ,nvl(IPC.JOB_TITLE,'IRC' || PAV.VACANCY_ID)   "title"
      /*,(CASE WHEN (IVS.EMPLOYEE = 'Y' AND IVS.CONTRACTOR = 'Y') THEN
			'Permanent Contract, Fixed Term Contract'
		WHEN (IVS.EMPLOYEE ='Y' AND IVS.CONTRACTOR ='N') THEN
		    'Permanent Contract'
		ELSE		    'Fixed Term Contract'			
	    END)    "contract" */
      ,  DECODE(ivs.salary_period
		    ,'ANNUAL'
			,xxas_util_pkg.format_currency(ivs.min_salary
		                                  ,IVS.SALARY_CURRENCY
										  ,null
										  )
           ,xxas_util_pkg.format_currency(ivs.min_salary
		                                  ,IVS.SALARY_CURRENCY
										  )
		)    "salary"
	  ,decode (hl2.MEANING, 'Either' ,'Full Time, Part Time',hl2.meaning)  "contract"
      ,LOC.location_code "location"
	  ,decode(ijbi.person_id,null,'false','true')          "favorites"
      ,xxas_applicant_events_pkg.get_can_det_train_status_xml(paaf.assignment_id) "trainline"
      ,pav.date_to "closing"
      ,'Ref: IRC' || PAV.VACANCY_ID "ref"
      ,xxas_com_rest_util_pkg.uri_from_app_route
        ('job-application-detail/' || paaf.assignment_id) "href"
	  ,xxas_applicant_events_pkg.get_status_change_comms_xml(pav.vacancy_id
	                                                        ,ias.assignment_status_type_id
														    ,ias.status_change_date
														    ,paaf.assignment_id
						                                    ) xmlfields
FROM   per_all_assignments_f paaf
      ,per_all_vacancies pav
	  ,IRC_SEARCH_CRITERIA IVS
	  ,HR_LOCATIONS_ALL loc
	  ,PER_RECRUITMENT_ACTIVITIES PRA
      ,IRC_POSTING_CONTENTS_VL IPC
      ,PER_RECRUITMENT_ACTIVITY_FOR PerRecruitmentActivityForEO
	  ,irc_job_basket_items ijbi
	  ,HR_LOOKUPS HL2
	  ,IRC_ASSIGNMENT_STATUSES ias
WHERE  paaf.assignment_type = 'A'
AND    pav.vacancy_id = paaf.vacancy_id
AND PAV.VACANCY_ID                                      = PerRecruitmentActivityForEO.VACANCY_ID
AND PerRecruitmentActivityForEO.RECRUITMENT_ACTIVITY_ID = PRA.RECRUITMENT_ACTIVITY_ID
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    PRA.POSTING_CONTENT_ID                           = IPC.POSTING_CONTENT_ID
AND ijbi.recruitment_activity_id (+)                    = PerRecruitmentActivityForEO.recruitment_activity_id
AND ijbi.person_id (+)                                  = fnd_global.employee_id
AND    pav.vacancy_id = IVS.object_id
AND    ivs.object_type = 'VACANCY'
AND HL2.LOOKUP_TYPE(+)                                  = 'IRC_EMP_CAT'
AND IVS.EMPLOYMENT_CATEGORY                             = HL2.LOOKUP_CODE(+)
AND    loc.location_id (+) = pav.location_id
AND    ias.assignment_id = paaf.assignment_id
AND    ias.status_change_date = (select max(status_change_date)
                                 FROM   IRC_ASSIGNMENT_STATUSES ias1
                                 WHERE  ias1.assignment_id = ias.assignment_id
                                )
AND    paaf.effective_start_date =
         (SELECT max(paaf2.effective_start_date)
          FROM   per_all_assignments_f paaf2
          WHERE  paaf2.assignment_id = paaf.assignment_id
          AND    paaf2.assignment_type = 'A')
		  );

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

COMMIT;
EXIT;

