Query to get Candidate Requisition and Offer details

SELECT IC.CANDIDATE_NUMBER,

IRB.REQUISITION_NUMBER,

PPNF.LAST_NAME,

PPNF.FIRST_NAME,

PHONE.AREA_CODE || PHONE.PHONE_NUMBER PHONENUMBER,

EMAIL.EMAIL_ADDRESS EMAIL,

PJFT.NAME JOB,

PPNFR.FULL_NAME "RECRUITER NAME",

IRB.CREATION_DATE,

IC.PERSON_ID,

ADDRESS_LINE_1 || ' ' || ADDRESS_LINE_2 "ADDRESS",

LOC.INTERNAL_LOCATION_CODE LOCATION,

IO.OFFER_ID,

(

SELECT PAPF.PERSON_NUMBER

FROM PER_ALL_ASSIGNMENTS_M PAAM,

PER_ALL_PEOPLE_F PAPF

WHERE PAAM.PERSON_ID = IC.PERSON_ID

AND SYSDATE BETWEEN PAAM.EFFECTIVE_START_DATE

AND PAAM.EFFECTIVE_END_DATE

AND PAAM.ASSIGNMENT_TYPE = 'E'

AND PAAM.PERSON_ID = PAPF.PERSON_ID

) EMPLOYEE_NUMBER

FROM IRC_OFFERS IO,

PER_PERSON_NAMES_F PPNF,

IRC_CANDIDATES IC,

PER_EMAIL_ADDRESSES EMAIL,

PER_PHONES PHONE,

PER_ADDRESSES_F ADDRESS,

IRC_REQUISITIONS_B IRB,

IRC_SUBMISSIONS IRS,

PER_PERSON_NAMES_F PPNFR,

PER_JOBS_F_TL PJFT,

IRC_STATES_TL IST,

PER_LOCATIONS LOC

WHERE IO.PERSON_ID = IC.PERSON_ID

AND IC.PERSON_ID = PPNF.PERSON_ID

AND PPNF.NAME_TYPE = 'GLOBAL'

AND TRUNC(SYSDATE) BETWEEN PPNF.EFFECTIVE_START_DATE

AND PPNF.EFFECTIVE_END_DATE

AND EMAIL.EMAIL_ADDRESS_ID(+) = IC.CAND_EMAIL_ID

AND PHONE.PHONE_ID(+) = IC.CAND_PHONE_ID

AND ADDRESS.ADDRESS_ID(+) = IC.CAND_ADDRESS_ID

AND LOC.LOCATION_ID(+) = IRB.LOCATION_ID

AND IRS.REQUISITION_ID = IRB.REQUISITION_ID

AND IO.SUBMISSION_ID = IRS.SUBMISSION_ID

AND IRB.RECRUITER_ID = PPNFR.PERSON_ID

AND PPNFR.NAME_TYPE = 'GLOBAL'

AND TRUNC(SYSDATE) BETWEEN PPNFR.EFFECTIVE_START_DATE

AND PPNFR.EFFECTIVE_END_DATE

AND PJFT.JOB_ID = IRB.JOB_ID

AND PJFT.SOURCE_LANG = USERENV('LANG')

AND SYSDATE BETWEEN PJFT.EFFECTIVE_START_DATE

AND PJFT.EFFECTIVE_END_DATE

AND IST.STATE_ID = IRS.CURRENT_STATE_ID

AND IST.SOURCE_LANG = USERENV('LANG')

AND IC.CANDIDATE_NUMBER = :P_CANDIDATE_NUM

ORDER BY IRB.REQUISITION_NUMBER,

IC.CANDIDATE_NUMBER DESC

Query to get data from HCM User-Defined tables

SELECT *

FROM (

SELECT R.ROW_LOW_RANGE_OR_NAME ROW_ID,

V.VALUE,

C_TL.USER_COLUMN_NAME

FROM FF_USER_TABLES T,

FF_USER_ROWS_F R,

FF_USER_COLUMNS C,

FF_USER_COLUMNS_TL C_TL,

FF_USER_COLUMN_INSTANCES_F V

WHERE T.BASE_USER_TABLE_NAME = :UDT_TABLE_NAME

AND T.USER_TABLE_ID = R.USER_TABLE_ID

AND C.USER_COLUMN_ID = C_TL.USER_COLUMN_ID

AND T.USER_TABLE_ID = C.USER_TABLE_ID

AND C.USER_COLUMN_ID = V.USER_COLUMN_ID

AND R.USER_ROW_ID = V.USER_ROW_ID

AND SYSDATE BETWEEN V.EFFECTIVE_START_DATE

AND V.EFFECTIVE_END_DATE

AND SYSDATE BETWEEN R.EFFECTIVE_START_DATE

AND R.EFFECTIVE_END_DATE

)

PIVOT(MAX(VALUE) FOR USER_COLUMN_NAME IN (

USER_COLUMN_NAME1,

USER_COLUMN_NAME2

))