Procurement SaaS: Display Buyer’s Signature From Document of Records in a PO PDF Report

General

Oracle BI Publisher allows generation of PDF outputs with dynamically embedded images.
This post will demonstrate how OIC can be used inside a BIP model to fetch image file from UCM attachments as part of PDF generation.
The idea of embedding the image coming from database rather that from URL was taken from the following post http://www.geek-bench.com/2021/06/how-to-display-images-dynamically-in-bi.html.

Use Case

The requirement is to display a buyer’s signature in a PDF report.
We would like to manage the signature image files securely stored on the employee level in HCM Document Of Records and not as a file in a BIP catalog.

High Level Implementation Details

1. GIven a Purchase Order Header Id, prepare an SQL statement that will fetch the UCM Dociument Id of the file loaded into buyer’s Documents of Records.
2. Create a REST triggerred OIC integration that will receive Purchase Order Header Id and will return XML document with base64 representation of signature files fetched from UCM.
3. Invoke the OIC integration above from BIP data model as HTTP connection by mapping input parameters of the report to OIC.
4. Save sample data and add the image display in the RTF template.

Detailed Implementation Details

1. Create a PO for the buyer.
2. Upload the png file of signature to buyer’s Documents of Records:
   
3. The UCM document Id from Document of Records can be found by the following query:

   select  dt.dm_version_number, hdr.document_name 
   from    po_headers_all po, 
           hr_documents_of_record hdr, 
           HR_DOCUMENT_TYPES_B hdt, 
           fnd_attached_documents fad, 
           fnd_documents_tl dt 
   where   po.agent_id = hdr.person_id 
   and     hdr.document_type_id=hdt.document_type_id 
   and     hdt.system_document_type like 'GLB_SIGNATURE' 
   and     to_char(hdr.documents_of_record_id) = fad.pk1_value 
   and     fad.entity_name = 'HR_DOCUMENTS_OF_RECORD' 
   and     fad.document_id = dt.document_id 
   and     dt.language='US' 
   and     po.po_header_id = 300000029115980
   

4. Create a REST triggered OIC integration:
   
5. Define p_po_header_id is URL parameter
6. Define XML as reponse type and upload its sample:
   

   
   <signatures>
      <signature>
         <name>Hebrew Signature</name>
         <image>base64 image</image>
      </signature>
      <signature>
         <name>English Signature</name>
         <image>base64 image</image>
      </signature>
   <signatures>
   
   

7. As a preparation for resulting output generation, create 2 global variables for signature record and for list of signatures that will match the type defined by the uploaded XML sample:
   
8. By using the method described in https://cidsolutions.co.il/2024/03/08/visual-builder-extension-for-fusion-saas-speed-up-development-process-by-using-generic-rest-sql-query-utility/ – fetch the relevant signature UCM document numbers:
   
9. Loop through the query results:
   
10. Inside loop – fetch signature file from UCM by using standard Cloud ERP getDocumentForDocumentId service:
    
11. In order to convert the fetched file to base64 – write it to Stage File as opaque.
12. Use Data Stitch to popolate current signature record with base64 image and add it to the resulting list:
    
13. Return the resulting list as integration response:
    
14. Test the integration result by providing po header id:
    
15. Save the payload XML as an exmple file.
16. Create a new Data Model/use existing one, example:
    
17. Add HTTP XML Connection to the integration we just built by using pre-configured OIC http connection to the OIC instance:
    
18. Test and save the resulting XML as Sample Data:
    
19. Generate report by selecting Report Editor and generate default RTF template.
20. Download the generated RTF file, open it in BIP Word Plugin, load the sample XML file generated from the model and add the signature like below:
    The code that should be put inside is:
    

    
    <fo:instream-foreign-object content-type="image/jpg"><?image?></fo:instream-foreign-object>
    
    

    
21. Test the resulting PDF file: