Adding BI Publisher Add IN in MS word

 Follow the DOC ID 2435208.1

Most important point is Note: The versions of Microsoft Office, BI Publisher Desktop, and the Java JRE must all match as either 32-bit or 64-bit.

Get current logged in user in BIP

select SYS_CONTEXT ('USERENV', 'CLIENT_IDENTIFIER') from dual

This query will give the current logged in user in BIP

Select multiple values in BI Report parameter dropdown

When we want to allow a BI Report parameter to take in multiple values, we need to write the where clause as below.

select * from (select 'a' col1 from dual

union

select 'b' col1 from dual

union

select 'c' col1 from dual

union

select 'd' col1 from dual

union

select 'e' col1 from dual

union

select 'f' col1 from dual

)

where 1=1

--and col1 in (:p_col1)

AND ( ( Coalesce(NULL, :p_col1) IS NULL ) OR ( col1 IN ( :p_col1) ) )

If and col1 in (:p_col1) is used then when we select All, then nothing is printed instead of all the values. 

Coalesce function returns the first non null value from its parameters

HCM Extract - View User Entity Details - Change to old UI

Update the lookup ORA_PER_EXT_CONFIG and add below entry

Add a Lookup Code: EXT_UE_NEW with a Meaning as NO. This enables the Professional UI for View User Entity Details page

UCM Bursting Query BIP

 select to_char(call_id) as KEY,

'RolesRemoval' TEMPLATE,
'en-US' LOCALE,
'TEXT' OUTPUT_FORMAT,
'WCC' DEL_CHANNEL,
'FA_UCM_PROVISIONED' PARAMETER1,  /* Server Name */
'FAFusionImportExport' PARAMETER2,  /* Security Group */
:xdo_user_name PARAMETER3,  /* Author of the File */
'User'||to_char(call_id)||'.txt' PARAMETER5,  /* Title */
'User.dat' PARAMETER6,  /* Output File Name */
:TASK_INSTANCE_ID PARAMETER7,  /* Comments (Optional) */
'User'||to_char(call_id) PARAMETER8,   /* Content ID (Optional) If you specify the ID, it must be unique. If you don't specify the ID, the system generates a unique one. */
'FALSE' PARAMETER9  /* Custom metadata (true/false). Specify ‘false’.*/
from 
(
select r.call_id
from pay_flow_task_instances fti
,pay_flow_tasks_vl ft
,pay_requests r
where ft.flow_task_name = 'Generate Data'
and ft.base_flow_task_id = fti.base_flow_task_id 
and fti.flow_task_instance_id = r.flow_task_instance_id 
and r.call_type= 'ESS'
and  fti.flow_task_instance_id = :TASK_INSTANCE_ID
union
select 1234
from dual
where :TASK_INSTANCE_ID is null
)

Query for Date Parameter in the format YYYY/MM

with year as (
select yyyy from (SELECT LEVEL yyyy FROM DUAL CONNECT BY LEVEL <= EXTRACT(YEAR FROM SYSDATE)) where yyyy>=1951
),
month as (
SELECT to_char(LEVEL,'fm00') mm FROM DUAL CONNECT BY LEVEL <= EXTRACT(month FROM SYSDATE)
)
select yyyy||'/'||mm param from year y, month m
order by yyyy desc,mm desc

Sample Fast Formula - Global Absence Type Duration Formula

/******************************************************************************

FORMULA NAME: XYZ_Client_LWP_Duration_FF
FORMULA TYPE: Global Absence Type Duration Formula
DESCRIPTION: This formula returns the duration for India Client emps for LWP absence type deducting Republic Day, Independence Day and Gandhi Jayanthi
Change History:
Name Date Version Comments
-------------------------------------------------------------------------------
Aditya  Initial Version
*******************************************************************************/
DEFAULT FOR IV_START_DATE IS '4712/12/31 00:00:00' (date)
DEFAULT FOR IV_END_DATE IS '4712/12/31 00:00:00' (date)
DEFAULT FOR IV_START_TIME IS '00:00'
DEFAULT FOR IV_END_TIME IS '23:59'
DEFAULT FOR PER_ASG_FTE_VALUE IS 1
INPUTS ARE IV_START_DATE (date), IV_END_DATE (date), IV_START_TIME(text),IV_END_TIME(text)
ln_entry_duration_d = 0
ln_subtract_duration = 0
DURATION =0
ld_period_start_date = to_date(to_char(IV_START_DATE,'DD/MM/RRRR')||' '||'00:00:00','DD/MM/RRRR HH24:MI:SS')
ld_period_end_date = to_date(to_char(IV_END_DATE,'DD/MM/RRRR')||' '||'00:00:00','DD/MM/RRRR HH24:MI:SS')

ln_entry_duration_d = days_between(ld_period_end_date,ld_period_start_date) + 1
ln_start_year = to_number(to_char(IV_START_DATE,'RRRR'))
ln_end_year = to_number(to_char(IV_END_DATE,'RRRR'))
while ln_start_year<=ln_end_year loop
(
ld_republic_day = to_date('26/01/'||to_char(ln_start_year)||' '||'00:00:00','DD/MM/RRRR HH24:MI:SS')
ld_independence_day = to_date('15/08/'||to_char(ln_start_year)||' '||'00:00:00','DD/MM/RRRR HH24:MI:SS')
ld_gandhi_jayanthi = to_date('02/10/'||to_char(ln_start_year)||' '||'00:00:00','DD/MM/RRRR HH24:MI:SS')
ln_subtract_duration = ln_subtract_duration + is_date_between(ld_republic_day,ld_period_start_date,ld_period_end_date)
ln_subtract_duration = ln_subtract_duration + is_date_between(ld_independence_day,ld_period_start_date,ld_period_end_date)
ln_subtract_duration = ln_subtract_duration + is_date_between(ld_gandhi_jayanthi,ld_period_start_date,ld_period_end_date)
ln_start_year = ln_start_year + 1
)
ln_entry_duration_d = ln_entry_duration_d - ln_subtract_duration
if ln_entry_duration_d > 0 then
DURATION = ln_entry_duration_d
else
DURATION = 0

RETURN DURATION