Fusion BIP : How to show one parameter in report and pass different value to the datamodel

Hi All,

There can be a scenario where we might need to show one value in the report and a different value to the actual query. Lets say a person_name to the user and person_id to the underlying data model.

Here the query builder for the parameter shows only single query field :
==>

So in this case we can write the query as below

select A , B from dual;

Here  the field A will be shown to the user and the field B would be passed to the data model.


Thanks,
Srikanth

How to get name of the day in Fusion BIP env

If you have already worked in BIP reports ,getting the name of the day from SQL query might seem not feasible.

Lets say if we run below query, it is always going to return a number denoting the day.

==> select to_char( SYSDATE , 'DAY' ) Todays_name from dual;  -- considering today is friday

Todays_name
==========
6

However if you want the result to be either FRIDAY or Saturday, then we need to pass an additional parameter to the TO_CHAR() function. This is 'nls_date_language = American'.

===> select to_char(sysdate, 'DAY' , 'nls_date_language = American' ) Todays_name from dual;

Todays_name
==========
FRIDAY

For more info on this please refer to official Oracle documentation.

===> https://docs.oracle.com/cd/B13789_01/server.101/b10749/ch9sql.htm


Hope this helps.


Thanks,
Srikanth

Fusion BIP : How to accomodate a big table in MS-WORD page

While working in BIP, you might encounter a requirement where a huge table ( say with 30 columns ) need to be accomodated in a single page. For example below is the page which shows the table partially.

 In that case even when we select any of the below page sizes, the table would not be shown completely.

                       

To view the table in its entirety we can select the below option. This is called WebLayout and is present in the bottom right corner of the document.

                                       

Now the complete table can be viewed and the prompts can be edited as we wish.

Thanks for reading.

Regards,
Srikanth

What to do if HCM Extract definition is not editable : Rel 13 18C / 18D

If you are suddenly seeing that you are not able to edit the HCM Extract definition, which you were able to edit earlier, then there is a new feature from 18C/18D patch bundle that is causing it.

The extract would be in read only format as shown below :

The solution for this is to open the extract and uncheck the "Lock Definition" and provide the comments.

Once the extract is saved, the extract would become editable again.

Thanks,
Srikanth

HCM Extracts : How to find ESS job id for a scheduled extract to be cancelled

Hi ,

Many times we may not find the exact ESS job to be cancelled, when we want to cancel an already scheduled extract.

In that case we can use below SQL to identify the ESS job ID. Here we just need to key in the extract run name which we have entered while scheduling the extract :


select pfi1.INSTANCE_NAME , pfi1.flow_instance_id , essrh1.requestid  from
pay_flow_instances pfi1 ,
pay_requests pr1,
ess_request_history essrh1
where pfi1.INSTANCE_NAME like '<Extract Run Name>'
and pfi1.flow_instance_id = pr1.flow_instance_id
and pr1.call_id = essrh1.requestid

Hope this helps.

Thanks,
Srikanth

Fusion BIP : How to get current logged in user in report

I would like to share a very small topic, yet very useful in Fusion reports space.

Many times we want to query records/ transactions related to the logged in user. (i.e, the user who is currently running the report ).

For this we can use the below query :

SQL>> select SYS_CONTEXT( 'USERENV', 'CLIENT_IDENTIFIER' )  from dual;


Happy learning and sharing,
Srikanth

HCM Extracts : How to specify dynamic date in HCM Extracts Schedule

It is very rare that the HCM Extracts would be developed to be run on ad-hoc basis.
Most of the times the extracts are scheduled on a periodic basis.

Saying so, we might need to pass dynamic value for its parameters for ex: effective date. ( in place of SYSDATE which is by default )

Lets say we want the extract to run with date range of 15 days in future. ( i.e EffectiveDate is SYSDATE+15 )

To achieve this, we need to go to Data Exchange ==> HCM Extracts ==> Refine Extracts ==>
Search for the extract and click on edit.

Select the Effective Date parameter and click on edit. Enter below values :
a. Parameter Basis : Post SQL Bind
b. Basis Value : select sysdate+15 from dual
c. Save and then Submit.
 

Bingo, now whenever the extract runs the effective date would be Todays date + 15.

Thanks for reading,
Srikanth