New Hired employee details with Requisition Details (Req Number, Hiring Manager, Recruiter Name etc)
Sql Query to get New Hired employee details with Requisition Details (Req Number, Hiring Manager, Recruiter Name etc)
Version (include the version you are using, if applicable):
Code Snippet (add any code snippets that support your topic, if applicable):
AND papf.person_id=ICC.PERSON_ID(+) AND IRB.REQUISITION_ID = ISS.REQUISITION_ID(+) AND papf.person_id= ISS.PERSON_ID(+) I tried above code but its not working.
select distinct papf.person_number as "Person No", ppnf.display_name as "Name", ( case when paam.assignment_type in ('E') then 'Employee' when paam.assignment_type in ('C') then 'Contingent Worker' else ' ' end ) as "Worker_Type", to_char(ppos.date_start, 'DD-Mon-YYYY','NLS_DATE_LANGUAGE = American') as hd, HAOU_BU.NAME AS bu, HAOU_DEP.NAME AS dep, PG.NAME AS "Grade", PJ.NAME AS "Job", PJF.ATTRIBUTE1 as cs, PJFFT.JOB_FAMILY_NAME as JBFAM, flv2.meaning as jobclass, FLV.MEANING as PLS, HLA.location_NAME AS "LOCATION_NAME", HLA1.location_NAME AS REQLOC, ICC.CANDIDATE_NUMBER, IRB.REQUISITION_NUMBER as REQUISITION_ID from per_all_people_f papf, per_person_names_f ppnf, per_all_assignments_m paam, --per_seniority_dates_f psdf, per_periods_of_service ppos, hr_all_organization_units HAOU_BU, hr_all_organization_units HAOU_DEP, PER_GRADES PG, PER_JOBS PJ, PER_JOB_FAMILY_F_TL PJFFT, PER_JOBS_F PJF, FND_LOOKUP_VALUES FLV2, FND_LOOKUP_VALUES FLV, HR_LOCATIONS_ALL HLA, HR_LOCATIONS_ALL HLA1, PER_PERSON_TYPES_VL PPT, IRC_REQUISITIONS_B IRB, IRC_SUBMISSIONS ISS, -- IRC_OFFERS IRO, IRC_CANDIDATES ICC --IRC_GEO_HIER_NODES_IDEN IGHNI, where papf.person_id = ppnf.person_id and ppnf.name_type IN ('GLOBAL') and paam.person_id =papf.person_id and paam.assignment_type in ('E','C') AND paam.action_code in ('HIRE','ADD_CWK_WORK_RELATION','ADD_CWK') AND PAAM.BUSINESS_UNIT_ID = HAOU_BU.ORGANIZATION_ID(+) AND PAAM.ORGANIZATION_ID = HAOU_DEP.ORGANIZATION_ID(+) AND PAAM.GRADE_ID = PG.GRADE_ID(+) AND PAAM.JOB_ID = PJ.JOB_ID(+) AND PJ.JOB_ID =