[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select hou.business_group_id,
to_number(hoi.org_information1) header_record_formatter,
to_number(hoi.org_information2) data_record_init,
to_number(hoi.org_information3) data_record_detail,
to_number(hoi.org_information4) data_record_formatter,
to_number(hoi.org_information5) trailer_record_formatter,
to_number(hoi.org_information6) end_record_formatter
from hr_organization_information hoi,
hr_all_organization_units hou
where hou.organization_id = g_parameter.organization_id
and hoi.organization_id(+) = hou.organization_id
and hoi.org_information_context(+) = 'JP_LTAX_EFILE';
select v.assignment_action_id,
v.ltax_district_code,
v.assignment_id,
v.date_earned,
v.ltax,
v.ltax_lumpsum,
v.sp_ltax,
v.sp_ltax_income,
v.sp_ltax_shi,
v.sp_ltax_to
from (select pptn.action_information1 assignment_action_id,
paan.assignment_id assignment_id,
ppan.date_earned date_earned,
pptn.action_information3 ltax_district_code,
pptn.action_information5 ltax,
pptn.action_information6 ltax_lumpsum,
decode(pptn.action_information3,
nvl(pptn.action_information15,pptn.action_information3), pptn.action_information7, 0) sp_ltax,
decode(pptn.action_information3,
nvl(pptn.action_information15,pptn.action_information3), pptn.action_information8, 0) sp_ltax_income,
decode(pptn.action_information3,
nvl(pptn.action_information15,pptn.action_information3), pptn.action_information9, 0) sp_ltax_shi,
decode(pptn.action_information3,
nvl(pptn.action_information15,pptn.action_information3), pptn.action_information10, 0) sp_ltax_to
from pay_payroll_actions ppan,
pay_assignment_actions paan,
pay_action_information pptn
where ppan.business_group_id = g_parameter.business_group_id
and ppan.action_type in ('R', 'Q', 'B')
and ppan.effective_date
between g_parameter.effective_date_from and g_parameter.effective_date_to
and paan.payroll_action_id = ppan.payroll_action_id
and paan.action_status = 'C'
and pptn.action_information_category = 'JP_PRE_TAX_2'
and pptn.action_context_type = 'AAP'
and pptn.action_information1 = paan.assignment_action_id
and pptn.assignment_id = paan.assignment_id
and pptn.action_information3 is not null
union
select pptl.action_information1 assignment_action_id,
paal.assignment_id assignment_id,
ppal.date_earned date_earned,
pptl.action_information15 ltax_district_code,
pptl.action_information5 ltax,
decode(pptl.action_information15,
nvl(pptl.action_information3,pptl.action_information15), pptl.action_information6, 0) ltax_lumpsum,
pptl.action_information7 sp_ltax,
pptl.action_information8 sp_ltax_income,
pptl.action_information9 sp_ltax_shi,
pptl.action_information10 sp_ltax_to
from pay_payroll_actions ppal,
pay_assignment_actions paal,
pay_action_information pptl
where ppal.business_group_id = g_parameter.business_group_id
and ppal.action_type in ('R', 'Q', 'B')
and ppal.effective_date
between g_parameter.effective_date_from and g_parameter.effective_date_to
and paal.payroll_action_id = ppal.payroll_action_id
and paal.action_status = 'C'
and pptl.action_information_category = 'JP_PRE_TAX_2'
and pptl.action_context_type = 'AAP'
and pptl.action_information1 = paal.assignment_action_id
and pptl.assignment_id = paal.assignment_id
and pptl.action_information15 is not null) v,
per_all_assignments_f asg,
per_all_people_f per
where asg.assignment_id = v.assignment_id
and v.date_earned
between asg.effective_start_date and asg.effective_end_date
and per.person_id = asg.person_id
and v.date_earned
between per.effective_start_date and per.effective_end_date
order by
v.ltax_district_code,
decode(p_sort_order,
'ASSIGNMENT_NUMBER', asg.assignment_number,
'EMPLOYEE_NUMBER', per.employee_number,
nvl(per.order_name, per.full_name));
select min(f.formula_id)
into l_formula_id
from ff_formula_types t,
ff_formulas_f f
where f.formula_name = p_formula_name
and f.legislation_code = 'JP'
and f.business_group_id is null
and t.formula_type_id = f.formula_type_id
and t.formula_type_name = 'Payment';
select effective_date
into g_parameter.effective_date
from fnd_sessions
where session_id = userenv('sessionid');
select count(1)
into l_count
from pay_action_information pai
where pai.action_information_category = 'JP_PRE_TAX_1'
and pai.action_context_type = 'AAP'
and pai.action_information1 = l_rec.assignment_action_id
and pai.action_information21 = g_parameter.organization_id
and not exists(
select NULL
from pay_action_interlocks pain,
pay_assignment_actions paan2,
pay_payroll_actions ppan2
where pain.locked_action_id = pai.action_information1
and paan2.assignment_action_id = pain.locking_action_id
and ppan2.payroll_action_id = paan2.payroll_action_id
and ppan2.action_type = 'V');
select adr.district_name_kana,
psn.swot_number
into l_district_name_kana,
l_swot_number
from per_jp_address_lookups adr,
pay_jp_swot_numbers psn
where adr.district_code = substrb(l_rec.ltax_district_code, 1, 5)
and psn.organization_id(+) = g_parameter.organization_id
and psn.district_code(+) = adr.district_code || substrb(l_rec.ltax_district_code,6);