/* $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 QueryTimecardLogEntries service
  --
  l_sql :=
'        SELECT e.log_entry_id
              ,e.person_id
              ,e.assignment_id
              ,e.period_id
              ,e.start_date_time
              ,e.end_date_time
              ,to_char(trunc(e.start_date_time), ''FMDy DD'') friendly_start_date
              ,to_char(trunc(e.start_date_time), ''FMDay ddth Month YYYY'') friendly_start_date_tip
              ,to_char(trunc(e.end_date_time), ''FMDy DD'') friendly_end_date
              ,to_char(trunc(e.end_date_time), ''FMDay ddth Month YYYY'') friendly_end_date_tip
              ,trunc(e.start_date_time) start_date
              ,trunc(e.end_date_time) end_date
              ,to_char(e.start_date_time, ''HH24:MI:SS'') start_time
              ,to_char(fnd_date.canonical_to_date(prev.start_date_time_prev), ''HH24:MI:SS'') start_time_old
              ,to_char(e.end_date_time, ''HH24:MI:SS'') end_time
              ,to_char(fnd_date.canonical_to_date(prev.end_date_time_prev), ''HH24:MI:SS'') end_time_old
              ,e.current_state_code
              ,e.current_state
              ,e.current_ins_upd_del
              ,e.current_approver_role
              ,wfr.display_name current_approver_name
              ,wfr.description current_approver_description
              ,decode(e.current_approver_role, null, ''N''
                     ,xxas_wf_role_util_pkg.has_access_to_role
                       (''XXAS_TCM_APPROVAL_PERM'', e.current_approver_role)) pending_with_user
              ,e.internal_data_id
              ,e.duration_hours
              ,fnd_number.canonical_to_number(prev.duration_hours_prev) duration_hours_old
              ,e.element_type_id
              ,eit.eei_information1 enabled_flag
              ,nvl(eit.eei_information3, pettl.element_name) element_name
              ,eit.eei_information2 icon_name
              ,eit.eei_information15 summation_group
              ,eit.eei_information16 summation_field
              ,eit.eei_information17 rostered
              ,pet.input_currency_code
              ,cur.precision currency_precision
              ,cur.extended_precision currency_extended_precision
              ,nvl(cur.symbol, cur.currency_code) currency_symbol
              ,cur.minimum_accountable_unit currency_min_unit
              ,xxas_tcm_config_pkg.record_start_end_times_flag(pet.element_type_id) show_start_end
              ,xxas_tcm_config_pkg.record_duration_flag(pet.element_type_id) show_duration
              ,xxas_tcm_config_pkg.start_time_label(pet.element_type_id) start_time_label
              ,nvl(xxas_com_elements_pkg.get_iv_uom
                 (eit.eei_information4, trunc(e.start_date_time)), ''H_HHMM'') start_time_datatype
              ,xxas_tcm_config_pkg.end_time_label(pet.element_type_id) end_time_label
              ,nvl(xxas_com_elements_pkg.get_iv_uom
                 (eit.eei_information6, trunc(e.start_date_time)), ''H_HHMM'') end_time_datatype
              ,xxas_tcm_config_pkg.duration_label(pet.element_type_id) duration_label
              ,nvl(xxas_com_elements_pkg.get_iv_uom
                 (eit.eei_information8, trunc(e.start_date_time)), ''H_DECIMAL1'') duration_datatype
              ,decode(eit.eei_information10, null, ''N'', ''Y'') show_field1              
              ,xxas_com_elements_pkg.get_iv_name
                 (eit.eei_information10, trunc(e.start_date_time)) field1_label
              ,xxas_com_elements_pkg.get_iv_uom
                 (eit.eei_information10, trunc(e.start_date_time)) field1_datatype
              ,e.field1
              ,prev.field1_prev field1_old
              ,xxas_com_elements_pkg.get_lov_meaning
                (eit.eei_information10, trunc(e.start_date_time), e.field1) field1_lov_meaning
              ,xxas_com_elements_pkg.get_lov_meaning
                (eit.eei_information10, trunc(e.start_date_time), prev.field1_prev) field1_lov_meaning_old
              ,decode(eit.eei_information11, null, ''N'', ''Y'') show_field2
              ,xxas_com_elements_pkg.get_iv_name
                 (eit.eei_information11, trunc(e.start_date_time)) field2_label
              ,xxas_com_elements_pkg.get_iv_uom
                 (eit.eei_information11, trunc(e.start_date_time)) field2_datatype
              ,e.field2
              ,prev.field2_prev field2_old
              ,xxas_com_elements_pkg.get_lov_meaning
                (eit.eei_information11, trunc(e.start_date_time), e.field2) field2_lov_meaning
              ,xxas_com_elements_pkg.get_lov_meaning
                (eit.eei_information11, trunc(e.start_date_time), prev.field2_prev) field2_lov_meaning_old
                ,decode(eit.eei_information12, null, ''N'', ''Y'') show_field3
              ,xxas_com_elements_pkg.get_iv_name
                 (eit.eei_information12, trunc(e.start_date_time)) field3_label
              ,xxas_com_elements_pkg.get_iv_uom
                 (eit.eei_information12, trunc(e.start_date_time)) field3_datatype
              ,e.field3
              ,prev.field3_prev field3_old
              ,xxas_com_elements_pkg.get_lov_meaning
                (eit.eei_information12, trunc(e.start_date_time), e.field3) field3_lov_meaning
              ,xxas_com_elements_pkg.get_lov_meaning
                (eit.eei_information12, trunc(e.start_date_time), prev.field3_prev) field3_lov_meaning_old
              ,decode(eit.eei_information13, null, ''N'', ''Y'') show_field4
              ,xxas_com_elements_pkg.get_iv_name
                 (eit.eei_information13, trunc(e.start_date_time)) field4_label
              ,xxas_com_elements_pkg.get_iv_uom
                 (eit.eei_information13, trunc(e.start_date_time)) field4_datatype
              ,e.field4
              ,prev.field4_prev field4_old
              ,xxas_com_elements_pkg.get_lov_meaning
                (eit.eei_information13, trunc(e.start_date_time), e.field4) field4_lov_meaning
              ,xxas_com_elements_pkg.get_lov_meaning
                (eit.eei_information13, trunc(e.start_date_time), prev.field4_prev) field4_lov_meaning_old
                ,decode(eit.eei_information14, null, ''N'', ''Y'') show_field5
              ,xxas_com_elements_pkg.get_iv_name
                 (eit.eei_information14, trunc(e.start_date_time)) field5_label
              ,xxas_com_elements_pkg.get_iv_uom
                 (eit.eei_information14, trunc(e.start_date_time)) field5_datatype
              ,e.field5
              ,prev.field5_prev field5_old
              ,xxas_com_elements_pkg.get_lov_meaning
                (eit.eei_information14, trunc(e.start_date_time), e.field5) field5_lov_meaning
              ,xxas_com_elements_pkg.get_lov_meaning
                (eit.eei_information14, trunc(e.start_date_time), prev.field5_prev) field5_lov_meaning_old
              ,e.cost_allocation_keyflex_id
              ,decode(nvl(eit.eei_information19, ''Y'')
                     ,''N'', null
                     ,xxas_com_cost_kff_pkg.get_conatenated_segment_names
                       (nvl(e.cost_allocation_keyflex_id, prev.cost_allocation_keyflex_idprev))) costing_heading
              ,decode(nvl(eit.eei_information19, ''Y'')
                     ,''N'', null
                     ,xxas_com_cost_kff_pkg.get_conatenated_formatted
                       (e.cost_allocation_keyflex_id)) costing_formatted
              ,decode(nvl(eit.eei_information19, ''Y'')
                     ,''N'', null
                     ,xxas_com_cost_kff_pkg.get_conatenated_formatted
                       (prev.cost_allocation_keyflex_idprev)) costing_formatted_old
              ,ca.segment1
              ,ca.segment2
              ,ca.segment3
              ,ca.segment4
              ,ca.segment5
              ,ca.segment6
              ,ca.segment7
              ,ca.segment8
              ,ca.segment9
              ,ca.segment10
              ,ca.segment11
              ,ca.segment12
              ,ca.segment13
              ,ca.segment14
              ,ca.segment15
              ,ca.segment16
              ,ca.segment17
              ,ca.segment18
              ,ca.segment19
              ,ca.segment20
              ,ca.segment21
              ,ca.segment22
              ,ca.segment23
              ,ca.segment24
              ,ca.segment25
              ,ca.segment26
              ,ca.segment27
              ,ca.segment28
              ,ca.segment29
              ,ca.segment30
              ,e.override_approver_role
              ,wfr1.display_name override_approver_name
              ,wfr1.description override_approver_description
              ,wfr1.email_address override_approver_email
              ,wfr2.display_name override_approver_name_old
              ,e.comments
              ,extractvalue
                (e.log_entry_details
                ,''//InterimResponse[@userId="'' || fnd_global.user_id || ''"]/@response'') interim_response
              ,extractvalue
                (e.log_entry_details
                ,''//InterimResponse[@userId="'' || fnd_global.user_id || ''"]/text()'') interim_comments
							,decode(eit.eei_information20,''Y'',decode(e.person_id,fnd_global.employee_id,''Y'',''N''),''N'') entry_locked
        FROM   xxas_tcm_log_entry_approvers_v e
              ,pay_element_types_f_tl pettl
              ,pay_element_types_f pet
              ,pay_element_type_extra_info eit
              ,pay_cost_allocation_keyflex ca
              ,fnd_currencies_vl cur
              ,wf_roles wfr
              ,wf_roles wfr1
              ,wf_roles wfr2
              ,xxas_tcm_log_entries_prev_v prev
        WHERE  e.person_id = :pPersonId
        AND    e.period_id = :pPeriodId
        AND    e.current_state_code &lt;&gt; ''X''
        AND    e.log_entry_id = prev.log_entry_id(+)
        AND    e.element_type_id = pet.element_type_id
        AND    trunc(e.start_date_time) BETWEEN
               pet.effective_start_date AND pet.effective_end_date
        AND    pet.element_type_id = pettl.element_type_id
        AND    pettl.language = userenv(''LANG'')
        AND    pet.element_type_id = eit.element_type_id
        AND    eit.information_type = ''XXAS_TCM_ENTRY_MAP''
        AND    e.cost_allocation_keyflex_id = ca.cost_allocation_keyflex_id(+)
        AND    e.current_approver_role = wfr.name(+)
        AND    e.override_approver_role = wfr1.name(+)
        AND    prev.override_approver_role_prev = wfr2.name(+)
        AND    pet.input_currency_code = cur.currency_code(+)
        ORDER BY nvl(to_number(eit.eei_information18), 999)
                ,nvl(eit.eei_information3, pettl.element_name)
                ,e.start_date_time, e.log_entry_id ';

  xxas_com_sr_pkg.seed_query_service
    (p_service_name      => 'QueryTimecardLogEntries'
    ,p_securing_function => 'XXAS_SRV_Q_TCM_LE_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('pPeriodId' , xxas_com_sr_pkg.g_NUMBER)));

END;
/
COMMIT;
EXIT;
