Has anyone worked on approval rule where we can fetch approver for requisition based sql query
We have this query to fetch approver on requisition based on Cost center and amount and the below query is working fine but we have a requirement to update the query to exclude one BU, post the change the requisition approval is erroring out. Has anyone worked on any such requirement.
working query -
orcl:query-database(concat('select listagg(description,'','') within group(order by description) description from (select ffvv.description description from (select (NVL(sum(prla.unit_price*prla.quantity),0) + NVL(sum(prla.amount),0)) price,prha.requisition_header_id from por_requisition_headers_all prha,por_requisition_lines_all prla where prha.requisition_header_id=prla.requisition_header_id and prha.requisition_header_id=',/task:task/task:payload/ns4:findReqHeaderDimensionResponse/ns4:result/ns2:RequisitionHeaderId,' group by prha.requisition_header_id) req_details,por_req_distributions_all prda,por_requisition_lines_all prla,fnd_flex_value_sets ffvs,fnd_flex_values_vl ffvv,fnd_flex_value_sets ffvs1,fnd_flex_values_vl ffvv1,gl_code_combinations gcc where req_details.requisition_header_id=prla.requisition_header_id and prla.requisition_line_id=prda.requisition_line_id and ffvs.flex_value_set_name=''LYF_CC_FPA'' and ffvs.flex_value_set_id=ffvv.flex_value_set_id and ffvv.enabled_flag=''Y'' and gcc.code_combination_id=prda.code_combination_id and gcc.segment4=ffvv.flex_value and sysdate between nvl(ffvv.start_date_active,sysdate) and nvl(ffvv.end_date_active,sysdate) and req_details.price >= substr(ffvv1.description,1,instr(ffvv1.description,''-'',1)-1) and ffvs1.flex_value_set_name=''LYF_FPA_VS'' and ffvs1.flex_value_set_id=ffvv1.flex_value_set_id and ffvv1.enabled_flag=''Y'' and prla.source_type_code = ''EXTERNAL'' and sysdate between nvl(ffvv1.start_date_active,sysdate) and nvl(ffvv1.end_date_active,sysdate) and ffvv1.flex_value=prla.currency_code GROUP BY ffvv.description)'),true(),true(),'jdbc/ApplicationDBDS’)