/* $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
                       := 'VacancyOffers';
                       
BEGIN
      
  xxas_com_sr_pkg.set_rest_service
    (p_service_name       => l_SERVICE
    ,p_description        => 'View and transact vacancies'
    ,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        => 'GetVacancyOffers'
    ,p_description        => 'Get a list of offers for a vacancy'
    ,p_path               => 'offers'
    ,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
                     ("XXAS_VACANCY_OFFER"
                     ,XMLForest
                       (vo.offer_id id
                       ,va.person_id
                       ,xxas_com_rest_util_pkg.uri_from_path
                           ('/vacancies/' || {pathParam.vacancyId} || '/applicants/' || va.assignment_id || '/offers/' || vo.offer_id) href
                       ,xxas_com_rest_util_pkg.uri_from_path
                           ('/vacancies/' || {pathParam.vacancyId} || '/applicants/' || va.assignment_id || '/offers/' || vo.offer_id || '/actions') actions_href
                       ,xxas_com_rest_util_pkg.uri_from_path
                           ('/vacancies/' || {pathParam.vacancyId} || '/applicants/' || va.assignment_id || '/offers/' || vo.offer_id || '/view-schema') view_schema_href
                       ,xxas_com_rest_util_pkg.uri_from_path
                           ('/vacancies/' || {pathParam.vacancyId} || '/applicants/' || va.assignment_id || '/offers/' || vo.offer_id || '/form-schema') form_schema_href
                       ,va.image_href
                       ,va.person_name
                       ,xxas_util_pkg.flag_to_boolean(xxas_com_sec_pkg.test_instance('XXAS_DATA_VAC_OFF_UPD', to_char(vo.offer_id))) edit_allowed
                       ,xxas_com_msg_pkg.get_string('XXAS_RB_COM_EDIT') edit_label
                       ,xxas_util_pkg.flag_to_boolean(xxas_com_sec_pkg.test_instance('XXAS_DATA_VAC_OFF_DEL', to_char(vo.offer_id))) delete_allowed
                       ,xxas_com_msg_pkg.get_string('XXAS_RB_COM_DEL') delete_label
                       ,'location' contact1_icon
                       ,va.town_or_city
                         || decode(va.region_1, null, null, ', ')
                         || xxas_util_pkg.decode_lookup(va.country || '_COUNTY', va.region_1) contact1_text
                       ,'smartphone' contact2_icon
                       ,va.phone_number contact2_text
                       ,'email' contact3_icon
                       ,va.email_address contact3_text
                       ,vo.status_meaning
                       ,vo.proposed_start_date
                       )
                     )
                   ORDER BY va.last_name, va.person_name
                   )
                 ) AS "data"
        FROM   xxas_vacancy_applicants_v va
              ,xxas_vacancy_offers_v vo
        WHERE  va.vacancy_id = {pathParam.vacancyId}
        AND    vo.vacancy_id = {pathParam.vacancyId}
        AND    vo.applicant_assignment_id = va.assignment_id
        AND    xxas_com_sec_pkg.test_instance('XXAS_DATA_VAC_VIEW', va.vacancy_id) = 'Y'
      ]'
    );
    
  xxas_com_sr_pkg.set_rest_method
    (p_service_name       => l_SERVICE
    ,p_method_name        => 'CreateEmptyOffer'
    ,p_description        => 'Creates a empty skeleton offer'
    ,p_path               => 'offers'
    ,p_methods            => 'POST'
    ,p_plsql              =>
      q'[
        DECLARE
          l_offer xxas_irc_offer;
          l_resp  xxas_http_resp_obj;
          
          CURSOR csr_apl IS
          SELECT *
          FROM   xxas_vacancy_applicants_v a
          WHERE  a.assignment_id = {requestParam.applicantId};
          
          l_apl  csr_apl%ROWTYPE;
          
        BEGIN
        
          OPEN  csr_apl;
          FETCH csr_apl INTO l_apl;
          CLOSE csr_apl;
       
          IF NOT xxas_com_sec_pkg.test_instance_rtn_boolean('XXAS_DATA_VAC_VIEW', {pathParam.vacancyId}) OR
             NOT xxas_com_sec_pkg.test_instance_rtn_boolean('XXAS_APL_CREATE_OFFER_PERM', l_apl.assignment_status_type_id) OR
             nvl(xxas_com_sec_pkg.person_on_rec_team_edit_flag({pathParam.vacancyId},'Y','Y'),'N') = 'N' THEN
            
            l_resp := xxas_http_response
                        .status(xxas_http_resp_status_pkg.unauthorised)
                        .build();
          ELSE
            l_offer :=
              xxas_offer_pkg.create_empty_offer
               (p_assignment_id => {requestParam.applicantId});
            IF l_offer 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
                          .created(l_offer.href)
                          .entity(xxas_offer_pkg.obj_to_xml(l_offer))
                          .build();
            END IF;
          END IF;
         
         {response} := l_resp;

        END
      ]'
    );
    
  xxas_com_sr_pkg.set_rest_method
    (p_service_name       => l_SERVICE
    ,p_method_name        => 'CreateApplicantEmptyOffer'
    ,p_description        => 'Creates a empty skeleton offer for applicant'
    ,p_path               => 'offers/create'
    ,p_methods            => 'POST'
    ,p_plsql              =>
      q'[
        DECLARE
          l_offer xxas_irc_offer;
          l_resp  xxas_http_resp_obj;
          
          CURSOR csr_apl IS
          SELECT *
          FROM   xxas_vacancy_applicants_v a
          WHERE  a.assignment_id = {requestParam.applicantId};
          
          l_apl  csr_apl%ROWTYPE;
          
        BEGIN
        
          OPEN  csr_apl;
          FETCH csr_apl INTO l_apl;
          CLOSE csr_apl;
		  
		  IF NOT xxas_com_sec_pkg.test_instance_rtn_boolean('XXAS_DATA_VAC_VIEW', {pathParam.vacancyId}) OR
             NOT xxas_com_sec_pkg.test_instance_rtn_boolean('XXAS_APL_CREATE_OFFER_PERM', l_apl.assignment_status_type_id) OR
             nvl(xxas_com_sec_pkg.person_on_rec_team_edit_flag({pathParam.vacancyId},'Y','Y'),'N') = 'N' THEN
            
            l_resp := xxas_http_response
                        .status(xxas_http_resp_status_pkg.unauthorised)
                        .build();
          ELSE
            l_offer :=
              xxas_offer_pkg.create_empty_offer
               (p_assignment_id => {requestParam.applicantId});
			   
            IF l_offer 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
                            ('create-offer/' ||to_char({pathParam.vacancyId}) ||'_'||to_char({requestParam.applicantId})|| '_' || to_char(l_offer.offer_id) 
                            ,'modal'
                            ,'true'
                            )
                         )
                      .build();						
            END IF;
          END IF;
         
         {response} := l_resp;

        END
      ]'
    );
	
  xxas_com_sr_pkg.set_rest_method
    (p_service_name       => l_SERVICE
    ,p_method_name        => 'GetVacancyOffer'
    ,p_description        => 'Get one particular offer'
    ,p_path               => 'applicants/{applicantId}/offers/{offerId}'
    ,p_methods            => 'GET'
    ,p_sql                =>
      q'[
        SELECT XMLElement
                 ("data"
                 ,XMLAttributes
                   ('true' as "json:force-array"
                   ,'http://json.org/' as "xmlns:json")
                 ,XMLAgg
                   (XMLElement
                     ("XXAS_VACANCY_OFFER"
                     ,XMLForest
                       (vo.offer_id id
                       ,va.person_id
                       ,xxas_com_rest_util_pkg.uri_from_path
                           ('/vacancies/' || {pathParam.vacancyId} || '/applicants/' || va.assignment_id || '/offers/' || vo.offer_id) href
                       ,xxas_com_rest_util_pkg.uri_from_path
                           ('/vacancies/' || {pathParam.vacancyId} || '/applicants/' || va.assignment_id || '/offers/' || vo.offer_id || '/actions') actions_href
                       ,xxas_com_rest_util_pkg.uri_from_path
                           ('/vacancies/' || {pathParam.vacancyId} || '/applicants/' || va.assignment_id || '/offers/' || vo.offer_id || '/view-schema') view_schema_href
                       ,xxas_com_rest_util_pkg.uri_from_path
                           ('/vacancies/' || {pathParam.vacancyId} || '/applicants/' || va.assignment_id || '/offers/' || vo.offer_id || '/form-schema') form_schema_href
                       ,va.image_href
                       ,va.person_name
                       ,xxas_util_pkg.flag_to_boolean(xxas_com_sec_pkg.test_instance('XXAS_DATA_VAC_OFF_UPD', to_char(vo.offer_id))) edit_allowed
                       ,xxas_com_msg_pkg.get_string('XXAS_RB_COM_EDIT') edit_label
                       ,xxas_util_pkg.flag_to_boolean(xxas_com_sec_pkg.test_instance('XXAS_DATA_VAC_OFF_DEL', to_char(vo.offer_id))) delete_allowed
                       ,xxas_com_msg_pkg.get_string('XXAS_RB_COM_DEL') delete_label
                       ,'location' contact1_icon
                       ,va.town_or_city
                         || decode(va.region_1, null, null, ', ')
                         || xxas_util_pkg.decode_lookup(va.country || '_COUNTY', va.region_1) contact1_text
                       ,'smartphone' contact2_icon
                       ,va.phone_number contact2_text
                       ,'email' contact3_icon
                       ,va.email_address contact3_text
                       ,vo.status_meaning
                       ,vo.proposed_start_date
                       )
                     )
                   ORDER BY va.last_name, va.person_name
                   )
                 ) AS "data"
        FROM   xxas_vacancy_applicants_v va
              ,xxas_vacancy_offers_v vo
        WHERE  vo.offer_id = {pathParam.offerId}
        AND    vo.applicant_assignment_id = va.assignment_id
        AND    xxas_com_sec_pkg.test_instance('XXAS_DATA_VAC_VIEW', va.vacancy_id) = 'Y'
      ]'
    );

  xxas_com_sr_pkg.set_rest_method
    (p_service_name       => l_SERVICE
    ,p_method_name        => 'DeleteOffer'
    ,p_description        => 'Deletes the given offer'
    ,p_methods            => 'DELETE'
    ,p_path               => 'applicants/{applicantId}/offers/{offerId}'
    ,p_plsql              =>
      q'[
        DECLARE
          l_offer xxas_irc_offer;
          l_resp  xxas_http_resp_obj;
        BEGIN
       
          IF NOT xxas_com_sec_pkg.test_instance_rtn_boolean
            ('XXAS_DATA_VAC_DEL', {pathParam.offerId}) THEN
            l_resp := xxas_http_response
                        .status(xxas_http_resp_status_pkg.unauthorised)
                        .build();
          ELSE
            xxas_offer_pkg.delete_offer
              (p_offer_id => {pathParam.offerId});       
            l_resp := xxas_http_response
                        .status(xxas_http_resp_status_pkg.no_content)
                        .build();
          END IF;
         
          {response} := l_resp;

        END
      ]'
    );

  xxas_com_sr_pkg.set_rest_method
    (p_service_name       => l_SERVICE
    ,p_method_name        => 'GetOfferViewSchema'
    ,p_description        => 'Gets an offer view schema'
    ,p_methods            => 'GET'
    ,p_path               => 'applicants/{applicantId}/offers/{offerId}/view-schema'
    ,p_sql                =>
      q'[
        SELECT xxas_offer_pkg.get_view_schema_xml
                (nvl2
                  (o.transaction_id
                  ,xxas_offer_pkg.trans_to_obj(o.transaction_id)
                  ,xxas_offer_pkg.base_to_obj(o.offer_id))) "SCHEMA"
        FROM   (SELECT  null transaction_id
                       ,rslt.offer_id
                FROM    xxas_vacancy_offers_v rslt
                WHERE   rslt.offer_id = {pathParam.offerId}
                AND     xxas_com_sec_pkg.test_instance('XXAS_DATA_VAC_VIEW', rslt.vacancy_id) = 'Y'
               ) o
      ]'
    );
    
  xxas_com_sr_pkg.set_rest_method
    (p_service_name       => l_SERVICE
    ,p_method_name        => 'PutOfferViewSchema'
    ,p_description        => 'Refreshes an offer view schema'
    ,p_methods            => 'PUT'
    ,p_path               => 'applicants/{applicantId}/offers/{offerId}/view-schema'
    ,p_plsql              =>
      q'[
        DECLARE
          l_body     XMLType;
          l_schema   XMLType;
        BEGIN
       
          --
          -- Return an updated schema based on the given data
          --
          l_body := XMLType({requestBody});
          l_schema := xxas_offer_pkg.get_view_schema_xml
            (p_assignment_id      => {pathParam.applicantId}
            ,p_business_group_id  => xxas_com_asg_pkg.get_bg_from_asg({pathParam.applicantId})
            ,p_view_data_xml      => l_body
            ,p_refresh_event      => {requestParam.event}
            ,p_refresh_field_name => {requestParam.field});
              
          SELECT XMLElement
                   ("SCHEMA"
                   ,l_schema)
          INTO   l_schema
          FROM   dual;
          
          {response} := xxas_http_response.ok().entity(l_schema).build();

        END
      ]'
    );
    
  xxas_com_sr_pkg.set_rest_method
    (p_service_name       => l_SERVICE
    ,p_method_name        => 'GetOfferFormSchema'
    ,p_description        => 'Gets an offer form schema'
    ,p_methods            => 'GET'
    ,p_path               => 'applicants/{applicantId}/offers/{offerId}/form-schema'
    ,p_sql                =>
      q'[
        SELECT xxas_offer_pkg.get_form_schema_xml
                (nvl2
                  (o.transaction_id
                  ,xxas_offer_pkg.trans_to_obj(o.transaction_id)
                  ,xxas_offer_pkg.base_to_obj(o.offer_id))) "SCHEMA"
        FROM   (SELECT  rslt.transaction_id
                       ,null offer_id
                FROM    hr_api_transactions rslt
                WHERE   rslt.transaction_ref_id = {pathParam.offerId}
                AND     rslt.transaction_ref_table = 'IRC_OFFERS'
                AND     rslt.status NOT IN ('D', 'N', 'W', 'ACTIVE', 'AC')
                AND    {authFunctionPredicate.XXAS_DATA_TRANS_VIEW}
                UNION
                SELECT  null transaction_id
                       ,rslt.offer_id
                FROM    xxas_vacancy_offers_v rslt
                WHERE   rslt.offer_id = {pathParam.offerId}
                AND     xxas_com_sec_pkg.test_instance('XXAS_DATA_VAC_VIEW', rslt.vacancy_id) = 'Y'
               ) o
      ]'
    );
    
  xxas_com_sr_pkg.set_rest_method
    (p_service_name       => l_SERVICE
    ,p_method_name        => 'PutOfferFormSchema'
    ,p_description        => 'Refreshes an offer form schema'
    ,p_methods            => 'PUT'
    ,p_path               => 'applicants/{applicantId}/offers/{offerId}/form-schema'
    ,p_plsql              =>
      q'[
        DECLARE
          l_body     XMLType;
          l_schema   XMLType;
          l_commit   boolean := false;
          l_resp     xxas_http_resp_obj;
          l_messages varchar2(400);
        BEGIN
       
          l_body := XMLType({requestBody});
          l_commit := (nvl({requestParam.submit}, 'false') = 'true');
       
          --
          -- Save the offer
          --
          IF l_commit THEN
          
            l_messages := xxas_offer_pkg.commit_form_data
              (p_offer_id        => {pathParam.offerId}
              ,p_form_data_xml   => l_body);         
            
            l_resp := xxas_http_response.ok().build();
          
          ELSE
            --
            -- Return an updated schema based on the given form data
            --
            l_schema := xxas_offer_pkg.get_form_schema_xml
              (p_offer_id           => {pathParam.offerId}
              ,p_assignment_id      => {pathParam.applicantId}
              ,p_business_group_id  => xxas_com_asg_pkg.get_bg_from_asg({pathParam.applicantId})
              ,p_form_data_xml      => l_body
              ,p_refresh_event      => {requestParam.event}
              ,p_refresh_field_name => {requestParam.field});
                
            SELECT XMLElement
                     ("SCHEMA"
                     ,l_schema)
            INTO   l_schema
            FROM   dual;
            
            l_resp := xxas_http_response.ok().entity(l_schema).build();
            
          END IF;

          {response} := l_resp;

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