Skip to main content

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 and thdr.stop_time_trunc) quantity from per_all_people_f papf ,per_all_assignments_m paaf ,per_person_names_f ppnf ,hwm_ext_timecard_hdr_v thdr ,hwm_ext_timecard_status_v hts where 1 = 1 and paaf.person_id = papf.person_id and paaf.assignment_type in ('E') and paaf.assignment_status_type in ('ACTIVE') and paaf.effective_latest_change = 'Y' and paaf.primary_flag = 'Y' and ppnf.person_id = paaf.person_id and ppnf.name_type = 'GLOBAL' and thdr.resource_id = papf.person_id and thdr.delete_flag is null and thdr.latest_version = 'Y' and ((thdr.orig_tm_rec_grp_id is null) or (thdr.orig_tm_rec_grp_id = thdr.tm_rec_grp_id)) and ((thdr.orig_tm_rec_grp_version is null) or (thdr.orig_tm_rec_grp_version = thdr.tm_rec_grp_version)) and thdr.layer_code = 'TIME_RPTD' and (thdr.start_time_trunc between :p_from_dt and :p_to_dt or thdr.stop_time_trunc between :p_from_dt and :p_to_dt) and thdr.grp_type_name = 'Timecard' and hts.resource_id = thdr.resource_id and hts.tm_bldg_blk_id = thdr.tm_rec_grp_id and hts.tm_bldg_blk_version = thdr.tm_rec_grp_version and hts.definitioncode = 'D_TM_UI_STATUS' and trunc(sysdate) between papf.effective_start_date and papf.effective_end_date and trunc(sysdate) between paaf.effective_start_date and paaf.effective_end_date and trunc(sysdate) between ppnf.effective_start_date and ppnf.effective_end_date
Labels:

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 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 ...
Post a Comment
Read more