/* $Id$ */
/*============================================================================*
 |                 Copyright (c) 2016 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
                       := 'Timecardperiods';
                       
BEGIN
      
  xxas_com_sr_pkg.set_rest_service
    (p_service_name       => l_SERVICE
    ,p_description        => 'Get timecard periods'
    ,p_consumes           => 'application/xml'
    ,p_produces           => 'application/xml'
    );
      
    xxas_com_sr_pkg.set_rest_method
    (p_service_name       => l_SERVICE
    ,p_method_name        => 'GetTmcPeriods'
    ,p_description        => 'Get timecard period list'
    ,p_methods            => 'GET'
    ,p_path               => 'timecard-periods/{personId}'
    ,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
                    ("person"
                    ,XMLForest
                       ( CASE WHEN p.period_name is not null 
                            THEN p.period_id||'_'||p.person_id
                            ELSE to_char(p.period_id)
                            end  "id"   
                      ,CASE WHEN p.period_name is not null 
                            THEN xxas_timecard_periods_pkg.tmc_item_actions
                                   (xxas_timecard_periods_pkg.is_submit_required
                                      (p.person_id,p.period_id)
                                   ,p.copy_action
                                   ,p.person_id
                                   ,p.period_id
                                   ,xxas_cal_rslt_cache_pkg.tcm_periods_source_block()).get_xml() 
                            ELSE xxas_otl_periods_pkg.tmc_item_actions
                                   (xxas_otl_periods_pkg.is_submit_required
                                      (p.person_id,p.period_id)
                                   ,p.copy_action
                                   ,p.person_id
                                   ,p.period_id
                                   ,xxas_cal_rslt_cache_pkg.tcm_periods_source_block()).get_xml() 
                            END "linked"
                      ,'flexible' "type"
                      ,CASE WHEN p.period_name is not null 
                            THEN null
                            ELSE xxas_otl_periods_pkg.tmc_period_summary
                                   (p.person_id,p.period_id)
                       END "line2"
                      ,CASE WHEN p.period_name is not null 
                            THEN p.period_name
                            ELSE xxas_otl_periods_pkg.tmc_period_details
                                   (p.person_id,p.period_id)
                       END "line1"
                      ,CASE WHEN p.period_name is not null 
                            THEN xxas_timecard_periods_pkg.get_per_group_values
                                   (p.person_id,p.period_id)
                            ELSE xxas_otl_periods_pkg.abs_dur_tmc_status
                                   (p.person_id,p.period_id)
                       END "line3"
                      ,CASE WHEN p.period_name is not null 
                            THEN null
                            ELSE xxas_otl_periods_pkg.tmc_status_icon
                                   (p.person_id,p.period_id)
                       END "icon"
                      ,CASE WHEN p.period_name is not null 
                            THEN null
                            ELSE xxas_otl_periods_pkg.tmc_status_icon_color
                                   (p.person_id,p.period_id)
                       END "statusColor"
                      )
                    )
                  )
                ) "data"
        FROM   (SELECT count(*) copy_action
                      ,rslt.period_name
                      ,rslt.period_id
                      ,rslt.person_id
                FROM   xxas_tcm_period_assignments_v rslt
                WHERE  rslt.person_id = {pathParam.personId}
                AND    (({requestParam.requires_action} = 'false')
                        OR (({requestParam.requires_action} = 'true')
                            AND rslt.period_id IN (SELECT les.period_id 
                                                   FROM   xxas_tcm_log_entry_states_v les
                                                   WHERE  ((sysdate BETWEEN les.period_start_date 
                                                                    AND     les.period_end_date
                                                           OR les.current_state_code IN ('D','R'))
                                                           AND les.current_state_code &lt;&gt; 'X')
                                                   AND    les.assignment_id = rslt.assignment_id)
                           )
                       )
                AND    {authFunctionPredicate.XXAS_SRV_Q_TCM_PER_TC_PERM}
                AND    fnd_profile.value('XXAS_TIMESTORE') = 'TCM'
                GROUP BY rslt.period_id
                        ,rslt.period_name
                        ,rslt.person_id
                UNION
                SELECT count(*)        copy_action
                      ,null            period_name
                      ,hts.timecard_id period_id
                      ,hts.resource_id person_id
                FROM   hxc_timecard_summary hts
                      ,per_all_people_f     rslt
                WHERE  hts.resource_id         = {pathParam.personId}
                AND    (({requestParam.requires_action} = 'false')
                        OR (({requestParam.requires_action} = 'true')
                            AND hts.timecard_id IN (SELECT  htv.tcrd_time_building_block_id 
                                                    FROM    hxc_timecards_v htv
                                                    WHERE   ((sysdate BETWEEN htv.tcrd_start_time 
                                                                      AND     htv.tcrd_stop_time
                                                             OR  htv.tcrd_approval_status IN ('WORKING')))
                                                    AND     htv.tcrd_resource_id = hts.resource_id)
                           )
                       )
                AND    rslt.person_id = hts.resource_id
                AND    trunc(sysdate) BETWEEN rslt.effective_start_date 
                                      AND     rslt.effective_end_date
                AND    (nvl(rslt.current_employee_flag, 'N') = 'Y'
                        OR nvl(rslt.current_npw_flag, 'N') = 'Y')
                AND    {authFunctionPredicate.XXAS_OTL_TMC_PERIODS_PERM}
                AND    fnd_profile.value('XXAS_TIMESTORE') = 'OTL'
                GROUP BY hts.timecard_id
                        ,hts.resource_id
                ORDER BY period_id DESC
               ) p
        ]'
      );
      
  xxas_com_sr_pkg.set_rest_method
    (p_service_name       => l_SERVICE
    ,p_method_name        => 'SubmitDraftEntries'
    ,p_description        => 'Submit to one or more draft entries'
    ,p_path               => 'tmc-periods/submit'
    ,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};

          IF fnd_profile.value('XXAS_TIMESTORE') = 'TCM' THEN
            xxas_tcm_log_entry_pkg.submit_draft_entries
              (p_action       => 'submit'
              ,p_request_body => l_request_xml
              ,p_resp         => l_resp);
          ELSIF fnd_profile.value('XXAS_TIMESTORE') = 'OTL' THEN
            l_response_xml := xxas_otl_periods_pkg.submit_otl_timcard
                                (p_action       => 'submit'
                                ,p_request_body => l_request_xml);                     
            l_resp := xxas_http_response
                        .ok(l_response_xml
                           )
                        .build();
          END IF;

          {response} := l_resp;

        END
      ]'
    );


END;
/
COMMIT;
EXIT;

