PAY_ELEMENT_ENTRY_VALUES_F This Table Column SCREEN_ENTRY_VALUE Is Not Pulling Values Correctly (Doc ID 2662137.1)

Last updated on APRIL 19, 2024

Applies to:

Symptoms

On : 11.13.20.01.0 version, Global Payroll

ACTUAL BEHAVIOR
---------------

the following query is not returning the input value of an element entry

SELECT EEV.*
 FROM
pay_entry_usages EU,
  pay_element_entries_f EE,
  pay_element_types_vl ET,
  pay_input_values_f PIV,
  pay_element_entry_values_f EEV,
  pay_ele_classifications_vl CLA ,
  pay_ele_classifications_vl SCLA
  WHERE EU.payroll_relationship_id = (select payroll_relationship_id from pay_pay_relationships_dn pprd
  where pprd.payroll_relationship_number = <PAYROLL_RELATIONSHIP_NUMBER>)
  AND EE.element_entry_id = EU.element_entry_id
  and CLA.classification_id = ET.classification_id
  and SCLA.classification_id (+) = ET.secondary_classification_id
  and PIV.input_value_id (+) = EEV.input_value_id
  and EEV.effective_start_date between PIV.effective_start_date (+)
  and PIV.effective_end_date (+)
  AND EU.date_from
  BETWEEN ET.effective_start_date
  AND ET.effective_end_date
  AND EE.element_type_id = ET.element_type_id
  AND EEV.element_entry_id (+) = EE.element_entry_id
  AND EE.effective_start_date between EEV.effective_start_date (+)
  and EEV.effective_end_date (+)
  AND ET.BASE_ELEMENT_NAME=<ELEMENT_NAME>
  order by ET.base_element_name, EU.date_from, PIV.base_name

EXPECTED BEHAVIOR
-----------------------
the query should return the input value.

STEPS
-----------------------
The issue can be reproduced at will with the following steps:
1. Run Custom SQL query


BUSINESS IMPACT
-----------------------
The issue has the following business impact:
Due to this issue, users cannot get a custom report where they can retrieve element entry input value

Cause

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.