Redwood Job Offer DFF Defaulting Hiring Manager Job Level

Summary:

Currently we have Configured DFF in Job Offer Descriptive Flexfield to default value based on SQL. It will populate Job Level of the Hiring Manager.

We then use this DFF value in Approval Configuration to route the approval.

Example: if Hiring Manager is in Job Level 13 & above and Action is Transfer → Auto Approved, otherwise route based on Management Hierarchy.

With move to Redwood DFF SQL defaulting is not supported, we are unable to achieve this defaulting using VBCS.

Anyone has found an alternate solution?

Code Snippet (add any code snippets that support your topic, if applicable):

"SELECTPJF.approval_authority as man_levelFROMper_all_assignments_m paam,per_jobs_f pjf,per_jobs_f_tl pjftWHERE1=1AND PAAM.Assignment_id = (SELECT MANAGER_ASSIGNMENT_ID FROM IRC_REQUISITIONS_B REQ, IRC_SUBMISSIONS SUBWHERE REQ.REQUISITION_ID = SUB.REQUISITION_IDAND SUB.SUBMISSION_ID = :{PARAMETER.SUBMISSION_ID})AND paam.primary_assignment_flag = 'Y'AND (paam.assignment_type = 'E' OR paam.assignment_type = 'C')and paam.effective_latest_change = 'Y'AND TRUNC(SYSDATE) BETWEEN paam.effective_start_date AND paam.effective_end_dateAND paam.job_id = pjf.job_idAND TRUNC(SYSDATE) BETWEEN pjf.effective_start_date AND pjf.effective_end_dateAND pjf.job_id = pjft.job_idAND pjft.language = 'US'AND TRUNC(SYSDATE) BETWEEN pjft.effective_start_date AND pjft.effective_end_date