/* $Id$ */
/*============================================================================*
 |                 Copyright (c) 2014 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
                       := 'VacancyApplicantComms';
                       
BEGIN
      
  xxas_com_sr_pkg.set_rest_service
    (p_service_name       => l_SERVICE
    ,p_description        => 'View available applicant satuses'
    ,p_path               => '/vacancies/{vacancyId}'
    ,p_consumes           => 'application/xml'
    ,p_produces           => 'application/xml'
    );
    
  xxas_com_sr_pkg.set_rest_method
    (p_service_name       => l_SERVICE
    ,p_method_name        => 'GetVacancyApplicantStatusComms'
    ,p_description        => 'Get details of communications for an applicant status change'
    ,p_methods            => 'GET'
    ,p_path               => 'applicant-status-comms/{statusTypeId}'
    ,p_remove_root_ele    => true
    ,p_sql                =>
      q'[
          SELECT XMLElement
                   ("data"
                   ,xxas_applicant_events_pkg.get_status_change_comms_xml
                      ({pathParam.vacancyId}, {pathParam.statusTypeId})
                   ) AS "data"
          FROM   dual
          WHERE  xxas_com_sec_pkg.test_instance('XXAS_DATA_VAC_VIEW', {pathParam.vacancyId}) = 'Y'
      ]'
    );
    
  xxas_com_sr_pkg.set_rest_method
    (p_service_name       => l_SERVICE
    ,p_method_name        => 'UpdateVacancyApplicantStatus'
    ,p_description        => 'Updates applicants to the given status'
    ,p_methods            => 'PUT'
    ,p_path               => 'applicant-status-comms/{statusTypeId}'
    ,p_plsql              =>
      q'[
        DECLARE
          
          CURSOR csr_status_details
            (cp_xml IN XMLType)
          IS
          SELECT s.assignment_status_type_id
                ,s.status_change_reason
                ,s.body_title
                ,s.body_text
                ,cast(
                    multiset(
                      SELECT a.assignment_id
                      FROM   XMLTable
                               ('/APPLICANTS/APPLICANT'
                                PASSING s.applicants_xml
                                COLUMNS assignment_id NUMBER path '.'
                               ) a
                    ) as xxas_com_num_table) assignment_ids
          FROM   XMLTable
                   ('/XXAS_VACANCY_APPLICANT_STATUS_CHANGE'
                   PASSING (cp_xml)
                   COLUMNS assignment_status_type_id NUMBER         path 'STATUS'
                          ,status_change_reason      VARCHAR2(400)  path 'REASON'
                          ,body_title                VARCHAR2(4000) path 'BODY_TITLE'
                          ,body_text                 XMLType        path 'BODY_TEXT'
                          ,applicants_xml            XMLType        path 'APPLICANTS'
                    ) s;
                        
          l_status_details csr_status_details%ROWTYPE;
          l_resp           xxas_http_resp_obj;
          
        BEGIN
        
          IF NOT xxas_com_sec_pkg.test_instance_rtn_boolean
            ('XXAS_DATA_VAC_VIEW', {pathParam.vacancyId}) THEN
            l_resp := xxas_http_response
                        .status(xxas_http_resp_status_pkg.unauthorised)
                        .build();
          ELSE
          
            OPEN  csr_status_details(XMLType({requestParam._entity}));
            FETCH csr_status_details INTO l_status_details;
            CLOSE csr_status_details;

            xxas_applicant_pkg.mass_status_change
              (p_assignment_ids            => l_status_details.assignment_ids
              ,p_effective_date            => trunc(sysdate)
              ,p_assignment_status_type_id => l_status_details.assignment_status_type_id
              ,p_status_change_reason      => l_status_details.status_change_reason
              ,p_override_body_title       => l_status_details.body_title
              ,p_override_body_text        => l_status_details.body_text);
              
            l_resp := xxas_http_response
                        .ok()
                        .build();
            
          END IF;
          
          {response} := l_resp;

        END
      ]'
    );
    
  xxas_com_sr_pkg.set_rest_method
    (p_service_name       => l_SERVICE
    ,p_method_name        => 'SendVacancyAdHocComms'
    ,p_description        => 'Sends ad hoc communications to applicants in a vacancy'
    ,p_methods            => 'PUT'
    ,p_path               => 'applicant-adhoc-comms'
    ,p_plsql              =>
      q'[
        DECLARE
          
          CURSOR csr_details
            (cp_xml IN XMLType)
          IS
          SELECT s.body_title
                ,s.body_text
                ,cast(
                    multiset(
                      SELECT a.assignment_id
                      FROM   XMLTable
                               ('/APPLICANTS/APPLICANT'
                                PASSING s.applicants_xml
                                COLUMNS assignment_id NUMBER path '.'
                               ) a
                    ) as xxas_com_num_table) assignment_ids
          FROM   XMLTable
                   ('/XXAS_VACANCY_APPLICANT_AD_HOC'
                   PASSING (cp_xml)
                   COLUMNS body_title                VARCHAR2(4000) path 'BODY_TITLE'
                          ,body_text                 XMLType        path 'BODY_TEXT'
                          ,applicants_xml            XMLType        path 'APPLICANTS'
                    ) s;
                        
          l_details csr_details%ROWTYPE;
          l_resp    xxas_http_resp_obj;
          
        BEGIN
        
          IF NOT xxas_com_sec_pkg.test_instance_rtn_boolean
            ('XXAS_DATA_VAC_VIEW', {pathParam.vacancyId}) THEN
            l_resp := xxas_http_response
                        .status(xxas_http_resp_status_pkg.unauthorised)
                        .build();
          ELSE
          
            OPEN  csr_details(XMLType({requestParam._entity}));
            FETCH csr_details INTO l_details;
            CLOSE csr_details;

            xxas_applicant_events_pkg.send_ad_hoc
              (p_assignment_ids   => l_details.assignment_ids
              ,p_body_title       => l_details.body_title
              ,p_body_text        => l_details.body_text);
              
            l_resp := xxas_http_response
                        .ok()
                        .build();
            
          END IF;
          
          {response} := l_resp;

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