Skip to main content

SQL Query for Extracting Employee in Oracle

SQL Query for Extracting Employee


 SELECT distinct Papf.Person_Id,
  Papf.First_Name,
  Papf.Last_Name,
  Papf.Sex,
  Papf.Effective_Start_Date,
  Papf.Effective_End_Date,
  Papf.Email_Address,
  Papf.Employee_Number,
  papf1.Full_Name Supervisor,
  Pa.Assignment_Id,
  Pa.Ass_Attribute1 "JOB NAME",
  Pa.Ass_Attribute2 "GRADE NAME",
  Papf.Attribute1 Role1,
  Papf.Attribute2 Role2,
  Papf.Attribute3 "HRBP CODE",
  Papf.Attribute4 "BENEFICIARY NAME",
  Papf.Attribute5 "BANK NAME",
  Papf.Attribute6 "BANK ACCOUNT NUMBER",
  Papf.Attribute7 "IFSC CODE",
  Papf.Attribute8 "BANK BRANCH NAME",
  Papf.Attribute9 "PAN", 
  Pjt.Name Job,
  Hla.Location_Code Location,
  Hou.Name Organization
FROM Hr.Per_All_Assignments_F Pa,
  Hr.Per_All_People_F papf,
  Hr.Per_Jobs_Tl Pjt,
  Hr.Hr_Locations_All Hla,
  Hr.Hr_All_Organization_Units Hou,
  hr.per_all_people_f papf1
WHERE Pa.Person_Id=Papf.Person_Id
AND Pa.Job_Id          =Pjt.Job_Id
AND Pa.Location_Id     =Hla.Location_Id
AND Hou.Organization_Id=Pa.Organization_Id
AND pa.Supervisor_Id   = papf1.person_id
AND trunc(sysdate) between papf1.effective_start_date and papf1.effective_end_Date;