/* $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
                       := 'Vacancies';
                       
BEGIN
      
  xxas_com_sr_pkg.set_rest_service
    (p_service_name       => l_SERVICE
    ,p_description        => 'View and transact vacancies'
    ,p_consumes           => 'application/xml'
    ,p_produces           => 'application/xml'
    );
    
  xxas_com_sr_pkg.set_rest_method
    (p_service_name       => l_SERVICE
    ,p_method_name        => 'GetVacancies'
    ,p_description        => 'Get a list of vacancies'
    ,p_path               => 'vacancies'
    ,p_methods            => 'GET'
    ,p_remove_root_ele    => true
    ,p_sql                =>
      q'[
         SELECT XMLELEMENT
                  ("data" 
                  ,XMLELEMENT
                    ("items" 
                    ,XMLAttributes('http://json.org/' AS "xmlns:json" ,'true' AS "json:force-array")
                    ,xxas_com_sec_pkg.get_obj_data
                      (decode(xxas_com_sec_pkg.function_exists_flag('XXX_XXAS_VACANCIES_PUB')
                             ,'Y', 'XXX_XXAS_VACANCIES_PUB'
                             ,'XXAS_VACANCIES_PUB')
                      ,null /* p_instance_pk1_value */
                      ,null /* p_instance_pk2_value */
                      ,null /* p_instance_pk3_value */
                      ,null /* p_instance_pk4_value */
                      ,null /* p_instance_pk5_value */
                      ,null /* p_rowset_tag_name */
                      ,'item' /* p_row_tag_name */)
                    )                    
                  ) "data"
          FROM dual
      ]'
    );
   
  xxas_com_sr_pkg.set_rest_method
    (p_service_name       => l_SERVICE
    ,p_method_name        => 'GetRecruiterVacancies'
    ,p_description        => 'Get a list of vacancies'
    ,p_path               => '/people/{personId}/vacancies'
    ,p_methods            => 'GET'
    ,p_remove_root_ele    => true
    ,p_sql                =>
      q'[
        SELECT XMLElement
                ("DATA"
                ,XMLElement
                  ("META"
                  ,XMLElement
                    ("APPLICANT_STATUS_CHANGE_REASONS"
                    ,XMLElement
                      ("HREF"
                      ,xxas_com_rest_util_pkg.uri_from_path('/lookups/APL_ASSIGN_REASON')
                      )
                    )
                  ,XMLElement
                    ("CREATE"
                    ,XMLForest
                      (xxas_com_msg_pkg.get_string('XXAS_RB_CREATE_VAC') as "LABEL"
                      ,xxas_util_pkg.flag_to_boolean
                         (xxas_com_sec_pkg.test_instance
                           ('XXAS_DATA_VAC_INS', {pathParam.personId})) as "ALLOWED"
                      ,'POST' as "METHOD"
                      ,xxas_com_rest_util_pkg.uri_from_path('people/' || {pathParam.personId} || '/vacancies') as "HREF"
                      )
                    )
                  )
                ,XMLElement
                  ("VACANCIES"
                  ,(SELECT XMLElement
                            ("DRAFT"
                            ,XMLElement
                              ("TITLE", 'XXAS_RB_VAC_DRAFT_COUNT')
                            ,XMLElement
                              ("COUNT", min(v.row_count))
                            ,XMLAgg
                              (xxas_vacancy_pkg.obj_to_xml
                                (xxas_vacancy_pkg.trans_to_obj(v.transaction_id)))
                             )
                    FROM   (SELECT rslt.transaction_id
                                  ,COUNT(*) OVER (PARTITION BY 0) row_count
                            FROM   hr_api_transactions rslt
                            WHERE  rslt.creator_person_id = {pathParam.personId} /* rslt.selected_person_id = null, so better if this can be populated instead */
                            AND    rslt.transaction_ref_table = 'PER_ALL_VACANCIES'
                            AND    rslt.status NOT IN ('D', 'N', 'W', 'ACTIVE', 'AC')
                            AND    {authFunctionPredicate.XXAS_DATA_TRANS_VIEW}
                            ORDER BY CASE WHEN {requestParam.sort} = 'creationDate'  
                                                                      THEN rslt.CREATION_DATE
                                                                      ELSE rslt.LAST_UPDATE_DATE
                                                END DESC) v)
                  ,(SELECT XMLElement
                            ("HIRING_MANAGER"
                            ,XMLElement
                              ("TITLE", 'XXAS_RB_VAC_HIRE_MGR_COUNT')
                            ,XMLElement
                              ("COUNT", min(v.row_count))
                            ,XMLAgg
                              (xxas_vacancy_pkg.obj_to_xml
                                (xxas_vacancy_pkg.base_to_obj(v.vacancy_id))
                              )
                             )
                    FROM   (SELECT rslt.vacancy_id
                                  ,COUNT(*) OVER (PARTITION BY 0) row_count
                            FROM   per_all_vacancies rslt
                            WHERE  rslt.manager_id IS NOT NULL
                            AND    rslt.manager_id = {pathParam.personId}
                            AND    {authFunctionPredicate.XXAS_DATA_VAC_VIEW}
                            ORDER BY CASE WHEN {requestParam.sort} = 'creationDate'  
                                                                      THEN rslt.CREATION_DATE
                                                                      ELSE rslt.LAST_UPDATE_DATE
                                                END DESC) v
                    WHERE   rownum &lt;= nvl({requestParam.per_page}, 25))
                  ,(SELECT XMLElement
                            ("REC_MANAGER"
                            ,XMLElement
                              ("TITLE", 'XXAS_RB_VAC_REC_MGR_COUNT')
                            ,XMLElement
                              ("COUNT", min(v.row_count))
                            ,XMLAgg
                              (xxas_vacancy_pkg.obj_to_xml
                                (xxas_vacancy_pkg.base_to_obj(v.vacancy_id))
                              )
                             )
                    FROM   (SELECT rslt.vacancy_id
                                  ,COUNT(*) OVER (PARTITION BY 0) row_count
                            FROM   per_all_vacancies rslt
                            WHERE (rslt.manager_id IS NULL OR
                                   rslt.manager_id &lt;&gt; {pathParam.personId})
                            AND    rslt.recruiter_id IS NOT NULL
                            AND    rslt.recruiter_id = {pathParam.personId}
                            AND   {authFunctionPredicate.XXAS_DATA_VAC_VIEW}
                            ORDER BY CASE WHEN {requestParam.sort} = 'creationDate'  
                                                                      THEN rslt.CREATION_DATE
                                                                      ELSE rslt.LAST_UPDATE_DATE
                                                END DESC) v
                    WHERE   rownum &lt;= nvl({requestParam.per_page}, 25))
                  ,(SELECT XMLElement
                            ("TEAM_MEMBER"
                            ,XMLElement
                              ("TITLE", 'XXAS_RB_VAC_REC_TEAM_COUNT')
                            ,XMLElement
                              ("COUNT", min(v.row_count))
                            ,XMLAgg
                              (xxas_vacancy_pkg.obj_to_xml
                                (xxas_vacancy_pkg.base_to_obj(v.vacancy_id))
                              )
                             )
                    FROM   (SELECT rslt.vacancy_id
                                  ,COUNT(*) OVER (PARTITION BY 0) row_count
                            FROM   per_all_vacancies rslt
                                  ,irc_rec_team_members tm
                            WHERE  rslt.vacancy_id = tm.vacancy_id
                            AND    tm.person_id = {pathParam.personId}
                            AND    trunc(sysdate) BETWEEN
                                   nvl(tm.start_date, trunc(sysdate)) AND nvl(tm.end_date, trunc(sysdate))
                            AND   (rslt.recruiter_id IS NULL OR
                                   rslt.recruiter_id &lt;&gt; {pathParam.personId})
                            AND   (rslt.manager_id IS NULL OR
                                   rslt.manager_id &lt;&gt; {pathParam.personId})
                            AND   {authFunctionPredicate.XXAS_DATA_VAC_VIEW}
                            ORDER BY CASE WHEN {requestParam.sort} = 'creationDate'  
                                                                      THEN rslt.CREATION_DATE
                                                                      ELSE rslt.LAST_UPDATE_DATE
                                                END DESC) v
                    WHERE   rownum &lt;= nvl({requestParam.per_page}, 25))
                  )
                ) "data"
        FROM dual
      ]'
    );
    
  xxas_com_sr_pkg.set_rest_method
    (p_service_name       => l_SERVICE
    ,p_method_name        => 'GetRecruiterVacancy'
    ,p_description        => 'Get one vacancy'
    ,p_methods            => 'GET'
    ,p_path               => '/people/{personId}/vacancies/{vacancyId}'
    ,p_sql                =>
      q'[
        SELECT xxas_vacancy_pkg.obj_to_xml
                (nvl2
                  (v.transaction_id
                  ,xxas_vacancy_pkg.trans_to_obj(v.transaction_id)
                  ,xxas_vacancy_pkg.base_to_obj(v.vacancy_id))) vacancies
        FROM   (SELECT  rslt.transaction_id
                       ,null vacancy_id
                FROM    hr_api_transactions rslt
                WHERE   rslt.transaction_ref_id = {pathParam.vacancyId}
                AND     rslt.transaction_ref_table = 'PER_ALL_VACANCIES'
                AND     rslt.status NOT IN ('D', 'N', 'W', 'ACTIVE', 'AC')
                AND    {authFunctionPredicate.XXAS_DATA_TRANS_VIEW}
                UNION
                SELECT  null transaction_id
                       ,rslt.vacancy_id
                FROM    per_all_vacancies rslt
                WHERE   rslt.vacancy_id = {pathParam.vacancyId}
                AND    {authFunctionPredicate.XXAS_DATA_VAC_VIEW}
               ) v
      ]'
    );
       
  xxas_com_sr_pkg.set_rest_method
    (p_service_name       => l_SERVICE
    ,p_method_name        => 'CreateEmptyVacancy'
    ,p_description        => 'Creates a empty skeleton vacancy'
    ,p_methods            => 'POST'
    ,p_path               => '/people/{personId}/vacancies'
    ,p_plsql              =>
      q'[
        DECLARE
          l_vacancy xxas_per_all_vacancy;
          l_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
                          .created(l_vacancy.href)
                          .entity(xxas_vacancy_pkg.obj_to_xml(l_vacancy))
                          .build();
            END IF;
          END IF;
         
         {response} := l_resp;

        END
      ]'
    );

  xxas_com_sr_pkg.set_rest_method
    (p_service_name       => l_SERVICE
    ,p_method_name        => 'DeleteVacancy'
    ,p_description        => 'Deletes the given vacancy'
    ,p_methods            => 'DELETE'
    ,p_path               => '/people/{personId}/vacancies/{vacancyId}'
    ,p_plsql              =>
      q'[
        DECLARE
        
          l_vacancy        xxas_per_all_vacancy;
          l_resp           xxas_http_resp_obj;
          l_transaction_id NUMBER;
          
          CURSOR csr_transaction_id IS
          SELECT t.transaction_id
          FROM   hr_api_transactions t
          WHERE  t.transaction_ref_id = {pathParam.vacancyId}
          AND    t.transaction_ref_table = 'PER_ALL_VACANCIES';
          
        BEGIN
       
          OPEN  csr_transaction_id;
          FETCH csr_transaction_id INTO l_transaction_id;
          CLOSE csr_transaction_id;
       
          IF l_transaction_id IS NULL OR
           NOT xxas_com_sec_pkg.test_instance_rtn_boolean
                ('XXAS_DATA_TRANS_DEL', l_transaction_id) THEN
            l_resp := xxas_http_response
                        .status(xxas_http_resp_status_pkg.unauthorised)
                        .build();
          ELSE
            xxas_vacancy_pkg.delete_vacancy
              (p_vacancy_id => {pathParam.vacancyId});       
            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        => 'GetVacancyViewSchema'
    ,p_description        => 'Gets a vacancy view schema'
    ,p_methods            => 'GET'
    ,p_path               => '/people/{personId}/vacancies/{vacancyId}/view-schema'
    ,p_sql                =>
      q'[
        SELECT xxas_vacancy_pkg.get_view_schema_xml
                (nvl2
                  (v.transaction_id
                  ,xxas_vacancy_pkg.trans_to_obj(v.transaction_id)
                  ,xxas_vacancy_pkg.base_to_obj(v.vacancy_id))) "SCHEMA"
        FROM   (SELECT  rslt.transaction_id
                       ,null vacancy_id
                FROM    hr_api_transactions rslt
                WHERE   rslt.transaction_ref_id = {pathParam.vacancyId}
                AND     rslt.transaction_ref_table = 'PER_ALL_VACANCIES'
                AND     rslt.status NOT IN ('D', 'N', 'W', 'ACTIVE', 'AC')
                AND    {authFunctionPredicate.XXAS_DATA_TRANS_VIEW}
                UNION
                SELECT  null transaction_id
                       ,rslt.vacancy_id
                FROM    per_all_vacancies rslt
                WHERE   rslt.vacancy_id = {pathParam.vacancyId}
                AND    {authFunctionPredicate.XXAS_DATA_VAC_VIEW}
                AND NOT EXISTS 
                            (SELECT  rslt.transaction_id
                            ,null vacancy_id
                       FROM    hr_api_transactions rslt
                       WHERE   rslt.transaction_ref_id = {pathParam.vacancyId}
                       AND     rslt.transaction_ref_table = 'PER_ALL_VACANCIES'
                       AND     rslt.status NOT IN ('D', 'N', 'W', 'ACTIVE', 'AC')
                       AND    {authFunctionPredicate.XXAS_DATA_TRANS_VIEW})
               ) v


      ]'
    );
    
  xxas_com_sr_pkg.set_rest_method
    (p_service_name       => l_SERVICE
    ,p_method_name        => 'PutVacancyViewSchema'
    ,p_description        => 'Refreshes a vacancy view schema'
    ,p_methods            => 'PUT'
    ,p_path               => '/people/{personId}/vacancies/{vacancyId}/view-schema'
    ,p_plsql              =>
      q'[
        DECLARE
          l_body     XMLType;
          l_schema   XMLType;
        BEGIN
       
          l_body := XMLType({requestBody});
          
          --
          -- Return an updated schema based on the given data
          --
          l_schema := xxas_vacancy_pkg.get_view_schema_xml
            (p_person_id          => {pathParam.personId}
            ,p_business_group_id  => xxas_per_pkg.get_business_group_id({pathParam.personId})
            ,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        => 'GetVacancyFormSchema'
    ,p_description        => 'Gets a vacancy form schema'
    ,p_methods            => 'GET'
    ,p_path               => '/people/{personId}/vacancies/{vacancyId}/form-schema'
    ,p_sql                =>
      q'[
        SELECT xxas_vacancy_pkg.get_form_schema_xml
                (nvl2
                  (v.transaction_id
                  ,xxas_vacancy_pkg.trans_to_obj(v.transaction_id)
                  ,xxas_vacancy_pkg.base_to_obj(v.vacancy_id))) "SCHEMA"
        FROM   (SELECT  rslt.transaction_id
                       ,null vacancy_id
                FROM    hr_api_transactions rslt
                WHERE   rslt.transaction_ref_id = {pathParam.vacancyId}
                AND     rslt.transaction_ref_table = 'PER_ALL_VACANCIES'
                AND     rslt.status NOT IN ('D', 'N', 'W', 'ACTIVE', 'AC')
                AND    {authFunctionPredicate.XXAS_DATA_TRANS_VIEW}
                UNION
                SELECT  null transaction_id
                       ,rslt.vacancy_id
                FROM    per_all_vacancies rslt
                WHERE   rslt.vacancy_id = {pathParam.vacancyId}
                AND    {authFunctionPredicate.XXAS_DATA_VAC_VIEW}
               ) v
      ]'
    );
    
  xxas_com_sr_pkg.set_rest_method
    (p_service_name       => l_SERVICE
    ,p_method_name        => 'PutVacancyFormSchema'
    ,p_description        => 'Refreshes a vacancy form schema'
    ,p_methods            => 'PUT'
    ,p_path               => '/people/{personId}/vacancies/{vacancyId}/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_commit := (nvl({requestParam.submit}, 'false') = 'true');
       
          l_body := XMLType({requestBody});
       
          --
          -- Save the vacancy
          --
          IF l_commit THEN
          
            l_messages := xxas_vacancy_pkg.commit_form_data
              (p_vacancy_id        => {pathParam.vacancyId}
              ,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_vacancy_pkg.get_form_schema_xml
              (p_vacancy_id         => {pathParam.vacancyId}
              ,p_person_id          => {pathParam.personId}
              ,p_business_group_id  => xxas_per_pkg.get_business_group_id({pathParam.personId})
              ,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
      ]'
    );
    
	xxas_com_sr_pkg.set_rest_method
    (p_service_name       => l_SERVICE
    ,p_method_name        => 'GetLoginPage'
    ,p_path               => 'signin'
    ,p_description        => 'Get the sign in or up page'
    ,p_methods            => 'GET'
    ,p_remove_root_ele    => true
    ,p_sql                =>
      q'[
         SELECT  XMLELEMENT("ROW"
                         ,XMLFOREST((SELECT language_code
                                    FROM   fnd_languages 
                                    WHERE  nls_language = substr(USERENV('LANGUAGE'),1,instr(USERENV('LANGUAGE'),'_')-1)) LANGUAGE
                                   )
                                   ) "data"
        FROM    DUAL
      ]'
    );
END;
/
COMMIT;
EXIT;

