Skip to main content

Do you know how to fetch Oracle Cloud Payroll Costing data using SQL?

By

In this session we are going to see how to fetch Oracle Cloud Payroll Costing data using SQL.

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)) ) ,pay_period_tbl_vw as ( select ptp.time_period_id ,ptp.payroll_id ,ptp.start_date ,ptp.end_date ,ptp.default_paydate ,ptp.regular_earn_date from pay_time_periods ptp where 1 = 1 and ptp.default_paydate between trunc(:p_date_paid_frm) and trunc(:p_date_paid_to) ) ,pay_tbl_vw1 as ( select papf.person_number ,ppnf.full_name person_name ,per_extract_utility.get_decoded_lookup('PAY_COST_DISTRIBUTION_STATUS',pc.status) status ,bu.name business_unit ,to_char(ptp.end_date,'yyyy/mm/dd') pay_end_dt ,to_char(ptp.default_paydate,'yyyy/mm/dd') date_paid ,case when pc.transfer_to_gl_flag = 'Y' then to_char(ppa.effective_date,'yyyy/mm/dd') end gl_posting_date ,paam1.assignment_number ,paam1.assignment_id ,paam1.assignment_name ,dep.name department ,pecv.classification_name ,pet.element_name ,dist_pet.element_name dist_element ,case when pc.balance_or_cost = 'B' then 'Balance' when pc.balance_or_cost = 'C' then 'Cost' end balance_or_cost ,pc.costed_value ,pcak.segment1 ,pcak.segment2 ,pcak.segment3 from people_tbl_vw papf ,per_all_assignments_m paam ,pay_payroll_actions ppa ,pay_period_tbl_vw ptp ,pay_pay_relationships_dn prd ,pay_payroll_rel_actions pra ,pay_input_values_f pivf ,pay_element_types_vl pet ,pay_ele_classifications_vl pecv ,per_person_names_f ppnf ,hr_organization_v bu ,hr_organization_v dep ,pay_costs pc ,pay_input_values_f dist_pivf ,pay_element_types_vl dist_pet ,pay_cost_alloc_keyflex pcak ,pay_payroll_assignments ppasg ,per_all_assignments_m paam1 where 1 = 1 and paam.person_id = papf.person_id and paam.assignment_type = 'E' and paam.primary_assignment_flag = 'Y' and paam.effective_latest_change = 'Y' and prd.person_id = paam.person_id and ptp.time_period_id = ppa.earn_time_period_id and ptp.payroll_id = ppa.payroll_id and ppa.action_status = 'C' and ppa.action_type IN ('R','Q','B','V','I','S','C','CA','EC') and pra.payroll_relationship_id = prd.payroll_relationship_id and pra.payroll_action_id = ppa.payroll_action_id and pra.retro_component_id is null and pet.element_type_id = pivf.element_type_id and pecv.classification_id = pet.classification_id and ppnf.person_id = paam.person_id and ppnf.name_type = 'GLOBAL' and bu.organization_id = paam.business_unit_id and bu.classification_code = 'FUN_BUSINESS_UNIT' and bu.status = 'A' and dep.organization_id(+) = paam.organization_id and dep.classification_code(+) = 'DEPARTMENT' and dep.status(+) = 'A' and pc.payroll_rel_action_id = pra.payroll_rel_action_id and pc.input_value_id = pivf.input_value_id and dist_pivf.input_value_id(+) = pc.distributed_input_value_id and dist_pet.element_type_id(+) = dist_pivf.element_type_id and pcak.cost_allocation_keyflex_id = pc.cost_allocation_keyflex_id and ppasg.payroll_assignment_id(+) = pc.payroll_assignment_id and paam1.assignment_id(+) = ppasg.hr_assignment_id and paam1.assignment_type(+) = 'E' and paam1.effective_latest_change(+) = 'Y' and ppa.effective_date between paam.effective_start_date and paam.effective_end_date and ppa.effective_date between pet.effective_start_date and pet.effective_end_date and ppa.effective_date between pivf.effective_start_date and pivf.effective_end_date and ppa.effective_date between dist_pet.effective_start_date(+) and dist_pet.effective_end_date(+) and ppa.effective_date between dist_pivf.effective_start_date(+) and dist_pivf.effective_end_date(+) and ppa.effective_date between ppnf.effective_start_date and ppnf.effective_end_date and paam.effective_start_date between bu.effective_start_date and bu.effective_end_date and paam.effective_start_date between dep.effective_start_date(+) and dep.effective_end_date(+) and ppa.effective_date between paam1.effective_start_date(+) and paam1.effective_end_date(+) ) select * from pay_tbl_vw1

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