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 cancel the Work Relationship using HDL (HCM Data Loader) Please make sure you have taken the back up of all the necessary data before cancelling the Work Relationship To cancel the Work Relationship, we use HDL Source Key method. Use the below sample template for reference SET PURGE_FUTURE_CHANGES N METADATA|WorkRelationship|SourceSystemOwner|SourceSystemId|PersonId(SourceSystemId)|LegalEmployerName|PersonNumber|DateStart|WorkerType|CancelWorkRelationshipFlag DELETE|WorkRelationship|FUSION|1234|WRK_1234|Legal_Employer|1234|2018/02/01|E|Y In the above template, we do not know the values of the source keys SourceSystemOwner SourceSystemId PersonId(SourceSystemId) Use below query to fetch the SourceSystemOwner Query: select source_system_owner from hrc_integration_key_map where surrogate_id = period_of_service_id -- pass period_of_service_id corresponding to Work Relationship Use below query to fetch the SourceSy...
Comments
Post a Comment