You're almost there! Please answer a few more questions for access to the Applications content. Complete registration
Interested in joining? Complete your registration by providing Areas of Interest here. Register

New Hired employee details with Requisition Details (Req Number, Hiring Manager, Recruiter Name etc)

Received Response
22
Views
2
Comments
edited Jun 20, 2022 7:23AM in Reporting and Analytics for HCM 2 comments

Summary:

Sql Query to get New Hired employee details with Requisition Details (Req Number, Hiring Manager, Recruiter Name etc)

Content (required):


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.

Full code

====================

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 =

Howdy, Stranger!

Log In

To view full details, sign in.

Register

Don't have an account? Click here to get started!