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
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,
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,
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,
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
