[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select to_char(p_date, p_date_format, 'NLS_CALENDAR=''Japanese Imperial''')
into l_dummy
from dual;
select nvl(sum(taxable_amt), 0),
nvl(sum(mutual_aid), 0),
nvl(sum(si_prem), 0),
nvl(sum(itax), 0),
nvl(sum(itax_adjustment), 0),
nvl(sum(disaster_tax_reduction), 0)
into p_withholding_tax_info.taxable_income,
p_withholding_tax_info.mutual_aid_prem,
p_withholding_tax_info.si_prem,
p_withholding_tax_info.itax,
p_withholding_tax_info.itax_adjustment,
p_withholding_tax_info.disaster_tax_reduction
from pay_jp_wic_assacts_v2
where assignment_id = p_assignment_id
and action_sequence <= p_action_sequence
and effective_date >= trunc(p_effective_date, 'YYYY')
and itax_organization_id = p_itax_organization_id;
select nvl(sum(taxable_amt), 0),
nvl(sum(mutual_aid), 0),
nvl(sum(si_prem), 0),
nvl(sum(itax), 0),
nvl(sum(itax_adjustment), 0),
nvl(sum(disaster_tax_reduction), 0)
into p_withholding_tax_info.taxable_income,
p_withholding_tax_info.mutual_aid_prem,
p_withholding_tax_info.si_prem,
p_withholding_tax_info.itax,
p_withholding_tax_info.itax_adjustment,
p_withholding_tax_info.disaster_tax_reduction
from pay_jp_wic_assacts_v2
where assignment_id = p_assignment_id
and action_sequence <= p_action_sequence
and effective_date >= trunc(p_effective_date, 'YYYY')
and (
(p_itax_category in ('M_KOU', 'D_KOU') and itax_category in ('M_KOU', 'D_KOU'))
or (p_itax_category in ('M_OTSU', 'D_OTSU') and itax_category in ('M_OTSU', 'D_OTSU'))
or (p_itax_category = 'D_HEI' and itax_category = 'D_HEI')
)
and itax_organization_id = p_itax_organization_id;
select wic.assignment_action_id,
wic.assignment_id,
wic.action_sequence,
wic.effective_date,
wic.itax_organization_id,
wic.itax_category,
wic.itax_yea_category,
hoi.org_information1 SALARY_PAYER_NAME_KANJI,
hoi.org_information2 SALARY_PAYER_NAME_KANA,
hoi.org_information6 || hoi.org_information7 || hoi.org_information8 SALARY_PAYER_ADDRESS_KANJI,
hoi.org_information9 || hoi.org_information10 || hoi.org_information11 SALARY_PAYER_ADDRESS_KANA
from hr_organization_information hoi,
pay_jp_wic_assacts_v wic
where wic.assignment_id = p_assignment_id
and wic.action_sequence < p_action_sequence
and wic.effective_date >= trunc(p_effective_date, 'YYYY')
and wic.itax_organization_id <> p_itax_organization_id
-- The following condition removed because dimension (in YEA) can not handle this case.
-- and wic.itax_category in ('M_KOU', 'D_KOU', 'D_HEI')
and hoi.organization_id(+) = wic.itax_organization_id
and hoi.org_information_context(+) = 'JP_TAX_SWOT_INFO'
order by wic.action_sequence desc;
select nvl(fnd_number.canonical_to_number(pem_information3), 0) taxable_income,
nvl(fnd_number.canonical_to_number(pem_information4), 0) si_prem,
fnd_number.canonical_to_number(pem_information5) mutual_aid_prem,
nvl(fnd_number.canonical_to_number(pem_information6), 0) itax,
end_date termination_date,
decode(employer_country, null, 'N', 'JP', 'N', 'Y') foreign_address_flag,
employer_name salary_payer_name_kanji,
employer_address salary_payer_address_kanji,
pem_information1 salary_payer_name_kana,
pem_information2 salary_payer_address_kana
from per_previous_employers
where person_id = p_person_id
--
-- Bug.4159708. Added new segment "Tax Year"(PEM_INFORMATION7)
--
and pem_information_category = 'JP'
and p_effective_date >= nvl(end_date, p_effective_date)
and (
(
pem_information7 is not null
and fnd_number.canonical_to_number(pem_information7) = to_number(to_char(p_effective_date, 'YYYY'))
)
or ( pem_information7 is null
and end_date >= trunc(p_effective_date, 'YYYY')
)
)
order by end_date desc;
select nvl(sum(decode(piv.display_sequence, 1, to_number(peev.screen_entry_value))), 0) taxable_income,
nvl(sum(decode(piv.display_sequence, 2, to_number(peev.screen_entry_value))), 0) si_prem,
sum(decode(piv.display_sequence, 3, to_number(peev.screen_entry_value))) mutual_aid_prem,
nvl(sum(decode(piv.display_sequence, 4, to_number(peev.screen_entry_value))), 0) itax,
min(decode(piv.display_sequence, 5, fnd_date.canonical_to_date(peev.screen_entry_value))) termination_date,
nvl(min(decode(piv.display_sequence, 6, peev.screen_entry_value)), 'N') foreign_address_flag,
min(decode(piv.display_sequence, 7, peev.screen_entry_value)) salary_payer_address_kana,
min(decode(piv.display_sequence, 8, peev.screen_entry_value)) salary_payer_address_kanji,
min(decode(piv.display_sequence, 9, peev.screen_entry_value)) salary_payer_name_kana,
min(decode(piv.display_sequence, 10, peev.screen_entry_value)) salary_payer_name_kanji
from pay_input_values_f piv,
pay_element_entry_values_f peev,
pay_element_entries_f pee,
pay_element_links_f pel
where pel.element_type_id = g_prev_job_elm
and pel.business_group_id + 0 = p_business_group_id
and p_date_earned
between pel.effective_start_date and pel.effective_end_date
and pee.assignment_id = p_assignment_id
and pee.element_link_id = pel.element_link_id
and p_date_earned
between pee.effective_start_date and pee.effective_end_date
and peev.element_entry_id = pee.element_entry_id
and peev.effective_start_date = pee.effective_start_date
and peev.effective_end_date = pee.effective_end_date
and piv.input_value_id = peev.input_value_id
and p_date_earned
between piv.effective_start_date and piv.effective_end_date
group by pee.element_entry_id
order by termination_date desc;
select /*+ ORDERED USE_NL(PER) */
per.last_name LAST_NAME_KANA,
per.per_information18 LAST_NAME_KANJI,
per.first_name FIRST_NAME_KANA,
per.per_information19 FIRST_NAME_KANJI,
per.date_of_birth,
decode(ctr.contact_type, 'S', decode(p_sex, 'F', g_prompt_kanji.husband, g_prompt_kanji.wife),
hr_general.decode_lookup('CONTACT', ctr.contact_type)) D_CONTACT_TYPE_KANJI,
decode(ctr.contact_type, 'S', decode(p_sex, 'F', g_prompt_kana.husband, g_prompt_kana.wife),
hr_jp_standard_pkg.to_hankaku(hr_general.decode_lookup('JP_CONTACT_KANA', ctr.contact_type))) D_CONTACT_TYPE_KANA,
trunc(months_between(
to_date(decode(to_char(nvl(least(per.date_of_death, p_effective_date), p_effective_date),'YYYY/MM/DD'),
to_char(per.date_of_death,'YYYY/MM/DD'), to_char(per.date_of_death,'YYYY/MM/DD'),
to_char(add_months(trunc(p_effective_date, 'YYYY'), 12) - 1,'YYYY/MM/DD')),'YYYY/MM/DD') + 1,
per.date_of_birth) / 12) age
from per_contact_relationships ctr,
per_all_people_f per
where ctr.person_id = p_person_id
and ctr.dependent_flag = 'Y'
and p_effective_date
between nvl(ctr.date_start, p_effective_date) and nvl(ctr.date_end, p_effective_date)
and per.person_id = ctr.contact_person_id
and ( p_effective_date
between per.effective_start_date and per.effective_end_date
or (
per.effective_start_date = per.start_date
and not exists(
select null
from per_all_people_f per2
where per2.person_id = per.person_id
and p_effective_date
between per2.effective_start_date and per2.effective_end_date)
)
)
order by
decode(ctr.contact_type, 'S', 1, 2),
per.date_of_birth,
decode(per.sex, 'M', 1, 'F', 2, 3),
per.last_name,
per.first_name;
select /*+ ORDERED
USE_NL(PIV_1, PIV_3, PLIV_1, PLIV_2, PLIV_3, PEE, PEEV_1, PEEV_2, PEEV_3)
INDEX(PIV_1 PAY_INPUT_VALUES_F_PK)
INDEX(PIV_3 PAY_INPUT_VALUES_F_PK)
INDEX(PLIV_1 PAY_LINK_INPUT_VALUES_F_N2)
INDEX(PLIV_2 PAY_LINK_INPUT_VALUES_F_N2)
INDEX(PLIV_3 PAY_LINK_INPUT_VALUES_F_N2)
INDEX(PEE PAY_ELEMENT_ENTRIES_F_N51)
INDEX(PEEV_1 PAY_ELEMENT_ENTRY_VALUES_F_N50)
INDEX(PEEV_2 PAY_ELEMENT_ENTRY_VALUES_F_N50)
INDEX(PEEV_3 PAY_ELEMENT_ENTRY_VALUES_F_N50) */
decode(piv_1.uom,'D',fnd_date.canonical_to_date(peev_1.screen_entry_value),null) residence_date,
peev_2.screen_entry_value loan_type,
decode(piv_3.uom,'M',fnd_number.canonical_to_number(peev_3.screen_entry_value),null) loan_balance
from pay_input_values_f piv_1,
pay_input_values_f piv_3,
pay_link_input_values_f pliv_1,
pay_link_input_values_f pliv_2,
pay_link_input_values_f pliv_3,
pay_element_entries_f pee,
pay_element_entry_values_f peev_1,
pay_element_entry_values_f peev_2,
pay_element_entry_values_f peev_3
where piv_1.input_value_id = g_housing_loan_info_elm.res_date_iv
and p_date_earned
between piv_1.effective_start_date and piv_1.effective_end_date
and piv_3.input_value_id = g_housing_loan_info_elm.loan_balance_iv
and p_date_earned
between piv_3.effective_start_date and piv_3.effective_end_date
and pliv_1.input_value_id = piv_1.input_value_id
and p_date_earned
between pliv_1.effective_start_date and pliv_1.effective_end_date
and pliv_2.input_value_id = g_housing_loan_info_elm.loan_type_iv
and p_date_earned
between pliv_2.effective_start_date and pliv_2.effective_end_date
and pliv_3.input_value_id = piv_3.input_value_id
and p_date_earned
between pliv_3.effective_start_date and pliv_3.effective_end_date
and pee.element_link_id = pliv_1.element_link_id
and pee.element_link_id = pliv_2.element_link_id
and pee.element_link_id = pliv_3.element_link_id
and pee.assignment_id = p_assignment_id
and pee.entry_type = 'E'
and p_date_earned
between pee.effective_start_date and pee.effective_end_date
and peev_1.element_entry_id = pee.element_entry_id
and peev_1.input_value_id = pliv_1.input_value_id
and peev_1.effective_start_date = pee.effective_start_date
and peev_1.effective_end_date = pee.effective_end_date
and peev_2.element_entry_id = pee.element_entry_id
and peev_2.input_value_id = pliv_2.input_value_id
and peev_2.effective_start_date = pee.effective_start_date
and peev_2.effective_end_date = pee.effective_end_date
and peev_3.element_entry_id = pee.element_entry_id
and peev_3.input_value_id = pliv_3.input_value_id
and peev_3.effective_start_date = pee.effective_start_date
and peev_3.effective_end_date = pee.effective_end_date
order by 1, 2;
select /*+ ORDERD USE_NL(PEE PEEV PIV) */
nvl(min(decode(piv.display_sequence, 1, peev.screen_entry_value)), 'N') itw_override_flag,
min(decode(piv.display_sequence, 2, peev.screen_entry_value)) itw_description1,
min(decode(piv.display_sequence, 3, peev.screen_entry_value)) itw_description2,
min(decode(piv.display_sequence, 4, peev.screen_entry_value)) itw_description3,
min(decode(piv.display_sequence, 5, peev.screen_entry_value)) itw_description4,
min(decode(piv.display_sequence, 6, peev.screen_entry_value)) itw_description5,
nvl(min(decode(piv.display_sequence, 7, peev.screen_entry_value)), 'N') wtm_override_flag,
min(decode(piv.display_sequence, 8, peev.screen_entry_value)) wtm_description1,
min(decode(piv.display_sequence, 9, peev.screen_entry_value)) wtm_description2,
min(decode(piv.display_sequence, 10, peev.screen_entry_value)) wtm_description3,
min(decode(piv.display_sequence, 11, peev.screen_entry_value)) wtm_description4,
min(decode(piv.display_sequence, 12, peev.screen_entry_value)) wtm_description5
from pay_element_links_f pel,
pay_element_entries_f pee,
pay_element_entry_values_f peev,
pay_input_values_f piv
where pel.element_type_id = g_desc_element_type_id
and pel.business_group_id = p_business_group_id
and p_effective_date
between pel.effective_start_date and pel.effective_end_date
and pee.assignment_id = p_assignment_id
and pee.element_link_id = pel.element_link_id
and pee.entry_type = 'E'
and p_effective_date
between pee.effective_start_date and pee.effective_end_date
and peev.element_entry_id = pee.element_entry_id
and peev.effective_start_date = pee.effective_start_date
and peev.effective_end_date = pee.effective_end_date
and piv.input_value_id = peev.input_value_id
and p_effective_date
between piv.effective_start_date and piv.effective_end_date
group by pee.element_entry_id;
select 'Y'
from per_contact_relationships ctr
where ctr.person_id = p_person_id
and ctr.contact_type = 'S'
and p_effective_date
between nvl(ctr.date_start, p_effective_date) and nvl(ctr.date_end, p_effective_date);
select wic.assignment_id,
wic.action_sequence,
wic.business_group_id,
wic.effective_date,
wic.date_earned,
wic.itax_organization_id,
wic.itax_category,
wic.itax_yea_category,
nvl(nvl(pay.prl_information1, hoi.org_information2), 'CTR_EE') DPNT_REF_TYPE,
nvl(fnd_date.canonical_to_date(pay_core_utils.get_parameter('ITAX_DPNT_EFFECTIVE_DATE', wic.legislative_parameters)),
wic.effective_date) DPNT_EFFECTIVE_DATE,
per.person_id,
per.sex,
per.date_of_birth,
wic.leaving_reason,
per.per_information18 LAST_NAME_KANJI,
per.last_name LAST_NAME_KANA,
wic.employment_category
from per_all_people_f per,
hr_organization_information hoi,
pay_all_payrolls_f pay,
/* Use V2 instead of V for debugging. */
pay_jp_wic_assacts_v2 wic
where wic.assignment_action_id = p_assignment_action_id
and pay.payroll_id = wic.payroll_id
and wic.date_earned
between pay.effective_start_date and pay.effective_end_date
and hoi.organization_id(+) = wic.business_group_id
and hoi.org_information_context(+) = 'JP_BUSINESS_GROUP_INFO'
and per.person_id = wic.person_id
and wic.effective_date
between per.effective_start_date and per.effective_end_date;