/* $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 QueryTimecardElements service
  --
  l_sql :=
'        SELECT eit.element_type_extra_info_id
              ,:pAssignmentId assignment_id
              ,e.element_type_id
              ,e.element_link_id
              ,nvl(eit.eei_information3, e.element_name) element_name
              ,e.description
              ,e.business_group_id 
              ,pet.processing_type
              ,decode(nvl(eit.eei_information19, ''Y'')
                     ,''N'', null
                     ,e.costable_type) costable_type
              ,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
              ,eit.eei_information2 icon_name
              ,eit.eei_information15 summation_group
              ,eit.eei_information16 summation_field
              ,eit.eei_information17 rostered
              ,xxas_com_sec_pkg.test_instance(''XXAS_TCM_OVR_APPR_PERM'', :pAssignmentId) override_approver_allowed
              ,xxas_tcm_config_pkg.record_start_end_times_flag(e.element_type_id) record_start_end
              ,xxas_tcm_config_pkg.record_duration_flag(e.element_type_id) record_duration
              ,eit.eei_information4 start_time_iv_id
              ,xxas_tcm_config_pkg.start_time_label(e.element_type_id) start_time_label
              ,nvl(xxas_com_elements_pkg.get_iv_uom
                 (eit.eei_information4, :pEffectiveDate), ''H_HHMM'') start_time_datatype
              ,xxas_com_elements_pkg.get_iv_default_value
                 (eit.eei_information4, :pEffectiveDate, e.element_link_id) start_time_default
              ,xxas_com_elements_pkg.get_iv_min_value
                 (eit.eei_information4, :pEffectiveDate, e.element_link_id) start_time_min
              ,xxas_com_elements_pkg.get_iv_max_value
                 (eit.eei_information4, :pEffectiveDate, e.element_link_id) start_time_max
              ,eit.eei_information6 end_time_iv_id
              ,xxas_tcm_config_pkg.end_time_label(e.element_type_id) end_time_label
              ,nvl(xxas_com_elements_pkg.get_iv_uom
                 (eit.eei_information6, :pEffectiveDate), ''H_HHMM'') end_time_datatype
              ,xxas_com_elements_pkg.get_iv_default_value
                 (eit.eei_information6, :pEffectiveDate, e.element_link_id) end_time_default
              ,xxas_com_elements_pkg.get_iv_min_value
                 (eit.eei_information6, :pEffectiveDate, e.element_link_id) end_time_min
              ,xxas_com_elements_pkg.get_iv_max_value
                 (eit.eei_information6, :pEffectiveDate, e.element_link_id) end_time_max
              ,eit.eei_information8 duration_iv_id
              ,xxas_tcm_config_pkg.duration_label(e.element_type_id) duration_label
              ,nvl(xxas_com_elements_pkg.get_iv_uom
                 (eit.eei_information8, :pEffectiveDate), ''H_DECIMAL1'') duration_datatype
              ,xxas_com_elements_pkg.get_iv_default_value
                 (eit.eei_information8, :pEffectiveDate, e.element_link_id) duration_default
              ,xxas_com_elements_pkg.get_iv_min_value
                 (eit.eei_information8, :pEffectiveDate, e.element_link_id) duration_min
              ,xxas_com_elements_pkg.get_iv_max_value
                 (eit.eei_information8, :pEffectiveDate, e.element_link_id) duration_max
              ,eit.eei_information10 field1_iv_id            
              ,xxas_com_elements_pkg.get_iv_name
                 (eit.eei_information10, :pEffectiveDate) field1_label
              ,xxas_com_elements_pkg.get_iv_uom
                 (eit.eei_information10, :pEffectiveDate) field1_datatype
              ,xxas_com_elements_pkg.get_iv_mandatory_char
                 (eit.eei_information10, :pEffectiveDate) field1_required
              ,xxas_com_elements_pkg.get_iv_default_value
                 (eit.eei_information10, :pEffectiveDate, e.element_link_id) field1_default
              ,xxas_com_elements_pkg.get_iv_has_lov_char
                 (eit.eei_information10, :pEffectiveDate) field1_has_lov
              ,xxas_com_elements_pkg.get_iv_lookup_type
                 (eit.eei_information10, :pEffectiveDate) field1_lookup_type
              ,xxas_com_elements_pkg.get_iv_value_set_id
                 (eit.eei_information10, :pEffectiveDate) field1_value_set_id
              ,xxas_com_elements_pkg.get_iv_has_ff_validation_char
                 (eit.eei_information10, :pEffectiveDate) field1_has_ff_validation
              ,xxas_com_elements_pkg.get_iv_warn_or_err
                 (eit.eei_information10, :pEffectiveDate, e.element_link_id) field1_warning_or_error
              ,xxas_com_elements_pkg.get_iv_min_value
                 (eit.eei_information10, :pEffectiveDate, e.element_link_id) field1_min
              ,xxas_com_elements_pkg.get_iv_max_value
                 (eit.eei_information10, :pEffectiveDate, e.element_link_id) field1_max
              ,eit.eei_information11 field2_iv_id
              ,xxas_com_elements_pkg.get_iv_name
                 (eit.eei_information11, :pEffectiveDate) field2_label
              ,xxas_com_elements_pkg.get_iv_uom
                 (eit.eei_information11, :pEffectiveDate) field2_datatype
              ,xxas_com_elements_pkg.get_iv_mandatory_char
                 (eit.eei_information11, :pEffectiveDate) field2_required
              ,xxas_com_elements_pkg.get_iv_default_value
                 (eit.eei_information11, :pEffectiveDate, e.element_link_id) field2_default
              ,xxas_com_elements_pkg.get_iv_has_lov_char
                 (eit.eei_information11, :pEffectiveDate) field2_has_lov
              ,xxas_com_elements_pkg.get_iv_lookup_type
                 (eit.eei_information11, :pEffectiveDate) field2_lookup_type
              ,xxas_com_elements_pkg.get_iv_value_set_id
                 (eit.eei_information11, :pEffectiveDate) field2_value_set_id
              ,xxas_com_elements_pkg.get_iv_has_ff_validation_char
                 (eit.eei_information11, :pEffectiveDate) field2_has_ff_validation
              ,xxas_com_elements_pkg.get_iv_warn_or_err
                 (eit.eei_information11, :pEffectiveDate, e.element_link_id) field2_warning_or_error
              ,xxas_com_elements_pkg.get_iv_min_value
                 (eit.eei_information11, :pEffectiveDate, e.element_link_id) field2_min
              ,xxas_com_elements_pkg.get_iv_max_value
                 (eit.eei_information11, :pEffectiveDate, e.element_link_id) field2_max
              ,eit.eei_information12 field3_iv_id
              ,xxas_com_elements_pkg.get_iv_name
                 (eit.eei_information12, :pEffectiveDate) field3_label
              ,xxas_com_elements_pkg.get_iv_uom
                 (eit.eei_information12, :pEffectiveDate) field3_datatype
              ,xxas_com_elements_pkg.get_iv_mandatory_char
                 (eit.eei_information12, :pEffectiveDate) field3_required
              ,xxas_com_elements_pkg.get_iv_default_value
                 (eit.eei_information12, :pEffectiveDate, e.element_link_id) field3_default
              ,xxas_com_elements_pkg.get_iv_has_lov_char
                 (eit.eei_information12, :pEffectiveDate) field3_has_lov
              ,xxas_com_elements_pkg.get_iv_lookup_type
                 (eit.eei_information12, :pEffectiveDate) field3_lookup_type
              ,xxas_com_elements_pkg.get_iv_value_set_id
                 (eit.eei_information12, :pEffectiveDate) field3_value_set_id
              ,xxas_com_elements_pkg.get_iv_has_ff_validation_char
                 (eit.eei_information12, :pEffectiveDate) field3_has_ff_validation
              ,xxas_com_elements_pkg.get_iv_warn_or_err
                 (eit.eei_information12, :pEffectiveDate, e.element_link_id) field3_warning_or_error
              ,xxas_com_elements_pkg.get_iv_min_value
                 (eit.eei_information12, :pEffectiveDate, e.element_link_id) field3_min
              ,xxas_com_elements_pkg.get_iv_max_value
                 (eit.eei_information12, :pEffectiveDate, e.element_link_id) field3_max
              ,eit.eei_information13 field4_iv_id
              ,xxas_com_elements_pkg.get_iv_name
                 (eit.eei_information13, :pEffectiveDate) field4_label
              ,xxas_com_elements_pkg.get_iv_uom
                 (eit.eei_information13, :pEffectiveDate) field4_datatype
              ,xxas_com_elements_pkg.get_iv_mandatory_char
                 (eit.eei_information13, :pEffectiveDate) field4_required
              ,xxas_com_elements_pkg.get_iv_default_value
                 (eit.eei_information13, :pEffectiveDate, e.element_link_id) field4_default
              ,xxas_com_elements_pkg.get_iv_has_lov_char
                 (eit.eei_information13, :pEffectiveDate) field4_has_lov
              ,xxas_com_elements_pkg.get_iv_lookup_type
                 (eit.eei_information13, :pEffectiveDate) field4_lookup_type
              ,xxas_com_elements_pkg.get_iv_value_set_id
                 (eit.eei_information13, :pEffectiveDate) field4_value_set_id
              ,xxas_com_elements_pkg.get_iv_has_ff_validation_char
                 (eit.eei_information13, :pEffectiveDate) field4_has_ff_validation
              ,xxas_com_elements_pkg.get_iv_warn_or_err
                 (eit.eei_information13, :pEffectiveDate, e.element_link_id) field4_warning_or_error
              ,xxas_com_elements_pkg.get_iv_min_value
                 (eit.eei_information13, :pEffectiveDate, e.element_link_id) field4_min
              ,xxas_com_elements_pkg.get_iv_max_value
                 (eit.eei_information13, :pEffectiveDate, e.element_link_id) field4_max
              ,eit.eei_information14 field5_iv_id
              ,xxas_com_elements_pkg.get_iv_name
                 (eit.eei_information14, :pEffectiveDate) field5_label
              ,xxas_com_elements_pkg.get_iv_uom
                 (eit.eei_information14, :pEffectiveDate) field5_datatype
              ,xxas_com_elements_pkg.get_iv_mandatory_char
                 (eit.eei_information14, :pEffectiveDate) field5_required
              ,xxas_com_elements_pkg.get_iv_default_value
                 (eit.eei_information14, :pEffectiveDate, e.element_link_id) field5_default
              ,xxas_com_elements_pkg.get_iv_has_lov_char
                 (eit.eei_information14, :pEffectiveDate) field5_has_lov
              ,xxas_com_elements_pkg.get_iv_lookup_type
                 (eit.eei_information14, :pEffectiveDate) field5_lookup_type
              ,xxas_com_elements_pkg.get_iv_value_set_id
                 (eit.eei_information14, :pEffectiveDate) field5_value_set_id
              ,xxas_com_elements_pkg.get_iv_has_ff_validation_char
                 (eit.eei_information14, :pEffectiveDate) field5_has_ff_validation
              ,xxas_com_elements_pkg.get_iv_warn_or_err
                 (eit.eei_information14, :pEffectiveDate, e.element_link_id) field5_warning_or_error
              ,xxas_com_elements_pkg.get_iv_min_value
                 (eit.eei_information14, :pEffectiveDate, e.element_link_id) field5_min
              ,xxas_com_elements_pkg.get_iv_max_value
                 (eit.eei_information14, :pEffectiveDate, e.element_link_id) field5_max
        FROM   pay_element_types_f pet
              ,fnd_currencies_vl cur
              ,TABLE(xxas_com_elements_pkg.links_tbl(:pAssignmentId, :pEffectiveDate)) e
              ,pay_element_type_extra_info eit
        WHERE  pet.element_type_id = e.element_type_id
        AND    pet.input_currency_code = cur.currency_code(+)
        AND    e.element_type_id = eit.element_type_id
        AND    eit.information_type = ''XXAS_TCM_ENTRY_MAP''
        AND    eit.eei_information1 IS NOT NULL
        AND    eit.eei_information1 = ''Y''
		AND   
    (
    NVL(eit.eei_information20,''N'') = ''N''
		OR
    (NVL(eit.eei_information20,''N'') = ''Y'' AND fnd_global.employee_id not in (select person_id from per_all_assignments_f where assignment_id = :pAssignmentId))
    )
        AND    :pEffectiveDate BETWEEN
               pet.effective_start_date AND pet.effective_end_date
        ORDER BY nvl(to_number(eit.eei_information18), 999)
                ,nvl(eit.eei_information3, e.element_name) ';

  xxas_com_sr_pkg.seed_query_service
    (p_service_name      => 'QueryTimecardElements'
    ,p_securing_function => 'XXAS_SRV_Q_TCM_TC_ELMT_PERM'
    ,p_sql               => l_sql
    ,p_parameters        => xxas_com_sr_param_varray
                              (xxas_com_sr_param_type('pAssignmentId' , xxas_com_sr_pkg.g_NUMBER)
                              ,xxas_com_sr_param_type('pEffectiveDate', xxas_com_sr_pkg.g_DATE)));

END;
/
COMMIT;
EXIT;
