Drill down report in BIP Report

 Hi Everyone,

In this post, we will see how to add a BIP Report as a Drill Down report to another BIP Report.

Step1: Create Drill down BIP report as usual

Step2: In the main Report, In sql code add the following as new field

'https://<host>:443/xmlpserver/reportpath.xdo??_xiasynch=&_xpf=&_xpt=0&_dFlag=false&_edIndex=0&_dIndex=0&_rToken=&_ranDiag=false&_xdo=%report_path.xdo&_xmode=4&xdo%3Axdo%3A_params<parametername>_div_input='||<parametervalue from code>||'&_params<parameter2>='||<parameter 2 value from code>||'&_xt=<ReportName without extension>&_xf=html&_xautorun=true&_linkToReport=true' 

Oracle doc link: Accessing Reports via a URL

Step3: In the Main report xpt, edit, on the column where hyperlink has to appear

select properties > Misc > URL - give the DM field where step2 is there

Creating and working with Infolets

 Infolet:

It is a small interactive interface to see key info

Key Points to note:

1. It can have 3 views : Front , Back and Expanded

so max of 3 reports can be mapped

2. Infolets can have a link to detailed reports.

3. These can be hidden or re-orded

4. Front view is mandatory, if no report is used, shows blank (3*2 size is not recommended as it is difficult to view in mobile devices)

5. Back view size will be always same as front size (usually will have a graphs)

6. Back and Expanded view are optionals

7.Expanded view is bigger than other views.

8. Actions menu will available for all views

9.Available sizes: 1*1, 2*1, 3*1, 1*2, 2*2, 3*2, 1*3, 2*3

Infolet Creation:

1. Navigator > Configuration > Sandboxes

2. Create Sandbox

3. Give Name, and select Page Composer

click on Create and Enter

4.From Tools > Select Page Composer

this is how it will come

5. Goto Analytics section on Home Page, click on Infolet Repoistory >Create Infolet

6. Give Infolet name, select dimension for front view, select enable or disable for back and expanded views

click save and close

7. Add content

select report  and click add

close, we can see data if exists

Similarly can be added for other views

Actions menu:

Link detailed report: to see detailed one.

edit content: to change the report

edit title and view: to go to Infolet creation page and change title and view sizes, enable options

delete: to delete the infolet

edit visiblity: to see el expression related info

to switch to back view: press side arrow

for front view

for expanded view

expanded view tile

Add Barcode in oracle fusion Reports

 Hi Everyone,

In this post let us see how to add Barcode in Oracle Fusion RTF Templates and setups to be done to reflect in the report output.

Step1: Check if  "Code 128" font is available in MS Word.

There are many fonts that support barcode, for this blog, we are using "Code 128"       

If not available Font File will be available in the system if you have already BI Publisher installed in your system.

Follow the steps to get the file, so it can be installed

Goto the following path: C:\Program Files\Oracle\BI Publisher\BI Publisher Desktop\Template Builder for Word\fonts

Copy the highlighted file 128R00.rtf and install it.

Step2: Once we have required font, goto your rtf and insert the tag and select the font as Code128.

Sample xml:

Inserted field in rtf

Now change font to Code 128

Step3: Upload the rtf in required report

Step4: If Code 128 is not available in Oracle ERP, follow these steps to install so that when users run the report the see the Barcode

Login > goto Reports and Analytics > under username > Administration>Manage BI Publisher > Font Mappings

"Upload Fonts" under "Manage Custom Fonts"

This is how it looks:

Then add this uploaded Font under RTF Templates > Add Font Mapping

Base Font: Code 128, Select Target Font: 128R00.TTF and click Apply

So thats it , now report is ready to be tested with barcode

Query to fetch COA Account Fields

 Hi All,

A short post to get COA Account Segment related fields.

select 

fvvv.value,

FVVV.DESCRIPTION,

fvvv.ENABLED_FLAG  Enabled,

fvvv.START_DATE_ACTIVE Start_Date,

fvvv.END_DATE_ACTIVE End_Date,

fvvv.Sort_Order,

fvvv.SUMMARY_FLAG Summary,

fvvv.FLEX_VALUE_ATTRIBUTE1 Allow_Posting,

fvvv.FLEX_VALUE_ATTRIBUTE2 Allow_Budgeting,

gl.Meaning Account_type

from 

fnd_vs_value_sets fvvs

,FND_VS_VALUES_VL fvvv

,gl_lookups gl

where 

fvvs.VALUE_SET_CODE=valueset_name --pass required valueset name

and fvvs.VALUE_SET_ID=fvvv.VALUE_SET_ID

and fvvv.FLEX_VALUE_ATTRIBUTE3 =gl.lookup_code

and gl.LOOKUP_TYPE='ACCOUNT TYPE'

Query to fetch AR Receipts Application reference — Oracle Fusion

 Select

acra.RECEIPT_NUMBER TRX_NUMBER,
HZ.PARTY_NAME CUSTOMER_NAME,
(Select listagg(apsa1.trx_number,’;’) within group (order by RECEIVABLE_APPLICATION_ID)
from 
ar_payment_schedules_all apsa1,
AR_RECEIVABLE_APPLICATIONS_ALL ARAA

where
araa.APPLIED_PAYMENT_SCHEDULE_ID=apsa1.PAYMENT_SCHEDULE_ID
and araa.cash_receipt_id=acra.cash_receipt_id
and araa.display=’Y’
) Application_Reference

from
AR_CASH_RECEIPTS_ALL Acra,
AR_PAYMENT_SCHEDULES_ALL apsa ,
HZ_CUST_ACCOUNTS hca,
HZ_PARTIES HZ

where
acra.CASH_RECEIPT_ID = apsa.CASH_RECEIPT_ID(+)
and acra.PAY_FROM_CUSTOMER=hca.cust_account_id
and hca.party_id=hz.party_id

Query for PPM Project Status #PPM #Projects

 Hi,

In this post let's see how to fetch Project Status in Oracle Fusion.

Tables used:

PJF_PROJECTS_ALL_VL

PJF_PROJECT_STATUSES_TL

Query:

Select PPST.PROJECT_STATUS_NAME

from

PJF_PROJECTS_ALL_VL PPAV

PJF_PROJECT_STATUSES_TL PPST

where 1=1

AND PPST.LANGUAGE=USERENV('LANG')

AND PPA.PROJECT_STATUS_CODE=PPST.PROJECT_STATUS_CODE

Query to get PPM Project Manager name in Oracle Fusion

 Hi,

In this post lets see how to fetch Project Manager name of a given project in Oracle Fusion

List of Tables used:

PJF_PROJECTS_ALL_VL

PJF_PROJECT_PARTIES

PJF_PROJ_ROLE_TYPES_TL

PER_PERSON_NAMES_F_V

Query:

Select

ppnfv.full_name as Project_manager

from

PJF_PROJECTS_ALL_VL ppav

PJF_PROJECT_PARTIES ppp

PJF_PROJ_ROLE_TYPES_TL pprtl

PER_PERSON_NAMES_F_V ppnfv

where 1=1

AND ppav.project_id = ppp.project_id

AND ppp.RESOURCE_SOURCE_ID=ppnfv1.person_id

AND ppnfv1.name_type='GLOBAL'

AND ppp.project_role_id = pprtl.project_role_id

AND pprtl.PROJECT_ROLE_NAME ='Project Manager'