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

SET VERIFY OFF
SET FEEDBACK OFF
SET SCAN OFF
WHENEVER OSERROR  EXIT FAILURE ROLLBACK
WHENEVER SQLERROR EXIT FAILURE ROLLBACK

DECLARE

  --
  -- Local constants and variables
  --
  l_SERVICE CONSTANT xxas_com_service_repository.service_name%TYPE
                       := 'Htmlvacancies';
                       
BEGIN
      
  xxas_com_sr_pkg.set_rest_service
    (p_service_name       => l_SERVICE
    ,p_description        => 'View Html vacancies'
    ,p_consumes           => 'application/xml'
    ,p_produces           => 'application/xml'
    );
    
  xxas_com_sr_pkg.set_rest_method
    (p_service_name       => l_SERVICE
    ,p_method_name        => 'GetHtmlVacanciesList'
    ,p_description        => 'Get a list of vacancies'
    ,p_path               => 'vacancies-html/{personId}'
    ,p_methods            => 'GET'
    ,p_remove_root_ele    => true
    ,p_sql                =>
      q'[
	    SELECT XMLELEMENT
                  ("data" 
                  ,XMLAttributes
                   ('true' as "json:force-array"
                   ,'http://json.org/' as "xmlns:json")
                  ,XMLAgg
                     (XMLElement
                       ("VacancyList"
                        ,XMLForest
					     (name|| decode(job_name,null,'',' - '||job_name) || ', ' 
						      || org_name ||' ('   ||to_char(num_accept
						      || ' of ' || openings || ' filled)')                  "title",
			              town_or_city                                              "overline",
			              'Posted '|| xxas_util_pkg.format_date(date_from)
			                       || nvl2(date_to,', Closes '
						           || xxas_util_pkg.format_date(date_to),'')        "subTitle",
			              'IRC' || VACANCY_ID                                       "actionText",
                          vacancy_id                                                "id",
			              xxas_com_rest_util_pkg.uri_from_app_route
							 ('vac-detail/'|| VACANCY_ID||'_'
								           ||PERSON_ID||'_'||transaction_id)        "href",
                          nvl(JOB_TITLE,'IRC' || VACANCY_ID)                        "jobtitle",
                          title                                                     "postedName",
						  status                                                    "status",
						  name                                                      "name",
						  to_char(date_from,fnd_global.nls_date_format)             "postedDate",
						  to_char(last_update_date,fnd_global.nls_date_format)      "lastUpdate",
						  recruiter_full_name                                       "recruiter",
                          manager_full_name                                         "manager"
						 )
						,(XMLELEMENT
					      ("chips"
						  ,XMLAttributes
                                        ('true' as "json:force-array"
                                        ,'http://json.org/' as "xmlns:json")
						  ,XMLELEMENT
					      ("chip"
					        ,XMLForest
					           (status							   "title"
								,CASE status 
						              WHEN 'Pending approval' THEN 'timer-sand'
									  WHEN 'Hold'       THEN 'pause'
									  WHEN 'Filled'     THEN 'account-check'
									  WHEN 'Closed'     THEN 'stop'
									  WHEN 'Draft'      THEN 'square-edit-outline'
									  WHEN 'Open'       THEN 'account-search'
									  ELSE 'check'
								 END "icon"
		    				    ,status_color "color")
					       )
						 )
						 )
						,(XMLELEMENT
					      ("subList"
						  ,XMLAttributes
                                        ('true' as "json:force-array"
                                        ,'http://json.org/' as "xmlns:json")
						   ,XMLELEMENT
					       ("list"
					        ,CASE WHEN num_review = 1
							      THEN
								  XMLForest
					                 ('Review 1 Applicant'   "title"
		    				         ,xxas_com_rest_util_pkg.uri_from_app_route
							                ('applicant-list/'|| VACANCY_ID ||'_'||{pathParam.personId}||'_?'
											|| xxas_rslt_cache_pkg.get_appl_list_func_id('applicant-list') ||'-search={"status":"Review"}')        "href"
									 ,'false'   "inset")
								  WHEN num_review > 1
							      THEN
								  XMLForest
					                 ('Review '||num_review ||' Applicants'   "title"
		    				         ,xxas_com_rest_util_pkg.uri_from_app_route
							                ('applicant-list/'|| VACANCY_ID ||'_'||{pathParam.personId}||'_?'
											|| xxas_rslt_cache_pkg.get_appl_list_func_id('applicant-list') ||'-search={"status":"Review"}')        "href"
									 ,'false'   "inset")
								  ELSE null
							 END
					       )
						  ,XMLELEMENT
					       ("list"
					        ,CASE WHEN num_interview = 1
							      THEN
								  XMLForest
					                 ('Interview 1 Applicant'   "title"
		    				         ,xxas_com_rest_util_pkg.uri_from_app_route
							                ('applicant-list/'|| VACANCY_ID ||'_'||{pathParam.personId}||'_?'
											|| xxas_rslt_cache_pkg.get_appl_list_func_id('applicant-list') ||'-search={"status":"Interview"}')  "href"
									 ,'false'   "inset")
								  WHEN num_interview > 1
							      THEN
								  XMLForest
					                 ('Interview '||num_interview ||' Applicants'   "title"
		    				         ,xxas_com_rest_util_pkg.uri_from_app_route
							                ('applicant-list/'|| VACANCY_ID ||'_'||{pathParam.personId}||'_?'
											|| xxas_rslt_cache_pkg.get_appl_list_func_id('applicant-list') ||'-search={"status":"Interview"}')        "href"
									 ,'false'   "inset")
								  ELSE null
							 END
					       )
						   ,XMLELEMENT
					       ("list"
					        ,CASE WHEN num_offer = 1
							      THEN
								  XMLForest
					                 ('Progress offer for 1 Applicant'   "title"
		    				         ,xxas_com_rest_util_pkg.uri_from_app_route
							                ('applicant-list/'|| VACANCY_ID ||'_'||{pathParam.personId}||'_?'
											|| xxas_rslt_cache_pkg.get_appl_list_func_id('applicant-list') ||'-search={"status":"Offer"}')        "href"
									 ,'false'   "inset")
								  WHEN num_offer > 1
							      THEN
								  XMLForest
					                 ('Progress offers for '||num_offer ||' Applicants'   "title"
		    				         ,xxas_com_rest_util_pkg.uri_from_app_route
							                ('applicant-list/'|| VACANCY_ID ||'_'||{pathParam.personId}||'_?'
											|| xxas_rslt_cache_pkg.get_appl_list_func_id('applicant-list') ||'-search={"status":"Offer"}')        "href"
									 ,'false'   "inset")
								  ELSE null
							 END
					       )
						  ,XMLELEMENT
					       ("list"
					        ,CASE WHEN num_review = 1
							      THEN
								  XMLForest
					                 ('Reject 1 Applicant'   "title"
		    				         ,xxas_com_rest_util_pkg.uri_from_app_route
							                ('applicant-list/'|| VACANCY_ID ||'_'||{pathParam.personId}||'_?'
											|| xxas_rslt_cache_pkg.get_appl_list_func_id('applicant-list') ||'-search={"status":"Review"}')        "href"
									 ,'false'   "inset")
								  WHEN num_review > 1
							      THEN
								  XMLForest
					                 ('Reject '||num_review ||' Applicants'   "title"
		    				         ,xxas_com_rest_util_pkg.uri_from_app_route
							                ('applicant-list/'|| VACANCY_ID ||'_'||{pathParam.personId}||'_?'
											|| xxas_rslt_cache_pkg.get_appl_list_func_id('applicant-list') ||'-search={"status":"Review"}')        "href"
									 ,'false'   "inset")
								  ELSE null
							 END
					       )
						  )
						 )
						,(XMLELEMENT
					      ("stepper"
						  ,(XMLELEMENT
					        ("steps"
						     ,XMLAttributes
                                          ('true' as "json:force-array"
                                          ,'http://json.org/' as "xmlns:json")
						      ,XMLELEMENT
					           ("step"
					             ,XMLForest
					              ('Applied'				                               "title"
								   ,decode(num_applied,0,'GREY','positive')                "color"
								   ,CASE WHEN num_applied > 0 THEN
								         xxas_com_rest_util_pkg.uri_from_app_route
                                         ('applicant-list/'||vacancy_id||'_'||{pathParam.personId} || '_')
                                         ELSE null
                                    END	              									   "href"
								   ,num_applied                                            "number")
					           )
							  ,XMLELEMENT
					           ("step"
					             ,XMLForest
					              ('Review'					                               "title"
								   ,decode(num_review,0,'GREY','positive')                 "color"
								   ,CASE WHEN num_review > 0 THEN 
								         xxas_com_rest_util_pkg.uri_from_app_route
							                ('applicant-list/'|| VACANCY_ID ||'_'||{pathParam.personId}||'_?'
											|| xxas_rslt_cache_pkg.get_appl_list_func_id('applicant-list') ||'-search={"status":"Review"}')
                                         ELSE NULL
                                    END         										   "href"
								   ,num_review                                             "number")
					           )
							  ,XMLELEMENT
					           ("step"
					             ,XMLForest
					              ('Interview'					                           "title"
								   ,decode(num_interview,0,'GREY','positive')              "color"
								   ,CASE WHEN num_interview > 0 THEN 
								         xxas_com_rest_util_pkg.uri_from_app_route
							                ('applicant-list/'|| VACANCY_ID ||'_'||{pathParam.personId}||'_?'
											|| xxas_rslt_cache_pkg.get_appl_list_func_id('applicant-list') ||'-search={"status":"Interview"}')                         
										 ELSE NULL 
									END                                                    "href"
								   ,num_interview                                          "number")
					           )
							  ,XMLELEMENT
					           ("step"
					             ,XMLForest
					              ('Offer'					                               "title"
								   ,decode(num_offer,0,'GREY','positive')                  "color"
								   ,CASE WHEN num_offer > 0 THEN 
								         xxas_com_rest_util_pkg.uri_from_app_route
							                ('applicant-list/'|| VACANCY_ID ||'_'||{pathParam.personId}||'_?'
											|| xxas_rslt_cache_pkg.get_appl_list_func_id('applicant-list') ||'-search={"status":"Offer"}')                             
										 ELSE NULL
									END                                                    "href"
								   ,num_offer                                              "number")
					           )
							  ,XMLELEMENT
					           ("step"
					             ,XMLForest
					              ('Accepted'					                           "title"
								   ,decode(num_accept,0,'GREY','balanced')                 "color"
								   ,CASE WHEN num_accept > 0 THEN 
								         xxas_com_rest_util_pkg.uri_from_app_route
							                ('applicant-list/'|| VACANCY_ID ||'_'||{pathParam.personId}||'_?'
											|| xxas_rslt_cache_pkg.get_appl_list_func_id('applicant-list') ||'-search={"status":"Accepted"}')                            
										 ELSE NULL
									END                                                    "href"
								   ,num_accept                                             "number")
					           )
						    )
						   )
						  )
					     )
					  )
                  )
                )  "data"
			from ((select PAV.name name,
				PAV.date_from date_from,
				PAV.date_to date_to,
				PAV.vacancy_id vacancy_id,
				HRL.meaning status,
				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 'balanced'
					 ELSE '#C0C0C0'
                 END  status_color,
				PAV.budget_measurement_value openings,
				ORG.name org_name,
				PJ.name job_name,
				PAV.last_update_date last_update_date,
				LOC.location_code town_or_city,
				IPC.job_title job_title,
				IPC.name title,
				IPC.recruiter_full_name recruiter_full_name,
				IPC.manager_full_name manager_full_name,
				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,
				PAV.manager_id person_id,
                null transaction_id
			from PER_ALL_VACANCIES PAV,
				PER_REQUISITIONS  PR,
				HR_LOCATIONS_ALL LOC,
				IRC_POSTING_CONTENTS_VL IPC,
				HR_ALL_ORGANIZATION_UNITS_VL ORG,
				PER_JOBS_VL PJ,
                HR_LOOKUPS HRL
			where PAV.manager_id         = {pathParam.personId}
			AND   PAV.requisition_id     = PR.requisition_id
			AND   PAV.primary_posting_id = IPC.posting_content_id(+)
			AND   PAV.location_id        = LOC.location_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   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 name,
				PAV.date_from date_from,
				PAV.date_to date_to,
				PAV.vacancy_id vacancy_id,
				HRL.meaning status,
				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 'balanced'
					 ELSE '#C0C0C0'
                 END  status_color,
				PAV.budget_measurement_value openings,
				ORG.name org_name,
				PJ.name job_name,
				PAV.last_update_date last_update_date,
				LOC.location_code town_or_city,
				IPC.job_title job_title,
				IPC.name title,
				IPC.recruiter_full_name recruiter_full_name,
				IPC.manager_full_name manager_full_name,
				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,
				TM.person_id person_id,
                null transaction_id
			from   per_all_vacancies PAV,
				irc_rec_team_members TM,
				PER_REQUISITIONS  PR,
				HR_LOCATIONS_ALL LOC,
				IRC_POSTING_CONTENTS_VL IPC,
				HR_ALL_ORGANIZATION_UNITS_VL ORG,
				PER_JOBS_VL PJ,
				HR_LOOKUPS HRL
			WHERE  TM.person_id = {pathParam.personId}
			AND    PAV.vacancy_id = tm.vacancy_id
			AND   PAV.requisition_id     = PR.requisition_id
			AND   PAV.primary_posting_id = IPC.posting_content_id(+)
			AND   PAV.location_id        = LOC.location_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   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 hr_xml_util.get_node_value(hat.transaction_id,'Name','//PerAllVacanciesEORow/') name,
				xxas_xml_util_pkg.oafstring_to_date(hr_xml_util.get_node_value(hat.transaction_id,'DateFrom','//PerAllVacanciesEORow/')) date_from,
				xxas_xml_util_pkg.oafstring_to_date(hr_xml_util.get_node_value(hat.transaction_id,'DateTo','//PerAllVacanciesEORow/')) date_to,
				to_number(hr_xml_util.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,
				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 '#FF0000'
                     WHEN 'APPROVED' THEN 'balanced'
					 ELSE '#C0C0C0'
                 END status_color,
				to_number(hr_xml_util.get_node_value(hat.transaction_id,'NumberOfOpenings','//PerAllVacanciesEORow/')) openings,
				ORG.name org_name,
				PJ.name job_name,
				xxas_xml_util_pkg.oafstring_to_date(hr_xml_util.get_node_value(hat.transaction_id,'LastUpdateDate','//PerAllVacanciesEORow/')) last_update_date,
				LOC.location_code town_or_city,
				hr_xml_util.get_node_value(hat.transaction_id,'JobTitle','//PerAllVacanciesEORow/') job_title,
				hr_xml_util.get_node_value(hat.transaction_id,'Name','//IrcPostingContentsVlEORow/') title,
				--hr_xml_util.get_node_value(hat.transaction_id,'RecruiterId','//PerAllVacanciesEORow/') 
				null recruiter_full_name,
				--hr_xml_util.get_node_value(hat.transaction_id,'ManagerId','//PerAllVacanciesEORow/') 
				null manager_full_name,
				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,
				hat.creator_person_id person_id,
                hat.transaction_id transaction_id
			from HR_API_TRANSACTIONS hat,
				hr_locations_all LOC,
				HR_ALL_ORGANIZATION_UNITS_VL ORG,
				PER_JOBS_VL PJ,
				hr_lookups HRL
			where hat.creator_person_id     = {pathParam.personId}
			AND   hat.status                NOT IN ('D', 'N', 'W', 'ACTIVE', 'AC')
			AND   hat.transaction_ref_table = 'PER_ALL_VACANCIES'
			AND   hr_xml_util.get_node_value(hat.transaction_id,'LocationId','//PerAllVacanciesEORow/') = LOC.location_id (+)
			AND   hr_xml_util.get_node_value(hat.transaction_id,'OrganizationId','//PerAllVacanciesEORow/') = ORG.organization_id (+)
			AND   hr_xml_util.get_node_value(hat.transaction_id,'JobId','//PerAllVacanciesEORow/') = PJ.job_id(+)
			AND   HRL.lookup_type           = 'VACANCY_STATUS'
            AND   HRL.lookup_code           = hr_xml_util.get_node_value(hat.transaction_id,'Status','//PerAllVacanciesEORow/')
			AND   xxas_com_sec_pkg.test_instance('XXAS_DATA_TRANS_VIEW', hat.transaction_id) = 'Y'
			)
			)
			]'
    );
	
	
	xxas_com_sr_pkg.set_rest_method
    (p_service_name       => l_SERVICE
    ,p_method_name        => 'GetHtmlVacancyDetail'
    ,p_description        => 'Get a particular vacancy detail'
    ,p_path               => 'vac-details-html/{vacancyId}/{personId}'
    ,p_methods            => 'GET'
    ,p_remove_root_ele    => true
    ,p_sql                =>
      q'[
	    SELECT XMLELEMENT
                  ("data" 
                  ,XMLAttributes
                 ('true' as "json:force-array"
                 ,'http://json.org/' as "xmlns:json")
                    ,XMLAgg
                      (XMLElement
                        ("VacancyList"
                          ,XMLForest
						   (NAME|| decode(job_name,null,'',' - '||job_name) || ', ' 
						    || org_name ||' (' 
						    ||to_char(num_accept
						    || ' of '
							|| openings || ' filled)')                              "title",
			                TOWN_OR_CITY                                            "overline",
			                'Posted '|| xxas_util_pkg.format_date(date_from)
			                || nvl2(date_to,', Closes '
							|| xxas_util_pkg.format_date(date_to),'')               "subTitle",
			                'IRC' || VACANCY_ID                                     "actionText",
                            vacancy_id                                              "id",
			                nvl(JOB_TITLE,'IRC' || VACANCY_ID)                      "jobtitle",
                            title                                                   "postedName",
							status                                                  "status",
							NAME                                                    "name",
							date_from                                               "postedDate",
							last_update_date                                        "lastUpdate",
							recruiter_full_name                                     "recruiter",
                            manager_full_name                                       "manager"
						   )
						,(XMLELEMENT
					      ("chips"
						  ,XMLAttributes
                                        ('true' as "json:force-array"
                                        ,'http://json.org/' as "xmlns:json")
						  ,XMLELEMENT
					      ("chip"
					        ,XMLForest
					           (status					   "title"
								,CASE status 
						              WHEN 'Pending approval' THEN 'timer-sand'
									  WHEN 'Hold'       THEN 'pause'
									  WHEN 'Filled'     THEN 'account-check'
									  WHEN 'Closed'     THEN 'stop'
									  WHEN 'Draft'      THEN 'square-edit-outline'
									  WHEN 'Open'       THEN 'account-search'
									  ELSE 'check'
								 END "icon"
		    				    ,status_color "color")
					       )
						 )
						 )
						,(XMLELEMENT
					      ("subList"
						  ,XMLAttributes
                                        ('true' as "json:force-array"
                                        ,'http://json.org/' as "xmlns:json")
						   ,XMLELEMENT
					       ("list"
					        ,CASE WHEN num_review = 1
							      THEN
								  XMLForest
					                 ('Review 1 Applicant'   "title"
		    				         ,xxas_com_rest_util_pkg.uri_from_app_route
							                ('applicant-list/'|| VACANCY_ID ||'_'||{pathParam.personId}||'_?'
											|| xxas_rslt_cache_pkg.get_appl_list_func_id('applicant-list') ||'-search={"status":"Review"}')        "href"
									 ,'false'   "inset")
								  WHEN num_review > 1
							      THEN
								  XMLForest
					                 ('Review '||num_review ||' Applicants'   "title"
		    				         ,xxas_com_rest_util_pkg.uri_from_app_route
							                ('applicant-list/'|| VACANCY_ID ||'_'||{pathParam.personId}||'_?'
											|| xxas_rslt_cache_pkg.get_appl_list_func_id('applicant-list') ||'-search={"status":"Review"}')        "href"
									 ,'false'   "inset")
								  ELSE null
							 END
					       )
						  ,XMLELEMENT
					       ("list"
					        ,CASE WHEN num_interview = 1
							      THEN
								  XMLForest
					                 ('Interview 1 Applicant'   "title"
		    				         ,xxas_com_rest_util_pkg.uri_from_app_route
							                ('applicant-list/'|| VACANCY_ID ||'_'||{pathParam.personId}||'_?'
											|| xxas_rslt_cache_pkg.get_appl_list_func_id('applicant-list') ||'-search={"status":"Interview"}')  "href"
									 ,'false'   "inset")
								  WHEN num_interview > 1
							      THEN
								  XMLForest
					                 ('Interview '||num_interview ||' Applicants'   "title"
		    				         ,xxas_com_rest_util_pkg.uri_from_app_route
							                ('applicant-list/'|| VACANCY_ID ||'_'||{pathParam.personId}||'_?'
											|| xxas_rslt_cache_pkg.get_appl_list_func_id('applicant-list') ||'-search={"status":"Interview"}')        "href"
									 ,'false'   "inset")
								  ELSE null
							 END
					       )
						   ,XMLELEMENT
					       ("list"
					        ,CASE WHEN num_offer = 1
							      THEN
								  XMLForest
					                 ('Progress offer for 1 Applicant'   "title"
		    				         ,xxas_com_rest_util_pkg.uri_from_app_route
							                ('applicant-list/'|| VACANCY_ID ||'_'||{pathParam.personId}||'_?'
											|| xxas_rslt_cache_pkg.get_appl_list_func_id('applicant-list') ||'-search={"status":"Offer"}')        "href"
									 ,'false'   "inset")
								  WHEN num_offer > 1
							      THEN
								  XMLForest
					                 ('Progress offers for '||num_offer ||' Applicants'   "title"
		    				         ,xxas_com_rest_util_pkg.uri_from_app_route
							                ('applicant-list/'|| VACANCY_ID ||'_'||{pathParam.personId}||'_?'
											|| xxas_rslt_cache_pkg.get_appl_list_func_id('applicant-list') ||'-search={"status":"Offer"}')        "href"
									 ,'false'   "inset")
								  ELSE null
							 END
					       )
						   ,XMLELEMENT
					       ("list"
					        ,CASE WHEN num_review = 1
							      THEN
								  XMLForest
					                 ('Reject 1 Applicant'   "title"
		    				         ,xxas_com_rest_util_pkg.uri_from_app_route
							                ('applicant-list/'|| VACANCY_ID ||'_'||{pathParam.personId}||'_?'
											|| xxas_rslt_cache_pkg.get_appl_list_func_id('applicant-list') ||'-search={"status":"Review"}')        "href"
									 ,'false'   "inset")
								  WHEN num_review > 1
							      THEN
								  XMLForest
					                 ('Reject '||num_review ||' Applicants'   "title"
		    				         ,xxas_com_rest_util_pkg.uri_from_app_route
							                ('applicant-list/'|| VACANCY_ID ||'_'||{pathParam.personId}||'_?'
											|| xxas_rslt_cache_pkg.get_appl_list_func_id('applicant-list') ||'-search={"status":"Review"}')        "href"
									 ,'false'   "inset")
								  ELSE null
							 END
					       )
						  )
						 )
						,(XMLELEMENT
					      ("stepper"
						  ,(XMLELEMENT
					        ("steps"
						     ,XMLAttributes
                                          ('true' as "json:force-array"
                                          ,'http://json.org/' as "xmlns:json")
						      ,XMLELEMENT
					           ("step"
					             ,XMLForest
					              ('Applied'				                               "title"
								   ,decode(num_applied,0,'GREY','positive')                "color"
								   ,CASE WHEN num_applied > 0 THEN
								         xxas_com_rest_util_pkg.uri_from_app_route
                                         ('applicant-list/'||vacancy_id||'_'||{pathParam.personId})
                                         ELSE null
                                    END	              									   "href"
								   ,num_applied                                            "number")
					           )
							  ,XMLELEMENT
					           ("step"
					             ,XMLForest
					              ('Review'					                               "title"
								   ,decode(num_review,0,'GREY','positive')                 "color"
								   ,CASE WHEN num_review > 0 THEN 
								         xxas_com_rest_util_pkg.uri_from_app_route
							                ('applicant-list/'|| VACANCY_ID ||'_'||{pathParam.personId}||'_?'
											|| xxas_rslt_cache_pkg.get_appl_list_func_id('applicant-list') ||'-search={"status":"Review"}')
                                         ELSE NULL
                                    END         										   "href"
								   ,num_review                                             "number")
					           )
							  ,XMLELEMENT
					           ("step"
					             ,XMLForest
					              ('Interview'					                           "title"
								   ,decode(num_interview,0,'GREY','positive')              "color"
								   ,CASE WHEN num_review > 0 THEN 
								         xxas_com_rest_util_pkg.uri_from_app_route
							                ('applicant-list/'|| VACANCY_ID ||'_'||{pathParam.personId}||'_?'
											|| xxas_rslt_cache_pkg.get_appl_list_func_id('applicant-list') ||'-search={"status":"Interview"}')
                                         ELSE NULL
                                    END         										   "href"
								   ,num_interview                                          "number")
					           )
							  ,XMLELEMENT
					           ("step"
					             ,XMLForest
					              ('Offer'					                               "title"
								   ,decode(num_offer,0,'GREY','positive')                  "color"
								   ,CASE WHEN num_review > 0 THEN 
								         xxas_com_rest_util_pkg.uri_from_app_route
							                ('applicant-list/'|| VACANCY_ID ||'_'||{pathParam.personId}||'_?'
											|| xxas_rslt_cache_pkg.get_appl_list_func_id('applicant-list') ||'-search={"status":"Offer"}')
                                         ELSE NULL
                                    END         										   "href"
								   ,num_offer                                              "number")
					           )
							  ,XMLELEMENT
					           ("step"
					             ,XMLForest
					              ('Accepted'					                           "title"
								   ,decode(num_accept,0,'GREY','balanced')                 "color"
								   ,CASE WHEN num_review > 0 THEN 
								         xxas_com_rest_util_pkg.uri_from_app_route
							                ('applicant-list/'|| VACANCY_ID ||'_'||{pathParam.personId}||'_?'
											|| xxas_rslt_cache_pkg.get_appl_list_func_id('applicant-list') ||'-search={"status":"Accepted"}')
                                         ELSE NULL
                                    END         										   "href"
								   ,num_accept                                             "number")
					           )
						    )
						   )
						  )
					     )
					  )
                  )
                )  "data"
			from ((select PAV.name name,
				          PAV.date_from date_from,
				          PAV.date_to date_to,
				          PAV.vacancy_id vacancy_id,
				          HRL.meaning status,
						  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 'balanced'
							   ELSE '#C0C0C0'
                          END  status_color,
				          PAV.budget_measurement_value openings,
						  ORG.name org_name,
						  PJ.name job_name,
				          PAV.last_update_date last_update_date,
				          LOC.location_code town_or_city,
				          IPC.job_title job_title,
				          IPC.name title,
				          IPC.recruiter_full_name recruiter_full_name,
				          IPC.manager_full_name manager_full_name,
                			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,
                			PAV.manager_id person_id,
                               null transaction_id
		   from PER_ALL_VACANCIES PAV,
				PER_REQUISITIONS  PR,
				HR_LOCATIONS_ALL LOC,
				IRC_POSTING_CONTENTS_VL IPC,
				HR_ALL_ORGANIZATION_UNITS_VL ORG,
				PER_JOBS_VL PJ,
                HR_LOOKUPS HRL
		    where PAV.manager_id         = {pathParam.personId}
			AND   PAV.vacancy_id         = {pathParam.vacancyId}
			AND   PAV.requisition_id     = PR.requisition_id
			AND   PAV.primary_posting_id = IPC.posting_content_id(+)
			AND   PAV.organization_id    = org.organization_id(+)
			AND   PAV.job_id             = pj.job_id(+)
			AND   PAV.location_id        = LOC.location_id(+)
			AND   HRL.lookup_type        = 'VACANCY_STATUS'
            AND   HRL.lookup_code        = PAV.status
			AND   NOT EXISTS(SELECT '1'
                             FROM  hr_api_transactions ht
                             WHERE ht.transaction_ref_table = 'PER_ALL_VACANCIES'
                             AND ht.status NOT              IN ('D', '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 name,
				PAV.date_from date_from,
				PAV.date_to date_to,
				PAV.vacancy_id vacancy_id,
				HRL.meaning status,
				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,
				PAV.budget_measurement_value openings,
				ORG.name org_name,
				PJ.name job_name,
				PAV.last_update_date last_update_date,
				LOC.location_code town_or_city,
				IPC.job_title job_title,
				IPC.name title,
				IPC.recruiter_full_name recruiter_full_name,
				IPC.manager_full_name manager_full_name,
				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,
				TM.person_id person_id,
                null transaction_id
			from   per_all_vacancies PAV,
				irc_rec_team_members TM,
				PER_REQUISITIONS  PR,
				HR_LOCATIONS_ALL LOC,
				HR_ALL_ORGANIZATION_UNITS_VL ORG,
				PER_JOBS_VL PJ,
				IRC_POSTING_CONTENTS_VL IPC,
				HR_LOOKUPS HRL
			WHERE  TM.person_id          = {pathParam.personId}
			AND   PAV.vacancy_id         = {pathParam.vacancyId}
			AND   PAV.vacancy_id         = tm.vacancy_id
			AND   PAV.requisition_id     = PR.requisition_id
			AND   PAV.primary_posting_id = IPC.posting_content_id(+)
			AND   PAV.organization_id    = org.organization_id(+)
			AND   PAV.job_id             = pj.job_id(+)
			AND   PAV.location_id        = LOC.location_id(+)
			AND   HRL.lookup_type        = 'VACANCY_STATUS'
            AND   HRL.lookup_code        = PAV.status
			AND   NOT EXISTS(SELECT '1'
                             FROM  hr_api_transactions ht
                             WHERE ht.transaction_ref_table = 'PER_ALL_VACANCIES'
                             AND ht.status NOT              IN ('D', '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 hr_xml_util.get_node_value(hat.transaction_id,'Name','//PerAllVacanciesEORow/') name,
				xxas_xml_util_pkg.oafstring_to_date(hr_xml_util.get_node_value(hat.transaction_id,'DateFrom','//PerAllVacanciesEORow/')) date_from,
				xxas_xml_util_pkg.oafstring_to_date(hr_xml_util.get_node_value(hat.transaction_id,'DateTo','//PerAllVacanciesEORow/')) date_to,
				to_number(hr_xml_util.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,
				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'
					 ELSE '#C0C0C0'
                 END status_color,
				to_number(hr_xml_util.get_node_value(hat.transaction_id,'NumberOfOpenings','//PerAllVacanciesEORow/')) openings,
				ORG.name org_name,
				PJ.name job_name,
				xxas_xml_util_pkg.oafstring_to_date(hr_xml_util.get_node_value(hat.transaction_id,'LastUpdateDate','//PerAllVacanciesEORow/')) last_update_date,
				LOC.location_code town_or_city,
				hr_xml_util.get_node_value(hat.transaction_id,'JobTitle','//PerAllVacanciesEORow/') job_title,
				hr_xml_util.get_node_value(hat.transaction_id,'Name','//IrcPostingContentsVlEORow/') title,
				--hr_xml_util.get_node_value(hat.transaction_id,'RecruiterId','//PerAllVacanciesEORow/') 
				null recruiter_full_name,
				--hr_xml_util.get_node_value(hat.transaction_id,'ManagerId','//PerAllVacanciesEORow/') 
				null manager_full_name,
				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,
				HAT.creator_person_id person_id,
                HAT.transaction_id transaction_id
			from HR_API_TRANSACTIONS hat,
				hr_locations_all LOC,
				HR_ALL_ORGANIZATION_UNITS_VL ORG,
				PER_JOBS_VL PJ,
				hr_lookups HRL
			where hat.creator_person_id     = {pathParam.personId}
			AND   to_number(hr_xml_util.get_node_value(hat.transaction_id,'VacancyId','//PerAllVacanciesEORow/')) = {pathParam.vacancyId}
			AND   hat.status                NOT IN ('D', 'N', 'W', 'ACTIVE', 'AC')
			AND   hat.transaction_ref_table = 'PER_ALL_VACANCIES'
			AND   hr_xml_util.get_node_value(hat.transaction_id,'LocationId','//PerAllVacanciesEORow/') = LOC.location_id (+)
			AND   hr_xml_util.get_node_value(hat.transaction_id,'OrganizationId','//PerAllVacanciesEORow/') = ORG.organization_id(+)
			AND   hr_xml_util.get_node_value(hat.transaction_id,'JobId','//PerAllVacanciesEORow/') = PJ.job_id(+) 
			AND   HRL.lookup_type           = 'VACANCY_STATUS'
            AND   HRL.lookup_code           = hr_xml_util.get_node_value(hat.transaction_id,'Status','//PerAllVacanciesEORow/')
			AND   xxas_com_sec_pkg.test_instance('XXAS_DATA_TRANS_VIEW', hat.transaction_id) = 'Y'
			)
			)
	]'
    );
	
	xxas_com_sr_pkg.set_rest_method
    (p_service_name       => l_SERVICE
    ,p_method_name        => 'GetVacancyMenuContext'
    ,p_description        => 'Get a vacancy''s menu context'
    ,p_methods            => 'GET'
    ,p_path               => '/vacancy/{vacancyId}/{personId}/menu-context'
    ,p_remove_root_ele    => true
    ,p_sql                =>
      q'[
        SELECT XMLElement
                 ("data"
				  ,XMLForest
                          (XTV.vacancy_id        "id",
						   XTV.vacancy_name 
						   || decode(XTV.job_name,null,'',' - '||job_name)
						   || ', ' 
						   || XTV.org_name       "title",
						   LOC.location_code     "subTitle"
				          )
				 ,(select XMLElement
                            ("labels"
                            ,XMLAttributes
                               ('true' as "json:force-array"
                               ,'http://json.org/' as "xmlns:json")
                               ,XMLAgg(XMLElement
                                        ("label"
                                        ,XMLForest
                                         (XTV.status_code	"title"
								         ,XTV.status_color  "color"
										 ,CASE XTV.status_code
										       WHEN 'Pending approval' THEN 'timer-sand'
									           WHEN 'Hold'       THEN 'pause'
									           WHEN 'Filled'     THEN 'account-check'
									           WHEN 'Closed'     THEN 'stop'
									           WHEN 'Draft'      THEN 'square-edit-outline'
									           WHEN 'Open'       THEN 'account-search'
									           ELSE 'check'  
										  END  "icon"
										 )
                                        )
                                       )
						    ) labels
                   FROM  dual)
				) as "data"
			FROM   XXAS_HTML_VACANCIES_V XTV,
			       hr_locations_all LOC
		WHERE  XTV.person_id    = {pathParam.personId}
		AND    XTV.vacancy_id   = {pathParam.vacancyId}
		AND    XTV.location_id  = LOC.location_id(+)
		]'
    );
	
	xxas_com_sr_pkg.set_rest_method
    (p_service_name       => l_SERVICE
    ,p_method_name        => 'DeleteHTMLVacancy'
    ,p_description        => 'Deletes the given vacancy'
    ,p_methods            => 'DELETE'
    ,p_path               => '/del-vac/{vacancyId}'
    ,p_plsql              =>
      q'[
        DECLARE
        
          l_resp           xxas_http_resp_obj;
          
        BEGIN
       
            xxas_vacancy_pkg.delete_vacancy
              (p_vacancy_id => {pathParam.vacancyId});       
            l_resp := xxas_http_response
                      .ok(xxas_com_rest_util_pkg.app_route_redirect_response
                            ('vacancy-list'
                            ,null
                            ,null
                            )
                         )
                      .build();
          
            {response} := l_resp;

        END
      ]'
    );
	
	xxas_com_sr_pkg.set_rest_method
    (p_service_name       => l_SERVICE
    ,p_method_name        => 'CreateHTMLEmptyVacancy'
    ,p_description        => 'Creates a empty skeleton vacancy'
    ,p_methods            => 'POST'
    ,p_path               => '/html-vac/{personId}'
    ,p_plsql              =>
      q'[
        DECLARE
          l_vacancy     xxas_per_all_vacancy;
          l_resp        xxas_http_resp_obj;
		  l_dummy_resp  xxas_http_resp_obj;
        BEGIN
       
          IF NOT xxas_com_sec_pkg.test_instance_rtn_boolean
            ('XXAS_DATA_VAC_INS', {pathParam.personId}) THEN
            l_resp := xxas_http_response
                        .status(xxas_http_resp_status_pkg.unauthorised)
                        .build();
          ELSE
            l_vacancy :=
              xxas_vacancy_pkg.create_empty_vacancy
               (p_person_id            => {pathParam.personId}
               ,p_copy_from_vacancy_id => {requestParam.copyFromVacancyId});
            IF l_vacancy IS NULL THEN
              l_resp := xxas_http_response
                          .status(xxas_http_resp_status_pkg.unauthorised)
                          .build(); /* TODO handle properly */         
            ELSE
			 
              l_resp := xxas_http_response
                      .ok(xxas_com_rest_util_pkg.app_route_redirect_response
                            ('vacancy-create/' || l_vacancy.vacancy_id || '_'
							 || l_vacancy.transaction_id
                            ,'modal'
                            ,'true'
							,null
							,'large'
                            )
                         )
                      .build();
            END IF;
          END IF;
         
         {response} := l_resp;

        END
      ]'
    );
	
END;
/
COMMIT;
EXIT;