Fusion HCM – Query for Payroll Costing Details

SELECT paaf.assignment_number, petf.base_element_name element_name,
       (SELECT petf2.base_element_name
          FROM pay_input_values_f piv2,
               pay_element_types_f petf2
         WHERE piv2.element_type_id = petf2.element_type_id
           AND pc.distributed_input_value_id = piv2.input_value_id
           AND ppa.effective_date BETWEEN petf2.effective_start_date
                                      AND petf2.effective_end_date
           AND ppa.effective_date BETWEEN piv2.effective_start_date
                                      AND piv2.effective_end_date
           AND ROWNUM < 2) distributed_element,
       DECODE (pc.debit_or_credit, ‘D’, 1, 0) * pc.costed_value AS debit,
       DECODE (pc.debit_or_credit, ‘C’, 1, 0) * pc.costed_value AS credit,
       DECODE (pc.balance_or_cost,
               ‘B’, ‘Balance’,
               ‘C’, ‘Cost’
              ) AS balance_or_cost,
       ppa.effective_date, pcak.*
  FROM pay_payroll_actions ppa,
       pay_time_periods ptp,
       pay_costs pc,
       pay_run_results prr,
       pay_input_values_vl piv,
       pay_cost_alloc_keyflex pcak,
       pay_pay_relationships_dn prd,
       pay_all_payrolls_f pap,
       pay_element_types_f petf,
       pay_ele_classifications_tl pectl,
       pay_payroll_rel_actions pra,
       hcm_lookups uomlookup,
       pay_rel_groups_dn payrel,
       pay_assigned_payrolls_dn papd,
       per_all_assignments_f paaf,
       per_legal_employers ple
 WHERE ppa.legislative_data_group_id = (SELECT legislative_data_group_id
                                          FROM per_legislative_data_groups_vl
                                         WHERE NAME = ‘US LDG’)
   AND pc.payroll_rel_action_id = pra.payroll_rel_action_id
   AND prd.payroll_relationship_id = pra.payroll_relationship_id
   AND pra.payroll_relationship_id = payrel.payroll_relationship_id
   AND payrel.assignment_id = paaf.assignment_id
   AND payrel.group_type = ‘A’
   AND payrel.parent_rel_group_id = papd.payroll_term_id
   AND ppa.effective_date BETWEEN payrel.start_date AND payrel.end_date
   AND papd.payroll_id = pap.payroll_id
   AND ppa.effective_date BETWEEN papd.start_date AND papd.end_date
   AND ppa.effective_date BETWEEN paaf.effective_start_date
                              AND paaf.effective_end_date
   AND paaf.legal_entity_id = ple.organization_id
   AND ple.status = ‘A’
   AND ppa.effective_date BETWEEN ple.effective_start_date
                              AND ple.effective_end_date
   AND pra.chunk_number IS NOT NULL
   AND ppa.payroll_action_id = pra.payroll_action_id
   AND ppa.effective_date BETWEEN prd.start_date AND prd.end_date
   AND pra.action_status = ‘C’
   AND ppa.action_type IN (‘B’, ‘C’, ‘CA’, ‘EC’, ‘Q’, ‘R’, ‘S’, ‘V’)
   AND ppa.payroll_id = pap.payroll_id
   AND ppa.effective_date BETWEEN pap.effective_start_date
                              AND pap.effective_end_date
   AND ppa.effective_date BETWEEN prd.start_date AND prd.end_date
   AND TRUNC (ppa.effective_date) BETWEEN NVL (:paydate,
                                               TRUNC (ppa.effective_date)
                                              )
                                      AND NVL (:paydate,
                                               TRUNC (ppa.effective_date)
                                              )
   AND ppa.earn_time_period_id = ptp.time_period_id(+)
   AND prr.run_result_id = pc.run_result_id
   AND prr.element_type_id = petf.element_type_id
   AND pectl.classification_id = petf.classification_id
   AND ppa.effective_date BETWEEN petf.effective_start_date
                              AND petf.effective_end_date
   AND pc.input_value_id = piv.input_value_id
   AND uomlookup.lookup_code = piv.uom
   AND uomlookup.lookup_type = ‘PAY_UNITS’
   AND pc.cost_allocation_keyflex_id = pcak.cost_allocation_keyflex_id