Using Default Task For Sending Purchase Order Rejected FYI to Requester and Approver
DocumentRejectedFYI
Hi Dear All, we are talking about an alternative way to send FYI instead of Bursting. Actually, this way is the default way to send FYI. Because the system can send FYI almost after every action with some arrangements.
The business side wanted the system to send Purchase Order Rejected FYI to Approver and Requester besides Buyer. After Approval rejected any purchase order, the system sends FYI mail to the Buyer automatically. Here we will use this feature of the system.
In the first step, we need to prepare a SQL query for finding Approvers and Requester from Purchase Order. For approver, we can use Purchase Order History table. Why? After the approver rejects any Order, the system writes data about all actions from participants. You shouldn’t forget. You don’t find any information about the Order that status is Pending Approval for the Order’s final sequence. The system waits for final action for writing to Database. Let’s check the Approver query!
Select LISTAGG(per.Username, ‘,’) WITHIN GROUP (ORDER BY per.username) as Username From PO_ACTION_HISTORY pohis
Inner Join PER_USERS per ON per.PERSON_ID = pohis.PERFORMER_ID
and ((per.END_DATE is null) or (Trunc(Sysdate) Between per.START_DATE AND per.END_DATE))Where pohis.CORRELATION_ID =
(Select * From
(Select pohisx.CORRELATION_ID From PO_ACTION_HISTORY pohisx Where pohisx.Object_Id = pohis.Object_Id and pohisx.ACTION_CODE = ‘REJECT’ Order By pohisx.SEQUENCE_NUM Desc) Where Rownum <= 1)
and pohis.ACTION_CODE = ‘APPROVE’
We will find approver usernames with this SQL query. We need to find a final sequence of Purchase Order. So, the approver can reject an order more than one time. Another important thing is that we list usernames side by side. Then we will paste a textbox with some additions.
Then, we will check the Requester SQL query below.
Select LISTAGG(per.Username, ‘,’) WITHIN GROUP (ORDER BY per.username) as Requester From POR_REQUISITION_LINES_ALL porreq
Inner Join PER_USERS per ON per.PERSON_ID = porreq.Requester_Idand ((per.END_DATE is null) or (Trunc(Sysdate) Between per.START_DATE AND per.END_DATE))
We listed again requester usernames. Because the order can have a Requester value of more than one.
We need to find the feature on the system. We are opening BPM Worklist page and run Administration > Task Configuration tab. So we are searching DocumentRejectedFYI task. Then you click one after the other like DocumentRejectedFYI task, Edit (Pencil Icon) button, Assignees, DocumentRejected.Submitter button. You will see the Assignees section bottom of the page. You click the plus button two times for Approver SQL and Requester SQL.
I said before. We need to add some phrases for recognizing by the system like PO_Header_ID filter, oracle:query-database keywords, extra apostrophe for string values.
Approvers Phrase should be like;
orcl:query-database(concat(‘Select LISTAGG(per.Username, ‘’,’’) WITHIN GROUP (ORDER BY per.username) as Username From PO_ACTION_HISTORY pohis Inner Join PER_USERS per ON per.PERSON_ID = pohis.PERFORMER_ID and ((per.END_DATE is null) or (Trunc(Sysdate) Between per.START_DATE AND per.END_DATE)) Where pohis.CORRELATION_ID = (Select * From (Select pohisx.CORRELATION_ID From PO_ACTION_HISTORY pohisx Where pohisx.Object_Id = pohis.Object_Id and pohisx.ACTION_CODE = ‘’REJECT’’ Order By pohisx.SEQUENCE_NUM Desc) Where Rownum <= 1) and pohis.ACTION_CODE = ‘’APPROVE’’ and pohis.Object_Id =’,/task:task/task:payload/task:DocumentId),true(),true(),’jdbc/ApplicationDBDS’)
Requester Phrase should be like;
orcl:query-database(concat(‘Select LISTAGG(per.Username, ‘’,’’) WITHIN GROUP (ORDER BY per.username) as Requester From POR_REQUISITION_LINES_ALL porreq Inner Join PER_USERS per ON per.PERSON_ID = porreq.Requester_Id and ((per.END_DATE is null) or (Trunc(Sysdate) Between per.START_DATE AND per.END_DATE)) Where porreq.PO_Header_Id =’,/task:task/task:payload/task:DocumentId),true(),true(),’jdbc/ApplicationDBDS’)
You should paste these phrases into the textboxes. Then submit your changes.
We didn’t finish yet. We need to do some arrangements inside SOA of course. You think it’s easy. Don’t you? But not :).
You should go SOA Composer with https://yourserver.oraclecloud.com/soa/composer and then export SOA Changes with the Export/Import button from the upright side on page
You should download .jar file then you need to open it with Winrar. Then you find default_PrcPoApprovalComposite_SomeNumbers.jar and open it again. You will see DocumentRejectedFyi.task. You can take it to Desktop from Winrar this file for editing.
Open with Visual Studio Code or Notepad++. We will change the STATIC words with XPATH for two of them. Save and Close.
Like this.
After the saving, you can throw it into the Winrar page. You should add the file same place in Winrar. Then close the page. Probably you will face an error about updating .jar file in Winrar and we will click Yes and pass this step. Almost done.
So we will Import .jar file with all changes to SOA with Import Button.
We need to click Import after you chose jar file that changed before. And follow up on the Status progress. When you see Successful. It’s Done. You did a great, complex job. After all, the system sends a FYI mail to Approvers and Requesters after Rejected. You should test of course.
Source;
https://cloudcustomerconnect.oracle.com/posts/296ed3d97a
Thanks for reading. We will meet again…
