Linking a requisition_id to a checklist task

Claire W

Candidate A has applied for Requisition A, Requisition B and Requisition C.

They have been offered the job for Requisition A and been unsuccessful/still going through the recruitment process on the others.

As part of the onboarding process, the candidate needs to complete a reference form, which is configured as a checklist task. We then have a BI publisher report that has a bursting job to email the references to the referees provided in the checklist task. 

When the reference emails are sent out, they are currently being sent in relation to Requisition A, B and C even though they should only be sent for the Requisition A.

The report query is using tables:

irc_requisitions_vl req

irc_candidates cand,

irc_submissions sub,

per_person_names_f ppnf_cand,

per_all_people_f papf_mgr,

per_person_names_f ppnf_mgr,

per_email_addresses pea_mgr,

per_phones pph_mgr,

per_allocated_checklists checklist,

per_tasks_in_checklist_b chklist_tsk_b,

per_tasks_in_checklist_tl chklist_tsk_tl,

per_allocated_tasks_vl task

which are linked as below

AND sub.person_id = cand.person_id

AND sub.requisition_id = req.requisition_id

AND ppnf_cand.person_id = cand.person_id

AND req.hiring_manager_id = papf_mgr.person_id

AND ppnf_mgr.person_id = papf_mgr.person_id

AND papf_mgr.primary_email_id = pea_mgr.email_address_id(+)

AND papf_mgr.primary_phone_id = pph_mgr.phone_id(+)

AND cand.person_id = checklist.person_id

AND checklist.checklist_id = chklist_tsk_b.checklist_id

AND chklist_tsk_b.task_in_checklist_id = chklist_tsk_tl.task_in_checklist_id

AND chklist_tsk_tl.task_in_checklist_id = task.task_in_checklist_id

AND task.performer_orig_sys_id = cand.person_id

However, we can't find any tables that would allow us to link the checklist task to the requisition_id which will be why the references are being generated for all requisitions the candidate has linked to.

Are there any tables that can be included in the query to restrict to the relevant requisition? 

Mallikarjuna Kuppauru-Oracle

Hi @User_APA6Q

Fusion Apps team can able to help on this question. Can you post the same in below link —

https://community.oracle.com/customerconnect

Regards,

Arjun