In this session we are going to see how to fetch Notes from the Oracle Cloud WFC Compensation Worksheet.
Note: The "Notes" from Compensation Worksheet is stored as a CLOB.
So you need to use html2fo RTF function to convert html data to text. (<?html2fo:NOTE_TXT?>).
with people_tbl_vw as (
select person_id
,person_number
from per_all_people_f papf
where 1 = 1
and sysdate between papf.effective_start_date and papf.effective_end_date
and (coalesce(null,:p_pers_num) is null or papf.person_id in (:p_pers_num))
)
,cmp_plan_prd_tbl_vw as (
select cpp.plan_id
,cpp.period_id
from cmp_plan_periods cpp
where 1 = 1
and (coalesce(null,:p_plan) is null or cpp.period_id in (:p_plan))
)
,cmp_cwb_person_info_tbl_vw as (
select ccpi.person_number
,cpv.plan_name
,cpp.period_name
,replace(replace(replace(zn.note_txt,chr(10),''),chr(13),''),'<div><br/></div>','') note_txt
from cmp_cwb_person_info ccpi
,cmp_plan_prd_tbl_vw cpptv
,people_tbl_vw ptv
,per_all_assignments_f paaf
,cmp_plans_vl cpv
,cmp_plan_periods cpp
,zmm_notes zn
where 1 = 1
and cpptv.period_id = ccpi.period_id
and ptv.person_id = ccpi.person_id
and paaf.assignment_id = ccpi.assignment_id
and paaf.primary_assignment_flag = 'Y'
and cpv.plan_id = ccpi.plan_id
and cpv.status_code = 'ACTIVE'
and cpv.comp_type = 'CWB'
and cpp.period_id = ccpi.period_id
and zn.source_object_uid(+) = ccpi.person_event_id
and zn.source_object_code(+) = 'HcmCompWorkbench'
and sysdate between paaf.effective_start_date and paaf.effective_end_date
)
select * from cmp_cwb_person_info_tbl_vw
In this blog, we are going to see how to generate output using eText Template in Fusion BI Publisher I am going to show this using sample data. This is my sample data Click "Export" button to download the XML file. This is my XML File Open a blank Word Document and save it as type "Rich Text Format" Add a format setup which is given below <TEMPLATE TYPE> FIXED_POSITION_BASED <OUTPUT CHARACTER SET> UTF-8 <NEW RECORD CHARACTER> Carriage Return Add a Header table which is given below. For headers, give the header name within quotes ex: 'Emplid'. For headers, use the level DATA_DS which is the top level and appears only once in the XML <LEVEL> DATA_DS <POSITION> <LENGTH> <FORMAT> <PAD> <DATA> <COMMENTS> <NEW RECORD> DATA_DS Alpha ‘Emplid’
Comments
Post a Comment