/* $Id$ */
/*============================================================================*
 |                 Copyright (c) 2017 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
                       := 'TimecardEntry';

BEGIN

  xxas_com_sr_pkg.set_rest_service
    (p_service_name       => l_SERVICE
    ,p_description        => 'TimecardEntry'
    ,p_consumes           => 'application/xml'
    ,p_produces           => 'application/xml'
    ,p_path               => 'timecard-entry');

  xxas_com_sr_pkg.set_rest_method
    (p_service_name       => l_SERVICE
    ,p_method_name        => 'TimecardEntryForm'
    ,p_description        => 'Get Timecard Entry Form'
    ,p_methods            => 'GET'
    ,p_remove_root_ele    => true
    ,p_path               => 'form'
    ,p_sql                =>
      q'[  
          SELECT xxas_timecard_entry_pkg.entry_form
                   ({requestParam.assignmentId}
                   ,{requestParam.startTime}
                   ,{requestParam.endTime}
                   ,{requestParam.sourceBlock}
                   ,{requestParam.savedSearchId}) "data"
          FROM   dual
      ]'
    );

  xxas_com_sr_pkg.set_rest_method
    (p_service_name       => l_SERVICE
    ,p_method_name        => 'TimecardEntryView'
    ,p_description        => 'Get Timecard Entry View'
    ,p_methods            => 'GET'
    ,p_remove_root_ele    => true
    ,p_path               => 'view/{assignmentId}/log-entry/{logEntryId}'
    ,p_sql                =>
      q'[
          SELECT xxas_timecard_entry_pkg.entry_view
                   ({pathParam.assignmentId}
                   ,{pathParam.logEntryId}
                   ,{requestParam.sourceBlock}
                   ,{requestParam.savedSearchId}) "data"
          FROM   dual
      ]'
    );

  xxas_com_sr_pkg.set_rest_method
    (p_service_name       => l_SERVICE
    ,p_method_name        => 'TimecardEntryEdit'
    ,p_description        => 'Get Timecard Entry Edit'
    ,p_methods            => 'GET'
    ,p_remove_root_ele    => true
    ,p_path               => 'edit/{assignmentId}/log-entry/{logEntryId}'
    ,p_sql                =>
      q'[
          SELECT xxas_timecard_entry_pkg.entry_edit
                   ({pathParam.assignmentId}
                   ,{pathParam.logEntryId}
                   ,{requestParam.times}
                   ,{requestParam.sourceBlock}
                   ,{requestParam.savedSearchId}) "data"
          FROM   dual
      ]'
    );

  xxas_com_sr_pkg.set_rest_method
    (p_service_name       => l_SERVICE
    ,p_method_name        => 'WeeklyTimecardEdit'
    ,p_description        => 'Get Weekly Timecard Edit'
    ,p_methods            => 'GET'
    ,p_remove_root_ele    => true
    ,p_path               => 'edit-weekly/{personId}'
    ,p_sql                =>
      q'[
          SELECT xxas_timecard_entry_pkg.edit_weekly_timecard_form
                   ({pathParam.personId}
                   ,{requestParam.startTime}
                   ,{requestParam.endTime}
                   ,{requestParam.paymentTypeId}
                   ,{requestParam.sourceBlock}
                   ,{requestParam.savedSearchId}
                   ,{requestParam.excludeTermBy}) "data"
          FROM   dual
      ]'
    );

  xxas_com_sr_pkg.set_rest_method
    (p_service_name       => l_SERVICE
    ,p_method_name        => 'DeleteTimecardEntry'
    ,p_description        => 'Deletes a Timecard Entry'
    ,p_path               => '{assignmentId}/log-entry/{logEntryId}'
    ,p_methods            => 'DELETE'
    ,p_plsql              =>
      q'[
        DECLARE
          l_resp          xxas_http_resp_obj;
          l_response_xml  XMLType;
        BEGIN

          -- Check access to delete the log entry

          IF xxas_com_sec_pkg.test_instance('XXAS_DT_LOG_TIME_DELETE',{pathParam.assignmentId},{pathParam.logEntryId}) = 'Y' THEN

            l_response_xml := xxas_timecard_entry_pkg.delete_timecard_entry
                                ({pathParam.assignmentId}
                                ,{pathParam.logEntryId}
                                ,replace({requestParam.sourceBlock},'_','$')
                                ,{requestParam.savedSearchId});

          END IF;

          l_resp := xxas_http_response
                      .ok(l_response_xml
                         )
                      .build();

          {response} := l_resp;

        END
      ]'
    );

  xxas_com_sr_pkg.set_rest_method
    (p_service_name       => l_SERVICE
    ,p_method_name        => 'UnDeleteTimecardEntry'
    ,p_description        => 'UnDelete a Timecard Entry'
    ,p_path               => 'undelete/{assignmentId}/log-entry/{logEntryId}'
    ,p_methods            => 'DELETE'
    ,p_plsql              =>
      q'[
        DECLARE
          l_resp          xxas_http_resp_obj;
          l_response_xml  XMLType;
        BEGIN

          -- Check access to undelete the log entry

          IF xxas_com_sec_pkg.test_instance('XXAS_DT_LOG_TIME_UNDELETE',{pathParam.assignmentId},{pathParam.logEntryId}) = 'Y' THEN

            l_response_xml := xxas_timecard_entry_pkg.undelete_timecard_entry
                                ({pathParam.assignmentId}
                                ,{pathParam.logEntryId}
                                ,replace({requestParam.sourceBlock},'_','$')
                                ,{requestParam.savedSearchId});

          END IF;

          l_resp := xxas_http_response
                      .ok(l_response_xml
                         )
                      .build();

          {response} := l_resp;

        END
      ]'
    );

  xxas_com_sr_pkg.set_rest_method
    (p_service_name       => l_SERVICE
    ,p_method_name        => 'RespondToDraftEntries'
    ,p_description        => 'Respond to one or more draft entries'
    ,p_path               => '{action}'
    ,p_methods            => 'DELETE'
    ,p_plsql              =>
      q'[
        DECLARE

          l_resp         xxas_http_resp_obj;
          l_request_xml  XMLType;
          l_response_xml XMLType;

        BEGIN

          l_request_xml := {requestBody};

          l_response_xml:= xxas_timecard_entry_pkg.bulk_draft_respond
                             (p_action       => {pathParam.action}
                             ,p_request_body => l_request_xml);

          l_resp := xxas_http_response
                      .ok(l_response_xml
                         )
                      .build();

          {response} := l_resp;

        END
      ]'
    );

  xxas_com_sr_pkg.set_rest_method
    (p_service_name       => l_SERVICE
    ,p_method_name        => 'RespondToDraftEntry'
    ,p_description        => 'Respond to single draft entry'
    ,p_path               => '{action}/draft-log-entry/{logEntryId}'
    ,p_methods            => 'DELETE'
    ,p_plsql              =>
      q'[
        DECLARE

          l_resp         xxas_http_resp_obj;
          l_response_xml XMLType;

        BEGIN

          l_response_xml := xxas_timecard_entry_pkg.draft_respond
                              (p_action          => {pathParam.action}
                              ,p_log_entry_id    => {pathParam.logEntryId}
                              ,p_source_block    => {requestParam.sourceBlock}
                              ,p_saved_search_id => {requestParam.savedSearchId});

          l_resp := xxas_http_response
                      .ok(l_response_xml
                         )
                      .build();

          {response} := l_resp;

        END
      ]'
    );

  xxas_com_sr_pkg.set_rest_method
    (p_service_name       => l_SERVICE
    ,p_method_name        => 'SubmitWeeklyTimecardDraftEntries'
    ,p_description        => 'Submit weekly timecard draft entries'
    ,p_path               => 'submit-weekly-draft-entries/{personId}'
    ,p_methods            => 'POST'
    ,p_plsql              =>
      q'[
        DECLARE

          l_resp         xxas_http_resp_obj;
          l_response_xml XMLType;

        BEGIN

          l_response_xml := xxas_timecard_entry_pkg.submit_weekly_draft_entries
                              (p_person_id       => {pathParam.personId}
                              ,p_start_time      => {requestParam.startTime}
                              ,p_end_time        => {requestParam.endTime}
                              ,p_payment_type_id => {requestParam.paymentTypeId}
                              ,p_exclude_term_by => {requestParam.excludeTermBy}
                              ,p_source_block    => {requestParam.sourceBlock}
                              ,p_saved_search_id => {requestParam.savedSearchId});

          l_resp := xxas_http_response
                      .ok(l_response_xml
                         )
                      .build();

          {response} := l_resp;

        END
      ]'
    );

  xxas_com_sr_pkg.set_rest_method
    (p_service_name       => l_SERVICE
    ,p_method_name        => 'TimecardEntryFormPaymentTypes'
    ,p_description        => 'Get Timecard Entry 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_global.employee_id not in (SELECT person_id 
                                                              FROM   per_all_assignments_f 
                                                              WHERE  assignment_id = {requestParam.asgId})
                          )
                      )
               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_global.employee_id not in (SELECT person_id 
                                                              FROM   per_all_assignments_f 
                                                              WHERE  assignment_id = {requestParam.asgId})
                          )
                      )
               AND    fnd_date.canonical_to_date({requestParam.effDate}) BETWEEN pet.effective_start_date 
                                                                         AND     pet.effective_end_date
              ) rslt
      ]'
    );

  xxas_com_sr_pkg.set_rest_method
    (p_service_name       => l_SERVICE
    ,p_method_name        => 'TimecardEntryFormORApprovers'
    ,p_description        => 'Get Timecard Entry Form Override Approvers'
    ,p_methods            => 'GET'
    ,p_remove_root_ele    => true
    ,p_path               => 'form/override-approvers'
    ,p_sql                =>
      q'[
        SELECT XMLElement
                 ("data"
                 ,XMLAttributes
                   ('true' as "json:force-array"
                   ,'http://json.org/' as "xmlns:json")
                 ,XMLAgg
                   (XMLElement
                     ("approver"
                     ,XMLForest
                       (p.name         "id"
                       ,p.display_name "value"
                       ,p.display_name "label")
                     ) ORDER BY p.display_name
                   )
                 ) "data"
        FROM  (SELECT rslt.name
                     ,rslt.display_name
               FROM   TABLE(xxas_wf_role_util_pkg.roles_by_display_name_tbl({requestParam.q}, trunc(sysdate))) rslt
               WHERE  rslt.name NOT IN (SELECT column_value role_name
                                     FROM   TABLE(xxas_wf_role_util_pkg.person_roles_tbl
                                                    (fnd_global.employee_id, trunc(sysdate))))
               AND    {requestParam.id} is null
               AND    {authFunctionPredicate.XXAS_SRV_Q_WFR_BY_NAME_PERM}
               UNION
               SELECT rslt.name
                     ,rslt.display_name
               FROM   TABLE(xxas_wf_role_util_pkg.roles_by_display_name_tbl
                              (xxas_cal_util_pkg.display_name_from_username
                                 ({requestParam.id}), trunc(sysdate))) rslt
               WHERE  {requestParam.id} is not null
               AND    rslt.name NOT IN (SELECT column_value role_name
                                        FROM   TABLE(xxas_wf_role_util_pkg.person_roles_tbl
                                                       (fnd_global.employee_id, trunc(sysdate))))
               AND    {authFunctionPredicate.XXAS_SRV_Q_WFR_BY_NAME_PERM}
              ) p
      ]'
    );

  xxas_com_sr_pkg.set_rest_method
    (p_service_name       => l_SERVICE
    ,p_method_name        => 'TimecardPeriods'
    ,p_description        => 'Get Timecard Periods'
    ,p_methods            => 'GET'
    ,p_remove_root_ele    => true
    ,p_path               => 'form/periods'
    ,p_sql                =>
      q'[
        SELECT XMLElement
                 ("data"
                 ,XMLAttributes
                   ('true' as "json:force-array"
                   ,'http://json.org/' as "xmlns:json")
                 ,XMLAgg
                   (XMLElement
                     ("paymenttype"
                     ,XMLForest
                       (rslt.period_id      "id"
                       ,rslt.period_name    "value"
                       ,rslt.period_name    "label")
                     ) ORDER BY rslt.period_id DESC
                   )
                 ) "data"
        FROM  (SELECT xtp.period_id
                     ,xtp.period_name
               FROM   xxas_tcm_periods xtp
               WHERE  {requestParam.id} is null
               AND    fnd_profile.value('XXAS_TIMESTORE') = 'TCM'
               UNION
               SELECT xtp.period_id
                     ,xtp.period_name
               FROM   xxas_tcm_periods xtp
               WHERE  {requestParam.id} is not null
               AND    xtp.period_id  = {requestParam.id}
               AND    fnd_date.canonical_to_date({requestParam.effDate}) BETWEEN xtp.start_date 
                                                                         AND     xtp.end_date - 1
               AND    fnd_profile.value('XXAS_TIMESTORE') = 'TCM'
              ) rslt
      ]'
    );

  xxas_com_sr_pkg.set_rest_method
    (p_service_name       => l_SERVICE
    ,p_method_name        => 'CopyTime'
    ,p_description        => 'Creates Copy Time page'
    ,p_methods            => 'POST'
    ,p_path               => 'copy-time'
    ,p_sql                =>
      q'[
        DECLARE
          l_resp xxas_http_resp_obj;
        BEGIN
          l_resp := xxas_http_response
                      .ok(xxas_com_rest_util_pkg.app_route_redirect_response
                            ('time-new'
                             || '/'
                             || {requestParam.timeNewAsgId}
                             || '_'
                             || {requestParam.timeNewStartTime}
                             || '_'
                             || {requestParam.timeNewEndTime}
                             || '_'
                             || {requestParam.timeNewPaymentId}
                             || '_'
                             || replace({requestParam.sourceBlock},'_','$')
                             || '_'
                             || {requestParam.savedSearchId}
                             || '?modal=/'
                             || 'time-copy/'
                             || '_'
                             || '_'
                             || '_'
                             || replace({requestParam.sourceBlock},'_','$')
                             || '_'
                             || {requestParam.savedSearchId}
                            )
                         )
                      .build();

         {response} := l_resp;

        END
      ]'
    );

  xxas_com_sr_pkg.set_rest_method
    (p_service_name       => l_SERVICE
    ,p_method_name        => 'CopyTimeForm'
    ,p_description        => 'Get Copy Time Form'
    ,p_methods            => 'GET'
    ,p_remove_root_ele    => true
    ,p_path               => 'copy-time/form'
    ,p_sql                =>
      q'[
          SELECT xxas_timecard_entry_pkg.copy_time_form
                   ({requestParam.periodId}
                   ,{requestParam.personId}
                   ,{requestParam.assignmentId}
                   ,{requestParam.mgrPersonId}
                   ,{requestParam.sourceBlock}
                   ,{requestParam.savedSearchId}) "data"
          FROM   dual
      ]'
    );

  xxas_com_sr_pkg.set_rest_method
    (p_service_name       => l_SERVICE
    ,p_method_name        => 'CopyTimeFromPeriods'
    ,p_description        => 'Get Copy Time From Periods'
    ,p_methods            => 'GET'
    ,p_remove_root_ele    => true
    ,p_path               => 'copy-time/from-periods'
    ,p_sql                =>
      q'[
        SELECT XMLElement
                 ("data"
                 ,XMLAttributes
                   ('true' as "json:force-array"
                   ,'http://json.org/' as "xmlns:json")
                 ,XMLAgg
                   (XMLElement
                     ("period"
                     ,XMLForest
                       (p.period_id   "id"
                       ,p.period_name "value"
                       ,p.period_name "label")
                     ) ORDER BY p.period_id DESC
                   )
                 ) "data"
         FROM  (SELECT distinct xtpa.period_id
                               ,xtpa.period_name
                FROM   xxas_tcm_log_entries          xtle
                      ,xxas_tcm_period_assignments_v xtpa
                WHERE  ({requestParam.id} is null OR 
                        ({requestParam.id} is not null AND
                         {requestParam.id} = xtpa.period_id))
                AND    ({requestParam.toPeriodId} = -1 OR 
                        ({requestParam.toPeriodId} &lt;&gt; xtpa.period_id))
                AND    ({requestParam.fromPersonId} is not null AND 
                        xtpa.person_id = {requestParam.fromPersonId})
                AND    xtle.assignment_id   = xtpa.assignment_id
                AND    xtle.start_date_time &lt; nvl(xtpa.early_finish, xtpa.end_date)
                AND    xtle.end_date_time   &gt; nvl(xtpa.late_start, xtpa.start_date)
                AND    ({requestParam.calledView} is not null AND 
                        {requestParam.calledView} IN ('EMP','TEAM_DR','TEAM_SS'))
                AND    xxas_com_sec_pkg.test_instance(decode({requestParam.calledView},'EMP','XXAS_SRV_Q_TCM_PER_TC_PERM','TEAM_DR','XXAS_SRV_Q_TCM_TEAM_TC_PERM','TEAM_SS','XXAS_SRV_Q_TCM_TEAM_TC_PERM')
                                                     ,xtpa.period_id
                                                     ,xtpa.assignment_id) = 'Y'
               ) p
      ]'
    );

  xxas_com_sr_pkg.set_rest_method
    (p_service_name       => l_SERVICE
    ,p_method_name        => 'CopyTimeToPeriods'
    ,p_description        => 'Get Copy Time To Periods'
    ,p_methods            => 'GET'
    ,p_remove_root_ele    => true
    ,p_path               => 'copy-time/to-periods'
    ,p_sql                =>
      q'[
        SELECT XMLElement
                 ("data"
                 ,XMLAttributes
                   ('true' as "json:force-array"
                   ,'http://json.org/' as "xmlns:json")
                 ,XMLAgg
                   (XMLElement
                     ("period"
                     ,XMLForest
                       (p.period_id   "id"
                       ,p.period_name "value"
                       ,p.period_name "label")
                     ) ORDER BY p.period_id DESC
                   )
                 ) "data"
         FROM  (SELECT distinct xtpa.period_id
                               ,xtpa.period_name
                FROM   xxas_tcm_period_assignments_v xtpa
                WHERE  ({requestParam.id} is null OR 
                        ({requestParam.id} is not null AND
                         {requestParam.id} = xtpa.period_id))
                AND    ({requestParam.fromPeriodId} = -1 OR 
                        ({requestParam.fromPeriodId} &lt;&gt; xtpa.period_id))
                AND    ({requestParam.toPersonId} is not null AND 
                        xtpa.person_id = {requestParam.toPersonId})
                AND    ({requestParam.calledView} is not null AND 
                        {requestParam.calledView} IN ('EMP','TEAM_DR','TEAM_SS'))
                AND    xxas_com_sec_pkg.test_instance(decode({requestParam.calledView},'EMP','XXAS_SRV_Q_TCM_PER_TC_PERM','TEAM_DR','XXAS_SRV_Q_TCM_TEAM_TC_PERM','TEAM_SS','XXAS_SRV_Q_TCM_TEAM_TC_PERM')
                                                     ,xtpa.period_id
                                                     ,xtpa.assignment_id) = 'Y'
               ) p
      ]'
    );

END;
/
COMMIT;
EXIT;
