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
, hoi.org_information2 COL03
, to_char(sum(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_set_members esm
, pay_element_sets es
, hr_organization_information hoi
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 et.element_type_id = esm.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 ||'''
and hoi.organization_id = :business_group_id
and hoi.org_information_context (+)=''SE_SOE_ELEMENT_ADD_DETAILS''
and nvl(hoi.org_information1,et.element_type_id) = et.element_type_id
group by nvl(ettl.reporting_name,ettl.element_name)
, ettl.reporting_name, hoi.org_information2
,nvl(ettl.reporting_name,et.element_type_id)
order by nvl(ettl.reporting_name,ettl.element_name)';
'select distinct ettl.element_name COL01
, nvl(ettl.reporting_name, ettl.element_name) COL02 -- for BUG 3880887
, ivtl.name COL03
, rrv.result_value COL04
, 1 COL05 -- to indicate that we should drilldown directly to run_result_values
, hoi.org_information2 COL06
, rr.run_result_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_set_members esm
, pay_element_sets es
, hr_organization_information oi
, hr_organization_information hoi
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 rrv.input_value_id = iv.input_value_id
and to_char(iv.input_value_id) = oi.org_information3
and iv.input_value_id = ivtl.input_value_id
and ivtl.language = userenv(''LANG'')
and :effective_date between
iv.effective_start_date and iv.effective_end_date
and to_char(et.element_type_id) = oi.org_information2
and :effective_date between
et.effective_start_date and et.effective_end_date
and et.element_type_id = ettl.element_type_id
and ettl.language = userenv(''LANG'')
and et.element_type_id = esm.element_type_id
and ( esm.BUSINESS_GROUP_ID IS NULL
OR esm.BUSINESS_GROUP_ID = :business_group_id)
AND ( esm.LEGISLATION_CODE IS NULL
OR esm.LEGISLATION_CODE = '':legislation_code'')
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 || '''
--
and oi.org_information1 = ''ELEMENT''
--
and oi.org_information_context = ''Business Group:SOE Detail''
and oi.organization_id = :business_group_id
and hoi.organization_id = :business_group_id
and hoi.org_information_context (+)=''SE_SOE_ELEMENT_ADD_DETAILS''
and nvl(hoi.org_information1,et.element_type_id) = et.element_type_id';