/* $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
                       := 'Timecardactions';
                       
BEGIN
      
  xxas_com_sr_pkg.set_rest_service
    (p_service_name       => l_SERVICE
    ,p_description        => 'View Timecard actions'
    ,p_consumes           => 'application/xml'
    ,p_produces           => 'application/xml'
    );
    
    
  xxas_com_sr_pkg.set_rest_method
    (p_service_name       => l_SERVICE
    ,p_method_name        => 'GetTimeCardactions'
    ,p_description        => 'Gets time card actions'
    ,p_methods            => 'GET'
    ,p_remove_root_ele    => true
    ,p_path               => 'timecardactions/{personId}'
    ,p_sql                =>
      q'[ 
        SELECT XMLElement
                ("data" 
                ,XMLAttributes
                  ('true' as "json:force-array"
                  ,'http://json.org/' as "xmlns:json")
                ,XMLAgg
                  (XMLElement
                    ("item"
                    ,XMLForest
                      ('flexible'   "type"
                      ,CASE WHEN period_name is not null 
                            THEN period_name
                            ELSE to_char(start_date,'Mon FMDD') || ' - ' || to_char(end_date,'Mon FMDD YYYY')
                       END    "id"
                      ,start_date   "startdate"
                      ,end_date     "enddate"
                      ,sum(entries) "timecards"
                      ,CASE WHEN period_name is not null 
                            THEN null
                            ELSE xxas_otl_periods_pkg.team_periods_action_status
                                   (count(person_id)
                                   ,sum(pending)
                                   ,sum(draft)
                                   ,sum(approved)
                                   ,sum(returned)
                                   ,sum(transfered)
                                   ,sum(removed)
                                   ,sum(errored))
                       END "line2"
                      ,CASE WHEN period_name is not null 
                            THEN period_name
                            ELSE xxas_com_msg_pkg.get_decoded_string
                                   (xxas_com_msg_type
                                      (xxas_util_pkg.app
                                      ,'XXAS_OTL_PERIOD'
                                      ,xxas_com_msg_token_varray
                                         (xxas_com_msg_token_type
                                            ('START_TIME', to_char(start_date
											                      ,nvl(xxas_cal_event_pkg.get_display_date_format
										                           ({pathParam.personId}),'Mon FMDD')))
                                         ,xxas_com_msg_token_type
                                            ('STOP_TIME', to_char(end_date
											                     ,nvl(xxas_cal_event_pkg.get_display_date_format
										                            ({pathParam.personId}),'Mon FMDD YYYY'))))))
                       END "line1"
                      ,CASE WHEN period_name is not null 
                            THEN xxas_tcm_periods_pkg.get_action_status
                                   (count(person_id)
                                   ,sum(pending)
                                   ,sum(draft)
                                   ,sum(approved)
                                   ,sum(returned)
                                   ,sum(transfered)
                                   ,sum(removed))
                            ELSE xxas_otl_periods_pkg.team_periods_tmc_abs_duration
                                   (sum(recorded_hours)
                                   ,sum(absence_days)
                                   ,sum(absence_hours))
                       END "line3"
                      ,CASE WHEN period_name is not null 
                            THEN xxas_tcm_periods_pkg.tcm_show_calendar_period
                                   ({pathParam.personId}
                                   ,start_date).get_xml()
                            ELSE xxas_otl_periods_pkg.tc_show_in_calendar_actions
                                   ({pathParam.personId}
                                   ,start_date).get_xml()
                       END "linked"
                      )
                    ) order by start_date desc 
                  ) 
                ) "data"
        FROM   (SELECT rslt.period_id
                      ,rslt.start_date
                      ,rslt.end_date
                      ,rslt.period_name
                      ,rslt.person_id
                      ,rslt.assignment_id
                      ,0 entries
                      ,SUM(DECODE(NVL(e.current_state_code, 'XX'), 'P', 1, 0)) pending
                      ,SUM(DECODE(NVL(e.current_state_code, 'XX'), 'D', 1, 0)) draft
                      ,SUM(DECODE(NVL(e.current_state_code, 'XX'), 'A', 1, 0)) approved
                      ,SUM(DECODE(NVL(e.current_state_code, 'XX'), 'R', 1, 0)) returned
                      ,SUM(DECODE(NVL(e.current_state_code, 'XX'), 'T', 1, 0)) transfered
                      ,SUM(DECODE(NVL(e.current_state_code, 'XX'), 'X', 1, 0)) removed
                      ,null                                                    errored
                      ,null                                                    recorded_hours
                      ,null                                                    absence_days
                      ,null                                                    absence_hours
                FROM   xxas_tcm_period_assignments_v rslt 
                      ,xxas_tcm_log_entry_approvers_v e
                WHERE  {requestParam.savedSearchId} is null 
                AND    rslt.period_id     = e.period_id
                AND    rslt.assignment_id = e.assignment_id(+)
                AND    (({requestParam.requires_action} = 'true' AND 
                         e.current_state_code = 'P')
                        OR ({requestParam.requires_action} = 'false' AND 
                            e.current_state_code NOT IN ('X'))
                       )
                AND    e.person_id IN (SELECT column_value 
                                       FROM TABLE(xxas_com_hier_pkg.drs_for_per_tbl({pathParam.personId},sysdate))
                                      )
                AND    {authFunctionPredicate.XXAS_SRV_Q_TCM_TEAM_TC_PERM}
                AND    fnd_profile.value('XXAS_TIMESTORE') = 'TCM'
                GROUP BY rslt.period_id
                        ,rslt.start_date
                        ,rslt.end_date
                        ,rslt.period_name
                        ,rslt.assignment_id
                        ,rslt.person_id
                UNION
                SELECT rslt.period_id
                      ,rslt.start_date
                      ,rslt.end_date
                      ,rslt.period_name
                      ,rslt.person_id  
                      ,rslt.assignment_id
                      ,count(*) entries
                      ,SUM(DECODE(NVL('XX', 'XX'), 'P', 1, 0)) pending
                      ,SUM(DECODE(NVL('XX', 'XX'), 'D', 1, 0)) draft
                      ,SUM(DECODE(NVL('XX', 'XX'), 'A', 1, 0)) approved
                      ,SUM(DECODE(NVL('XX', 'XX'), 'R', 1, 0)) returned
                      ,SUM(DECODE(NVL('XX', 'XX'), 'T', 1, 0)) transfered
                      ,SUM(DECODE(NVL('XX', 'XX'), 'X', 1, 0)) removed
                      ,null                                    errored
                      ,null                                    recorded_hours
                      ,null                                    absence_days
                      ,null                                    absence_hours
                FROM   xxas_tcm_period_assignments_v rslt
                      ,(SELECT drs.column_value pid
                        FROM   TABLE(xxas_com_hier_pkg.drs_for_per_tbl
                                       ({pathParam.personId}, trunc(sysdate))) drs
                       ) teamdrs
                WHERE  rslt.person_id = teamdrs.pid
                AND    {requestParam.requires_action} = 'false' 
                AND    {authFunctionPredicate.XXAS_SRV_Q_TCM_TEAM_TC_PERM}
                AND    fnd_profile.value('XXAS_TIMESTORE') = 'TCM'
                GROUP BY rslt.period_id
                        ,rslt.start_date
                        ,rslt.end_date
                        ,rslt.period_name
                        ,rslt.assignment_id
                        ,rslt.person_id
                UNION  
                SELECT rslt.period_id
                      ,rslt.start_date
                      ,rslt.end_date
                      ,rslt.period_name
                      ,rslt.person_id
                      ,rslt.assignment_id
                      ,0 entries
                      ,SUM(DECODE(NVL(e.current_state_code, 'XX'), 'P', 1, 0)) pending
                      ,SUM(DECODE(NVL(e.current_state_code, 'XX'), 'D', 1, 0)) draft
                      ,SUM(DECODE(NVL(e.current_state_code, 'XX'), 'A', 1, 0)) approved
                      ,SUM(DECODE(NVL(e.current_state_code, 'XX'), 'R', 1, 0)) returned
                      ,SUM(DECODE(NVL(e.current_state_code, 'XX'), 'T', 1, 0)) transfered
                      ,SUM(DECODE(NVL(e.current_state_code, 'XX'), 'X', 1, 0)) removed
                      ,null                                                    errored
                      ,null                                                    recorded_hours
                      ,null                                                    absence_days
                      ,null                                                    absence_hours
                FROM   xxas_tcm_period_assignments_v rslt 
                      ,xxas_tcm_log_entry_approvers_v e
                WHERE  {requestParam.savedSearchId} is not null
                AND    rslt.period_id          = e.period_id
                AND    rslt.assignment_id      = e.assignment_id(+)
                AND    (({requestParam.requires_action} = 'true' AND
                         (sysdate BETWEEN rslt.start_date 
                                  AND     rslt.end_date
                          OR e.current_state_code = 'P'))
                        OR ({requestParam.requires_action} = 'false' AND
                            e.current_state_code NOT IN ('X'))
                       )
                AND    e.assignment_id IN (SELECT /*+ cardinality(rslts 10)*/
                                                  rslts.assignment_id
                                           FROM   TABLE(xxas_per_search_pkg.people_tbl
                                                          (null
                                                          ,(SELECT xxas_saved_search_pkg.xml_to_obj
                                                                     (ss.search_parameters)
                                                            FROM   xxas_saved_searches_v ss
                                                            WHERE  ss.saved_search_id = {requestParam.savedSearchId}
                                                            AND    xxas_com_sec_pkg.test_instance
                                                                     ('XXAS_DATA_SAVED_SEARCH_VIEW', {requestParam.savedSearchId}) = 'Y'
                                                           )
                                                          )
                                                       ) rslts
                                          )
                AND    {authFunctionPredicate.XXAS_SRV_Q_TCM_TEAM_TC_PERM}
                AND    fnd_profile.value('XXAS_TIMESTORE') = 'TCM'
                GROUP BY rslt.period_id
                        ,rslt.start_date
                        ,rslt.end_date
                        ,rslt.period_name
                        ,rslt.assignment_id
                        ,rslt.person_id
                UNION
                SELECT hts.timecard_id period_id
                      ,trunc(hts.start_time)  start_date
                      ,trunc(hts.stop_time)   end_date
                      ,null            period_name
                      ,hts.resource_id person_id
                      ,null            assignment_id
                      ,count(*)        entries
                      ,SUM(DECODE(NVL(hts.approval_status, 'XX'), 'SUBMITTED', 1, 0))  pending
                      ,SUM(DECODE(NVL(hts.approval_status, 'XX'), 'WORKING', 1, 0))    draft
                      ,SUM(DECODE(NVL(hts.approval_status, 'XX'), 'APPROVED', 1, 0))   approved
                      ,SUM(DECODE(NVL(hts.approval_status, 'XX'), 'REJECTED', 1, 0))   returned
                      ,SUM(DECODE(hts.transferred_to,null,0,1))                        transfered
                      ,SUM(DECODE(NVL(hts.approval_status, 'XX'), 'RDBDELETED', 1, 0)) removed
                      ,SUM(DECODE(NVL(hts.approval_status, 'XX'), 'ERROR', 1, 0))      errored
                      ,SUM(NVL(hts.recorded_hours,0))                                  recorded_hours
                      ,SUM(NVL(hts.absence_days,0))                                    absence_days
                      ,SUM(NVL(hts.absence_hours,0))                                   absence_hours
                FROM   hxc_timecard_summary hts
                      ,fnd_lookup_values    flv
                      ,hxc_data_sets        hds
                      ,per_all_people_f     rslt
                WHERE  {requestParam.savedSearchId} is null 
                AND    hts.approval_status     = flv.lookup_code
                AND    flv.language            = userenv('LANG')
                AND    flv.view_application_id = 3
                AND    flv.lookup_type         = 'HXC_APPROVAL_STATUS'
                AND    flv.security_group_id   = 0
                AND    hts.data_set_id         = hds.data_set_id (+)
                AND    hts.resource_id         IN (SELECT /*+ cardinality(reportees 10)*/ reportees.column_value 
                                                   FROM   TABLE(xxas_com_hier_pkg.drs_for_per_tbl({pathParam.personId},sysdate)) reportees
                                                   WHERE  rownum >= 0
                                                  )
                AND    (({requestParam.requires_action} = 'false')
                        OR (({requestParam.requires_action} = 'true')
                        AND hts.timecard_id IN (SELECT  hts1.timecard_id 
                                                FROM    hxc_timecard_summary hts1
                                                WHERE   hts1.approval_status IN ('SUBMITTED')
                                                AND     hts1.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_TEAM_TMC_PERIODS_PERM}
                AND    fnd_profile.value('XXAS_TIMESTORE') = 'OTL'
                GROUP BY hts.timecard_id
                        ,trunc(hts.start_time)
                        ,trunc(hts.stop_time)
                        ,hts.resource_id
                UNION
                SELECT hts.timecard_id period_id
                      ,trunc(hts.start_time)  start_date
                      ,trunc(hts.stop_time)   end_date
                      ,null            period_name
                      ,hts.resource_id person_id
                      ,null            assignment_id
                      ,count(*)        entries
                      ,SUM(DECODE(NVL(hts.approval_status, 'XX'), 'SUBMITTED', 1, 0))  pending
                      ,SUM(DECODE(NVL(hts.approval_status, 'XX'), 'WORKING', 1, 0))    draft
                      ,SUM(DECODE(NVL(hts.approval_status, 'XX'), 'APPROVED', 1, 0))   approved
                      ,SUM(DECODE(NVL(hts.approval_status, 'XX'), 'REJECTED', 1, 0))   returned
                      ,SUM(DECODE(hts.transferred_to,null,0,1))                        transfered
                      ,SUM(DECODE(NVL(hts.approval_status, 'XX'), 'RDBDELETED', 1, 0)) removed
                      ,SUM(DECODE(NVL(hts.approval_status, 'XX'), 'ERROR', 1, 0))      errored
                      ,SUM(NVL(hts.recorded_hours,0))                                  recorded_hours
                      ,SUM(NVL(hts.absence_days,0))                                    absence_days
                      ,SUM(NVL(hts.absence_hours,0))                                   absence_hours
                FROM   hxc_timecard_summary hts
                      ,fnd_lookup_values    flv
                      ,hxc_data_sets        hds
                      ,per_all_people_f     rslt
                WHERE  {requestParam.savedSearchId} is not null 
                AND    hts.approval_status     = flv.lookup_code
                AND    flv.language            = userenv('LANG')
                AND    flv.view_application_id = 3
                AND    flv.lookup_type         = 'HXC_APPROVAL_STATUS'
                AND    flv.security_group_id   = 0
                AND    hts.data_set_id         = hds.data_set_id (+)
                AND    hts.resource_id         IN (SELECT /*+ cardinality(rslts 10)*/
                                                          distinct rslts.person_id
                                                   FROM   TABLE(xxas_per_search_pkg.people_tbl
                                                                  (null
                                                                  ,(SELECT xxas_saved_search_pkg.xml_to_obj
                                                                             (ss.search_parameters)
                                                                    FROM   xxas_saved_searches_v ss
                                                                    WHERE  ss.saved_search_id = {requestParam.savedSearchId}
                                                                    AND    xxas_com_sec_pkg.test_instance
                                                                             ('XXAS_DATA_SAVED_SEARCH_VIEW', {requestParam.savedSearchId}) = 'Y'
                                                                   )
                                                                  )
                                                               ) rslts
                                                   WHERE  xxas_com_sec_pkg.test_instance
                                                               ('XXAS_DT_SAVE_SRCH_OTL_TIME_PERIOD'
                                                               ,rslts.person_id
                                                               ,null
                                                               ,null
                                                               ,null
                                                               ,null) = 'Y'
                                                  )
                AND    (({requestParam.requires_action} = 'false')
                        OR (({requestParam.requires_action} = 'true')
                        AND hts.timecard_id IN (SELECT  hts1.timecard_id 
                                                FROM    hxc_timecard_summary hts1
                                                WHERE   hts1.approval_status IN ('SUBMITTED')
                                                AND     hts1.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    fnd_profile.value('XXAS_TIMESTORE') = 'OTL'
                GROUP BY hts.timecard_id
                        ,trunc(hts.start_time)
                        ,trunc(hts.stop_time)
                        ,hts.resource_id
               )
        GROUP BY start_date
                ,end_date
                ,period_name
        ]'
    );
    
  
    
END;
/
COMMIT;
EXIT;