Using a query in BPM approval
Summary:
We have created a custom stage in BPM and would like to use a query based on a DFF field at agreement header level.
Content (please ensure you mask any confidential information):
We have a DFF field created at agreement header level. It that holds the person ID of contract admin.
We want to get PO, BPA and CPA approved by the contract admin. For this, we have created a custom stage in BPM where Asignees based on is Names and Expression. Assignee is User and used this query
orcl:query-database( concat( "SELECT pu.username FROM po_headers_all pha, per_users pu WHERE pu.PERSON_ID = pha.attribute_number5 AND TYPE_LOOKUP_CODE in ('BLANKET','CONTRACT') AND PRC_BU_ID = 'XXXX' AND pha.po_header_Id = '", string(/task:task/task:payload/task:DocumentId), "'" ), true(), true(), "jdbc/ApplicationDBDS" )