Categories
Sql Query to fetch details of offer creation in Oracle Recruiting Cloud
Hi Team,
I am currently encountering an issue with an SQL query. It appears that the query is returning multiple assignment numbers for candidates who have Moved to HR in the ORC system. However, it is not capturing candidates who have moved to the offer creation stage but do not have an assignment number, particularly those who have not yet joined. The same issue I'm having with fetching person_number as well.
Could anyone provide guidance or suggestions on how to modify the query to ensure that all relevant candidates are included, regardless of their assignment status.
Query
with assignment as
(Select distinct
IC.person_id id,
paam1.Assignment_number,
paam1.ASSIGNMENT_STATUS_TYPE
from
per_all_assignments_m paam1,
per_all_people_f papf1,
IRC_CANDIDATES IC
where 1=1
AND PAAM1.PERSON_ID = PAPF1.PERSON_ID
AND IC.PERSON_ID = PAAM1.PERSON_ID
AND TRUNC(SYSDATE) BETWEEN TRUNC(paam1.EFFECTIVE_START_DATE) AND TRUNC(paam1.EFFECTIVE_END_DATE)
AND TRUNC(SYSDATE) BETWEEN TRUNC(papf1.EFFECTIVE_START_DATE) AND TRUNC(papf1.EFFECTIVE_END_DATE)
and paam1.ASSIGNMENT_TYPE IN ('E', 'P'))
SELECT DISTINCT
offers.ASSIGNMENT_OFFER_ID,
SUB.PERSON_ID,
REQ.REQUISITION_NUMBER,
CAND.CANDIDATE_NUMBER,
PER.DISPLAY_NAME,
SUB.CONFIRMED_FLAG ,
SUB.INTERNAL_FLAG,
SUB.SYSTEM_PERSON_TYPE,
SUB.OBJECT_STATUS,
SUB.CREATED_BY,
SUB.ADDED_BY_CONTEXT_CODE,
--ASG.ASSIGNMENT_NUMBER,
TO_CHAR(SUB.SUBMISSION_DATE,'DD/MM/YYYY') SUBMISSION_DATE,
TO_CHAR(Offers.CREATION_DATE,'DD/MM/YYYY') CREATION_DATE,
TO_CHAR(ASG.PROJECTED_START_DATE,'DD/MM/YYYY') OFFER_DOJ,
TO_CHAR(PPOS.DATE_START, 'DD-MM-YYYY') COREHR_DOJ,
--ASG.ASSIGNMENT_TYPE,
OFFERS.OFFER_NAME,
div.division,
ipv.name as phase,
isv.name as status,
GRADE.NAME GRADE,
(case PLID.Sex
when 'F' then 'Female'
when 'M' then 'Male'
else '' end)Gender,
Offers.ACCEPTED_ON_BEHALF,
to_char(Offers.EXTENDED_DATE,'dd/mm/yyyy') EXTENDED_DATE,
to_char(Offers.WITHDRAWN_REJECTED_DATE,'dd/mm/yyyy') WITHDRAWN_REJECTED_DATE,
to_char(Offers.ACCEPTED_DATE,'dd/mm/yyyy') ACCEPTED_DATE,
to_char(Offers.APPROVED_DATE,'dd/mm/yyyy') APPROVED_DATE,
to_char(Offers.DRAFTED_DATE,'dd/mm/yyyy') DRAFTED_DATE,
Offers.ATTRIBUTE_NUMBER1 Employers_contribution_ESI,
Offers.ATTRIBUTE_NUMBER2 Basic,
Offers.ATTRIBUTE_NUMBER3 HRA,
Offers.ATTRIBUTE_NUMBER4 Special_Allowance,
Offers.ATTRIBUTE_NUMBER5 Gross_Salary,
Offers.ATTRIBUTE_NUMBER6 Employers_Contribution_PF,
Offers.ATTRIBUTE_NUMBER7 Transport_Allowance,
Offers.ATTRIBUTE_NUMBER8 Flexi_Benefit,
Offers.ATTRIBUTE_NUMBER9 Flexi_Benefit_Total,
Offers.ATTRIBUTE_NUMBER10 Total_Fixed_Compensation_CTC,
Offers.ATTRIBUTE_NUMBER11 Target_Variable_Compensation,
Offers.ATTRIBUTE_NUMBER12 On_Target_compensation,
Offers.ATTRIBUTE_CHAR2 Seat_Cabin,
Offers.ATTRIBUTE_CHAR3 ID_card,
Offers.ATTRIBUTE_CHAR4 Business_Card,
Offers.ATTRIBUTE_CHAR5 Desk_Laptop_MacBook,
Offers.ATTRIBUTE_CHAR6 Types_operating_system,
Offers.ATTRIBUTE_CHAR8 Candidate_Category,
Offers.ATTRIBUTE_CHAR10 Previous_Employment_CTC,
Offers.ATTRIBUTE_CHAR13 Statutory_Bonus,
Offers.ATTRIBUTE_NUMBER7 Business_Incentive,
Offers.LAST_UPDATED_BY,
to_char(Offers.LAST_UPDATE_DATE,'dd/mm/yyyy') LAST_UPDATE_DATE,
Offers.OFFER_LETTER_CUSTOMIZED_FLAG,
Offers.MOVE_TO_HR_STATUS,
to_char(Offers.MOVE_TO_HR_DATE,'dd/mm/yyyy') MOVE_TO_HR_DATE,
Offers.OFFER_MOVE_STATUS,
to_char(Offers.EXTEND_SCHEDULE_DATE,'dd/mm/yyyy') EXTEND_SCHEDULE_DATE,
to_char(Offers.EXTEND_UPDATE_DATE,'dd/mm/yyyy') EXTEND_UPDATE_DATE,
Offers.EXTEND_REQUEST_ID,
haot.name as Business,
ASG.ASS_ATTRIBUTE5 Fuction,
ASG.ASS_ATTRIBUTE6 Sub_Family,
ASG.ASS_ATTRIBUTE3 Billable_Non_Billable,
ASG.ASS_ATTRIBUTE14 Cost_center,
ASG.ASS_ATTRIBUTE25 Source_of_Hire,
ASG.ASS_ATTRIBUTE26 Source_Type,
ASG.ASS_ATTRIBUTE27 Refer_Emp_ID,
ASG.ASS_ATTRIBUTE1 TVC_Applicable,
ASG.ASS_ATTRIBUTE2 Business_Incentive_Applicable,
ASG.ASS_ATTRIBUTE7 ROLE,
Source.SOURCE_URL_VALUE SOURCE_NAME,
Source.SOURCE_LEVEL,
dep.name as JOB_FAMILY_NAME,
(Case
when isv.name = 'To be Reviewed' then 'Candidate Reviewed for Interview'
when isv.name = 'Shared with Hiring Manager' then 'Candidate Reviewed for Interview'
when isv.name = 'Rejected by Employer' then 'Candidate Rejected'
when isv.name = 'Withdrawn by Candidate' then 'Candidate Withdrawn'
when isv.name = 'Candidate on hold' then 'Candidate on hold'
when isv.name = 'Vertical Interview Panel Scheduled' then 'Vertical interview Scheduled'
when isv.name = 'Feedback Completed for discussion with Candidate' then 'completing feedback for candidate discussion'
when isv.name = 'Discussion with Candidate to be Scheduled' then 'Candidate discussion yet to be scheduled'
when isv.name = 'Discussion with Candidate Scheduled' then 'Candidate discussion Scheduled'
when isv.name = 'Selected for Offer - Experienced' then 'Candidate selected for offer'
when isv.name = 'Feedback Completed for Vertical Head Interview Panel' then 'Completed feedback for vertical head interview'
when isv.name = 'Selected for Offer - Fresher' then 'Candidate selected for offer'
when isv.name = 'Discussion with the Hiring Manager about the Salary Proposed' then 'Salary discussion with the Hiring Manager'
when isv.name = 'Proposed CTC details sent to the Candidate' then 'Salary discussion with the Hiring Manager'
when isv.name = 'Approved' then 'Offer Approved for sending to the candidate'
when isv.name = 'To be Created' then 'Offer yet to create'
when isv.name = 'Accepted' then 'Offer Accepted by the Candidate'
when isv.name = 'Approval Rejected' then 'Offer approval Rejected'
when isv.name = 'Extended' then 'Offer Sent to the Candidate'
when isv.name = 'Draft' then 'Offer created and ready to send for the approval'
when isv.name = 'Pending Approval' then 'Offer Yet to be Approved'
when isv.name = 'Processed' then 'Employee Joined'
when isv.name = 'Pending Manual Processing' then 'Employee yet to Join'
when isv.name = 'Error During Processing' then 'Employee yet to Join'
when isv.name = 'Processing in Progress' then 'Employee Yet to Join'end
) Candidate_Hiring_Status,
EML.EMAIL_ADDRESS,
PH.PHONE_NUMBER,
PLDX.LOCATION_NAME,
PLDX.GEO_HIERARCHY_NODE_VALUE State,
PLDX.ATTRIBUTE1 Region,
FTL.TERRITORY_SHORT_NAME AS COUNTRY,
REQ.ATTRIBUTE_CHAR8 Replacement_EMP_ID,REQ.ATTRIBUTE_CHAR4 IC_PM,
REQ.ATTRIBUTE_CHAR7 New_Position_Replacement,
Recruiter_name.DISPLAY_NAME as RECRUITER_NAME,
Recruiter_id.PERSON_NUMBER AS RECRUITER_NUMBER,
Manger_Name.DISPLAY_NAME as MANAGER_NAME,
Manger_ID.PERSON_NUMBER AS MANAGER_NUMBER,
assignmentsemp.Assignment_number as emp_assign,
assignmentsemp.ASSIGNMENT_STATUS_TYPE
--EMPLOYEE_ASSIGN.Assignment_number as emp_assign
FROM
FUSION.IRC_REQUISITIONS_B REQ,
FUSION.IRC_CANDIDATES CAND,
FUSION.IRC_SUBMISSIONS SUB,
FUSION.PER_PERSON_NAMES_F PER,
PER_ALL_ASSIGNMENTS_M ASG,
PER_PERIODS_OF_SERVICE PPOS,
IRC_OFFERS Offers,
per_all_people_f papf,
PER_DEPARTMENTS Dep,
PER_LOCATION_DETAILS_X PLDX,
PER_LOCATIONS PL,
FND_TERRITORIES_TL FTL,
PER_PERSON_NAMES_F_V Recruiter_name,
PER_ALL_PEOPLE_F Recruiter_id,
PER_PERSON_NAMES_F_V Manger_Name,
PER_ALL_PEOPLE_F Manger_ID,
assignment assignmentsemp,
/*
(Select distinct
paam1.ASSIGNMENT_ID id,
paam1.Assignment_number
from
per_all_assignments_m paam1,
per_all_people_f papf1
where 1=1
AND PAAM1.PERSON_ID(+) = PAPF1.PERSON_ID
AND TRUNC(SYSDATE) BETWEEN TRUNC(paam1.EFFECTIVE_START_DATE) AND TRUNC(paam1.EFFECTIVE_END_DATE)
AND TRUNC(SYSDATE) BETWEEN TRUNC(papf1.EFFECTIVE_START_DATE) AND TRUNC(papf1.EFFECTIVE_END_DATE)
and paam1.ASSIGNMENT_TYPE IN ('E', 'P')
and paam1.ASSIGNMENT_STATUS_TYPE IN ('INACTIVE', 'ACTIVE')
AND ROWNUM = 1
) EMPLOYEE_ASSIGN,
/
/ (Select distinct
paam1.Assignment_number
from
per_all_assignments_m paam1
where 1=1
AND PAAM1.PERSON_ID(+) = PAPF.PERSON_ID
AND TRUNC(SYSDATE) BETWEEN TRUNC(paam1.EFFECTIVE_START_DATE) AND TRUNC(paam1.EFFECTIVE_END_DATE)
and paam1.ASSIGNMENT_TYPE IN ('P')
and paam1.ASSIGNMENT_STATUS_TYPE IN ('INACTIVE', 'ACTIVE')
AND ROWNUM = 1
) PENDING_WORKER, */
(
SELECT DISTINCT
houft.name AS bu,
houft1.name AS division,
potn.parent_pk1_value,
houft.organization_id
FROM
hr_organization_units_f_tl houft,
hr_organization_units_f_tl houft1,
hr_org_unit_classifications_f houcf,
per_org_tree_node potn,
per_all_assignments_m asg
WHERE
houft.organization_id = potn.pk1_start_value(+)
AND houcf.organization_id = houft.organization_id
AND houcf.classification_code = 'FUN_BUSINESS_UNIT'
AND asg.business_unit_id = houft.organization_id
AND potn.parent_pk1_value = houft1.organization_id(+)
) div,
HR_ALL_ORGANIZATION_UNITS_F hao,
HR_ORGANIZATION_UNITS_F_TL haot,
irc_phases_vl ipv,
irc_states_vl isv,
PER_GRADES_F_VL GRADE,
PER_PEOPLE_LEGISLATIVE_F PLID,
(select distinct
ist.PROSPECT_ID,
ist.RECRUITER_ID,
ist.SUBMISSION_ID,
ist.CANDIDATE_NUMBER,
ist.SOURCE_TRACKING_ID,
idd.SOURCE_URL_VALUE,
idd.LAST_UPDATE_DATE,
idd.LAST_UPDATED_BY,
idd.dimension_id,
flv.lookup_type,
ist.SOURCE_LEVEL,
flv.meaning source_medium
from
irc_source_tracking ist,
irc_dimension_def_b idd,
fnd_lookup_values_vl flv
where 1=1
and ist.dimension_id = idd.dimension_id(+)
and idd.source_medium = flv.lookup_code(+)
and lookup_type='ORA_IRC_SOURCE_TRACKING_MEDIUM'
)Source,
PER_EMAIL_ADDRESSES EML,
PER_PHONES PH
WHERE 1=1
and Offers.SUBMISSION_ID =SUB.SUBMISSION_ID
AND REQ.REQUISITION_ID = SUB.REQUISITION_ID
AND SUB.PERSON_ID = CAND.PERSON_ID
AND CAND.PERSON_ID = PER.PERSON_ID
and papf.person_id = ASG.PERSON_ID
and Offers.ASSIGNMENT_OFFER_ID=ASG.ASSIGNMENT_ID
AND PPOS.PERIOD_OF_SERVICE_ID = ASG.PERIOD_OF_SERVICE_ID
AND dep.ORGANIZATION_ID = ASG.ORGANIZATION_ID
AND PLDX.LOCATION_ID=ASG.LOCATION_ID
AND PL.LOCATION_ID=ASG.LOCATION_ID
and PL.COUNTRY = FTL.TERRITORY_CODE(+)
and Offers.RECRUITER_ID=Recruiter_id.PERSON_ID
and Offers.RECRUITER_ID=Recruiter_name.PERSON_ID
and Offers.HIRING_MANAGER_ID=Manger_Name.PERSON_ID
and Offers.HIRING_MANAGER_ID=Manger_ID.PERSON_ID
and assignmentsemp.id(+) = Offers.PERSON_ID
AND hao.ORGANIZATION_ID = ASG.business_unit_id
AND div.organization_id = haot.organization_id
AND hao.ORGANIZATION_ID = haot.ORGANIZATION_ID
AND ipv.PHASE_ID (+) = SUB.CURRENT_PHASE_ID
AND isv.STATE_ID (+) = SUB.CURRENT_STATE_ID
and GRADE.GRADE_ID = ASG.GRADE_ID
and PLID.PERSON_ID=ASG.PERSON_ID
and SUB.SUBMISSION_ID = Source.SUBMISSION_ID
AND CAND.CAND_EMAIL_ID = EML.EMAIL_ADDRESS_ID(+)
AND CAND.CAND_PHONE_ID = PH.PHONE_ID(+)
AND TRUNC(SYSDATE) BETWEEN TRUNC(hao.EFFECTIVE_START_DATE) AND TRUNC(hao.EFFECTIVE_END_DATE)
AND TRUNC(SYSDATE) BETWEEN TRUNC(GRADE.EFFECTIVE_START_DATE) AND TRUNC(GRADE.EFFECTIVE_END_DATE)
AND TRUNC(SYSDATE) BETWEEN TRUNC(haot.EFFECTIVE_START_DATE) AND TRUNC(haot.EFFECTIVE_END_DATE)
AND TRUNC(SYSDATE) BETWEEN TRUNC(ASG.EFFECTIVE_START_DATE) AND TRUNC(ASG.EFFECTIVE_END_DATE)
AND TRUNC(SYSDATE) BETWEEN TRUNC(PER.EFFECTIVE_START_DATE) AND TRUNC(PER.EFFECTIVE_END_DATE)
AND TRUNC(SYSDATE) BETWEEN TRUNC(papf.EFFECTIVE_START_DATE) AND TRUNC(papf.EFFECTIVE_END_DATE)
AND TRUNC(SYSDATE) BETWEEN TRUNC(PLID.EFFECTIVE_START_DATE) AND TRUNC(PLID.EFFECTIVE_END_DATE)
AND TRUNC(SYSDATE) BETWEEN TRUNC(Recruiter_name.EFFECTIVE_START_DATE) AND TRUNC(Recruiter_name.EFFECTIVE_END_DATE)
AND TRUNC(SYSDATE) BETWEEN TRUNC(Recruiter_id.EFFECTIVE_START_DATE) AND TRUNC(Recruiter_id.EFFECTIVE_END_DATE)
AND TRUNC(SYSDATE) BETWEEN TRUNC(Manger_Name.EFFECTIVE_START_DATE) AND TRUNC(Manger_Name.EFFECTIVE_END_DATE)
AND TRUNC(SYSDATE) BETWEEN TRUNC(Manger_ID.EFFECTIVE_START_DATE) AND TRUNC(Manger_ID.EFFECTIVE_END_DATE)
AND UPPER(PER.NAME_TYPE) <> UPPER('GLOBAL')
AND CAND.CANDIDATE_NUMBER = '6367788'
--AND CAND.CANDIDATE_NUMBER = '6378058'
--and TRUNC(Offers.CREATION_DATE) BETWEEN :P_START_DATE AND :P_END_DATE
--AND CAND.CANDIDATE_NUMBER = '6367788'
Order by CANDIDATE_NUMBER,TO_DATE(CREATION_DATE ,'dd/mm/yyyy') ASC
thanks
Girish Kushibi
Best Answer
-
The tables in the SQL query are not FDI tables. You may have to post this in Fusion community channel.
You can post your question here - https://community.oracle.com/customerconnect/categories/hcm-announcements
1
