/* $Id: xxas_rest_missed_punch_upd.sql 6216 2018-10-23 05:56:55Z ybudamala $ */
/*============================================================================*
 |                 Copyright (c) 2019 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
                       := 'MissedPunch';

BEGIN

  xxas_com_sr_pkg.set_rest_service
    (p_service_name       => l_SERVICE
    ,p_description        => 'MissedPunch'
    ,p_consumes           => 'application/xml'
    ,p_produces           => 'application/xml'
    ,p_path               => 'missed-punch');

  xxas_com_sr_pkg.set_rest_method
    (p_service_name       => l_SERVICE
    ,p_method_name        => 'MissedPunchForm'
    ,p_description        => 'Get Missed Punch Form'
    ,p_methods            => 'GET'
    ,p_remove_root_ele    => true
    ,p_path               => 'form'
    ,p_sql                =>
      q'[  
          SELECT xxas_tcm_missed_punch_pkg.entry_form
                   ({requestParam.personId}
                   ,{requestParam.assignmentId}
                   ,{requestParam.pendingClockId}) "data"
          FROM   dual
      ]'
    );

  xxas_com_sr_pkg.set_rest_method
    (p_service_name       => l_SERVICE
    ,p_method_name        => 'MissedPunchView'
    ,p_description        => 'Get Missed Punch View'
    ,p_methods            => 'GET'
    ,p_remove_root_ele    => true
    ,p_path               => 'view/{assignmentId}/log-entry/{logEntryId}'
    ,p_sql                =>
      q'[
          SELECT xxas_tcm_missed_punch_pkg.entry_view
                   ({requestParam.personId}
                   ,{requestParam.assignmentId}
                   ,{requestParam.pendingClockId}) "data"
          FROM   dual
      ]'
    );

  xxas_com_sr_pkg.set_rest_method
    (p_service_name       => l_SERVICE
    ,p_method_name        => 'DeleteMissedPunch'
    ,p_description        => 'Deletes a Missed Punch Entry'
    ,p_path               => '{assignmentId}/entry/{pendingClockId}'
    ,p_methods            => 'DELETE'
    ,p_plsql              =>
      q'[
        DECLARE
          l_resp          xxas_http_resp_obj;
          l_response_xml  XMLType;
          l_access_flag   varchar2(1);
        BEGIN

          IF nvl({requestParam.noAccess},'N') = 'Y' THEN

            -- Check access to delete the entry
            IF xxas_com_sec_pkg.test_instance('XXAS_DT_MISSED_PUNCH_NA_DELETE',{pathParam.pendingClockId},{pathParam.assignmentId}) = 'Y' THEN

              l_access_flag := 'Y';

            ELSE

              l_access_flag := 'N';

            END IF;

          ELSE

            -- Check access to delete the entry
            IF xxas_com_sec_pkg.test_instance('XXAS_DT_MISSED_PUNCH_DELETE',{pathParam.pendingClockId},{pathParam.assignmentId}) = 'Y' THEN

              l_access_flag := 'Y';

            ELSE

              l_access_flag := 'N';

            END IF;

          END IF;

          IF l_access_flag = 'Y' THEN

            xxas_tcm_missed_punch_pkg.delete_punch_entry
              ({pathParam.pendingClockId}
              ,{pathParam.assignmentId});

            l_resp := xxas_http_response
                        .ok(xxas_com_msg_pkg.get_success_message_xml
                              (xxas_com_msg_type
                                 (xxas_util_pkg.app
                                 ,'XXAS_COM_CHNG_SAVE'
                                 ,null
                                 )
                              ,'self')
                           )
                        .build();

          ELSE

            l_resp := xxas_http_response
                        .status(xxas_http_resp_status_pkg.unauthorised)
                        .build();

          END IF;

          {response} := l_resp;

        END
      ]'
    );

  xxas_com_sr_pkg.set_rest_method
    (p_service_name       => l_SERVICE
    ,p_method_name        => 'FormEmployeeDetails'
    ,p_description        => 'Get Missed Punch Form Employee Details'
    ,p_methods            => 'GET'
    ,p_remove_root_ele    => true
    ,p_path               => 'form/employee-details'
    ,p_sql                =>
      q'[
        SELECT XMLElement
                 ("data"
                 ,XMLAttributes
                   ('true' as "json:force-array"
                   ,'http://json.org/' as "xmlns:json")
                 ,XMLAgg
                   (XMLElement
                     ("person"
                     ,XMLForest
                       (p.person_id  "id"
                       ,p.short_name "value"
                       ,p.short_name "label")
                     ,XMLElement
                       ("linked"
                       ,XMLElement
                         ("image"
                         ,XMLForest
                           (xxas_com_rest_util_pkg.uri_from_path
                              ('people/' || p.img_person_id || '/photo') "self"
                           ,p.initials "initials"
                           ,xxas_per_image_pkg.url
                              (p.img_person_id, p.img_last_update_date, 'SMALL') "small"
                           )
                         )
                       )
                     )
                   )
                 ) "data"
        FROM  (SELECT /*+ cardinality(rslt 10)*/
                      distinct rslt.person_id
                     ,rslt.short_name
                     ,rslt.initials
                     ,img.person_id              img_person_id
                     ,img.last_update_date       img_last_update_date
               FROM   TABLE(xxas_per_search_pkg.people_tbl
                             ({requestParam.q}, trunc(sysdate))) rslt
                     ,xxas_per_thumbnails img
               WHERE  {requestParam.id} is null
               AND    rslt.person_id = img.person_id(+)
               UNION
               SELECT rslt.person_id
                     ,xxas_util_pkg.get_formatted_name
                        (rslt.full_name
                        ,rslt.global_name
                        ,rslt.local_name
                        ,rslt.first_name
                        ,rslt.last_name
                        ,rslt.known_as) short_name
                     ,xxas_util_pkg.get_initials(rslt.first_name, rslt.last_name, rslt.known_as) initials
                     ,img.person_id              img_person_id
                     ,img.last_update_date       img_last_update_date
               FROM   per_all_people_f rslt
                     ,xxas_per_thumbnails img
               WHERE  {requestParam.id} is not null
               AND    rslt.person_id = {requestParam.id}
               AND    DECODE({requestParam.dateStart},'-1',trunc(sysdate),fnd_date.canonical_to_date({requestParam.dateStart})) BETWEEN rslt.effective_start_date 
                                                                                                                                AND     rslt.effective_end_date
               AND    rslt.person_id = img.person_id(+)) p
      ]'
    );

  xxas_com_sr_pkg.set_rest_method
    (p_service_name       => l_SERVICE
    ,p_method_name        => 'FormEmployeeWorkerAssignment'
    ,p_description        => 'Get Missed Punch Form Employee Worker Assignment'
    ,p_methods            => 'GET'
    ,p_remove_root_ele    => true
    ,p_path               => 'form/emp-worker-asg'
    ,p_sql                =>
      q'[
        SELECT XMLElement
                 ("data"
                 ,XMLAttributes
                   ('true' as "json:force-array"
                   ,'http://json.org/' as "xmlns:json")
                 ,XMLAgg
                   (XMLElement
                     ("person"
                     ,XMLForest
                       (p.assignment_id "id"
                       ,decode
                         (p.result_text
                         ,null
                         ,xxas_cal_util_pkg.job_name(p.job_id)
                         ,p.result_text) "value"
                       ,decode
                         (p.result_text
                         ,null
                         ,xxas_cal_util_pkg.job_name(p.job_id)
                         ,p.result_text) "label")
                     ,XMLElement
                       ("linked"
                       ,XMLElement
                         ("image"
                         ,XMLForest
                           (xxas_com_rest_util_pkg.uri_from_path
                              ('people/' || p.img_person_id || '/photo') "self"
                           ,p.initials "initials"
                           ,xxas_per_image_pkg.url
                              (p.img_person_id, p.img_last_update_date, 'SMALL') "small"
                           )
                         )
                       )
                     )
                   )
                 ) "data"
        FROM  (SELECT /*+ cardinality(rslt 10)*/
                      rslt.assignment_id
                     ,rslt.result_text
                     ,null job_id
                     ,rslt.initials
                     ,img.person_id              img_person_id
                     ,img.last_update_date       img_last_update_date
               FROM   TABLE(xxas_per_search_pkg.people_tbl
                             ({requestParam.q}, trunc(sysdate))) rslt
                     ,xxas_per_thumbnails img
               WHERE  {requestParam.id} is null
               AND    rslt.person_id = img.person_id(+)
               AND    rslt.person_id = {requestParam.personId}
               UNION
               SELECT rslt.assignment_id
                     ,null result_text
                     ,rslt.job_id job_id
                     ,xxas_util_pkg.get_initials(papf.first_name, papf.last_name, papf.known_as) initials
                     ,img.person_id              img_person_id
                     ,img.last_update_date       img_last_update_date
               FROM   per_all_people_f papf
                     ,per_all_assignments_f rslt
                     ,xxas_per_thumbnails img
               WHERE  {requestParam.id} is not null
               AND    papf.person_id = rslt.person_id
               AND    trunc(sysdate) BETWEEN papf.effective_start_date 
                                     AND     papf.effective_end_date
               AND    rslt.assignment_id = {requestParam.id}
               AND    DECODE({requestParam.dateStart},'-1',trunc(sysdate),fnd_date.canonical_to_date({requestParam.dateStart})) BETWEEN rslt.effective_start_date
                                                                                                                                AND     rslt.effective_end_date
               AND    papf.person_id = img.person_id(+)) p
      ]'
    );

  xxas_com_sr_pkg.set_rest_method
    (p_service_name       => l_SERVICE
    ,p_method_name        => 'FormPaymentTypes'
    ,p_description        => 'Get Missed Punch Form Payment Types'
    ,p_methods            => 'GET'
    ,p_remove_root_ele    => true
    ,p_path               => 'form/payment-types'
    ,p_sql                =>
      q'[
        SELECT XMLElement
                 ("data"
                 ,XMLAttributes
                   ('true' as "json:force-array"
                   ,'http://json.org/' as "xmlns:json")
                 ,XMLAgg
                   (XMLElement
                     ("paymenttype"
                     ,XMLForest
                       (rslt.element_type_id "id"
                       ,rslt.element_name    "value"
                       ,rslt.element_name    "label")
                     ) ORDER BY rslt.information18, rslt.element_name
                   )
                 ) "data"
        FROM  (SELECT e.element_type_id
                     ,nvl(eit.eei_information3,e.element_name) element_name
                     ,nvl(to_number(eit.eei_information18),999) information18
               FROM   pay_element_types_f pet
                     ,TABLE(xxas_com_elements_pkg.links_tbl({requestParam.asgId}, fnd_date.canonical_to_date({requestParam.effDate}))) e
                     ,pay_element_type_extra_info eit
               WHERE  pet.element_type_id = e.element_type_id
               AND    e.element_type_id = eit.element_type_id
               AND    eit.information_type = 'XXAS_TCM_ENTRY_MAP'
               AND    eit.eei_information1 is not null
               AND    eit.eei_information1 = 'Y'
               AND    ((nvl(eit.eei_information20,'N') = 'N' AND nvl(eit.eei_information21,'N') = 'N')
                       OR ((nvl(eit.eei_information20,'N') = 'Y' OR nvl(eit.eei_information21,'N') = 'Y')
                          )
                      )
               AND    fnd_date.canonical_to_date({requestParam.effDate}) BETWEEN pet.effective_start_date 
                                                                         AND     pet.effective_end_date
               AND    {requestParam.id} is null
               UNION
               SELECT e.element_type_id
                     ,nvl(eit.eei_information3,e.element_name) element_name
                     ,nvl(to_number(eit.eei_information18),999) information18
               FROM   pay_element_types_f pet
                     ,TABLE(xxas_com_elements_pkg.links_tbl({requestParam.asgId}, fnd_date.canonical_to_date({requestParam.effDate}))) e
                     ,pay_element_type_extra_info eit
               WHERE  {requestParam.id} is not null
               AND    pet.element_type_id  = {requestParam.id}
               AND    pet.element_type_id  = e.element_type_id
               AND    e.element_type_id    = eit.element_type_id
               AND    eit.information_type = 'XXAS_TCM_ENTRY_MAP'
               AND    eit.eei_information1 is not null
               AND    eit.eei_information1 = 'Y'
               AND    ((nvl(eit.eei_information20,'N') = 'N' AND nvl(eit.eei_information21,'N') = 'N') 
                       OR ((nvl(eit.eei_information20,'N') = 'Y' OR nvl(eit.eei_information21,'N') = 'Y')
                          )
                      )
               AND    fnd_date.canonical_to_date({requestParam.effDate}) BETWEEN pet.effective_start_date 
                                                                         AND     pet.effective_end_date
              ) rslt
      ]'
    );

END;
/
COMMIT;
EXIT;
