[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select pa.payroll_id
--, to_number(to_char(pa.effective_date,'J')) effective_date
,replace(substr(FND_DATE.DATE_TO_CANONICAL(pa.effective_date),1,10),'/','-') jsqldate --YYYY-MM-DD
,'' || pa.effective_date || '' effective_date
, aa.assignment_id
, pa.business_group_id
, aa.tax_unit_id
,'''' || bg.currency_code || '''' currency_code
,action_type
,fc.name currency_name
from pay_payroll_actions pa
, pay_assignment_actions aa
, per_business_groups bg
, fnd_currencies_vl fc
where aa.assignment_action_id = p_assignment_action_id
and aa.payroll_action_id = pa.payroll_action_id
and pa.business_group_id = bg.business_group_id
and fc.currency_code = bg.currency_code
and rownum = 1;
select assignment_action_id
from pay_assignment_actions
where level =
(select max(level)
from pay_assignment_actions
connect by source_action_id = prior assignment_action_id
start with assignment_action_id = p_assignment_action_id)
connect by source_action_id = prior assignment_action_id
start with assignment_action_id = p_assignment_action_id;
select locked_action_id,
action_sequence
from pay_action_interlocks,
pay_assignment_actions
where locking_action_id = p_assignment_action_id
and locked_action_id = assignment_action_id
order by action_sequence desc;
select action_type
into l_action_type
from pay_payroll_actions pa
,pay_assignment_actions aa
where aa.assignment_action_id = p_assignment_action_id
and aa.payroll_action_id = pa.payroll_action_id;
select assignment_id
from per_all_assignments_f
where person_id = p_person_id
and p_effective_date between effective_start_date and effective_end_date;
select to_number(substr(max(to_char(pa.effective_date,'J')||lpad(aa.assignment_action_id,15,'0')),8))
from pay_payroll_actions pa,
pay_assignment_actions aa
-- per_time_periods ptp
where aa.action_status = 'C'
and pa.payroll_action_id = aa.payroll_action_id
and aa.assignment_id = asg_id
--and ptp.payroll_id = pa.payroll_id
--and pa.effective_date <= ptp.regular_payment_date
--and p_effective_date between ptp.start_date and ptp.end_date
and pa.action_type in ('P','Q','R','U')
order by pa.effective_date desc ;
'Select org.name COL01
,job.name COL02
,loc.location_code COL03
,grd.name COL04
,pay.payroll_name COL05
,pos.name COL06
,hr_general.decode_organization(:tax_unit_id) COL07
,pg.group_name COL08
,peo.national_identifier COL09
,employee_number COL10
,hl.meaning COL11
,assignment_number COL12
,nvl(ppb1.salary,''0'') COL13
from per_all_people_f peo
,per_all_assignments_f asg
,hr_all_organization_units_vl org
,per_jobs_vl job
,per_all_positions pos
,hr_locations loc
,per_grades_vl grd
,pay_payrolls_f pay
,pay_people_groups pg
,hr_lookups hl
,(select ppb2.pay_basis_id
,ppb2.business_group_id
,ee.assignment_id
,eev.screen_entry_value salary
from per_pay_bases ppb2
,pay_element_entries_f ee
,pay_element_entry_values_f eev
where ppb2.input_value_id = eev.input_value_id
and ee.element_entry_id = eev.element_entry_id
and :effective_date between ee.effective_start_date
and ee.effective_end_date
and :effective_date between eev.effective_start_date
and eev.effective_end_date
) ppb1
where asg.assignment_id = :assignment_id
and :effective_date
between asg.effective_start_date and asg.effective_end_date
and asg.person_id = peo.person_id
and :effective_date
between peo.effective_start_date and peo.effective_end_date
and asg.position_id = pos.position_id(+)
and asg.job_id = job.job_id(+)
and asg.location_id = loc.location_id(+)
and asg.grade_id = grd.grade_id(+)
and asg.people_group_id = pg.people_group_id(+)
and asg.payroll_id = pay.payroll_id(+)
and :effective_date
between pay.effective_start_date(+) and pay.effective_end_date(+)
and asg.organization_id = org.organization_id
and :effective_date
between org.date_from and nvl(org.date_to, :effective_date)
and asg.pay_basis_id = ppb1.pay_basis_id(+)
and asg.assignment_id = ppb1.assignment_id(+)
and asg.business_group_id = ppb1.business_group_id(+)
and hl.application_id (+) = 800
and hl.lookup_type (+) =''NATIONALITY''
and hl.lookup_code (+) =peo.nationality';
select pa.action_type from
pay_payroll_actions pa
, pay_assignment_actions aa
where pa.payroll_action_id = aa.payroll_action_id
and aa.assignment_action_id = p_assignment_action_id;
'select tp1.period_name || decode(tp2.period_name, tp1.period_name, null, '' - '' || tp2.period_name) COL01
,fnd_date.date_to_displaydate(tp1.end_date,2) COL04 -- change as per bug 11830805
,fnd_date.date_to_displaydate(pa2.effective_date,2) COL03 -- change as per bug 11830805
,fnd_date.date_to_displaydate(aa1.start_date,2) COL05 -- change as per bug 11830805
,fnd_date.date_to_displaydate(aa2.end_date,2) COL06 -- change as per bug 11830805
,fnd_date.date_to_displaydate(tp1.start_date,2) COL02 -- change as per bug 11830805
,tp1.period_type COL07
from pay_all_payrolls_f pp1
,pay_all_payrolls_f pp2
,pay_payroll_actions pa1
,pay_payroll_actions pa2
,per_time_periods tp1
,per_time_periods tp2
,pay_assignment_actions aa1
,pay_assignment_actions aa2
where pa1.payroll_action_id = aa1.payroll_action_id
--We are considering effective_date(Date Paid) which some
--localizations may allows the user to change. Its the same
--case for the Date Earned as well. Its better to use
--time period id which is consistent.
/*and pa1.effective_date +nvl(pp1.pay_date_offset,0) =
tp1.regular_payment_date*/
and pa1.time_period_id = tp1.time_period_id
and pa1.payroll_id = tp1.payroll_id
and aa1.assignment_action_id = :PREPAY_MAX_ACTION
and pa2.payroll_action_id = aa2.payroll_action_id
--We are considering effective_date(Date Paid) which some
--localizations may allows the user to change. Its the same
--case for the Date Earned as well. Its better to use
--time period id which is consistent.
/*and pa2.effective_date +nvl(pp2.pay_date_offset,0) =
tp2.regular_payment_date*/
and pa2.time_period_id = tp2.time_period_id
and pa2.payroll_id = tp2.payroll_id
and aa2.assignment_action_id = :PREPAY_MIN_ACTION
and pa1.payroll_id = pp1.payroll_id
and pa1.effective_date between pp1.effective_start_date
and pp1.effective_end_date
and pa2.payroll_id = pp2.payroll_id
and pa2.effective_date between pp2.effective_start_date
and pp2.effective_end_date';
'select tp.period_name COL01
,fnd_date.date_to_displaydate(tp.end_date,2) COL04 -- change as per bug 11830805
,fnd_date.date_to_displaydate(pa.effective_date,2) COL03 -- change as per bug 11830805
,fnd_date.date_to_displaydate(aa.start_date,2) COL05 -- change as per bug 11830805
,fnd_date.date_to_displaydate(aa.end_date,2) COL06 -- change as per bug 11830805
,fnd_date.date_to_displaydate(tp.start_date,2) COL02 -- change as per bug 11830805
,tp.period_type COL07
from pay_payroll_actions pa
,per_time_periods tp
,pay_assignment_actions aa
where pa.payroll_action_id = aa.payroll_action_id
-- Bug 9877851 : Included join on time period id rather than on effective date
/* and pa.effective_date = tp.regular_payment_date */
and pa.time_period_id = tp.time_period_id
and pa.payroll_id = tp.payroll_id
and aa.assignment_action_id = :assignment_action_id';
'select tp.period_name COL01
,fnd_date.date_to_displaydate(tp.end_date,2) COL04 -- change as per bug 11830805
,fnd_date.date_to_displaydate(pa.effective_date,2) COL03 -- change as per bug 11830805
,fnd_date.date_to_displaydate(aa.start_date,2) COL05 -- change as per bug 11830805
,fnd_date.date_to_displaydate(aa.end_date,2) COL06 -- change as per bug 11830805
,fnd_date.date_to_displaydate(tp.start_date,2) COL02 -- change as per bug 11830805
,tp.period_type COL07
from pay_payroll_actions pa
,per_time_periods tp
,pay_assignment_actions aa
where pa.payroll_action_id = aa.payroll_action_id
and pa.time_period_id = tp.time_period_id
and aa.assignment_action_id = :assignment_action_id';
l_sql := 'select /*+ ORDERED */ nvl(ettl.reporting_name,et.element_type_id) COL01
, nvl(orginfo.org_information7,nvl(ettl.reporting_name, ettl.element_name)) COL02
, 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
, hr_organization_information orginfo
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 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 ||''')
and orginfo.org_information1 = ''ELEMENT''
and orginfo.org_information_context = ''Business Group:SOE Detail''
and orginfo.organization_id = :business_group_id
and et.element_type_id (+)= to_number(orginfo.org_information2)
group by nvl(orginfo.org_information7,nvl(ettl.reporting_name, ettl.element_name))
, ettl.reporting_name
,nvl(ettl.reporting_name,et.element_type_id)
UNION ALL
select /*+ ORDERED */ nvl(ettl.reporting_name,et.element_type_id) COL01
, nvl(ettl.reporting_name,ettl.element_name) COL02
, 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
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 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 ||''')
AND not exists (select 1
from hr_organization_information orginfo
WHERE orginfo.org_information1 = ''ELEMENT''
and orginfo.org_information_context = ''Business Group:SOE Detail''
and orginfo.organization_id = :business_group_id
and et.element_type_id = to_number(orginfo.org_information2))
group by nvl(ettl.reporting_name,ettl.element_name)
, ettl.reporting_name
,nvl(ettl.reporting_name,et.element_type_id)
order by COL02';
select pa.payroll_id
, aa.action_sequence
, pa.effective_date
, aa.assignment_id
, pa.business_group_id
, bg.legislation_code
, lrl.rule_mode
from pay_payroll_actions pa
, pay_assignment_actions aa
, per_business_groups bg
, pay_legislation_rules lrl
where aa.assignment_action_id = p_assignment_action_id
and aa.payroll_action_id = pa.payroll_action_id
and pa.business_group_id = bg.business_group_id
and lrl.legislation_code(+) = bg.legislation_code
and lrl.rule_type(+) = 'SAVE_ASG_RUN_BAL';
select ba.defined_balance_id
, bd.dimension_name
, bd.period_type
, bt.balance_name
, bt.reporting_name
, nvl(oi.org_information7,nvl(bt.reporting_name,bt.balance_name)) defined_balance_name
from pay_balance_attributes ba
, pay_bal_attribute_definitions bad
, pay_defined_balances db
, pay_balance_dimensions bd
, pay_balance_types_tl bt
, hr_organization_information oi
where bad.attribute_name = p_balance_attribute
and ( bad.BUSINESS_GROUP_ID IS NULL
OR bad.BUSINESS_GROUP_ID = l_business_group_id)
AND ( bad.LEGISLATION_CODE IS NULL
OR bad.LEGISLATION_CODE = l_legislation_code)
and bad.attribute_id = ba.attribute_id
and ba.defined_balance_id = db.defined_balance_id
and db.balance_dimension_id = bd.balance_dimension_id
and db.balance_type_id = bt.balance_type_id
and bt.language = userenv('LANG')
--
and oi.org_information1 = 'BALANCE'
--
and oi.org_information4 = to_char(bt.balance_type_id)
and oi.org_information5 = to_char(db.balance_dimension_id)
--
and oi.org_information_context = 'Business Group:SOE Detail'
and oi.organization_id = l_business_group_id;
select rb.TAX_UNIT_ID
, rb.JURISDICTION_CODE
, rb.SOURCE_ID
, rb.SOURCE_TEXT
, rb.SOURCE_NUMBER
, rb.SOURCE_TEXT2
from pay_run_balances rb
, pay_assignment_actions aa
, pay_payroll_actions pa
where rb.ASSIGNMENT_ID = l_assignment_id
and l_action_sequence >= aa.action_sequence
and rb.assignment_action_id = aa.assignment_action_id
and aa.payroll_action_id = pa.payroll_action_id
and pa.effective_date >= l_earliest_ctx_date;
select distinct
aa.tax_unit_id tax_unit_id
, rr.jurisdiction_code jurisdiction_code
, decode(l_si_needed_chr,
'Y', pay_balance_pkg.find_context('SOURCE_ID'
,rr.run_result_id)
,null) source_id
, decode(l_st_needed_chr,
'Y', pay_balance_pkg.find_context('SOURCE_TEXT'
,rr.run_result_id)
,null) source_text
, decode(l_sn_needed_chr,
'Y', pay_balance_pkg.find_context('SOURCE_NUMBER'
,rr.run_result_id)
,null) source_number
, decode(l_st2_needed_chr,
'Y', pay_balance_pkg.find_context('SOURCE_TEXT2'
,rr.run_result_id)
,null) source_text2
from pay_assignment_actions aa,
pay_payroll_actions pa,
pay_run_results rr
where aa.ASSIGNMENT_ID = l_assignment_id
and aa.assignment_action_id = rr.assignment_action_id
and l_action_sequence >= aa.action_sequence
and aa.payroll_action_id = pa.payroll_action_id
and pa.effective_date >= l_earliest_ctx_date;
'select distinct ettl.element_name COL01
, nvl(oi.org_information7,nvl(ettl.reporting_name, ettl.element_name)) COL02 -- for BUG 3880887,7527825
, ivtl.name COL03
, rrv.result_value COL04
, 1 COL05 -- to indicate that we should drilldown directly to run_result_values
, 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
, hr_organization_information oi
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 iv.element_type_id = et.element_type_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.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';
'select ORG_PAYMENT_METHOD_NAME COL01
,pt.payment_type_name COL04
,pay_soe_util.getBankDetails('':legislation_code''
,ppm.external_account_id
,''BANK_NAME''
,null) COL02
,pay_soe_util.getBankDetails('':legislation_code''
,ppm.external_account_id
,''BANK_ACCOUNT_NUMBER''
,fnd_profile.value(''HR_MASK_CHARACTERS'')) COL03
,to_char(pp.value,fnd_currency.get_format_mask(:G_CURRENCY_CODE,40)) COL16
from pay_pre_payments pp
, pay_personal_payment_methods_f ppm
, pay_org_payment_methods_f opm
, pay_payment_types_tl pt
where pp.assignment_action_id in
(select ai.locking_action_id
from pay_action_interlocks ai
where ai.locked_action_id :action_clause)
and pp.personal_payment_method_id = ppm.personal_payment_method_id(+)
and :effective_date
between ppm.effective_start_date(+) and ppm.effective_end_date(+)
and pp.org_payment_method_id = opm.org_payment_method_id
and :effective_date
between opm.effective_start_date and opm.effective_end_date
and opm.payment_type_id = pt.payment_type_id
and pt.language = userenv(''LANG'')';
'select distinct line_text COL01
from pay_message_lines
where source_id :action_clause';
SELECT creator_type
FROM pay_element_entries_f pee
WHERE pee.element_entry_id=c_element_entry_id
and c_date_earned between pee.effective_start_date and pee.effective_end_date;
SELECT ptp.start_date
,ptp.end_date
,ptp.period_num || '/' || to_char(ptp.start_date,'YYYY')
FROM per_time_periods ptp,
pay_payroll_actions ppa,
pay_assignment_actions paa,
pay_run_results prr,
pay_element_entries_f pee
WHERE pee.element_entry_id=c_element_entry_id
and prr.run_result_id = pee.source_id
and paa.assignment_action_id=prr.assignment_action_id
and ppa.payroll_action_id=paa.payroll_action_id
and ptp.payroll_id=ppa.payroll_id
and pee.creator_type='RR'
and ppa.date_earned between ptp.start_date and ptp.end_date
and c_date_earned between pee.effective_start_date and pee.effective_end_date;
SELECT ptp.start_date
,ptp.end_date
,ptp.period_num || '/' || to_char(ptp.start_date,'YYYY')
FROM per_time_periods ptp,
pay_payroll_actions ppa,
pay_assignment_actions paa,
pay_run_results prr,
pay_element_entries_f pee
WHERE pee.element_entry_id=c_element_entry_id
and prr.run_result_id = pee.source_id
and paa.assignment_action_id=prr.assignment_action_id
and ppa.payroll_action_id=paa.payroll_action_id
and ptp.payroll_id=ppa.payroll_id
and pee.creator_type='NR'
and ppa.date_earned between ptp.start_date and ptp.end_date
and c_date_earned between pee.effective_start_date and pee.effective_end_date;
SELECT ptp.start_date
,ptp.end_date
,ptp.period_num || '/' || to_char(ptp.start_date,'YYYY')
FROM per_time_periods ptp,
pay_payroll_actions ppa,
pay_assignment_actions paa,
pay_run_results prr,
pay_element_entries_f pee
WHERE pee.element_entry_id=c_element_entry_id
and prr.run_result_id = pee.source_id
and paa.assignment_action_id=prr.assignment_action_id
and ppa.payroll_action_id=paa.payroll_action_id
and ptp.payroll_id=ppa.payroll_id
and pee.creator_type='PR'
and ppa.date_earned between ptp.start_date and ptp.end_date
and c_date_earned between pee.effective_start_date and pee.effective_end_date;
SELECT ptp.start_date
,ptp.end_date
,ptp.period_num || '/' || to_char(ptp.start_date,'YYYY')
FROM per_time_periods ptp,
pay_payroll_actions ppa,
pay_assignment_actions paa,
pay_element_entries_f pee
WHERE pee.element_entry_id=c_element_entry_id
and paa.assignment_action_id=pee.source_asg_action_id
and ppa.payroll_action_id=paa.payroll_action_id
and ptp.payroll_id=ppa.payroll_id
and pee.creator_type='EE'
and ppa.date_earned between ptp.start_date and ptp.end_date
and c_date_earned between pee.effective_start_date and pee.effective_end_date;