/* $Id$ */
/*============================================================================*
 |                 Copyright (c) 2011 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 QueryPersonTimecards service
  --
  l_sql :=
'        SELECT p.period_id
              ,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, ''red'') badge_color
              ,p.alert_total badge_value
        FROM (SELECT tpa.period_id
                    ,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'')
                                                    , ''R'', 1
                                                    , ''E'', 1
                                                    , 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
                    ,xxas_tcm_log_entry_states_v e
                    ,per_all_assignments_f paaf
              WHERE  tpa.person_id = :pPersonId
              AND    tpa.period_id = e.period_id(+)
              AND    tpa.assignment_id = e.assignment_id(+)
              AND    tpa.assignment_id = paaf.assignment_id
              AND    paaf.assignment_type IN (''E'', ''C'')
              AND    nvl(tpa.late_start, tpa.start_date) BETWEEN
                     paaf.effective_start_date AND paaf.effective_end_date
              GROUP BY tpa.period_id
                       ,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      => 'QueryPersonTimecards'
    ,p_securing_function => 'XXAS_SRV_Q_TCM_PER_TC_PERM'
    ,p_sql               => l_sql
    ,p_parameters        => xxas_com_sr_param_varray
                              (xxas_com_sr_param_type('pPersonId', xxas_com_sr_pkg.g_VARCHAR2)));

END;
/
COMMIT;
EXIT;
