ERP BI - Create Master details BI report using subgroup

 

ERP BI - Case study 3 - Create RTF template for multiple groups or master details report.

Case Study:

1. Show PO header information in FORM layout and PO Lines in Table Layout.
2. Provide a report name
3. Add header to insert company logo on the left side and date timestamps using extended XSL function on the right. <?xdoxslt:sysdate('DD-MON-YYYY HH24:MI')?>
4. Always show a PO from a new page <?split-by-page-break:?>
5. Decode the PO Status to a user friendly status using extended SQL function <?xdofx:decode(PO_STATUS, 'OPEN', 'Open PO', 'CLOSED', 'Closed PO', 'Others')?>
6. Calculate Line Amount <?ITEM_UNIT_PRICE * ITEM_QUANTITY?>
7. Calculate the PO Amount (Sum of all line amount)

<?xdoxslt:set_variable($_XDOCTX,'TotLineAmt',0)?>

<?xdoxslt:set_variable($_XDOCTX,'LineAmt',ITEM_UNIT_PRICE * ITEM_QUANTITY)?>

<?xdoxslt:set_variable($_XDOCTX,'TotLineAmt',

xdoxslt:get_variable($_XDOCTX,'TotLineAmt')+ xdoxslt:get_variable($_XDOCTX,'LineAmt'))?>

2 & 3 can be combined into one statement

<?xdoxslt:set_variable($_XDOCTX,'TotLineAmt',

xdoxslt:get_variable($_XDOCTX,'TotLineAmt')+ ITEM_UNIT_PRICE * ITEM_QUANTITY)?>

<?xdoxslt:get_variable($_XDOCTX,'TotLineAmt')?>

  8. Show Total Line Amount or PO Amount un words

<?xdoxslt:toCheckNumbers($_XDOLOCALE,xdoxslt:get_variable($_XDOCTX,'TotLineAmt'), 'USD')?>

Extended XSL and SQL functions support in RTF templates:

https://docs.oracle.com/cd/E10091_01/doc/bip.1013/b40017/T421739T481158.htm

Detailed Screenshots:

Show PO header information in FORM layout and PO Lines in Table Layout.

Provide a report name

Add header to insert company logo on the left side and date timestamps using extended XSL function on the right. <?xdoxslt:sysdate('DD-MON-YYYY HH24:MI')?>

Always show a PO from a new page <?split-by-page-break:?>

Decode the PO Status to a user friendly status using extended SQL function <?xdofx:decode(PO_STATUS, 'OPEN', 'Open PO', 'CLOSED', 'Closed PO', 'Others')?>

Calculate Line Amount:

Calculate the PO Amount (Sum of all line amount)

Show Total Line Amount or PO Amount un words

ERP BI - Case Study 2 - Create Fixed position based BI report

Case Study: 

we will create a fixed position based eText template(RTF) to generate BI report. The columns will be as below:

1. Start with hardcoded value "NEW"   which is of 3 chars.
2. Next a whitespace.
3. Next vendor number , size 15 chars,  if less size, will be padded with whitespace.
4. Next invoice Number, size 30 chars, all in caps, if less size , will be padded with whitespace
5. Next invoice date , in YYYYMMDD, size 8 chars.
6. Next a whitespace
7. Next invoice amount , size 15 chars, if less size, will be padded "0" before the original digits.
8. Next a whitespace
9. Next Invoice Currency code, size 3 chars.

SQL Query:

SELECT PS.segment1 VENDOR_NUM

,AVA.invoice_num INVOICE_NUM

,AVA.invoice_date INVOICE_DATE

,AVA.invoice_amount INVOICE_AMOUNT

,AVA.invoice_currency_code INVOICE_CURRENCY_CODE

FROM ap_invoices_all AVA

,poz_suppliers PS

WHERE AVA.vendor_id = PS.vendor_id

EFT template or layout:

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

Detailed screenshots:

Or

Notes:

1. All SQL functions can be applied in data like TO_CHAR, TO_NUMBER, MAX, MIN, NVL, DECODE, UPPER, LOWER, COUNT, CELL, ROUND, REPLACE ETC.
2. Etext template can also be used for character delimited files as well.
3. Payment files, bank statements are created using eText templates.

BI report - How to secure your pdf report output | Static method

 Usecase: Here, we will see how to protect your pdf file with password.

Report edit >> properties >> formatting >> enable pdf security as True >> provide open document password.

ERP BI report service - invoke a BI report from soap ui using report service.

Usecase: 

Generally we call the below service to extract the data from BI report.

https://<your oracle cloud application instance domain>/xmlpserver/services/ExternalReportWSSService?WSDL

HERE, We can also use below report service.

https://<host>:<port>/xmlpserver/services/v2/ReportService?wsdl

Screenshots:

Soap >> put wsdl >> ok >> choose run report 

Provide report absolute path  and other details, parameter name values if any >> basic authenticaltion 

We will get the response in base64 encoded that we need to decode and use it.

Reference:

https://docs.oracle.com/cd/E28280_01/bi.1111/e22259/webservices.htm#BIPDV002