Skip to main content

Do you know how to fetch Oracle Cloud HCM TRU and Department associated with Legal Employer

By
In this session, we are going to see how to fetch Oracle Cloud HCM TRU and Department associated with Legal Employer

SQL Text
select papf.person_number ,paam.assignment_number ,ppnf.full_name ,leg.name legal_employer ,tru.name tax_reporting_unit ,dep.name department ,dep_leg.name dep_legal_employer 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 ,hr_organization_v leg ,hr_organization_v dep ,hr_organization_information_f inf ,hr_organization_v dep_leg 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 leg.organization_id = paam.legal_entity_id and leg.classification_code = 'HCM_LEMP' and leg.status = 'A' and dep.organization_id = paam.organization_id and dep.classification_code = 'DEPARTMENT' and dep.status = 'A' and inf.organization_id = dep.organization_id and inf.org_information_context = 'XX_LE_ASSOCIATED_DEPT' -- Client Specific and dep_leg.legal_entity_id = inf.org_information_number1 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 and trunc(sysdate) between leg.effective_start_date and leg.effective_end_date and trunc(sysdate) between dep.effective_start_date and dep.effective_end_date and trunc(sysdate) between dep_leg.effective_start_date and dep_leg.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