Skip to main content

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

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

Do you know how to generate output using eText Template in Oracle Cloud BI Publisher?

In this blog, we are going to see how to generate output using eText Template in Fusion BI Publisher I am going to show this using sample data. This is my sample data Click "Export" button to download the XML file. This is my XML File Open a blank Word Document and save it as type "Rich Text Format" Add a format setup which is given below <TEMPLATE TYPE> FIXED_POSITION_BASED <OUTPUT CHARACTER SET> UTF-8 <NEW RECORD CHARACTER> Carriage Return Add a Header table which is given below. For headers, give the header name within quotes ex: 'Emplid'. For headers, use the level DATA_DS which is the top level and appears only once in the XML <LEVEL> DATA_DS <POSITION> <LENGTH> <FORMAT> <PAD> <DATA> <COMMENTS> <NEW RECORD> DATA_DS Alpha ‘Emplid’
20 comments
Read more

Do you know how to cancel the Work Relationship using HDL in Oracle Cloud HCM?

In this blog, we are going to see how to cancel the Work Relationship using HDL (HCM Data Loader) Please make sure you have taken the back up of all the necessary data before cancelling the Work Relationship To cancel the Work Relationship, we use HDL Source Key method. Use the below sample template for reference SET PURGE_FUTURE_CHANGES N METADATA|WorkRelationship|SourceSystemOwner|SourceSystemId|PersonId(SourceSystemId)|LegalEmployerName|PersonNumber|DateStart|WorkerType|CancelWorkRelationshipFlag DELETE|WorkRelationship|FUSION|1234|WRK_1234|Legal_Employer|1234|2018/02/01|E|Y In the above template, we do not know the values of the source keys SourceSystemOwner SourceSystemId PersonId(SourceSystemId) Use below query to fetch the SourceSystemOwner Query: select source_system_owner from hrc_integration_key_map where surrogate_id = period_of_service_id   -- pass period_of_service_id corresponding to Work Relationship Use below query to fetch the SourceSystemId Q
3 comments
Read more

Do you know how to generate output using Excel Template in Oracle Cloud BI Publisher?

In this blog, we are going to see how to generate output using Excel Templates in Fusion BI Publisher I am going to show this using sample data. This is my sample data Click "Export" button to download the XML file. This is my XML File Open a blank Excel file and save it as type "Excel 97-2003 Workbook" (.xls) I have created 3 tabs "Summary", "Detail" and "XDO_METADATA" XDO_METADATA tab is mandatory. Place the below text in XDO_METADATA tab Version ARU-dbdrv Extractor Version Template Code Template Type TYPE_EXCEL_TEMPLATE Preprocess XSLT File Last Modified Date Last Modified By Data Constraints: In the "Detail" tab create Header row as shown below Now add the XDO tags Select the blank &quo
2 comments
Read more