Skip to main content

Do you how to fetch the Oracle Cloud HCM TRU associated with Legal Employer using SQL

By
In this session we are going to see how to fetch the Oracle Cloud HCM TRU associated with Legal Employer using SQL

SQL Text:
select papf.person_number ,paam.assignment_number ,ppnf.full_name ,pdccd.display_name ,tru.name from per_all_people_f papf ,per_person_names_f ppnf ,per_all_assignments_m paam ,pay_payroll_assignments ppa ,pay_dir_cards_f pdcf ,pay_dir_card_definitions_vl pdccd ,pay_dir_card_components_f pdccf ,pay_dir_rep_cards_f pdrcf ,hr_organization_v tru where 1 = 1 -- and papf.person_number in ('0000') and papf.person_id = ppnf.person_id and papf.person_id = paam.person_id and paam.legislation_code = 'US' and ppa.hr_assignment_id = paam.assignment_id and pdcf.payroll_relationship_id = ppa.payroll_relationship_id and pdccd.dir_card_definition_id = pdcf.dir_card_definition_id and pdccf.dir_card_id = pdcf.dir_card_id and pdccd.display_name = 'Tax Withholding' and pdrcf.dir_card_comp_id = pdccf.dir_card_comp_id and pdrcf.dir_card_id = pdccf.dir_card_id and paam.assignment_status_type = 'ACTIVE' and paam.assignment_type = 'E' and ppnf.name_type = 'GLOBAL' and paam.effective_latest_change = 'Y' and tru.organization_id = pdrcf.tax_unit_id and tru.classification_code = 'HCM_TRU' and tru.status = 'A' and trunc(sysdate) between ppa.start_date and ppa.end_date and trunc(sysdate) between paam.effective_start_date and paam.effective_end_date and trunc(sysdate) between papf.effective_start_date and papf.effective_end_date and trunc(sysdate) between ppnf.effective_start_date and ppnf.effective_end_date and trunc(sysdate) between pdcf.effective_start_date and coalesce(pdcf.effective_end_date,to_date('4712-12-31')) and trunc(sysdate) between pdccf.effective_start_date and coalesce(pdccf.effective_end_date,to_date('4712-12-31')) and trunc(sysdate) between pdrcf.effective_start_date and coalesce(pdrcf.effective_end_date,to_date('4712-12-31')) and trunc(sysdate) between tru.effective_start_date and tru.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