[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select hloc.BUSINESS_GROUP_ID,
to_char(hloc.CREATION_DATE,'YYYY-MM-DD'),
'A' ,
hloc.LOCATION_ID,
tl.language,
tl.DESCRIPTION,
STYLE,
COUNTRY,
ADDRESS_LINE_1,
ADDRESS_LINE_2,
ADDRESS_LINE_3,
TOWN_OR_CITY,
REGION_1,
REGION_2,
REGION_3,
POSTAL_CODE,
TELEPHONE_NUMBER_1,
TELEPHONE_NUMBER_2,
TELEPHONE_NUMBER_3,
LOC_INFORMATION13,
LOC_INFORMATION14,
LOC_INFORMATION15,
LOC_INFORMATION16,
LOC_INFORMATION17,
LOC_INFORMATION18,
LOC_INFORMATION19,
LOC_INFORMATION20
from
hr_locations_all hloc,hr_locations_all_tl tl
where tl.location_id = hloc.location_id
and nvl(inactive_date,to_date('31/12/4712','DD/MM/YYYY')) > sysdate
union
select hloc.BUSINESS_GROUP_ID,
to_char(inactive_date,'YYYY-MM-DD'),
'I',
hloc.LOCATION_ID,
tl.language,
tl.DESCRIPTION,
STYLE,
COUNTRY,
ADDRESS_LINE_1,
ADDRESS_LINE_2,
ADDRESS_LINE_3,
TOWN_OR_CITY,
REGION_1,
REGION_2,
REGION_3,
POSTAL_CODE,
TELEPHONE_NUMBER_1,
TELEPHONE_NUMBER_2,
TELEPHONE_NUMBER_3,
LOC_INFORMATION13,
LOC_INFORMATION14,
LOC_INFORMATION15,
LOC_INFORMATION16,
LOC_INFORMATION17,
LOC_INFORMATION18,
LOC_INFORMATION19,
LOC_INFORMATION20
from
hr_locations_all hloc,hr_locations_all_tl tl
where tl.location_id = hloc.location_id
and inactive_date is not null
order by business_group_id,location_id ;
select pj.job_id,
business_group_id,
tl.language,
tl.name,
to_char(DATE_FROM,'YYYY-MM-DD') ,
'A'
from per_jobs pj,per_jobs_tl tl
where pj.job_id = tl.job_id
and nvl(date_to,to_date('31/12/4712','DD/MM/YYYY')) > sysdate
union
select pj.job_id,
business_group_id,
tl.language,
tl.name,
to_char(DATE_TO,'YYYY-MM-DD') ,
'I'
from per_jobs pj,per_jobs_tl tl
where pj.job_id = tl.job_id
and date_to is not null
order by business_group_id,job_id;
select ORG.BUSINESS_GROUP_ID,
ORG.ORGANIZATION_ID,
to_char(DATE_FROM,'YYYY-MM-DD') ,
'A' ,
TL.LANGUAGE,
TL.NAME,
ORG.LOCATION_ID,
/*Fix for 7576511 - to fetch employee number*/
(select employee_number from per_all_people_f ppf,hr_organization_information hrorg1
where ppf.person_id = hrorg1.ORG_INFORMATION2
and ppf.business_group_id = org.business_group_id
and hrorg1.org_information_context = 'Organization Name Alias'
and hrorg1.organization_id = org.organization_id
and nvl(org.date_to,to_date('31/12/4712','DD/MM/YYYY')) between fnd_date.canonical_to_date(hrorg1.org_information3)
and nvl(fnd_date.canonical_to_date(hrorg1.org_information4),to_date('31/12/4712','DD/MM/YYYY'))
and fnd_date.canonical_to_date(hrorg1.org_information3) between ppf.effective_start_date and ppf.effective_end_date) MANAGER_ID
from hr_all_organization_units org,hr_all_organization_units_tl TL
,hr_organization_information hrorg
where tl.organization_id = org.organization_id
and hrorg.organization_id = org.organization_id
and hrorg.org_information1 = 'HR_ORG'
and nvl(date_to,to_date('31/12/4712','DD/MM/YYYY')) > sysdate
union
select ORG.BUSINESS_GROUP_ID,
ORG.ORGANIZATION_ID,
to_char(DATE_TO,'YYYY-MM-DD') ,
'I' ,
TL.LANGUAGE,
TL.NAME,
ORG.LOCATION_ID,
/*Fix for 7576511 - to fetch employee number*/
(select employee_number from per_all_people_f ppf,hr_organization_information hrorg1
where ppf.person_id = hrorg1.ORG_INFORMATION2
and ppf.business_group_id = org.business_group_id
and hrorg1.org_information_context = 'Organization Name Alias'
and hrorg1.organization_id = org.organization_id
and nvl(org.date_to,to_date('31/12/4712','DD/MM/YYYY')) between fnd_date.canonical_to_date(hrorg1.org_information3)
and nvl(fnd_date.canonical_to_date(hrorg1.org_information4),to_date('31/12/4712','DD/MM/YYYY'))
and fnd_date.canonical_to_date(hrorg1.org_information3) between ppf.effective_start_date and ppf.effective_end_date) MANAGER_ID
from hr_all_organization_units org,hr_all_organization_units_tl TL
,hr_organization_information hrorg
where tl.organization_id = org.organization_id
and hrorg.organization_id = org.organization_id
and hrorg.org_information1 = 'HR_ORG'
and date_to is not null
order by business_group_id,organization_id;
SELECT
pas.person_id,
pas.assignment_id,
pas.assignment_number,
to_char(pas.effective_start_date,'YYYY-MM-DD'),
to_char(pas.effective_end_date,'YYYY-MM-DD'),
pas.probation_period,
pas.probation_unit,
pas.organization_id,
pas.job_id,
pas.position_id,
pas.assignment_status_type_id,
pas.location_id,
pas.employment_category,
pas.business_group_id,
pas.normal_hours,
pas.frequency,
pas.grade_id,
pas.supervisor_id,
case when (pas.person_id = pos.person_id and pas.effective_end_date = pos.actual_termination_date) then
to_char(pos.final_process_date,'YYYY-MM-DD')
when (pas.person_id = pop.person_id and pas.effective_end_date = pop.actual_termination_date) then to_char(pop.final_process_date,'YYYY-MM-DD') end "final_prcs_date",
case when (pas.person_id = pos.person_id and pas.effective_end_date = pos.actual_termination_date)
then to_char(pos.ACTUAL_TERMINATION_DATE,'YYYY-MM-DD')
when (pas.person_id = pop.person_id and pas.effective_end_date = pop.actual_termination_date) then to_char(pop.ACTUAL_TERMINATION_DATE,'YYYY-MM-DD') end "act_termn_date",
primary_flag
FROM
per_all_assignments_f pas,
per_periods_of_service pos,
per_periods_of_placement pop
WHERE pas.person_id = pop.person_id (+)
AND pas.person_id = pos.person_id (+)
order by pas.business_group_id,pas.assignment_id,pas.effective_start_date;
SELECT ppf.person_id,
ppf.business_group_id,
(select org_information9 from
hr_organization_information where organization_id = ppf.business_group_id
and org_information_context = 'Business Group Information') LEGISLATION_CODE,
EMPLOYEE_NUMBER,
APPLICANT_NUMBER,
NPW_NUMBER,
PERSON_TYPE_ID ,
to_char(DATE_OF_BIRTH,'YYYY-MM-DD'),
TOWN_OF_BIRTH,
COUNTRY_OF_BIRTH,
to_char(DATE_OF_DEATH,'YYYY-MM-DD'),
to_char(ORIGINAL_DATE_OF_HIRE,'YYYY-MM-DD'),
to_char(EFFECTIVE_START_DATE,'YYYY-MM-DD'),
to_char(EFFECTIVE_END_DATE,'YYYY-MM-DD'),
SEX,
FULL_NAME,
SUFFIX,
TITLE,
LAST_NAME,
FIRST_NAME,
MIDDLE_NAMES,
NATIONALITY,
NATIONAL_IDENTIFIER,
EMAIL_ADDRESS,
(select message_text from fnd_new_messages where message_name = 'HR_NATIONAL_ID_NUMBER_'|| (select to_char(org_information9) from
hr_organization_information where organization_id = ppf.business_group_id
and org_information_context = 'Business Group Information')
and language_code = USERENV('LANG') )NATIONAL_IDENTIFIER_LABEL
FROM PER_ALL_PEOPLE_F ppf
order by ppf.person_id,ppf.effective_start_date;
Select
Address_Type,
style,
To_Char(Date_From,'YYYY-MM-DD'),
To_Char(Date_To,'YYYY-MM-DD'),
Country,
Address_Line1,
Address_Line2,
Address_Line3,
Town_Or_City,
Telephone_Number_1,
Region_1,
Region_2,
Postal_Code,
Primary_Flag
FROM per_addresses
where person_id = p_person_id
and date_from between P_Eff_St_Dt and P_Eff_End_Dt
order by date_from;
Select
to_char(ppn.date_from,'YYYY-MM-DD'),
to_char(ppn.date_to,'YYYY-MM-DD'),
PHONE_TYPE,
PHONE_NUMBER
FROM per_phones ppn
where ppn.PARENT_ID (+) = P_PERSON_ID
AND PPN.PARENT_TABLE (+) = 'PER_ALL_PEOPLE_F'
AND DATE_FROM between P_Eff_St_Dt and P_Eff_End_Dt;