[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select fe.function_id
from fnd_menu_entries fe
where fe.function_id is not null
start with fe.menu_id=
(select resp.menu_id from fnd_responsibility resp
where resp.responsibility_id=p_responsibility_id
and resp.application_id=800)
connect by prior fe.sub_menu_id= fe.menu_id
and fe.grant_flag='Y'
order by level,fe.entry_sequence;
select fff.function_name
from fnd_form_functions fff
where fff.function_id=p_function_id;
SELECT empl.EMPLOYER_NAME
FROM PER_PREVIOUS_EMPLOYERS empl,
PER_ALL_PEOPLE_F ppf,
PER_ALL_PEOPLE_F ppf2
WHERE ppf.person_id = p_person_id
AND ppf.party_id = ppf2.party_id
AND p_date BETWEEN ppf.effective_start_date and ppf.effective_end_date
AND p_date BETWEEN ppf2.effective_start_date and ppf2.effective_end_date
AND ppf2.person_id = empl.person_id
AND p_date BETWEEN nvl(empl.START_DATE,trunc(SYSDATE))
and(nvl(empl.END_DATE,hr_api.g_eot));
SELECT ppe.employer_name
FROM per_all_people_f ppf
,per_previous_employers ppe
WHERE ppf.party_id = p_party_id
AND p_eff_date BETWEEN ppf.effective_start_date AND ppf.effective_end_date
AND p_eff_date BETWEEN ppe.start_date AND NVL(ppe.end_date,HR_GENERAL.end_of_time)
AND ppe.person_id = ppf.person_id
ORDER BY NVL(ppe.end_date,HR_GENERAL.end_of_time) DESC
,NVL(ppe.start_date,HR_GENERAL.start_of_time) DESC;
SELECT QTYP.NAME,qtyp.rank
FROM PER_QUALIFICATION_TYPES QTYP,
PER_QUALIFICATIONS QUAL,
PER_ALL_PEOPLE_F ppf,
PER_ALL_PEOPLE_F ppf2
WHERE ppf.person_id = p_person
AND ppf.party_id = ppf2.party_id
AND trunc(sysdate) BETWEEN ppf.effective_start_date and ppf.effective_end_date
AND trunc(sysdate) BETWEEN ppf2.effective_start_date and ppf2.effective_end_date
AND ppf2.person_id = QUAL.PERSON_ID
AND QUAL.QUALIFICATION_TYPE_ID = QTYP.QUALIFICATION_TYPE_ID
order by QTYP.RANK desc, qual.awarded_date desc, qual.creation_date desc;
SELECT QTYP.NAME
FROM PER_QUALIFICATION_TYPES QTYP,
PER_QUALIFICATIONS QUAL,
PER_ESTABLISHMENT_ATTENDANCES ESTAB,
PER_ALL_PEOPLE_F ppf,
PER_ALL_PEOPLE_F ppf2
WHERE ppf.person_id = p_person
AND ppf.party_id = ppf2.party_id
AND trunc(sysdate) BETWEEN ppf.effective_start_date and ppf.effective_end_date
AND trunc(sysdate) BETWEEN ppf2.effective_start_date and ppf2.effective_end_date
AND ppf2.person_id = ESTAB.PERSON_ID
AND estab.attendance_id=qual.attendance_id
AND QUAL.QUALIFICATION_TYPE_ID = QTYP.QUALIFICATION_TYPE_ID
and nvl(qtyp.rank,-1)>=nvl(p_max_rank,-1)
order by QTYP.RANK desc, qual.awarded_date desc, qual.creation_date desc;
SELECT QTYP.NAME
FROM PER_QUALIFICATION_TYPES QTYP,
PER_QUALIFICATIONS QUAL,
PER_ALL_PEOPLE_F ppf
WHERE ppf.party_id = p_party_id
AND trunc(sysdate) BETWEEN ppf.effective_start_date and ppf.effective_end_date
AND ppf.person_id = QUAL.PERSON_ID
AND QUAL.QUALIFICATION_TYPE_ID = QTYP.QUALIFICATION_TYPE_ID
order by QTYP.RANK desc, qual.awarded_date desc, qual.creation_date desc;
SELECT /*+ FIRST_ROWS */ QTYP.NAME
FROM PER_QUALIFICATION_TYPES QTYP,
PER_QUALIFICATIONS QUAL,
PER_ESTABLISHMENT_ATTENDANCES ESTAB,
PER_ALL_PEOPLE_F ppf
WHERE ppf.party_id = p_party_id
AND trunc(sysdate) BETWEEN ppf.effective_start_date and ppf.effective_end_date
AND ppf.person_id = ESTAB.PERSON_ID
AND estab.attendance_id=qual.attendance_id
AND QUAL.QUALIFICATION_TYPE_ID = QTYP.QUALIFICATION_TYPE_ID
and not exists (select 1 from per_qualifications qual2,per_qualification_types qtyp2
where qual2.person_id=ppf.person_id
and qtyp2.qualification_type_id=qual2.qualification_type_id
and nvl(qtyp2.rank,-1)>nvl(qtyp.rank,-1))
order by QTYP.RANK desc, qual.awarded_date desc, qual.creation_date desc;
SELECT ttl.user_person_type
FROM per_person_types_tl ttl
,per_person_types typ
,per_person_type_usages_f ptu
,PER_ALL_PEOPLE_F ppf
,PER_ALL_PEOPLE_F ppf2
WHERE ppf.person_id = p_person_id
AND ppf.party_id = ppf2.party_id
AND p_eff_date BETWEEN ppf.effective_start_date and ppf.effective_end_date
AND p_eff_date BETWEEN ppf2.effective_start_date and ppf2.effective_end_date
AND ppf2.person_id = ptu.person_id
AND ttl.language = userenv('LANG')
AND ttl.person_type_id = typ.person_type_id
AND typ.system_person_type IN ('EMP','EX_EMP')
AND typ.person_type_id = ptu.person_type_id
AND p_eff_date BETWEEN ptu.effective_start_date
AND ptu.effective_end_date
order by typ.system_person_type ASC, ptu.effective_start_date DESC;
SELECT ttl.user_person_type
FROM per_person_types_tl ttl
,per_person_types typ
,per_person_type_usages_f ptu
,PER_ALL_PEOPLE_F ppf
WHERE ppf.party_id = p_party_id
AND p_eff_date BETWEEN ppf.effective_start_date and ppf.effective_end_date
AND ppf.person_id = ptu.person_id
AND ttl.language = userenv('LANG')
AND ttl.person_type_id = typ.person_type_id
AND typ.system_person_type IN ('EMP','EX_EMP')
AND typ.person_type_id = ptu.person_type_id
AND p_eff_date BETWEEN ptu.effective_start_date
AND ptu.effective_end_date
order by typ.system_person_type ASC, ptu.effective_start_date DESC;
SELECT GET_EMP_UPT_FOR_PERSON (ppf.person_id,p_eff_date)
FROM per_people_f ppf
WHERE ppf.person_id = p_person_id
AND p_eff_date BETWEEN ppf.effective_start_date
AND ppf.effective_end_date;
SELECT ttl.user_person_type
FROM per_person_types_tl ttl
,per_person_types typ
,per_person_type_usages_f ptu
,PER_ALL_PEOPLE_F ppf
,PER_ALL_PEOPLE_F ppf2
WHERE ppf.person_id = p_person_id
AND ppf.party_id = ppf2.party_id
AND p_eff_date BETWEEN ppf.effective_start_date and ppf.effective_end_date
AND p_eff_date BETWEEN ppf2.effective_start_date and ppf2.effective_end_date
AND ppf2.person_id = ptu.person_id
AND ttl.language = userenv('LANG')
AND ttl.person_type_id = typ.person_type_id
AND typ.system_person_type IN ('APL','EX_APL')
AND typ.person_type_id = ptu.person_type_id
AND p_eff_date BETWEEN ptu.effective_start_date
AND ptu.effective_end_date
ORDER BY typ.system_person_type ASC, ptu.effective_start_date DESC;
SELECT ttl.user_person_type
FROM per_person_types_tl ttl
,per_person_types typ
,per_person_type_usages_f ptu
,PER_ALL_PEOPLE_F ppf
WHERE ppf.party_id = p_party_id
AND p_eff_date BETWEEN ppf.effective_start_date and ppf.effective_end_date
AND ppf.person_id = ptu.person_id
AND ttl.language = userenv('LANG')
AND ttl.person_type_id = typ.person_type_id
AND typ.system_person_type IN ('APL','EX_APL')
AND typ.person_type_id = ptu.person_type_id
AND p_eff_date BETWEEN ptu.effective_start_date
AND ptu.effective_end_date
order by typ.system_person_type ASC, ptu.effective_start_date DESC;
SELECT typ.system_person_type
FROM per_person_types typ
,per_person_type_usages_f ptu
,PER_ALL_PEOPLE_F ppf
,PER_ALL_PEOPLE_F ppf2
WHERE ppf.person_id = p_person_id
AND ppf.party_id = ppf2.party_id
AND p_eff_date BETWEEN ppf.effective_start_date and ppf.effective_end_date
AND p_eff_date BETWEEN ppf2.effective_start_date and ppf2.effective_end_date
AND ppf2.person_id = ptu.person_id
AND typ.system_person_type IN ('EMP','EX_EMP')
AND typ.person_type_id = ptu.person_type_id
AND p_eff_date BETWEEN ptu.effective_start_date
AND ptu.effective_end_date
order by typ.system_person_type ASC, ptu.effective_start_date DESC;
SELECT inp.person_id
FROM PER_ALL_PEOPLE_F PPF,
IRC_NOTIFICATION_PREFERENCES INP,
PER_ALL_PEOPLE_F PPF2
WHERE ppf.person_id = p_person_id
AND trunc(p_effective_date) BETWEEN ppf.effective_start_date
AND ppf.effective_end_date
AND ppf.party_id = ppf2.party_id
AND trunc(p_effective_date) BETWEEN ppf2.effective_start_date
AND ppf2.effective_end_date
AND ppf2.person_id = inp.person_id;
select 1
from per_all_people_f per_per
where per_per.party_id=p_party_id
and p_eff_date between per_per.effective_start_date and per_per.effective_end_date
and per_per.current_employee_flag='Y';
select 1
from per_all_people_f per_per
,per_all_assignments_f per_asg
,per_vacancies per_vac
where per_per.party_id=p_party_id
and p_eff_date between per_per.effective_start_date and per_per.effective_end_date
and per_per.person_id=per_asg.person_id
and
(
per_asg.vacancy_id=per_vac.vacancy_id
or
per_vac.vacancy_id in (select
pvla.vacancy_id
from
per_vac_linked_assignments pvla,
per_all_assignments_f paf
where
pvla.tgt_apl_asg_id = per_asg.assignment_id
and sysdate between nvl(start_date,sysdate) and nvl(sysdate,end_date)
and paf.assignment_id = pvla.tgt_apl_asg_id
and paf.assignment_type = 'E'
)
);
select 1
from per_all_people_f per_per
where per_per.person_id=p_person_id
and p_eff_date between per_per.effective_start_date and per_per.effective_end_date
and (per_per.current_employee_flag='Y' or per_per.current_npw_flag='Y');
select 1
from per_all_people_f per_per
, per_all_people_f per_per2
where per_per.person_id=p_person_id
and p_eff_date between per_per.effective_start_date and per_per.effective_end_date
and per_per.party_id is not null
and per_per2.party_id=per_per.party_id
and p_eff_date between per_per2.effective_start_date and per_per2.effective_end_date
and (per_per2.current_employee_flag='Y' or per_per2.current_npw_flag='Y');
select employee_id
from fnd_user
where upper(email_address)=upper(p_email_address);
select employee_id
from fnd_user
where user_name=upper(p_user_name);
SELECT pqt.name
FROM per_qualifications pq
,per_qualification_types_vl pqt
WHERE pq.party_id = p_party_id
AND pq.attendance_id IS NOT NULL
AND pq.awarded_date IS NOT NULL
AND pqt.qualification_type_id = pq.qualification_type_id
ORDER BY pq.awarded_date DESC;
,p_update_mode in varchar2
,p_details_version out nocopy number
,p_effective_start_date out nocopy date
,p_effective_end_date out nocopy date
,p_object_version_number out nocopy number
)is
pragma autonomous_transaction;
l_update_mode varchar2(30) := p_update_mode;
select *
from irc_assignment_details_f
where assignment_id =p_assignment_id
and sysdate between effective_start_date and effective_end_Date
and latest_details='Y';
select business_group_id
from per_all_assignments_f
where assignment_id = p_assignment_id
and trunc(sysdate) between effective_start_date and effective_end_date;
select nvl(ORG_INFORMATION11,'N')
from HR_ORGANIZATION_INFORMATION
where organization_id = l_business_grp_id
and org_information_context = 'BG Recruitment';
hr_utility.set_location('p_update_mode::'||p_update_mode,50);
irc_assignment_details_api.update_assignment_details (
p_validate => false
,p_effective_date => l_effective_date
,p_datetrack_update_mode => l_update_mode
,p_assignment_id => l_assignment_id
,p_attempt_id => l_attempt_id
,p_qualified => l_qualified
,p_considered => l_considered
,p_assignment_details_id => l_assignment_details_id
,p_object_version_number => l_object_version_number
,p_details_version => l_details_version
,p_effective_start_date => l_effective_start_date
,p_effective_end_date => l_effective_end_date
);
hr_utility.set_location('Updated assignment details record',60);
l_update_mode varchar2(30);
cursor c_bg_id is select business_group_id from per_all_assignments_f where assignment_id=p_assignment_id and trunc(sysdate) between effective_start_date and effective_end_date;
cursor c_assgn_details_row is select * from irc_assignment_details_f where assignment_id =p_assignment_id and latest_details='Y';
cursor c_assgn_details is select 1 from irc_assignment_details_f where assignment_id =p_assignment_id and sysdate between effective_start_date and effective_end_Date and latest_details='Y';
cursor c_appln_tracking is select nvl(ORG_INFORMATION11,'N') from HR_ORGANIZATION_INFORMATION where organization_id=l_business_grp_id and org_information_context='BG Recruitment';
l_update_mode:='UPDATE';
l_update_mode:='CORRECTION';
irc_assignment_details_api.update_assignment_details (
p_validate => false
,p_effective_date => l_effective_date
,p_datetrack_update_mode => l_update_mode
,p_assignment_id => l_assignment_id
,p_attempt_id => l_assgn_details_row.attempt_id
,p_qualified => l_assgn_details_row.qualified
,p_considered => l_considered
,p_assignment_details_id => l_assignment_details_id
,p_object_version_number => l_object_version_number
,p_details_version => l_details_version
,p_effective_start_date => l_effective_start_date
,p_effective_end_date => l_effective_end_date
);
SELECT history.transaction_history_id
,irc_xml_util.valueOf(histstate.transaction_document,'/Transaction/TransCtx/pAMETranType')
,irc_xml_util.valueOf(histstate.transaction_document,'/Transaction/TransCtx/pAMEAppId')
FROM pqh_ss_transaction_history history,
pqh_ss_trans_state_history histstate
WHERE history.transaction_identifier = 'OFFER'
AND history.transaction_history_id = ( SELECT min(transaction_history_id)
FROM pqh_ss_step_history
WHERE api_name = 'IRC_OFFERS_SWI.PROCESS_OFFERS_API'
AND pk1 = c_offerId )
AND histstate.transaction_history_id = history.transaction_history_id;
select person_id
, business_group_id
, effective_start_date
from per_all_assignments_f
where assignment_id=p_assignment_id
and sysdate between effective_start_date and effective_end_date;
select address_id
from per_addresses
where person_id=l_person_id_appl;
select min(person_id)
from per_all_people_f
where party_id in (select party_id
from per_all_people_f
where person_id=l_person_id_appl);
select *
from per_addresses
where person_id=l_person_id
and address_type='REC'
and sysdate between date_from and nvl(date_to,sysdate);
select *
from per_addresses
where person_id=l_person_id
and primary_flag = 'Y'
and sysdate between date_from and nvl(date_to,sysdate);
SELECT pos.working_hours ,
DECODE(pos.frequency ,'Y',1 ,'M',12 ,'W',52 ,'D',365 ,1)
FROM hr_all_positions pos
WHERE pos.position_id=p_position_id;
SELECT fnd_number.canonical_to_number(org.org_information3) normal_hours ,
DECODE(org.org_information4 ,'Y',1 ,'M',12 ,'W',52 ,'D',365 ,1)
FROM HR_ORGANIZATION_INFORMATION org
WHERE org.organization_id =p_organization_id
AND org.organization_id = p_organization_id
AND org.org_information_context(+) = 'Work Day Information';
SELECT fnd_number.canonical_to_number(bus.working_hours) normal_hours ,
DECODE(bus.frequency ,'Y',1 ,'M',12 ,'W',52 ,'D',365 ,1)
FROM per_business_groups bus
WHERE bus.business_group_id = p_business_group_id;
SELECT asg.normal_hours * (DECODE(asg.frequency, 'D', 365, 'W', 52, 'M', 12, 'Y', 1, 1 ) ) as p_asg_hours_per_year
,asg.position_id as p_position_id
,asg.organization_id as p_organization_id
,asg.business_group_id as p_business_group_id
,pay.change_date as p_effective_date
FROM PER_ALL_ASSIGNMENTS_F asg
,PER_PAY_PROPOSALS pay
WHERE asg.assignment_id = p_assignment_id
and pay.assignment_id = p_assignment_id
and asg.effective_end_date = ( SELECT max (effective_end_date)
FROM per_all_assignments_f
WHERE assignment_id = p_assignment_id
);
select function_id from fnd_form_functions
where function_name = p_function_name;
query:='SELECT PAV.NAME title ,
fnd_message.get_string(''PER'',''IRC_412594_RSS_JOB_TITLE'')||'' ''||IPC.JOB_TITLE ||''
''|| fnd_message.get_string(''PER'',''IRC_412595_RSS_ORGANIZATION'')||'' ''||IPC.ORG_NAME||
''
''|| fnd_message.get_string(''PER'',''IRC_412596_RSS_DESCRIPTION'')||'' ''||IPC.BRIEF_DESCRIPTION description ,
to_char( trunc(pra.date_start),''Dy, DD Mon YYYY'') pubDate,
irc_utilities_pkg.getVacancyRunFunctionUrl('|| l_funcId||',
pav.vacancy_id,
pav.primary_posting_id,
1) rlink
FROM HR_LOOKUPS HRL,
HR_LOOKUPS HL2,
PER_ALL_VACANCIES PAV,
PER_RECRUITMENT_ACTIVITIES PRA,
PER_RECRUITMENT_ACTIVITY_FOR PRF,
IRC_POSTING_CONTENTS_VL IPC,
IRC_SEARCH_CRITERIA IVS,
HR_LOCATIONS_ALL LOC,
HR_LOOKUPS HLW,
HR_LOOKUPS HLT,
IRC_ALL_RECRUITING_SITES ias WHERE pav.status = ''APPROVED''
AND PAV.VACANCY_ID = PRF.VACANCY_ID
AND PRF.RECRUITMENT_ACTIVITY_ID = PRA.RECRUITMENT_ACTIVITY_ID
AND PRA.POSTING_CONTENT_ID = IPC.POSTING_CONTENT_ID
AND PAV.VACANCY_ID = IVS.OBJECT_ID
AND IVS.OBJECT_TYPE = ''VACANCY''
AND HRL.LOOKUP_TYPE(+) = ''IRC_PROFESSIONAL_AREA''
AND IVS.PROFESSIONAL_AREA = HRL.LOOKUP_CODE(+)
AND HL2.LOOKUP_TYPE(+) = ''IRC_EMP_CAT''
AND IVS.EMPLOYMENT_CATEGORY = HL2.LOOKUP_CODE(+)
AND HLW.LOOKUP_TYPE(+) = ''IRC_WORK_AT_HOME''
AND IVS.WORK_AT_HOME = HLW.LOOKUP_CODE(+)
AND HLT.LOOKUP_TYPE(+) = ''IRC_TRAVEL_PERCENTAGE''
AND IVS.TRAVEL_PERCENTAGE = HLT.LOOKUP_CODE(+)
AND sysdate BETWEEN PRA.date_start AND NVL(PRA.date_end,sysdate)
AND loc.location_id(+)=pav.location_id
AND ias.EXTERNAL = ''Y''
AND
(
pav.date_from <= sysdate
AND
(
(
pav.date_to IS NOT NULL
AND pav.date_to > = sysdate
)
OR
(
pav.date_to IS NULL
)
)
)
AND EXISTS
(SELECT 1
FROM per_vacancies vac1
WHERE vac1.vacancy_id =pav.vacancy_id
)
AND pra.recruiting_site_id = ias.recruiting_site_id
AND sysdate BETWEEN pra.date_start AND NVL (pra.date_end, sysdate)';
'SELECT NULL '||
'FROM irc_location_preferences ilp '||
',hr_locations_all loc1 '||
'WHERE ilp.object_id = pav.vacancy_id '||
'AND ilp.object_type = ''VACANCY'' '||
'AND loc1.location_id = ilp.location_id '||
'AND locator_within_distance (loc1.geometry '||
' ,mdsys.sdo_geometry (2001 '||
',8307 '||
' ,mdsys.sdo_point_type ('||l_long||
','||l_lat||
',NULL) '||
',NULL '||
',NULL) '||
',''distance= '||l_distance||',units=mile'') = ''TRUE'' '||
'AND loc1.geometry IS NOT NULL '||
') '||
'OR EXISTS '||
'( '||
'SELECT NULL '||
'FROM hr_locations_all loc2 '||
'WHERE loc2.location_id = pav.location_id '||
'AND locator_within_distance (loc2.geometry '||
',mdsys.sdo_geometry (2001 '||
',8307 '||
',mdsys.sdo_point_type ('||l_long||
','||l_lat||
',NULL) '||
',NULL '||
',NULL) '||
',''distance= '||l_distance||',units=mile'') = ''TRUE'' '||
'AND loc2.geometry IS NOT NULL '||
') '||
') ';
query:='SELECT fnd_message.get_string(''PER'',''IRC_412597_RSS_CRITERIA_ERROR'') title ,
fnd_message.get_string(''PER'',''IRC_412011_BAD_LOCATION'') description ,
to_char(trunc(sysdate),''Dy, DD Mon YYYY'') pubDate,
''/OA_HTML/IrcVisitor.jsp'' rlink from dual';
query:='SELECT fnd_message.get_string(''PER'',''IRC_412597_RSS_CRITERIA_ERROR'') title ,
fnd_message.get_string(''PER'',''IRC_412164_NO_DISTANCE'') description ,
to_char(trunc(sysdate),''Dy, DD Mon YYYY'') pubDate,
''/OA_HTML/IrcVisitor.jsp'' rlink from dual';
'select null ' ||
'from irc_location_preferences ilp, ' ||
'hr_locations_all loc1 '||
'where ilp.object_id = pav.vacancy_id '||
'and ilp.object_type = ''VACANCY'' '||
'and loc1.location_id = ilp.location_id ' ||
'and catsearch(loc1.derived_locale,:'|| bindnum||',NULL) > 0 '||
'and loc1.derived_locale is not null ' ||
') '||
'or exists '||
'( '||
'select null '||
'from hr_locations_all loc2 '||
'where loc2.location_id = loc.location_id '||
'and catsearch(loc2.derived_locale,:'||(bindnum+1)||',NULL) > 0 '||
'and loc2.derived_locale is not null ' ||
') '||
') ';
query:='SELECT fnd_message.get_string(''PER'',''IRC_412597_RSS_CRITERIA_ERROR'') title ,
fnd_message.get_string(''PER'',''PER_34296_DIAG_NO_BLANKL_QUERY'') description ,
to_char(trunc(sysdate),''Dy, DD Mon YYYY'') pubDate,
''/OA_HTML/IrcVisitor.jsp'' rlink from dual';
'SELECT fnd_message.get_string(''PER'',''IRC_412592_RSS_CHANNEL_TITLE'') title, fnd_message.get_string(''PER'',''IRC_412593_RSS_CHANNEL_DESC'') description , to_char(trunc(sysdate),''Dy, DD Mon YYYY'') pubDate,
''/OA_HTML/IrcVisitor.jsp'' rlink
FROM dual';
select CURRENCY_CODE
from per_business_groups
where BUSINESS_GROUP_ID = fnd_profile.value('IRC_CORPORATE_BUSINESS_GROUP');
select nvl(fnd_profile.value('IRC_CURR_CONV_DATE'),'') into l_profile_date
from dual;
select CURRENCY_CODE
from per_business_groups
where BUSINESS_GROUP_ID = fnd_profile.value('IRC_CORPORATE_BUSINESS_GROUP');
select max(conversion_date) into l_user_date
from GL_DAILY_RATES
where from_currency = l_from_currency
and to_currency = l_to_currency
and conversion_type = 'Corporate'
and conversion_date <= effective_date
order by conversion_date desc;
CURSOR csr_select_column_names(dff_name VARCHAR2)
IS
SELECT APPLICATION_COLUMN_NAME
FROM FND_DESCR_FLEX_COLUMN_USAGES
WHERE DESCRIPTIVE_FLEXFIELD_NAME = (dff_name)
and DESCRIPTIVE_FLEX_CONTEXT_CODE = 'Global Data Elements'
AND END_USER_COLUMN_NAME = fnd_profile.value('IRC_OFFER_SAL_FLEX_SEGMENT');
SELECT name
INTO l_grade_name
FROM per_grades
WHERE grade_id = p_grade_id;
OPEN csr_select_column_names('PER_GRADES');
FETCH csr_select_column_names
INTO l_column_name;
IF csr_select_column_names%found THEN
query_stmt := 'select '
|| l_column_name
|| ' from per_grades where grade_id = :grade_id';
CLOSE csr_select_column_names;
SELECT name
INTO l_position_name
FROM HR_ALL_POSITIONS_F
WHERE position_id = p_position_id
AND TRUNC(sysdate) between effective_start_date and effective_end_date;
OPEN csr_select_column_names('PER_POSITIONS');
FETCH csr_select_column_names
INTO l_column_name;
IF csr_select_column_names%found THEN
query_stmt := 'select '
|| l_column_name
|| ' from hr_all_positions_f where position_id = '
|| p_position_id
|| ' AND TRUNC(sysdate) between effective_start_date and effective_end_date';
CLOSE csr_select_column_names;
SELECT name
INTO l_job_name
FROM per_jobs
WHERE job_id = p_job_id;
OPEN csr_select_column_names('PER_JOBS');
FETCH csr_select_column_names
INTO l_column_name;
IF csr_select_column_names%found THEN
query_stmt := 'select '
|| l_column_name
|| ' from per_jobs where job_id = :job_id';
CLOSE csr_select_column_names;
SELECT name
INTO l_organization_name
FROM HR_ALL_ORGANIZATION_UNITS
WHERE organization_id = p_organization_id;
OPEN csr_select_column_names('PER_ORGANIZATION_UNITS');
FETCH csr_select_column_names
INTO l_column_name;
IF csr_select_column_names%found THEN
query_stmt := 'select '
|| l_column_name
|| ' from HR_ALL_ORGANIZATION_UNITS where organization_id = '
||p_organization_id;
CLOSE csr_select_column_names;
SELECT name
INTO l_business_group_name
FROM HR_ALL_ORGANIZATION_UNITS
WHERE organization_id = p_business_group_id;
OPEN csr_select_column_names('PER_ORGANIZATION_UNITS');
FETCH csr_select_column_names
INTO l_column_name;
IF csr_select_column_names%found THEN
query_stmt := 'select '
|| l_column_name
|| ' from HR_ALL_ORGANIZATION_UNITS where organization_id = '
||p_organization_id;
CLOSE csr_select_column_names;
CURSOR csr_select_column_names
IS
SELECT APPLICATION_COLUMN_NAME
FROM FND_DESCR_FLEX_COLUMN_USAGES
WHERE DESCRIPTIVE_FLEXFIELD_NAME = 'PER_PAY_BASES'
and DESCRIPTIVE_FLEX_CONTEXT_CODE = 'Global Data Elements'
AND END_USER_COLUMN_NAME = fnd_profile.value('IRC_OFFER_SAL_FLEX_SEGMENT');
SELECT name
INTO l_salary_basis_name
FROM per_pay_bases
WHERE PAY_BASIS_ID = p_salary_basis_id;
OPEN csr_select_column_names;
FETCH csr_select_column_names
INTO l_column_name;
IF csr_select_column_names%found THEN
query_stmt := 'select '
|| l_column_name
|| ' from per_pay_bases where PAY_BASIS_ID = '
||p_salary_basis_id;
CLOSE csr_select_column_names;
SELECT typ.system_person_type
FROM per_person_types typ
,per_person_type_usages_f ptu
,PER_ALL_PEOPLE_F ppf
WHERE ppf.party_id = p_party_id
AND p_eff_date BETWEEN ppf.effective_start_date and ppf.effective_end_date
AND ppf.person_id = ptu.person_id
AND typ.system_person_type IN ('EMP','EX_EMP')
AND typ.person_type_id = ptu.person_type_id
AND p_eff_date BETWEEN ptu.effective_start_date
AND ptu.effective_end_date
order by typ.system_person_type ASC, ptu.effective_start_date DESC;
SELECT typ.system_person_type,
ppf.rehire_recommendation,
hl.Meaning
FROM per_person_types typ
,per_person_type_usages_f ptu
,per_all_people_f ppf
,hr_lookups hl
WHERE ppf.party_id = p_party_id
AND p_eff_date BETWEEN ppf.effective_start_date and ppf.effective_end_date
AND ppf.person_id = ptu.person_id
AND typ.system_person_type = 'EX_EMP'
AND typ.person_type_id = ptu.person_type_id
AND p_eff_date BETWEEN ptu.effective_start_date AND ptu.effective_end_date
AND hl.lookup_type = 'IRC_REHIRE_RECOMMENDATION'
AND nvl(ppf.rehire_recommendation,'N') = hl.lookup_code
AND ppf.effective_start_date = (SELECT max(effective_start_date)
FROM per_all_people_f ppf1
WHERE ppf1.party_id = ppf.party_id
AND effective_start_date <= p_eff_date
)
AND NOT EXISTS (SELECT 1
FROM per_person_types typ1
,per_all_people_f ppf1
WHERE ppf1.party_id = ppf.party_id
AND typ1.person_type_id = ppf1.person_type_id
AND typ1.system_person_type = 'EMP'
AND p_eff_date BETWEEN ppf1.effective_start_date AND ppf1.effective_end_date
)
ORDER BY typ.system_person_type ASC, ptu.effective_start_date DESC;
select meaning into l_rehire_recommendation
from hr_lookups
where lookup_type = 'IRC_REHIRE_RECOMMENDATION'
and lookup_code = 'NOT_APPLICABLE';
SELECT typ.system_person_type,
ppf.rehire_recommendation
FROM per_person_types typ
,per_person_type_usages_f ptu
,per_all_people_f ppf
WHERE ppf.party_id = p_party_id
AND p_eff_date BETWEEN ppf.effective_start_date and ppf.effective_end_date
AND ppf.person_id = ptu.person_id
AND typ.system_person_type = 'EX_EMP'
AND typ.person_type_id = ptu.person_type_id
AND p_eff_date BETWEEN ptu.effective_start_date AND ptu.effective_end_date
AND ppf.effective_start_date = (SELECT max(effective_start_date)
FROM per_all_people_f ppf1
WHERE ppf1.party_id = ppf.party_id
AND effective_start_date <= p_eff_date
)
AND NOT EXISTS (SELECT 1
FROM per_person_types typ1
,per_all_people_f ppf1
WHERE ppf1.party_id = ppf.party_id
AND typ1.person_type_id = ppf1.person_type_id
AND typ1.system_person_type = 'EMP'
AND p_eff_date BETWEEN ppf1.effective_start_date AND ppf1.effective_end_date
)
ORDER BY typ.system_person_type ASC, ptu.effective_start_date DESC;
select lookup_code into l_rehire_recommendation
from hr_lookups
where lookup_type = 'IRC_REHIRE_RECOMMENDATION'
and lookup_code = 'NOT_APPLICABLE';
SELECT phn.phone_number || nvl2(hlk.meaning, '(' || hlk.meaning || ')', null)
into l_number
FROM per_phones phn, hr_lookups hlk
WHERE phn.parent_id = pPersonIdIn
AND phn.parent_table = 'PER_ALL_PEOPLE_F'
AND trunc(SYSDATE) BETWEEN phn.date_from (+)
AND nvl (phn.date_to , trunc(SYSDATE))
AND phn.validity = hlk.lookup_code (+)
AND hlk.lookup_type (+) = 'IRC_CONTACT_TIMES'
AND phn.phone_type = 'M'
and phn.last_update_date = (select max(last_update_date)
from per_phones phn2
where phn.parent_id = phn2.parent_id
and phn2.phone_type = 'M'
and phn2.parent_table = 'PER_ALL_PEOPLE_F'
)
and rownum <2;
select distinct PERSON_ID
from per_all_assignments_f paf
start with paf.person_id = c_managerId
and sysdate between paf.effective_start_date and paf.effective_end_date
and assignment_type IN ('E','A')
connect by prior paf.supervisor_id = paf.person_id ;
select pav.manager_id
from per_all_vacancies pav
where pav.vacancy_id = p_vacancy_id ;
select nvl(
(SELECT max (idoc3.document_id)
FROM irc_documents idoc3
WHERE idoc3.person_id = c_person_id
AND idoc3.type IN ('RESUME','AUTO_RESUME')
AND
(
SELECT min (ias3.status_change_date) + (5 / 86400)
FROM irc_assignment_statuses ias3
WHERE ias3.assignment_id = c_assignment_Id
) BETWEEN idoc3.creation_date
AND nvl2 (idoc3.end_date
,idoc3.last_update_date - (5 / 86400)
,sysdate)),-1) from dual;
select nvl(
(SELECT max (idoc3.document_id)
FROM irc_documents idoc3
WHERE idoc3.party_id = c_party_id
AND idoc3.type IN ('RESUME','AUTO_RESUME')
AND
(
SELECT min (ias3.status_change_date) + (5 / 86400)
FROM irc_assignment_statuses ias3
WHERE ias3.assignment_id = c_assignment_Id
) BETWEEN idoc3.creation_date
AND nvl2 (idoc3.end_date
,idoc3.last_update_date - (5 / 86400)
,sysdate)),-1) from dual;
select nvl((select file_name from irc_documents
where document_id=p_doc_id),-1) into l_doc_name from dual;