Get Started with Redwood for Oracle Cloud HCM Begin Now
To ensure that questions get required attention from community members and are NOT left unanswered, it’s important for the author to indicate (by selecting “Yes” or “No” when prompted) whether the question was answered. (newly added) Please note that it is also important to respond to EACH comment your question receives. Your Yes or No response ensures an accurate status for your question.
For more information, please refer to this announcement explaining best practices for getting answers to questions.
For more information, please refer to this announcement explaining best practices for getting answers to questions.
New Hired employee details with Requisition Details (Req Number, Hiring Manager, Recruiter Name etc)
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 =
Tagged:
0