Weekends & Holidays to exclude in Fusion OTBI Reports

Arun Kumar.C

Hi All, We have Custom OTBI report which is calculating PR unprocessed days (PR creation → PO Creation days count). We have a requirement to exclude Weekends & Holidays in Days calculation in Fusion OTBI report. Can anyone help in this?

Regards,

Arun Kumar.C

John Hung-Oracle

Hi Arun, To better serve you, please post this question in the Reporting and Analytics for ERP forum to get the appropriate procurement experts eyes on it. I will close this post. Thank you.

Nathan CCC

Hi, You can use page Manage Calendar Events. Setup events of type Public Holiday by Geography. Then join to the transaction you are looking, in this case a purchase requisition, for example based on the HCM person worker assignment location of the requester having the same geography.

Add this query in your data model in your report in OTBI.

select all --count() over (partition by null) as n,

to_char(geopublicholiday.day_date,upper('yyyy-mm-dd'))||'~'||geopublicholiday.short_code||'~'||to_char(geopublicholiday.business_group_id) as publicholiday_id

, geopublicholiday.day_date, geopublicholiday.short_code

, geopublicholiday.business_group_id

, geopublicholiday.calendar_event_id

, geopublicholiday.cal_event_coverage_id as calendar_event_coverage_id

, geopublicholiday.tree_structure_code

, geopublicholiday.tree_code

, geopublicholiday.tree_version_id

, geopublicholiday.tree_node_id, geopublicholiday.category

, geopublicholiday.geography_type

, geopublicholiday.geography_code

, geopublicholiday.geography_name

, geopublicholiday.geography_code as geo_hierarchy_node_value

from

( -- geogpublicholiday

select all --count() over (partition by null) as n

tl.language

||'~'||t.tree_structure_code

||'~'||t.tree_code

||'~'||to_char(t.tree_version_id)

||'~'||to_char(c.tree_node_id)

||'~'||to_char(t.business_group_id)

||'~'||t.short_code

||'~'||to_char(c.cal_event_coverage_id)

as geogpublicholiday_id

, tl.language

, t.tree_structure_code

, t.tree_code

, t.tree_version_id

, c.tree_node_id, t.calendar_event_id

, c.cal_event_coverage_id

, t.business_group_id

, t.short_code

, t.coverage_type

, t.category

, g.depth as tree_depth

, g.pk3_start_value as geography_type

, g.pk1_start_value as geography_code

, g.pk2_start_value as geography_name

, g.parent_tree_node_id as tree_node_id_parent

, g.parent_pk3_value as geography_type_parent

, g.parent_pk1_value as geography_code_parent

, g.parent_pk2_value as geography_name_parent

, tl.name

, tl.description

, t.start_date_time

, t.end_date_time

, (t.end_date_time - t.start_date_time) as time_interval, (trunc(t.start_date_time,upper('DD'))) as day_date -- assume duration = 1 dayfrom

-- https://docs.oracle.com/en/cloud/saas/human-resources/23c/oedmh/percalendarevents-26821.html#percalendarevents-26821

-- per_calendar_events_pk calendar_event_id

-- per_calendar_events_pk   unique  fusion_ts_tx_idx    calendar_event_id

-- per_calendar_events_u2   unique  default short_code, business_group_id

-- fk per_cal_event_coverage    per_calendar_events calendar_event_id

per_calendar_events t
inner join 

-- per_calendar_events_tl_pk calendar_event_id, language

-- per_calendar_events_tl_pk    unique  default calendar_event_id, language

per_calendar_events_tl tl

on (

tl.language = upper('us') --userenv('lang')

and t.calendar_event_id = tl.calendar_event_id 

)


inner join

-- https://docs.oracle.com/en/cloud/saas/human-resources/23c/oedmh/percaleventcoverage-24496.html#percaleventcoverage-24496

-- per_cal_event_coverage_pk cal_event_coverage_id

-- per_cal_event_coverage_pk    unique  fusion_ts_tx_idx    cal_event_coverage_id

-- tree_node_id not null

per_cal_event_coverage c

on (

t.calendar_event_id = c.calendar_event_id 


)

inner join 

-- https://docs.oracle.com/en/cloud/saas/human-resources/23c/oedmh/pergeotreenode-5649.html#pergeotreenode-5649

-- per_geo_tree_node_pk tree_structure_code, tree_code, tree_version_id, tree_node_id

-- per_geo_tree_node_pk unique  fusion_ts_tx_data   tree_structure_code, tree_code, tree_version_id, tree_node_id, ora_seed_set1    active

-- per_geo_tree_node_pk1    unique  fusion_ts_tx_data   tree_structure_code, tree_code, tree_version_id, tree_node_id, ora_seed_set2    active

-- pk3_start_value --ORA_PER_GEOGRAPHIC_TREE_NODES

-- pk1_start_value --code

-- pk2_start_value --name	

per_geo_tree_node g 

on (

g.tree_structure_code = t.tree_structure_code 

and g.tree_code = t.tree_code

and g.tree_version_id = t.tree_version_id

and g.tree_node_id = c.tree_node_id

)

where 1 = 1

and t.coverage_type = upper('G') -- coverage type (org or geo)

and t.category = upper('PH') -- public holidays         
) geopublicholiday