In this blog, we will explore how to leverage Oracle Integration Cloud (OIC) to streamline the process of withdrawing long-pending purchase requisitions. With OIC’s powerful integration capabilities, you can automate and simplify this task, ensuring your procurement workflows remain efficient and hassle-free.
Join us as we guide you through the steps to effectively manage and withdraw those stalled requisitions using Oracle Integration Cloud.
PREREQUISITES:-
1) ERP INSTANCE having BI administrator role for creating BIP report.
2) OIC instance access with User having Service Developer role, Service Monitor, Service invoker role.
SOLUTION: This approach is divided into two parts
Part 1: Creating BIP report for fetching Data
Part 2: Creating an OIC integration for withdrawing Purchase requisitions.
PART 1: Create BIP report for fetching details of Purchase Requisitions which is Pending for approval for more than 90 days.
STEP 1: Sample BIP Query.
select prha.requisition_number,prha.requisition_header_id,
TO_CHAR(prha.CREATION_DATE,’DD-MON-RR’,’NLS_DATE_LANGUAGE=AMERICAN’) “creation date” ,
TO_CHAR(prha.last_update_date,’DD-MON-RR’,’NLS_DATE_LANGUAGE=AMERICAN’) “last update date” ,
prha.document_status
from por_requisition_headers_all prha,
por_requisition_lines_all prla
where prha.document_status=’PENDING APPROVAL’
and prha.requisition_header_id=prla.requisition_header_id
AND trunc(PRHA.creation_date+90)<trunc(sysdate)
order by prha.creation_date desc.
STEP 2: Save the Data Model and create the report.
Also Read – How To Apply Hold on Duplicate Invoices In Fusion Using Rest API In OIC?
For the steps to create BIP report you can follow the above blog and with the help of Part 1 you can create a Report.
PART 2:- Creating an OIC integration for withdrawing Purchase requisitions.
STEP 1: Select an App Driven Orchestration and click on create after adding NAME.
STEP: 2 Create REST trigger integration to Trigger Input by taking REST connection as trigger.
Provide Name to your endpoint Select the action as “GET”. Check the box to configure endpoint to receive response.
Select the payload format as JSON Sample and enter sample JSON by clicking on <<<inline>>> and then click on ok and then NEXT then DONE.
STEP:3 Select SOAP connection for invoking the BIP report.
STEP:4 Select the Operation as RunReport -> NEXT -> NEXT -> DONE.
STEP:5 Add reportabsolutepath, sizeofChunkData as “-1” and attributeformat “csv”.
Validate and close.
STEP:6 Write the data (change into base64decode) from BIP in temporary directory i.e. ‘Oic’ by using Stage file Action.
Provide the name of the call action, then click on NEXT and from expression builder, provide the valid name of the file and save.
Provide the format of file contents and click on NEXT.
Select OPAQUE SCHEMA FILE, click on NEXT and then DONE.
In the mapper, provide required mapping.
After writing the file, Read the file by selecting the file in csv format.
After writing the file, Read the file by selecting the file in csv format.
Select Stage file Operation as Read entire file -> configure file reference as Yes -> provide the reference of writefile response from expression builder.
Select file format sample i.e. “csv” -> NEXT.
Select the sample csv file (which has been downloaded from BI Publisher after creating report), provide the name of record and recordset keep, one field as mandatory and others as optional then click on NEXT-> DONE.
Iterate the element from read file response by using ‘For Each’ loop.
STEP :7 Before moving forward, create an error handler to handle any fault.
Select Scope from Collection.
Inside Error handler, select Rest API as invoke and for withdrawing purchase requisition.
STEP:8 Provide the end point name and action as “POST”
URI:-/fscmRestApi/resources/latest/purchaseRequisitions/{purchaseRequisitionsUniqID}
Click on all the checkboxes as shown below that you want to configure.
Here, the request parameter will be purchaseRequisitionUniqID.
Enter sample JSON request payload -> ok ->NEXT.
In Response payload, provide Sample response.
STEP:9 In the mapper, provide the value of content-type, name and map required details as shown below.
Additionally, we can add the response to the mapper of REST trigger.
Save the integration and test as shown below.
Integration will pick all the data from the report and will perform required Operation to withdraw the Purchase requisition.
If you have any questions or concern, please get in touch with us on [email protected].
