oraext:query-database() not able use in requisition BPM workflow
Summary:
We are trying to use below query in a new participant in BPM workflow , but the when we submit the requisition for approval it is getting auto rejected
oraext:query-database(concat("WITH CC_APPROVER AS (SELECT pu.username Approver FROM hz_ref_entities MRE ,per_users pu WHERE 1 = 1 AND UPPER(MRE.attribute_category) = upper('Procurement_Approval_Matrix_C') AND upper(pu.username) = upper(MRE.EXTN_ATTRIBUTE_CHAR011) AND MRE.EXTN_ATTRIBUTE_CHAR007 = 'US ABC' AND MRE.EXTN_ATTRIBUTE_NUMBER008 <= (SELECT SUM(prl.ASSESSABLE_VALUE) req_total FROM por_requisition_lines_all prl,por_requisition_headers_all prh WHERE 1=1 AND PRH.requisition_header_id = PRL.requisition_header_id AND prh.REQUISITION_HEADER_ID = ", /task:task/task:payload/ns1:findReqHeaderDimensionResponse/ns1:result/ns2:RequisitionHeaderId, " AND NVL(prl.LINE_STATUS, 'N') <> 'CANCELED' AND prl.ASSESSABLE_VALUE is not null GROUP BY prh.REQUISITION_HEADER_ID) ORDER BY MRE.EXTN_ATTRIBUTE_NUMBER006) SELECT zzz.approver from CC_APPROVER zzz"),false(),false(),"jdbc/ApplicationDBDS")