select
pa.address_line1, pa.address_line2, pa.address_line3, pa.town_or_city,pa.region_2 state, pa.region_1 county, pa.postal_code, T.TERRITORY_SHORT_NAME COUNTRY ,
pa.telephone_number_1, pa.telephone_number_2, pa.primary_flag,
pa.date_from, pa.date_to, TL.DESCRIPTIVE_FLEX_CONTEXT_NAME Address_STYLE
from
PER_ALL_PEOPLE_F PAPF,
per_addresses pa,
FND_TERRITORIES_TL T ,
FND_DESCR_FLEX_CONTEXTS_TL TL ,
HR_LOOKUPS HL1
where 1=1
AND PAPF.PERSON_ID = PA.PERSON_ID
and PA.COUNTRY = T.TERRITORY_CODE (+)
AND PA.STYLE = TL.DESCRIPTIVE_FLEX_CONTEXT_CODE
AND TL.APPLICATION_ID = 800
AND TL.DESCRIPTIVE_FLEXFIELD_NAME = 'Address Structure'
AND TL.LANGUAGE = userenv('LANG')
AND HL1.LOOKUP_TYPE(+) = 'ADDRESS_TYPE'
AND PA.ADDRESS_TYPE = HL1.LOOKUP_CODE (+)
AND T.LANGUAGE (+) = USERENV('LANG')
and papf.person_id = %Person_ID;
Hi Raj,
ReplyDeletecan you please help me to get query information about below details.
Terms All Salaried Employees Name
Employee ID
Term Date
Job Name
Supervisor
Pay Cycle Changes All Employees Name
Employee ID
Effective Date
Company
Leave Status Changes Salaried Employees Name
Employee ID
Effective Date of Change
LOA Status
New Hires Salaried Employees Name
Employee ID
DOH
SELECT p.FULL_NAME,p.employee_number Employee_Number,
Deletep.last_name Last_Name,
p.first_name First_Name,
p.original_date_of_hire Hire_date,
p.DATE_OF_BIRTH DOB,
p.SEX Gender,
p.EMAIL_ADDRESS EMAIL_Address,
(SELECT name
FROM apps.hr_all_organization_units
WHERE organization_id = p.business_group_id)
Organization,
(SELECT location_code
FROM apps.hr_locations_all_tl
WHERE location_id = a.location_id
AND language = USERENV ('LANG'))
Location_Name,
'A' Status_Flag,
(SELECT CONCATENATED_SEGMENTS
FROM apps.GL_CODE_COMBINATIONS_KFV
WHERE code_combination_id = a.DEFAULT_CODE_COMB_ID)
Expense_Account,
(SELECT papf1.full_name supervisor_name
FROM apps.per_all_people_f papf,
apps.per_all_assignments_f paaf,
apps.per_all_people_f papf1
WHERE papf.person_id = paaf.person_id
AND paaf.primary_flag = 'Y'
AND paaf.assignment_type = 'E'
AND paaf.supervisor_id = papf1.person_id
AND papf1.current_employee_flag = 'Y'
AND papf.business_group_id = paaf.business_group_id
AND SYSDATE BETWEEN papf.effective_start_date
AND papf.effective_end_date
AND SYSDATE BETWEEN paaf.effective_start_date
AND paaf.effective_end_date
AND SYSDATE BETWEEN papf1.effective_start_date
AND papf1.effective_end_date
AND papf.employee_number = p.employee_number
and papf.person_id = p.person_id)
Supervisor
FROM apps.per_all_people_f p,
apps.per_all_assignments_f a,
apps.pay_people_groups ppg,
apps.hr_all_positions_f hap
WHERE P.Person_Id = A.Person_Id
AND SYSDATE BETWEEN P.Effective_Start_Date AND P.Effective_End_Date
AND SYSDATE BETWEEN a.effective_start_date AND a.effective_end_date
AND a.people_group_id = ppg.people_group_id
AND hap.position_id(+) = a.position_id