ERP BI - How to print Barcode in BI report

Usecase: 

Here, will convert Invoice number into a barcode in BI report using font mappings.

Detailed steps:

Create a RTF template based on data model xml file. >> select the invoice num element >> change font to Marlett

Go to tools >> Reports  & analytics >> browse catalog >> administration >> publisher >> Manage Publisher >> Runtime Configuration >> Font Mappings >> base font: Marlett >> target font type: barcode39 >> apply

Create the data model >> view >> save data as sample >> save >> create report >> choose use report editor >> finish >> upload the template

View a list >> selexct PDF as default format >> save >> view report.

ERP - Create Master details BI report using element link

Usecase: Create a master BI report using element link between two datamodels or groups.

We will create two data models supplier and supplier site and link them using common column vendor_id.

Detailed screenshots:

ERP BIP - Create Master details BI report using group Link

Usecase: 

Here, we will create a master details BI report using group link. We will link po header with po lines(one or more) using group link where the unique and common field is po_header_id to map in these 2 tables.

PO_HDR( query for open standard PO with suppliers):

SELECT POH.po_header_id PO_HDR_ID

,POH.segment1  PO_NUMBER

,POH.document_status PO_ STATUS

,POH.type_lookup_code PO_TYPE

,PS.segment1 SUPPLIER_NUMBER

,HP.party_name SUPPLIER_NAME

FROM po_headers_all POH

, poz_suppliers PS

,hz_parties HP

WHERE POH.vendor_id= PS.vendor_id

AND PS.party_id= HP.party_id

AND POH.type_lookup_code= 'STANDARD'

AND POH.document_status='OPEN'

AND PS.segment1= 1255

PO_LINES(Query to find PO Lines against a PO Header.

SELECT POL.po_header_id PO_HEADER_ID

,POL.po_line_id PO_LINE_ID

,POL.line_num PO_LINE_NUM

,POL.line_status PO_LINE_STATUS

,POL.item_description PO_ITEM

,POL.uom_code ITEM_UOM

,POL.unit_price ITEM_UNIT_PRICE

,POL.quantity ITEM_QUANTITY

FROM po_lines_all POL

WHERE POL.po_header_id = :po_hdr_id

Detailed screenshots:

Create data model

Create data sets for po_header and po_lines.

Create group link

Data view and save as sample date

Create report

VBCS - Create a Business object relationship

 Usecase: Create a business object relationship

Open one business object >> overview >> Relationship >> select right side employee table >> choose the relationship as needed here one to many. >> create relationship.

Notepad ++ - How to Find special characters in data in notepad++

Why:

Sometimes integration fails for special characters in data which we can't see and its like a double space in data. 

If you are cutting and pasting from a text file, such as an XML file, you can find non-ASCII characters as

Follow below steps to find the special char:

1. Open the file in Notepad++
2. Click the ‘Search’ menu.
3. Click ‘Find Characters in Range” (the last item in the list)
4. Make sure “Non-ASCII Characters (128-255)” is selected (I think it is the default).
5. Click ‘Find’.
6. It will highlight the row where the special character is.
7. Click ‘Close’ and the cursor will be place immediately after the special character if one is found. (cursor will return to where the search began if no special characters are found).
8. If you hit ‘Backspace’ immediately after the search, a special character will expand into its character codes. Delete them and replace with the desired character.

OIC ERP - How to extract Incremental data from ERP using BIP report in Oracle integration

Method 1:  Use a custom sql query in Data model to include last update date which will be passed as parameter from OIC. In OIC, we will use schedule parameter to persist the last update date or last run date to get the latest incremental data.

For example, the following custom SQL will create a custom data model for Suppliers.  Add the parameter LAST_RUN_DATE when is prompted and set the Date Format String to MM-dd-yyyy HH:mm:ss.

SELECT
    Supplier.VENDOR_ID AS Supplier_Id,
    Party.PARTY_NAME AS Supplier_Name,
    Party.PARTY_NUMBER AS Supplier_Number,
    Party.DUNS_NUMBER_C AS Duns_Number,
    Supplier.VENDOR_TYPE_LOOKUP_CODE AS Supplier_Vendor_Type,
    Party.PARTY_TYPE AS Supplier_Type,
    Supplier.LAST_UPDATE_DATE AS Last_Update_Date
FROM 
    POZ_SUPPLIERS Supplier LEFT OUTER JOIN 
    HZ_PARTIES Party ON Supplier.PARTY_ID = Party.PARTY_ID
WHERE 
    Supplier.LAST_UPDATE_DATE >= NVL(:LAST_RUN_DATE, Supplier.LAST_UPDATE_DATE)

From OIC:

We can also include start and end date to pull the incremental data as below query:

SELECT
    Supplier.VENDOR_ID AS Supplier_Id,
    Party.PARTY_NAME AS Supplier_Name,
    Party.PARTY_NUMBER AS Supplier_Number,
    Party.DUNS_NUMBER_C AS Duns_Number,
    Supplier.VENDOR_TYPE_LOOKUP_CODE AS Supplier_Vendor_Type,
    Party.PARTY_TYPE AS Supplier_Type,
    Supplier.LAST_UPDATE_DATE AS Last_Update_Date
FROM 
    POZ_SUPPLIERS Supplier LEFT OUTER JOIN 
    HZ_PARTIES Party ON Supplier.PARTY_ID = Party.PARTY_ID
WHERE 
    Supplier.CREATION_DATE >= NVL(:START_DATE, Supplier.CREATION_DATE) AND Supplier.CREATION_DATE < NVL(:END_DATE, Supplier.CREATION_DATE)

Method 2 : using Modified sql query and using ESS job respective ess_request_history table "processstart" time as last run time.

Please follow my below blog.

https://soalicious.blogspot.com/2021/05/oic-ess-job-run-for-delta-calculation.html

Reference:

https://blogs.perficient.com/2017/02/28/using-custom-sql-bip-to-extract-erp-cloud-data-from-otbi/

OIC - How to embed image in email body | How to send image in email notification body in oracle integration

Usecase: 

We get a requirement to embed or show an image to email body. Here, we will do the same using html code.

Solution:

We will use following html code in notification body and "img" tag to embed or show image base64 data to email body.

Html code used:

<html>

<head>

</head>

<body>

<p align="right">

<img src="data:image/jpg;base64,{P_Image}" alt=" Cat image" width="100" height="100"/>

</p>

</body>

</html>

Implementation steps:

1. Create a rest trigger connection.
2. Take app driven orchestration and configure rest trigger to get image/jpeg file as binary 
3. Take a assign and decode the stream ref to base64 file. ( encodeReferenceToBase64(streamReference)
4. Take a notification action and create a parameter P_Image to assign the $decodedStreamToBase64 data and use the html code to show image.
5. Activate and test

Detailed steps with screenshots:

Integration flow:

Configure rest trigger

Assign image stream ref to base64

Put the html code in notification body

Test:

Reference:

https://stackoverflow.com/questions/6706891/embedding-inage-in-html-email