IRC_REC_EVENT_AUD_HIRED

Details

Schema: FUSION
Object owner: IRC
Object type: VIEW

Columns

Name
EVENT_POOL_MEMBER_ID PERSON_ID REQUISITION_ID SUBMISSION_ID CANDIDATE_NUMBER EVENT_ID EVENT_START_DATE SUBMISSIONS_CREATED_BY SUBMISSIONS_CREATED_DATE SUBMISSIONS_LAST_UPDATED_BY SUBMISSIONS_LAST_UPDATE_LOGIN

Name

EVENT_POOL_MEMBER_ID

PERSON_ID

REQUISITION_ID

SUBMISSION_ID

CANDIDATE_NUMBER

EVENT_ID

EVENT_START_DATE

SUBMISSIONS_CREATED_BY

SUBMISSIONS_CREATED_DATE

SUBMISSIONS_LAST_UPDATED_BY

SUBMISSIONS_LAST_UPDATE_LOGIN

Query

SQL_Statement
WITH tab_ne AS ( SELECT /+materialize/ subject_id,event_date FROM irc_lc_history h WHERE h.source_object_id = 4 AND h.state_id IN ( 1004, 1005 ) ) SELECT /+use_hash(b h)/ distinct e.event_pool_member_id, e.person_id, c.requisition_id, c.submission_id, cand.candidate_number, e.event_id, b.event_start_date, c.created_by submissions_created_by, c.creation_date submissions_created_date, c.last_updated_by submissions_last_updated_by, c.last_update_login submissions_last_update_login FROM irc_rec_events_b b, irc_rec_event_pool_members e, irc_submissions c, irc_lc_history h, irc_candidates cand WHERE b.event_id = e.event_id and c.person_id = cand.person_id AND c.submission_id = h.subject_id AND h.source_object_id = 4 AND h.state_id IN ( 1025, 1026, 1027, 1033, 1034 ) AND NOT EXISTS ( SELECT 1 FROM tab_ne h WHERE c.submission_id = h.subject_id AND h.event_date BETWEEN b.event_start_date AND b.event_end_date + coalesce(b.hired_metrics_days, 90) ) AND e.person_id = c.person_id AND e.object_status = 'ORA_ACTIVE' AND c.object_status = 'ORA_ACTIVE' AND h.event_date BETWEEN b.event_start_date AND b.event_end_date + coalesce(b.hired_metrics_days, 90)

SQL_Statement

WITH tab_ne AS (

SELECT /*+materialize*/

subject_id,event_date

FROM

irc_lc_history h

WHERE

h.source_object_id = 4

AND h.state_id IN ( 1004, 1005 )

)

SELECT /*+use_hash(b h)*/

distinct e.event_pool_member_id,

e.person_id,

c.requisition_id,

c.submission_id,

cand.candidate_number,

e.event_id,

b.event_start_date,

c.created_by submissions_created_by,

c.creation_date submissions_created_date,

c.last_updated_by submissions_last_updated_by,

c.last_update_login submissions_last_update_login

FROM

irc_rec_events_b b,

irc_rec_event_pool_members e,

irc_submissions c,

irc_lc_history h,

irc_candidates cand

WHERE

b.event_id = e.event_id

and c.person_id = cand.person_id

AND c.submission_id = h.subject_id

AND h.source_object_id = 4

AND h.state_id IN ( 1025, 1026, 1027, 1033, 1034 )

AND NOT EXISTS (

SELECT

1

FROM

tab_ne h

WHERE

c.submission_id = h.subject_id

AND h.event_date BETWEEN b.event_start_date AND b.event_end_date + coalesce(b.hired_metrics_days, 90)

)

AND e.person_id = c.person_id

AND e.object_status = 'ORA_ACTIVE'

AND c.object_status = 'ORA_ACTIVE'

AND h.event_date BETWEEN b.event_start_date AND b.event_end_date + coalesce(b.hired_metrics_days, 90)