/* $Id$ */
/*============================================================================*
 |                 Copyright (c) 2012 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 QueryTeamTimecards service
  --
  l_sql :=q'[
select * from 
(SELECT p.period_id
,xxas_util_pkg.get_formatted_name
(papf.full_name
,papf.global_name
,papf.local_name
,papf.first_name
,papf.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
,papf.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 
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('XX', 'XX'), 'XX', 0, 1)) all_total 
,sum(decode(nvl('XX', 'XX'), 'P', decode(xxas_wf_role_util_pkg.has_access_to_role('XXAS_TCM_APPROVAL_PERM', NULL) ,'Y', 1, 0)
, 0)) alert_total 
,sum(decode(nvl('XX', 'XX'), 'D', 1, 0)) draft_total 
,sum(decode(nvl('XX', 'XX'), 'P', 1, 0)) pending_total 
,sum(decode(nvl('XX', 'XX'), 'R', 1, 0)) returned_total 
,sum(decode(nvl('XX', 'XX'), 'T', 1, 0)) transferred_total 
,sum(decode(nvl('XX', 'XX'), 'A', 1, 0)) approved_total 
,sum(decode(nvl('XX', 'XX'), 'E', 1, 0)) errored_total
FROM xxas_tcm_period_assignments_v tpa,

                   (SELECT drs.column_value pid
                     FROM TABLE(xxas_com_hier_pkg.drs_for_per_tbl(:pPersonId, :pEffectiveDate))DRS)teamdrs

                         WHERE  tpa.person_id =  teamdrs.pid


              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
                           union

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')
, '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
,xxas_tcm_log_entry_approvers_v e

            WHERE  tpa.period_id = e.period_id
            AND    tpa.assignment_id = e.assignment_id
            AND   e.current_approver_role IS NOT NULL
            AND    e.current_approver_role IN
                       (SELECT  column_value
                       FROM    TABLE(xxas_wf_role_util_pkg.person_roles_tbl(:pPersonId, :pEffectiveDate)))

          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

                  ) p ,
                                per_all_people_f papf

    where  papf.person_id in (p.person_id)
and  :pEffectiveDate between papf.effective_start_date and papf.effective_end_date)
order by end_date desc
]';

  xxas_com_sr_pkg.seed_query_service
    (p_service_name      => 'QueryTeamTimecards'
    ,p_securing_function => 'XXAS_SRV_Q_TCM_TEAM_TC_PERM'
    ,p_sql               => l_sql
    ,p_parameters        => xxas_com_sr_param_varray
                              (xxas_com_sr_param_type('pPersonId'      , xxas_com_sr_pkg.g_NUMBER)
                              ,xxas_com_sr_param_type('pEffectiveDate' , xxas_com_sr_pkg.g_DATE)));

END;
/
COMMIT;
EXIT;
