Monday, November 20, 2023

ERP BI - Case study 1 - Create EDI template based BI report | Create Delimiter based report

Usecase: Here, we will create a BI report based on eText delimiter based rtf template or EDI template .

Delimiter template used:

https://docs.google.com/document/d/1Hvur9U0kmy9hCGw59OR_O8jePHNu2mkx/edit?usp=drivesdk&ouid=105651791254983245041&rtpof=true&sd=true


Implementation Steps:

Step1: Create a Data model based on a Sql query:

Navigation Menu >> Tools >> Reports and Analytics >> Browse Catalog >> Create >> Data Model >> Click New Data Set >> SQL Query >> provide the SQl query and Dataset name >> ok >> go to data tab and view the data >> save as sample data >> export the xml data to see the structure and content. >> save the data model.

Sql query used: 

Select INVOICE_ID, INVOICE_NUM,INVOICE_AMOUNT,TO_CHAR(Invoice_date,'DD-MON-RRRR') Invoice_date

FROM AP_INVOICES_ALL

WHERE rownum <101






Step2: Based on the Exported xml data structure, create EDI delimiter based rtf doc and upload it to create a report >> Save >> view report

Note: Use the attached EDI template google drive link and copy and modifiy as per your need. Save it as .rtf file.







Another example of Delimiter Based eText Report:

Step1: First create a Data model based on a SQL Query for instance, "Generate_invoice_id".

For steps follow - oic-how-to-create-bi-report-in-oracle

SQL Query used: select AP_INVOICES_S.nextval invoice_id from dual

type of SQL: standard

data source: ApplicationDB_FSCM

Name: apt name.

Step2: Create a RTF template file. To begin with, Copy trailing content and save it as Delimetr_eText.rtf.

Delimiter Format Setup:


Format Setup:

Hint: Define formatting options...



<TEMPLATE TYPE>

DELIMITER_BASED

<OUTPUT CHARACTER SET>

iso-8859-1

<CASE CONVERSION>

UPPER

<NEW RECORD CHARACTER>

Carriage Return


Hint: Format Data Records Table for DELIMITER_BASED

 



<LEVEL>

DATA_DS

<MAXIMUMLENGTH>

<FORMAT>

<DATA>

<COMMENTS>

<NEW RECORD>

InvoiceHeader

250

Alpha

'Invoice_id'

 





<LEVEL>

G_1

<MAXIMUMLENGTH>

<FORMAT>

<DATA>

<COMMENTS>

<NEW RECORD>

CLRDAta

250

Alpha

INVOICE_ID

 


<END LEVEL>

G_1


<END LEVEL>

DATA_DS


Step3: Create a BIP report with the above Data model (ref point 1) , upload Delimetr_eText.rtf  file with template type as eText.

Execute the Report. You might expect outcome as below:

invoice_id

12345





No comments:

Post a Comment

Featured Post

OIC - how can I use XSLT functions to remove leading zeros from numeric and alphanumeric fields?

To remove leading zeros from an numeric field in Oracle Integration Cloud (OIC) using XSLT, you can Use number() Function The number() funct...