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

11g to 12c OSB projects migration points

1. Export 11g OSB code and import in 12c Jdeveloper. Steps to import OSB project in Jdeveloper:   File⇾Import⇾Service Bus Resources⇾ Se...