Tuesday, 9 January 2018

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

6 comments:

  1. Thanks for sharing article about CXML Punchout
    CXML Punchout

    ReplyDelete
  2. cXML PunchOut - Commerce extensible markup language application who is a protocol created by Ariba used to communicate between sessions across the internet.
    Commerce Extensible Markup Language Punchout

    ReplyDelete
  3. Such an ideal piece of blog.Thanks for sharing
    Oracle SOA Online Course

    ReplyDelete
  4. This is like an algorithm and not all of us are coders here.

    paycheck stubs
    check stub maker

    ReplyDelete