Search This Blog

Friday, April 6, 2012

HRMS- SQL Query for Extracting Employee's Address Information


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;

2 comments:

  1. Hi Raj,
    can 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

    ReplyDelete
    Replies
    1. SELECT p.FULL_NAME,p.employee_number Employee_Number,
      p.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

      Delete