[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select org_information2
from hr_organization_information
where organization_id = p_business_group_id
and org_information_context = 'JP_BUSINESS_GROUP_INFO';
select /*+ ORDERED PUSH_SUBQ */
ctr.contact_type,
cei.contact_extra_info_id,
/* Note to add 1 day to effective_date(This is JP legal age calculation rule). */
trunc(months_between(
to_date(decode(to_char(nvl(least(per.date_of_death, l_effective_date), l_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(l_eoy,'YYYY/MM/DD')),'YYYY/MM/DD') + 1,
per.date_of_birth) / 12) AGE,
--
-- The following 2 flags are available only for "KOU",
-- not available for "OTSU".
--
decode(cei.information_type, c_kou_information_type, cei.cei_information1, null) AGED_DPNT_PARENTS_LT_TYPE,
decode(cei.information_type, c_kou_information_type, cei.cei_information6, null) DSBL_TYPE,
decode(ctr.contact_type, 'S', decode(per.sex, 'F', c_wife_kanji, c_husband_kanji), hrl1.meaning) D_CONTACT_TYPE_KANJI,
decode(ctr.contact_type, 'S', decode(per.sex, 'F', c_wife_kana, c_husband_kana), hrl2.meaning) D_CONTACT_TYPE_KANA,
per.per_information18 LAST_NAME_KANJI,
per.per_information19 FIRST_NAME_KANJI,
per.last_name LAST_NAME_KANA,
per.first_name FIRST_NAME_KANA
from per_all_assignments_f asg,
per_contact_relationships ctr,
per_contact_extra_info_f cei,
per_all_people_f per,
hr_lookups hrl1,
hr_lookups hrl2
where asg.assignment_id = p_assignment_id
and l_effective_date
between asg.effective_start_date and asg.effective_end_date
and ctr.person_id = asg.person_id
and cei.contact_relationship_id(+) = ctr.contact_relationship_id
and cei.information_type(+) = p_information_type
and l_effective_date
between cei.effective_start_date(+) and cei.effective_end_date(+)
/* We need to get "Spouse" CTR information even
1) CTR does not have CEI information.
2) CTR does not exist as of effective_date. */
and (cei.information_type is not null
or (ctr.contact_type = 'S'
and l_effective_date
between nvl(ctr.date_start, l_effective_date) and nvl(ctr.date_end, l_effective_date)))
and per.person_id = ctr.contact_person_id
and ((l_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 l_effective_date
between per2.effective_start_date and per2.effective_end_date)))
and hrl1.lookup_type = 'CONTACT'
and hrl1.lookup_code = ctr.contact_type
and hrl2.lookup_type(+) = 'JP_CONTACT_KANA'
and hrl2.lookup_code(+) = hrl1.lookup_code
order by
decode(ctr.contact_type, 'S', 1, 2),
to_number(ctr.cont_information2),
per.date_of_birth,
decode(per.sex, 'F', 2, 1),
per.last_name,
per.first_name;
select effective_date
into l_effective_date
from fnd_sessions
where session_id = userenv('sessionid');