[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
'select /*+ ORDERED */ nvl(ettl.reporting_name,et.element_type_id) COL01
, nvl(ettl.reporting_name,ettl.element_name) COL02
, to_char(sum(decode(ec.classification_name,
''Earnings Adjustment''
,( 0 - FND_NUMBER.CANONICAL_TO_NUMBER(rrv.result_value))
,FND_NUMBER.CANONICAL_TO_NUMBER(rrv.result_value))
),fnd_currency.get_format_mask(:G_CURRENCY_CODE,40)) COL16
, decode(count(*),1,''1'',''2'') COL17 -- destination indicator
, decode(count(*),1,max(rr.run_result_id),max(et.element_type_id)) COL18
from pay_assignment_actions aa
, pay_run_results rr
, pay_run_result_values rrv
, pay_input_values_f iv
, pay_input_values_f_tl ivtl
, pay_element_types_f et
, pay_element_types_f_tl ettl
, pay_element_classifications ec
where aa.assignment_action_id :action_clause
and aa.assignment_action_id = rr.assignment_action_id
and rr.status in (''P'',''PA'')
and rr.run_result_id = rrv.run_result_id
and rr.element_type_id = et.element_type_id
and :effective_date between
et.effective_start_date and et.effective_end_date
and et.element_type_id = ettl.element_type_id
and rrv.input_value_id = iv.input_value_id
and iv.name = ''Pay Value''
and :effective_date between
iv.effective_start_date and iv.effective_end_date
and iv.input_value_id = ivtl.input_value_id
and ettl.language = userenv(''LANG'')
and ivtl.language = userenv(''LANG'')
and iv.element_type_id = et.element_type_id
and et.CLASSIFICATION_ID = ec.CLASSIFICATION_ID
and exists (select 1
from pay_element_set_members esm
,pay_element_sets es
where et.element_type_id = esm.element_type_id
and iv.element_type_id = et.element_type_id
and esm.element_set_id = es.element_set_id
and ( es.BUSINESS_GROUP_ID IS NULL
OR es.BUSINESS_GROUP_ID = :business_group_id )
AND ( es.LEGISLATION_CODE IS NULL
OR es.LEGISLATION_CODE = '':legislation_code'' )
and es.element_set_name = '''|| p_element_set_name ||''' )
group by nvl(ettl.reporting_name,ettl.element_name)
, ettl.reporting_name
,nvl(ettl.reporting_name,et.element_type_id)
order by nvl(ettl.reporting_name,ettl.element_name)';