Skip to main content

Do you know how to fetch Oracle Cloud HCM Job Details using SQL

By
In this session, we are going to see how to fetch Oracle Cloud HCM Job Details using SQL.

SQL Text:
select pjfv.name ,pjfv.job_code ,to_char(pjfv.effective_start_date, 'mm/dd/yyyy','nls_date_language=english') effective_date ,per_extract_utility.get_decoded_lookup('ACTIVE_INACTIVE',pjfv.active_status) status ,setid.set_name job_set ,pjffv.job_family_name ,per_extract_utility.get_decoded_lookup('JOB_FUNCTION_CODE',pjfv.job_function_code) job_function ,per_extract_utility.get_decoded_lookup('HRX_US_FLSA_CATEGORY',jl.information1) overtime_status ,per_extract_utility.get_decoded_lookup('HRX_US_EEO1_JOB_CATEGORY',jl.information2) eeo1_category ,pgfv.name grade ,round(prvf.minimum,2) minimum ,round(prvf.maximum,2) maximum ,round(prvf.mid_value,2) mid_value from per_jobs_f_vl pjfv ,fnd_setid_sets setid ,per_job_leg_f jl ,per_job_family_f_vl pjffv ,per_valid_grades_f pvgf ,per_grades_f_vl pgfv ,per_rate_values_f prvf where 1 = 1 and pjfv.active_status = 'A' and setid.set_id(+) = pjfv.set_id and jl.job_id(+) = pjfv.job_id and jl.legislation_code(+) = 'US' and pjffv.job_family_id(+) = pjfv.job_family_id and pvgf.job_id(+) = pjfv.job_id and pgfv.grade_id(+) = pvgf.grade_id and prvf.rate_object_id(+) = pgfv.grade_id and prvf.rate_object_type(+) = 'GRADE' and :p_eff_dt between pjfv.effective_start_date and pjfv.effective_end_date and :p_eff_dt between jl.effective_start_date(+) and jl.effective_end_date(+) and :p_eff_dt between pjffv.effective_start_date(+) and pjffv.effective_end_date(+) and :p_eff_dt between pvgf.effective_start_date(+) and pvgf.effective_end_date(+) and :p_eff_dt between pgfv.effective_start_date(+) and pgfv.effective_end_date(+) and :p_eff_dt between prvf.effective_start_date(+) and prvf.effective_end_date(+)

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

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