Skip to main content

Do you know how to fetch Notes from the Oracle Cloud WFC Compensation Worksheet?

By

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 and you need to use appropriate Replace statements based on your requirements. Also use htm2fo RTF function to convert html tags to text. (<?html2fo:NOTE_TXT?>)

SQL Text:

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

Comments

Post a Comment

Popular posts from this blog

Oracle Cloud HCM - Custom SQL

By
WITH SAWITH0 AS (   select          /*+ inline */     T5889157.C442017697 as c3,     T5889157.C435128591 as c4,     T5889157.C262853249 as c5,     T5889157.C475696698 as c6,     T5889157.C171798894 as c7,     T5889157.C350832392 as c8,     T5889157.C180745024 as c9,     T5889157.C171898426 as c10,     T5889157.C43504333 as c11,     T5889157.C3583004 as c12,     T5889157.C458818009 as c13   from     (       SELECT         V184978139.USER_CATEGORY_NAME AS C442017697,         V148402464.DIMENSION_NAME AS C435128591,         V187766949.BALANCE_NAME AS C262853249,         V294690684.NAME AS C475696698,  ...
Post a Comment
Read more

Do you know how to fetch Oracle Cloud Time Card Status using SQL

By
In this session we are going to see how to fetch Oracle Cloud Time Card Status using SQL   select papf . person_number , ppnf . full_name , per_extract_utility . get_decoded_lookup ( 'HWM_UI_STATUS' , hts . status_value ) status , to_char ( thdr . start_time_trunc , 'fmmm/dd/yyyy' ) || ' - ' || to_char ( thdr . stop_time_trunc , 'fmmm/dd/yyyy' ) week ,( select sum ( trec . measure ) from hwm_tm_rec trec where 1 = 1 and trec . delete_flag is null and trec . latest_version = 'Y' and (( trec . orig_tm_rec_id is null ) or ( trec . orig_tm_rec_id = trec . tm_rec_id )) and (( trec . orig_tm_rec_version is null ) or ( trec . orig_tm_rec_version = trec . tm_rec_version )) and trec . resource_id = papf . person_id and trunc ( trec . actual_date ) between thdr . start_time_trunc...
Post a Comment
Read more