/* $Id$ */
/*============================================================================*
 |                 Copyright (c) 2015 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_sql varchar2(32000);

BEGIN

  --
  -- Register QuerySavedSearchTimecards service
  --
  l_sql :=
'        SELECT p.period_id
               ,xxas_util_pkg.get_formatted_name
                 (p.full_name
                 ,p.global_name
                 ,p.local_name
                 ,p.first_name
                 ,p.last_name) person_name
              ,xxas_com_asg_pkg.get_job_title(p.assignment_id, :pEffectiveDate) job_title
              ,p.start_date
              ,p.end_date
              ,p.assignment_id
              ,p.person_id
              ,p.late_start
              ,p.early_finish
              ,p.period_name
              ,p.all_total
              ,p.draft_total
              ,p.pending_total
              ,p.returned_total
              ,p.transferred_total
              ,p.approved_total
              ,p.errored_total
              ,decode(p.alert_total, 0, null, ''amber'') badge_color
              ,p.alert_total badge_value
        FROM   (SELECT /*+ cardinality(drs 15)*/
                       tpa.period_id
                      ,papf.full_name
                      ,papf.global_name
                      ,papf.local_name
                      ,papf.first_name
                      ,papf.last_name
                      ,tpa.start_date
                      ,tpa.end_date
                      ,tpa.assignment_id
                      ,tpa.person_id
                      ,tpa.late_start
                      ,tpa.early_finish
                      ,tpa.period_name
                      ,sum(decode(nvl(e.current_state_code, ''XX''), ''XX'', 0, 1)) all_total 
                      ,sum(decode(nvl(e.current_state_code, ''XX'')
                                                      , ''P'', decode
                                                              (xxas_wf_role_util_pkg.has_access_to_role
                                                                (''XXAS_TCM_APPROVAL_PERM'', e.current_approver_role)
                                                              ,''Y'', 1, 0)
                                                      , 0)) alert_total 
                      ,sum(decode(nvl(e.current_state_code, ''XX''), ''D'', 1, 0)) draft_total 
                      ,sum(decode(nvl(e.current_state_code, ''XX''), ''P'', 1, 0)) pending_total 
                      ,sum(decode(nvl(e.current_state_code, ''XX''), ''R'', 1, 0)) returned_total 
                      ,sum(decode(nvl(e.current_state_code, ''XX''), ''T'', 1, 0)) transferred_total 
                      ,sum(decode(nvl(e.current_state_code, ''XX''), ''A'', 1, 0)) approved_total 
                      ,sum(decode(nvl(e.current_state_code, ''XX''), ''E'', 1, 0)) errored_total
                FROM   xxas_tcm_period_assignments_v tpa
                      ,per_all_people_f papf
                      ,xxas_tcm_log_entry_approvers_v e
                WHERE  tpa.person_id = papf.person_id
                AND    :pEffectiveDate BETWEEN
                       papf.effective_start_date AND papf.effective_end_date
                AND    tpa.period_id = e.period_id(+)
                AND    tpa.assignment_id = e.assignment_id(+)
                AND    tpa.assignment_id IN
                         (SELECT s.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 = :pSavedSearchId
                                          AND    xxas_com_sec_pkg.test_instance
                                                  (''XXAS_DATA_SAVED_SEARCH_VIEW'', :pSavedSearchId) = ''Y'')
                                        )) s
                          )
                GROUP BY tpa.period_id
                        ,papf.full_name
                        ,papf.global_name
                        ,papf.local_name
                        ,papf.first_name
                        ,papf.last_name
                        ,tpa.start_date
                        ,tpa.end_date
                        ,tpa.assignment_id
                        ,tpa.person_id
                        ,tpa.late_start
                        ,tpa.early_finish
                        ,tpa.period_name
                ORDER BY tpa.end_date DESC) p ';

  xxas_com_sr_pkg.seed_query_service
    (p_service_name      => 'QuerySavedSearchTimecards'
    ,p_securing_function => 'XXAS_SRV_Q_TCM_SS_TC_PERM'
    ,p_sql               => l_sql
    ,p_parameters        => xxas_com_sr_param_varray
                              (xxas_com_sr_param_type('pSavedSearchId' , xxas_com_sr_pkg.g_NUMBER)
                              ,xxas_com_sr_param_type('pEffectiveDate' , xxas_com_sr_pkg.g_DATE)));

END;
/
COMMIT;
EXIT;
